Re: [GENERAL] dropping role w/dependent objects

"Ed L." <pgsql(at)bluepolka(dot)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(at)bluepolka(dot)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(at)bluepolka(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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