[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Modelling versioning in Postgres
From:       Laura Smith <n5d9xq3ti233xiyif2vp () protonmail ! ch>
Date:       2021-05-28 17:56:45
Message-ID: -a2zC7nYdFldvdZN4tk40aFxrIsSxfE6x-ROZtDdXqG2bW310poGmCufekk_bZuxk7nW3sEg_6DkQ9MtKZo0ju3ZEw40lKQeHxoKZpYmIkw= () protonmail ! ch
[Download RAW message or body]

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 <mvanderkolff@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 <n5d9xq3ti233xiyif2vp@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


[prev in list] [next in list] [prev in thread] [next in thread]