Thread: 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@decibel.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@svana.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