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 1t2UA0-004L8i-Ai for pgsql-general@arkaria.postgresql.org; Sun, 20 Oct 2024 11:31:49 +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 1t2U9w-00ElwS-IP for pgsql-general@arkaria.postgresql.org; Sun, 20 Oct 2024 11:31:44 +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 1t2U9v-00Elt6-Qp for pgsql-general@lists.postgresql.org; Sun, 20 Oct 2024 11:31:44 +0000 Received: from eu-shark2.inbox.eu ([195.216.236.82]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t2U9s-001psY-1L for pgsql-general@lists.postgresql.org; Sun, 20 Oct 2024 11:31:42 +0000 Received: from eu-shark2.inbox.eu (localhost [127.0.0.1]) by eu-shark2-out.inbox.eu (Postfix) with ESMTP id 4XWbqs6BV4z4d7MW for ; Sun, 20 Oct 2024 14:31:37 +0300 (EEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=signed-inbox.eu; s=p20220330; t=1729423897; x=1729425697; bh=m3uOmfa/lvH7Nh43mPnXKweSMIzyrMcuPYh9A9hNi38=; h=Reply-To:From:Date:Message-ID:Subject:To:Content-Type:X-ESPOL: From:Date:To:Cc:Message-ID:Subject:Reply-To; b=ZTfBedv8prmzXk8u0RzHwJs0ON1mRDevTV4ghAmQxHL+CkWDfhfMruCOQZJChgiaI O7u9I+VPFh7GNozuQvtTiHo2jcZ6cpJ7rikWr8z8gp5qgXUZXbtnXn378VVCzbcKrX n8/a/zle2es292ysKTgd2aQzRE5MjIWyK7d4SkgA= Received: from eu-shark2.inbox.eu (localhost [127.0.0.1]) by eu-shark2-in.inbox.eu (Postfix) with ESMTP id 4XWbqs5fDDz4d7Lr for ; Sun, 20 Oct 2024 14:31:37 +0300 (EEST) Received: from eu-shark2.inbox.eu ([127.0.0.1]) by localhost (eu-shark2.inbox.eu [127.0.0.1]) (spamfilter, port 35) with ESMTP id Xz17DIJEs8uN for ; Sun, 20 Oct 2024 14:31:37 +0300 (EEST) Received: from mail.inbox.eu (eu-pop1 [127.0.0.1]) by eu-shark2-in.inbox.eu (Postfix) with ESMTP id 4XWbqs3KSjz4d7Ll for ; Sun, 20 Oct 2024 14:31:37 +0300 (EEST) Received: by mail-ed1-f41.google.com with SMTP id 4fb4d7f45d1cf-5c935d99dc5so3696226a12.1 for ; Sun, 20 Oct 2024 04:31:37 -0700 (PDT) X-Gm-Message-State: AOJu0Yy+rMyZzPCp/dKN0GOc8BKakzPc5Q1orkXWIzhW7/8aJR5l1XNq E+lGQeaxWImAvC9OHRmaAWmmnQLe1KIasp7cmusrSaxm/AJOOEp53gZwcFvQhIMYD745rvf5Sjd JO9ySadRmlXnzwNntR3QGeFvW/8I= X-Google-Smtp-Source: AGHT+IHQexFNAKSY1ATF0C3DJHMGLbL4AVITu0BIg8a8NldYCDAR7gczvWxas/aWsf9Z1ELrax4QZbWq7C3v5LEbI2A= X-Received: by 2002:a05:6402:3547:b0:5c3:d2bc:2f64 with SMTP id 4fb4d7f45d1cf-5ca0af8e9c6mr6502139a12.35.1729423893725; Sun, 20 Oct 2024 04:31:33 -0700 (PDT) MIME-Version: 1.0 Reply-To: user@pidu.dev From: user Date: Sun, 20 Oct 2024 13:31:23 +0200 X-Gmail-Original-Message-ID: Message-ID: Subject: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000da13c80624e6e097" X-Virus-Scanned: OK X-ESPOL: +d1m8elSaUCp3xu8AySeBBpV3CdIQJSF9uW62QNRmWX4MSaaekkR List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000da13c80624e6e097 Content-Type: text/plain; charset="UTF-8" Hello, I was reading all the tips that could make the attach partition operation seamless. https://www.postgresql.org/docs/current/ddl-partitioning.html There is a mention about check constraint that could be places before the attach process. But to minimise the time when AccessExclusive lock is held on my table, I wanted to push it further and also add indexes and foreign keys BEFORE the attach command is invoked. And here is a problem. When I run the attach command without foreign keys being present beforehand on a table, there is only AccessExclusive lock on a table I attach partition to. BUT if my table to-be-attached has a foreign key constraint already, then the referenced table will get the ExclusiveLock! I do not understand why is it needed, the constraint already exists... The reproduction: ( Postgres Version 14 ) CREATE TABLE refs ( id integer primary key, did integer ); CREATE TABLE films ( id integer, code char(5) , title varchar(40) NOT NULL, did integer NOT NULL references refs(id) ) partition by list (code); insert into refs values (5, 5) create table films_partition (LIKE films INCLUDING ALL) case 1: films_partition does not have a foreign key added before the attach BEGIN; insert into films_partition values (1, 'dr', 'musician',5) alter table films_partition add constraint check_code check (code = 'dr'); ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr') keep the transaction running... check the locks: select relname, mode from pg_locks l join pg_class c on (relation = c.oid) join pg_namespace nsp on (c.relnamespace = nsp.oid); films relname, ShareUpdateExclusiveLock mode films_partition relname, AccessShareLock mode films_partition relname, RowExclusiveLock mode films_partition relname, ShareRowExclusiveLock mode films_partition relname, AccessExclusiveLock mode refs relname, AccessShareLock mode refs relname, RowShareLock mode refs relname, ShareRowExclusiveLock mode No AccessExclusive lock on "refs" table! case 2: films_partition does have the foreign key contrain BEGIN; insert into films_partition values (1, 'dr', 'musician',5) alter table films_partition add constraint check_code check (code = 'dr'); alter table films_partition ADD CONSTRAINT fk_did FOREIGN KEY (did) REFERENCES refs (id); ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr') keep the transaction running... check the locks: films relname, ShareUpdateExclusiveLock mode films_partition relname, AccessShareLock mode films_partition relname, RowExclusiveLock mode films_partition relname, ShareRowExclusiveLock mode films_partition relname, AccessExclusiveLock mode refs relname, AccessShareLock mode refs relname, RowShareLock mode refs relname, ShareRowExclusiveLock mode refs relname, AccessExclusiveLock mode There is AccessExclusiveLock on "refs" table! Conclusion I really don't want the "attach partition" to take too much time, so I want to have all the constraints added before it is run. And indeed, the time is reduced. But this additional lock now increases the chance of deadlocks, as AccessExclusive locks are grabbed on many tables referenced by foreing keys. Is there anything I can do better? Whi is it that attach_partition adds a foreign key without additional AccessExclusive lock, but this lock is required when the constrint already exists? Regards! --000000000000da13c80624e6e097 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello,




<= /div>
No AccessExclusive lock on "refs" table!

case 2: films_partition d= oes have the foreign key contrain
=C2=A0BEGIN;
=
insert into films_partition values (1, 'dr', '= ;musician',5)
alter table films_partition add co= nstraint check_code check (code =3D 'dr');
a= lter table films_partition ADD CONSTRAINT fk_did FOREIGN KEY (did) REFERENC= ES refs (id);
ALTER TABLE films ATTACH PARTITION fil= ms_partition for values in ('dr')
keep the t= ransaction running...

ch= eck the locks:

films rel= name, ShareUpdateExclusiveLock mode
films_partition = relname, AccessShareLock mode
films_partition relnam= e, RowExclusiveLock mode
films_partition relname, Sh= areRowExclusiveLock mode
films_partition relname, Ac= cessExclusiveLock mode
refs relname, AccessShareLock= mode
refs relname, RowShareLock mode
refs relname, ShareRowExclusiveLock mode
r= efs relname, AccessExclusiveLock mode

There is AccessExclusiveLock on "refs" table!
=

Conclusion
I really don't want the "attach partition" to take too m= uch time, so I want to have all the constraints added before it is run. And= indeed, the time is reduced. But this additional lock now increases the ch= ance of deadlocks, as AccessExclusive locks are grabbed on many tables refe= renced by foreing keys. Is there anything I can do better? Whi is it that a= ttach_partition adds a foreign key without additional AccessExclusive lock,= but this lock is required when the constrint already exists?

Regards!

<= /div>

--000000000000da13c80624e6e097--