Re: Database Features Questions, - Mailing list pgsql-general

On Wed, 20 Sep 2000 17:54:40 -0400, Joe Kislo wrote:

> > you should not use the OID value for application level work.  for one thing,
> > it's not portable, and if you rebuild the database it'll change.  you should
> > consider it a strictly internal value.
>
>     Hmm, I think you missed what my ultimate goal of using it was...
>

well, you said:
"I see that to create unique identifiers for a column, I can use the OID
value or a sequence. ..."

to which i would say: OID's aren't created in the user's space:  they're a global
value for all databases (they also don't uniquely identify a column, but a row in
a given table, in a given database in an installation of postgres).
if you're looking to create unique identifiers for a given application,
you want to use sequences and not rely on OID's.

but, if i'm missing something i apologize in advance.


> > anyway, that's what sequences are for (as you recognize).  they're portable,
> > and under your control.  you can create one like so:
> >
> >         CREATE SEQUENCE tablename_colname_seq;
> >         CREATE TABLE tablename
> >             (colname INT4 DEFAULT nextval('tablename_colname_seq');
> >         CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
> >
> > in the midst of a transaction you can get the current value of the sequence
> > by calling the funciton 'currval':
> >
> >         SELECT currval('tablename_colname_seq');
> >
> > This is the most portable way of accomplishing what you're after.
> >
> > Postgres also offers a special data type called 'SERIAL':
> >
> >         CREATE TABLE tablename (colname SERIAL);
> >
> > is the equivalent of the above 3 create statements.
>
>     What I'm trying to do is insert a record, one with a SERIAL column, and
> after that insert know what the setting to that serial value was.
>
> So:
>
> CREATE Table whatever (whateverID SERIAL),
>                        (otherdata int);
> insert into whatever (otherdata) values (1);
>
> This insert function, in postgresql, returns the OID value as a result.
> Unfortunately I want the -serial- number back, not the OID.  Does this
> mean I must initiate another request to the database server to get the
> serialID that was issued to my insert function?
>

yes, the insert statement returns the OID (along with the command, and the
number of rows affected), but only in psql.  other interfaces are not guaranteed
to return it (because it's postgres specific).  for example, if you do an
insert via perl's DBD::Pg, all you get back into the application is the number of rows
affected.


> As to your suggestion to use:
> select currval("whatever_seq");
>
> Would that work if I was in autocommit mode?  Wouldn't that create a

yes that's what you need to do.  and no, it won't work in autocommit.
see below . . .


> race condition if other threads were inserting rows into table
> "whatever", and updating the sequence number? Or does "currval" always
> return the LAST sequence number used by -my- connection?
>

it won't create a race condition, because postgres doesn't lock a row in
the database from reading when writing to it (unless specified to).  currval
will return the last sequence number used by your connection only when you're
using transactions (i.e. autocommit set to true) and you haven't committed
or rolled back.


> Is there anyway to get the serial# to be returned instead of the OID, or
> do I have to send a second query to track the serial numbers on my
> inserts?
>

so, to finally answer your question ;-) . . .

you would get the value of the serial column by calling the function currval
(which is simply a sql query on a system table, which maintains info about each
individual sequence by name) on the sequence for the SERIAL column in your table.
the naming conventiion is (i believe) "tablename_colname_seq."

if you need the value for a second insert (like for a table that has a FK
reference to the one you just inserted to) you could do this:

begin;
insert into foo (A,B) values (B);  /* where A is a serial column */
insert into bar (C,D) values ( currval('foo_A_seq'), 'yuck');  /* where C is a FK ref to foo */
commit;

if you simply need to get the "number" from that column you'd:

begin;
insert into foo (A,B) values (B);  /* where A is a serial column */
select currval('foo_A_seq');
commit;

note that the transaction is key, without which there's no guarantee that
some other statement will affect the value of the sequence.

HTH
--e--






> Thanks,
> -Joe
>