Thread: 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
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
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 > >
"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 > > 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 > >
"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