Thanks both for the interesting idea of using tsrange, but also for introducing me to EXCLUDE USING GIST, I had never heard of it before.
Have a good weekend ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, 28 May 2021 14:13, Michael van der Kolff <mvanderko...@gmail.com> wrote: > One thing you could consider is a range type for your "versionTS" field > instead of a single point in time. > > So that would be: > > CREATE TABLE objects ( > objectID uuid, > versionID uuid, > validRange tsrange, > objectData text, > ); > > See https://www.postgresql.org/docs/12.5/rangetypes.html for more information. > > In particular, you can enforce the obvious business rule, that there is no > objectID with overlapping validRanges (as long as you have the btree_gist > extension): > > CREATE EXTENSION btree_gist; > CREATE TABLE objects ( > objectID uuid, > versionID uuid, > validRange tsrange, > objectData text, > EXCLUDE USING GIST(objectID WITH =, validRange WITH &&) > ); > > On Fri, May 28, 2021 at 8:20 PM Laura Smith > <n5d9xq3ti233xiyif...@protonmail.ch> wrote: > > > Hi > > > > I was wondering what the current thinking is on ways to model versioning in > > Postgres. > > > > The overall premise is that the latest version is the current version > > unless a rollback has occurred, in which case versions get tracked from the > > rollback point (forking ?). > > > > My initial naïve starting point is something along the lines of : > > > > create table objects ( > > objectID uuid, > > versionID uuid, > > versionTS timestamp > > objectData text > > ); > > > > This obviously creates a fool-proof answer to "latest version is the > > current version" because its a simple case of an "where objectID=x order by > > versionTS desc limit 1" query. However it clearly doesn't cover the > > rollback to prior scenarios. > > > > I then though about adding a simple "versionActive boolean". > > > > But the problem with that is it needs hand-holding somewhere because there > > can only be one active version and so it would introduce the need for a > > "active switch" script somewhere that activated the desired version and > > deactivated the others. It also perhaps is not the right way to deal with > > tracking of changes post-rollback. > > > > How have others approached the problem ? > > > > N.B. If it makes any difference, I'm dealing with a 12.5 install here, but > > this could easily be pushed up to 13 if there are benefits. > > > > Thanks for your time. > > > > Laura
