Re: partitioned tables referenced by FKs

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