On Mon, Jun 10, 2013 at 11:24 AM, Gustavo Amarilla Santacruz <gusamasan@gmail.com> wrote:
Thank you, Merlin Moncure.
On Mon, Jun 10, 2013 at 10:13 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
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
-- ------------------------ 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_codeINT;
BEGIN
-- HEAD table definition:
-- ======================
--
-- CREATE TABLE head(
-- codeSERIALPRIMARY KEY,
--nameTEXTUNIQUE NOT NULL
-- );
--
INSERT INTO head( head_name ) VALUES( p_name ) RETURNING code INTO v_code;