Обсуждение: help!
I screwed up, and dropped a column when I shouldn't have. I have *not* vacuumed this DB yet. Is there any catalog mucking I can do to bring it back? LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
--On Monday, November 17, 2003 19:36:08 -0600 Larry Rosenman <ler@lerctr.org> wrote: > I screwed up, and dropped a column when I shouldn't have. > > I have *not* vacuumed this DB yet. > > Is there any catalog mucking I can do to bring it back? Actually, I got lucky. pg_catalog.pg_attribute is what I needed to muck with, and I got the column I needed back, and then translated. > > LER > > > -- > Larry Rosenman http://www.lerctr.org/~ler > Phone: +1 972-414-9812 E-Mail: ler@lerctr.org > US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Wait for confirmation from at least one other developer perhaps, buy you can try this: 1. Set attisdropped to false for the attribute 2. Set the atttypid back to whatever the oid of the type of that column is/was (Compare to an undropped similar column) 3. Use ALTER TABLE/SET NOT NULL on the column if it was originally NOT NULL 4. Set attstattarget to -1 to re-enable stat gathering 5. Rename the column (attname field) back to whatever it was. 6. Re set the default on the column 7. Done. (I think) By the way, vacuuming doesn't necessarily have much to do with it - updating rows does though. I'm not 100% sure what will happen exactly when you follow the steps above (reversing what's in RemoveAttributeById). Chris Larry Rosenman wrote: > I screwed up, and dropped a column when I shouldn't have. > > I have *not* vacuumed this DB yet. > > Is there any catalog mucking I can do to bring it back? > > LER > >
--On Tuesday, November 18, 2003 09:59:32 +0800 Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote: > Wait for confirmation from at least one other developer perhaps, buy you > can try this: > > 1. Set attisdropped to false for the attribute > > 2. Set the atttypid back to whatever the oid of the type of that column > is/was (Compare to an undropped similar column) > > 3. Use ALTER TABLE/SET NOT NULL on the column if it was originally NOT > NULL > > 4. Set attstattarget to -1 to re-enable stat gathering > > 5. Rename the column (attname field) back to whatever it was. > > 6. Re set the default on the column > > 7. Done. (I think) > > By the way, vacuuming doesn't necessarily have much to do with it - > updating rows does though. I'm not 100% sure what will happen exactly > when you follow the steps above (reversing what's in RemoveAttributeById). I did the following, and was able to do what I needed to do: update pg_catalog.pg_attribute set attname='instance1',attisdropped='f',attypid=1048 where attrelid=2356153 and attname='........pg.dropped.6........'; and then re-do the stuff that my rt needed, and then re-drop the column. (basically the RT docs blew one character in a field name, and I wasn't paying attention :-) ) LER > > Chris > > Larry Rosenman wrote: > >> I screwed up, and dropped a column when I shouldn't have. >> >> I have *not* vacuumed this DB yet. >> >> Is there any catalog mucking I can do to bring it back? >> >> LER >> >> -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749