Thread: libpq: usage of PQoidValue to obtain serial primary key after insert

Hello,

First of all forgive me if this has been done before. I searched the 
list and found some ontopic answers but none formulated exactly this way.

Let's assume I have a test table:

create table test( id serial, data text );

I'm using libpq to execute an insert:

insert into test( data ) values( 'aaaaaa' );

which generates a new serial primary key. I need to obtain this key back 
into the application in order to use it elsewhere. So far my approach 
has been to use PQoidValue to obtain the last inserted oid and then 
explicitly do:

select id from test where oid = <oid>

Browsing the general list I've just read a post claiming that oid are 
not guaranteed to be unique in the table. This is also stated in the 
documentation - which seems to have escaped me this far. So I assume the 
approach presented above is flawed.

I would like to ask what is a most elegant way to deal with such issues?

I know I can do this:

select nextval( <sequence> );

insert into test values( <newid>, 'aaaaa' );

or:

insert into test( data ) values( 'aaaaaa' );

select currval( <sequence> );

But neiher seems particulary appealing to me because we have a little 
wrapper library we use for various projects and:

1) I would prefer not to make any assumptions about the name of the 
sequence that is created by postgres for the serial type (possibly it is 
a temp table I have just created from the code or something like that).

2) I would prefer not to make assumptions about the need for the 'select 
nextval' prior to insert since some tables might not need this at all.

3) I can imagine the primary key defaulting to some other value (i.e. 
generated by some function) where there would be no race condition proof 
currval equivalent.

So to reasume my question:

The ideal situation for me would be if I just could fire the insert with  the primary key being automagically generated
bysome (any) default 
 
(doesn't have to be a seqence) and then (if I need to) to be able to 
reach back for the primary key that was actually inserted. So far I've 
been achieving it with select ... where oid = ..... This is most 
probably wrong. Is there any other way?

Your help is very much appreciated,

Regards,

Michal Dobaczewski.

Am Donnerstag, 22. Juli 2004 13:14 schrieb Michal Dobaczewski:
> Browsing the general list I've just read a post claiming that oid are
> not guaranteed to be unique in the table. This is also stated in the
> documentation - which seems to have escaped me this far. So I assume the
> approach presented above is flawed.
>
> I would like to ask what is a most elegant way to deal with such issues?

Create a unique constraint on the oid column.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

> Am Donnerstag, 22. Juli 2004 13:14 schrieb Michal Dobaczewski:
> 
>>Browsing the general list I've just read a post claiming that oid are
>>not guaranteed to be unique in the table. This is also stated in the
>>documentation - which seems to have escaped me this far. So I assume the
>>approach presented above is flawed.
>>
>>I would like to ask what is a most elegant way to deal with such issues?
> 
> 
> Create a unique constraint on the oid column.

Thanks for the response.

I wonder: how will postgres behave if it happens to generate a 
repetitive oid for a table with such constraint? I understand it will 
work it out somehow internally and get a different oid, but I would like 
to be sure. It doesn't create a risk of inserts failing at random, does it?

I also understand this theoretically limits the number of rows in a 
table to 2^32 whereas without such constraint there is no set limit - is 
that true? It's not a problem, we don't have such big tables so far but 
it would be interesting to know.

Regards,

Michal Dobaczewski.

Michal Dobaczewski wrote:
> I wonder: how will postgres behave if it happens to generate a
> repetitive oid for a table with such constraint?

You get a constraint violation error.

> I understand it will
> work it out somehow internally and get a different oid,

No.

> but I would
> like to be sure. It doesn't create a risk of inserts failing at
> random, does it?

Yes, it would (for certain definitions of "random").

> I also understand this theoretically limits the number of rows in a
> table to 2^32 whereas without such constraint there is no set limit -
> is that true? It's not a problem, we don't have such big tables so
> far but it would be interesting to know.

If you're concerned about that, you better go back to sequences and use 
bigserial columns.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/