pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how 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>
  2007-10-31 16:04 ` Re: how to check objects dependency?? Scott Marlowe <scott.marlowe@gmail.com>
  2007-10-31 19:19 ` Re: how to check objects dependency?? Alvaro Herrera <alvherre@commandprompt.com>
  2007-10-31 19:58 ` Re: how to check objects dependency?? Tom Lane <tgl@sss.pgh.pa.us>
  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 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   ` Re: how to check objects dependency?? adey <adey11@gmail.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 14:50 how to check objects dependency?? Jessica Richard <rjessil@yahoo.com>
  2007-10-31 16:04 ` Re: how to check objects dependency?? Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-10-31 21:38   ` adey <adey11@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-10-31 14:50 how to check objects dependency?? Jessica Richard <rjessil@yahoo.com>
@ 2007-10-31 19:19 ` Alvaro Herrera <alvherre@commandprompt.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 14:50 how to check objects dependency?? Jessica Richard <rjessil@yahoo.com>
@ 2007-10-31 19:58 ` Tom Lane <tgl@sss.pgh.pa.us>
  2007-11-02 12:50   ` Re: how to check objects dependency?? 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 14:50 how to check objects dependency?? Jessica Richard <rjessil@yahoo.com>
  2007-10-31 19:58 ` Re: how to check objects dependency?? Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-02 12:50   ` Jessica Richard <rjessil@yahoo.com>
  2007-11-02 15:15     ` Re: how to check objects dependency?? Tom Lane <tgl@sss.pgh.pa.us>
  2007-11-02 19:30     ` Re: how to check objects dependency?? Alvaro Herrera <alvherre@commandprompt.com>
  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-10-31 14:50 how to check objects dependency?? Jessica Richard <rjessil@yahoo.com>
  2007-10-31 19:58 ` Re: how to check objects dependency?? Tom Lane <tgl@sss.pgh.pa.us>
  2007-11-02 12:50   ` Re: how to check objects dependency?? Jessica Richard <rjessil@yahoo.com>
@ 2007-11-02 15:15     ` Tom Lane <tgl@sss.pgh.pa.us>
  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-10-31 14:50 how to check objects dependency?? Jessica Richard <rjessil@yahoo.com>
  2007-10-31 19:58 ` Re: how to check objects dependency?? Tom Lane <tgl@sss.pgh.pa.us>
  2007-11-02 12:50   ` Re: how to check objects dependency?? Jessica Richard <rjessil@yahoo.com>
@ 2007-11-02 19:30     ` Alvaro Herrera <alvherre@commandprompt.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