On 12 September 2017 at 14:32, Andrew Hankinson <andrew.hankin...@gmail.com> wrote: > 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.
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+unsubscr...@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. -- 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. }<(((*> -- 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+unsubscr...@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/CABCjzWqk-8p%2BqN9mLUfMuW2kAjP1Z7%3Dx8s3im78ARmsXrqkPjA%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
