Re: pg_migrator and handling dropped columns

Peter Eisentraut wrote:
> Tom Lane wrote:
> >> Is this acceptable to everyone? We could name the option
> >> -u/--upgrade-compatible.
> >
> > If the switch is specifically for pg_upgrade support (enabling this as
> > well as any other hacks we find necessary), which seems like a good
> > idea, then don't chew up a short option letter for it. There should be
> > a long form only.
>
> Note that pg_dump's output is already upgrade compatible. That's what
> pg_dump is often used for after all. I believe what we are after here
> is something like "in-place upgrade compatible" or "upgrade binary
> compatible".
>
> > And probably not even list it in the user documentation.
>
> I think we should still list it somewhere and say it is for use by
> in-place upgrade utilities. It will only confuse people if it is not
> documented at all.

OK, I have completed the patch; attached.

I ran into a little problem, as documented by this comment in
catalog/heap.c:

/*
* Set the type OID to invalid. A dropped attribute's type link
* cannot be relied on (once the attribute is dropped, the type might
* be too). Fortunately we do not need the type row --- the only
* really essential information is the type's typlen and typalign,
* which are preserved in the attribute's attlen and attalign. We set
* atttypid to zero here as a means of catching code that incorrectly
* expects it to be valid.
*/

Basically, drop column zeros pg_attribute.atttypid, and there doesn't
seem to be enough information left in pg_attribute to guess the typid
that, combined with atttypmod, would restore the proper values for
pg_attribute.atttypid and pg_attribute.attalign. Therefore, I just
brute-forced an UPDATE into dump to set the values properly after
dropping the fake TEXT column.

I did a minimal documentation addition by adding something to the
"Notes" section of the manual pages.

Here is what a dump of a table with dropped columns looks like:

--
-- Name: test; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE test (
x integer,
"........pg.dropped.2........" TEXT
);
ALTER TABLE ONLY test DROP COLUMN "........pg.dropped.2........";

-- For binary upgrade, recreate dropped column's length and alignment.
UPDATE pg_attribute
SET attlen = -1, attalign = 'i'
WHERE attname = '........pg.dropped.2........'
AND attrelid =
(
SELECT oid
FROM pg_class
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = CURRENT_SCHEMA)
AND relname = 'test'
);

ALTER TABLE public.test OWNER TO postgres;

--
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. +