pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCan not drop partition if exist foreign keys
5+ messages / 3 participants
[nested] [flat]
* Can not drop partition if exist foreign keys
@ 2023-10-11 11:08 Олег Самойлов <splarv@ya.ru>
0 siblings, 2 replies; 5+ messages in thread
From: Олег Самойлов @ 2023-10-11 11:08 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi all.
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.
$ 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.=
Attachments:
test.sql (application/octet-stream, 373B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can not drop partition if exist foreign keys
@ 2023-10-11 11:17 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Олег Самойлов <splarv@ya.ru>
1 sibling, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2023-10-11 11:17 UTC (permalink / raw)
To: Олег Самойлов <splarv@ya.ru>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can not drop partition if exist foreign keys
@ 2023-10-11 11:20 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Олег Самойлов <splarv@ya.ru>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2023-10-11 11:20 UTC (permalink / raw)
To: Олег Самойлов <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can not drop partition if exist foreign keys
@ 2023-10-12 14:56 Олег Самойлов <splarv@ya.ru>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Олег Самойлов @ 2023-10-12 14:56 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
<div> </div><div> </div><div>11.10.2023, 14:20, "Laurenz Albe" <laurenz.albe@cybertec.at>:</div><blockquote><p>On Wed, 2023-10-11 at 14:08 +0300, Олег Самойлов wrote:</p><blockquote> There are two common practice to drop partition from partitioned table: just drop or detach-drop.<br /> But simple drop don't work if exist foreign key. Example script attached.</blockquote><p><br />That is working as designed. You cannot detach a partition of a table if a<br />foreign key points to it.</p></blockquote><div>Nope, of cause any can <span style="background-color:transparent">detach a partition of a empty table even if a foreign key points to it.</span></div><div>The problem is other. Documented that there are two variants how drop partition. Just drop or detach then drop. Both work on a simple partition, but only the second variant works in this case.</div>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can not drop partition if exist foreign keys
@ 2023-10-13 06:05 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Олег Самойлов <splarv@ya.ru>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2023-10-13 06:05 UTC (permalink / raw)
To: Олег Самойлов <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-10-13 06:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-11 11:08 Can not drop partition if exist foreign keys Олег Самойлов <splarv@ya.ru>
2023-10-11 11:17 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-10-11 11:20 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-12 14:56 ` Олег Самойлов <splarv@ya.ru>
2023-10-13 06:05 ` Laurenz Albe <laurenz.albe@cybertec.at>
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