Обсуждение: Last value inserted

Hi guys,

I am in troubles with a SERIAL field.

I have five tables. A parent table and four child
tables. When I do the INSERT in the parent table, I
have an ID (generated) by the sequence (SERIAL field),
and I have to use this ID to reference all child
tables.

Well, once I do an INSERT in the parent table, how can
I know (for sure) which number id was generated by the
sequence?

Simple example:

------------------------------------------------------
CREATE TABLE parent(id SERIAL, descrip CHAR(50));
------------------------------------------------------

So,

------------------------------------------------------
INSERT INTO parent (descrip) VALUES ('project 1');
------------------------------------------------------

How can I now (for sure) with value was generated by
the sequence to fill the field ID?

(There is lots of users using the software at the same
time, so I am not able to use the last_value()
function on the sequence.)

Best Regards,

Marcelo Pereira
Brazil





_______________________________________________________
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/

I think the best way would be not to use a SERIAL field, but an INTEGER
field and a sequence:

CREATE SEQUENCE parent_seq;
CREATE TABLE parent(id INTEGER, descrip CHAR(50));


So when you want to insert on the parent table, you obtain the next
value from the sequence and then you insert in the parent and child
tables the value you obtained:

newId:=SELECT nextval('parent_seq')
INSERT INTO parent(id, descrip) VALUES (newId, 'XXXX');
INSERT INTO child_1(..., ..., parentId) VALUES (..., ..., newId);
INSERT INTO child_2(..., ..., parentId) VALUES (..., ..., newId);
INSERT INTO child_3(..., ..., parentId) VALUES (..., ..., newId);


hope it helps.

MaRCeLO PeReiRA wrote:

>Hi guys,
>
>I am in troubles with a SERIAL field.
>
>I have five tables. A parent table and four child
>tables. When I do the INSERT in the parent table, I
>have an ID (generated) by the sequence (SERIAL field),
>and I have to use this ID to reference all child
>tables.
>
>Well, once I do an INSERT in the parent table, how can
>I know (for sure) which number id was generated by the
>sequence?
>
>Simple example:
>
>------------------------------------------------------
>CREATE TABLE parent(id SERIAL, descrip CHAR(50));
>------------------------------------------------------
>
>So,
>
>------------------------------------------------------
>INSERT INTO parent (descrip) VALUES ('project 1');
>------------------------------------------------------
>
>How can I now (for sure) with value was generated by
>the sequence to fill the field ID?
>
>(There is lots of users using the software at the same
>time, so I am not able to use the last_value()
>function on the sequence.)
>
>Best Regards,
>
>Marcelo Pereira
>Brazil
>
>
>
>
>
>_______________________________________________________
>Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>

On Thu, 2004-11-11 at 09:59 -0300, MaRCeLO PeReiRA wrote:
> Hi guys,
>
> I am in troubles with a SERIAL field.
>
> I have five tables. A parent table and four child
> tables. When I do the INSERT in the parent table, I
> have an ID (generated) by the sequence (SERIAL field),
> and I have to use this ID to reference all child
> tables.
>
> Well, once I do an INSERT in the parent table, how can
> I know (for sure) which number id was generated by the
> sequence?
>
> Simple example:
>
> ------------------------------------------------------
> CREATE TABLE parent(id SERIAL, descrip CHAR(50));
> ------------------------------------------------------
>
> So,
>
> ------------------------------------------------------
> INSERT INTO parent (descrip) VALUES ('project 1');
> ------------------------------------------------------
>
> How can I now (for sure) with value was generated by
> the sequence to fill the field ID?
>
> (There is lots of users using the software at the same
> time, so I am not able to use the last_value()
> function on the sequence.)
>
> Best Regards,
>
> Marcelo Pereira
> Brazil


I just asked this same question about a week or two ago and I got a
response from Jonathan Daugherty who helped me with the initial query,
and in PHP I was able to come up with:

http://blog.planetargon.com/index.php?/archives/29_PHP_pg_insert_id_.html

This was on the list a few weeks ago:

> -- get_sequence(schema_name, table_name, column_name)
>
> CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS
> text AS '
>   SELECT seq.relname::text
>   FROM pg_class src, pg_class seq, pg_namespace, pg_attribute,
> pg_depend
>   WHERE
>     pg_depend.refobjsubid = pg_attribute.attnum AND
>     pg_depend.refobjid = src.oid AND
>     seq.oid = pg_depend.objid AND
>     src.relnamespace = pg_namespace.oid AND
>     pg_attribute.attrelid = src.oid AND
>     pg_namespace.nspname = $1 AND
>     src.relname = $2 AND
>     pg_attribute.attname = $3;
> ' language sql;

hth,

Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON  | www.planetargon.com
* Portland, OR  | robby@planetargon.com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
*    --- Now supporting PHP5 ---
****************************************/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


