Re: Query inside transaction - Mailing list pgsql-general

From Scott Marlowe
Subject Re: Query inside transaction
Date
Msg-id 1088653439.29619.12.camel@localhost.localdomain
Whole thread Raw
In response to Re: Query inside transaction  (NMB Webmaster <webmaster@nmb.it>)
List pgsql-general
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
>


pgsql-general by date:

Previous
From: Rajesh Kumar Mallah
Date:
Subject: Re: case for lock_timeout
Next
From: Bruno Wolff III
Date:
Subject: Re: backups