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 | BANLkTimtKC6RGvdgJLeMza9Gj_JGTpP4vw@mail.gmail.com Whole thread Raw |
In response to | Re: plpgsql function with update and seeing changed data from outside during run (Craig Ringer <craig@postnewspapers.com.au>) |
List | pgsql-general |
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
pgsql-general by date: