Thread: Re: [GENERAL] dropping role w/dependent objects

Re: [GENERAL] dropping role w/dependent objects

From
Tom Lane
Date:
"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

Re: [GENERAL] dropping role w/dependent objects

From
"Ed L."
Date:
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

Re: [GENERAL] dropping role w/dependent objects

From
Alvaro Herrera
Date:
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

Re: [GENERAL] dropping role w/dependent objects

From
Alvaro Herrera
Date:
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

Re: [GENERAL] dropping role w/dependent objects

From
Tom Lane
Date:
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

Re: [GENERAL] dropping role w/dependent objects

From
Alvaro Herrera
Date:
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