Re: BUG #15718: DROP TABLE fails if it's partitioned by a key of a deleted enum

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 Thu, Mar 28, 2019 at 6:04 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)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
>