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

Hi Tom,

Op maandag 18 april 2011, schreef Tom Lane:
> Hmmm .... look into pg_shdepend to see if there are entries linking
> those functions to an owner.

mmm, indeed it seems that some things are our of sync here

the following is coming from the production database, thus after the 'reassign
from A to postgres' was run

****
1.
****

SELECT
n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_userbyid(p.proowner) as "Owner"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_get_userbyid(p.proowner) != 'postgres';

This returns all 60 functions that were not reassigned, 'Owner' here still is
user 'A'.

****
2.
****

select
s.deptype,
p.proname,
pg_catalog.pg_get_userbyid(p.proowner) as proc_owner,
pg_catalog.pg_get_userbyid(s.refobjid) as sh_dep_owner
from
pg_shdepend s
full outer join pg_proc p on p.oid = s.objid
where
(
coalesce(
(select datname from pg_database where oid = s.dbid) = 'megafox'
and s.classid::regclass::text = 'pg_proc'
and pg_catalog.pg_get_userbyid(refobjid) != 'postgres', false)
or
coalesce(
pg_catalog.pg_get_userbyid(p.proowner) != 'postgres', false))
order by
s.deptype, p.proname;

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

How is it possible that a function had a pg_catalog.pg_proc.proowner other
than postgres while there are no corresponding records in pg_shdepend? Fyi,
the last major upgrade (for which a pg_restore was done) was in july 2009.

****
3.
****

The query above returns 10 other suspicious rows, suspicious to us at least.
These rows are about functions which according to pg_catalog.pg_proc.proowner
are owned by postgres (the last-but-one column), while in pg_shdepend they
still have an 'o' record with owner 'A' (the last column).

So again, pg_catalog.pg_proc.proowner and pg_shdepend are not in sync.

-----

For what its worth, nothing special was noticed about postgresql nor the
hardware. Postgresql for us has been and still is rock stable for almost ten
years now ;)

We did try some scenarios of changing ownership of things, but were not able
to generate a situation with pg_proc.proowner not in sync with pg_shdepend. I
guess this was to be expected, since a newly restored dump also does not show
the problem, it's only in the production database, which ofcourse has moved
through a number of minor upgrades without a restore.

--
Best,

Frank.