Row Versioning, for jdbc updateable result sets

Don't know about JDBC, but couldn't you just use UPDATE <xxx> SET
<yyy>=<zzz> WHERE xmin=<stored/old xmin> AND primarykey=<stored/old pk> and
get the number of altered records? (if its zero then you know somethings
wrong and can investigate further)
- Stuart

Stuart,

I had no idea that xmin even existed, but having a quick look I think this
is what I am looking for. Can I assume that if xmin has changed, then
another process has changed the underlying data ?

Dave
----- Original Message -----
From: "Henshall, Stuart - WCP" <SHenshall@westcountrypublications.co.uk>
To: "'Dave Cramer'" <dave@fastcrypt.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Friday, June 15, 2001 5:41 AM
Subject: [HACKERS] RE: Row Versioning, for jdbc updateable result sets

Don't know about JDBC, but couldn't you just use UPDATE <xxx> SET
<yyy>=<zzz> WHERE xmin=<stored/old xmin> AND primarykey=<stored/old pk>

and

get the number of altered records? (if its zero then you know somethings
wrong and can investigate further)
- Stuart

-----Original Message-----
From: Dave Cramer [SMTP:dave@fastcrypt.com]
Sent: Thursday, June 14, 2001 4:34 AM
To: pgsql-hackers@postgresql.org
Subject: Row Versioning, for jdbc updateable result sets

In order to be able to implement updateable result sets there needs to

be

a mechanism for determining if the underlying data has changed since the
resultset was fetched. Short of retrieving the current data and

comparing

"Dave Cramer" <dave@fastcrypt.com> writes:

I had no idea that xmin even existed, but having a quick look I think this
is what I am looking for. Can I assume that if xmin has changed, then
another process has changed the underlying data ?

xmin is a transaction ID, not a process ID, but looking at it should
work for your purposes at present.

There has been talk of redefining xmin as part of a solution to the
XID-overflow problem: what would happen is that all "sufficiently old"
tuples would get relabeled with the same special xmin, so that only
recent transactions would need to have distinguishable xmin values.
If that happens then your code would break, at least if you want to
check for changes just at long intervals.

A hack that comes to mind is that when relabeling an old tuple this way,
we could copy its original xmin into cmin while setting xmin to the
permanently-valid XID. Then, if you compare both xmin and cmin, you
have only about a 1 in 2^32 chance of being fooled. (At least if we
use a wraparound style of allocating XIDs. I think Vadim is advocating
resetting the XID counter to 0 at each system restart, so the active
range of XIDs might be a lot smaller than 2^32 in that scenario.)

regards, tom lane

Tom,

I am considering coding this into postgres's jdbc driver, as there are alot
of requests for updateable rowsets. I really don't want to code this in;
only to have it break later. Is there a way to do this? Can the version # of
the row be made available to the client?

Dave

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Dave Cramer" <dave@fastcrypt.com>
Cc: "Henshall, Stuart - WCP" <SHenshall@westcountrypublications.co.uk>;
<pgsql-hackers@postgresql.org>
Sent: Friday, June 15, 2001 10:21 AM
Subject: Re: [HACKERS] RE: Row Versioning, for jdbc updateable result sets

"Dave Cramer" <dave@fastcrypt.com> writes:

I had no idea that xmin even existed, but having a quick look I think

this

"Dave Cramer" <dave@fastcrypt.com> writes:

Can the version # of
the row be made available to the client?

There is no "version # of the row" in postgres, unless you set up such a
thing for yourself (which could certainly be done, using triggers).

regards, tom lane

On Fri, Jun 15, 2001 at 10:21:37AM -0400, Tom Lane wrote:

"Dave Cramer" <dave@fastcrypt.com> writes:

I had no idea that xmin even existed, but having a quick look I think this
is what I am looking for. Can I assume that if xmin has changed, then
another process has changed the underlying data ?

xmin is a transaction ID, not a process ID, but looking at it should
work for your purposes at present.

There has been talk of redefining xmin as part of a solution to the
XID-overflow problem: what would happen is that all "sufficiently old"
tuples would get relabeled with the same special xmin, so that only
recent transactions would need to have distinguishable xmin values.
If that happens then your code would break, at least if you want to
check for changes just at long intervals.

An simpler alternative was change all "sufficiently old" tuples to have
an xmin value, N, equal to the oldest that would need to be distinguished.
xmin values could then be compared using normal arithmetic: less(xminA,
xminB) is just ((xminA - N) < (xminB - N)), with no special cases.

A hack that comes to mind is that when relabeling an old tuple this way,
we could copy its original xmin into cmin while setting xmin to the
permanently-valid XID. Then, if you compare both xmin and cmin, you
have only about a 1 in 2^32 chance of being fooled. (At least if we
use a wraparound style of allocating XIDs. I think Vadim is advocating
resetting the XID counter to 0 at each system restart, so the active
range of XIDs might be a lot smaller than 2^32 in that scenario.)

That assumes a pretty frequent system restart. Many of us prefer
to code to the goal of a system that could run for decades.

Nathan Myers
ncm@zembu.com

"Dave Cramer" <dave@fastcrypt.com> writes:

Can the version # of
the row be made available to the client?

There is no "version # of the row" in postgres, unless you
set up such a
thing for yourself (which could certainly be done, using triggers).

And in addition there is no row version in SQL in general.
So I have the question whether it is actually intended to solve
updateable result sets with proprietary row versions, or whether
someone implemented it that way to optimize concurrent access for
another db system, that blocks readers ?

Andreas