Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-10-10 Thread Andres Freund
On 2013-10-02 13:16:06 +0900, Michael Paquier wrote: Each patch applied with its parents compiles, has no warnings AFAIK and passes regression/isolation tests. Working on 0004 by the end of the CF seems out of the way IMO, so I'd suggest focusing on 0002 and 0003 now, and I can put some time

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-10-09 Thread Michael Paquier
Marking this patch as returned with feedback, I will not be able to work on that by the 15th of October. It would have been great to get the infrastructure patches 0002 and 0003 committed to minimize the work on the core patch, but well it is not the case. I am attaching as well a patch fixing

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-10-01 Thread Alvaro Herrera
Michael Paquier escribió: Btw, taking the problem from another viewpoint... This feature has now 3 patches, the 2 first patches doing only code refactoring. Could it be possible to have a look at those ones first? Straight-forward things should go first, simplifying the core feature

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-26 Thread Andres Freund
On 2013-09-26 12:13:30 +0900, Michael Paquier wrote: 2) I don't think the drop algorithm used now is correct. Your index_concurrent_set_dead() sets both indisvalid = false and indislive = false at the same time. It does so after doing a WaitForVirtualLocks() - but that's not sufficient.

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-26 Thread Michael Paquier
On Thu, Sep 26, 2013 at 7:34 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-26 12:13:30 +0900, Michael Paquier wrote: 2) I don't think the drop algorithm used now is correct. Your index_concurrent_set_dead() sets both indisvalid = false and indislive = false at the same time. It

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-26 Thread Andres Freund
On 2013-09-26 20:40:40 +0900, Michael Paquier wrote: On Thu, Sep 26, 2013 at 7:34 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-26 12:13:30 +0900, Michael Paquier wrote: 2) I don't think the drop algorithm used now is correct. Your index_concurrent_set_dead() sets both

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-26 Thread Michael Paquier
On Thu, Sep 26, 2013 at 8:43 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-26 20:40:40 +0900, Michael Paquier wrote: On Thu, Sep 26, 2013 at 7:34 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-26 12:13:30 +0900, Michael Paquier wrote: 2) I don't think the drop

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-26 Thread Andres Freund
On 2013-09-26 20:47:33 +0900, Michael Paquier wrote: On Thu, Sep 26, 2013 at 8:43 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-26 20:40:40 +0900, Michael Paquier wrote: On Thu, Sep 26, 2013 at 7:34 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-26 12:13:30

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-26 Thread Michael Paquier
On Thu, Sep 26, 2013 at 8:56 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-26 20:47:33 +0900, Michael Paquier wrote: On Thu, Sep 26, 2013 at 8:43 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-26 20:40:40 +0900, Michael Paquier wrote: On Thu, Sep 26, 2013 at 7:34

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-26 Thread Andres Freund
On 2013-09-27 05:41:26 +0900, Michael Paquier wrote: In this case, doing a call to WaitForOldSnapshots after the swap phase is enough. It was included in past versions of the patch but removed in the last 2 versions. I don't think it is. I really, really suggest following the protocol used by

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-19 Thread Robert Haas
On Tue, Sep 17, 2013 at 7:04 PM, Andres Freund and...@2ndquadrant.com wrote: 1. We're not in a huge hurry to ensure that sinval notifications are delivered in a timely fashion. We know that sinval resets are bad, so if a backend is getting close to needing a sinval reset, we kick it in an

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-17 Thread Robert Haas
On Mon, Sep 16, 2013 at 10:38 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-29 10:39:09 -0400, Robert Haas wrote: I have been of the opinion for some time now that the shared-invalidation code is not a particularly good design for much of what we need. Waiting for an old snapshot

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-17 Thread Andres Freund
On 2013-09-17 16:34:37 -0400, Robert Haas wrote: On Mon, Sep 16, 2013 at 10:38 AM, Andres Freund and...@2ndquadrant.com wrote: Actually, the shared inval code already has that knowledge, doesn't it? ISTM all we'd need is have a sequence number of SI entries which has to be queryable. Then

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-16 Thread Andres Freund
On 2013-08-29 10:39:09 -0400, Robert Haas wrote: I have been of the opinion for some time now that the shared-invalidation code is not a particularly good design for much of what we need. Waiting for an old snapshot is often a proxy for waiting long enough that we can be sure every other

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-09-16 Thread Andres Freund
Hi, Looking at this version of the patch now: 1) comment for Phase 4 of REINDEX CONCURRENTLY ends with an incomplete sentence. 2) I don't think the drop algorithm used now is correct. Your index_concurrent_set_dead() sets both indisvalid = false and indislive = false at the same time. It does so

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-08-29 Thread Robert Haas
On Wed, Aug 28, 2013 at 9:02 AM, Andres Freund and...@2ndquadrant.com wrote: During swap phase, process was waiting for transactions with older snapshots than the one taken by transaction doing the swap as they might hold the old index information. I think that we can get rid of it thanks to

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-08-28 Thread Andres Freund
On 2013-08-28 13:58:08 +0900, Michael Paquier wrote: On Tue, Aug 27, 2013 at 11:09 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-27 15:34:22 +0900, Michael Paquier wrote: I have been working a little bit more on this patch for the next commit fest. Compared to the previous

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-08-27 Thread Andres Freund
On 2013-08-27 15:34:22 +0900, Michael Paquier wrote: I have been working a little bit more on this patch for the next commit fest. Compared to the previous version, I have removed the part of the code where process running REINDEX CONCURRENTLY was waiting for transactions holding a snapshot

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-08-27 Thread Michael Paquier
On Tue, Aug 27, 2013 at 11:09 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-27 15:34:22 +0900, Michael Paquier wrote: I have been working a little bit more on this patch for the next commit fest. Compared to the previous version, I have removed the part of the code where process

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-11 Thread Michael Paquier
On Thu, Jul 11, 2013 at 5:11 PM, Michael Paquier michael.paqu...@gmail.com wrote: I am resending the patches after Fujii-san noticed a bug allowing to even drop valid toast indexes with the latest code... While looking at that, I found a couple of other bugs: - two bugs, now fixed, with the

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-04 Thread Michael Paquier
Hi, I noticed some errors in the comments of the patch committed. Please find attached a patch to correct that. Regards, -- Michael 20130704_reltoastidxid_comments.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-04 Thread Fujii Masao
On Thu, Jul 4, 2013 at 3:38 PM, Michael Paquier michael.paqu...@gmail.com wrote: Hi, I noticed some errors in the comments of the patch committed. Please find attached a patch to correct that. Committed. Thanks! Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-03 Thread Andres Freund
On 2013-07-03 10:03:26 +0900, Michael Paquier wrote: +static int +toast_open_indexes(Relation toastrel, +LOCKMODE lock, +Relation **toastidxs, +int *num_indexes) + /* + * Free index

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-03 Thread Michael Paquier
On Wed, Jul 3, 2013 at 11:16 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-07-03 10:03:26 +0900, Michael Paquier wrote: index 9ee9ea2..23e0373 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -2778,10 +2778,9 @@ binary_upgrade_set_pg_class_oids(Archive

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-03 Thread Andres Freund
On 2013-07-04 02:32:32 +0900, Michael Paquier wrote: Wouldn't it make more sense to fetch the toast index oid in the query ontop instead of making a query for every relation? With something like a CASE condition in the upper query for reltoastrelid? This code path is not only taken by

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-03 Thread Fujii Masao
On Thu, Jul 4, 2013 at 2:41 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Jul 4, 2013 at 2:36 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-07-04 02:32:32 +0900, Michael Paquier wrote: Wouldn't it make more sense to fetch the toast index oid in the query ontop instead of

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-03 Thread Michael Paquier
On Thu, Jul 4, 2013 at 3:26 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Jul 4, 2013 at 2:41 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Jul 4, 2013 at 2:36 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-07-04 02:32:32 +0900, Michael Paquier wrote: Wouldn't it make

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-02 Thread Fujii Masao
On Fri, Jun 28, 2013 at 4:30 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Wed, Jun 26, 2013 at 1:06 AM, Fujii Masao masao.fu...@gmail.com wrote: Thanks for updating the patch! And thanks for taking time to look at that. I updated the patch according to your comments, except for the

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-02 Thread Fujii Masao
On Wed, Jul 3, 2013 at 5:43 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Wed, Jul 3, 2013 at 5:22 AM, Fujii Masao masao.fu...@gmail.com wrote: Why did you remove the check of indisvalid from the --binary-upgrade SQL? Without this check, if there is the invalid toast index, more than

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-01 Thread Fujii Masao
On Mon, Jul 1, 2013 at 9:31 AM, Michael Paquier michael.paqu...@gmail.com wrote: Hi all, Please find attached an updated version of the patch removing reltoastidxid (with and w/o context diffs), patch fixing the vacuum full issue. With this fix, all the comments are addressed. Thanks for

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-07-01 Thread Michael Paquier
On Tue, Jul 2, 2013 at 7:36 AM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Jul 1, 2013 at 9:31 AM, Michael Paquier michael.paqu...@gmail.com wrote: Hi all, Please find attached an updated version of the patch removing reltoastidxid (with and w/o context diffs), patch fixing the vacuum

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-28 Thread Andres Freund
On 2013-06-28 16:30:16 +0900, Michael Paquier wrote: When I ran VACUUM FULL, I got the following error. ERROR: attempt to apply a mapping to unmapped relation 16404 STATEMENT: vacuum full; This can be reproduced when doing a vacuum full on pg_proc, pg_shdescription or

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-28 Thread Michael Paquier
On Fri, Jun 28, 2013 at 4:52 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-28 16:30:16 +0900, Michael Paquier wrote: When I ran VACUUM FULL, I got the following error. ERROR: attempt to apply a mapping to unmapped relation 16404 STATEMENT: vacuum full; This can be

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-25 Thread Fujii Masao
On Tue, Jun 25, 2013 at 8:15 AM, Michael Paquier michael.paqu...@gmail.com wrote: Patch updated according to comments. Thanks for updating the patch! When I ran VACUUM FULL, I got the following error. ERROR: attempt to apply a mapping to unmapped relation 16404 STATEMENT: vacuum full; Could

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-24 Thread Andres Freund
On 2013-06-24 07:46:34 +0900, Michael Paquier wrote: On Mon, Jun 24, 2013 at 7:22 AM, Fujii Masao masao.fu...@gmail.com wrote: Compile error ;) It looks like filterdiff did not work correctly when generating the latest patch with context diffs, I cannot apply it cleanly wither. This is

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-24 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: Otherwise I think there's not really much left to be done. Fujii? Well, other than the fact that we've not got MVCC catalog scans yet. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-24 Thread Andres Freund
On 2013-06-24 09:57:24 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: Otherwise I think there's not really much left to be done. Fujii? Well, other than the fact that we've not got MVCC catalog scans yet. That statement was only about about the patch dealing the removal

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-24 Thread Fujii Masao
On Mon, Jun 24, 2013 at 7:39 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-24 07:46:34 +0900, Michael Paquier wrote: On Mon, Jun 24, 2013 at 7:22 AM, Fujii Masao masao.fu...@gmail.com wrote: Compile error ;) It looks like filterdiff did not work correctly when generating the

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-24 Thread Michael Paquier
On Mon, Jun 24, 2013 at 11:06 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-24 09:57:24 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: Otherwise I think there's not really much left to be done. Fujii? Well, other than the fact that we've not got MVCC catalog

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-23 Thread Fujii Masao
On Wed, Jun 19, 2013 at 9:50 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Wed, Jun 19, 2013 at 12:36 AM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Jun 18, 2013 at 10:53 AM, Michael Paquier michael.paqu...@gmail.com wrote: An updated patch for the toast part is attached. On

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-23 Thread Fujii Masao
On Sun, Jun 23, 2013 at 3:34 PM, Michael Paquier michael.paqu...@gmail.com wrote: OK. Please find an updated patch for the toast part. On Sat, Jun 22, 2013 at 10:48 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-22 22:45:26 +0900, Michael Paquier wrote: On Sat, Jun 22, 2013 at

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-22 Thread Andres Freund
On 2013-06-22 12:50:52 +0900, Michael Paquier wrote: On Fri, Jun 21, 2013 at 10:47 PM, Andres Freund and...@2ndquadrant.com wrote: Hm. Looking at how this is currently used - I am afraid it's not correct... the reason RelationGetIndexList() returns a copy is that cache invalidations will

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-22 Thread Michael Paquier
On Sat, Jun 22, 2013 at 10:34 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-22 12:50:52 +0900, Michael Paquier wrote: On Fri, Jun 21, 2013 at 10:47 PM, Andres Freund and...@2ndquadrant.com wrote: Hm. Looking at how this is currently used - I am afraid it's not correct... the

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-22 Thread Andres Freund
On 2013-06-22 22:45:26 +0900, Michael Paquier wrote: On Sat, Jun 22, 2013 at 10:34 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-22 12:50:52 +0900, Michael Paquier wrote: By looking at the comments of RelationGetIndexList:relcache.c, actually the method of the patch is

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-22 Thread Alvaro Herrera
Andres Freund escribió: On 2013-06-22 22:45:26 +0900, Michael Paquier wrote: And I imagine that you have the same problem even with RelationGetIndexList, not only RelationGetIndexListIfInvalid, because this would appear as long as you try to open more than 1 index with an index list.

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-21 Thread Andres Freund
On 2013-06-19 09:55:24 +0900, Michael Paquier wrote: Please find an updated patch. The regression test rules has been updated, and all the comments are addressed. On Tue, Jun 18, 2013 at 6:35 PM, Andres Freund and...@2ndquadrant.com wrote: Hi, On 2013-06-18 10:53:25 +0900, Michael

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-21 Thread Michael Paquier
On Fri, Jun 21, 2013 at 6:19 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-19 09:55:24 +0900, Michael Paquier wrote: /* Clean up. */ heap_freetuple(reltup1); @@ -1529,12 +1570,13 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, if

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-21 Thread Andres Freund
On 2013-06-21 20:54:34 +0900, Michael Paquier wrote: On Fri, Jun 21, 2013 at 6:19 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-19 09:55:24 +0900, Michael Paquier wrote: @@ -1529,12 +1570,13 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, Is it actually possible to get

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-21 Thread Michael Paquier
OK let's finalize this patch first. I'll try to send an updated patch within today. On Fri, Jun 21, 2013 at 10:47 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-21 20:54:34 +0900, Michael Paquier wrote: On Fri, Jun 21, 2013 at 6:19 PM, Andres Freund and...@2ndquadrant.com wrote:

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-21 Thread Michael Paquier
On Fri, Jun 21, 2013 at 10:47 PM, Andres Freund and...@2ndquadrant.com wrote: Hm. Looking at how this is currently used - I am afraid it's not correct... the reason RelationGetIndexList() returns a copy is that cache invalidations will throw away that list. And you do index_open() while

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-18 Thread Andres Freund
Hi, On 2013-06-18 10:53:25 +0900, Michael Paquier wrote: diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c index c381f11..3a6342c 100644 --- a/contrib/pg_upgrade/info.c +++ b/contrib/pg_upgrade/info.c @@ -321,12 +321,17 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo)

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-18 Thread Andres Freund
Hi, On 2013-06-18 11:35:10 +0200, Andres Freund wrote: Going to do some performance tests now. Ok, so ran the worst case load I could think of and didn't notice any relevant performance changes. The test I ran was: CREATE TABLE test_toast(id serial primary key, data text); ALTER TABLE

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-18 Thread Fujii Masao
On Tue, Jun 18, 2013 at 10:53 AM, Michael Paquier michael.paqu...@gmail.com wrote: An updated patch for the toast part is attached. On Tue, Jun 18, 2013 at 3:26 AM, Fujii Masao masao.fu...@gmail.com wrote: Here are the review comments of the removal_of_reltoastidxid patch. I've not completed

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-18 Thread Fujii Masao
On Tue, Jun 18, 2013 at 9:54 PM, Andres Freund and...@2ndquadrant.com wrote: Hi, On 2013-06-18 11:35:10 +0200, Andres Freund wrote: Going to do some performance tests now. Ok, so ran the worst case load I could think of and didn't notice any relevant performance changes. The test I ran

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-18 Thread Michael Paquier
On Wed, Jun 19, 2013 at 12:36 AM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Jun 18, 2013 at 10:53 AM, Michael Paquier michael.paqu...@gmail.com wrote: An updated patch for the toast part is attached. On Tue, Jun 18, 2013 at 3:26 AM, Fujii Masao masao.fu...@gmail.com wrote: Here are

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Michael Paquier
On Mon, Jun 17, 2013 at 5:23 AM, Andres Freund and...@2ndquadrant.comwrote: On 2013-06-17 04:20:03 +0900, Fujii Masao wrote: On Thu, Jun 6, 2013 at 1:29 PM, Michael Paquier michael.paqu...@gmail.com wrote: Hi all, Please find attached the latest versions of REINDEX CONCURRENTLY for

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Peter Eisentraut
On 6/17/13 8:23 AM, Michael Paquier wrote: As mentionned by Andres, the only thing that the MVCC catalog patch can improve here is the index swap phase (index_concurrent_swap:index.c) where the relfilenode of the old and new indexes are exchanged. Now an AccessExclusiveLock is taken on the 2

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Andres Freund
On 2013-06-17 09:12:12 -0400, Peter Eisentraut wrote: On 6/17/13 8:23 AM, Michael Paquier wrote: As mentionned by Andres, the only thing that the MVCC catalog patch can improve here is the index swap phase (index_concurrent_swap:index.c) where the relfilenode of the old and new indexes

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Peter Eisentraut
On 6/17/13 9:19 AM, Andres Freund wrote: Without getting rid of the AccessExclusiveLock, REINDEX CONCURRENTLY is not really concurrent, at least not concurrent to the standard set by CREATE and DROP INDEX CONCURRENTLY. Well, it still does the main body of work in a concurrent fashion, so I

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Andres Freund
On 2013-06-17 11:03:35 -0400, Peter Eisentraut wrote: On 6/17/13 9:19 AM, Andres Freund wrote: Without getting rid of the AccessExclusiveLock, REINDEX CONCURRENTLY is not really concurrent, at least not concurrent to the standard set by CREATE and DROP INDEX CONCURRENTLY. Well, it

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Fujii Masao
On Mon, Jun 17, 2013 at 9:23 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Mon, Jun 17, 2013 at 5:23 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-17 04:20:03 +0900, Fujii Masao wrote: On Thu, Jun 6, 2013 at 1:29 PM, Michael Paquier michael.paqu...@gmail.com

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Josh Berkus
Well, it can do lots stuff that DROP/CREATE CONCURRENTLY can't: * reindex primary keys * reindex keys referenced by foreign keys * reindex exclusion constraints * reindex toast tables * do all that for a whole database so I don't think that comparison is fair. Having it would have made

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Andres Freund
On 2013-06-17 12:52:36 -0700, Josh Berkus wrote: Well, it can do lots stuff that DROP/CREATE CONCURRENTLY can't: * reindex primary keys * reindex keys referenced by foreign keys * reindex exclusion constraints * reindex toast tables * do all that for a whole database so I don't

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Alvaro Herrera
Andres Freund wrote: PS: Josh, minor thing, but could you please not trim the CC list, at least when I am on it? Yes, it's annoying. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Josh Berkus
On 06/17/2013 01:40 PM, Alvaro Herrera wrote: Andres Freund wrote: PS: Josh, minor thing, but could you please not trim the CC list, at least when I am on it? Yes, it's annoying. I also get private comments from people who don't want me to cc them when they are already on the list. I

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-17 Thread Andres Freund
On 2013-06-17 13:46:07 -0700, Josh Berkus wrote: On 06/17/2013 01:40 PM, Alvaro Herrera wrote: Andres Freund wrote: PS: Josh, minor thing, but could you please not trim the CC list, at least when I am on it? Yes, it's annoying. I also get private comments from people who don't

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-16 Thread Fujii Masao
On Thu, Jun 6, 2013 at 1:29 PM, Michael Paquier michael.paqu...@gmail.com wrote: Hi all, Please find attached the latest versions of REINDEX CONCURRENTLY for the 1st commit fest of 9.4: - 20130606_1_remove_reltoastidxid_v9.patch, removing reltoastidxid, to allow a toast relation to have

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-06-16 Thread Andres Freund
On 2013-06-17 04:20:03 +0900, Fujii Masao wrote: On Thu, Jun 6, 2013 at 1:29 PM, Michael Paquier michael.paqu...@gmail.com wrote: Hi all, Please find attached the latest versions of REINDEX CONCURRENTLY for the 1st commit fest of 9.4: - 20130606_1_remove_reltoastidxid_v9.patch,

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-31 Thread Michael Paquier
Hi, I moved this patch to the next commit fest. Thanks, -- Michael

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-28 Thread Fujii Masao
On Thu, Mar 28, 2013 at 10:34 AM, Andres Freund and...@anarazel.de wrote: On 2013-03-28 10:18:45 +0900, Michael Paquier wrote: On Thu, Mar 28, 2013 at 3:12 AM, Fujii Masao masao.fu...@gmail.com wrote: Since we call relation_open() with lockmode, ISTM that we should also call relation_close()

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-27 Thread Fujii Masao
On Wed, Mar 27, 2013 at 8:26 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Wed, Mar 27, 2013 at 3:05 AM, Fujii Masao masao.fu...@gmail.com wrote: ISTM you failed to make the patches from your repository. 20130323_1_toastindex_v7.patch contains all the changes of

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-27 Thread Andres Freund
On 2013-03-28 10:18:45 +0900, Michael Paquier wrote: On Thu, Mar 28, 2013 at 3:12 AM, Fujii Masao masao.fu...@gmail.com wrote: Since we call relation_open() with lockmode, ISTM that we should also call relation_close() with the same lockmode instead of NoLock. No? Agreed on that. That

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-27 Thread Andres Freund
On 2013-03-19 08:57:31 +0900, Michael Paquier wrote: On Tue, Mar 19, 2013 at 3:24 AM, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Mar 13, 2013 at 9:04 PM, Michael Paquier michael.paqu...@gmail.com wrote: I have been working on improving the code of the 2 patches: 1) reltoastidxid

