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

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

From: Noah Misch <noah(at)leadboat(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pg_dump --binary-upgrade vs. ALTER TYPE ... DROP ATTRIBUTE
Date: 2011-03-29 21:50:43
Message-ID: 20110329215043.GA11023@tornado.gateway.2wire.net (view raw or flat)
Thread:
Lists: pgsql-hackers
I took a look at the open item concerning typed tables and pg_upgrade:
http://archives.postgresql.org/pgsql-hackers/2011-03/msg00767.php

"pg_dump --binary-upgrade" emits commands to recreate the dropped-column
situation of the database, and it does not currently understand that it must
alter the parent type when the subject is a typed table.  Actually, pg_dump
doesn't handle dropped columns in composite types at all.  pg_upgrade runs fine
on a database that received these commands, but the outcome is wrong:

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)

Fixing that looks clear enough, but the right fix for the typed table issue is
less clear to me.  The pg_attribute tuples for a typed table will include any
attributes dropped from the parent type after the table's creation, but not
those attributes dropped before the table's creation.  Example:

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;

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?

nm

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2011-03-29 21:52:41
Subject: Re: Additional options for Sync Replication
Previous:From: Merlin MoncureDate: 2011-03-29 21:34:16
Subject: Process local hint bit cache

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