modifying views

I've been thinking a little more about modifying views:

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00694.php

AFAICS, out of all of the things that ALTER TABLE is capable of doing,
there are only four that make any sense in the context of views:

(1) add a column
(2) drop a column
(3) change the type of an existing column
(4) renaming a column

The tricky part of course is that the underlying query has to change
along with the type definition. It's fairly obvious what the
semantics of dropping a column should be, but the other cases are
trickier. New columns could possibly be returned as NULL (pending a
subsequent CREATE OR REPLACE VIEW), but changing the type of a column
just seems crazy as a standalone operation. The bigger problem though
is that I'm pretty sure that ALTER TABLE <viewname> ADD COLUMN <name>
<type> is not a command that anyone is eager to be typing. What we
really want to be able to do is a CREATE OR REPLACE VIEW command and
have the system worry about what columns needed to be added, dropped,
or retyped, and giving us an error if we've tried to (for example)
remove a column that some other view depends on. (Apart from CREATE
OR REPLACE VIEW, the only operation that looks independently useful to
me is RENAME COLUMN.)

Unfortunately, it looks to me like a fully general implementation of
this feature would be Really Hard, because a CREATE OR REPLACE VIEW
command, beyond attempting to add, drop, or retype columns, could also
attempt to reorder them. A cursory inspection of the situation
suggests this would require modifying the attnum values in
pg_attribute, which looks like a nightmare. (Anyone want to argue
otherwise? Tom? I'd love to be wrong about this...) However, it
might be possible to allow some or all of the following: (1) dropping
columns from anywhere, (2) adding new columns at the end, and (3)
changing the type of existing columns. (I think (1) and (2) are
clearly more useful than (3).)

Thoughts?

...Robert

On Jul 29, 2008, at 9:12 PM, Robert Haas wrote:
> Unfortunately, it looks to me like a fully general implementation of
> this feature would be Really Hard, because a CREATE OR REPLACE VIEW
> command, beyond attempting to add, drop, or retype columns, could also
> attempt to reorder them. A cursory inspection of the situation
> suggests this would require modifying the attnum values in
> pg_attribute, which looks like a nightmare.

FWIW, there is desire to be able to re-order columns within real
tables, too. But before that can happen we need to divorce
presentation order from on-page order (which is actually desirable
for other reasons), but that's an awfully big task that no one's
taken on.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

On Thu, Aug 14, 2008 at 05:57:26PM -0500, Decibel! wrote:
> FWIW, there is desire to be able to re-order columns within real
> tables, too. But before that can happen we need to divorce
> presentation order from on-page order (which is actually desirable
> for other reasons), but that's an awfully big task that no one's
> taken on.

Actually, ISTR that someone posted a patch and it was rejected on the
basis that it made the backend coding too confusing and would cause
bugs to creep in (by using the wrong position during coding). I don't
buy the argument though, since the end goal is to have logical position
!= physical position, so I don't see how bugs could survive very long.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout wrote:
> On Thu, Aug 14, 2008 at 05:57:26PM -0500, Decibel! wrote:
>
>> FWIW, there is desire to be able to re-order columns within real
>> tables, too. But before that can happen we need to divorce
>> presentation order from on-page order (which is actually desirable
>> for other reasons), but that's an awfully big task that no one's
>> taken on.
>>
>
> Actually, ISTR that someone posted a patch and it was rejected on the
> basis that it made the backend coding too confusing and would cause
> bugs to creep in (by using the wrong position during coding). I don't
> buy the argument though, since the end goal is to have logical position
> != physical position, so I don't see how bugs could survive very long.
>
>
>

NO, IIRC it was rejected because it didn't implement what we wanted,
namely Tom's three-number scheme (immutable id, plus mutable logical and
physical order).

cheers

andrew