Re: [HACKERS] Async commands (like drop index)
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 [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Async commands (like drop index)
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Async commands (like drop index)
Joshua D. Drake [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Async commands (like drop index)
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/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Async commands (like drop index)
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Async commands (like drop index)
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 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. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Async commands (like drop index)
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Async commands (like drop index)
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 [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
