Re: [GENERAL] dropping role w/dependent objects
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Ed L. wrote: On Wednesday April 4 2007 5:37 pm, Bruce Momjian wrote: Perhaps this could be added to the TODO list? I won't get to it anytime soon. Yes. What should the TODO text be? See if the attached patch is acceptable. If not, perhaps the TODO text should be: Enable end user to identify dependent objects when the following error is encountered: ERROR: role mygroup cannot be dropped because some objects depend on it DETAIL: 227 objects in this database [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] dropping role w/dependent objects
Ed L. wrote: This is pgsql 8.2.3: % psql -c drop role mygroup ERROR: role mygroup cannot be dropped because some objects depend on it DETAIL: 227 objects in this database How do I identify what these dependent objects are? Hum, this seems to be a bug. The objects are supposed to be logged in the DETAIL field of that error, but when the count is too high apparently the detail is being clobbered and rewritten with a count instead. I would have expected that it listed some of those objects, say the first 40. Note that you can give the objects owned by that role to someone else with REASSIGN OWNED, and drop the objects with DROP OWNED (note that they act differently regarding grants; see the docs) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dropping role w/dependent objects
On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote: Note that you can give the objects owned by that role to someone else with REASSIGN OWNED, and drop the objects with DROP OWNED (note that they act differently regarding grants; see the docs) Yes, but how do identify what they are so that I know if I want to DROP OWNED them? TIA. Ed ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] dropping role w/dependent objects
On Wednesday April 4 2007 4:39 pm, Ed L. wrote: On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote: Note that you can give the objects owned by that role to someone else with REASSIGN OWNED, and drop the objects with DROP OWNED (note that they act differently regarding grants; see the docs) Yes, but how do identify what they are so that I know if I want to DROP OWNED them? The REASSIGN OWNED appears to be insufficient: % psql -c reassign owned by mygroup to mydba REASSIGN OWNED % psql -c drop group mygroup ERROR: role mygroup cannot be dropped because some objects depend on it DETAIL: 225 objects in this database Thanks, Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] dropping role w/dependent objects
On Wednesday April 4 2007 4:41 pm, Ed L. wrote: On Wednesday April 4 2007 4:39 pm, Ed L. wrote: On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote: Note that you can give the objects owned by that role to someone else with REASSIGN OWNED, and drop the objects with DROP OWNED (note that they act differently regarding grants; see the docs) Yes, but how do identify what they are so that I know if I want to DROP OWNED them? The REASSIGN OWNED appears to be insufficient: % psql -c reassign owned by mygroup to mydba REASSIGN OWNED % psql -c drop group mygroup ERROR: role mygroup cannot be dropped because some objects depend on it DETAIL: 225 objects in this database I did a before and after dump to compare. The objects it is complaining about are GRANTs to that group. Hmm... seems like you shouldn't have to drop every grant for a group you're dropping. Thanks, Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] dropping role w/dependent objects
Ed L. wrote: On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote: Note that you can give the objects owned by that role to someone else with REASSIGN OWNED, and drop the objects with DROP OWNED (note that they act differently regarding grants; see the docs) Yes, but how do identify what they are so that I know if I want to DROP OWNED them? There's no way AFAICT, short of peeking the catalogs (or information_schema). Try pg_shdepend. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dropping role w/dependent objects
Ed-- check out REASSIGN OWNED http://www.postgresql.org/docs/current/static/sql-reassign-owned.html then use CASCADE option of DROP OWNED to drop dependents e.g. DROP OWNED BY FUBAR CASCADE http://www.postgresql.org/docs/current/static/sql-drop-owned.html HTH, Martin -- This email message and any files transmitted with it contain confidential information intended only for the person(s) to whom this email message is addressed. If you have received this email message in error, please notify the sender immediately by telephone or email and destroy the original message without making a copy. Thank you. - Original Message - From: Ed L. [EMAIL PROTECTED] To: Alvaro Herrera [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, April 04, 2007 6:41 PM Subject: Re: [GENERAL] dropping role w/dependent objects On Wednesday April 4 2007 4:39 pm, Ed L. wrote: On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote: Note that you can give the objects owned by that role to someone else with REASSIGN OWNED, and drop the objects with DROP OWNED (note that they act differently regarding grants; see the docs) Yes, but how do identify what they are so that I know if I want to DROP OWNED them? The REASSIGN OWNED appears to be insufficient: % psql -c reassign owned by mygroup to mydba REASSIGN OWNED % psql -c drop group mygroup ERROR: role mygroup cannot be dropped because some objects depend on it DETAIL: 225 objects in this database Thanks, Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] dropping role w/dependent objects
Ed L. wrote: On Wednesday April 4 2007 4:39 pm, Ed L. wrote: On Wednesday April 4 2007 4:35 pm, Alvaro Herrera wrote: Note that you can give the objects owned by that role to someone else with REASSIGN OWNED, and drop the objects with DROP OWNED (note that they act differently regarding grants; see the docs) Yes, but how do identify what they are so that I know if I want to DROP OWNED them? The REASSIGN OWNED appears to be insufficient: % psql -c reassign owned by mygroup to mydba REASSIGN OWNED % psql -c drop group mygroup ERROR: role mygroup cannot be dropped because some objects depend on it DETAIL: 225 objects in this database Right. REASSIGN OWNED changes ownership to someone else, but leaves grants untouched. DROP OWNED revokes the permissions the role might have. This is the intended behavior. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dropping role w/dependent objects
On Wednesday April 4 2007 4:48 pm, Alvaro Herrera wrote: Yes, but how do identify what they are so that I know if I want to DROP OWNED them? There's no way AFAICT, short of peeking the catalogs (or information_schema). Try pg_shdepend. I guess if the bug were fixed, it'd be a non-issue. Thanks. Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] dropping role w/dependent objects
Ed L. wrote: On Wednesday April 4 2007 4:48 pm, Alvaro Herrera wrote: Yes, but how do identify what they are so that I know if I want to DROP OWNED them? There's no way AFAICT, short of peeking the catalogs (or information_schema). Try pg_shdepend. I guess if the bug were fixed, it'd be a non-issue. Sure, please submit a patch. It should not be too difficult. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] dropping role w/dependent objects
On Wednesday April 4 2007 5:02 pm, Alvaro Herrera wrote: I guess if the bug were fixed, it'd be a non-issue. Sure, please submit a patch. It should not be too difficult. Perhaps this could be added to the TODO list? I won't get to it anytime soon. Ed ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dropping role w/dependent objects
Ed L. wrote: On Wednesday April 4 2007 5:02 pm, Alvaro Herrera wrote: I guess if the bug were fixed, it'd be a non-issue. Sure, please submit a patch. ?It should not be too difficult. Perhaps this could be added to the TODO list? I won't get to it anytime soon. Yes. What should the TODO text be? -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] dropping role w/dependent objects
On Wednesday April 4 2007 5:37 pm, Bruce Momjian wrote: Perhaps this could be added to the TODO list? I won't get to it anytime soon. Yes. What should the TODO text be? See if the attached patch is acceptable. If not, perhaps the TODO text should be: Enable end user to identify dependent objects when the following error is encountered: ERROR: role mygroup cannot be dropped because some objects depend on it DETAIL: 227 objects in this database Index: ./src/backend/catalog/pg_shdepend.c === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/pg_shdepend.c,v retrieving revision 1.17 diff -C1 -r1.17 pg_shdepend.c *** ./src/backend/catalog/pg_shdepend.c 3 Mar 2007 19:32:54 - 1.17 --- ./src/backend/catalog/pg_shdepend.c 5 Apr 2007 00:05:56 - *** *** 484,488 * We try to limit the number of reported dependencies to something sane, ! * both for the user's sake and to avoid blowing out memory. */ ! #define MAX_REPORTED_DEPS 100 --- 484,497 * We try to limit the number of reported dependencies to something sane, ! * both for the user's sake and to avoid blowing out memory. But since ! * this is the only way for an end user to easily identify the dependent ! * objects, make the limit pretty big. Generously assuming each object ! * description is 64 chars long, and assuming we add some commentary of ! * up to 15 chars in storeObjectDescription(), that's ~80 chars per ! * object. If we allow 2000, that's 160Kb, which is reasonable. If the ! * installer gets wild and uses 128 character names, that's still only ! * 320Kb. These sorts of high numbers of dependencies are reached quite ! * easily when a sizeable schema of hundreds of tables has specific grants ! * on each relation. */ ! #define MAX_REPORTED_DEPS 2000 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
