Thread: LIST OWNED BY...

LIST OWNED BY...

From
Thom Brown
Date:
Hi all,

If someone wants to drop objects owned by a particular role, they'll
use DROP OWNED BY role.  However, the implications of this statement
aren't easily known, and once you've run it, it's not communicated
which objects were dropped.

So could we introduce either a command to show which objects are owned
by a particular role, or allow a dry-run of DROP OWNED BY?  Also when
DROP OWNED BY has been executed, shouldn't we get feedback about what
it did?  At the moment the only feedback provided is what objects the
statement cascaded to.

-- 
Thom


Re: LIST OWNED BY...

From
Tom Lane
Date:
Thom Brown <thom@linux.com> writes:
> So could we introduce either a command to show which objects are owned
> by a particular role, or allow a dry-run of DROP OWNED BY?

It's always been possible to do that:
begin;drop owned by joe;rollback;

I believe this is already the recommended approach if you're concerned
about what DROP CASCADE will do.
        regards, tom lane


Re: LIST OWNED BY...

From
Thom Brown
Date:
On 29 February 2012 17:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> So could we introduce either a command to show which objects are owned
>> by a particular role, or allow a dry-run of DROP OWNED BY?
>
> It's always been possible to do that:
>
>        begin;
>        drop owned by joe;
>        rollback;
>
> I believe this is already the recommended approach if you're concerned
> about what DROP CASCADE will do.

No, the cascade part is fine.  It's the objects which won't cause a
cascade that are an issue.  Putting it in a transaction for rolling
back doesn't help find out what it intends to drop.

How can the user tell what the statement would drop (ignoring cascades)?

--
Thom


Re: LIST OWNED BY...

From
Euler Taveira de Oliveira
Date:
On 29-02-2012 14:20, Thom Brown wrote:
> No, the cascade part is fine.  It's the objects which won't cause a
> cascade that are an issue.  Putting it in a transaction for rolling
> back doesn't help find out what it intends to drop.
> 
DROP OWNED BY foo VERBOSE?


--   Euler Taveira de Oliveira - Timbira       http://www.timbira.com.br/  PostgreSQL: Consultoria, Desenvolvimento,
Suporte24x7 e Treinamento
 


Re: LIST OWNED BY...

From
Thom Brown
Date:
On 29 February 2012 18:15, Euler Taveira de Oliveira <euler@timbira.com> wrote:
> On 29-02-2012 14:20, Thom Brown wrote:
>> No, the cascade part is fine.  It's the objects which won't cause a
>> cascade that are an issue.  Putting it in a transaction for rolling
>> back doesn't help find out what it intends to drop.
>>
> DROP OWNED BY foo VERBOSE?

Or just change it to output a verbose notice without changing the syntax?

--
Thom


Re: LIST OWNED BY...

From
Robert Haas
Date:
On Wed, Feb 29, 2012 at 12:20 PM, Thom Brown <thom@linux.com> wrote:
> On 29 February 2012 17:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Thom Brown <thom@linux.com> writes:
>>> So could we introduce either a command to show which objects are owned
>>> by a particular role, or allow a dry-run of DROP OWNED BY?
>>
>> It's always been possible to do that:
>>
>>        begin;
>>        drop owned by joe;
>>        rollback;
>>
>> I believe this is already the recommended approach if you're concerned
>> about what DROP CASCADE will do.
>
> No, the cascade part is fine.  It's the objects which won't cause a
> cascade that are an issue.  Putting it in a transaction for rolling
> back doesn't help find out what it intends to drop.
>
> How can the user tell what the statement would drop (ignoring cascades)?

It's certainly possible to write a query for this, but I think this
gets back to the old argument about whether every client (and every
end-user) should be required to reimplement this, or whether maybe we
ought to provide some server functionality around it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: LIST OWNED BY...

