Delayed result from another connection

From: SZŰCS Gábor <surrano(at)mailbox(dot)hu>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Delayed result from another connection
Date: 2004-11-04 17:19:13
Message-ID: 012601c4c292$6804b810$0403a8c0@fejleszt4
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-sql

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2004-11-04 17:46:28 Re: Delayed result from another connection
Previous Message Oliver Elphick 2004-11-04 13:49:47 Re: Bug in pgAdminIII or in pg 8 beta3 ?

Browse pgsql-sql by date

  From Date Subject
Next Message Sam Mason 2004-11-04 17:25:08 Re: Group by and aggregates
Previous Message Michael L. Hostbaek 2004-11-04 16:54:30 Group by and aggregates