Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: Query inside transaction

Re: Query inside transaction

From
NMB Webmaster
Date:
But if someone else runs the same transaction in the same time what value does "currval('sequence')" return? That one
ofthe first transaction or that one of the other transaction? Moreover, field id is a unique primary key, it does not
acceptduplicates. 

On 25/06/2004 11.58, Michal Táborský <michal@taborsky.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
>
>
>

Re: Query inside transaction

From
"Scott Marlowe"
Date:
This subject has been discussed literally a thousand times before.  If
currval didn't work properly with parallel transactions I'm pretty sure
someone would have noticed and fixed it by now.

http://www.postgresql.org/docs/7.4/static/functions-sequence.html

explains all about sequences.

On Fri, 2004-06-25 at 04:27, NMB Webmaster wrote:
> But if someone else runs the same transaction in the same time what value does "currval('sequence')" return? That one
ofthe first transaction or that one of the other transaction? Moreover, field id is a unique primary key, it does not
acceptduplicates. 
>
> On 25/06/2004 11.58, Michal Táborský <michal@taborsky.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
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>