Thread: Bad copy-n-paste on character conversion fix - how screwed am I?

Hey folks,

   I just had a copy-n-paste mishap on one of my databases, while
fixing the character conversion problem.

   The following erroneous command was entered:

UPDATE pg_proc SET proacl = '{=}'
;

   .. instead of the correct one (missing the WHERE clause).  It
updated 1747 rows - oh crap.

   This is 8.0.2 running on OpenBSD 3.5-STABLE.  Is there a way to
fix what I've just borked?  I'm not even real sure what damage I've
just done...

   Any help would be appreciated.  :)

Benny


--
"You come from a long line of scary women."     -- Ranger, "Three To
                                                   Get Deadly"

"C. Bensend" <benny@bennyvision.com> writes:
>    The following erroneous command was entered:
> UPDATE pg_proc SET proacl = '{=}'
> ;
>    .. instead of the correct one (missing the WHERE clause).  It
> updated 1747 rows - oh crap.

Always a good idea to do this kind of thing inside a BEGIN block ;-)

As far as the system functions are concerned, you can just set the
proacl column to NULL (ie, default) and that'll be fine for everything
except the character conversion functions.

As far as user-defined functions are concerned, NULL probably works for
most of them too, but you would want to consult a recent backup to see
if any were not like that.

            regards, tom lane

> Always a good idea to do this kind of thing inside a BEGIN block ;-)

Yeah.  That was the _second_ thing that came to mind.  ;)

> As far as the system functions are concerned, you can just set the
> proacl column to NULL (ie, default) and that'll be fine for everything
> except the character conversion functions.

So, just to be sure:

1)  Start a %(&@#*(&% transaction
2)  Set the column to NULL for all rows, undoing my stupidity
3)  Issue the CORRECT SQL statement, and that will take care of the
    character conversion problem
4)  COMMIT said transaction after verifying that I'm not doing
    something stupid _again_

Is this correct?

> As far as user-defined functions are concerned, NULL probably works for
> most of them too, but you would want to consult a recent backup to see
> if any were not like that.

Happily, I am a novice at this, and I do not have any user-defined
functions.  Very simple database.  :)

Thank you so much!

Benny


--
"You come from a long line of scary women."     -- Ranger, "Three To
                                                   Get Deadly"

"C. Bensend" <benny@bennyvision.com> writes:
> So, just to be sure:

> 1)  Start a %(&@#*(&% transaction
> 2)  Set the column to NULL for all rows, undoing my stupidity
> 3)  Issue the CORRECT SQL statement, and that will take care of the
>     character conversion problem
> 4)  COMMIT said transaction after verifying that I'm not doing
>     something stupid _again_

> Is this correct?

Right.

All this thrashing will have bloated pg_proc a bit (like a factor of
three...) so you might want to do a "VACUUM FULL pg_proc" after the dust
has settled.  But that's a bit on the anal-retentive side considering
that pg_proc isn't really all that big.  I offer it up merely for
educational purposes.

            regards, tom lane