Thread: ...

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
takesthe 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;

Thanks.

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
takesthe 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