pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedKilled backend won't rollback transaction?
3+ messages / 2 participants
[nested] [flat]
* Killed backend won't rollback transaction?
@ 2004-11-05 14:52 SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 1 reply; 3+ messages in thread
From: SZŰCS Gábor @ 2004-11-05 14:52 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Killed backend won't rollback transaction?
@ 2004-11-05 16:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-11-05 16:02 UTC (permalink / raw)
To: SZŰCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-bugs@postgresql.org
=?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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Killed backend won't rollback transaction?
@ 2004-11-08 14:21 SZŰCS Gábor <surrano@mailbox.hu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: SZŰCS Gábor @ 2004-11-08 14:21 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-08 14:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-05 14:52 Killed backend won't rollback transaction? SZŰCS Gábor <surrano@mailbox.hu>
2004-11-05 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-08 14:21 ` SZŰCS Gábor <surrano@mailbox.hu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox