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 1umS3w-00Fti7-4n for pgsql-general@arkaria.postgresql.org; Thu, 14 Aug 2025 07:07:48 +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 1umS3t-003Vvh-FP for pgsql-general@arkaria.postgresql.org; Thu, 14 Aug 2025 07:07:45 +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 1umS3s-003VvY-OE for pgsql-general@lists.postgresql.org; Thu, 14 Aug 2025 07:07:45 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1umS3o-000UrX-0i for pgsql-general@lists.postgresql.org; Thu, 14 Aug 2025 07:07:43 +0000 Content-Type: multipart/alternative; boundary="------------N1ghtWg07LiWt34Js9eqQ0DE" Message-ID: <0c474bc1-e7d6-4d7f-88ad-5284f89c997b@cloud.gatewaynet.com> Date: Thu, 14 Aug 2025 08:07:36 +0100 MIME-Version: 1.0 Content-Language: en-US To: "pgsql-general@lists.postgresql.org" From: Achilleas Mantzios Subject: Strange deadlock with object/target of lock : transaction 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. --------------N1ghtWg07LiWt34Js9eqQ0DE Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable Hi All We've been hit by a weird deadlock which it took me some days to isolate=20 and replicate. It does not have to do with order of updates or any=20 explicit TABLE-level locking, the objects/targets of the deadlock in=20 question are transactions. I show the schema of the table and its triggers functions : amantzio@[local]/dynacom=3D# \d bdynacom.payments_finalization =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Table=20 "bdynacom.payments_finalization" =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Column =C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0Type =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0| Collation |=20 Nullable | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0Default -------------------------+--------------------------+-----------+--------= --+------------------------------------------------------------=20 id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| integer =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| not=20 null | nextval('bdynacom.payments_finalization_id_seq'::regclass) year =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| integer =C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| = not null | doc_no =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| integer =C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null = | accnt_ukey =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0| integer =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | inserted_at =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0| timestamp with time zone | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0| not=20 null | now() bank_name =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null = | management_company_name | text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not nu= ll | beneficiary_name =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| currency =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not nu= ll | amount =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| double precision =C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| not null | explanation =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0| text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | card_code =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0| character varying(20) =C2=A0=C2=A0=C2=A0| =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| vsl_code =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0| character varying(20) =C2=A0=C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| signed_by =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null = | delivered_at =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| timestamp with time zone | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0| not null | group_explanation =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0| ingroup =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| boolean =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| false is_transfer =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0| boolean =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not=20 null | false bank_bic =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0| character varying(11) =C2=A0=C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not=20 null | 'XXXXXXXX'::character varying bank_account =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| character varying(35) =C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not=20 null | ''::character varying amount_local =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| double precision =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not=20 null | 0 creditor_bank_name =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| creditor_bank_bic =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| character varying= (11) =C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| creditor_bank_account =C2=A0=C2=A0| character varying(35) =C2=A0=C2=A0=C2= =A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| sign_list =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0| boolean =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| false Indexes: =C2=A0=C2=A0=C2=A0"payments_finalization_pkey" PRIMARY KEY, btree (id) D= EFERRABLE =C2=A0=C2=A0=C2=A0"payments_finalization_accnt_ukey_uk" UNIQUE CONSTRAIN= T, btree=20 (accnt_ukey) REPLICA IDENTITY =C2=A0=C2=A0=C2=A0"payments_finalization_bank_account" btree (bank_accou= nt) =C2=A0=C2=A0=C2=A0"payments_finalization_delivered_at_date" btree=20 (extract_date(delivered_at)) =C2=A0=C2=A0=C2=A0"payments_finalization_idx1" btree (inserted_at, vsl_c= ode,=20 card_code, ingroup) =C2=A0=C2=A0=C2=A0"payments_finalization_sign_list" btree (sign_list) =C2=A0=C2=A0=C2=A0"payments_finalization_uk" UNIQUE CONSTRAINT, btree (d= oc_no, year) Check constraints: =C2=A0=C2=A0=C2=A0"payments_finalization_check_ingroup_group_explanation= " CHECK=20 (COALESCE(ingroup, false) AND group_explanation IS NOT NULL OR NOT=20 COALESCE(ingroup, false) AND group_explanation IS NUL L) =C2=A0=C2=A0=C2=A0"valid_signatures" CHECK (signed_by =3D ANY (ARRAY['GP= '::text,=20 'EP'::text, 'IP'::text, 'MP'::text, 'NC'::text, 'MEP'::text, 'N/A'::text]= )) Triggers: =C2=A0=C2=A0=C2=A0payments_finalization AFTER INSERT ON bdynacom.payment= s_finalization=20 FOR EACH ROW EXECUTE FUNCTION payments_finalization_force_integrity() =C2=A0=C2=A0=C2=A0payments_finalization_set_epayment_finalized_tg AFTER = INSERT OR=20 DELETE ON bdynacom.payments_finalization FOR EACH ROW EXECUTE FUNCTION=20 payments_finalization_set_epayment_finalized() =C2=A0=C2=A0=C2=A0payments_finalization_set_id_tg BEFORE INSERT ON=20 bdynacom.payments_finalization FOR EACH ROW EXECUTE FUNCTION=20 payments_finalization_set_id() Inherits: payments_finalization amantzio@[local]/dynacom=3D# \sf payments_finalization_set_id CREATE OR REPLACE FUNCTION public.payments_finalization_set_id() RETURNS trigger LANGUAGE plpgsql AS $function$DECLARE =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0nuid INTEGER; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0footmp text; BEGIN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0IF (TG_OP <> 'INSERT') THEN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0RAISE EXCEPTION 'TRIGGER : % called on unsuported op :=20 %. ONLY INSERT IS ALLOWED',TG_NAME, TG_OP; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0IF (new.id > 0) THEN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0select pg_advisory_lock(1010) = INTO footmp; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0select COALESCE(max(id),0)+1 I= NTO nuid FROM payments_finalization; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0select pg_advisory_unlock(1010= ) INTO footmp; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0NEW.id :=3D nuid ; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0RETURN NEW; END; $function$ amantzio@[local]/dynacom=3D# \sf payments_finalization_set_epayment_final= ized CREATE OR REPLACE FUNCTION=20 public.payments_finalization_set_epayment_finalized() RETURNS trigger LANGUAGE plpgsql AS $function$ DECLARE tmp int; REC RECORD; BEGIN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0IF (TG_WHEN !=3D 'AFTER') THEN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0RAISE EXCEPTION 'TRIGGER : % supports only ON AFTER.=20 Called on unsuported WHEN : %',TG_NAME, TG_WHEN; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0IF (TG_OP =3D 'INSERT') THEN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0UPDATE payment p SET isfinalized =3D 't', status =3D 'F= NLZ'=20 WHERE p.year=3DNEW.year AND p.doc_no=3DNEW.doc_no AND p.is_epayment=3D0; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0ELSIF (TG_OP =3D 'DELETE') THE= N =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0UPDATE payment p SET isfinalized =3D 'f', status =3D 'I= NSD'=20 WHERE p.year=3DOLD.year AND p.doc_no=3DOLD.doc_no AND p.is_epayment=3D0; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0ELSE =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0/* UPDATE */ =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0RAISE EXCEPTION 'TRIGGER : % called on unsuported op :=20 %',TG_NAME, TG_OP; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF; /* */ =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0RETURN NEW; END; $function$ amantzio@[local]/dynacom=3D# \sf payments_finalization_force_integrity CREATE OR REPLACE FUNCTION public.payments_finalization_force_integrity() RETURNS trigger LANGUAGE plpgsql AS $function$ DECLARE tmpingroup int; tmp int; REC RECORD; initid INT; BEGIN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 IF (NEW.id > 0 AND pg_trigger_depth= () =3D 1) THEN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0SET CONSTRAINTS ALL DEFERRED; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0SELECT COALESCE(max(id),0)+1 into initid FROM=20 payments_finalization WHERE sign_list; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0tmp :=3D 0; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0FOR REC IN SELECT year,doc_no FROM payments_finalizatio= n=20 WHERE NOT sign_list ORDER BY=20 bank_name,management_company_name,beneficiary_name,year,doc_no DESC LOOP =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0UPDATE = payments_finalization SET id=3Dinitid+tmp=20 WHERE year=3DREC.year AND doc_no=3DREC.doc_no; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0tmp :=3D= tmp + 1; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0END LOOP; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0RETURN NEW; END; $function$ amantzio@[local]/dynacom=3D# How I replicated : (all tested in PgSQL 18beta1) session A) amantzio@[local]/dynacom=3D# begin; select txid_current(),=20 pg_backend_pid() ; =C2=A0UPDATE payments_finalization pf set sign_list =3D= true=20 where delivered_at::date =3D current_date ; BEGIN txid_current | pg_backend_pid --------------+---------------- =C2=A0=C2=A0=C2=A0117269038 | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A016941 (1 row) UPDATE 6 amantzio@[local]/dynacom=3D*# session B) postgres@[local]/dynacom=3D# begin; select txid_current(),=20 pg_backend_pid() ; =C2=A0UPDATE payments_finalization pf set sign_list =3D= true=20 where delivered_at::date =3D current_date ; BEGIN txid_current | pg_backend_pid --------------+---------------- =C2=A0=C2=A0=C2=A0117269039 | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A016952 (1 row) (..waits..) session C) amantzio@[local]/dynacom=3D# begin; select txid_current() ,=20 pg_backend_pid() =C2=A0; INSERT INTO payments_finalization (year, doc_no,= =20 accnt_ukey, inserted_at, bank_name, management_company_name, beneficiary_name, currency, amount, explanation, card_code, vsl_code,=20 signed_by, delivered_at, group_explanation, ingroup, is_transfer,=20 bank_bic, bank_account, amount_local, creditor_bank_ name, creditor_bank_bic, creditor_bank_account, sign_list) VALUES (2025,=20 395302, 143392502, '2025-08-13 01:00:00+03', 'CREDIT SUISSE AG',=20 '0006-DYNACOM TANKERS MANAGEMENT LTD', 'AUTUMN SHI PPING SERVICES LIMITED', 'EUR', 500, 'TRANSFER TO EUROBANK GR / AUTUMN=20 SHIP. E - OCEANIA', NULL, NULL, 'GP', '2025-08-13 11:55:28.359485+03',=20 NULL, false, true, 'CRESCHZH', '08352333263820 01', 500, NULL, 'ERBKGRAA', '0026.0029.27.0200765876', false); BEGIN txid_current | pg_backend_pid --------------+---------------- =C2=A0=C2=A0=C2=A0117269040 | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A016960 (1 row) (..waits..) session A) amantzio@[local]/dynacom=3D*# rollback ; ROLLBACK amantzio@[local]/dynacom=3D# session B) UPDATE 6 postgres@[local]/dynacom=3D*# session C) ERROR: =C2=A0deadlock detected DETAIL: =C2=A0Process 16960 waits for ShareLock on transaction 117269039;= =20 blocked by process 16952. Process 16952 waits for ShareLock on transaction 117269040; blocked by=20 process 16960. HINT: =C2=A0See server log for query details. CONTEXT: =C2=A0while updating tuple (9611,12) in relation=20 "payments_finalization" SQL statement "UPDATE payments_finalization SET id=3Dinitid+tmp WHERE=20 year=3DREC.year AND doc_no=3DREC.doc_no" PL/pgSQL function payments_finalization_force_integrity() line 30 at SQL=20 statement Two workarounds - solutions I found : 1) If I replace the advisory lock in=20 public.payments_finalization_set_id() with : LOCK TABLE payments_finalization IN SHARE ROW EXCLUSIVE MODE; Then apparently all inserts and updates are serialized , and this seems=20 to do the trick. 2) Also, If i keep the advisory locks (no table locking) , but change=20 all updates so that they perform row level locking , by a) changing public.payments_finalization_force_integrity()'s loop to =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 FOR REC IN SELECT year,doc_no FROM payments_finalization=20 WHERE NOT sign_list ORDER BY=20 bank_name,management_company_name,beneficiary_name,year,doc_no FOR=20 UPDATE LOOP =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0UPDATE = payments_finalization SET id=3Dinitid+tmp=20 WHERE year=3DREC.year AND doc_no=3DREC.doc_no; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0tmp :=3D= tmp + 1; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0END LOOP; b) *AND* changing the other session updates to with tempqry as (select year,doc_no from payments_finalization where=20 delivered_at::date =3D current_date order by bank_name,management_company_name,beneficiary_name,year,doc_no FOR UPDATE= ) UPDATE payments_finalization pf set sign_list =3D true FROM tempqry WHERE= =20 pf.year =3D tempqry.year and pf.doc_no =3D tempqry.doc_no ; also works and no deadlock is caused. (added consistent ordering in all=20 updates as well, dont know if this has any effect). What is certain is=20 that consistent ordering alone without the FOR UPDATE row level lock=20 does not work, still causes the deadlock. The thing is, that while I think I can solve this particular problem, I=20 still don't understand why it happened.=C2=A0 I have some questions and=20 remarks. Regarding the docs on locking=20 (https://www.postgresql.org/docs/18/explicit-locking.html) , I found for=20 instance that SHARE UPDATE EXCLUSIVE MODE does not block INSERTs,=20 UPDATEs, whereas SHARE ROW EXCLUSIVE MODE blocks INSERTs and UPDATEs. It=20 would help if those behaviors were documented, and also explain how does=20 default implicit locking via MVCC interact with TABLE level locks. And=20 the most important question is about transactionid-type locks,=C2=A0 whil= e=20 pg_locks's doc state that locktype can be "transactionid"=C2=A0 reading h= ere=20 : https://www.postgresql.org/docs/18/xact-locking.html didn't help much=20 to understand the mechanics behind it, so while technically I see the=20 deadlock pattern between pids 16960 and16952 andtransactions : 117269040=20 and117269039 I cannot see where those transactionid-type ShareLock locks=20 are acquired / requested for , in which part of the code / sql and why. --------------N1ghtWg07LiWt34Js9eqQ0DE Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi All

We've been hit by a weird deadlock which it took me some days to isolate and replicate. It does not have to do with order of updates or any explicit TABLE-level locking, the objects/targets of the deadlock in question are transactions.

I show the schema of the table and its triggers functions :

amantzio@[loc= al]/dynacom=3D# \d bdynacom.payments_finalization
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0T= able "bdynacom.payments_finalization"
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Column =C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Type =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0| Collation | Nullable | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0Default =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0
-------------------------+--------------------------+-----------+--------= --+------------------------------------------------------------
id =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| integer = =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0| not null | nextval('bdynacom.payments_finalization_id_seq'::regclass)
year =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| integer =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| not null | =C2=A0
doc_no =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| integer =C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | =C2=A0
accnt_ukey =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0| integer =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | =C2=A0
inserted_at =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| timestamp with time zone | =C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | now()
bank_name =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | =C2=A0
management_company_name | text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | =C2=A0
beneficiary_name =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| text= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0 currency =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= | not null | =C2=A0
amount =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| double precision =C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0| not null | =C2=A0
explanation =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | =C2=A0
card_code =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| character varying(20) =C2=A0=C2=A0=C2=A0| =C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0 vsl_code =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| character varying(20) =C2=A0=C2=A0=C2=A0= | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0 signed_by =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | =C2=A0
delivered_at =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0| timestamp with time zone | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | =C2=A0
group_explanation =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| text =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0 ingroup =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| boolean =C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| false
is_transfer =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| boolean =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | false
bank_bic =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| character varying(11) =C2=A0=C2=A0=C2=A0= | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | 'XXXXXXXX'::character varying
bank_account =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0| character varying(35) =C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | ''::character varying
amount_local =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0| double precision =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| not null | 0
creditor_bank_name =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| text =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0 creditor_bank_bic =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| character= varying(11) =C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0 creditor_bank_account =C2=A0=C2=A0| character varying(35) =C2=A0=C2= =A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0 sign_list =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| boolean =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| false
Indexes:
=C2=A0=C2=A0=C2=A0"payments_finalization_pkey" PRIMARY KEY, btree= (id) DEFERRABLE
=C2=A0=C2=A0=C2=A0"payments_finalization_accnt_ukey_uk" UNIQUE CO= NSTRAINT, btree (accnt_ukey) REPLICA IDENTITY
=C2=A0=C2=A0=C2=A0"payments_finalization_bank_account" btree (ban= k_account)
=C2=A0=C2=A0=C2=A0"payments_finalization_delivered_at_date" btree (extract_date(delivered_at))
=C2=A0=C2=A0=C2=A0"payments_finalization_idx1" btree (inserted_at= , vsl_code, card_code, ingroup)
=C2=A0=C2=A0=C2=A0"payments_finalization_sign_list" btree (sign_l= ist)
=C2=A0=C2=A0=C2=A0"payments_finalization_uk" UNIQUE CONSTRAINT, b= tree (doc_no, year)
Check constraints:
=C2=A0=C2=A0=C2=A0"payments_finalization_check_ingroup_group_expl= anation" CHECK (COALESCE(ingroup, false) AND group_explanation IS NOT NULL OR NOT COALESCE(ingroup, false) AND group_explanation IS NUL
L)
=C2=A0=C2=A0=C2=A0"valid_signatures" CHECK (signed_by =3D ANY (AR= RAY['GP'::text, 'EP'::text, 'IP'::text, 'MP'::text, 'NC'::text, 'MEP'::text, 'N/A'::text]))
Triggers:
=C2=A0=C2=A0=C2=A0payments_finalization AFTER INSERT ON bdynacom.payments_finalization FOR EACH ROW EXECUTE FUNCTION payments_finalization_force_integrity()
=C2=A0=C2=A0=C2=A0payments_finalization_set_epayment_finalized_tg= AFTER INSERT OR DELETE ON bdynacom.payments_finalization FOR EACH ROW EXECUTE FUNCTION payments_finalization_set_epayment_finalized()
=C2=A0=C2=A0=C2=A0payments_finalization_set_id_tg BEFORE INSERT O= N bdynacom.payments_finalization FOR EACH ROW EXECUTE FUNCTION payments_finalization_set_id()
Inherits: payments_finalization

