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

On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> On 9/06/2011 2:41 PM, Clemens Schwaighofer wrote:
>>
>> Hi,
>>
>> I have a plpgsql function where I read data from a table in a loop and
>> update data in a different table.
>>
>> Is it possible to see the updated data from a different access during
>> the run of this function? Or is this impossible because the function
>> is a "transaction" and no data change is visible outside until the
>> function is finished? I can see the changed data inside the function.
>
> (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. If you don't want to deal with mid
transaction changes, take out a lock or bump up to serializable.

merlin