Thread: Re: Query inside transaction

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? 


On 25/06/2004 14.54, Bruno Wolff III <bruno@wolff.to> wrote:
>On Fri, Jun 25, 2004 at 12:48:43 +0200,
>  Ago <ago@nmb.it> wrote:
>> OK, thanks Michal, I did not know this issue. I thought I should
>use LOCK table inside the transaction to pick up the correct id value
>from SELECT MAX(id) FROM e_catalog.
>
>It depends on what you want. Sequences should be used to produce
>unique
>values. If you want to get consecutively numbered rows then they
>shouldn't
>be used.
>
>>
>>
>> On 25/06/2004 12.38, Michal Táborský <michal@taborsky.cz> wrote:
>> >NMB Webmaster wrote:
>> >
>> >> But if someone else runs the same transaction in the same time
>
>> >what
>> > > value does "currval('sequence')" return? That one of the first
>> > > transaction or that one of the other transaction? Moreover,
>field
>> > > id is a unique primary key, it does not accept duplicates.
>> >
>> >That's the beauty of sequences. They are transaction-safe. Co
>
>> >"currval('sequence')" will always return the same value of the
>previous
>> >
>> >nextval call within that transaction, no matter how many other
>
>> >transactions picked the numbers in between.
>
>It is actually a bit stronger promise than that. Currval returns
>that last
>assigned value from the current session, which may span multiple
>transactions.
>
>
>

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.