From: | Michael van der Kolff <mvanderkolff(at)gmail(dot)com> |
---|---|
To: | Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> |
Cc: | postgre <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Modelling versioning in Postgres |
Date: | 2021-05-28 13:13:22 |
Message-ID: | CAFBbO2S7A3VZ+wiw7u1k=zTad9hsYDkgUTQj-L9dQyuuUy9SDQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)protonmail(dot)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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2021-05-28 13:21:51 | WARNING: oldest xmin is far in the past |
Previous Message | Ravi Krishna | 2021-05-28 12:40:33 | Re: TRUNCATE memory leak with temporary tables? |