Postgres Pro
Downloads
Home   >   mailing lists

Thread: Row versioning

Row versioning

From
"Ruediger Herrmann"
Date:
Hi all,

has anyone implemented row versions/timestamps in PostgreSQL or any
thoughts on this?
Did I hit the right term? What I want to achieve is optimistic
concurrency beyound transaction boundaries. When retrieving data
I would also retrieve the row version and later on, in a different
transaction, before updating the data, I could check if was unchanged.
(row version at read time = row version at update time)

What are the pro's and con's about adding a "sequence" row that is
incremented by a trigger each time the row is updated?
Is having a timestamp row instand better?

Regards
Ruediger


--
Geschenkt: 3 Monate GMX ProMail + 3 Ausgaben der TV Movie mit DVD
++++ Jetzt anmelden und testen http://www.gmx.net/de/go/mail ++++

Re: Row versioning

From
Tom Lane
Date:
"Ruediger Herrmann" <ruediger.herrmann@gmx.de> writes:
> has anyone implemented row versions/timestamps in PostgreSQL or any
> thoughts on this?
> Did I hit the right term? What I want to achieve is optimistic
> concurrency beyound transaction boundaries. When retrieving data
> I would also retrieve the row version and later on, in a different
> transaction, before updating the data, I could check if was unchanged.

You could use the xmin system column for this.

            regards, tom lane

Re: Row versioning

From
"Ruediger Herrmann"
Date:
thanks for your replay. This approach sounds very comfy. As I read the
documentation this is kind of a "transaction sequence" or better a
"unique transaction id". Am I right with this? So every row inserted or
updated within the same transaction is tagged with the same xmin.

Is there any information wether this approach is future proof? I heard
the OID is depecated now, maybe XMIN is next, no idea...

TIA
Ruediger


> > has anyone implemented row versions/timestamps in
> PostgreSQL or any
> > thoughts on this?
> > Did I hit the right term? What I want to achieve
> is optimistic
> > concurrency beyound transaction boundaries. When
> retrieving data
> > I would also retrieve the row version and later
> on, in a different
> > transaction, before updating the data, I could
> check if was unchanged.
>
> You could use the xmin system column for this.
>
>             regards, tom lane
>
>
>

--
Geschenkt: 3 Monate GMX ProMail + 3 Ausgaben der TV Movie mit DVD
++++ Jetzt anmelden und testen http://www.gmx.net/de/go/mail ++++

Re: Row versioning

From
Tom Lane
Date:
"Ruediger Herrmann" <ruediger.herrmann@gmx.de> writes:
> Is there any information wether this approach is future proof?

[ shrug... ]  As much as anything that's not specified by the SQL
standard is around here.  We have no plans to replace MVCC, and
xmin/xmax are a pretty fundamental part of that.

            regards, tom lane