pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedlibpq: usage of PQoidValue to obtain serial primary key after insert
4+ messages / 2 participants
[nested] [flat]
* libpq: usage of PQoidValue to obtain serial primary key after insert
@ 2004-07-22 11:14 Michal Dobaczewski <mdobaczewski@cc.com.pl>
0 siblings, 1 reply; 4+ messages in thread
From: Michal Dobaczewski @ 2004-07-22 11:14 UTC (permalink / raw)
To: pgsql-interfaces
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 by some (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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: libpq: usage of PQoidValue to obtain serial primary key after insert
@ 2004-07-22 12:44 Peter Eisentraut <peter_e@gmx.net>
parent: Michal Dobaczewski <mdobaczewski@cc.com.pl>
0 siblings, 1 reply; 4+ messages in thread
From: Peter Eisentraut @ 2004-07-22 12:44 UTC (permalink / raw)
To: Michal Dobaczewski <mdobaczewski@cc.com.pl>; +Cc: pgsql-interfaces
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: libpq: usage of PQoidValue to obtain serial primary
@ 2004-07-22 13:51 Michal Dobaczewski <mdobaczewski@cc.com.pl>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: Michal Dobaczewski @ 2004-07-22 13:51 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: libpq: usage of PQoidValue to obtain serial primary key after insert
@ 2004-07-22 17:54 Peter Eisentraut <peter_e@gmx.net>
parent: Michal Dobaczewski <mdobaczewski@cc.com.pl>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2004-07-22 17:54 UTC (permalink / raw)
To: Michal Dobaczewski <mdobaczewski@cc.com.pl>; +Cc: pgsql-interfaces
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-07-22 17:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-22 11:14 libpq: usage of PQoidValue to obtain serial primary key after insert Michal Dobaczewski <mdobaczewski@cc.com.pl>
2004-07-22 12:44 ` Peter Eisentraut <peter_e@gmx.net>
2004-07-22 13:51 ` Re: libpq: usage of PQoidValue to obtain serial primary Michal Dobaczewski <mdobaczewski@cc.com.pl>
2004-07-22 17:54 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox