Обсуждение: plpgsql function with update and seeing changed data from outside during run

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.

the function looks something like this

create or replace function insert_log(i_log_id INT, i_queue_id INT)
returns "varchar"
as $$
  declare
    [... here are record, etc declarations]
   begin
   for myrec in select * from queue where queue_id = i_queue_id;
   loop
      insert into log_sub () values ();
      update log set rows = [internal row count] where log_id = i_log_id;
   end loop
end; $$ language plpgsql;
--
★ 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

If you rewrite your function in plperlu , you can store data in shared memory.

2011/6/9, Clemens Schwaighofer <clemens.schwaighofer@e-graphics.com>:
> 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.
>
> the function looks something like this
>
> create or replace function insert_log(i_log_id INT, i_queue_id INT)
> returns "varchar"
> as $$
>   declare
>     [... here are record, etc declarations]
>    begin
>    for myrec in select * from queue where queue_id = i_queue_id;
>    loop
>       insert into log_sub () values ();
>       update log set rows = [internal row count] where log_id = i_log_id;
>    end loop
> end; $$ language plpgsql;
> --
> ★ 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
>


--
------------
pasman

I can try this, but I have never done anything with plperl yet.

2011/6/9 pasman pasmański <pasman.p@gmail.com>:
> If you rewrite your function in plperlu , you can store data in shared memory.
>
> 2011/6/9, Clemens Schwaighofer <clemens.schwaighofer@e-graphics.com>:
>> 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.
>>
>> the function looks something like this
>>
>> create or replace function insert_log(i_log_id INT, i_queue_id INT)
>> returns "varchar"
>> as $$
>>   declare
>>     [... here are record, etc declarations]
>>    begin
>>    for myrec in select * from queue where queue_id = i_queue_id;
>>    loop
>>       insert into log_sub () values ();
>>       update log set rows = [internal row count] where log_id = i_log_id;
>>    end loop
>> end; $$ language plpgsql;
>> --
>> ★ 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
>>
>
>
> --
> ------------
> pasman
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
★ 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

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.

A function *can* see changes it or functions it has called have made
within its own transaction.

The reason for this is that PL/PgSQL functions, whether they are in
READ_COMMITTED or SERIALIZABLE mode, do not get an updated snapshot at
any point while they are running. The database system takes a snapshot
of the state of the database when the function starts running, and
that's all the function can see until it's finished.

A *transaction* can see data that has been committed by other
transactions since it started if it is in READ_COMMITTED mode. Each
individual statement run in the transaction cannot; it gets a snapshot
when the statement starts and keeps it until the statement ends.
PL/PgSQL functions can only be called from SQL statements, and are
subject to that rule.

If you want to see updates made since your function started, you'll need
to either use dblink to have the function control a second connection to
the database and do all the work via that, or you'll need to keep your
function outside the database in a program that connects to PostgreSQL.

What is the goal of this function? I don't see the point of it as
written, but perhaps it's been simplified to the point where it's no
longer meaingful or useful.

> create or replace function insert_log(i_log_id INT, i_queue_id INT)
> returns "varchar"
> as $$
>    declare
>      [... here are record, etc declarations]
>     begin
>     for myrec in select * from queue where queue_id = i_queue_id;
>     loop
>        insert into log_sub () values ();
>        update log set rows = [internal row count] where log_id = i_log_id;
>     end loop
> end; $$ language plpgsql;


--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

2011/6/9 Craig Ringer <craig@postnewspapers.com.au>:
> 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.
>
> A function *can* see changes it or functions it has called have made within
> its own transaction.
>
> The reason for this is that PL/PgSQL functions, whether they are in
> READ_COMMITTED or SERIALIZABLE mode, do not get an updated snapshot at any
> point while they are running. The database system takes a snapshot of the
> state of the database when the function starts running, and that's all the
> function can see until it's finished.
>
> A *transaction* can see data that has been committed by other transactions
> since it started if it is in READ_COMMITTED mode. Each individual statement
> run in the transaction cannot; it gets a snapshot when the statement starts
> and keeps it until the statement ends. PL/PgSQL functions can only be called
> from SQL statements, and are subject to that rule.
>
> If you want to see updates made since your function started, you'll need to
> either use dblink to have the function control a second connection to the
> database and do all the work via that, or you'll need to keep your function
> outside the database in a program that connects to PostgreSQL.
>
> What is the goal of this function? I don't see the point of it as written,
> but perhaps it's been simplified to the point where it's no longer meaingful
> or useful.

The sample is just over simplified.

What I do is I read data from one table and copy it into a log table.
As the data set can be quite big (about 200.000 rows) I wanted to add
a process progress that can be viewed in the web interface.

So there is one script that runs this function and then a web
interface where I wanted to have the progress.

>
>> create or replace function insert_log(i_log_id INT, i_queue_id INT)
>> returns "varchar"
>> as $$
>>   declare
>>     [... here are record, etc declarations]
>>    begin
>>    for myrec in select * from queue where queue_id = i_queue_id;
>>    loop
>>       insert into log_sub () values ();
>>       update log set rows = [internal row count] where log_id = i_log_id;
>>    end loop
>> end; $$ language plpgsql;
>
>
> --
> Craig Ringer
>
> Tech-related writing at http://soapyfrogs.blogspot.com/
>



--
★ 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

On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer
<craig@postnewspapers.com.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

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.

            regards, tom lane

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

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