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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
