Re: Typed-tables patch broke pg_upgrade

On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Wed, Mar 30, 2011 at 07:50:12PM +0300, Peter Eisentraut wrote:
>> On tor, 2011-02-10 at 06:31 +0200, Peter Eisentraut wrote:
>> > > ERROR:  cannot drop column from typed table
>> > >
>> > > which probably is because test_type2 has a dropped column.
>> >
>> > It should call
>> >
>> > ALTER TYPE test_type2 DROP ATTRIBUTE xyz CASCADE;
>> >
>> > instead.  That will propagate to the table.
>>
>> Here is a patch that addresses this problem.
>
> This only works when exactly one typed table uses each composite type having
> dropped columns.  With zero users, the placeholder column never gets dropped.
> Actually, it happens to work for >1 user, but only because ALTER TYPE mistakenly
> only touches the first table-of-type:
>
> create type t as (x int, y int);
> create table is_a of t;
> create table is_a2 of t;
> alter type t drop attribute y cascade, add attribute z int cascade;
> \d is_a
>     Table "public.is_a"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  x      | integer |
>  z      | integer |
> Typed table of type: t
> \d is_a2
>     Table "public.is_a2"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  x      | integer |
>  y      | integer |
> Typed table of type: t
>
> Might be a simple fix; looks like find_typed_table_dependencies() only grabs the
> first match.  Incidentally, this led me to notice that you can hang a typed
> table off a table row type.  ALTER TABLE never propagates to such typed tables,
> allowing them to get out of sync:
>
> create table t (x int, y int);
> create table is_a of t;
> create table is_a2 of t;
> alter table t drop y, add z int;
> \d is_a
>     Table "public.is_a"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  x      | integer |
>  y      | integer |
> Typed table of type: t
>
> Perhaps we should disallow the use of table row types in CREATE TABLE ... OF?
>
>> It looks like Noah Misch might have found another problem in this area.
>> We'll have to investigate that.
>
> Your bits in dumpCompositeType() are most of what's needed to fix that, I think.

Where are we on this?

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