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 Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

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

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." <pgsql@bluepolka.net>
To: "Alvaro Herrera" <alvherre@commandprompt.com>
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: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

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 Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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 Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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 Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

"Ed L." <pgsql@bluepolka.net> writes:

[ enlarge MAX_REPORTED_DEPS to 2000 ]

I was about to apply this, but stopped to reflect that it is probably
not such a hot idea. My concern is that enormously long error message
detail fields are likely to break client software, particularly GUI
clients. A poor (e.g., truncated) display isn't unlikely, and a crash
not entirely out of the question. Moreover, who's to say that 2000 is
enough lines to cover all cases? And if it's not, aren't you faced with
an even bigger problem?

Perhaps a better solution is to keep MAX_REPORTED_DEPS where it is, and
arrange that when it's exceeded, the *entire* list of dependencies gets
reported to the postmaster log; we can expect that that will work.
We still send the same just-the-count message to the client. We could
add a hint suggesting to look in the postmaster log for the details.
This would require some refactoring of checkSharedDependencies's API,
I suppose, but doesn't seem especially difficult.

regards, tom lane

On Tuesday 01 May 2007 9:34 pm, Tom Lane wrote:

"Ed L." <pgsql@bluepolka.net> writes:

[ enlarge MAX_REPORTED_DEPS to 2000 ]

I was about to apply this, but stopped to reflect that it is
probably not such a hot idea. My concern is that enormously
long error message detail fields are likely to break client
software, particularly GUI clients. A poor (e.g., truncated)
display isn't unlikely, and a crash not entirely out of the
question. Moreover, who's to say that 2000 is enough lines to
cover all cases? And if it's not, aren't you faced with an
even bigger problem?

Perhaps a better solution is to keep MAX_REPORTED_DEPS where
it is, and arrange that when it's exceeded, the *entire* list
of dependencies gets reported to the postmaster log; we can
expect that that will work. We still send the same
just-the-count message to the client. We could add a hint
suggesting to look in the postmaster log for the details. This
would require some refactoring of checkSharedDependencies's
API, I suppose, but doesn't seem especially difficult.

Fair enough. Something, anything, in the server log would
suffice to identify the problem specifics which are now hidden.
Unfortunately, I won't get to it anytime soon.

Ed

Tom Lane wrote:

"Ed L." <pgsql@bluepolka.net> writes:

[ enlarge MAX_REPORTED_DEPS to 2000 ]

I was about to apply this, but stopped to reflect that it is probably
not such a hot idea. My concern is that enormously long error message
detail fields are likely to break client software, particularly GUI
clients. A poor (e.g., truncated) display isn't unlikely, and a crash
not entirely out of the question. Moreover, who's to say that 2000 is
enough lines to cover all cases? And if it's not, aren't you faced with
an even bigger problem?

Perhaps a better solution is to keep MAX_REPORTED_DEPS where it is, and
arrange that when it's exceeded, the *entire* list of dependencies gets
reported to the postmaster log; we can expect that that will work.
We still send the same just-the-count message to the client. We could
add a hint suggesting to look in the postmaster log for the details.
This would require some refactoring of checkSharedDependencies's API,
I suppose, but doesn't seem especially difficult.

Actually I was thinking that we could report MAX_REPORTED_DEPS (the
original value) dependencies to the client log, and finish with "and
other N dependencies not shown here". Maybe we could mix both solutions
and send a partial report to the client and a full report to the server
log.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Tom Lane wrote:

Perhaps a better solution is to keep MAX_REPORTED_DEPS where it is, and
arrange that when it's exceeded, the *entire* list of dependencies gets
reported to the postmaster log; we can expect that that will work.
We still send the same just-the-count message to the client. We could
add a hint suggesting to look in the postmaster log for the details.
This would require some refactoring of checkSharedDependencies's API,
I suppose, but doesn't seem especially difficult.

Attached is a patch to do something like that. Note that I made
checkSharedDependencies report the full list of dependencies by itself,
instead of passing it back to the caller. This can easily be changed if
considered too ugly.

I also removed the code that truncated the message when there were too
many entries, so that it reports MAX_REPORTED_DEPS to the client and
append "and other %d objects reported to the server log".