Re: Remove invalid indexes from pg_dump Was: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-26 Thread Fujii Masao
On Tue, Mar 19, 2013 at 9:19 AM, Michael Paquier michael.paqu...@gmail.com wrote: If failures happen with CREATE INDEX CONCURRENTLY, the system will be let with invalid indexes. I don't think that the user would like to see invalid indexes of an existing system being recreated as valid after a

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-26 Thread Fujii Masao
On Sun, Mar 24, 2013 at 12:37 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Sat, Mar 23, 2013 at 10:20 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-03-22 07:38:36 +0900, Michael Paquier wrote: Is someone planning to provide additional feedback about this patch at

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-23 Thread Andres Freund
On 2013-03-22 07:38:36 +0900, Michael Paquier wrote: Is someone planning to provide additional feedback about this patch at some point? Yes, now that I have returned from my holidays - or well, am returning from them, I do plan to. But it should probably get some implementation level review

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-23 Thread Michael Paquier
On Sat, Mar 23, 2013 at 10:20 PM, Andres Freund and...@2ndquadrant.comwrote: On 2013-03-22 07:38:36 +0900, Michael Paquier wrote: Is someone planning to provide additional feedback about this patch at some point? Yes, now that I have returned from my holidays - or well, am returning from

Remove invalid indexes from pg_dump Was: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-21 Thread Michael Paquier
Hi, On Sun, Mar 17, 2013 at 9:24 PM, Michael Paquier michael.paqu...@gmail.com wrote: Please find attached the patches wanted: - 20130317_dump_only_valid_index.patch, a 1-line patch that makes pg_dump not take a dump of invalid indexes. This patch can be backpatched to 9.0. The patch

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-21 Thread Michael Paquier
Is someone planning to provide additional feedback about this patch at some point? Thanks, -- Michael

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-18 Thread Fujii Masao
On Sun, Mar 17, 2013 at 9:24 PM, Michael Paquier michael.paqu...@gmail.com wrote: Please find attached the patches wanted: - 20130317_dump_only_valid_index.patch, a 1-line patch that makes pg_dump not take a dump of invalid indexes. This patch can be backpatched to 9.0. Don't indisready and

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-18 Thread Fujii Masao
On Wed, Mar 13, 2013 at 9:04 PM, Michael Paquier michael.paqu...@gmail.com wrote: I have been working on improving the code of the 2 patches: 1) reltoastidxid removal: snip - Fix a bug with pg_dump and binary upgrade. One valid index is necessary for a given toast relation. Is this bugfix

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-18 Thread Michael Paquier
On Tue, Mar 19, 2013 at 3:03 AM, Fujii Masao masao.fu...@gmail.com wrote: On Sun, Mar 17, 2013 at 9:24 PM, Michael Paquier michael.paqu...@gmail.com wrote: Please find attached the patches wanted: - 20130317_dump_only_valid_index.patch, a 1-line patch that makes pg_dump not take a dump of

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-18 Thread Michael Paquier
On Tue, Mar 19, 2013 at 3:24 AM, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Mar 13, 2013 at 9:04 PM, Michael Paquier michael.paqu...@gmail.com wrote: I have been working on improving the code of the 2 patches: 1) reltoastidxid removal: snip - Fix a bug with pg_dump and binary

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-18 Thread Michael Paquier
On Tue, Mar 19, 2013 at 8:54 AM, Michael Paquier michael.paqu...@gmail.comwrote: On Tue, Mar 19, 2013 at 3:03 AM, Fujii Masao masao.fu...@gmail.comwrote: On Sun, Mar 17, 2013 at 9:24 PM, Michael Paquier michael.paqu...@gmail.com wrote: Please find attached the patches wanted: -

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-17 Thread Michael Paquier
Please find attached the patches wanted: - 20130317_reindexdb_concurrently.patch, adding an option -c/--concurrently to reindexdb Note that I added an error inside reindexdb for options -s -c as REINDEX CONCURRENTLY does not support SYSTEM. - 20130317_dump_only_valid_index.patch, a 1-line patch

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-16 Thread Fujii Masao
On Wed, Mar 13, 2013 at 9:04 PM, Michael Paquier michael.paqu...@gmail.com wrote: I have been working on improving the code of the 2 patches: I found pg_dump dumps even the invalid index. But pg_dump should ignore the invalid index? This problem exists even without REINDEX CONCURRENTLY patch. So

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-16 Thread Michael Paquier
On 2013/03/17, at 0:35, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Mar 13, 2013 at 9:04 PM, Michael Paquier michael.paqu...@gmail.com wrote: I have been working on improving the code of the 2 patches: I found pg_dump dumps even the invalid index. But pg_dump should ignore the invalid

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-09 Thread Fujii Masao
On Sat, Mar 9, 2013 at 1:31 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Sat, Mar 9, 2013 at 1:37 AM, Fujii Masao masao.fu...@gmail.com wrote: + para + Concurrent indexes based on a literalPRIMARY KEY/ or an literal + EXCLUSION/ constraint need to be dropped with

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-09 Thread Fujii Masao
On Fri, Mar 8, 2013 at 1:46 AM, Andres Freund and...@2ndquadrant.com wrote: Why do you want to temporarily mark it as valid? I don't see any requirement that it is set to that during validate_index() (which imo is badly named, but...). I'd just set it to valid in the same transaction that does

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-09 Thread Fujii Masao
On Sun, Mar 10, 2013 at 3:48 AM, Fujii Masao masao.fu...@gmail.com wrote: Thanks for updating the patch! - SELECT reltoastidxid - FROM info_rels i JOIN pg_catalog.pg_class c -

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-09 Thread Michael Paquier
On Sun, Mar 10, 2013 at 4:50 AM, Fujii Masao masao.fu...@gmail.com wrote: On Sun, Mar 10, 2013 at 3:48 AM, Fujii Masao masao.fu...@gmail.com wrote: Thanks for updating the patch! - SELECT reltoastidxid -

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-08 Thread Fujii Masao
On Fri, Mar 8, 2013 at 10:00 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Mar 8, 2013 at 1:41 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Mar 7, 2013 at 7:19 AM, Andres Freund and...@2ndquadrant.com wrote: The strange think about hoge_pkey_cct_cct is that it seems

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-07 Thread Fujii Masao
On Thu, Mar 7, 2013 at 7:19 AM, Andres Freund and...@2ndquadrant.com wrote: The strange think about hoge_pkey_cct_cct is that it seems to imply that an invalid index was reindexed concurrently? But I don't see how it could happen either. Fujii, can you reproduce it? Yes, I can even with the

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-07 Thread Andres Freund
On 2013-03-07 09:58:58 +0900, Michael Paquier wrote: +The recommended recovery method in such cases is to drop the concurrent +index and try again to perform commandREINDEX CONCURRENTLY/. If an invalid index depends on the constraint like primary key, drop

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-06 Thread Andres Freund
On 2013-03-06 13:21:27 +0900, Michael Paquier wrote: Please find attached updated patch realigned with your comments. You can find my answers inline... The only thing that needs clarification is the comment about UNIQUE_CHECK_YES/UNIQUE_CHECK_NO. Except that all the other things are corrected

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-06 Thread Andres Freund
On 2013-03-06 20:59:37 +0900, Michael Paquier wrote: OK. Patches updated... Please see attached. With all the work done on those patches, I suppose this is close to being something clean... Yes, its looking good. There are loads of improvements possible but those can very well be made

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-06 Thread Michael Paquier
On Wed, Mar 6, 2013 at 9:09 PM, Andres Freund and...@2ndquadrant.comwrote: On 2013-03-06 20:59:37 +0900, Michael Paquier wrote: OK. Patches updated... Please see attached. With all the work done on those patches, I suppose this is close to being something clean... Yes, its looking good.

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-06 Thread Andres Freund
On 2013-03-06 21:19:57 +0900, Michael Paquier wrote: On Wed, Mar 6, 2013 at 9:09 PM, Andres Freund and...@2ndquadrant.comwrote: On 2013-03-06 20:59:37 +0900, Michael Paquier wrote: OK. Patches updated... Please see attached. With all the work done on those patches, I suppose this is

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-06 Thread Fujii Masao
On Wed, Mar 6, 2013 at 8:59 PM, Michael Paquier michael.paqu...@gmail.com wrote: OK. Patches updated... Please see attached. I found odd behavior. After I made REINDEX CONCURRENTLY fail twice, I found that the index which was not marked as INVALID remained unexpectedly. =# CREATE TABLE hoge (i

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-06 Thread Andres Freund
On 2013-03-07 02:09:49 +0900, Fujii Masao wrote: On Wed, Mar 6, 2013 at 8:59 PM, Michael Paquier michael.paqu...@gmail.com wrote: OK. Patches updated... Please see attached. I found odd behavior. After I made REINDEX CONCURRENTLY fail twice, I found that the index which was not marked as

  1   2   3   >