Re: Fixing invalid owners on pg_toast tables in 8.3.5

On Fri, 2009-02-20 at 16:35 -0500, Tom Lane wrote:

> Can you see any pattern or common characteristic to the tables whose
> toast pg_type rows failed to change owner? I'm not sure what to look
> for exactly, but similarities in the column contents might be a
> possibility. Also, can you tell if the problem tables were adjacent
> in the dump that was restored?

I fiddled around a while, found the problem, and I can repeat it at
will. It's actually related to type changes we've made over the months
since the restore.

Scenario:

1. Create a table as user A.

create table toaster (
bread varchar(8000)
);

2. Check typowner of toast type

# select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
typowner
----------
16388

3. Alter the field type

alter table toaster ALTER bread type varchar(9000);

4. Check the typowner of toast type

# select typowner from pg_type join pg_class on (typname=relname) where
pg_class.oid in (select reltoastrelid from pg_class where
relname='toaster');
typowner
----------
3555301

The table and toast table owners are not affected:

# select relowner from pg_class where relname='toaster';
relowner
----------
16388

# select relowner from pg_class where oid in (select reltoastrelid from
pg_class where relname='toaster');
relowner
----------
16388

Hope this helps.

Cott