Re: Fixing invalid owners on pg_toast tables in 8.3.5

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(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997