2011/6/9 Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
