FROM ONLY in Postgres back-end

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/

--
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+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@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.