Re: Postgresql - Currval Vs Session Pool

[Gustavo Amarilla Santacruz <gusamasan@xxxxxxxxx>]

Thank you, Merlin Moncure.

On Mon, Jun 10, 2013 at 10:13 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:

On Sun, Jun 9, 2013 at 10:59 AM, Gustavo Amarilla Santacruz
<gusamasan@xxxxxxxxx> 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

--
------------------------
Gustavo Amarilla