pg_migrator and handling dropped columns

bruce wrote:
> Peter Eisentraut wrote:
> > Bruce Momjian wrote:
> > > Now that pg_migrator is BSD licensed, and already in C, I am going to
> > > spend my time trying to improve pg_migrator for 8.4:
> > >
> > > http://pgfoundry.org/projects/pg-migrator/
> >
> > What is the plan now? Get pg_upgrade working, get pg_migrator working,
> > ship pg_migrator in core or separately? Is there any essential
> > functionality that we need to get into the server code before release?
> > Should we try to get dropped columns working? It's quite late to be

I have thought about how to handle dumped columns and would like to get
some feedback on this.

It is easy to find the dropped columns with 'pg_attribute.attisdropped
= true'.

The basic problem is that dropped columns do not appear in the pg_dump
output schema, but still exist in the data files. While the missing
data is not a problem, the dropped column's existence affects all
subsequent columns, increasing their attno values and their placement in
the data files.

I can think of three possible solutions, all involve recreating and
dropping the dropped column in the new schema:

1 modify the pg_dumpall --schema-only output file before
loading to add the dropped column
2 drop/recreate the table after loading to add the dropped
column
3 modify the system tables directly to add the dropped column,
perhaps using pg_depend information

#1 seems like the best option, though it requires parsing the pg_dump
file to some extent. #2 is a problem because dropping/recreating the
table might be difficult because of foreign key relationships, even for
empty tables. #3 seems prone to maintenance requirements every time we
change system object relationships.

Once the dropped column is created in the new server, it can be dropped
to match the incoming data files.

Comments?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +