Hi Alvaro,
On 3/28/19 2:59 PM, Alvaro Herrera wrote:
I ended up revising the dependencies that we give to the constraint in
the partition -- instead of giving it partition-type dependencies, we
give it an INTERNAL dependency. Now when you request to drop the
partition, it says this:
create table pk (a int primary key) partition by list (a);
create table fk (a int references pk);
create table pk1 partition of pk for values in (1);
alvherre=# drop table pk1;
ERROR: cannot drop table pk1 because other objects depend on it
DETAIL: constraint fk_a_fkey on table fk depends on table pk1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
If you do say CASCADE, the constraint is dropped. Not really ideal (I
would prefer that the drop is prevented completely), but at least it's
not completely bogus. If you do "DROP TABLE pk", it works sanely.
Also, if you DETACH the partition that pg_depend row goes away, so a
subsequent drop of the partition works sanely.
Fixed the psql issue pointed out by Amit L too.
Could expand a bit on the change to DEPENDENCY_INTERNAL instead of
DEPENDENCY_PARTITION_PRI / DEPENDENCY_PARTITION_SEC ?
If you run "DROP TABLE t2_p32 CASCADE" the foreign key constraint is
removed from all of t1.
-- ddl.sql --
CREATE TABLE t1 (i1 INT PRIMARY KEY, i2 INT NOT NULL) PARTITION BY HASH
(i1);
CREATE TABLE t2 (i1 INT PRIMARY KEY, i2 INT NOT NULL) PARTITION BY HASH
(i1);
\o /dev/null
SELECT 'CREATE TABLE t1_p' || x::text || ' PARTITION OF t1
FOR VALUES WITH (MODULUS 64, REMAINDER ' || x::text || ');'
from generate_series(0,63) x;
\gexec
\o
\o /dev/null
SELECT 'CREATE TABLE t2_p' || x::text || ' PARTITION OF t2
FOR VALUES WITH (MODULUS 64, REMAINDER ' || x::text || ');'
from generate_series(0,63) x;
\gexec
\o
ALTER TABLE t1 ADD CONSTRAINT fk_t1_i2_t2_i1 FOREIGN KEY (i2) REFERENCES
t2(i1);
INSERT INTO t2 (SELECT i, i FROM generate_series(1, 1000) AS i);
INSERT INTO t1 (SELECT i, i FROM generate_series(1, 1000) AS i);
ANALYZE;
-- ddl.sql --
Detaching the partition for DROP seems safer to me.
Thanks in advance !
Best regards,
Jesper
