Thread: Can not drop partition if exist foreign keys

Hi all.
There are two common practice to drop partition from partitioned table: just drop or detach-drop. But simple drop don't
workif exist foreign key. Example script attached. 


$ psql -p 5416 -a -f test.sql
\setenv PSQL_EDITOR 'vim'
\setenv PSQL_EDITOR_LINENUMBER_ARG '+'
\set ON_ERROR_ROLLBACK 'interactive'
\set ON_ERROR_STOP 'on'
--\set SHOW_CONTEXT 'always'
\set PROMPT1 '%[%033[38;5;'`echo $PROMPT_COLOR`'m%]%x%n@%m:%>/%/\n%R%# %[%033[m%]'
\set PROMPT2 '%[%033[38;5;'`echo $PROMPT_COLOR`'m%]%R%# %[%033[m%]'
BEGIN;
BEGIN
CREATE TABLE parent (
    id    int primary key
) PARTITION BY RANGE (id);
CREATE TABLE
CREATE TABLE parent_0 PARTITION OF parent
    FOR VALUES FROM (0) TO (100);
CREATE TABLE
CREATE TABLE children (
    id    int primary key references parent(id)
) PARTITION BY RANGE (id);
CREATE TABLE
CREATE TABLE children_0 PARTITION OF children
    FOR VALUES FROM (0) TO (100);
CREATE TABLE
DROP TABLE children_0;
DROP TABLE
DROP TABLE parent_0;
psql:test.sql:15: ERROR:  cannot drop table parent_0 because other objects depend on it
DETAIL:  constraint children_id_fkey on table children depends on table parent_0
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Looked like a bug.

On 2023-Oct-11, Олег Самойлов wrote:

> There are two common practice to drop partition from partitioned
> table: just drop or detach-drop. But simple drop don't work if exist
> foreign key. Example script attached.

Yeah.  Detach it first, then you should be able to drop it.

> psql:test.sql:15: ERROR:  cannot drop table parent_0 because other objects depend on it
> DETAIL:  constraint children_id_fkey on table children depends on table parent_0
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
> 
> Looked like a bug.

We tried to make DROP work, but we didn't find a way.  Patches welcome.

-- 
Álvaro Herrera

On Wed, 2023-10-11 at 14:08 +0300, Олег Самойлов wrote:
> There are two common practice to drop partition from partitioned table: just drop or detach-drop.
> But simple drop don't work if exist foreign key. Example script attached.

That is working as designed.  You cannot detach a partition of a table if a
foreign key points to it.

Create the foreign key constraints between the partitions instead.

Yours,
Laurenz Albe

On Thu, 2023-10-12 at 17:56 +0300, Олег Самойлов wrote: 
> 11.10.2023, 14:20, "Laurenz Albe" <laurenz.albe@cybertec.at>:
> > On Wed, 2023-10-11 at 14:08 +0300, Олег Самойлов wrote:
> > >  There are two common practice to drop partition from partitioned table: just drop or detach-drop.
> > >  But simple drop don't work if exist foreign key. Example script attached.
> >
> > That is working as designed. You cannot detach a partition of a table if a
> > foreign key points to it.
>
> Nope, of cause any can detach a partition of a empty table even if a foreign key points to it.

You are right, and I was wrong.  You can detach partitions from tables with a
foreign key pointing to them.

Yours,
Laurenz Albe