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 1tADzt-00ERab-Uz for pgsql-general@arkaria.postgresql.org; Sun, 10 Nov 2024 19:53:21 +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 1tADzr-008Z2U-DA for pgsql-general@arkaria.postgresql.org; Sun, 10 Nov 2024 19:53:19 +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 1tADzq-008YcI-KK for pgsql-general@lists.postgresql.org; Sun, 10 Nov 2024 19:53:19 +0000 Received: from eu-shark1.inbox.eu ([195.216.236.81]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tADzj-001B4P-6d for pgsql-general@lists.postgresql.org; Sun, 10 Nov 2024 19:53:17 +0000 Received: from eu-shark1.inbox.eu (localhost [127.0.0.1]) by eu-shark1-out.inbox.eu (Postfix) with ESMTP id 4Xmjyq3137zJjjC2 for ; Sun, 10 Nov 2024 21:53:07 +0200 (EET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=signed-inbox.eu; s=p20220330; t=1731268387; x=1731270187; bh=Kf+nICyonYaJevThRVTfR84MtBBoXpS6CZMpkP3E19s=; h=References:In-Reply-To:Reply-To:From:Date:Message-ID:Subject:To: Cc:Content-Type:X-ESPOL:From:Date:To:Cc:Message-ID:Subject: Reply-To; b=TgXGWqwmXByUNPFXTNDsLVl9EJgO0iL0/Rbta33BjT3wz0VRnAf8zpIvY1IY6kG2w SvN6uyDiE7gj5PlJmMNw5lg+XNfL88na5ND1CU8KLC/1ak5cGfyN19gZ4QvYmZoETZ E6ut6Nv37JRQWezLMWhw1tp7vig+xdtznAYFgP+Q= Received: from eu-shark1.inbox.eu (localhost [127.0.0.1]) by eu-shark1-in.inbox.eu (Postfix) with ESMTP id 4Xmjyq1rnGzJjjBS for ; Sun, 10 Nov 2024 21:53:07 +0200 (EET) Received: from eu-shark1.inbox.eu ([127.0.0.1]) by localhost (eu-shark1.inbox.eu [127.0.0.1]) (spamfilter, port 35) with ESMTP id Fzt4EGH_alAc for ; Sun, 10 Nov 2024 21:53:05 +0200 (EET) Received: from mail.inbox.eu (eu-pop1 [127.0.0.1]) by eu-shark1-in.inbox.eu (Postfix) with ESMTP id 4Xmjyn5lxrzJjjBt for ; Sun, 10 Nov 2024 21:53:05 +0200 (EET) Received: by mail-ed1-f41.google.com with SMTP id 4fb4d7f45d1cf-5c9693dc739so5592370a12.3 for ; Sun, 10 Nov 2024 11:53:05 -0800 (PST) X-Gm-Message-State: AOJu0YzNVvl6P4vdNalQ5vFXaugloVKxa8p276caipg0JdDKNwnGkc+/ 7my3zevbIoL01ZZ/sXF7Lft3+S+f14oljjw7wLIQqaSjBkWtWtLFN/miUFb2Q4N1czMe2TBrKY8 I+juXyQJnRA4NK9rM1Xk/VbnFxRc= X-Google-Smtp-Source: AGHT+IHqw/TA7VfNEbjGZI7As1noq/4hf0GNt0VfGh9mQSRvLL+1/Geuzvs++yMhBPBL+dvt3ASOz5wlRVOdxl2oJRc= X-Received: by 2002:a05:6402:35d2:b0:5cf:3d14:b3e1 with SMTP id 4fb4d7f45d1cf-5cf3d14b49dmr1702071a12.3.1731268381930; Sun, 10 Nov 2024 11:53:01 -0800 (PST) MIME-Version: 1.0 References: <254c31c2-ff15-4720-a4fc-f0969d677d72@aklaver.com> <4ebbbcf5-9c35-4711-bd7c-06a56a31aeff@aklaver.com> In-Reply-To: Reply-To: user@pidu.dev From: user Date: Sun, 10 Nov 2024 20:52:52 +0100 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed To: adrian.klaver@aklaver.com Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ea843f0626945495" X-Virus-Scanned: OK X-ESPOL: +d1m8elSeE2pi0LYI3bcBQ8ppy9XXu3t5eO7zG4nkS6/LG7bPkkR List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ea843f0626945495 Content-Type: text/plain; charset="UTF-8" Thank you for an answer! So reparenting of a constraint required that additional lock. I made some measurements and can see that even that reparenting (and additional lock) is required, the time it takes to make the attach is smaller than when the foreign constraint hasn't been created beforehand. So, to summarise, there is a tradeoff. 1. Create constraint before attach, but during attach additional tables will be locked with AccessExculive. The time of an attach will be minimal (for large tables it is still tens of ms in our db) but there is a higher chance of deadlocks (as more tables locked with restrictive locks) 2. Just proceed with attach. The constraint will be created because the parent table has the constraint in its definition. Because no reparenting is required, no additional exclusive lock is held. But this process will take more time to finish as a constraint is created from scratch. Are these the only options? Basically I want to add partitions dynamically to db while app is running. I want to minimise the duration of "attach" command but also the amount of locks held on several tables at once (to avoid deadlocks). Once again, thanks for an answer. It is now clear to me why such behaviour occurs. Regards On Sun, 10 Nov 2024, 20:07 Adrian Klaver, wrote: > On 11/10/24 05:18, user wrote: > > Hello, > > Sorry for nagging, but I would really like to find some answers. > > So, to reiterate. Experiment done as follows: > > """""""""""""""""""""' > > 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) > > 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); > > """"""""""""""""""""""""" > > Then, when we open a transaction and try to attach: > > """"""""""""""""""""""""" > > BEGIN; > > ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr') > > keep the transaction running.. > > """""""""""""""""""""""" > > Once we check a locks, we will see that there is AccessExclusiveLock on > > table refs. > > """""" > > select relname, mode > > from pg_locks l > > join pg_class c on (relation = c.oid) > > join pg_namespace nsp on (c.relnamespace = nsp.oid); > > """"" > > My questions are: > > 1. Why is postgres adding again a constraint? Can't it detect that > > foreign key already exists? I want to avoid locking partitioned table > > for too long. > > I see, I missed it my previous post: > > alter table films_partition ADD CONSTRAINT fk_did FOREIGN KEY (did) > REFERENCES refs (id); > > \d films_partition > > Foreign-key constraints: > "fk_did" FOREIGN KEY (did) REFERENCES refs(id) > > ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr') > > \d films_partition > > Foreign-key constraints: > TABLE "films" CONSTRAINT "films_did_fkey" FOREIGN KEY (did) > REFERENCES refs(id) > > The FK constraint changes from being > > films_partition <--> refs > > to > > films <--> refs > > > 2. Even when attach is adding a foreign key again, why is there > > AccessExclusiveLock on refs table? foreign key constraint addition does > > not require it. > > > https://pglocks.org/?pgcommand=ALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD) > < > https://pglocks.org/?pgcommand=ALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD) > > > > 3. If I repeat the steps listed above, but do not add foreign key > > manually, then attach partition does not hold AccessExclusive lock on > > table refs. It still needs to add a foreign key, as "films" table has > > that constraint. Why is the AccessExclusive lock missing from "refs" > > table now? > > Best guess because the FK is changing referencing table and in: > > ~//src/backend/commands/tablecmds.c > > "CloneFkReferencing > > For each FK constraint of the parent relation in the given list, find an > equivalent constraint in its partition relation that can be reparented; > if one cannot be found, create a new constraint in the partition as its > child." > > > [...] > > addFkRecurseReferencing(wqueue, > fkconstraint, > partRel, > pkrel, > indexOid, > constrOid, > numfks, > confkey, > mapped_conkey, > conpfeqop, > conppeqop, > conffeqop, > numfkdelsetcols, > confdelsetcols, > false, /* no old check exists */ > AccessExclusiveLock, > insertTriggerOid, > updateTriggerOid); > > > > > > > > > > > > Regards! > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --000000000000ea843f0626945495 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you for an answer!=C2=A0
So repare= nting of a constraint required that additional lock.=C2=A0
I made some measurements and can see that even that reparenting (and= additional lock) is required, the time it takes to make the attach is smal= ler than when the foreign constraint hasn't been created beforehand.=C2= =A0

So, to summarise, th= ere is a tradeoff.
1. Create constraint before attac= h, but during attach additional tables will be locked with AccessExculive. = The time of an attach will be minimal=C2=A0 (for large tables it is still t= ens of ms in our db) but there is a higher chance of deadlocks (as more tab= les locked with restrictive locks)
2. Just proceed w= ith attach. The constraint will be created because the parent table has the= constraint in its definition. Because no reparenting is required, no addit= ional exclusive lock is held. But this process will take more time to finis= h as a constraint is created from scratch.=C2=A0
Are these the only options?
Basically I want to add partitions dynamically to db while app is running.= I want to minimise the duration of "attach" command but also the= amount of locks held on several tables at once (to avoid deadlocks).
=

Once again, thanks for an ans= wer. It is now clear to me why such behaviour occurs.=C2=A0
Regards

On Sun, 10 Nov 2024, 20:07 Adrian Klaver, <adrian.klaver@aklaver.com> wr= ote:
On 11/10/24 05:18, user wrote:=
> Hello,
> Sorry for nagging, but I would really like to find some answers.
> So, to reiterate. Experiment done as follows:
> """""""""""&quo= t;"""""""""'
> CREATE TABLE refs (
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 id integer primary key,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 did integer
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 );
> CREATE TABLE films (
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 id integer,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 code char(5) ,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 title varchar(40) NOT NULL,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 did integer NOT NULL references refs(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 = =C2=A0 partition by list (code);
>
> insert into refs values (5, 5)
> create table films_partition (LIKE films INCLUDING ALL)
> insert into films_partition values (1, 'dr', 'musician'= ;,5)
> alter table films_partition add constraint check_code check (code =3D = 'dr');
> alter table films_partition ADD CONSTRAINT fk_did FOREIGN KEY (did) > REFERENCES refs (id);
> """""""""""&quo= t;""""""""""""&= quot;
> Then, when we open a transaction and try to attach:
> """""""""""&quo= t;""""""""""""&= quot;
>=C2=A0 =C2=A0BEGIN;
> ALTER TABLE films ATTACH PARTITION films_partition for values in ('= ;dr')
> keep the transaction running..
> """""""""""&quo= t;""""""""""""<= br> > Once we check a locks, we will see that there is AccessExclusiveLock o= n
> table refs.
> """"""
> select relname, mode
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from pg_= locks l
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 join pg_class c on (relation =3D c.oid)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 join pg_namespace nsp on (c.relnamespace =3D nsp.oid);
> """""
> My questions are:
> 1. Why is postgres adding again a constraint? Can't it detect that=
> foreign key already exists? I want to avoid locking partitioned table =
> for too long.

I see, I missed it my previous post:

alter table films_partition ADD CONSTRAINT fk_did FOREIGN KEY (did)
REFERENCES refs (id);

\d films_partition

Foreign-key constraints:
=C2=A0 =C2=A0 =C2=A0"fk_did" FOREIGN KEY (did) REFERENCES refs(id= )

ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr&#= 39;)

\d films_partition

Foreign-key constraints:
=C2=A0 =C2=A0 =C2=A0TABLE "films" CONSTRAINT "films_did_fkey= " FOREIGN KEY (did)
REFERENCES refs(id)

The FK constraint changes from being

films_partition <--> refs

to

films <--> refs

> 2. Even when attach is adding a foreign key again, why is there
> AccessExclusiveLock on refs table? foreign key constraint addition doe= s
> not require it.
> https:= //pglocks.org/?pgcommand=3DALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD) <https= ://pglocks.org/?pgcommand=3DALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD)>
> 3. If I repeat the steps listed above, but do not add foreign key
> manually, then attach partition does not hold AccessExclusive lock on =
> table refs. It still needs to add a foreign key, as "films" = table has
> that constraint. Why is the AccessExclusive lock missing from "re= fs"
> table now?

Best guess because the FK is changing referencing table and in:

~//src/backend/commands/tablecmds.c

"CloneFkReferencing

For each FK constraint of the parent relation in the given list, find an equivalent constraint in its partition relation that can be reparented;
if one cannot be found, create a new constraint in the partition as its
child."


[...]

addFkRecurseReferencing(wqueue,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0fkconstraint,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0partRel,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0pkrel,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0indexOid,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0constrOid,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0numfks,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0confkey,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0mapped_conkey,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0conpfeqop,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0conppeqop,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0conffeqop,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0numfkdelsetcols,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0confdelsetcols,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0false,=C2=A0 /* no old check e= xists */
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AccessExclusiveLock,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0insertTriggerOid,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0updateTriggerOid);








>
> Regards!


--
Adrian Klaver
adrian.klaver@aklaver.com

--000000000000ea843f0626945495--