Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group