Re: Fixing invalid owners on pg_toast tables in 8.3.5

Cott Lang <cott(at)internetstaff(dot)com> writes:
> I found a handful of other pg_types with an unusual owner and in every
> case, the toast type is owned by the user that created the database via
> full pg_restore some months ago.

> pg_restore should have created the table as the user running it, and
> immediately done an ALTER TABLE .. OWNER TO. So does the pg_toast type
> take ownership from the owner of the originating table, or the user id
> inserting data that forces a toast table creation ?

> Either way, it's inconsistent - it's only a very small percentage of
> tables that would have had toast tables created at restore time that are
> affected.

The toast table would be created immediately. ALTER TABLE OWNER is
supposed to update ownership of any attached toast table, too, and that
should propagate to the pg_type row as well. There isn't supposed to be
any way for a toast table to have different ownership from its parent,
let alone a composite-type pg_type row have different ownership from the
associated pg_class row.

We have seen a small number of reports that sometimes toast tables fail
to track parent-table updates, fail to get dropped when the parent is,
etc. Nobody knows how to reproduce that though :-(. I was hoping you
might find some tidbit that would provide the missing link, but no luck
yet.

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?

regards, tom lane