Re: my pl/pgsql functions return weird results

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Olivier Garcia <ogarcia(at)waidan(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: my pl/pgsql functions return weird results
Date: 2002-02-22 15:10:53
Message-ID: 16817.1014390653@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2002-02-22 15:29:50 Re: Now trying Cygwin with normal download of PostgreSQL
Previous Message Nicolas Paymal 2002-02-22 13:38:53 CURRENT_TIMESTAMP not work correctly insinde a transaction.