PL/pgSQL: Inserted data not available when function returns

Question

I have an interesting problem that me and my collegue troubles for some time now.

I have a PL/pgSQL function in a PostgreSQL-8.3 (sorry for that old version, I can't change that) that does the following four things:

1. Get a new serial (ID) from a sequence
2. Insert a couple of records into a table with that serial
3. Send a notify signal that an insertion took place
4. Return the ID to the caller.

Simplified function:

CREATE OR REPLACE FUNCTION add_entry(_user_name text, _visible_attr integer[])
  RETURNS bigint AS
$BODY$
  DECLARE
    user_name text := '#' || $1;
    user_id bigint;
  BEGIN

    -- get the ID from the sequence
    SELECT nextval('my_sequence') INTO user_id;

    -- insert the name (and some other data not shown here) 5x
    FOR item IN 1..5
    LOOP
      INSERT INTO mytable
        (id,index,username,visible)
        VALUES (user_id,item,user_name,$2[item]);
    END LOOP;

    -- send notify that an insertion took place
    notify my_notify;

    RETURN user_id;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

So, my collegue called this function from his application. He gets the returned ID and uses another thread (DB pooling) in his application to call a function which shall return the data previously inserted with that ID. However, this doesn't work the first time. Only with the second request he is able to select the data. It seems as if that INSERT isn't finished while the function already returns?!

We checked mutiple times, the data will be inserted into the table correctly but somehow it is not available as fast as the return value (the ID from the sequence) is available! Why is that so?

Update: wrong assumption

I examined further and reduced the example to a simple query which really shows the problem:

select * from mytable where id = (select add_entry('MyTestUser'));

This query returns no rows. But if I do that in two seperate steps I can select the data which I inserted with the add_entry function.

I have no clue what I'm doing wrong or how I could speed up the insertion...

Answer 1

From the 8.3 manual

In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run

Since the update is done in the select itself the inserted row will not be seen.

http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html

Change the function to return setof mytable. It can be plain SQL. To change the return type the function must be dropped first

drop function add_entry(text);

create or replace function add_entry (_user_name text, _visible_attr integer[])
returns setof mytable as $body$

    notify my_notify;
    with ins as (
        insert into mytable (id, index, username, visible)
        select user_id, item, '#' || $1, $2[item]
        from
            generate_series(1, 5) g(item)
            cross join
            (values (nextval('my_sequence'))) s(user_id)
        returning *
    )
    select * from ins;

$body$ language sql volatile;

The notification must happen before anything is returned from the function. It is not a problem as if the insert fails the transaction rolls back including the notification. Call it like

select * from add_entry('MyTestUser');

The select will not see the modified table but the returned mytable rows.

If it is necessary for the function to be plpgsql then use return query

create or replace function add_entry (_user_name text, _visible_attr integer[])
returns setof mytable as $body$
begin
    notify my_notify;
    return query
        insert into mytable (id, index, username, visible)
        select user_id, item, '#' || $1, $2[item]
        from
            generate_series(1, 5) g(item)
            cross join
            (values (nextval('my_sequence'))) s(user_id)
        returning *
    ;
end;
$body$ language plpgsql volatile;