Re: Postgresql - Currval Vs Session Pool

On Mon, Jun 10, 2013 at 11:24 AM, Gustavo Amarilla Santacruz <
gusamasan(at)gmail(dot)com> wrote:

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

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