Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sFVFe-002us9-TI for pgsql-bugs@arkaria.postgresql.org; Fri, 07 Jun 2024 08:47:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sFVFc-00A2dG-TA for pgsql-bugs@arkaria.postgresql.org; Fri, 07 Jun 2024 08:47:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sFVFc-00A2cU-BS for pgsql-bugs@lists.postgresql.org; Fri, 07 Jun 2024 08:47:09 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sFVFZ-0008H6-2I for pgsql-bugs@lists.postgresql.org; Fri, 07 Jun 2024 08:47:07 +0000 Received: from [172.30.2.66] (unknown [172.30.2.66]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) (Authenticated sender: d.koval@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 1F107E20520 for ; Fri, 7 Jun 2024 11:47:01 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1717750021; bh=3TLi+g3BOsjLpCjyBNT4fkv3b4wIlLmX+kglueuPZSQ=; h=Message-ID:Date:User-Agent:Subject:To:References:From:In-Reply-To: From; b=Y3I0HniJZaFZ7EDDu566YXoMYcoG5nmcxD0TFLU6/9gFSo+myd0XY16W+VfN33ves CkOKRWf29ftSGaXMD8qlTtsTl9XDyDyDEmw1/nX5dY5ycMC/pUq1J/jBALaHB6IP5T S922qLJV9yhi8AYPFFFHKfGPdGzthCteyRMfx9iXHJ8H0F504WZNoh7JohBMQUZAhl M1J1dz7rFt6saF+ohnD4nuIREcC6WlZAQfm8B7hd+hhGupdf25NYAIUpTSKpx3QYAq m1vUKA39G4hR2qb4eVipHXqFs5T4hku6e1lYcxatpHJnqJ5pueYTa9E00wpjqrptMW G7UNkEbMuzLPQ== Content-Type: multipart/mixed; boundary="------------NE5aZPqprh8YuJwcJn4BNPCC" Message-ID: <3a6d9e93-3c29-4fc4-b41d-edec72937db9@postgrespro.ru> Date: Fri, 7 Jun 2024 11:47:02 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? To: pgsql-bugs@lists.postgresql.org References: <18498-b95f3ee4c156de45@postgresql.org> Content-Language: en-US From: Dmitry Koval In-Reply-To: <18498-b95f3ee4c156de45@postgresql.org> X-KSMG-AntiPhishing: NotDetected, bases: 2024/06/07 08:12:00 X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2024/06/07 04:07:00 #25486014 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------NE5aZPqprh8YuJwcJn4BNPCC Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Additional attachments for previous email. -- With best regards, Dmitry Koval Postgres Professional: http://postgrespro.com --------------NE5aZPqprh8YuJwcJn4BNPCC Content-Type: text/plain; charset=UTF-8; name="v1-0001-LOCK-TABLE-with-deadlocks.patch" Content-Disposition: attachment; filename="v1-0001-LOCK-TABLE-with-deadlocks.patch" Content-Transfer-Encoding: base64 RnJvbSAwYjJmODNlMDc5YjUwMDM3MDc4NGFlNDgzOGIyNDdhYjcyY2MyZTgyIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBLb3ZhbCBEbWl0cnkgPGQua292YWxAcG9zdGdyZXNw cm8ucnU+CkRhdGU6IFRodSwgNiBKdW4gMjAyNCAxOTo1MzoxNSArMDMwMApTdWJqZWN0OiBb UEFUQ0ggdjFdIExPQ0sgVEFCTEUgd2l0aCBkZWFkbG9ja3MKCi0tLQogLi4uL3Rlc3RfbWlz Yy90LzAwNl9wYXJhbGxlbF9pbnNlcnRzLnBsICAgICAgIHwgNzcgKysrKysrKysrKysrKysr KysrKwogMSBmaWxlIGNoYW5nZWQsIDc3IGluc2VydGlvbnMoKykKIGNyZWF0ZSBtb2RlIDEw MDY0NCBzcmMvdGVzdC9tb2R1bGVzL3Rlc3RfbWlzYy90LzAwNl9wYXJhbGxlbF9pbnNlcnRz LnBsCgpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvbW9kdWxlcy90ZXN0X21pc2MvdC8wMDZfcGFy YWxsZWxfaW5zZXJ0cy5wbCBiL3NyYy90ZXN0L21vZHVsZXMvdGVzdF9taXNjL3QvMDA2X3Bh cmFsbGVsX2luc2VydHMucGwKbmV3IGZpbGUgbW9kZSAxMDA2NDQKaW5kZXggMDAwMDAwMDAw MC4uZTgxODVlYTE4OAotLS0gL2Rldi9udWxsCisrKyBiL3NyYy90ZXN0L21vZHVsZXMvdGVz dF9taXNjL3QvMDA2X3BhcmFsbGVsX2luc2VydHMucGwKQEAgLTAsMCArMSw3NyBAQAordXNl IHN0cmljdDsKK3VzZSB3YXJuaW5nczsKKwordXNlIHRocmVhZHM7Cit1c2UgSVBDOjpSdW47 Cit1c2UgUG9zdGdyZVNRTDo6VGVzdDo6Q2x1c3RlcjsKK3VzZSBQb3N0Z3JlU1FMOjpUZXN0 OjpVdGlsczsKK3VzZSBUZXN0OjpNb3JlOworCitteSAkVEhSRUFEUyA9IDQ7CitteSAkUk9X UyA9IDM2NTsKKworIyBJbml0aWFsaXplIG5ldyBub2RlLgorbXkgJG5vZGUgPSBQb3N0Z3Jl U1FMOjpUZXN0OjpDbHVzdGVyLT5uZXcoIm5vZGUiKTsKKyRub2RlLT5pbml0KCk7Ciskbm9k ZS0+c3RhcnQ7CisKKyMgSW5pdGlhbCBxdWVyaWVzLgorJG5vZGUtPnNhZmVfcHNxbCgicG9z dGdyZXMiLCAiCitDUkVBVEUgVEFCTEUgdGVzdChpIGludCwgdCB0ZXh0KTsKK0NSRUFURSBU QUJMRSB0ZXN0MihpIGludCwgdCB0ZXh0KTsKKworQ1JFQVRFIEZVTkNUSU9OIHRlc3RfZnVu YygpIFJFVFVSTlMgdHJpZ2dlciBBUworXCRCT0RZXCQKK0RFQ0xBUkUKKyAgcSB0ZXh0Owor QkVHSU4KKyAgUkFJU0UgTE9HICdCZWZvcmUgbG9jayAocGlkICUpJywgcGdfYmFja2VuZF9w aWQoKTsKKyAgTE9DSyBUQUJMRSB0ZXN0OworICBSQUlTRSBMT0cgJ0FmdGVyIGxvY2sgKHBp ZCAlKScsIHBnX2JhY2tlbmRfcGlkKCk7CisgIHEgPSAnSU5TRVJUIElOVE8gdGVzdDIgU0VM RUNUICgoXCQxKTo6dGVzdCkuKic7CisgIEVYRUNVVEUgcSBVU0lORyBORVc7CisgIFJFVFVS TiBOVUxMOworRU5EOworXCRCT0RZXCQgTEFOR1VBR0UgcGxwZ3NxbCBTRUNVUklUWSBERUZJ TkVSOworCitDUkVBVEUgVFJJR0dFUiB0ZXN0X3RyaWdnZXIgQkVGT1JFIElOU0VSVCBPTiB0 ZXN0IEZPUiBFQUNIIFJPVyBFWEVDVVRFIFBST0NFRFVSRSB0ZXN0X2Z1bmMoKTsKKyIpOwor CisjIEludm9rZSBwc3FsLgorc3ViIHBzcWxfd29ya2VyCit7CisJbXkgKCR3b3JrZXJfbmFt ZSkgPSBAXzsKKworCWZvciAobXkgJGogPSAxOyAkaiA8PSAkUk9XUzsgJGogKz0gMSkKKwl7 CisJCW15ICRxdWVyeSA9IHFxW0lOU0VSVCBJTlRPIHRlc3QgVkFMVUVTICgkaiwgJyR3b3Jr ZXJfbmFtZScpO107CisJCW15IEBjbWQgPSBbJ3BzcWwnLCAnLVhBdHEnLCAnLWQnLCAkbm9k ZS0+Y29ubnN0cigncG9zdGdyZXMnKSwgJy12JywgJ09OX0VSUk9SX1NUT1A9MScsICctYycs ICRxdWVyeV07CisKKwkJcHJpbnQoIiMgRXhlY3V0aW5nOiAkcXVlcnlcbiIpOworCQlJUEM6 OlJ1bjo6cnVuIEBjbWQ7CisJfQorfQorCisjIFN0YXJ0IHRoZSB3b3JrZXJzLgorbXkgQHdv cmtlcnM7Citmb3IgKG15ICRpID0gMTsgJGkgPD0gJFRIUkVBRFM7ICRpICs9IDEpCit7CisJ bXkgJHdvcmtlcl9uYW1lX2kgPSAid29ya2VyXyRpIjsKKwlwcmludCgiIyBXb3JrZXI6ICR3 b3JrZXJfbmFtZV9pXG4iKTsKKwlwdXNoIEB3b3JrZXJzLCB0aHJlYWRzLT5jcmVhdGUoJ3Bz cWxfd29ya2VyJywgJHdvcmtlcl9uYW1lX2kpOworfQorCisjIFdhaXQgZm9yIHRoZSB3b3Jr ZXJzIHRvIGZpbmlzaC4KK2ZvcmVhY2ggbXkgJHRociAoQHdvcmtlcnMpCit7CisJJHRoci0+ am9pbigpOworfQorCisjIENoZWNrIHJvd3MuCitteSAkcHNxbF9zdGRvdXQ7Ciskbm9kZS0+ cHNxbCgncG9zdGdyZXMnLCBxKFNFTEVDVCBjb3VudCgqKSBGUk9NIHRlc3QyOyksIHN0ZG91 dCA9PiBcJHBzcWxfc3Rkb3V0KTsKK2lzKCRwc3FsX3N0ZG91dCwgJFJPV1MgKiAkVEhSRUFE UywgJ3Jvd3MgYW1vdW50Jyk7CisKKyMgRG9uZS4KKyRub2RlLT5zdG9wOworZG9uZV90ZXN0 aW5nKCk7Ci0tIAoyLjM0LjEKCg== --------------NE5aZPqprh8YuJwcJn4BNPCC Content-Type: text/plain; charset=UTF-8; name="v1-0001-LOCK-TABLE-wo-errors.patch" Content-Disposition: attachment; filename="v1-0001-LOCK-TABLE-wo-errors.patch" Content-Transfer-Encoding: base64 RnJvbSAzZjY0ZWYxMmI3NzdkNDllMzdiZjMzOGZhNGUxZTk5MjQzMjU1NDQ1IE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBLb3ZhbCBEbWl0cnkgPGQua292YWxAcG9zdGdyZXNw cm8ucnU+CkRhdGU6IFRodSwgNiBKdW4gMjAyNCAxOTo1MzoxNSArMDMwMApTdWJqZWN0OiBb UEFUQ0ggdjFdIExPQ0sgVEFCTEUgd28gZXJyb3JzCgotLS0KIC4uLi90ZXN0X21pc2MvdC8w MDZfcGFyYWxsZWxfaW5zZXJ0cy5wbCAgICAgICB8IDc0ICsrKysrKysrKysrKysrKysrKysK IDEgZmlsZSBjaGFuZ2VkLCA3NCBpbnNlcnRpb25zKCspCiBjcmVhdGUgbW9kZSAxMDA2NDQg c3JjL3Rlc3QvbW9kdWxlcy90ZXN0X21pc2MvdC8wMDZfcGFyYWxsZWxfaW5zZXJ0cy5wbAoK ZGlmZiAtLWdpdCBhL3NyYy90ZXN0L21vZHVsZXMvdGVzdF9taXNjL3QvMDA2X3BhcmFsbGVs X2luc2VydHMucGwgYi9zcmMvdGVzdC9tb2R1bGVzL3Rlc3RfbWlzYy90LzAwNl9wYXJhbGxl bF9pbnNlcnRzLnBsCm5ldyBmaWxlIG1vZGUgMTAwNjQ0CmluZGV4IDAwMDAwMDAwMDAuLjA4 OTY3ZTVhYTgKLS0tIC9kZXYvbnVsbAorKysgYi9zcmMvdGVzdC9tb2R1bGVzL3Rlc3RfbWlz Yy90LzAwNl9wYXJhbGxlbF9pbnNlcnRzLnBsCkBAIC0wLDAgKzEsNzQgQEAKK3VzZSBzdHJp Y3Q7Cit1c2Ugd2FybmluZ3M7CisKK3VzZSB0aHJlYWRzOwordXNlIElQQzo6UnVuOwordXNl IFBvc3RncmVTUUw6OlRlc3Q6OkNsdXN0ZXI7Cit1c2UgUG9zdGdyZVNRTDo6VGVzdDo6VXRp bHM7Cit1c2UgVGVzdDo6TW9yZTsKKworbXkgJFRIUkVBRFMgPSA0OworbXkgJFJPV1MgPSAz NjU7CisKKyMgSW5pdGlhbGl6ZSBuZXcgbm9kZS4KK215ICRub2RlID0gUG9zdGdyZVNRTDo6 VGVzdDo6Q2x1c3Rlci0+bmV3KCJub2RlIik7Ciskbm9kZS0+aW5pdCgpOworJG5vZGUtPnN0 YXJ0OworCisjIEluaXRpYWwgcXVlcmllcy4KKyRub2RlLT5zYWZlX3BzcWwoInBvc3RncmVz IiwgIgorQ1JFQVRFIFRBQkxFIHRlc3QoaSBpbnQsIHQgdGV4dCk7CisKK0NSRUFURSBGVU5D VElPTiB0ZXN0X2Z1bmMoYV9pIGludCwgYV90IHRleHQpIFJFVFVSTlMgdm9pZCBBUworXCRC T0RZXCQKK0RFQ0xBUkUKKyAgcSB0ZXh0OworQkVHSU4KKyAgUkFJU0UgTE9HICdCZWZvcmUg bG9jayAocGlkICUpJywgcGdfYmFja2VuZF9waWQoKTsKKyAgTE9DSyBUQUJMRSB0ZXN0Owor ICBSQUlTRSBMT0cgJ0FmdGVyIGxvY2sgKHBpZCAlKScsIHBnX2JhY2tlbmRfcGlkKCk7Cisg IHEgPSAnSU5TRVJUIElOVE8gdGVzdCBWQUxVRVMoJyB8fCBhX2kgfHwgJywnJyB8fCAgYV90 IHx8ICcnKSc7CisgIEVYRUNVVEUgcTsKK0VORDsKK1wkQk9EWVwkIExBTkdVQUdFIHBscGdz cWwgU0VDVVJJVFkgREVGSU5FUjsKKyIpOworCisjIEludm9rZSBwc3FsLgorc3ViIHBzcWxf d29ya2VyCit7CisJbXkgKCR3b3JrZXJfbmFtZSkgPSBAXzsKKworCWZvciAobXkgJGogPSAx OyAkaiA8PSAkUk9XUzsgJGogKz0gMSkKKwl7CisJCW15ICRxdWVyeSA9ICJTRUxFQ1QgdGVz dF9mdW5jKCRqLCAnJHdvcmtlcl9uYW1lJyk7IjsKKworCQlteSBAY21kID0gWydwc3FsJywg Jy1YQXRxJywgJy1kJywgJG5vZGUtPmNvbm5zdHIoJ3Bvc3RncmVzJyksICctdicsICdPTl9F UlJPUl9TVE9QPTEnLCAnLWMnLCAkcXVlcnldOworCisJCXByaW50KCIjIEV4ZWN1dGluZzog JHF1ZXJ5XG4iKTsKKwkJSVBDOjpSdW46OnJ1biBAY21kOworCX0KK30KKworIyBTdGFydCB0 aGUgd29ya2Vycy4KK215IEB3b3JrZXJzOworZm9yIChteSAkaSA9IDE7ICRpIDw9ICRUSFJF QURTOyAkaSArPSAxKQoreworCW15ICR3b3JrZXJfbmFtZV9pID0gIndvcmtlcl8kaSI7CisJ cHJpbnQoIiMgV29ya2VyOiAkd29ya2VyX25hbWVfaVxuIik7CisJcHVzaCBAd29ya2Vycywg dGhyZWFkcy0+Y3JlYXRlKCdwc3FsX3dvcmtlcicsICR3b3JrZXJfbmFtZV9pKTsKK30KKwor IyBXYWl0IGZvciB0aGUgd29ya2VycyB0byBmaW5pc2guCitmb3JlYWNoIG15ICR0aHIgKEB3 b3JrZXJzKQoreworCSR0aHItPmpvaW4oKTsKK30KKworIyBDaGVjayByb3dzLgorbXkgJHBz cWxfc3Rkb3V0OworJG5vZGUtPnBzcWwoJ3Bvc3RncmVzJywgcShTRUxFQ1QgY291bnQoKikg RlJPTSB0ZXN0OyksIHN0ZG91dCA9PiBcJHBzcWxfc3Rkb3V0KTsKK2lzKCRwc3FsX3N0ZG91 dCwgJFJPV1MgKiAkVEhSRUFEUywgJ3Jvd3MgYW1vdW50Jyk7CisKKyMgRG9uZS4KKyRub2Rl LT5zdG9wOworZG9uZV90ZXN0aW5nKCk7Ci0tIAoyLjM0LjEKCg== --------------NE5aZPqprh8YuJwcJn4BNPCC--