Thread: 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
> ...."
>
> ....
>
> Then, I have the following question: PostgreSQL differentiates between
> sessions created for the same user?

I have little familiarity with using curval/nextval directly, and my pooler
is Java-based and basically provides a "connection checkout mechanism" which
is behaves differently than pgpool, but...

In short, yes, a single database user can have more than one single session
active concurrently.  The call to "currval" returns the (basically cached)
value that was last issued via a call to "nextval" inside the current
session.  The definition, behavior, and scope of a session will be noted in
the pooler's documentation.

Assuming transaction-level pooling then CONNECTION_01 and _02 calls to
"currval" will return different values that corresponding to whatever value
was acquired during the "nextval" call the happened during the insert into
the "head" table during the same transaction.

A pooler will, at some point, cause a connection's session to be reset.
When this happens depends on the pooler and its configuration.  While the
connection itself remains active all session-level state is discarded during
the reset so for all intents and purposes it is like a brand-new connection.
The only difference is that the underlying connection handler process is not
killed and the user is not required to login again - both of which are
time-intensive actions.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Postgresql-Currval-Vs-Session-Pool-tp5758517p5758522.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

On Sun, Jun 9, 2013 at 10:59 AM, Gustavo Amarilla Santacruz
<gusamasan@gmail.com> wrote:
> Hello, all.
>
> 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?

Connection pooling means you have to carefully consider using feature
of the database that is scoped to the session.  This includes
currval(), prepared statements, listen/notify, advisory locks, 3rd
party libraries that utilize backend private memory, etc.

For currval(), one solution is to only use those features
'in-transaction', and make sure your pooler is fully transaction aware
-- pgbouncer does this and I think (but I'm not sure) that pgpool does
as well.

Another solution is to stop using currval() and cache the value on the
client side.  postgres 8.2 RETURNING facilities this:

INSERT INTO foo (...) RETURNING foo_id;

This is a better way to deal with basis CRUD -- it also works for all
default values, not just sequences.  The only time I use currval() etc
any more is inside server side functions.

merlin