Re: Postgresql - Currval Vs Session Pool

Thank you, Merlin Moncure.

On Mon, Jun 10, 2013 at 10:13 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Sun, Jun 9, 2013 at 10:59 AM, Gustavo Amarilla Santacruz
> <gusamasan(at)gmail(dot)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
>

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