Re: Does INSERT inserts always at the end ?

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'), '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.

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(at)svana(dot)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.