Re: plpgsql function with update and seeing changed data from outside during run

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer
> <craig(at)postnewspapers(dot)com(dot)au> wrote:
>> (as far as I know) It's not possible for a function to see data committed by
>> other transactions since that function began executing, whether or not those
>> other transactions have committed.

> This is not correct. Yes, a snapshot is created, but that doesn't
> prevent you from seeing external changes. I have in fact many times
> relied on being able to block in a pl/pgsql loop and wait for a record
> to be set or something like that.

Note that the function needs to be declared VOLATILE for that to work.

But I believe the OP's question was the other way around: he wanted to
be able to see changes made by a function from elsewhere, before the
function completes. That's not possible, unless you resort to hacks
like using dblink to get the effect of autonomous subtransactions.

regards, tom lane