my pl/pgsql functions return weird results

Is there a repository on the web somewhere of all the bugs found, versions
they are found against and what theire status is?

Hi Andy,

Thats the reason which techdocs.postgresql.org started for, but since it's
grown to encompass so many things, that section hasn't been updated in ages.
:-(

Perhaps its time to look at a better way of keeping it updated?

:-)

Regards and best wishes,

Justin Clift

On Thursday 21 February 2002 20:32, Andy Marden wrote:
> Is there a repository on the web somewhere of all the bugs found, versions
> they are found against and what theire status is?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

Hi!

My base contains a table objectid

create table objectid(
next bigint
);

INSERT INTO objectid values(1);

I do really need to use this table like if it was a sequence so I made
some pl/pgsql functions ( functions at the end of the mail ).
But when I run them in three psql at the same time the results are
weird... Here are the results :

psql1: 2 ( ok )
psql2: 1 ( instead of 3 )
psql3: 1 ( instead of 4 )

Scary...

and after that, a "select * from objectid;" returns 4 ( that's ok ).

I'm using pgsql 7.1.3 ( debian package 7.1.3-8 on a 2.4.17 kernel )

-----------------------------
----------- Functions
-----------------------------

drop table objectid;

create table objectid(
next bigint
);

INSERT INTO objectid values(1);

drop function next_objectid();

create function next_objectid() returns bigint as '
DECLARE
o RECORD;
a integer;
BEGIN
raise notice ''before lock'';
lock table objectid in access exclusive mode;
raise notice ''locked, before pause'';
a := pause(1000000);
raise notice ''pause finished, updating'';
update objectid set next=next+1;
select into o next from objectid;
raise notice ''returning'';
return o.next;
END;
' language 'plpgsql';

drop function pause(int);

create function pause(int) returns int as '
DECLARE
i integer;
j integer;
BEGIN
FOR i IN 1 .. $1 LOOP
j := i+1;
end loop;
return j;
END;
' language 'plpgsql';

Olivier Garcia <ogarcia(at)waidan(dot)com> writes:
> [ can't re-implement nextval() in plpgsql ]

The problem is that plpgsql doesn't do SetQuerySnapshot between
statements of a function, so although your different invocations are
forced to wait for each other by the LOCK statements, they cannot see
each other's effects on the objectid table. (Each one sees the others
as transactions that hadn't committed when it started, so it ignores
their effects per MVCC rules.)

Whether this is a bug, and if so what correct behavior ought to be,
has been the subject of some dispute for awhile --- try searching in
the pghackers archives for "CommandCounterIncrement" and
"SetQuerySnapshot". Nothing's been decided yet.

Although you could get the behavior you want by issuing the commands
separately (not bundled in a plpgsql function), I rather wonder why
you are going to all this effort. Why not just use a sequence?

regards, tom lane