Re: Postgresql - Currval Vs Session Pool

Thank you very much, Laurenz Albe.

On Mon, Jun 10, 2013 at 9:21 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> 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
>

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