Skip site navigation (1) Skip section navigation (2)

Re: Typed-tables patch broke pg_upgrade

From: Noah Misch <noah(at)leadboat(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Bruce Momjian <bruce(at)momjian(dot)us>,pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Typed-tables patch broke pg_upgrade
Date: 2011-03-31 01:32:08
Message-ID: 20110331013208.GB18119@tornado.gateway.2wire.net (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

Thanks,
nm

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-03-31 01:37:56
Subject: Re: pg_dump --binary-upgrade vs. ALTER TYPE ... DROP ATTRIBUTE
Previous:From: Noah MischDate: 2011-03-31 01:30:52
Subject: Re: pg_dump --binary-upgrade vs. ALTER TYPE ... DROPATTRIBUTE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group