Re: 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/