Re: plpgsql function with update and seeing changed data from outside during run - Mailing list pgsql-general

From Clemens Schwaighofer
Subject Re: plpgsql function with update and seeing changed data from outside during run
Date
Msg-id BANLkTi=nDDFotQFne_TqE+xthGbBROo61Q@mail.gmail.com
Whole thread Raw
In response to Re: plpgsql function with update and seeing changed data from outside during run  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
2011/6/9 Tom Lane <tgl@sss.pgh.pa.us>:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer
>> <craig@postnewspapers.com.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.

Thanks, seems my only way would be to try dblink then.

--
★ Clemens 呉 Schwaighofer
★ IT Engineer/Web Producer/Planning
★ E-Graphics Communications SP Digital
★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN
★ Tel: +81-(0)3-3545-7706
★ Fax: +81-(0)3-3545-7343
★ http://www.e-gra.co.jp

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Юрий EGO
Date:
Subject: COPY and binary data
Next
From: Bruce Momjian
Date:
Subject: Re: WAL shipping replication server re-sync