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

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

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@2ndquadrant.com>
wrote: