Re: plpgsql function with update and seeing changed data from outside during run

2011/6/9 Craig Ringer <craig(at)postnewspapers(dot)com(dot)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