Re: Query inside transaction

On Fri, Jun 25, 2004 at 15:30:55 +0200,
  Ago <ago@nmb.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
elseinsert 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.