MIME-Version: 1.0
From: Daniil Davydov <3danissimo@gmail.com>
Date: Sat, 23 May 2026 20:29:09 +0700
Message-ID: 
 <CAJDiXggXjXhzdYg4KNyrn=tq6iWESG79te+RsGJhaq9pzxqCyg@mail.gmail.com>
Subject: ERROR during COMMIT PREPARED can leave orphaned locks
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000000da19e06527c2233"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJDiXggXjXhzdYg4KNyrn%3Dtq6iWESG79te%2BRsGJhaq9pzxqCyg%40mail.gmail.com>
Precedence: bulk

--0000000000000da19e06527c2233
Content-Type: text/plain; charset="UTF-8"

Hi,

When finishing the prepared transaction, we are marking it as "not valid" after
the WAL record has been written. This allows us to be sure that even if we fail
later, no one will be able to commit/rollback this transaction again. Here is
the code that implements it :

```
/*
 * In case we fail while running the callbacks, mark the gxact invalid so
 * no one else will try to commit/rollback, and so it will be recycled if
 * we fail after this point.  It is still locked by our backend so it
 * won't go away yet.
 *
 * (We assume it's safe to do this without taking TwoPhaseStateLock.)
 */
gxact->valid = false;
```

Comment says that we can fail while running the callbacks. But if this really
happens, then we are in trouble :
1) Twophase transaction we are working on is invalid, so AtAbort_Twophase will
simply delete it from shmem.
2) Not all on-commit/on-rollback callbacks are completed, so (for example)
transaction locks may remain.

Thus, if the COMMIT/ROLLBACK PREPARED command faces an ERROR, the user 1) will
not see the transaction in pg_prepared_xacts, but 2) will see its locks in
pg_locks. IIUC, the only way to release these locks is to restart the server.

I don't see any discussion about it on hackers, and I also don't see any sane
fix for this problem. So, I decided to share it with you. Looking forward to
your comments.

Please, see the attached test that demonstrates the problem. Actually,
reproduction is difficult. The only way to get an ERROR during callbacks
execution is to trigger OOM. Since it is hard to implement from perl tests, I
have added an injection point.

(The patch can be applied to the newest master).

--
Best regards,
Daniil Davydov

--0000000000000da19e06527c2233
Content-Type: text/x-patch; charset="US-ASCII";
	name="0001-Reproduce-error-with-twophase-commit.patch"
Content-Disposition: attachment;
	filename="0001-Reproduce-error-with-twophase-commit.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mpidwe150>
X-Attachment-Id: f_mpidwe150

