Delayed result from another connection
Dear Gurus,
I have a strange scenario that doesn't seem to work flawlessly. I think I
can produce a full working example if needed.
We use postgresql 7.4.5
It's something like this:
%----------------------- cut here -----------------------%
CREATE TABLE php_retval(id serial PRIMARY KEY, retval varchar);
CREATE FUNCTION php_run(int, varchar) RETURNS varchar AS '
declare
seq ALIAS FOR $1;
php ALIAS FOR $2;
ret varchar;
begin
perform php_run_c(php, seq);
ret := retval from php_retval where id = seq;
return ret;
end;'
LANGUAGE 'plpgsql' VOLATILE STRICT;
%----------------------- cut here -----------------------%
Now, this calls a c (SPI) function that calls system() to execute "php -q"
The php connects to the database and updates retval where id=seq.
It seems to be OK, but the function returns the value of php_retval.retval
_before_ the call. However, the php does the update. If I repeat the
function call in the transaction, the new result is returned:
%----------------------- cut here -----------------------%
UPDATE php_retval SET retval='nothing' WHERE id=1;
BEGIN;
SELECT php_run(1, 'test.php');
--> 'nothing'
SELECT php_run(1, 'test.php');
--> '3', the right value
END;
%----------------------- cut here -----------------------%
I thought it's something about "35.2 Visibility of Data Changes", but that's
only for triggers, isn't it? Also, the rule "query sees results of any
previously started queries" seems to be invaded: the perform runs right
before the query for retval.
Any ideas, explanations, clarifications, points to earlier discussions, rtfm
etc?
TIA,
G.
%----------------------- cut here -----------------------%
\end
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= <surrano@mailbox.hu> writes:
The php connects to the database and updates retval where id=seq.
It seems to be OK, but the function returns the value of php_retval.retval
_before_ the call.
Yes, because your transaction is working with a database snapshot that
predates the other transaction run by the separate PHP connection.
I thought it's something about "35.2 Visibility of Data Changes", but that's
only for triggers, isn't it?
Nope.
Also, the rule "query sees results of any
previously started queries" seems to be invaded: the perform runs right
before the query for retval.
Prior to PG 8.0, new snapshots are not taken between commands of a function,
even in READ COMMITTED mode. You could get the behavior you want by
issuing separate interactive commands instead of wrapping the sequence
in a function.
This has been a sore spot for a long time, but we didn't get consensus
about changing it till recently ...
regards, tom lane
Dear Tom,
Thanks for the explanation, it's more than enough. Some more questions if
you don't mind:
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Thursday, November 04, 2004 6:46 PM
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= <surrano@mailbox.hu> writes:
The php connects to the database and updates retval where id=seq.
It seems to be OK, but the function returns the value of
php_retval.retval
_before_ the call.
Yes, because your transaction is working with a database snapshot that
predates the other transaction run by the separate PHP connection.
Yes, but the snapshot is not constant during a transaction, right? (as my
example showed this clearly) ...
Prior to PG 8.0, new snapshots are not taken between commands of a
function,
even in READ COMMITTED mode. You could get the behavior you want by
issuing separate interactive commands instead of wrapping the sequence
in a function.
... So if I understood you correctly, it's the function call that sees the
old snapshot, and the transaction is more flexible in this issue. right?
I thought it's something about "35.2 Visibility of Data Changes", but
that's
only for triggers, isn't it?
Nope.
I felt like it's not only for triggers but interpreted the doc as it'd be
only for triggers... Having a closer look, discovered that there is another
section with this title: "41.4 Visibility of Data Changes" ;)
This has been a sore spot for a long time, but we didn't get consensus
about changing it till recently ...
I assume there is no point in begging for a backport... ;)
Thanks again,
G.
%----------------------- cut here -----------------------%
\end
Dear Gurus,
Version: PostgreSQL 7.4.3 on Linux.
I have two transactions trying to insert the same value in a unique index
(pkey).
Tested and if I simpy try two plain sql backends, everything's ok. However,
mine is a silly scenario.
Obviously, the first one locks something (I assume the tuple) since the
other one goes waiting.
If I end this transaction (either by COMMIT or ROLLBACK), the other
transaction resumes (either by pkey violation error or successful insert).
BUT if I kill the first transaction (with standard (15 Terminate) signal),
the second one stucks in "waiting".
I know it's not recommended to "kill -9 the postmaster", but it's plain
"kill", and I couldn't circumvent it.
What happend is that I
* called a plpgsql function that inserted in this pkey, then
* called a C function that
* called system() that
* called php that inserted the same value via another connection, sentenced
to waiting.
The process hung, with no cancellation possible (the backend wrote "Cancel
request sent" but nothing happened -- system() ignores interrupts).
I fixed the lock, but I wondered if it's considered a bug.
TIA,
G.
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= <surrano@mailbox.hu> writes:
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Yes, because your transaction is working with a database snapshot that
predates the other transaction run by the separate PHP connection.
Yes, but the snapshot is not constant during a transaction, right?
In pre-8.0 releases, the snapshot only advances between interactive
commands, not between commands of a function; so you can't see the
results of other transactions that commit after the function starts.
regards, tom lane
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= <surrano@mailbox.hu> writes:
BUT if I kill the first transaction (with standard (15 Terminate) signal),
the second one stucks in "waiting".
SIGTERM on individual backends is not recommended or supported.
FWIW, though, I could not duplicate this bug report. Are you sure you
know what the second one was really waiting on?
regards, tom lane
Dear Tom,
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Friday, November 05, 2004 5:02 PM
SIGTERM on individual backends is not recommended or supported.
That explains this. Keeping this policy in mind, I wouldn't qualify this as
a bug after all.
FWIW, though, I could not duplicate this bug report. Are you sure you
know what the second one was really waiting on?
Yeah I'm quite sure. See sample below. Ask for a working example if you
wish.
The main concept is that a system() call from a C function causes another
connection to be opened, and the original connection, after locking the
tuple, waits for the other connection (that is started implicitly by the C
function) that in turn waits for the locked tuple to be unlocked. Thus, the
transactions are "nested" even though they are on a different connection.
G.
%----------------------- cut here -----------------------%
-- table:
CREATE TABLE test_id (
id serial NOT NULL PRIMARY KEY,
retval varchar
);
-- there is a "before insert" trigger that allows retval of existing key
-- to be updated instead of inserted if retval is null (see PHP script
below)
-- plpgsql function:
CREATE PROCEDURE test_pl (int) RETURNS varchar AS '
declare
rv varchar;
begin
-- this can be delayed till the PHP call, but keeping here
-- helps reproduce the problem.
INSERT INTO test_id (id) VALUES ($1);
rv := test_c ($1);
rv := retval FROM test_id WHERE id = $1;
return rv;
end;
-- C function:
text *test_c (int)
{
...
system ("php -q test.php");
...
}
-- PHP script "test.php":
<?
...
// this gets converted to UPDATE but keeping as is
// helps reproduce the problem.
pg_exec($conn,
"INSERT INTO test_id (id, retval) VALUES (123, 3*123)");
...
?>
\end