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

Hi,

Op woensdag 20 april 2011, schreef Tom Lane:
> 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

Just did by setting enable_indexscan to false and verifying that all is used
are seq_scans by running explain first.

Both queries return the exact same result, so it seems the indexes are not the
problem in this case?

> or see if reindexing pg_shdepend fixes things up?

Didn't do that now, given the above result, but should you prefer it, just let
me know.

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

Nope, nothing like that running in the background. We basically never reindex
manually. The only DDL related stuff that does get used a fair bit, is
creating / using / dropping temp table stuff. During the period since the last
major postgresql update, numerous functions have been updated on numerous
moments in time, but this is mainly done during maintenance windows. Recently
we started a cleanup to 'correct wrong ownership and/or permissions', which
basically was what made this show up.

--
Best,

Frank.