Thread: Issue attaching a table to a partitioned table with an auto-referenced foreign key

Hello,

One of our customers has an issue with partitions and foreign keys. He works on a v13, but the issue is also present on v15.

I attach a SQL script showing the issue, and the results on 13.7, 13.9, and 15.1. But I'll explain the script here, and its behaviour on 13.9.

There is one partitioned table, two partitions and a foreign key. The foreign key references the same table:

create table t1 (
  c1 bigint not null,
  c1_old bigint null,
  c2 bigint not null,
  c2_old bigint null,
  primary key (c1, c2)
  )
  partition by list (c1);
create table t1_a   partition of t1 for values in (1);
create table t1_def partition of t1 default;
alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on delete restrict on update restrict;

I've a SQL function that shows me some information from pg_constraints (code of the function in the SQL script attached). Here is the result of this function after creating the table, its partitions, and its foreign key:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(5 rows)

The constraint works great :

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;
psql:ticket15010_v3.sql:34: ERROR:  update or delete on table "t1_a" violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".

This error is normal since the line I want to delete is referenced on the other line.

If I try to detach the partition, it also gives me an error.

alter table t1 detach partition t1_a;
psql:ticket15010_v3.sql:36: ERROR:  removing partition "t1_a" violates foreign key constraint "t1_c1_old_c2_old_fkey1"
DETAIL:  Key (c1_old, c2_old)=(1, 2) is still referenced from table "t1".

Sounds good to me too (well, I'd like it to be smarter and find that the constraint is still good after the detach, but I can understand why it won't allow it).

The pg_constraint didn't change of course:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(5 rows)

Now, I'll delete the whole table contents, and I'll detach the partition:

delete from t1;
alter table t1 detach partition t1_a;

It seems to be working, but the content of pg_constraints is weird:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     |
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(4 rows)

I understand why the ('t1_c1_old_c2_old_fkey1', 't1', 't1_a', 't1_c1_old_c2_old_fkey') tuple has gone but I don't understand why the ('t1_c1_old_c2_old_fkey', 't1_a', 't1', NULL) tuple is still there.

Anyway, I attach the partition:

alter table t1 attach partition t1_a for values in (1);

But pg_constraint has not changed:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(4 rows)

I was expecting to see the fifth tuple coming back, but alas, no.

And as a result, the foreign key doesn't work anymore:

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;

Well, let's truncate the partitioned table, and drop the partition:

truncate t1;
drop table t1_a;

The content of pg_constraint looks good to me:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(3 rows)

Let's create the partition to see if that works better:

create table t1_a   partition of t1 for values in (1);

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(4 rows)

insert into t1 values(1, NULL, 2, NULL);
INSERT 0 1
insert into t1 values(2, 1,    2, 2);
INSERT 0 1
delete from t1 where c1 = 1;
DELETE 1

Nope. I still miss the fifth tuple in pg_constraint, which results in a violated foreign key.

How about dropping the foreign key to create it once more:

truncate t1;
alter table t1 drop constraint t1_c1_old_c2_old_fkey;
select * from show_constraints();
 conname | t | tref | coparent
---------+---+------+----------
(0 rows)

drop table t1_a;
create table t1_a   partition of t1 for values in (1);
alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on delete restrict on update restrict;
select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(5 rows)

I have my fifth row back! And now, the foreign key works as it should:

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;
psql:ticket15010_v3.sql:87: ERROR:  update or delete on table "t1_a" violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".

This is what happens on 13.9 and 15.1. 13.7 shows another weird behaviour, but I guess I'll stop there. Everything is in the attached files.

I'd love to know if I did something wrong, if I didn't understand something, or if this is simply a bug.

Thanks.

Regards.

--

Guillaume.

Quick ping, just to make sure someone can get a look at this issue :)

Thanks.

Le ven. 6 janv. 2023 à 11:07, Guillaume Lelarge <guillaume@lelarge.info> a écrit :

Hello,

One of our customers has an issue with partitions and foreign keys. He works on a v13, but the issue is also present on v15.

I attach a SQL script showing the issue, and the results on 13.7, 13.9, and 15.1. But I'll explain the script here, and its behaviour on 13.9.

There is one partitioned table, two partitions and a foreign key. The foreign key references the same table:

