pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bad copy-n-paste on character conversion fix - how screwed am I?
4+ messages / 2 participants
[nested] [flat]

* Bad copy-n-paste on character conversion fix - how screwed am I?
@ 2005-05-03 01:16  C. Bensend <benny@bennyvision.com>
  0 siblings, 1 reply; 4+ messages in thread

From: C. Bensend @ 2005-05-03 01:16 UTC (permalink / raw)
  To: pgsql-admin


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"




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Bad copy-n-paste on character conversion fix - how screwed am I?
@ 2005-05-03 01:47  Tom Lane <tgl@sss.pgh.pa.us>
  parent: C. Bensend <benny@bennyvision.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2005-05-03 01:47 UTC (permalink / raw)
  To: C. Bensend <benny@bennyvision.com>; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Bad copy-n-paste on character conversion fix - how
@ 2005-05-03 01:58  C. Bensend <benny@bennyvision.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: C. Bensend @ 2005-05-03 01:58 UTC (permalink / raw)
  To: pgsql-admin


> 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"




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Bad copy-n-paste on character conversion fix - how
@ 2005-05-03 03:48  Tom Lane <tgl@sss.pgh.pa.us>
  parent: C. Bensend <benny@bennyvision.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2005-05-03 03:48 UTC (permalink / raw)
  To: C. Bensend <benny@bennyvision.com>; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-05-03 03:48 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-03 01:16 Bad copy-n-paste on character conversion fix - how screwed am I? C. Bensend <benny@bennyvision.com>
2005-05-03 01:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-03 01:58   ` Re: Bad copy-n-paste on character conversion fix - how C. Bensend <benny@bennyvision.com>
2005-05-03 03:48     ` Re: Bad copy-n-paste on character conversion fix - how Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox