Re: pg_dump --binary-upgrade vs. ALTER TYPE ... DROP ATTRIBUTE

On Tue, Mar 29, 2011 at 5:50 PM, Noah Misch <noah@leadboat.com> wrote:
> I took a look at the open item concerning typed tables and pg_upgrade:
> http://archives.postgresql.org/pgsql-hackers/2011-03/msg00767.php

Thanks!

> [ helpful summary of problem clipped ]

> To reproduce that catalog state, the dump would need to create the type, create
> all typed tables predating the DROP ATTRIBUTE, and finally create typed tables
> postdating the DROP ATTRIBUTE.  That implies an extra dump entry for the DROP
> ATTRIBUTE with the appropriate dependencies to compel that order of events.  Is
> there a better way?

I think so.  We have this same problem with regular table inheritance,
and the way we fix it is to jigger the tuple descriptor for the child
table so that it matches what we need, and THEN attach it to the
parent:

CREATE TABLE child (   a integer,   "........pg.dropped.2........" INTEGER /* dummy */
);

-- For binary upgrade, recreate inherited column.
UPDATE pg_catalog.pg_attribute
SET attislocal = false
WHERE attname = 'a' AND attrelid = 'child'::pg_catalog.regclass;

-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = 4, attalign = 'i', attbyval = false
WHERE attname = '........pg.dropped.2........' AND attrelid = 'child'::pg_catalog.regclass;
ALTER TABLE ONLY child DROP COLUMN "........pg.dropped.2........";

-- For binary upgrade, set up inheritance this way.
ALTER TABLE ONLY child INHERIT parent;

I think we need to do something similar here -- use the same hack
shown above to get the dropped column into the right state, and then
jigger things so that the child is a typed table associated with the
parent.  Perhaps it would be reasonable to extend ALTER TABLE .. [NO]
INHERIT to accept a type name as the final argument.  If used in this
way, it converts a typed table into a regular table or visca versa.
We could also do it with a direct catalog change, but there are some
dependencies that would need to be frobbed, which makes me a bit
reluctant to go that way.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company