Re: DROP COLUMN Proposal

Hannu Krosing <hannu(at)tm(dot)ee> writes:
>> The DROP COLUMN command does nothing but set the attisdropped of a column to
>> true,

> This will probably require a full lock on system tables to avoid nasty
> border conditions when updating caches.

AFAICS it's no different from any other ALTER TABLE command: exclusive
lock on the table being modified is necessary and sufficient.

>> Now the dropped column will be invisible. As you update rows, etc. the
>> space will be reclaimed in the table as NULLs are put in where the old value
>> used to be.

> You probably have to set DEFAULT for this column to NULL to achieve it.

Right, get rid of any default.

> And dropping / modifying indexes and constraints that reference the
> deleted column .

This part should fall out of Rod Taylor's pg_depend stuff pretty easily.
We still need to debate about the behavior, though. If for example there
is a unique index on column B, do you need "DROP B CASCADE" to get rid
of it, or is "DROP B RESTRICT" good enough? Does your answer change if
the unique index is on two columns (A,B)? I'm not real sure where the
boundary is between attributes of the column (okay to drop as part of
the column) and independent objects that ought to be treated as
requiring CASCADE.

>> A new command, something like "ALTER TABLE tab RECLAIM;" will be able to be
>> run on tables.

> Could it not just be an oprion to "VACUUM table "?

I thought the same. It certainly doesn't belong with ALTER TABLE...

>> a. What happens with TOASTed columns that are dropped?

> What happens currently when rows with TOASTed cols are deleted/updated ?

No different from anything else, AFAICS.

The nice thing about this implementation approach is that most of the
backend need not be aware of deleted columns. There are a few places in
the parser (probably few enough to count on one hand) that will have to
explicitly check for and reject references to dropped columns, and
you're done. The rewriter, planner and executor are blissfully ignorant
of the whole deal.

You might have some problems with code in psql, pg_dump, or other
clients that examines the system tables; it'd have to be fixed to pay
attention to attisdropped as well.

>> c. Do we need an 'attisreclaimed' field in pg_attribute to indicate that a
>> field as been fully reclaimed, or do we just let people run it whenever they
>> want (even if it has no effect other than to waste time)?

Don't think we need it.

regards, tom lane