On Mon, Jun 10, 2013 at 11:24 AM, Gustavo Amarilla Santacruz < gusama...@gmail.com> wrote:
> Thank you, Merlin Moncure. > > > On Mon, Jun 10, 2013 at 10:13 AM, Merlin Moncure <mmonc...@gmail.com>wrote: > >> On Sun, Jun 9, 2013 at 10:59 AM, Gustavo Amarilla Santacruz >> <gusama...@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 >> > > > > -- > ------------------------ > Gustavo Amarilla > > I tested the following function for a table; it works: CREATE OR REPLACE FUNCTION returning_test( p_name TEXT ) RETURNS INT AS $$ DECLARE v_code INT; BEGIN -- HEAD table definition: -- ====================== -- -- CREATE TABLE head( -- code SERIAL PRIMARY KEY , -- name TEXT UNIQUE NOT NULL -- ); -- INSERT INTO head( head_name ) VALUES( p_name ) RETURNING code INTO v_code; RETURN( v_code ); END; $$ LANGUAGE plpgsql; ------------------------ Gustavo Amarilla