I think we can now reduce MAX_REPORTED_DEPS. Is 50 OK? Even 20 could
be reasonable. (Do we take a poll?)

This is the chance to comment to the wording, the approach or the
ugliness of API ...

Sample output to the client (note: in this test, MAX_REPORTED_DEPS is
set to 10).

foo=# drop user foo;
ERROR: role "foo" cannot be dropped because some objects depend on it
DETAIL: owner of tablespace foo
owner of table a100
owner of table a99
owner of table a98
owner of table a97
owner of table a96
owner of table a95
owner of table a94
owner of table a93
owner of table a92
5 objects in database bar
and other 95 objects reported to the server log
foo=#

Whereas the postmaster log gets

LOG: objects dependent on role foo
DETAIL: owner of tablespace foo
owner of table a100
owner of table a99
owner of table a98
owner of table a97
owner of table a96
owner of table a95
owner of table a94
owner of table a93
owner of table a92
owner of table a91
owner of table a90
owner of table a89
owner of table a88
owner of table a87
owner of table a86
owner of table a85
owner of table a84
owner of table a83
owner of table a82
owner of table a81
owner of table a80
owner of table a79
owner of table a78
owner of table a77
owner of table a76
owner of table a75
owner of table a74
owner of table a73
owner of table a72
owner of table a71
owner of table a70
owner of table a69
owner of table a68
owner of table a67
owner of table a66
owner of table a65
owner of table a64
owner of table a63
owner of table a62
owner of table a61
owner of table a60
owner of table a59
owner of table a58
owner of table a57
owner of table a56
owner of table a55
owner of table a54
owner of table a53
owner of table a52
owner of table a51
owner of table a50
owner of table a49
owner of table a48
owner of table a47
owner of table a46
owner of table a45
owner of table a44
owner of table a43
owner of table a41
owner of table a40
owner of table a39
owner of table a38
owner of table a37
owner of table a36
owner of table a35
owner of table a34
owner of table a33
owner of table a32
owner of table a31
owner of table a30
owner of table a29
owner of table a28
owner of table a27
owner of table a26
owner of table a25
owner of table a24
owner of table a23
owner of table a22
owner of table a21
owner of table a20
owner of table a19
owner of table a18
owner of table a17
owner of table a16
owner of table a15
owner of table a14
owner of table a13
owner of table a12
owner of table a11
owner of table a10
owner of table a9
owner of table a8
owner of table a7
owner of table a6
owner of table a5
owner of table a4
owner of table a3
owner of table a2
owner of table a1
owner of table a42
owner of table qux
owner of table baz
owner of table bar
owner of table foo
STATEMENT: drop user foo;

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

Sample output to the client (note: in this test, MAX_REPORTED_DEPS is
set to 10).
...
foo=# drop user foo;
ERROR: role "foo" cannot be dropped because some objects depend on it
DETAIL: owner of tablespace foo
owner of table a100
owner of table a99
owner of table a98
owner of table a97
owner of table a96
owner of table a95
owner of table a94
owner of table a93
owner of table a92
5 objects in database bar
and other 95 objects reported to the server log

"and 95 other objects", please.

Whereas the postmaster log gets

Nothing about the objects in database bar?

regards, tom lane

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Sample output to the client (note: in this test, MAX_REPORTED_DEPS is
set to 10).
...
foo=# drop user foo;
ERROR: role "foo" cannot be dropped because some objects depend on it
DETAIL: owner of tablespace foo
owner of table a100
owner of table a99
owner of table a98
owner of table a97
owner of table a96
owner of table a95
owner of table a94
owner of table a93
owner of table a92
5 objects in database bar
and other 95 objects reported to the server log

"and 95 other objects", please.

Whereas the postmaster log gets

Nothing about the objects in database bar?

Applied with these corrections. I also made it produce a summary of
databases when there are too many, so you can get output like

owner of tablespace foo
owner of table qux
owner of table baz
owner of table bar
owner of table foo
one object in database a60
and objects in other 3 databases

which avoids a message flood when the user owns objects in too many
databases.

The corresponding server log is

owner of table foo
one object in database a60
one object in database a59
one object in database alvherre
5 objects in database bar

I kept the number of reported lines at 100.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support