Thread: REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it
L.S. # select version(); version --------------------------------------------------------------------------------------- PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.4, 64-bit (1 row) In a production database we experienced the following: * the database contains a number of tables and functions owned by user A * one is logged in as postgres * upon issuing 'REASSIGN OWNED BY A TO postgres', all tables and _most_ functions changed ownership, but not all.... a number of functions stay marked as owned by A, nothing weird in the logs, the reassign looked like it completed successfully * there doesn't seem to be anything 'special' about the functions that did not change ownership * changing ownership for one of these functions by using 'alter function' succeeds without any problem Mind you, we tested the reassign in a trial database first, a freshly restored dump, and there the reassign worked properly / completely. Any hints as to what could cause this? We left the production db as-is, so we still have a number of these functions 'still owned by A' for which we could check things in pg_* tables or something. -- Best, Frank.
Frank van Vugt <ftm.van.vugt@foxi.nl> writes: > * upon issuing 'REASSIGN OWNED BY A TO postgres', all tables and _most_ > functions changed ownership, but not all.... a number of functions stay marked > as owned by A, nothing weird in the logs, the reassign looked like it > completed successfully Hmmm .... look into pg_shdepend to see if there are entries linking those functions to an owner. For instance, after regression=# create user joe; CREATE ROLE regression=# \c - joe You are now connected to database "regression" as user "joe". regression=> create function foo() returns int as 'select 1' language sql; CREATE FUNCTION I get regression=> select * from pg_shdepend; dbid | classid | objid | objsubid | refclassid | refobjid | deptype --------+---------+--------+----------+------------+----------+--------- ... 123822 | 1255 | 148691 | 0 | 1260 | 148690 | o ... 1255 = pg_proc, 1260 = pg_authid, and the other numbers are the OIDs of the database, function, and role (user) respectively. This data is what REASSIGN OWNED works off of, and I suppose that some rows must be wrong or missing in your pg_shdepend. regards, tom lane
Re: REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it
From
Frank van Vugt
Date:
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.
Frank van Vugt <ftm.van.vugt@foxi.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
Re: REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it
From
Frank van Vugt
Date:
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.
Frank van Vugt <ftm.van.vugt@foxi.nl> writes: > Op woensdag 20 april 2011, schreef Tom Lane: >> 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. Actually, now that I think about it, 8.4 didn't allow on-the-fly reindexing of shared catalogs anyway. So that couldn't be your problem even if the test had shown the indexes didn't match the catalog. But it seems the rows actually disappeared from the catalog, and I have no idea what would've caused that. regards, tom lane
Re: REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it
From
Frank van Vugt
Date:
Hi, Op woensdag 20 april 2011, schreef Tom Lane: > Actually, now that I think about it, 8.4 didn't allow on-the-fly > reindexing of shared catalogs anyway. So that couldn't be your problem > even if the test had shown the indexes didn't match the catalog. But > it seems the rows actually disappeared from the catalog, and I have no > idea what would've caused that. ok, clear should we even try to get them back by generating them somehow? we're planning to upgrade to v9 in a month or so.... also: would there be any reason you can think of why using alter function in the current version in order to correct this situation would have a negative side-effect? -- Best, Frank.
Frank van Vugt <ftm.van.vugt@foxi.nl> writes: > Op woensdag 20 april 2011, schreef Tom Lane: >> Actually, now that I think about it, 8.4 didn't allow on-the-fly >> reindexing of shared catalogs anyway. So that couldn't be your problem >> even if the test had shown the indexes didn't match the catalog. But >> it seems the rows actually disappeared from the catalog, and I have no >> idea what would've caused that. > should we even try to get them back by generating them somehow? It's not really important --- the only difficulty with not having them is the one you already hit, that REASSIGN OWNED misses things it should do. If you're planning an update or dump-and-reload shortly, I wouldn't worry about it. What's much more worrisome is the question of whether the same kind of data loss happened anywhere else, and that isn't something that the available info tells me anything about. > also: would there be any reason you can think of why using alter function in > the current version in order to correct this situation would have a negative > side-effect? If you do want to go around and do retail ALTER OWNER commands, you certainly could. I thought for a moment that changeDependencyOnOwner would complain about the lack of a pre-existing pg_shdepend entry, but I see it won't, so it should work fine. regards, tom lane