Обсуждение: Modelling versioning in Postgres

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
versionsget 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
"whereobjectID=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
wouldintroduce 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
thereare benefits. 

Thanks for your time.

Laura

Thanks both for the interesting idea of using tsrange, but also for introducing me to EXCLUDE USING GIST, I had never
heardof 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
longas 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
versionsget 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
wouldintroduce 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
thereare benefits. 
> >
> > Thanks for your time.
> >
> > Laura