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@xxxxxxxxxxx
