Gustavo Amarilla Santacruz wrote: > In the PostgreSQL documentation I found "currval: Return the value most recently obtained by nextval > for this sequence in the current session ...." > > In other documentations (pgpool, for example), I found "Connection Pooling > pgpool-II saves connections to the PostgreSQL servers, and reuse them whenever a new connection with > the same properties (i.e. username, database, protocol version) comes in. It reduces connection > overhead, and improves system's overall throughput" > > Then, I have the following question: PostgreSQL differentiates between sessions created for the same > user? > > Background > ========== > - I, traditionalmente, have several user in a web application (user table, for example); but I use > only one postgresql-db-user to get connetions to database > > - If a CREATE (CRUD) operation uses a head-detail couple of tables, then in a transaction: data in > HEAD table is inserted; next, the value for the primary key is achieved from currval function; next > references to head table is inserted in detail table.
"currval" will return a different value or an error message if the query happens to use a different session than the one that you used for "nextval".
The best way to solve this is the INSERT ... RETURNING statement, like in INSERT INTO t VALUES (...) RETURNING id, which will return new value of the automatically generated column.