option 1) use a stored procedure to create the record. Within a transaction
the last value function will return the correct last value, not the one of a
concurrent insert.
option 2) if you know that this user uses the same connection for all his
queries - or at least the ones in question - you can rely on the last value
being the correct one. Like with transactions, if you use the same connection
the last value will be the correct one.

You're only in trouble if you're not within a transaction and you're not sure
if the connection stays the same for the queries in question. The later could
be due to connection pooling.

UC


On Thursday 11 November 2004 04:59 am, MaRCeLO PeReiRA wrote:
> Hi guys,
>
> I am in troubles with a SERIAL field.
>
> I have five tables. A parent table and four child
> tables. When I do the INSERT in the parent table, I
> have an ID (generated) by the sequence (SERIAL field),
> and I have to use this ID to reference all child
> tables.
>
> Well, once I do an INSERT in the parent table, how can
> I know (for sure) which number id was generated by the
> sequence?
>
> Simple example:
>
> ------------------------------------------------------
> CREATE TABLE parent(id SERIAL, descrip CHAR(50));
> ------------------------------------------------------
>
> So,
>
> ------------------------------------------------------
> INSERT INTO parent (descrip) VALUES ('project 1');
> ------------------------------------------------------
>
> How can I now (for sure) with value was generated by
> the sequence to fill the field ID?
>
> (There is lots of users using the software at the same
> time, so I am not able to use the last_value()
> function on the sequence.)
>
> Best Regards,
>
> Marcelo Pereira
> Brazil
>
>
>
>
>
> _______________________________________________________
> Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora!
> http://br.acesso.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD4DBQFBk6lnjqGXBvRToM4RAgOXAJiVy9TG9Yv05pegzACw2VPeN7USAKDRYg/N
H0BKK8WT1aOZ+CB3rCl8WQ==
=kiLq
-----END PGP SIGNATURE-----

On Thu, Nov 11, 2004 at 09:59:16 -0300,
  MaRCeLO PeReiRA <gandalf_mp@yahoo.com.br> wrote:
>
> Well, once I do an INSERT in the parent table, how can
> I know (for sure) which number id was generated by the
> sequence?

Use currval.

MaRCeLO PeReiRA wrote:

> How can I now (for sure) with value was generated by
> the sequence to fill the field ID?
>
> (There is lots of users using the software at the same
> time, so I am not able to use the last_value()
> function on the sequence.)

Yes you are nextval()/currval() are multi-user safe. They return the
next/current value *in the current connection*.

--
   Richard Huxton
   Archonet Ltd

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 11 November 2004 10:23 am, Franco Bruno Borghesi wrote:
> I think the best way would be not to use a SERIAL field, but an INTEGER
> field and a sequence:

a "serial" is just a convenient shortcut to an int with an automatically
created sequence. As proof - just create a table with a serial and dump it
with pg_dump: you'll end up with a table containing an int with a nextval(...
as the default. The only difference is that in case of the "serial" field you
don't name the sequence yourself.

> CREATE SEQUENCE parent_seq;
> CREATE TABLE parent(id INTEGER, descrip CHAR(50));
>
>
> So when you want to insert on the parent table, you obtain the next
> value from the sequence and then you insert in the parent and child
> tables the value you obtained:
>
> newId:=SELECT nextval('parent_seq')
> INSERT INTO parent(id, descrip) VALUES (newId, 'XXXX');
> INSERT INTO child_1(..., ..., parentId) VALUES (..., ..., newId);
> INSERT INTO child_2(..., ..., parentId) VALUES (..., ..., newId);
> INSERT INTO child_3(..., ..., parentId) VALUES (..., ..., newId);

which amounts to the curval in the same connection.

>
> hope it helps.
>
> MaRCeLO PeReiRA wrote:
> >Hi guys,
> >
> >I am in troubles with a SERIAL field.
> >
> >I have five tables. A parent table and four child
> >tables. When I do the INSERT in the parent table, I
> >have an ID (generated) by the sequence (SERIAL field),
> >and I have to use this ID to reference all child
> >tables.
> >
> >Well, once I do an INSERT in the parent table, how can
> >I know (for sure) which number id was generated by the
> >sequence?
> >
> >Simple example:
> >
> >------------------------------------------------------
> >CREATE TABLE parent(id SERIAL, descrip CHAR(50));
> >------------------------------------------------------
> >
> >So,
> >
> >------------------------------------------------------
> >INSERT INTO parent (descrip) VALUES ('project 1');
> >------------------------------------------------------
> >
> >How can I now (for sure) with value was generated by
> >the sequence to fill the field ID?
> >
> >(There is lots of users using the software at the same
> >time, so I am not able to use the last_value()
> >function on the sequence.)
> >
> >Best Regards,
> >
> >Marcelo Pereira
> >Brazil
> >
> >
> >
> >
> >
> >_______________________________________________________
> >Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora!
> > http://br.acesso.yahoo.com/
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

