Does INSERT inserts always at the end ?

[Florence HENRY]

Hello,

well, almost everything is in the subject !

I have to fill 2 tables (more complicated than in the example !):

CREATE TABLE A (
id serial primary key,
foo text);

CREATE TABLE B (
id serial references A,
bar text);

I fill A with :
INSERT into A VALUES (DEFAULT, "toto");

Then I need to retreive the "A.id" that was given to A, in order to give it
to B.id. If I was doing this by hand, it would have been quite easy, but I'm
doing this with a script.

So, if I make a SELECT id from A; and take the last row, will it *always*
be the row that I've just inserted.

Thanks for any tip

--
Florence HENRY

[Christopher Browne]

A long time ago, in a galaxy far, far away, fl************@obspm.fr (Florence HENRY) wrote:

well, almost everything is in the subject !

I have to fill 2 tables (more complicated than in the example !):

CREATE TABLE A (
id serial primary key,
foo text);

CREATE TABLE B (
id serial references A,
bar text);

I fill A with :
INSERT into A VALUES (DEFAULT, "toto");

Then I need to retreive the "A.id" that was given to A, in order to give it
to B.id. If I was doing this by hand, it would have been quite easy, but I'm
doing this with a script.

So, if I make a SELECT id from A; and take the last row, will it *always*
be the row that I've just inserted.

This won't happen "implicitly."

tutorial=# CREATE TABLE A (id serial primary key,foo text);
NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for "serial" column "a.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
tutorial=# drop table b;
DROP TABLE
tutorial=# CREATE TABLE B (id serial references A,bar text);
NOTICE: CREATE TABLE will create implicit sequence "b_id_seq" for "serial" column "b.id"
CREATE TABLE

I can suggest a couple of ways you might do this:

1. So long as the inserts take place within the scope of the same
transaction on the same connection, it would be safe to create
the B entry via currval for the sequence.

tutorial=# begin;
BEGIN
tutorial=# insert into a (foo) values ('toto');
INSERT 2587831 1
tutorial=# insert into b (id, bar) values (currval('a_id_seq'), 'yellow brick road');
INSERT 2587832 1
tutorial=# commit;
COMMIT

Note that if you don't enclose it in BEGIN/COMMIT, the insert into b
could pick up on changes from other concurrent sessions.

2. You might create a stored procedure that creates both entries,
using currval() behind your back.

