public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
Date: Sun, 10 Nov 2024 11:07:22 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPDhG9YqwVzXFFZ+19XggHYsYaRVROGxoTn=E6GS_J=t5tpAdQ@mail.gmail.com>
References: <CAPDhG9aVAt6ed7Lji=G3XhcGRFz0FxHkGiuxmRu=hZgWW9D0OA@mail.gmail.com>
	<[email protected]>
	<CAPDhG9aZMS_nocLJ86bFHHqd1t=QOdztcR0Uy8BC5xpFrdes3A@mail.gmail.com>
	<CAPDhG9bvTyiCG_Z0pmuD_57z7xCbSn2ExkCvAbcgtkmMws9u8A@mail.gmail.com>
	<[email protected]>
	<CAPDhG9YM2B-P4ozEznSKXas+LGe6yDW4-QE=eKcxtvnis9eOvQ@mail.gmail.com>
	<CAPDhG9YqwVzXFFZ+19XggHYsYaRVROGxoTn=E6GS_J=t5tpAdQ@mail.gmail.com>

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
[email protected]

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox