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
Mon, 17 Nov 2003 19:36:08 -0600
Larry Rosenman <ler@lerctr.org>
help!
--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
Mon, 17 Nov 2003 19:52:52 -0600
Larry Rosenman <ler@lerctr.org>
Re: help!
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
> 
> 


Tue, 18 Nov 2003 09:59:32 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: help!
--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
Mon, 17 Nov 2003 20:01:50 -0600
Larry Rosenman <ler@lerctr.org>
Re: help!