amantzio@[loc= al]/dynacom=3D# \sf payments_finalization_set_id
CREATE OR REPLACE FUNCTION public.payments_finalization_set_id()
RETURNS trigger
LANGUAGE plpgsql
AS $function$DECLARE
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0nuid INTEGER;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0footmp text;
BEGIN
=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0IF (TG_OP <> 'INS= ERT') THEN
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0RAISE EXCEPTION 'TRIGGER : % called on unsuported op : %. ONLY INSERT IS ALLOWED',TG_NAME, TG_OP;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF;

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0IF (new.id > 0) THEN

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0select pg_advisory_lock= (1010) INTO footmp;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0select COALESCE(max(id)= ,0)+1 INTO nuid FROM payments_finalization;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0select pg_advisory_unlo= ck(1010) INTO footmp;
=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0NEW.id :=3D nuid ;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF;
=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0RETURN NEW;
=C2=A0
END;
$function$
amantzio@[local]/dynacom=3D# \sf payments_finalization_set_epayment_finalized
CREATE OR REPLACE FUNCTION public.payments_finalization_set_epayment_finalized()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
tmp int;
REC RECORD;
BEGIN
=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0IF (TG_WHEN !=3D 'AFTER= ') THEN
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0RAISE EXCEPTION 'TRIGGER : % supports only ON AFTER. Called on unsuported WHEN : %',TG_NAME, TG_WHEN;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF;
=C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0IF (TG_OP =3D 'INSERT')= THEN
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0UPDATE payment p SET isfinalized =3D 't', status = =3D 'FNLZ' WHERE p.year=3DNEW.year AND p.doc_no=3DNEW.doc_no AND p.is_epayment=3D0;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0ELSIF (TG_OP =3D 'DELET= E') THEN
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0UPDATE payment p SET isfinalized =3D 'f', status = =3D 'INSD' WHERE p.year=3DOLD.year AND p.doc_no=3DOLD.doc_no AND p.is_epayment=3D0;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0ELSE
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0/* UPDATE */
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0RAISE EXCEPTION 'TRIGGER : % called on unsuported op : %',TG_NAME, TG_OP;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF;
=C2=A0
/* */
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0RETURN NEW;
END;
$function$
amantzio@[local]/dynacom=3D# \sf payments_finalization_force_integrity =C2=A0
CREATE OR REPLACE FUNCTION public.payments_finalization_force_integrity()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
tmpingroup int;
tmp int;
REC RECORD;
initid INT;
BEGIN
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 IF (NEW.id > 0 AND pg_tri= gger_depth() =3D 1) THEN
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0SET CONSTRAINTS ALL DEFERRED;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0SELECT COALESCE(max(id),0)+1 into initid FROM payments_finalization WHERE sign_list;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0tmp :=3D 0;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0FOR REC IN SELECT year,doc_no FROM payments_finalization WHERE NOT sign_list ORDER BY bank_name,management_company_name,beneficiary_name,year,doc_no DESC LOOP
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0U= PDATE payments_finalization SET id=3Dinitid+tmp WHERE year=3DREC.year AND doc_no=3DREC.doc_no;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0t= mp :=3D tmp + 1;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0END LOOP;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0END IF;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0RETURN NEW;
END;
$function$
amantzio@[local]/dynacom=3D#

