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 else insert a new row (and then a new id) in the meantime?

On 25/06/2004 14.54, Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
>On Fri, Jun 25, 2004 at 12:48:43 +0200,
> Ago <ago(at)nmb(dot)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(at)taborsky(dot)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.
>
>
>