Re: Fixing invalid owners on pg_toast tables in 8.3.5 - Mailing list pgsql-general

From Joshua D. Drake
Subject Re: Fixing invalid owners on pg_toast tables in 8.3.5
Date
Msg-id 1235157930.31546.119.camel@jd-laptop.pragmaticzealot.org
Whole thread Raw
In response to Fixing invalid owners on pg_toast tables in 8.3.5  (Cott Lang <cott@internetstaff.com>)
Responses Re: Fixing invalid owners on pg_toast tables in 8.3.5
List pgsql-general
On Fri, 2009-02-20 at 12:01 -0700, Cott Lang wrote:
> We're running 8.3.5 on RHEL4 x86_64.
>
> We removed a user yesterday and were greeted with warnings from pg_dump
> this morning. :)
>
> pg_dump: WARNING: owner of data type "pg_toast_80075" appears to be
> invalid
>
> The usual archives and google searches produced mainly 8.0 and earlier
> incidents and suggested resolving this by re-creating a user with that
> sysid.  Since you can no longer specify a SYSID when creating a user
> (despite what \h in psql says), we gave ALTER type/table a shot with no
> luck.
>

Something isn't right:

postgres=# create user foobar superuser;
CREATE ROLE
postgres=# set role foobar;
SET
postgres=# create type typetext AS (bar text);
CREATE TYPE
postgres=# select current_user;
 current_user
--------------
 foobar
(1 row)

postgres=# set role postgres;
SET
postgres=# drop type typetest;
ERROR:  type "typetest" does not exist
postgres=# drop user foobar;
ERROR:  role "foobar" cannot be dropped because some objects depend on
it
DETAIL:  owner of type typetext


> The owner of the actual table and index is correct, only the type has an
> invalid owner. I have thus far avoided the temptation to try a manual
> update...
>
> Is there a recommended procedure for resolving this safely?
>

You can use alter type to change the owner of the type to a valid user
but see above. Something is wrong.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


pgsql-general by date:

Previous
From: Cott Lang
Date:
Subject: Fixing invalid owners on pg_toast tables in 8.3.5
Next
From: Tom Lane
Date:
Subject: Re: Fixing invalid owners on pg_toast tables in 8.3.5