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
-----Original Message----- From: django-users@googlegroups.com [mailto:django-users@googlegroups.com] On Behalf Of Christian Ledermann Sent: Wednesday, September 13, 2017 12:00 PM To: django-users@googlegroups.com Subject: Re: FROM ONLY in Postgres back-end 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-te > mporal-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. -- 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/e251ad2de0ae494b90cf626eac560903%40ISS1.ISS.LOCAL. For more options, visit https://groups.google.com/d/optout.