create table t1 (
  c1 bigint not null,
  c1_old bigint null,
  c2 bigint not null,
  c2_old bigint null,
  primary key (c1, c2)
  )
  partition by list (c1);
create table t1_a   partition of t1 for values in (1);
create table t1_def partition of t1 default;
alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on delete restrict on update restrict;

I've a SQL function that shows me some information from pg_constraints (code of the function in the SQL script attached). Here is the result of this function after creating the table, its partitions, and its foreign key:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(5 rows)

The constraint works great :

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;
psql:ticket15010_v3.sql:34: ERROR:  update or delete on table "t1_a" violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".

This error is normal since the line I want to delete is referenced on the other line.

If I try to detach the partition, it also gives me an error.

alter table t1 detach partition t1_a;
psql:ticket15010_v3.sql:36: ERROR:  removing partition "t1_a" violates foreign key constraint "t1_c1_old_c2_old_fkey1"
DETAIL:  Key (c1_old, c2_old)=(1, 2) is still referenced from table "t1".

Sounds good to me too (well, I'd like it to be smarter and find that the constraint is still good after the detach, but I can understand why it won't allow it).

The pg_constraint didn't change of course:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(5 rows)

Now, I'll delete the whole table contents, and I'll detach the partition:

delete from t1;
alter table t1 detach partition t1_a;

It seems to be working, but the content of pg_constraints is weird:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     |
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(4 rows)

I understand why the ('t1_c1_old_c2_old_fkey1', 't1', 't1_a', 't1_c1_old_c2_old_fkey') tuple has gone but I don't understand why the ('t1_c1_old_c2_old_fkey', 't1_a', 't1', NULL) tuple is still there.

Anyway, I attach the partition:

alter table t1 attach partition t1_a for values in (1);

But pg_constraint has not changed:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(4 rows)

I was expecting to see the fifth tuple coming back, but alas, no.

And as a result, the foreign key doesn't work anymore:

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;

Well, let's truncate the partitioned table, and drop the partition:

truncate t1;
drop table t1_a;

The content of pg_constraint looks good to me:

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(3 rows)

Let's create the partition to see if that works better:

create table t1_a   partition of t1 for values in (1);

select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(4 rows)

insert into t1 values(1, NULL, 2, NULL);
INSERT 0 1
insert into t1 values(2, 1,    2, 2);
INSERT 0 1
delete from t1 where c1 = 1;
DELETE 1

Nope. I still miss the fifth tuple in pg_constraint, which results in a violated foreign key.

How about dropping the foreign key to create it once more:

truncate t1;
alter table t1 drop constraint t1_c1_old_c2_old_fkey;
select * from show_constraints();
 conname | t | tref | coparent
---------+---+------+----------
(0 rows)

drop table t1_a;
create table t1_a   partition of t1 for values in (1);
alter table t1 add foreign key (c1_old, c2_old) references t1 (c1, c2) on delete restrict on update restrict;
select * from show_constraints();
        conname         |   t    |  tref  |       coparent        
------------------------+--------+--------+-----------------------
 t1_c1_old_c2_old_fkey  | t1     | t1     |
 t1_c1_old_c2_old_fkey  | t1_a   | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey  | t1_def | t1     | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey1 | t1     | t1_a   | t1_c1_old_c2_old_fkey
 t1_c1_old_c2_old_fkey2 | t1     | t1_def | t1_c1_old_c2_old_fkey
(5 rows)

I have my fifth row back! And now, the foreign key works as it should:

insert into t1 values(1, NULL, 2, NULL);
insert into t1 values(2, 1,    2, 2);
delete from t1 where c1 = 1;
psql:ticket15010_v3.sql:87: ERROR:  update or delete on table "t1_a" violates foreign key constraint "t1_c1_old_c2_old_fkey1" on table "t1"
DETAIL:  Key (c1, c2)=(1, 2) is still referenced from table "t1".

This is what happens on 13.9 and 15.1. 13.7 shows another weird behaviour, but I guess I'll stop there. Everything is in the attached files.

I'd love to know if I did something wrong, if I didn't understand something, or if this is simply a bug.

Thanks.

Regards.

--

Guillaume.

--

Guillaume.