Re: REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it - Mailing list pgsql-general

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.