Message-ID: <003d01c4c59e$2e2d1e70$0403a8c0@fejleszt4>
From: =?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= <surrano@mailbox.hu>
To: <pgsql-bugs@postgresql.org>
References: <012601c4c292$6804b810$0403a8c0@fejleszt4>
	<26896.1099590388@sss.pgh.pa.us>
	<011201c4c33b$7bd722c0$0403a8c0@fejleszt4>
	<013b01c4c347$195212c0$0403a8c0@fejleszt4>
	<26614.1099670548@sss.pgh.pa.us>
Subject: Re: Killed backend won't rollback transaction?
Date: Mon, 8 Nov 2004 15:21:03 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: 7bit

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