Re: Query inside transaction

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Ago <ago(at)nmb(dot)it>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Query inside transaction
Date: 2004-06-25 15:46:11
Message-ID: 20040625154611.GA22710@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Jun 25, 2004 at 15:30:55 +0200,
Ago <ago(at)nmb(dot)it> wrote:
> Then what have I to do, in your opinion, if I execute this transaction :
> BEGIN WORK;
> INSERT INTO e_catalog(id, name, descr) VALUES (nextval('sequence'), '$Name', '$Descr');
>
> INSERT INTO e_catalog_cache(id, name, descr) VALUES ((SELECT MAX(id) FROM e_catalog), '$Name', '$Descr');
>
> COMMIT WORK;
>
> and I want that the second statement takes the same id value of the first one in safe mode, that is, even if someone else insert a new row (and then a new id) in the meantime?

The second insert should use currval. My comment about currval working within
a session said that even if the two inserts weren't in the same transaction,
but were in the same session, you could still use currval. (Note that you
still want them to be in the same transaction for other reasons.)
My other comment was that you shouldn't count on id to be numbered from
1 to however many records you currently have in the table. Under simple
conditions (e.g. no rollbacks, no deleted rows, no grabbing sequence values
in blocks greater than 1) that may be the case, but you shouldn't count on
that.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Otto Blomqvist 2004-06-25 15:47:34 Speed of pg_dump -l -s (List Schema) Variations
Previous Message Ago 2004-06-25 13:30:55 Re: Query inside transaction