RnJvbSAyY2JiZThjY2RlMDNiNDY5NzFiODFlMmYzZjBhZmZhOTliZGVkMGQxIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBEYW5paWwgRGF2aWRvdiA8ZC5kYXZ5ZG92QHBvc3RncmVzcHJv
LnJ1PgpEYXRlOiBTYXQsIDIzIE1heSAyMDI2IDIwOjI3OjMwICswNzAwClN1YmplY3Q6IFtQQVRD
SF0gUmVwcm9kdWNlIGVycm9yIHdpdGggdHdvcGhhc2UgY29tbWl0CgotLS0KIHNyYy9iYWNrZW5k
L2FjY2Vzcy90cmFuc2FtL3R3b3BoYXNlLmMgICAgICAgICB8ICAyICsKIHNyYy90ZXN0L3JlY292
ZXJ5L21lc29uLmJ1aWxkICAgICAgICAgICAgICAgICB8ICAxICsKIC4uLi9yZWNvdmVyeS90LzA1
M190d29waGFzZV9jb21taXRfZXJyb3IucGwgICB8IDc2ICsrKysrKysrKysrKysrKysrKysKIDMg
ZmlsZXMgY2hhbmdlZCwgNzkgaW5zZXJ0aW9ucygrKQogY3JlYXRlIG1vZGUgMTAwNjQ0IHNyYy90
ZXN0L3JlY292ZXJ5L3QvMDUzX3R3b3BoYXNlX2NvbW1pdF9lcnJvci5wbAoKZGlmZiAtLWdpdCBh
L3NyYy9iYWNrZW5kL2FjY2Vzcy90cmFuc2FtL3R3b3BoYXNlLmMgYi9zcmMvYmFja2VuZC9hY2Nl
c3MvdHJhbnNhbS90d29waGFzZS5jCmluZGV4IDEwMzVlOGIzZmM3Li4wN2E2MjhlMmE3OCAxMDA2
NDQKLS0tIGEvc3JjL2JhY2tlbmQvYWNjZXNzL3RyYW5zYW0vdHdvcGhhc2UuYworKysgYi9zcmMv
YmFja2VuZC9hY2Nlc3MvdHJhbnNhbS90d29waGFzZS5jCkBAIC0xNjUyLDYgKzE2NTIsOCBAQCBG
aW5pc2hQcmVwYXJlZFRyYW5zYWN0aW9uKGNvbnN0IGNoYXIgKmdpZCwgYm9vbCBpc0NvbW1pdCkK
IAkgKi8KIAlMV0xvY2tBY3F1aXJlKFR3b1BoYXNlU3RhdGVMb2NrLCBMV19FWENMVVNJVkUpOwog
CisJSU5KRUNUSU9OX1BPSU5UKCJ0d29waGFzZS1jYWxsYmFja3MtZXhlY3V0aW9uIiwgTlVMTCk7
CisKIAkvKiBBbmQgbm93IGRvIHRoZSBjYWxsYmFja3MgKi8KIAlpZiAoaXNDb21taXQpCiAJCVBy
b2Nlc3NSZWNvcmRzKGJ1ZnB0ciwgZnhpZCwgdHdvcGhhc2VfcG9zdGNvbW1pdF9jYWxsYmFja3Mp
OwpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVjb3ZlcnkvbWVzb24uYnVpbGQgYi9zcmMvdGVzdC9y
ZWNvdmVyeS9tZXNvbi5idWlsZAppbmRleCAzNmQ3ODk3MjBhMy4uNjMwNWNlODhiNjcgMTAwNjQ0
Ci0tLSBhL3NyYy90ZXN0L3JlY292ZXJ5L21lc29uLmJ1aWxkCisrKyBiL3NyYy90ZXN0L3JlY292
ZXJ5L21lc29uLmJ1aWxkCkBAIC02MSw2ICs2MSw3IEBAIHRlc3RzICs9IHsKICAgICAgICd0LzA1
MF9yZWRvX3NlZ21lbnRfbWlzc2luZy5wbCcsCiAgICAgICAndC8wNTFfZWZmZWN0aXZlX3dhbF9s
ZXZlbC5wbCcsCiAgICAgICAndC8wNTJfY2hlY2twb2ludF9zZWdtZW50X21pc3NpbmcucGwnLAor
ICAgICAgJ3QvMDUzX3R3b3BoYXNlX2NvbW1pdF9lcnJvci5wbCcsCiAgICAgXSwKICAgfSwKIH0K
ZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlY292ZXJ5L3QvMDUzX3R3b3BoYXNlX2NvbW1pdF9lcnJv
ci5wbCBiL3NyYy90ZXN0L3JlY292ZXJ5L3QvMDUzX3R3b3BoYXNlX2NvbW1pdF9lcnJvci5wbApu
ZXcgZmlsZSBtb2RlIDEwMDY0NAppbmRleCAwMDAwMDAwMDAwMC4uOTYwMDZjNmRkMmQKLS0tIC9k
ZXYvbnVsbAorKysgYi9zcmMvdGVzdC9yZWNvdmVyeS90LzA1M190d29waGFzZV9jb21taXRfZXJy
b3IucGwKQEAgLTAsMCArMSw3NiBAQAorIyBDb3B5cmlnaHQgKGMpIDIwMjYsIFBvc3RncmVTUUwg
R2xvYmFsIERldmVsb3BtZW50IEdyb3VwCisKKyMgVGVzdCBmYWlsdXJlIHdoZW4gQ09NTUlUIFBS
RVBBUkVEIGdldHMgYW4gRVJST1IgYWZ0ZXIgaXRzIGNvbW1pdCBXQUwgcmVjb3JkCisjIGhhcyBi
ZWVuIGVtaXR0ZWQsIGJ1dCBiZWZvcmUgdHdvLXBoYXNlIGNsZWFudXAgY2FsbGJhY2tzIGhhdmUg
cmVsZWFzZWQgbG9ja3MuCisKK3VzZSBzdHJpY3Q7Cit1c2Ugd2FybmluZ3MgRkFUQUwgPT4gJ2Fs
bCc7CisKK3VzZSBQb3N0Z3JlU1FMOjpUZXN0OjpDbHVzdGVyOwordXNlIFBvc3RncmVTUUw6OlRl
c3Q6OlV0aWxzOwordXNlIFRlc3Q6Ok1vcmU7CisKK2lmICgkRU5We2VuYWJsZV9pbmplY3Rpb25f
cG9pbnRzfSBuZSAneWVzJykKK3sKKwlwbGFuIHNraXBfYWxsID0+ICdJbmplY3Rpb24gcG9pbnRz
IG5vdCBzdXBwb3J0ZWQgYnkgdGhpcyBidWlsZCc7Cit9CisKK215ICRub2RlID0gUG9zdGdyZVNR
TDo6VGVzdDo6Q2x1c3Rlci0+bmV3KCdub2RlJyk7Ciskbm9kZS0+aW5pdDsKKyRub2RlLT5hcHBl
bmRfY29uZigncG9zdGdyZXNxbC5jb25mJywgJ21heF9wcmVwYXJlZF90cmFuc2FjdGlvbnMgPSA1
Jyk7Ciskbm9kZS0+c3RhcnQ7CisKK2lmICghJG5vZGUtPmNoZWNrX2V4dGVuc2lvbignaW5qZWN0
aW9uX3BvaW50cycpKQoreworCXBsYW4gc2tpcF9hbGwgPT4gJ0V4dGVuc2lvbiBpbmplY3Rpb25f
cG9pbnRzIG5vdCBpbnN0YWxsZWQnOworfQorCiskbm9kZS0+c2FmZV9wc3FsKCdwb3N0Z3Jlcycs
ICdDUkVBVEUgRVhURU5TSU9OIGluamVjdGlvbl9wb2ludHMnKTsKKyRub2RlLT5zYWZlX3BzcWwo
CisJJ3Bvc3RncmVzJywgcXsKKwlTRUxFQ1QgaW5qZWN0aW9uX3BvaW50c19hdHRhY2goJ3R3b3Bo
YXNlLWNhbGxiYWNrcy1leGVjdXRpb24nLCAnZXJyb3InKQorfSk7CisKKyRub2RlLT5zYWZlX3Bz
cWwoCisJJ3Bvc3RncmVzJywgcXsKKwlDUkVBVEUgVEFCTEUgdHdvcGhhc2VfbG9ja190YXJnZXQo
aWQgaW50KTsKKwlDUkVBVEUgVEFCTEUgdHdvcGhhc2VfY29tbWl0X21hcmtlcihpZCBpbnQpOwor
CisJQkVHSU47CisJTE9DSyBUQUJMRSB0d29waGFzZV9sb2NrX3RhcmdldCBJTiBBQ0NFU1MgRVhD
TFVTSVZFIE1PREU7CisJSU5TRVJUIElOVE8gdHdvcGhhc2VfY29tbWl0X21hcmtlciBWQUxVRVMg
KDEpOworCVBSRVBBUkUgVFJBTlNBQ1RJT04gJ3ByZXBhcmVkJzsKK30pOworCitteSAkdGFibGVf
b2lkID0gJG5vZGUtPnNhZmVfcHNxbCgKKwkncG9zdGdyZXMnLCBxeworCVNFTEVDVCBvaWQgRlJP
TSBwZ19jbGFzcyBXSEVSRSByZWxuYW1lID0gJ3R3b3BoYXNlX2xvY2tfdGFyZ2V0JzsKK30pOwor
CisKK215ICgkcmV0LCAkc3Rkb3V0LCAkc3RkZXJyKSA9CisgICRub2RlLT5wc3FsKCdwb3N0Z3Jl
cycsIHF7Q09NTUlUIFBSRVBBUkVEICdwcmVwYXJlZCd9KTsKKworaXNudCgkcmV0LCAwLCAnQ09N
TUlUIFBSRVBBUkVEIGZhaWxlZCBhdCBpbmplY3RjaW9uIHBvaW50Jyk7CisKKyRyZXQgPSAkbm9k
ZS0+c2FmZV9wc3FsKAorCSdwb3N0Z3JlcycsIHF7CisJU0VMRUNUIGNvdW50KCopIEZST00gcGdf
cHJlcGFyZWRfeGFjdHM7Cit9KTsKK2lzKCRyZXQsIDAsICd3ZSBjYW5ub3Qgc2VlIHRyYW5zYWN0
aW9uIGluIHRoZSBjYXRhbG9nJyk7CisKKyRyZXQgPSAkbm9kZS0+c2FmZV9wc3FsKAorCSdwb3N0
Z3JlcycsIHF7CisJU0VMRUNUICogRlJPTSB0d29waGFzZV9jb21taXRfbWFya2VyOworfSk7Citp
cygkcmV0LCAxLCAndHJhbnNhY3Rpb24gaGFzIGJlZW4gY29tbWl0ZWQnKTsKKworCiskcmV0ID0g
JG5vZGUtPnNhZmVfcHNxbCgKKwkncG9zdGdyZXMnLCBxcXsKKwlTRUxFQ1QgQ09VTlQoKikgRlJP
TSBwZ19sb2NrcyBXSEVSRSBsb2NrdHlwZSA9ICdyZWxhdGlvbicgQU5EIHJlbGF0aW9uID0gJHRh
YmxlX29pZDsKK30pOworaXMoJHJldCwgMSwgJ29ycGhhbmVkIGxvY2sgZGV0ZWN0ZWQnKTsKKwor
JG5vZGUtPnN0b3A7Citkb25lX3Rlc3RpbmcoKTsKLS0gCjIuNDMuMAoK
--0000000000000da19e06527c2233--