FROM ONLY in Postgres back-end

[Andrew Hankinson]

Hello,

tl;dr I'm trying to implement 'temporal tables' [0] in Django with a Postgres backend, and I'm stuck on how to generate the appropriate SELECT ... FROM ONLY and UPDATE queries.

The temporal tables pattern depends on having two mirror tables. For a simple example, let's call them "person" and "person_history". As records are changed in "person," the previous states of that table are stored in "person_history".

The simplest way to implement table mirroring is to use "INHERITS" in Postgres, e.g., 

CREATE TABLE person_history () INHERITS (person);

This is the simplest since any changes to the 'person' schema will automatically be reflected in person_history. (And no, I haven't tested schema changes and column renaming and interactions with migrations with Django just yet... it will come later). I've implemented this as Operations in the table migrations.

When a table linked by "INHERITS" is queried, it will query both the 'person' and 'person_history' tables by default. This will obviously break a lot of things in Django. (e.g., a .get() will return more than one row...)  The easiest way around that is to change "FROM" to "FROM ONLY" in all SQL queries, e.g., 

SELECT name FROM ONLY person WHERE id = 1;

The same goes for UPDATE.

The part where I'm stuck is trying to figure out at what level I start digging. Do I modify QuerySet to try and implement my own custom lookup code? Or will I need to dig into the SQL compiler to do this? Or is this whole thing a waste of my time?

Or maybe there's a magic hook somewhere I'm missing that will let me inject what I need. I'm hoping for this one, but I haven't gotten my hopes up. :)

Thanks in advance for your help,

-Andrew

[0] http://clarkdave.net/2015/02/historical-records-with-postgresql-and-temporal-tables-and-sql-2011/

[Christian Ledermann]

On 12 September 2017 at 14:32, Andrew Hankinson

It depends

> Do I modify QuerySet to try and implement my own custom lookup
> code?

you can always do raw SQL queries, but that kind of defeats the
purpose of having an ORM.

> Or will I need to dig into the SQL compiler to do this?

Likely.

> Or is this
> whole thing a waste of my time?

even more likely

>
> Or maybe there's a magic hook somewhere I'm missing that will let me inject
> what I need. I'm hoping for this one, but I haven't gotten my hopes up. :)
>
> Thanks in advance for your help,
> -Andrew
>
> [0]
> http://clarkdave.net/2015/02/historical-records-with-postgresql-and-temporal-tables-and-sql-2011/
>

> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/f3423173-d0ab-4c7c-a5af-1592947f07ca%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Best Regards,

Christian Ledermann

Newark-on-Trent - UK
Mobile : +44 7474997517

https://uk.linkedin.com/in/christianledermann
https://github.com/cleder/


<*)))>{

If you save the living environment, the biodiversity that we have left,
you will also automatically save the physical environment, too. But If
you only save the physical environment, you will ultimately lose both.

1) Don’t drive species to extinction

2) Don’t destroy a habitat that species rely on.

3) Don’t change the climate in ways that will result in the above.

}<(((*>

[Matthew Pava]

If you want to do auditing of the database, I would look at packages available for Django. This is the first I've read about "temporal tables," and my first impression is that of grave concern.
Django-reversion is a popular package:
https://github.com/etianen/django-reversion

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CABCjzWqk-8p%2BqN9mLUfMuW2kAjP1Z7%3Dx8s3im78ARmsXrqkPjA%40mail.gmail.com.

[Andrew Hankinson]

Thanks for your replies.

I have looked at the other options available. While they are suitable for version control, they're not well suited for queries where you want to see the state of a given object at a given time. "Show me what X looked like on Y date" can be useful, and shouldn't require a reversion. In this case, I'm experimenting with developing an implementation of the Memento [1] standard within Django. 

http://mementoweb.org/guide/rfc/

I've figured out how to modify the SELECT query, and now I'm working on UPDATE. For select you need to subclass the Compiler and implement a custom `get_from_clause` method. UPDATE is a trickier, but I've made some progress.

Like I said, it may be stopped in its tracks when it comes to schema changes and migrations, but for now I'm satisfied with the amount of progress I'm making; it's all about finding which knobs to turn! 

Temporal Tables are a fairly well-established, if advanced, pattern in SQL servers, and is part of the spec in SQL 2011.