How I replicated : (all tested in PgSQL 18beta1)

session A)

amantzio@[loc= al]/dynacom=3D# begin; select txid_current(), pg_backend_pid() ; =C2=A0UPDATE payments_finalization pf set sign_list =3D true where delivered_at::date =3D current_date ;
BEGIN
txid_current | pg_backend_pid =C2=A0
--------------+----------------
=C2=A0=C2=A0=C2=A0117269038 | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A016941
(1 row)

UPDATE 6
amantzio@[local]/dynacom=3D*#

session B)

postgres@[loc= al]/dynacom=3D# begin; select txid_current(), pg_backend_pid() ; =C2=A0UPDATE payments_finalization pf set sign_list =3D true where delivered_at::date =3D
current_date ;
BEGIN
txid_current | pg_backend_pid =C2=A0
--------------+----------------
=C2=A0=C2=A0=C2=A0117269039 | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A016952
(1 row)

(..waits..)

session C)

amantzio@[loc= al]/dynacom=3D# begin; select txid_current() , pg_backend_pid() =C2=A0; INSERT = INTO payments_finalization (year, doc_no, accnt_ukey, inserted_at, bank_name, management_company_name,
beneficiary_name, currency, amount, explanation, card_code, vsl_code, signed_by, delivered_at, group_explanation, ingroup, is_transfer, bank_bic, bank_account, amount_local, creditor_bank_
name, creditor_bank_bic, creditor_bank_account, sign_list) VALUES (2025, 395302, 143392502, '2025-08-13 01:00:00+03', 'CREDIT SUISSE AG', '0006-DYNACOM TANKERS MANAGEMENT LTD', 'AUTUMN SHI
PPING SERVICES LIMITED', 'EUR', 500, 'TRANSFER TO EUROBANK GR / AUTUMN SHIP. E - OCEANIA', NULL, NULL, 'GP', '2025-08-13 11:55:28.359485+03', NULL, false, true, 'CRESCHZH', '08352333263820
01', 500, NULL, 'ERBKGRAA', '0026.0029.27.0200765876', false);
BEGIN
txid_current | pg_backend_pid =C2=A0
--------------+----------------
=C2=A0=C2=A0=C2=A0117269040 | =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A016960
(1 row)

(..waits..)

session A)

