Thread: Killed backend won't rollback transaction?

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.

SZŰCS Gábor <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