Table partition with primary key in 11.3
CREATE TABLE public.test1 ( x1 integer NOT NULL, x2 integer NOT NULL, CONSTRAINT test1_pkey PRIMARY KEY (x1) INCLUDE(x2) ) PARTITION BY RANGE (x2); This query works in 11.1 but fails in 11.3 with messages: ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "test1" lacks column "x2" which is part of the partition key. SQL state: 0A000
Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
** forwarding to mailing list, forgot to add header
Thanks for answering.
I think one misunderstanding happened.
The parent table has the foreign key constraint.
So attach partition will add this constraint for table being attached. (How
this compares to foreign keys not being considered, not sure).
Why is it that attach_partition does not require exclusive lock when
creating a constraint automatically?
What is more, you have provided a quote that states the lock is needed
because the table needs to be checked that all entries comply with the NEW
constraint.
Well it is not new when I manually create it before I attach.
It is new when I run attach command without previous manual constraint
creation, but then the lock is not created.
On Sun, 20 Oct 2024, 18:23 Adrian Klaver, wrote:
> On 10/20/24 04:31, user wrote:
> > Hello,
> > I was reading all the tips that could make the attach partition
> > operation seamless.
> > https://www.postgresql.org/docs/current/ddl-partitioning.html
> > <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
Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
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!
Re: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
Hello Adrian,
My apology for answering so late.
Indeed I have not splitted my transactions correctly in my first example.
BUT, if you change the syntaxt so that attach is performed in its own
transaction, result is the same. Try it out!
Also you have told me that I am seeing ExclusiveLock on refs table because
it is from foreign key constraint. Well this is incorrect, foreign key does
not take the ExclusiveLock in any situation.
https://pglocks.org/?pgcommand=ALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD)
Could you try again with only attach being in its own transaction? You
should reproduce it.
Regards
On Mon, 21 Oct 2024 at 20:31, Adrian Klaver
wrote:
>
>
> On 10/21/24 1:40 AM, user wrote:
> > ** forwarding to mailing list, forgot to add header
> >
> >
> > Thanks for answering.
> > I think one misunderstanding happened.
> > The parent table has the foreign key constraint.
> > So attach partition will add this constraint for table being attached.
> > (How this compares to foreign keys not being considered, not sure).
> >
> > Why is it that attach_partition does not require exclusive lock when
> > creating a constraint automatically?
> >
> > What is more, you have provided a quote that states the lock is needed
> > because the table needs to be checked that all entries comply with the
> > NEW constraint.
> >
> > Well it is not new when I manually create it before I attach.
> > It is new when I run attach command without previous manual constraint
> > creation, but then the lock is not created.
>
> 1) Case 1
>
>
> test=# \d films
> Partitioned table "public.films"
> Column | Type | Collation | Nullable | Default
> +---+---+--+-
> id | integer | | |
> code | character(5) | | |
> title | character varying(40) | | not null |
> did| integer | | not null |
> Partition key: LIST (code)
> Foreign-key constraints:
> "films_did_fkey" FOREIGN KEY (did) REFERENCES refs(id)
> Number of partitions: 0
>
> create table films_partition (LIKE films INCLUDING ALL);
> CREATE TABLE
>
> test=# \d+ films_partition
> Table "public.films_partition"
> Column | Type | Collation | Nullable | Default |
> Storage | Compression | Stats target | Description
>
> +---+---+--+-+--+-+--+-
> id | integer | | | |
> plain| | |
> code | character(5) | | | |
> extended | | |
> title | character varying(40) | | not null | |
> extended | | |
> did| integer | | not null | |
> plain| | |
> Access method: heap
>
>
> ALTER TABLE films ATTACH PARTITION films_partition for values in ('dr');
> ALTER TABLE
>
> test=# \d+ films
> Partitioned table
> "public.films"
> Column | Type | Collation | Nullable | Default |
> Storage | Compression | Stats target | Description
>
> +---+---+--+-+--+-+--+-
> id | integer | | | |
> plain| | |
> code | character(5) | | | |
> extended | | |
> title | character varying(40) | | not null | |
> extended | | |
> did| integer | | not null | |
> plain| | |
> Partition key: LIST (code)
> Foreign-key constraints:
> "films_did_fkey" FOREIGN KEY (did) REFERENCES refs(id)
> Partitions: films_partition FOR VALUES IN ('dr ')
>
> test=# \d+ films_partition
> Table "public.films_partition"
> Column | Type | Collation | Nullable | Default |
> Storage | Compression | Stats target | Description
>
> +---+---+--+-+--+-+--+-
> id | integer | | | |
> plain| | |
> code | charac
Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
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)
> >
> >
Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
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.
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)
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?
Regards!
-- Forwarded message -
From: user
Date: Fri, 1 Nov 2024 at 15:35
Subject: Re: Fwd: Postgres attach partition: AccessExclusive lock set on
different tables depending on how attaching is performed
To:
Cc:
Hello Adrian,
My apology for answering so late.
Indeed I have not splitted my transactions correctly in my first example.
BUT, if you change the syntaxt so that attach is performed in its own
transaction, result is the same. Try it out!
Also you have told me that I am seeing ExclusiveLock on refs table because
it is from foreign key constraint. Well this is incorrect, foreign key does
not take the ExclusiveLock in any situation.
https://pglocks.org/?pgcommand=ALTER%20TABLE%20ADD%20FOREIGN%20KEY%20(CHILD)
Could you try again with only attach being in its own transaction? You
should reproduce it.
Regards
On Mon, 21 Oct 2024 at 20:31, Adrian Klaver
wrote:
>
>
> On 10/21/24 1:40 AM, user wrote:
> > ** forwarding to mailing list, forgot to add header
> >
> >
> > Thanks for answering.
> > I think one misunderstanding happened.
> > The parent table has the foreign key constraint.
> > So attach partition will add this constraint for table being attached.
> > (How this compares to foreign keys not being considered, not sure).
> >
> > Why is it that attach_partition does not require exclusive lock when
> > creating a constraint automatically?
> >
> > What is more, you have provided a quote that states the lock is needed
> > because the table needs to be checked that all entries comply with the
> > NEW constraint.
> >
> > Well it is not new when I manually create it before I attach.
> > It is new when I run attach command without previous manual constraint
> > creation, but then the lock is not created.
>
> 1) Case 1
>
>
> test=# \d films
> Partitioned table "public.films"
> Column | Type | Collation | Nullable | Default
> +---+---+--+-
> id | integer | | |
> code | character(5) | | |
> title | character varying(40) | | not null |
>
barman with postgres server/s
Does anyone know a best practice when it comes to installing barman? Would barman be on its own system or does it make sense to have it running on a cascaded postgres server? I'm just getting started with it so I'd like to see how others have implemented its use in a variety of different implementations of postgres servers or server. Thank you
