Re: Changing owner of pg_toast tables

On Thu, Jan 29, 2009 at 12:29:07PM -0500, Tom Lane wrote:
> 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.

Actually, pgadmin3 may have given me an error on that operation (which I
ignored, it did what I wanted, I thought), I believe it was something
like 'OID not found'.

I have to do something similar for another role so I'll pay more
attention then.

> 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.

Thanks, I managed to clear out the offending dependencies. relowner was
actually set correctly, but the pg_shdepend records were wrong.

--
Mark
http://www.lambic.co.uk