Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-03-28 Thread Robert Haas
On Fri, Feb 3, 2012 at 10:28 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Feb 1, 2012 at 2:39 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Feb 1, 2012 at 7:31 PM, Peter Eisentraut pete...@gmx.net wrote: On sön, 2012-01-29 at 22:01 +, Simon Riggs wrote: Patch now locks index

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-02-12 Thread Jeff Janes
On Sun, Jan 8, 2012 at 8:19 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Jan 4, 2012 at 11:14 AM, Simon Riggs si...@2ndquadrant.com wrote: Not having a freelist at all is probably a simpler way of avoiding the lock contention, so I'll happily back that suggestion instead. Patch

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-02-03 Thread Robert Haas
On Wed, Feb 1, 2012 at 2:39 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Feb 1, 2012 at 7:31 PM, Peter Eisentraut pete...@gmx.net wrote: On sön, 2012-01-29 at 22:01 +, Simon Riggs wrote: Patch now locks index in AccessExclusiveLock in final stage of drop. Doesn't that defeat the

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-02-01 Thread Simon Riggs
On Wed, Feb 1, 2012 at 2:56 AM, Robert Haas robertmh...@gmail.com wrote: I improved the grammar issues in the attached version of the patch - the syntax is now simpler and more consistent, IF EXISTS now works, and RESTRICT is accepted (without changing the behavior) while CASCADE fails with a

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-02-01 Thread Peter Eisentraut
On sön, 2012-01-29 at 22:01 +, Simon Riggs wrote: Patch now locks index in AccessExclusiveLock in final stage of drop. Doesn't that defeat the point of doing the CONCURRENTLY business in the first place? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-02-01 Thread Simon Riggs
On Wed, Feb 1, 2012 at 7:31 PM, Peter Eisentraut pete...@gmx.net wrote: On sön, 2012-01-29 at 22:01 +, Simon Riggs wrote: Patch now locks index in AccessExclusiveLock in final stage of drop. Doesn't that defeat the point of doing the CONCURRENTLY business in the first place? That was my

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-31 Thread Robert Haas
On Sun, Jan 29, 2012 at 5:01 PM, Simon Riggs si...@2ndquadrant.com wrote: I can't see any way that situation can occur. The patch *explicitly* waits until all people that can see the index as usable have dropped their lock. So I don't think this is necessary. Having said that, since we are

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-29 Thread Simon Riggs
On Thu, Jan 26, 2012 at 2:25 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Jan 21, 2012 at 10:11 AM, Simon Riggs si...@2ndquadrant.com wrote: Your caution is wise. All users of an index have already checked whether the index is usable at plan time, so although there is much code that

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-25 Thread Robert Haas
On Sat, Jan 21, 2012 at 10:11 AM, Simon Riggs si...@2ndquadrant.com wrote: Your caution is wise. All users of an index have already checked whether the index is usable at plan time, so although there is much code that assumes they can look at the index itself, that is not executed until after

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-24 Thread Jeff Janes
On Sat, Jan 21, 2012 at 2:29 PM, Jim Nasby j...@nasby.net wrote: On Jan 20, 2012, at 11:54 AM, Heikki Linnakangas wrote: So, you're proposing that we remove freelist altogether? Sounds reasonable, but that needs to be performance tested somehow. I'm not sure what exactly the test should

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-24 Thread Jeff Janes
On Mon, Jan 23, 2012 at 5:06 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jan 23, 2012 at 12:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Jan 21, 2012 at 5:29 PM, Jim Nasby j...@nasby.net wrote: We should also look at having the freelist

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-24 Thread Simon Riggs
On Mon, Jan 23, 2012 at 4:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: The real problem there is that BufFreelistLock is also used to protect the clock sweep pointer. Agreed I think basically we gotta find a way to allow multiple backends to run clock sweeps concurrently.  Or else fix things

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-23 Thread Robert Haas
On Mon, Jan 23, 2012 at 12:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Jan 21, 2012 at 5:29 PM, Jim Nasby j...@nasby.net wrote: We should also look at having the freelist do something useful, instead of just dropping it completely. Unfortunately

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-23 Thread Simon Riggs
On Mon, Jan 23, 2012 at 1:06 PM, Robert Haas robertmh...@gmail.com wrote: It's pretty trivial to prove that there is a very serious problem with BufFreelistLock.  I'll admit I can't prove what the right fix is just yet, and certainly measurement is warranted. I agree there is a problem with

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-23 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Mon, Jan 23, 2012 at 12:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: The expensive part of what we do while holding BufFreelistLock is, I think, iterating through buffers taking and releasing a spinlock on each one (!). Yeah ... spinlocks that, by

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-23 Thread Robert Haas
On Mon, Jan 23, 2012 at 11:01 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jan 23, 2012 at 12:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: The expensive part of what we do while holding BufFreelistLock is, I think, iterating through buffers taking and

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-22 Thread Jim Nasby
On Jan 20, 2012, at 11:54 AM, Heikki Linnakangas wrote: On 04.01.2012 13:14, Simon Riggs wrote: On Tue, Jan 3, 2012 at 11:28 PM, Tom Lanet...@sss.pgh.pa.us wrote: Jim Nasbyj...@nasby.net writes: On Jan 3, 2012, at 12:11 PM, Simon Riggs wrote: This could well be related to the fact that

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-22 Thread Robert Haas
On Sat, Jan 21, 2012 at 5:29 PM, Jim Nasby j...@nasby.net wrote: We should also look at having the freelist do something useful, instead of just dropping it completely. Unfortunately that's probably more work... That's kinda my feeling as well. The free list in its current form is pretty much

