Thread: LIST OWNED BY...
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
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
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
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
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
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
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
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
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
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. +
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