Re: Changing owner of pg_toast tables

Mark Styles <postgres(at)lambic(dot)co(dot)uk> writes:
> On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote:
>> I guess the interesting question to me is what happened to the tables
>> those toast tables are/were attached to? They should have the same
>> owners as their parent tables.

> They did have the same owner, I changed the owner to postgres so I could
> drop the role, but the corresponding pg_toast tables did not change.

Well, that's just weird. Can you reproduce such a behavior? In my
tests 8.1 definitely does change the toast table's owner along with the
parent. One can imagine that step failing, but if so the whole
ALTER OWNER transaction should roll back.

As for getting out of your immediate problem, I think what you'd need to
do is manually adjust the pg_class.relowner fields for those toast
tables, and then get rid of the pg_shdepend entries that claim they
depend on the old role. (You don't need to put back new entries
claiming they depend on postgres.) But I'd sure like to find out what
happened. We've heard a few reports before of toast tables not getting
dropped when their parents were, and I wonder if this is related.

regards, tom lane