From: Jesper Pedersen <jesper.pedersen@redhat.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: partitioned tables referenced by FKs
Date: Fri, 29 Mar 2019 11:19:20 -0400
Message-ID: <b57809cd-3f7a-57ed-5085-560a80509824@redhat.com> (raw)
In-Reply-To: <20190328185925.GA23624@alvherre.pgsql>
References: <20190328185925.GA23624@alvherre.pgsql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: jesper.pedersen@redhat.com, alvherre@2ndquadrant.com, Langote_Amit_f8@lab.ntt.co.jp
Subject: Re: partitioned tables referenced by FKs
In-Reply-To: <b57809cd-3f7a-57ed-5085-560a80509824@redhat.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox