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@leadboat.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