- --
    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBlFD5jqGXBvRToM4RAmfQAJ9JyQxERqcau1kCnvkrXNmpaGTwzwCgqK6L
7zCpR+uO5pzvDuY/itTYCfs=
=mq0M
-----END PGP SIGNATURE-----

Which means that sometimes they do not return the correct value - if you
have a trigger that inserts another record you will not get the right value.
MSSQL has @@IDENTITY and SCOPE_IDENTITY() to handle this case, I'm new to
pgsql so I don't know if it has anything like that.

Jerry

"Richard Huxton" <dev@archonet.com> wrote in message
news:4193B30B.40905@archonet.com...
> MaRCeLO PeReiRA wrote:
>
>> How can I now (for sure) with value was generated by
>> the sequence to fill the field ID?
>>
>> (There is lots of users using the software at the same
>> time, so I am not able to use the last_value()
>> function on the sequence.)
>
> Yes you are nextval()/currval() are multi-user safe. They return the
> next/current value *in the current connection*.
>
> --
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--- Jerry III <jerryiii@hotmail.com> wrote:

> Which means that sometimes they do not return the
> correct value - if you
> have a trigger that inserts another record you will
> not get the right value.

If you are new to PostgreSQL, as you say, then why are
you so sure of this?  Perhaps you may profit from
looking a little more at how currval() works.

> MSSQL has @@IDENTITY and SCOPE_IDENTITY() to handle
> this case, I'm new to
> pgsql so I don't know if it has anything like that.
>
> Jerry
>
> "Richard Huxton" <dev@archonet.com> wrote in message
>
> news:4193B30B.40905@archonet.com...
> > MaRCeLO PeReiRA wrote:
> >
> >> How can I now (for sure) with value was generated
> by
> >> the sequence to fill the field ID?
> >>
> >> (There is lots of users using the software at the
> same
> >> time, so I am not able to use the last_value()
> >> function on the sequence.)
> >
> > Yes you are nextval()/currval() are multi-user
> safe. They return the
> > next/current value *in the current connection*.
> >
> > --
> >   Richard Huxton
> >   Archonet Ltd
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>




__________________________________
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com

On Tue, 16 Nov 2004, Jeff Eckermann wrote:

> --- Jerry III <jerryiii@hotmail.com> wrote:
>
> > Which means that sometimes they do not return the
> > correct value - if you
> > have a trigger that inserts another record you will
> > not get the right value.
>
> If you are new to PostgreSQL, as you say, then why are
> you so sure of this?  Perhaps you may profit from
> looking a little more at how currval() works.

He's correct.  One thing that currval will not help with is a
case where more than one row has been inserted by a statement
(whether due to the base statement or triggers).

A somewhat absurd example:

---

create table q1(a serial, b int);

create function f1() returns trigger as 'begin if (random() >
0.5) then insert into q1 default values; end if; return NEW; end;'
language 'plpgsql';

create trigger q1_f1 after insert on q1 for each row execute
procedure f1();

insert into q1(b) values (3);

select currval('q1_a_seq');

select * from q1;

----

I got a currval of 3 which was the last row inserted, but that was from
the trigger, not the row created by my insert so it didn't have the
correct b value.

Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
> --- Jerry III <jerryiii@hotmail.com> wrote:
>> Which means that sometimes they do not return the
>> correct value - if you
>> have a trigger that inserts another record you will
>> not get the right value.

> If you are new to PostgreSQL, as you say, then why are
> you so sure of this?  Perhaps you may profit from
> looking a little more at how currval() works.

Jerry's correct, although the concern is more theoretical than real IMHO.
What he's imagining is a situation where you do, say,

    INSERT INTO foo ...;
    SELECT currval('foo_id_seq');

and there is an ON INSERT trigger on foo that directly or indirectly
does a nextval('foo_id_seq').  Execution of the trigger will then
advance the sequence beyond what was used to generate the inserted row,
and the subsequent currval() will return the wrong answer (or at least
not the answer you wanted).  Note there is no race condition here; it's
just one process involved.

The reason I think this is mostly a theoretical issue is that I don't
see any good reason for such a trigger to be doing a nextval on the
table's ID sequence.  The trigger is certainly not going to insert
additional rows in foo --- if it did that would lead to infinite
recursion of the trigger.  So ISTM this scenario is really not
interesting.  If it did happen it would represent a bug in your
application design, no more and no less.  For instance, using the same
sequence to feed IDs for multiple tables would be a bug if you had a
trigger that did an insert on one of them as a consequence of an insert
on another.

            regards, tom lane