Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
2011/6/9 Tom Lane : > Merlin Moncure writes: >> On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer >> 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
Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
Merlin Moncure writes: > On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
2011/6/9 Craig Ringer : > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
I can try this, but I have never done anything with plperl yet. 2011/6/9 pasman pasmański : > If you rewrite your function in plperlu , you can store data in shared memory. > > 2011/6/9, Clemens Schwaighofer : >> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
If you rewrite your function in plperlu , you can store data in shared memory. 2011/6/9, Clemens Schwaighofer : > 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
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
