Thread: 8.1, OID's and plpgsql
Hi everyone,
in 8.1 by default tables have no OID's anymore. Since OID's are 4 byte it's
probably a good idea to discourage the use of them (they produced a lot of
trouble in the past anyways, particularly with backup/restores etc)
Now there's the issue with stored procs. A usual construct would be to
...
...
INSERT xxxxxx;
GET DIAGNOSTICS lastoid=RESULT_OID;
SELECT .... oid=lastoid;
....
....
Is there anything one could sanely replace this construct with?
I personally don't think that using the full primary key is really a good
option. Say you have a 3 column primary key - one being a "serial", the
others for example being timestamps, one of them generated with "default"
options. In order to retrieve the record I just inserted (where I don't know
the "serial" value or the timestamp) I'd have to
1) store the "nextval" of the sequence into a variable
2) generate the timestamp and store it to a variable
3) generate the full insert statement and retain the other values of the
primary key
4) issue a select to get the record.
Personally I think this adds unneccessary overhead. IMHO this diminishes the
use of defaults and sequences unless there is some easier way to retrieve the
last record. I must be missing something here - am I ?
UC
Uwe C. Schroeder wrote: > Hi everyone, > > in 8.1 by default tables have no OID's anymore. Since OID's are 4 byte it's > probably a good idea to discourage the use of them (they produced a lot of > trouble in the past anyways, particularly with backup/restores etc) > > Now there's the issue with stored procs. A usual construct would be to > ... > ... > INSERT xxxxxx; > GET DIAGNOSTICS lastoid=RESULT_OID; > SELECT .... oid=lastoid; > .... > .... > > Is there anything one could sanely replace this construct with? > I personally don't think that using the full primary key is really a good > option. There we disagree. That's what the primary-key is for. Of course that means we want a last_primary_key_from_insert() system-function. > Say you have a 3 column primary key - one being a "serial", the > others for example being timestamps, one of them generated with "default" > options. Then you have a bad primary key - the timestamps add nothing to the serial (or vice-versa). > In order to retrieve the record I just inserted (where I don't know > the "serial" value or the timestamp) I'd have to > > 1) store the "nextval" of the sequence into a variable > 2) generate the timestamp and store it to a variable > 3) generate the full insert statement and retain the other values of the > primary key > 4) issue a select to get the record. > > Personally I think this adds unneccessary overhead. IMHO this diminishes the > use of defaults and sequences unless there is some easier way to retrieve the > last record. I must be missing something here - am I ? Yes - add a SERIAL column with UNIQUE and fetch on that if you really need to. This effectively gives you your OID back. -- Richard Huxton Archonet Ltd
On 12/1/05, Uwe C. Schroeder <uwe@oss4u.com> wrote: > > Hi everyone, > > in 8.1 by default tables have no OID's anymore. Since OID's are 4 byte it's > probably a good idea to discourage the use of them (they produced a lot of > trouble in the past anyways, particularly with backup/restores etc) > > Now there's the issue with stored procs. A usual construct would be to > ... > ... > INSERT xxxxxx; > GET DIAGNOSTICS lastoid=RESULT_OID; > SELECT .... oid=lastoid; > .... > .... > > Is there anything one could sanely replace this construct with? > I personally don't think that using the full primary key is really a good > option. Say you have a 3 column primary key - one being a "serial", the > others for example being timestamps, one of them generated with "default" > options. In order to retrieve the record I just inserted (where I don't know > the "serial" value or the timestamp) I'd have to > > 1) store the "nextval" of the sequence into a variable > 2) generate the timestamp and store it to a variable > 3) generate the full insert statement and retain the other values of the > primary key > 4) issue a select to get the record. > > Personally I think this adds unneccessary overhead. IMHO this diminishes the > use of defaults and sequences unless there is some easier way to retrieve the > last record. I must be missing something here - am I ? > > UC > If you are using a SERIAL in your PK, why you need the other two fields? The serial will undoubtly identify a record? you just retrieve the current value you inserted with currval -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
On Thu, Dec 01, 2005 at 09:01:05AM -0800, Uwe C. Schroeder wrote: > Now there's the issue with stored procs. A usual construct would be to > ... > ... > INSERT xxxxxx; > GET DIAGNOSTICS lastoid=RESULT_OID; > SELECT .... oid=lastoid; > .... > .... > > Is there anything one could sanely replace this construct with? currval()? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Thursday 01 December 2005 10:24, Jaime Casanova wrote:
> On 12/1/05, Uwe C. Schroeder <uwe@oss4u.com> wrote:
> > Hi everyone,
> >
> > in 8.1 by default tables have no OID's anymore. Since OID's are 4 byte
> > it's probably a good idea to discourage the use of them (they produced a
> > lot of trouble in the past anyways, particularly with backup/restores
> > etc)
> >
> > Now there's the issue with stored procs. A usual construct would be to
> > ...
> > ...
> > INSERT xxxxxx;
> > GET DIAGNOSTICS lastoid=RESULT_OID;
> > SELECT .... oid=lastoid;
> > ....
> > ....
> >
> > Is there anything one could sanely replace this construct with?
> > I personally don't think that using the full primary key is really a good
> > option. Say you have a 3 column primary key - one being a "serial", the
> > others for example being timestamps, one of them generated with "default"
> > options. In order to retrieve the record I just inserted (where I don't
> > know the "serial" value or the timestamp) I'd have to
> >
> > 1) store the "nextval" of the sequence into a variable
> > 2) generate the timestamp and store it to a variable
> > 3) generate the full insert statement and retain the other values of the
> > primary key
> > 4) issue a select to get the record.
> >
> > Personally I think this adds unneccessary overhead. IMHO this diminishes
> > the use of defaults and sequences unless there is some easier way to
> > retrieve the last record. I must be missing something here - am I ?
> >
> > UC
>
> If you are using a SERIAL in your PK, why you need the other two
> fields? The serial will undoubtly identify a record?
>
> you just retrieve the current value you inserted with currval
>
No it doesn't. the serial identifies the record, the timestamp identifies the
version/time-validity of the record.
If a primary key needs to be something as simple as a serial then we could
just keep the OID's as well and pump them up to 32 bytes.
curval() doesn't do it, since that will only identify a group of records since
my PK is not just a simple int4.
sample:
create table xxx (
id serial,
field varchar,
...
...
valid_from timestamptz
)
PK is id,valid_from
There may be several records with the same id but different valid_from dates.
I'm storing a full timestamp, but the application only uses the date part -
the timestamp is just to correct for timezones.
From the application logic a record is considered valid until a record with a
newer valid_from is found. From that point on the records are referenced
depending on several legal factors (this is commercial insurance, lots of
lawyers and state/fed regulations)
I guess I either stick to the OID's which work fine, or I just have to store
the whole PK in variables and forget about defaults.
Why not have something like the rowid in oracle?
UC
On 12/1/05, Uwe C. Schroeder <uwe@oss4u.com> wrote: > On Thursday 01 December 2005 10:24, Jaime Casanova wrote: > > On 12/1/05, Uwe C. Schroeder <uwe@oss4u.com> wrote: > > > Hi everyone, > > > > > > in 8.1 by default tables have no OID's anymore. Since OID's are 4 byte > > > it's probably a good idea to discourage the use of them (they produced a > > > lot of trouble in the past anyways, particularly with backup/restores > > > etc) > > > > > > Now there's the issue with stored procs. A usual construct would be to > > > ... > > > ... > > > INSERT xxxxxx; > > > GET DIAGNOSTICS lastoid=RESULT_OID; > > > SELECT .... oid=lastoid; > > > .... > > > .... > > > > > > Is there anything one could sanely replace this construct with? > > > I personally don't think that using the full primary key is really a good > > > option. Say you have a 3 column primary key - one being a "serial", the > > > others for example being timestamps, one of them generated with "default" > > > options. In order to retrieve the record I just inserted (where I don't > > > know the "serial" value or the timestamp) I'd have to > > > > > > 1) store the "nextval" of the sequence into a variable > > > 2) generate the timestamp and store it to a variable > > > 3) generate the full insert statement and retain the other values of the > > > primary key > > > 4) issue a select to get the record. > > > > > > Personally I think this adds unneccessary overhead. IMHO this diminishes > > > the use of defaults and sequences unless there is some easier way to > > > retrieve the last record. I must be missing something here - am I ? > > > > > > UC > > > > If you are using a SERIAL in your PK, why you need the other two > > fields? The serial will undoubtly identify a record? > > > > you just retrieve the current value you inserted with currval > > > > No it doesn't. the serial identifies the record, the timestamp identifies the > version/time-validity of the record. you don't need valid_from to be part of the PK, just the serial... > If a primary key needs to be something as simple as a serial then we could > just keep the OID's as well and pump them up to 32 bytes. > No. because tables with OIDs are not the default anymore and is not recomended to use OIDs as PK > curval() doesn't do it, since that will only identify a group of records since > my PK is not just a simple int4. > currval() identifies the last value you inserted... that's one of the reason to prefer SERIAL over OIDs... an API for manage them... > sample: > > create table xxx ( > id serial, > field varchar, > ... > ... > valid_from timestamptz > ) > > PK is id,valid_from > There may be several records with the same id but different valid_from dates. > I'm storing a full timestamp, but the application only uses the date part - > the timestamp is just to correct for timezones. > obviously you are using wrong the datatype serial if you let the serial column insert always its default then there won't be several record with the same id > From the application logic a record is considered valid until a record with a > newer valid_from is found. From that point on the records are referenced > depending on several legal factors (this is commercial insurance, lots of > lawyers and state/fed regulations) > and? you still don't need valid_from as part of the PK if id is a serial... i think what you really want is to make id an integer and then let valid_from as part of PK... and make a select to retrieve the valid one SELECT * FROM xxx WHERE id = ??? ORDER BY valid_from DESC LIMIT 1 > > I guess I either stick to the OID's which work fine, or I just have to store > the whole PK in variables and forget about defaults. > > Why not have something like the rowid in oracle? > > > UC > -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
On Thu, Dec 01, 2005 at 07:18:10PM -0800, Uwe C. Schroeder wrote: > Why not have something like the rowid in oracle? http://www.postgresql.org/docs/8.1/interactive/datatype-oid.html, search on ctid. And http://www.postgresql.org/docs/8.1/interactive/ddl-system-columns.html. From the 2nd URL: ctid The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change each time it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows. Though I think that a lazy vacuum can change (well, technically remove) a ctid. AFAIK, it's not safe to use a ctid outside of the transaction you got it in. Though come to think about it, I don't think there's any way to get the ctid of a row you just inserted anyway... Maybe the docs should be changed to just say that you should never reuse a ctid outside of the transaction you obtained the ctid in? -- 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:
> Maybe the docs should be changed to just say that you should never reuse
> a ctid outside of the transaction you obtained the ctid in?
That's not a sufficient rule either: someone else could still delete or
update the row while your transaction runs. You'd really have to SELECT
FOR UPDATE or FOR SHARE to be sure the ctid remains stable. (Of course,
this isn't an issue for the case of a row you just inserted yourself,
since no one else can see it yet to change it.)
The paragraph defining ctid is not the place for a discussion of how it
could be used ... I'm not quite sure where is, though.
regards, tom lane
On Fri, Dec 02, 2005 at 06:58:39PM -0500, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > Maybe the docs should be changed to just say that you should never reuse > > a ctid outside of the transaction you obtained the ctid in? > > That's not a sufficient rule either: someone else could still delete or > update the row while your transaction runs. You'd really have to SELECT > FOR UPDATE or FOR SHARE to be sure the ctid remains stable. (Of course, > this isn't an issue for the case of a row you just inserted yourself, > since no one else can see it yet to change it.) > > The paragraph defining ctid is not the place for a discussion of how it > could be used ... I'm not quite sure where is, though. Maybe the MVCC paragraph? Related to the original question though, is there actually any way to get the ctid of a row that was just inserted? -- 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
On Fri, Dec 02, 2005 at 06:58:39PM -0500, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > Maybe the docs should be changed to just say that you should never reuse > > a ctid outside of the transaction you obtained the ctid in? > > That's not a sufficient rule either: someone else could still delete or > update the row while your transaction runs. You'd really have to SELECT > FOR UPDATE or FOR SHARE to be sure the ctid remains stable. (Of course, Erm, even if they do delete or update the row, wouldn't it's ctid still remain valid since nothing could vacuum it yet? Of course, now it'd probably see the old version of the row, but that behavior could be changed so that the database would follow t_ctid in that case. -- 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
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. THX UC On Friday 02 December 2005 15:58, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > Maybe the docs should be changed to just say that you should never reuse > > a ctid outside of the transaction you obtained the ctid in? > > That's not a sufficient rule either: someone else could still delete or > update the row while your transaction runs. You'd really have to SELECT > FOR UPDATE or FOR SHARE to be sure the ctid remains stable. (Of course, > this isn't an issue for the case of a row you just inserted yourself, > since no one else can see it yet to change it.) > > The paragraph defining ctid is not the place for a discussion of how it > could be used ... I'm not quite sure where is, though. > > regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Related to the original question though, is there actually any way to > get the ctid of a row that was just inserted? No. You'd have to identify the rows some other way (a sequence is the canonical way), and then grab the ctid from that. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200512042018 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFDk5WBvJuQZxSWSsgRAnaaAKDswxUhZH4wHAJJDTZSBtTVNY/9/gCgk3La KWRzIVIeamQZvhr+TaFp4RY= =Nevb -----END PGP SIGNATURE-----