SEO

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

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)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

"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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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/


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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 Herrera http://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

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

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)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly