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

On Wed, Mar 30, 2011 at 09:37:56PM -0400, Robert Haas wrote:
> On Wed, Mar 30, 2011 at 9:30 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> >> 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.
> >
> > Why extend ALTER TABLE ... INHERIT? ?I would have guessed independent syntax.
>
> I just didn't feel the need to invent something new, but we could if
> someone would rather.
>
> >> 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.
> >
> > Agreed; it's also an independently-useful capability to have.
>
> Yep.

Implemented as attached. The first patch just adds the ALTER TABLE subcommands
to attach and detach a table from a composite type. A few open questions
concerning typed tables will probably yield minor changes to these subcommands.
I implemented them to be agnostic toward the outcome of those decisions.

The second patch updates pg_dump to use those new subcommands. It's based
significantly on Peter's recent patch. The new bits follow pg_dump's design for
table inheritance.

I tested pg_upgrade of these previously-mentioned test cases:

create type t as (x int, y int);
create table has_a (tcol t);
insert into has_a values ('(1,2)');
table has_a; -- (1,2)
alter type t drop attribute y cascade, add attribute z int cascade;
table has_a; -- (1,)
table has_a; -- after pg_upgrade: (1,2)

create type t as (x int, y int);
create table is_a of t;
alter type t drop attribute y cascade;
create table is_a2 of t;
select * from pg_attribute where attrelid = 'is_a'::regclass;
select * from pg_attribute where attrelid = 'is_a2'::regclass;

create type unused as (x int);
alter type unused drop attribute x;

I also tested a regular dump+reload of the regression database, and a pg_upgrade
of the same. The latter failed further along, due (indirectly) to this failure
to create a TOAST table:

create table p ();
create table ch () inherits (p);
alter table p add column a text;
select oid::regclass,reltoastrelid from pg_class where oid::regclass IN ('p','ch');
insert into ch values (repeat('x', 1000000));

If I "drop table a_star cascade" in the regression database before attempting
pg_upgrade, it completes cleanly.

nm