Thread: Re: [GENERAL] 8.1, OID's and plpgsql
On Sat, Dec 03, 2005 at 03:07:19PM -0800, Uwe C. Schroeder wrote: > the ctid seems to be the solution to my problem. I'm inserting the record in a > transaction so the ctid shouldn't change while the transaction isn't finished > (either rolled back or committed). > One question though. How would I get the ctid of the just inserted record. GET > DIAGNOSTICS only handles row count and oid per the docs. Right now you don't. :( ISTM there should be a way to get back the row you just inserted. Whether a ctid is the right way to do that I don't know... I'm going to move this over to -hackers to see what people over there have to say. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jim@nasby.net> writes: > Right now you don't. :( ISTM there should be a way to get back the row > you just inserted. Whether a ctid is the right way to do that I don't > know... > > I'm going to move this over to -hackers to see what people over there > have to say. Perhaps the right thing to provide would be a data structure that bundled up the ctid and the transaction id. It would only be valid if the transaction id still matched the current transaction id it was used in. Any attempt to use it in a later transaction would give an error, much like using sequence.currval when nextval hasn't been used. Many people would suggest the right thing to be using is the primary key. And there ought to be an interface to fetch the current value (or values) of the primary key of the last inserted record. The benefits of providing something based on ctid is to avoid the inefficiency of the index lookup on the primary key and it would work on tables without any primary key. I'm not sure it's worth the effort it would entail for those narrow use cases especially since I think some interface to retrieve the primary will still be needed anyways. -- greg
Greg Stark <gsstark@mit.edu> writes:
> The benefits of providing something based on ctid is to avoid the inefficiency
> of the index lookup on the primary key and it would work on tables without any
> primary key. I'm not sure it's worth the effort it would entail for those
> narrow use cases especially since I think some interface to retrieve the
> primary will still be needed anyways.
Rather than hard-wiring a special case for any of these things, I'd much
rather see us implement INSERT...RETURNING and UPDATE...RETURNING as per
previous suggestions. Then you can fetch pkey, ctid, or whatever you
need.
regards, tom lane
Thanks Jim. Right now I just keep using the oid's - but it would be nice to eliminate the need for that completely. UC On Tuesday 06 December 2005 15:01, Jim C. Nasby wrote: > On Sat, Dec 03, 2005 at 03:07:19PM -0800, Uwe C. Schroeder wrote: > > the ctid seems to be the solution to my problem. I'm inserting the record > > in a transaction so the ctid shouldn't change while the transaction isn't > > finished (either rolled back or committed). > > One question though. How would I get the ctid of the just inserted > > record. GET DIAGNOSTICS only handles row count and oid per the docs. > > Right now you don't. :( ISTM there should be a way to get back the row > you just inserted. Whether a ctid is the right way to do that I don't > know... > > I'm going to move this over to -hackers to see what people over there > have to say. UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax: +1 707 568 6416
Tom Lane <tgl@sss.pgh.pa.us> writes: > Rather than hard-wiring a special case for any of these things, I'd much > rather see us implement INSERT...RETURNING and UPDATE...RETURNING as per > previous suggestions. Then you can fetch pkey, ctid, or whatever you > need. I happen to think UPDATE RETURNING is one of the coolest things since sliced bread, but that's because it saved my ass once in my last job. I wonder whether the ui tools need anything more low level than that. In general sticking their grubby fingers in the query the user entered seems wrong and they would have to tack on a RETURNING clause. Though I can't really see it failing in this particular instance. -- greg
Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Rather than hard-wiring a special case for any of these things, I'd much
>> rather see us implement INSERT...RETURNING and UPDATE...RETURNING as per
>> previous suggestions.
> I wonder whether the ui tools need anything more low level than that. In
> general sticking their grubby fingers in the query the user entered seems
> wrong and they would have to tack on a RETURNING clause.
That was mentioned before as a possible objection, but I'm not sure that
I buy it. The argument seems to be that a client-side driver would
understand the query and table structure well enough to know what to do
with a returned pkey value, but not well enough to understand how to
tack on a RETURNING clause to request that value. This seems a bit
bogus.
There may be some point in implementing a protocol-level equivalent of
RETURNING just to reduce the overhead on both sides, but I think we
ought to get the RETURNING functionality in place first and then worry
about that...
regards, tom lane
On Wed, Dec 07, 2005 at 12:06:23AM -0500, Tom Lane wrote: > Greg Stark <gsstark@mit.edu> writes: > > Tom Lane <tgl@sss.pgh.pa.us> writes: > >> Rather than hard-wiring a special case for any of these things, I'd much > >> rather see us implement INSERT...RETURNING and UPDATE...RETURNING as per > >> previous suggestions. > > > I wonder whether the ui tools need anything more low level than that. In > > general sticking their grubby fingers in the query the user entered seems > > wrong and they would have to tack on a RETURNING clause. > > That was mentioned before as a possible objection, but I'm not sure that > I buy it. The argument seems to be that a client-side driver would > understand the query and table structure well enough to know what to do > with a returned pkey value, but not well enough to understand how to > tack on a RETURNING clause to request that value. This seems a bit > bogus. > > There may be some point in implementing a protocol-level equivalent of > RETURNING just to reduce the overhead on both sides, but I think we > ought to get the RETURNING functionality in place first and then worry > about that... Along those lines, I don't see anything on the TODO list about this... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461