amantzio@[loc= al]/dynacom=3D*# rollback ;
ROLLBACK
amantzio@[local]/dynacom=3D#

session B)

UPDATE 6
postgres@[local]/dynacom=3D*#

session C)

ERROR: =C2=A0deadlock detected
DETAIL: =C2=A0Process 16960 waits for ShareLock on transaction 117269039; blocked by process 16952.
Process 16952 waits for ShareLock on transaction 117269040; blocked by process 16960.
HINT: =C2=A0See server log for query details.
CONTEXT: =C2=A0while updating tuple (9611,12) in relation "payments_finalization"
SQL statement "UPDATE payments_finalization SET id=3Dinitid+tmp WHERE year=3DREC.year AND doc_no=3DREC.doc_no"
PL/pgSQL function payments_finalization_force_integrity() line 30 at SQL statement

Two workarounds - solutions I found :

1) If I replace the advisory lock in=C2=A0public.paymen= ts_finalization_set_id() with :

LOCK TABLE payments_finalization IN SHARE ROW EXCLUSIVE MODE;

Then apparently all inserts and updates are serialized , and this seems to do the trick.

2) Also, If i keep the advisory locks (no table locking) , but change all updates so that they perform row level locking , by

a) changing public.paymen= ts_finalization_force_integrity()'s loop=C2=A0 to

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 FOR REC IN SELECT year,doc_no FROM payments_finalization WHERE NOT sign_list ORDER BY bank_name,management_company_name,beneficiary_name,year,doc_no FOR UPDATE LOOP
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0U= PDATE payments_finalization SET id=3Dinitid+tmp WHERE year=3DREC.year AND doc_no=3DREC.doc_no;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0t= mp :=3D tmp + 1;
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0END LOOP;

