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
> the entire row, can anyone think of a way possibly using the row version
> to determine if the data has been concurrently changed?
>  
> Dave

Fri, 15 Jun 2001 10:41:37 +0100
"Henshall, Stuart - WCP" <SHenshall@westcountrypublications.co.uk>
RE: Row Versioning, for jdbc updateable result sets
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
> > the entire row, can anyone think of a way possibly using the row version
> > to determine if the data has been concurrently changed?
> >
> > Dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>


Fri, 15 Jun 2001 07:48:48 -0400
"Dave Cramer" <dave@fastcrypt.com>
Re: 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
> 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

Fri, 15 Jun 2001 10:21:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: RE: Row Versioning, for jdbc updateable result sets 
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
> > 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
>
>


Fri, 15 Jun 2001 11:50:32 -0400
"Dave Cramer" <dave@fastcrypt.com>
Re: RE: Row Versioning, for jdbc updateable result sets 
"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

Fri, 15 Jun 2001 14:27:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: RE: Row Versioning, for jdbc updateable result sets 
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

Fri, 15 Jun 2001 16:31:06 -0700
ncm@zembu.com (Nathan Myers)
Re: RE: Row Versioning, for jdbc updateable result sets