Re: 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(at)oss4u(dot)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(at)svana(dot)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.

On Thursday 01 December 2005 10:24, Jaime Casanova wrote:
> On 12/1/05, Uwe C. Schroeder <uwe(at)oss4u(dot)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(at)oss4u(dot)com> wrote:
> On Thursday 01 December 2005 10:24, Jaime Casanova wrote:
> > On 12/1/05, Uwe C. Schroeder <uwe(at)oss4u(dot)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(at)pervasive(dot)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(at)nasby(dot)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(at)nasby(dot)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(at)pervasive(dot)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(at)nasby(dot)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(at)pervasive(dot)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(at)nasby(dot)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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200512042018
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFDk5WBvJuQZxSWSsgRAnaaAKDswxUhZH4wHAJJDTZSBtTVNY/9/gCgk3La
KWRzIVIeamQZvhr+TaFp4RY=
=Nevb
-----END PGP SIGNATURE-----

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

"Jim C. Nasby" <jim(at)nasby(dot)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(at)mit(dot)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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)mit(dot)edu> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461