Re: my pl/pgsql functions return weird results

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