Re: Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-22 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Sat, Jan 21, 2012 at 5:29 PM, Jim Nasby j...@nasby.net wrote: We should also look at having the freelist do something useful, instead of just dropping it completely. Unfortunately that's probably more work... That's kinda my feeling as well. The

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-21 Thread Simon Riggs
On Sat, Jan 21, 2012 at 1:53 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jan 19, 2012 at 10:02 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Jan 18, 2012 at 11:12 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jan 18, 2012 at 5:49 PM, Simon Riggs si...@2ndquadrant.com

Removing freelist (was Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?)

2012-01-20 Thread Heikki Linnakangas
On 04.01.2012 13:14, Simon Riggs wrote: On Tue, Jan 3, 2012 at 11:28 PM, Tom Lanet...@sss.pgh.pa.us wrote: Jim Nasbyj...@nasby.net writes: On Jan 3, 2012, at 12:11 PM, Simon Riggs wrote: This could well be related to the fact that DropRelFileNodeBuffers() does a scan of shared_buffers,

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-20 Thread Robert Haas
On Thu, Jan 19, 2012 at 10:02 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Jan 18, 2012 at 11:12 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jan 18, 2012 at 5:49 PM, Simon Riggs si...@2ndquadrant.com wrote: Can I just check with you that the only review comment is a one line

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-19 Thread Simon Riggs
On Wed, Jan 18, 2012 at 11:12 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jan 18, 2012 at 5:49 PM, Simon Riggs si...@2ndquadrant.com wrote: Can I just check with you that the only review comment is a one line change? Seems better to make any additional review comments in one go. No,

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-18 Thread Simon Riggs
On Wed, Jan 18, 2012 at 1:28 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jan 17, 2012 at 12:06 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2012-01-16 at 14:46 -0500, Robert Haas wrote: On Mon, Jan 16, 2012 at 2:06 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2012-01-16

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-18 Thread Robert Haas
On Wed, Jan 18, 2012 at 5:49 PM, Simon Riggs si...@2ndquadrant.com wrote: Can I just check with you that the only review comment is a one line change? Seems better to make any additional review comments in one go. No, I haven't done a full review yet - I just noticed that right at the outset

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-17 Thread Peter Eisentraut
On mån, 2012-01-16 at 14:46 -0500, Robert Haas wrote: On Mon, Jan 16, 2012 at 2:06 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2012-01-16 at 11:17 -0500, Robert Haas wrote: I don't see how setting indisvalid to false helps with this, because IIUC when a session sees indisvalid =

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-17 Thread Robert Haas
On Tue, Jan 17, 2012 at 12:06 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2012-01-16 at 14:46 -0500, Robert Haas wrote: On Mon, Jan 16, 2012 at 2:06 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2012-01-16 at 11:17 -0500, Robert Haas wrote: I don't see how setting indisvalid to

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-16 Thread Robert Haas
On Sat, Dec 31, 2011 at 8:26 AM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Dec 30, 2011 at 10:20 PM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-08-24 at 11:24 -0700, Daniel Farina wrote: I was poking around at tablecmds and index.c and wonder if a similar two-pass approach as

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-16 Thread Peter Eisentraut
On mån, 2012-01-16 at 11:17 -0500, Robert Haas wrote: I don't see how setting indisvalid to false helps with this, because IIUC when a session sees indisvalid = false, it is supposed to avoid using the index for queries but still make new index entries when a write operation happens - but to

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-16 Thread Robert Haas
On Mon, Jan 16, 2012 at 2:06 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2012-01-16 at 11:17 -0500, Robert Haas wrote: I don't see how setting indisvalid to false helps with this, because IIUC when a session sees indisvalid = false, it is supposed to avoid using the index for queries

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-08 Thread Simon Riggs
On Wed, Jan 4, 2012 at 11:14 AM, Simon Riggs si...@2ndquadrant.com wrote: Not having a freelist at all is probably a simpler way of avoiding the lock contention, so I'll happily back that suggestion instead. Patch attached, previous patch revoked. v2 attached with cleanup of some random stuff

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-04 Thread Simon Riggs
On Tue, Jan 3, 2012 at 11:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jim Nasby j...@nasby.net writes: On Jan 3, 2012, at 12:11 PM, Simon Riggs wrote: This could well be related to the fact that DropRelFileNodeBuffers() does a scan of shared_buffers, which is an O(N) approach no matter the size

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-03 Thread Simon Riggs
On Fri, Sep 9, 2011 at 11:02 PM, Daniel Farina dan...@heroku.com wrote: On Wed, Aug 24, 2011 at 1:04 PM, Daniel Farina dan...@heroku.com wrote: On Wed, Aug 24, 2011 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Assuming the issue really is the physical unlinks (which I agree I'd like to see

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-03 Thread Jim Nasby
On Jan 3, 2012, at 12:11 PM, Simon Riggs wrote: This could well be related to the fact that DropRelFileNodeBuffers() does a scan of shared_buffers, which is an O(N) approach no matter the size of the index. On top of that, taking what Robert Haas mentioned on another thread,

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-03 Thread Tom Lane
Jim Nasby j...@nasby.net writes: On Jan 3, 2012, at 12:11 PM, Simon Riggs wrote: This could well be related to the fact that DropRelFileNodeBuffers() does a scan of shared_buffers, which is an O(N) approach no matter the size of the index. Couldn't we just leave the buffers alone? Once an

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-03 Thread Jim Nasby
On Jan 3, 2012, at 5:28 PM, Tom Lane wrote: Jim Nasby j...@nasby.net writes: On Jan 3, 2012, at 12:11 PM, Simon Riggs wrote: This could well be related to the fact that DropRelFileNodeBuffers() does a scan of shared_buffers, which is an O(N) approach no matter the size of the index.

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-03 Thread Tom Lane
Jim Nasby j...@nasby.net writes: Yeah, but the problem we run into is that with every backend trying to run the clock on it's own we end up with high contention again... it's just in a different place than when we had a true LRU. The clock sweep might be cheaper than the linked list was,

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-03 Thread Robert Haas
On Tue, Jan 3, 2012 at 7:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jim Nasby j...@nasby.net writes: Yeah, but the problem we run into is that with every backend trying to run the clock on it's own we end up with high contention again... it's just in a different place than when we had a true

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2012-01-03 Thread Jim Nasby
On Jan 3, 2012, at 7:34 PM, Robert Haas wrote: On Tue, Jan 3, 2012 at 7:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jim Nasby j...@nasby.net writes: Yeah, but the problem we run into is that with every backend trying to run the clock on it's own we end up with high contention again... it's just

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2011-12-31 Thread Simon Riggs
On Fri, Dec 30, 2011 at 10:20 PM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-08-24 at 11:24 -0700, Daniel Farina wrote: I was poking around at tablecmds and index.c and wonder if a similar two-pass approach as used by CREATE INDEX CONCURRENTLY can be used to create a DROP INDEX

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2011-12-30 Thread Peter Eisentraut
On ons, 2011-08-24 at 11:24 -0700, Daniel Farina wrote: At Heroku we use CREATE INDEX CONCURRENTLY with great success, but recently when frobbing around some indexes I realized that there is no equivalent for DROP INDEX, and this is a similar but lesser problem (as CREATE INDEX takes much

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2011-09-09 Thread Daniel Farina
On Wed, Aug 24, 2011 at 1:04 PM, Daniel Farina dan...@heroku.com wrote: On Wed, Aug 24, 2011 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Assuming the issue really is the physical unlinks (which I agree I'd like to see some evidence for), I wonder whether the problem could be addressed by

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2011-08-24 Thread Merlin Moncure
On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina dan...@heroku.com wrote: Hello list, At Heroku we use CREATE INDEX CONCURRENTLY with great success, but recently when frobbing around some indexes I realized that there is no equivalent for DROP INDEX, and this is a similar but lesser problem

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2011-08-24 Thread Robert Haas
On Wed, Aug 24, 2011 at 2:24 PM, Daniel Farina dan...@heroku.com wrote: Could I make the ACCESS EXCLUSIVE section just long enough to commit catalog updates, and then have the bulk of the work happen afterwards? The general idea is: 1) set an index as invalid, to ensure no backend will use

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2011-08-24 Thread Tom Lane
Merlin Moncure mmonc...@gmail.com writes: On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina dan...@heroku.com wrote: At Heroku we use CREATE INDEX CONCURRENTLY with great success, but recently when frobbing around some indexes I realized that there is no equivalent for DROP INDEX, and this is a

Re: [HACKERS] Should I implement DROP INDEX CONCURRENTLY?

2011-08-24 Thread Daniel Farina
On Wed, Aug 24, 2011 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina dan...@heroku.com wrote: At Heroku we use CREATE INDEX CONCURRENTLY with great success, but recently when frobbing around some indexes I