Re: Does INSERT inserts always at the end ? - Mailing list pgsql-general

From Martijn van Oosterhout
Subject Re: Does INSERT inserts always at the end ?
Date
Msg-id 20040519200004.GA25725@svana.org
Whole thread Raw
In response to Re: Does INSERT inserts always at the end ?  (Christopher Browne <cbbrowne@acm.org>)
List pgsql-general
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@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.

Attachment

pgsql-general by date:

Previous
From: "Sam Masiello"
Date:
Subject: Re: Dblink question [SOLVED]
Next
From: Tom Lane
Date:
Subject: Re: A problem with the IN clause