Does INSERT inserts always at the end ?

**Christopher Browne** · Nov 23 '05, 12:52 AM

Re: Does INSERT inserts always at the end ?

A long time ago, in a galaxy far, far away, florence.henry@ obspm.fr (Florence HENRY) wrote:[color=blue]
> 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.[/color]

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.enworb bc" "@" "enworbbc") )

404 Not Found

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.eviloverlor d.com/>

**Martijn van Oosterhout** · Nov 23 '05, 12:53 AM

Re: Does INSERT inserts always at the end ?

On Wed, May 19, 2004 at 12:41:12PM -0400, Christopher Browne wrote:[color=blue]
> 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.[/color]

WRONG! As long as they're in the same session, it will work. The
transaction has nothing to do with it.
[color=blue]
> 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.[/color]

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 <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> 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.[/color]

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

iD8DBQFAq7zEY5T wig3Ge+YRAoqPAJ 9+duWf1ocYfxUuh 1Tbk4rVdCAo0gCg pzqV
jAo6/jAe3zbIt0GaiPKH hkc=
=JS7I
-----END PGP SIGNATURE-----

**Jim Seymour** · Nov 23 '05, 12:54 AM

Re: Does INSERT inserts always at the end ?

florence.henry@ obspm.fr (Florence HENRY) wrote:[color=blue]
>
> 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.[/color]

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.)
[color=blue]
>
> 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.[/color]

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?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Florence HENRY** · Nov 23 '05, 12:55 AM

Re: Does INSERT inserts always at the end ?

jseymour@LinxNe t.com (Jim Seymour) wrote :
[color=blue]
> 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.[/color]

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** · Nov 23 '05, 01:01 AM

Re: Does INSERT inserts always at the end ?

On Tue, May 25, 2004 at 01:15:14AM -0700, Florence HENRY wrote:[color=blue]
> Indeed, I'm using Perl's DBI. currval does exactly what I want. Thanks to
> all who pointed it to me.[/color]

That's the way it should be done.
[color=blue]
> 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 ?[/color]

NO! The OID will wrap eventually, it is not guarenteed to be unique.
And you can now make tables without OIDs.
--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> 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.[/color]

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

iD8DBQFAu6wLY5T wig3Ge+YRAj8XAK CWgv7KsTrhbc2Mh DNwcDxHUis1GgCg jUso
xVLDoUdheUkUG5Y Jv4QIS/4=
=Olr8
-----END PGP SIGNATURE-----