Thread: Async commands (like drop index)
Hello,
It seems that it may be useful to allow something like:
DROP INDEX NOWAIT.
The idea being, that the terminal will come back, the index will be
dropped in the background. If it doesn't drop, it rollback like normal
and logs.
I bring this up now, as an idea. We can argue about it later... :)
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Thu, May 17, 2007 at 12:30:45PM -0700, Joshua D. Drake wrote: > It seems that it may be useful to allow something like: > > DROP INDEX NOWAIT. > > The idea being, that the terminal will come back, the index will be > dropped in the background. If it doesn't drop, it rollback like normal > and logs. > > I bring this up now, as an idea. We can argue about it later... :) Assuming the concurrent psql stuff gets in, do you still see a use for this? -- Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Jim C. Nasby wrote:
> On Thu, May 17, 2007 at 12:30:45PM -0700, Joshua D. Drake wrote:
>> It seems that it may be useful to allow something like:
>>
>> DROP INDEX NOWAIT.
>>
>> The idea being, that the terminal will come back, the index will be
>> dropped in the background. If it doesn't drop, it rollback like normal
>> and logs.
>>
>> I bring this up now, as an idea. We can argue about it later... :)
>
> Assuming the concurrent psql stuff gets in, do you still see a use for
> this?
Yes. As cool as concurrent psql is... the majority of our users don't
use it. They use PgAdminIII.
This should be client agnostic imo.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Yes. As cool as concurrent psql is... the majority of our users don't
> use it. They use PgAdminIII.
So? IIRC pgAdmin can open up multiple connections already.
> This should be client agnostic imo.
Just to be perfectly clear: the odds of making a single backend support
concurrent operations in the foreseeable future are indistinguishable
from zero. So if you want a behavior like this, it's going to have to
happen by making the client open up multiple connections. I see no
reason at all to tie such a feature to index-dropping in particular.
regards, tom lane
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Yes. As cool as concurrent psql is... the majority of our users don't
>> use it. They use PgAdminIII.
>
> So? IIRC pgAdmin can open up multiple connections already.
>
>> This should be client agnostic imo.
>
> Just to be perfectly clear: the odds of making a single backend support
> concurrent operations in the foreseeable future are indistinguishable
> from zero. So if you want a behavior like this, it's going to have to
> happen by making the client open up multiple connections. I see no
> reason at all to tie such a feature to index-dropping in particular.
I was just using the index dropping as something particularly useful. It
could be anything.
Also note that I really wasn't trying to detract from what's important
right now. I just wanted to get this on the list for later discussion.
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Fri, 2007-18-05 at 11:47 -0500, Jim C. Nasby wrote: > Assuming the concurrent psql stuff gets in, do you still see a use for > this? I think concurrent psql (and/or async libpq) is the right way to handle this sort of requirement. "DROP INDEX NOWAIT" is hacky, and would be difficult (impossible?) to implement in a reasonable manner: the backend is fundamentally single-threaded. Also, how does the client learn when the DROP INDEX actually finishes? The client would either need to poll the database, or we'd need to implement something like select() -- neither is a very appealing alternative. -1 from me: this functionality belongs on the client-side, where asynchronous operations are much easier to manage. -Neil
Neil Conway wrote: > On Fri, 2007-18-05 at 11:47 -0500, Jim C. Nasby wrote: > > Assuming the concurrent psql stuff gets in, do you still see a use for > > this? > > I think concurrent psql (and/or async libpq) is the right way to handle > this sort of requirement. "DROP INDEX NOWAIT" is hacky, and would be > difficult (impossible?) to implement in a reasonable manner: the backend > is fundamentally single-threaded. Also, how does the client learn when > the DROP INDEX actually finishes? The client would either need to poll > the database, or we'd need to implement something like select() -- > neither is a very appealing alternative. I think what Joshua really wants is an equivalent of this: start:BEGIN;LOCK TABLE foo IN ACCESS EXCLUSIVE MODE NOWAIT;-- if fail, rollback and go to startDROP INDEX foo_idx;COMMIT; The idea is that the lock is only acquired if immediately available, thus not blocking other queries which would otherwise be blocked behind the DROP INDEX. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, 2007-18-05 at 13:29 -0400, Alvaro Herrera wrote: > I think what Joshua really wants is an equivalent of this That's not what his original email asked for, at any rate. > start: > BEGIN; > LOCK TABLE foo IN ACCESS EXCLUSIVE MODE NOWAIT; > -- if fail, rollback and go to start > DROP INDEX foo_idx; > COMMIT; > > The idea is that the lock is only acquired if immediately available, > thus not blocking other queries which would otherwise be blocked behind > the DROP INDEX. ISTM this can easily be implemented with statement_timeout (which is more general to boot). -Neil
On Fri, May 18, 2007 at 01:39:56PM -0400, Neil Conway wrote: > On Fri, 2007-18-05 at 13:29 -0400, Alvaro Herrera wrote: > > I think what Joshua really wants is an equivalent of this > > That's not what his original email asked for, at any rate. > > > start: > > BEGIN; > > LOCK TABLE foo IN ACCESS EXCLUSIVE MODE NOWAIT; > > -- if fail, rollback and go to start > > DROP INDEX foo_idx; > > COMMIT; > > > > The idea is that the lock is only acquired if immediately available, > > thus not blocking other queries which would otherwise be blocked behind > > the DROP INDEX. > > ISTM this can easily be implemented with statement_timeout (which is > more general to boot). Well, with statement_timeout, and writing a function... This functionality would actually be useful, but I'm not sure if it's worth including in core. It would be really nice to have an example of how to do this in a PostgreSQL Cookbook somewhere though. -- Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)