From
Euler Taveira de Oliveira
Date:
On 29-02-2012 15:23, Thom Brown wrote:
> Or just change it to output a verbose notice without changing the syntax?
> 
I can't see why we will do it only for DROP OWNED. Chat messages are annoying
unless the user asks for it (that's why I suggested VERBOSE).


--   Euler Taveira de Oliveira - Timbira       http://www.timbira.com.br/  PostgreSQL: Consultoria, Desenvolvimento,
Suporte24x7 e Treinamento
 


Re: LIST OWNED BY...

From
Thom Brown
Date:
On 29 February 2012 20:33, Euler Taveira de Oliveira <euler@timbira.com> wrote:
> On 29-02-2012 15:23, Thom Brown wrote:
>> Or just change it to output a verbose notice without changing the syntax?
>>
> I can't see why we will do it only for DROP OWNED. Chat messages are annoying
> unless the user asks for it (that's why I suggested VERBOSE).

Possibly, but I think if you're going ahead an dropping objects owned
by a role, you'll tend to be interested in what those things were
rather than blindly removing things from the database without actually
knowing what they are.

-- 
Thom


Re: LIST OWNED BY...

From
Alvaro Herrera
Date:
Excerpts from Thom Brown's message of mié feb 29 17:50:14 -0300 2012:
> On 29 February 2012 20:33, Euler Taveira de Oliveira <euler@timbira.com> wrote:
> > On 29-02-2012 15:23, Thom Brown wrote:
> >> Or just change it to output a verbose notice without changing the syntax?
> >>
> > I can't see why we will do it only for DROP OWNED. Chat messages are annoying
> > unless the user asks for it (that's why I suggested VERBOSE).
>
> Possibly, but I think if you're going ahead an dropping objects owned
> by a role, you'll tend to be interested in what those things were
> rather than blindly removing things from the database without actually
> knowing what they are.

Sure.

Initially DROP OWNED was supposed to be used only after REASSIGN OWNED.
So you give your objects to someone else, then DROP OWNED to revoke
whatever privileges you have (which is the only thing that REASSIGN
OWNED wouldn't have touched), then commit suicide.  There is no danger
of dropping useful objects in this scenario because you already gave
them away.  (Unless, of course, some other poor soul is busy reassigning
objects to you).

I agree that it might be useful to list objects owned by a given user.
Maybe this belongs into a view or a function, however, not a new
command.  After all, the mechanism that those commands use to display
objects is not very user-friendly, or even claims to be complete (the
lists are truncated after 200 objects or something like that).

More generally, maybe the thing to do is have a SRF that reports objects
that depend on some other object.  If you specify a role as the
referenced object, you get what's this thread is about.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: LIST OWNED BY...

From
Bruce Momjian
Date:
On Wed, Feb 29, 2012 at 01:27:43PM -0500, Robert Haas wrote:
> On Wed, Feb 29, 2012 at 12:20 PM, Thom Brown <thom@linux.com> wrote:
> > On 29 February 2012 17:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Thom Brown <thom@linux.com> writes:
> >>> So could we introduce either a command to show which objects are owned
> >>> by a particular role, or allow a dry-run of DROP OWNED BY?
> >>
> >> It's always been possible to do that:
> >>
> >>        begin;
> >>        drop owned by joe;
> >>        rollback;
> >>
> >> I believe this is already the recommended approach if you're concerned
> >> about what DROP CASCADE will do.
> >
> > No, the cascade part is fine.  It's the objects which won't cause a
> > cascade that are an issue.  Putting it in a transaction for rolling
> > back doesn't help find out what it intends to drop.
> >
> > How can the user tell what the statement would drop (ignoring cascades)?
> 
> It's certainly possible to write a query for this, but I think this
> gets back to the old argument about whether every client (and every
> end-user) should be required to reimplement this, or whether maybe we
> ought to provide some server functionality around it.

Is this a TODO?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: LIST OWNED BY...

From
Robert Haas
Date:
On Tue, Mar 13, 2012 at 2:56 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Is this a TODO?

If you're going to create one, link to Álvaro's message.  But I'm not
sure we should without a better sense of what we actually want to do,
from the options he laid out.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company