Обсуждение: pg_cancel_backend() doesn't abort a transaction
Hi, list members
In PostgreSQL source code there is a commentary for StatementCancelHandler() function which says: "Query-cancel signal from postmaster: abort current transaction at soonest convenient time". I think that's not true when using a savepoint:
Session 1:
BEGIN; -- #1
UPDATE dev.test SET status = 'y' WHERE foo = 1; -- #2
SAVEPOINT my_savepoint; -- #3
UPDATE dev.test SET status = 'z' WHERE foo = 2; -- #6
ROLLBACK TO SAVEPOINT my_savepoint; -- #9
COMMIT; -- #10
UPDATE dev.test SET status = 'y' WHERE foo = 1; -- #2
SAVEPOINT my_savepoint; -- #3
UPDATE dev.test SET status = 'z' WHERE foo = 2; -- #6
ROLLBACK TO SAVEPOINT my_savepoint; -- #9
COMMIT; -- #10
Session 2:
BEGIN; -- #4
SELECT * FROM dev.test WHERE foo = 2 FOR UPDATE; -- #5
ROLLBACK; -- #8
SELECT * FROM dev.test WHERE foo = 2 FOR UPDATE; -- #5
ROLLBACK; -- #8
Session 3:
SELECT pg_cancel_backend(xxx); -- #7 , xxx is the pid of #6 query
The result is that status = 'y' WHERE foo = 1 , so Session 1 committed the transaction successfully. I am missing something or the commentary is vague?
P.S. It's not clear from the documentation whether pg_cancel_backend() aborts transaction or not.
With best regards, Andrei Zhidenkov.
On Wednesday, June 10, 2020, Andrei Zhidenkov <andrei.zhidenkov@n26.com> wrote:
The result is that status = 'y' WHERE foo = 1 , so Session 1 committed the transaction successfully. I am missing something or the commentary is vague?P.S. It's not clear from the documentation whether pg_cancel_backend() aborts transaction or not
User-facing docs seem reasonably clear: “ Cancel a backend's current query. “
It makes it so that current query fails, nothing more. If that query is part of a transaction it will typically cause subsequent statements to fail since the transaction is in an aborted state. However, if the transaction can cope with that statement failing, as your example does using savepoints, the transaction can continue onto normal completion.
From what you say the source code comments seem to be written for the common case, but “query-cancel” should/does impact just a single query and does not target the transaction directly.
It does not seem desirable that the containing transaction be affected directly nor surely does changing the behavior make sense at this point.
David J.
With that said, it looks like the commentary for StatementCancelHandler() is not accurate.
On 10. Jun 2020, at 17:09, David G. Johnston <david.g.johnston@gmail.com> wrote:On Wednesday, June 10, 2020, Andrei Zhidenkov <andrei.zhidenkov@n26.com> wrote:The result is that status = 'y' WHERE foo = 1 , so Session 1 committed the transaction successfully. I am missing something or the commentary is vague?P.S. It's not clear from the documentation whether pg_cancel_backend() aborts transaction or notUser-facing docs seem reasonably clear: “ Cancel a backend's current query. “It makes it so that current query fails, nothing more. If that query is part of a transaction it will typically cause subsequent statements to fail since the transaction is in an aborted state. However, if the transaction can cope with that statement failing, as your example does using savepoints, the transaction can continue onto normal completion.From what you say the source code comments seem to be written for the common case, but “query-cancel” should/does impact just a single query and does not target the transaction directly.It does not seem desirable that the containing transaction be affected directly nor surely does changing the behavior make sense at this point.David J.