Thread: SERIAL values
Hi all, how can I get the current values from SERIAL types after an INSERT using libpq? Thanks, kostis.
SELECT currval('Table_SerialField_SEQ');
Note: This selects the last value of the sequence defined by your SERIAL
sequence, not necessarily the value in your INSERT. For example:
1. User A INSERTs.
2. User B INSERTs.
3. User A performs SELECT currval, which returns the value for User B's
INSERT.
If this could be a potential problem, I recommend peforming a:
SELECT nextval('Table_SerialField_SEQ');
*before* you perform the INSERT, and explicitly using that value.
Greg
----- Original Message -----
From: "Kostis Mentzelos" <mentzelos@ematic.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, March 01, 2001 5:53 PM
Subject: SERIAL values
> Hi all,
>
> how can I get the current values from SERIAL types after an INSERT using
>
> libpq?
>
>
> Thanks,
> kostis.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
This is an incorrect statement (as pointed out in the FAQ) 4.16.3) Don't currval() and nextval() lead to a race condition with other users? No. This is handled by the backends. More info: http://www.postgresql.org/users-lounge/docs/7.0/user/sql-createsequence.htm When calling currval, you recieve the last value used by *your backend* (regardless of what others have done). Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Fri, 2 Mar 2001, Gregory Wood wrote: > SELECT currval('Table_SerialField_SEQ'); > > Note: This selects the last value of the sequence defined by your SERIAL > sequence, not necessarily the value in your INSERT. For example: > > 1. User A INSERTs. > 2. User B INSERTs. > 3. User A performs SELECT currval, which returns the value for User B's > INSERT. > > If this could be a potential problem, I recommend peforming a: > > SELECT nextval('Table_SerialField_SEQ'); > > *before* you perform the INSERT, and explicitly using that value. > > Greg > > ----- Original Message ----- > From: "Kostis Mentzelos" <mentzelos@ematic.com> > To: <pgsql-general@postgresql.org> > Sent: Thursday, March 01, 2001 5:53 PM > Subject: SERIAL values > > > > Hi all, > > > > how can I get the current values from SERIAL types after an INSERT using > > > > libpq? > > > > > > Thanks, > > kostis. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Mensaje citado por: Kostis Mentzelos <mentzelos@ematic.com>: > Hi all, > > how can I get the current values from SERIAL types after an INSERT > using > > libpq? Don't know abou libpq, but the SERIAL type is managed by a sequence, and you can get the current value of the sequence (can't recall, but I guess it's current). Saludos... :-) P.D.: If I'm making an insert and after it I want to insert another value related with the serial inserted in the previous query to another table, which aproche is better: 1) Get the oid of the last insert and use it to find the INT in the serial column. 2) Get the current value in the sequence (I guess this is very bad, cause while the first insert is finishing, another client can insert information on that column, changing the current value on the sequence). 3) another solution? System Administration: It's a dirty job, but someone told I had to do it. ----------------------------------------------------------------- Martín Marqués email: martin@math.unl.edu.ar Santa Fe - Argentina http://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -----------------------------------------------------------------
"Martin A. Marques" wrote: > > Mensaje citado por: Kostis Mentzelos <mentzelos@ematic.com>: > > > Hi all, > > > > how can I get the current values from SERIAL types after an INSERT > > using > > > > libpq? > > Don't know abou libpq, but the SERIAL type is managed by a sequence, and you can > get the current value of the sequence (can't recall, but I guess it's current). > > Saludos... :-) > > P.D.: If I'm making an insert and after it I want to insert another value > related with the serial inserted in the previous query to another table, which > aproche is better: > > 1) Get the oid of the last insert and use it to find the INT in the serial > column. Yes, but if the table is large this may require an extra index (for the oid field) to get reasonable performance... The next solution is the good one: > 2) Get the current value in the sequence (I guess this is very bad, cause while > the first insert is finishing, another client can insert information on that > column, changing the current value on the sequence). Bad guess... The values of the sequences are managed by the postmaster so that they are 'session safe'. The price to pay (and it should be taken into account) is that the values of the secuence are not necessarily consecutive, as seen from one session. Also, that rollbacks can lead to holes in the secuence. This shouldn't be a problem, if (as is usually the case) the sequence is to be used as a unique identifier, not as a 'secuence' proper... Saludos Hernán González Buenos Aires, Argentina > 3) another solution? > > System Administration: It's a dirty job, > but someone told I had to do it. > ----------------------------------------------------------------- > Martín Marqués email: martin@math.unl.edu.ar > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > Administrador de sistemas en math.unl.edu.ar > ----------------------------------------------------------------- >