Re: REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it

Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> writes:
> mmm, indeed it seems that some things are our of sync here
> ...
> This confirms that these 60 functions do not have a 'o' (owner) record in
> pg_shdepend, it therefor matches what you seemed to expect: no records in
> pg_shdepend, so "reassign owned" does not do anything.

> Our obvious questions now are:
> - how did we get into this
> and
> - how do we get out

I wonder whether the pg_shdepend data is actually wrong, or just the
indexes on it are at fault. Did you try forcing that query to be done
with a seqscan, or see if reindexing pg_shdepend fixes things up?

The reason I'm wondering is that I've just found a failure mechanism
that could account for significant lossage of index entries for a system
catalog:
http://archives.postgresql.org/pgsql-hackers/2011-04/msg01070.php

To explain your problem that way would require assuming that somebody
was REINDEX'ing pg_shdepend at approximately the same time that somebody
else was rolling back DDL that had modified these same pg_shdepend
entries --- which in this case would probably mean a failed REASSIGN
OWNED for this same user ID. Have you got background tasks that try to
REINDEX everything in sight?

regards, tom lane