Re: Postgresql - Currval Vs Session Pool

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.

Yours,
Laurenz Albe