[assuming suitably-created funciton...

select make_foo_bar ('toto', 'yellow brick road');

This won't work so well if there are to be multiple associations; if
that be the case, you'd want to have an explicit external primary key,
and do something like:

select create_a ('toto', 'key-for-toto');
select link_b_to_a ('key-for-toto', 'yellow brick road');
select link_b_to_a ('key-for-toto', 'click, click');
select link_b_to_a ('key-for-toto', 'ruby shoes');

There's no magic there; the stored procedure link_b_to_a() would look
up the ID number for 'key-for-toto' in table A.
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://cbbrowne.com/info/spiritual.html
Rules of the Evil Overlord #54. "I will not strike a bargain with a
demonic being then attempt to double-cross it simply because I feel
like being contrary." <http://www.eviloverlord.com/>

[Martijn van Oosterhout]

On Wed, May 19, 2004 at 12:41:12PM -0400, Christopher Browne wrote:

This won't happen "implicitly."

tutorial=# CREATE TABLE A (id serial primary key,foo text);
NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for "serial" column "a.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
tutorial=# drop table b;
DROP TABLE
tutorial=# CREATE TABLE B (id serial references A,bar text);
NOTICE: CREATE TABLE will create implicit sequence "b_id_seq" for "serial" column "b.id"
CREATE TABLE

I can suggest a couple of ways you might do this:

1. So long as the inserts take place within the scope of the same
transaction on the same connection, it would be safe to create
the B entry via currval for the sequence.
WRONG! As long as they're in the same session, it will work. The
transaction has nothing to do with it.
tutorial=# begin;
BEGIN
tutorial=# insert into a (foo) values ('toto');
INSERT 2587831 1
tutorial=# insert into b (id, bar) values (currval('a_id_seq'), 'yellowbrick road');
INSERT 2587832 1
tutorial=# commit;
COMMIT

Note that if you don't enclose it in BEGIN/COMMIT, the insert into b
could pick up on changes from other concurrent sessions.
Nope. Even without the BEGIN/COMMIT and three hours between the two
statements, it will still work. All that matters is that they're in the
same session. Check the documentation.
--
Martijn van Oosterhout <kl*****@svana.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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAq7zEY5Twig3Ge+YRAoqPAJ9+duWf1ocYfxUuh1Tbk4 rVdCAo0gCgpzqV
jAo6/jAe3zbIt0GaiPKHhkc=
=JS7I
-----END PGP SIGNATURE-----

[Jim Seymour]

fl************@obspm.fr (Florence HENRY) wrote:

Hello,

well, almost everything is in the subject !

I have to fill 2 tables (more complicated than in the example !):

CREATE TABLE A (
id serial primary key,
foo text);

CREATE TABLE B (
id serial references A,
bar text);

I fill A with :
INSERT into A VALUES (DEFAULT, "toto");

Then I need to retreive the "A.id" that was given to A, in order to give it
to B.id. If I was doing this by hand, it would have been quite easy, but I'm
doing this with a script.
Define what you mean by "with a script." If you've a db handle open
with, say, Perl's DBI, you could simply do a select on currval() for
the sequence and get it. This is immune to other transactions. But
if, by "script" you mean, say, from a shell script, where you're
feeding commands to psql from stdin or some-such, well... I suppose you
could "echo 'mumble; select currval(blurfl)' |psql" and capture it.
(Caveat: I haven't tried this. I'm just guessing.)

So, if I make a SELECT id from A; and take the last row, will it *always*
be the row that I've just inserted.

I was told just a few days ago to always regard data in a table as
"unordered." Going by that philosophy: No, you cannot. You
*certainly* cannot if more than one session/task is operating on the
table.

Jim

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Florence HENRY]

js******@LinxNet.com (Jim Seymour) wrote :

Define what you mean by "with a script." If you've a db handle open
with, say, Perl's DBI, you could simply do a select on currval() for
the sequence and get it. This is immune to other transactions.

Indeed, I'm using Perl's DBI. currval does exactly what I want. Thanks to
all who pointed it to me.

Before I could read your messages, I made a workaround that worked nice also,
but could you tell me if it would always work ?

It uses the "oid" number. I've read that it used to be unique, but within
the same session, I can assume that it is always growing, can't I ?

INSERT into A VALUES (DEFAULT, "toto");

INSERT into B VALUES (
(SELECT id FROM A WHERE oid = SELECT ( max(oid) from A)),
"toto");

--
Florence HENRY
florence point henry arobasse obspm point fr

[Martijn van Oosterhout]

On Tue, May 25, 2004 at 01:15:14AM -0700, Florence HENRY wrote:

Indeed, I'm using Perl's DBI. currval does exactly what I want. Thanks to
all who pointed it to me.
That's the way it should be done.
Before I could read your messages, I made a workaround that worked nice also,
but could you tell me if it would always work ?

It uses the "oid" number. I've read that it used to be unique, but within
the same session, I can assume that it is always growing, can't I ?
NO! The OID will wrap eventually, it is not guarenteed to be unique.
And you can now make tables without OIDs.
--
Martijn van Oosterhout <kl*****@svana.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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAu6wLY5Twig3Ge+YRAj8XAKCWgv7KsTrhbc2MhDNwcD xHUis1GgCgjUso
xVLDoUdheUkUG5YJv4QIS/4=
=Olr8
-----END PGP SIGNATURE-----