Re: Query inside transaction

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.

On 25/06/2004 11.58, Michal Táborský <michal(at)taborsky(dot)cz> wrote:
>Ago wrote:
>
>> I have a PHP script that runs this query on Postgresql 7.2.3. Once
>it inserts the record in the e_catalog table it takes the id value
>and then inserts it in the e_catalog_cache table, the two tables
>must have the same rows and values.
>> I thought rhat inside a transaction block the subquery SELECT
>MAX(id) FROM e_catalog was safe from concurrent same transactions,
>that is the id value from SELECT MAX(id) FROM e_catalog was exactly
>that one inserted in the previous statement, but reading some threads
>in this mailing list I have some doubt now.
>> This is the query:
>>
>> 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;
>
>You want:
>BEGIN WORK;
>INSERT INTO e_catalog(id, name, descr) VALUES (nextval('sequence'),
> '$Name', '$Descr');
>INSERT INTO e_catalog_cache(id, name, descr) VALUES currval('sequence'),
> '$Name', '$Descr');
>COMMIT WORK;
>
>--
>Michal Taborsky
>http://www.taborsky.cz
>
>
>