b) *AND* changing the other session updates to

with tempqry as (select year,doc_no from payments_finalization where delivered_at::date =3D current_date order by
bank_name,management_company_name,beneficiary_name,year,doc_no FOR UPDATE ) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0
UPDATE payments_finalization pf set sign_list =3D true FROM tempqry WHERE pf.year =3D tempqry.year and pf.doc_no =3D tempqry.doc_no ;

also works and no deadlock is caused. (added consistent ordering in all updates as well, dont know if this has any effect). What is certain is that consistent ordering alone without the FOR UPDATE row level lock does not work, still causes the deadlock.

The thing is, that while I think I can solve this particular problem, I still don't understand why it happened.=C2=A0 I have som= e questions and remarks. Regarding the docs on locking (https://www.postgresql.org/docs/18/exp= licit-locking.html) , I found for instance that SHARE UPDATE EXCLUSIVE MODE does not block INSERTs, UPDATEs, whereas SHARE ROW EXCLUSIVE MODE blocks INSERTs and UPDATEs. It would help if those behaviors were documented, and also explain how does default implicit locking via MVCC interact with TABLE level locks. And the most important question is about transactionid-type locks,=C2=A0 while pg_locks's = doc state that locktype can be "transactionid"=C2=A0 reading here : https://www.postgresql.org/docs/18/xact-loc= king.html didn't help much to understand the mechanics behind it, so while technically I see the deadlock pattern between pids 16960 and 16952 and transactions :=C2=A0 117269040 and 117269039 I cannot see where those transactionid-type ShareLock locks are acquired / requested for , in which part of the code / sql and why.

--------------N1ghtWg07LiWt34Js9eqQ0DE--