pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to check objects dependency??
8+ messages / 5 participants
[nested] [flat]
* how to check objects dependency??
@ 2007-10-31 14:50 Jessica Richard <rjessil@yahoo.com>
0 siblings, 3 replies; 8+ messages in thread
From: Jessica Richard @ 2007-10-31 14:50 UTC (permalink / raw)
To: pgsql-admin
I am trying to drop a user but it complained with the following message:
ERROR: role "joe" cannot be dropped because some objects depend on it
DETAIL: 1 objects in database Jan_test
1 objects in database Jan
this user does not own any tables in those two databases. Is there any command I can use to check all the dependencies on this user?
Thanks,
Jessica
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to check objects dependency??
@ 2007-10-31 16:04 Scott Marlowe <scott.marlowe@gmail.com>
parent: Jessica Richard <rjessil@yahoo.com>
2 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2007-10-31 16:04 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin
On 10/31/07, Jessica Richard <rjessil@yahoo.com> wrote:
> I am trying to drop a user but it complained with the following message:
>
> ERROR: role "joe" cannot be dropped because some objects depend on it
> DETAIL: 1 objects in database Jan_test
> 1 objects in database Jan
>
> this user does not own any tables in those two databases. Is there any
> command I can use to check all the dependencies on this user?
What version of pgsql are you running? In 8.2 I get a more useful
error message for a table that is owned by a user, i.e.:
ERROR: role ... cannot be dropped ...
DETAIL: owner of table <tablename>
Have you looked in the logs to see if something like that shows up in them?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to check objects dependency??
@ 2007-10-31 19:19 Alvaro Herrera <alvherre@commandprompt.com>
parent: Jessica Richard <rjessil@yahoo.com>
2 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2007-10-31 19:19 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin
Jessica Richard wrote:
> I am trying to drop a user but it complained with the following message:
>
> ERROR: role "joe" cannot be dropped because some objects depend on it
> DETAIL: 1 objects in database Jan_test
> 1 objects in database Jan
>
> this user does not own any tables in those two databases. Is there any
> command I can use to check all the dependencies on this user?
Connect to those databases and issue the command again to see the
detail. When you are connected to a different database, you don't have
access to the other database catalogs, thus we cannot give you the
object names (only the fact that they exist).
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"PHP is what I call the "Dumb Monkey" language. [A]ny dumb monkey can code
something in PHP. Python takes actual thought to produce something useful."
(J. Drake)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to check objects dependency??
@ 2007-10-31 19:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jessica Richard <rjessil@yahoo.com>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2007-10-31 19:58 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin
Jessica Richard <rjessil@yahoo.com> writes:
> I am trying to drop a user but it complained with the following message:
> ERROR: role "joe" cannot be dropped because some objects depend on it
> DETAIL: 1 objects in database Jan_test
> 1 objects in database Jan
> this user does not own any tables in those two databases. Is there any command I can use to check all the dependencies on this user?
Try to do the DROP ROLE within those databases. You'll get more detail
about owned objects within the current database, but from outside that
database the info isn't reachable ...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to check objects dependency??
@ 2007-10-31 21:38 adey <adey11@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: adey @ 2007-10-31 21:38 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Jessica Richard <rjessil@yahoo.com>; pgsql-admin
Doesn't ownership also apply to views and stored procs...
On 11/1/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> On 10/31/07, Jessica Richard <rjessil@yahoo.com> wrote:
> > I am trying to drop a user but it complained with the following message:
> >
> > ERROR: role "joe" cannot be dropped because some objects depend on it
> > DETAIL: 1 objects in database Jan_test
> > 1 objects in database Jan
> >
> > this user does not own any tables in those two databases. Is there any
> > command I can use to check all the dependencies on this user?
>
> What version of pgsql are you running? In 8.2 I get a more useful
> error message for a table that is owned by a user, i.e.:
>
> ERROR: role ... cannot be dropped ...
> DETAIL: owner of table <tablename>
>
> Have you looked in the logs to see if something like that shows up in
> them?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to check objects dependency??
@ 2007-11-02 12:50 Jessica Richard <rjessil@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Jessica Richard @ 2007-11-02 12:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Instead of using drop user username, I used drop user "username" with double quotes, then the error message told me more detail about a specific user defined function. Then I was able to drop that function, then drop this user.
My next question,
How do I check the ownership of the functions. I can't tell the difference between system functions and the user defined functions.
I am sure in the future there will be times that I need to check the ownership of the functions but not allowed to drop users to see check the dependencies.
Thanks a lot,
Jessica
Tom Lane <tgl@sss.pgh.pa.us> wrote: Jessica Richard writes:
> I am trying to drop a user but it complained with the following message:
> ERROR: role "joe" cannot be dropped because some objects depend on it
> DETAIL: 1 objects in database Jan_test
> 1 objects in database Jan
> this user does not own any tables in those two databases. Is there any command I can use to check all the dependencies on this user?
Try to do the DROP ROLE within those databases. You'll get more detail
about owned objects within the current database, but from outside that
database the info isn't reachable ...
regards, tom lane
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to check objects dependency??
@ 2007-11-02 15:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jessica Richard <rjessil@yahoo.com>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2007-11-02 15:15 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: pgsql-admin
Jessica Richard <rjessil@yahoo.com> writes:
> I am sure in the future there will be times that I need to check the ownership of the functions but not allowed to drop users to see check the dependencies.
There's no need for a separate operation for that. Do
begin;
drop user foo;
rollback;
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to check objects dependency??
@ 2007-11-02 19:30 Alvaro Herrera <alvherre@commandprompt.com>
parent: Jessica Richard <rjessil@yahoo.com>
1 sibling, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2007-11-02 19:30 UTC (permalink / raw)
To: Jessica Richard <rjessil@yahoo.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
Jessica Richard wrote:
> Instead of using drop user username, I used drop user "username" with
> double quotes, then the error message told me more detail about a
> specific user defined function. Then I was able to drop that function,
> then drop this user.
FYI you can use the commands DROP OWNED and REASSIGN OWNED to drop the
objects by a given role, and respectively assign them to someone else.
(8.2 only).
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-11-02 19:30 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-31 14:50 how to check objects dependency?? Jessica Richard <rjessil@yahoo.com>
2007-10-31 16:04 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-31 21:38 ` adey <adey11@gmail.com>
2007-10-31 19:19 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-10-31 19:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-02 12:50 ` Jessica Richard <rjessil@yahoo.com>
2007-11-02 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-02 19:30 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox