Thread: BUG #15718: DROP TABLE fails if it's partitioned by a key of a deleted enum
BUG #15718: DROP TABLE fails if it's partitioned by a key of a deleted enum
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15718 Logged by: Alex Email address: pisarenco.a@gmail.com PostgreSQL version: 11.2 Operating system: All Linux Description: Code to replicate the issue: https://pastebin.com/rc8q35Qj This happened to our ETL processes that re-create the staging data on each run. Enums are defining partition keys for some tables, that then flow into tables in another schema. When the schema containing that enum gets dropped, the columns that use it are also dropped. If those columns are also partition keys, it ends up in a situation where it's not possible to drop the parent table. Failure with ERROR: cache lookup failed for type 0. Issue has been observed at least since Postgresql 10, but just got successfully replicated on minimal code.
Re: BUG #15718: DROP TABLE fails if it's partitioned by a key of adeleted enum
From
Alvaro Herrera
Date:
On 2019-Mar-28, PG Bug reporting form wrote: > Code to replicate the issue: > https://pastebin.com/rc8q35Qj Please paste the code in the email. We don't like external references. > This happened to our ETL processes that re-create the staging data on each > run. Enums are defining partition keys for some tables, that then flow into > tables in another schema. When the schema containing that enum gets dropped, > the columns that use it are also dropped. Sounds like we're forgetting to add a dependency on the datatype of the partition key. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: BUG #15718: DROP TABLE fails if it's partitioned by a key of adeleted enum
From
Alexandru Pisarenco
Date:
DROP SCHEMA IF EXISTS a CASCADE;
CREATE SCHEMA a;
DROP SCHEMA IF EXISTS b CASCADE;
CREATE SCHEMA b;
-- Create the culprit, in a separate schema
CREATE TYPE a.my_enum AS ENUM (
'option1',
'option2'
);
CREATE TABLE b.whatever (
some_int INT,
something a.my_enum,
some_text TEXT
) PARTITION BY LIST (something);
CREATE TABLE b.whatever_1 PARTITION OF b.whatever FOR VALUES IN ('option1');
CREATE TABLE b.whatever_2 PARTITION OF b.whatever FOR VALUES IN ('option2');
-- No more schema, no more enum, no more partition key
DROP SCHEMA a CASCADE;
-- Nnope!
DROP SCHEMA b CASCADE;
-- Maybe this?
DROP TABLE b.whatever_1 CASCADE;
DROP TABLE b.whatever_2 CASCADE;
-- it worked. Final touch?
DROP TABLE b.whatever CASCADE;
-- Nope.
-- What's going on?
SELECT * FROM pg_attribute
WHERE
attrelid IN (SELECT c.oid FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname LIKE 'whatever%' AND n.nspname='b')
AND attnum>0;
--Bad solution
UPDATE pg_attribute
SET atttypid='int'::REGTYPE::INT
WHERE
attrelid IN (
SELECT
c.oid
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE
c.relname LIKE 'whatever%'
AND n.nspname='b'
)
AND attnum>0
AND atttypid=0
AND attlen=4;
-- Works now
DROP SCHEMA b CASCADE;
CREATE SCHEMA a;
DROP SCHEMA IF EXISTS b CASCADE;
CREATE SCHEMA b;
-- Create the culprit, in a separate schema
CREATE TYPE a.my_enum AS ENUM (
'option1',
'option2'
);
CREATE TABLE b.whatever (
some_int INT,
something a.my_enum,
some_text TEXT
) PARTITION BY LIST (something);
CREATE TABLE b.whatever_1 PARTITION OF b.whatever FOR VALUES IN ('option1');
CREATE TABLE b.whatever_2 PARTITION OF b.whatever FOR VALUES IN ('option2');
-- No more schema, no more enum, no more partition key
DROP SCHEMA a CASCADE;
-- Nnope!
DROP SCHEMA b CASCADE;
-- Maybe this?
DROP TABLE b.whatever_1 CASCADE;
DROP TABLE b.whatever_2 CASCADE;
-- it worked. Final touch?
DROP TABLE b.whatever CASCADE;
-- Nope.
-- What's going on?
SELECT * FROM pg_attribute
WHERE
attrelid IN (SELECT c.oid FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname LIKE 'whatever%' AND n.nspname='b')
AND attnum>0;
--Bad solution
UPDATE pg_attribute
SET atttypid='int'::REGTYPE::INT
WHERE
attrelid IN (
SELECT
c.oid
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE
c.relname LIKE 'whatever%'
AND n.nspname='b'
)
AND attnum>0
AND atttypid=0
AND attlen=4;
-- Works now
DROP SCHEMA b CASCADE;
On Thu, Mar 28, 2019 at 6:04 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
On 2019-Mar-28, PG Bug reporting form wrote:
> Code to replicate the issue:
> https://pastebin.com/rc8q35Qj
Please paste the code in the email. We don't like external references.
> This happened to our ETL processes that re-create the staging data on each
> run. Enums are defining partition keys for some tables, that then flow into
> tables in another schema. When the schema containing that enum gets dropped,
> the columns that use it are also dropped.
Sounds like we're forgetting to add a dependency on the datatype of the
partition key.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services