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;
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