Re: [HACKERS] Happy column dropping
От | Tom Lane |
---|---|
Тема | Re: [HACKERS] Happy column dropping |
Дата | |
Msg-id | 4776.948602291@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Happy column dropping (Peter Eisentraut <peter_e@gmx.net>) |
Ответы |
Re: [HACKERS] Happy column dropping
(Peter Eisentraut <peter_e@gmx.net>)
Re: [HACKERS] Happy column dropping (Peter Eisentraut <peter_e@gmx.net>) |
Список | pgsql-hackers |
Peter Eisentraut <peter_e@gmx.net> writes: > With caveats, it is now possible to drop columns from tables. > The implementation is based on copying the old table to a new one minus > the specified column. This procedure changes the oids of everyone > involved, so I was wondering if > a) this is a good reason to tell people to stop using oids as keys, AFAIK there is nothing particularly magic about OIDs. You could perfectly well create the new table with the same OIDs as are in the old table (see COPY WITH OIDS if you are wondering how). User-level stuff like referential integrity triggers might get unhappy, but you're not going to let triggers see what you're doing, right ;-) ? > Is it possible/safe to change to oid of the new pg_class entry back to the > old one? In that case the trouble of moving over all the constraints, etc. > would be half the work. Wrong way to think about it. You should be doing a heap_update of the catalog tuples that need to change, ISTM. You could almost get away with doing it like you describe, except that there is a unique index on pg_class OIDs these days (right, Bruce?) and that index will kick out an error. But heap_update on the original table tuple will work. I think what we may want here is something comparable to what's been discussed recently for VACUUM: build the new table as a new heap file and then rename the physical file into place, without really doing anything to the pg_class tuple --- except of course you'd need to heap_update it to adjust the number-of-attributes field. > 2) how do I find out if the dropped column is referenced in a constraint, > trigger, rule (this is necessary for a correct RESTRICT/CASCADE > implementation) Actually it's worse than that: you need to be prepared to renumber the columns after the dropped one, too. Probably what you will need to do is read in and deparse all the relevant rules and triggers, then reparse them against the updated table schema. Ugly. And no, I have no idea how you even *find* all the relevant rules. (Jan?) > Oh, btw., heaven help you if you try this on tables that are inherited > from. The whole thing should be done inside a recursive routine that applies the same change to all children of the target table. See ALTER TABLE ADD COLUMN for an example. (ADD COLUMN is pretty broken too, since it doesn't preserve consistency of column numbering across child tables --- want to reimplement it in this same style?) regards, tom lane
В списке pgsql-hackers по дате отправления: