Feedback-ID: i76984098:Fastmail
Message-ID: <a38e4e1b-36ae-423a-8b06-0023a7e2630a@aklaver.com>
Date: Sun, 10 Nov 2024 11:07:22 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on
 different tables depending on how attaching is performed
To: user@pidu.dev, pgsql-general@lists.postgresql.org
References: <CAPDhG9aVAt6ed7Lji=G3XhcGRFz0FxHkGiuxmRu=hZgWW9D0OA@mail.gmail.com>
 <254c31c2-ff15-4720-a4fc-f0969d677d72@aklaver.com>
 <CAPDhG9aZMS_nocLJ86bFHHqd1t=QOdztcR0Uy8BC5xpFrdes3A@mail.gmail.com>
 <CAPDhG9bvTyiCG_Z0pmuD_57z7xCbSn2ExkCvAbcgtkmMws9u8A@mail.gmail.com>
 <4ebbbcf5-9c35-4711-bd7c-06a56a31aeff@aklaver.com>
 <CAPDhG9YM2B-P4ozEznSKXas+LGe6yDW4-QE=eKcxtvnis9eOvQ@mail.gmail.com>
 <CAPDhG9YqwVzXFFZ+19XggHYsYaRVROGxoTn=E6GS_J=t5tpAdQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAPDhG9YqwVzXFFZ+19XggHYsYaRVROGxoTn=E6GS_J=t5tpAdQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/a38e4e1b-36ae-423a-8b06-0023a7e2630a%40aklaver.com>
Precedence: bulk

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