Here is a rebased version of the patch.
Andreas
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index a0ca2851e5..f8c59ea127 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -926,6 +926,7 @@ ERROR: could not serialize access due to read/write dependencies among tr
I have attached a new, rebased version of the batch with most of Banck's
and some of your feedback incorporated. Thanks for the good feedback!
On 03/31/2017 08:27 AM, Michael Paquier wrote> When running REINDEX
SCHEMA CONCURRENTLY public on the regression
database I am bumping into a bunch of
On 04/03/2017 07:57 AM, Michael Paquier wrote:
On Fri, Mar 31, 2017 at 5:12 PM, Andreas Karlsson wrote:
On 03/31/2017 08:27 AM, Michael Paquier wrote:
- Do a per-index rebuild and not a per-relation rebuild for concurrent
indexing. Doing a per-relation reindex has the disadvantage that many
ob
On Fri, Mar 31, 2017 at 5:12 PM, Andreas Karlsson wrote:
> Thanks for the feedback. I will look at it when I get the time.
>
> On 03/31/2017 08:27 AM, Michael Paquier wrote:
>>
>> - Do a per-index rebuild and not a per-relation rebuild for concurrent
>> indexing. Doing a per-relation reindex has t
Thanks for the feedback. I will look at it when I get the time.
On 03/31/2017 08:27 AM, Michael Paquier wrote:
- Do a per-index rebuild and not a per-relation rebuild for concurrent
indexing. Doing a per-relation reindex has the disadvantage that many
objects need to be created at the same time,
On Thu, Mar 30, 2017 at 5:13 AM, Michael Banck
wrote:
> On Mon, Mar 13, 2017 at 03:11:50AM +0100, Andreas Karlsson wrote:
>> Spotted one of my TODO comments there so I have attached a patch where I
>> have cleaned up that function. I also fixed the the code to properly support
>> triggers.
>
> I h
On Mon, Mar 13, 2017 at 11:11 AM, Andreas Karlsson wrote:
> On 03/02/2017 03:10 AM, Michael Paquier wrote:
>> There is a lot of mumbo-jumbo in the patch to create the exact same
>> index definition as the original one being reindexed, and that's a
>> huge maintenance burden for the future. You can
Hi,
I had a look at this.
On Mon, Mar 13, 2017 at 03:11:50AM +0100, Andreas Karlsson wrote:
> Spotted one of my TODO comments there so I have attached a patch where I
> have cleaned up that function. I also fixed the the code to properly support
> triggers.
The patch applies with quite a few off
On 03/13/2017 03:11 AM, Andreas Karlsson wrote:
I also fixed the the code to properly support triggers.
And by "support triggers" I actually meant fixing the support for moving
the foreign keys to the new index.
Andreas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
On 03/02/2017 03:10 AM, Michael Paquier wrote:
On Wed, Mar 1, 2017 at 2:21 AM, Andreas Karlsson wrote:
+/*
+ * Copy contraint flags for old index. This is safe because the old index
+ * guaranteed uniquness.
+ */
+newIndexForm->indisprimary = oldIndexForm->indisprimary;
+
On Fri, Mar 10, 2017 at 9:36 PM, Thomas Munro
wrote:
> On Wed, Mar 8, 2017 at 4:12 PM, Andres Freund wrote:
>> Can you come up with an halfway realistic scenario why an index oid, not
>> a table, constraint, sequence oid, would be relied upon?
>
> Is there an implication for SIREAD locks? Predic
On Wed, Mar 8, 2017 at 4:12 PM, Andres Freund wrote:
> Can you come up with an halfway realistic scenario why an index oid, not
> a table, constraint, sequence oid, would be relied upon?
Is there an implication for SIREAD locks? Predicate locks on index
pages include the index OID in the tag.
-
On 3/8/17 9:34 AM, Andreas Karlsson wrote:
Also, if by any chance you think (or use any
software that thinks) that OIDs for system objects are a stable
identifier, this will be the first case where that ceases to be true.
If the system is shut down or crashes or the session is killed, you'll
be l
On 03/08/2017 03:48 AM, Robert Haas wrote:
On Sun, Mar 5, 2017 at 7:13 PM, Andreas Karlsson wrote:
And I would argue that his feature is useful for quite many, based on my
experience running a semi-large database. Index bloat happens and without
REINDEX CONCURRENTLY it can be really annoying to
On 2017-03-07 21:48:23 -0500, Robert Haas wrote:
> On Sun, Mar 5, 2017 at 7:13 PM, Andreas Karlsson wrote:
> > And I would argue that his feature is useful for quite many, based on my
> > experience running a semi-large database. Index bloat happens and without
> > REINDEX CONCURRENTLY it can be r
On Sun, Mar 5, 2017 at 7:13 PM, Andreas Karlsson wrote:
> And I would argue that his feature is useful for quite many, based on my
> experience running a semi-large database. Index bloat happens and without
> REINDEX CONCURRENTLY it can be really annoying to solve, especially for
> primary keys. C
On 03/05/2017 07:56 PM, Robert Haas wrote:
On Sat, Mar 4, 2017 at 12:34 PM, Andres Freund wrote:
I agree that'd it be nicer not to have this, but not having the feature at all
is a lot worse than this wart.
I, again, give that a firm "maybe". If the warts end up annoying 1%
of the users who
On Sat, Mar 4, 2017 at 9:34 AM, Andres Freund wrote:
> On March 4, 2017 1:16:56 AM PST, Robert Haas wrote:
>>Maybe. But it looks to me like this patch is going to have
>>considerably more than its share of user-visible warts, and I'm not
>>very excited about that. I feel like what we ought to b
On Sat, Mar 4, 2017 at 12:34 PM, Andres Freund wrote:
> I agree that'd it be nicer not to have this, but not having the feature at
> all is a lot worse than this wart.
I, again, give that a firm "maybe". If the warts end up annoying 1%
of the users who try to use this feature, then you're right
On March 4, 2017 1:16:56 AM PST, Robert Haas wrote:
>Maybe. But it looks to me like this patch is going to have
>considerably more than its share of user-visible warts, and I'm not
>very excited about that. I feel like what we ought to be doing is
>keeping the index OID the same and changing t
On Thu, Mar 2, 2017 at 11:48 AM, Andres Freund wrote:
> On 2017-03-01 19:25:23 -0600, Jim Nasby wrote:
>> On 2/28/17 11:21 AM, Andreas Karlsson wrote:
>> > The only downside I can see to this approach is that we no logner will
>> > able to reindex catalog tables concurrently, but in return it shou
On 03/02/2017 02:25 AM, Jim Nasby wrote:
On 2/28/17 11:21 AM, Andreas Karlsson wrote:
The only downside I can see to this approach is that we no logner will
able to reindex catalog tables concurrently, but in return it should be
easier to confirm that this approach can be made work.
Another do
On 2017-03-01 19:25:23 -0600, Jim Nasby wrote:
> On 2/28/17 11:21 AM, Andreas Karlsson wrote:
> > The only downside I can see to this approach is that we no logner will
> > able to reindex catalog tables concurrently, but in return it should be
> > easier to confirm that this approach can be made w
On Wed, Mar 1, 2017 at 2:21 AM, Andreas Karlsson wrote:
> For each table:
>
> 1. Create new indexes without populating them, and lock the tables and
> indexes for the session.
+/*
+ * Copy contraint flags for old index. This is safe because the old index
+ * guaranteed uniquness.
+
On 2/28/17 11:21 AM, Andreas Karlsson wrote:
The only downside I can see to this approach is that we no logner will
able to reindex catalog tables concurrently, but in return it should be
easier to confirm that this approach can be made work.
Another downside is any stored regclass fields will
Hi,
Here is a third take on this feature, heavily based on Michael Paquier's
2.0 patch. This time the patch does not attempt to preserve the index
oids, but instead creates new indexes and moves all dependencies from
the old indexes to the new before dropping the old ones. The only
downside I
On Mon, Feb 27, 2017 at 05:31:21PM -0500, Tom Lane wrote:
> Michael Paquier writes:
> > I don't object to the addition of this patch in next CF as this
> > presents no new concept. Still per the complications this patch and
> > because it is a complicated patch I was wondering if people are fine
>
Michael Paquier writes:
> I don't object to the addition of this patch in next CF as this
> presents no new concept. Still per the complications this patch and
> because it is a complicated patch I was wondering if people are fine
> to include it in this last CF.
The March CF is already looking p
On Tue, Feb 28, 2017 at 5:29 AM, Bruce Momjian wrote:
> On Fri, Feb 17, 2017 at 11:05:31PM +0900, Michael Paquier wrote:
>> On Fri, Feb 17, 2017 at 10:43 PM, Andreas Karlsson wrote:
>> > Thinking about this makes me wonder about why you decided to use a
>> > transaction per index in many of the s
On Fri, Feb 17, 2017 at 11:05:31PM +0900, Michael Paquier wrote:
> On Fri, Feb 17, 2017 at 10:43 PM, Andreas Karlsson wrote:
> > Thinking about this makes me wonder about why you decided to use a
> > transaction per index in many of the steps rather than a transaction per
> > step. Most steps shou
On Fri, Feb 17, 2017 at 10:43 PM, Andreas Karlsson wrote:
> Thinking about this makes me wonder about why you decided to use a
> transaction per index in many of the steps rather than a transaction per
> step. Most steps should be quick. The only steps I think the makes sense to
> have a transacti
On 02/17/2017 01:53 PM, Andreas Karlsson wrote:
I am actually thinking about going the opposite direction (by reducing
the number of times we call WaitForLockers), because it is not just
about consuming transaction IDs, we also do not want to wait too many
times for transactions to commit. I am l
On 02/14/2017 04:56 AM, Michael Paquier wrote:
On Tue, Feb 14, 2017 at 11:32 AM, Andreas Karlsson wrote:
On 02/13/2017 06:31 AM, Michael Paquier wrote:
Er, something like that as well, no?
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
REINDEX (VERBOSE) currently prints one suc
On Tue, Feb 14, 2017 at 12:56 PM, Michael Paquier
wrote:
> This way you can monitor the time it takes to build
> completely each index, including its .
You can ignore the terms "including its" here.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make chang
On Tue, Feb 14, 2017 at 11:32 AM, Andreas Karlsson wrote:
> On 02/13/2017 06:31 AM, Michael Paquier wrote:
>> Er, something like that as well, no?
>> DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
>
> REINDEX (VERBOSE) currently prints one such line per index, which does not
> really
On 02/13/2017 06:31 AM, Michael Paquier wrote:
- What should we do with REINDEX DATABASE CONCURRENTLY and the system
catalog? I so not think we can reindex the system catalog concurrently
safely, so what should REINDEX DATABASE do with the catalog indexes? Skip
them, reindex them while taking loc
On Sun, Feb 12, 2017 at 6:44 AM, Andreas Karlsson wrote:
> On 02/02/2015 03:10 PM, Andres Freund wrote:
>> I think if we should instead just use the new index, repoint the
>> dependencies onto the new oid, and then afterwards, when dropping,
>> rename the new index one onto the old one. That means
On 02/02/2015 03:10 PM, Andres Freund wrote:
I think if we should instead just use the new index, repoint the
dependencies onto the new oid, and then afterwards, when dropping,
rename the new index one onto the old one. That means the oid of the
index will change and some less than pretty grovell
On Mon, Feb 2, 2015 at 9:10 AM, Andres Freund wrote:
> On 2014-11-12 16:11:58 -0500, Robert Haas wrote:
>> On Wed, Nov 12, 2014 at 4:10 PM, Robert Haas wrote:
>> > On Thu, Nov 6, 2014 at 9:50 AM, Peter Eisentraut wrote:
>> >> If REINDEX cannot work without an exclusive lock, we should invent som
On 2014-11-12 16:11:58 -0500, Robert Haas wrote:
> On Wed, Nov 12, 2014 at 4:10 PM, Robert Haas wrote:
> > On Thu, Nov 6, 2014 at 9:50 AM, Peter Eisentraut wrote:
> >> If REINDEX cannot work without an exclusive lock, we should invent some
> >> other qualifier, like WITH FEWER LOCKS.
> >
> > What
On Tue, Dec 23, 2014 at 5:54 PM, Oskari Saarenmaa wrote:
>
> If the short-lived lock is the only blocker for this feature at the
> moment could we just require an additional qualifier for CONCURRENTLY
> (FORCE?) until the lock can be removed, something like:
> =# [blah]
FWIW, I'd just keep only C
13.11.2014, 23:50, Andres Freund kirjoitti:
> On November 13, 2014 10:23:41 PM CET, Peter Eisentraut
> wrote:
>> On 11/12/14 7:31 PM, Andres Freund wrote:
>>> Yes, it sucks. But it beats not being able to reindex a relation with
>> a
>>> primary key (referenced by a fkey) without waiting several
On Thu, Nov 13, 2014 at 10:25 AM, Alvaro Herrera
wrote:
> Andres Freund wrote:
>> On 2014-11-12 18:23:38 -0500, Robert Haas wrote:
>
>> > > The problem is that it's very hard to avoid the wrong index's
>> > > relfilenode being used when swapping the relfilenodes between two
>> > > indexes.
>> >
>>
On Fri, Nov 14, 2014 at 11:47 AM, Andres Freund wrote:
> On 2014-11-13 11:41:18 -0500, Robert Haas wrote:
>> On Wed, Nov 12, 2014 at 7:31 PM, Andres Freund
>> wrote:
>> > But I think it won't work realistically. We have a *lot* of
>> > infrastructure that refers to indexes using it's primary key
On 2014-11-13 11:41:18 -0500, Robert Haas wrote:
> On Wed, Nov 12, 2014 at 7:31 PM, Andres Freund wrote:
> > But I think it won't work realistically. We have a *lot* of
> > infrastructure that refers to indexes using it's primary key. I don't
> > think we want to touch all those places to also dis
On 2014-11-14 02:04:00 -0600, Jim Nasby wrote:
> On 11/13/14, 3:50 PM, Andres Freund wrote:
> Having been responsible for a site where downtime was a 6 figure
> dollar amount per hour, I've spent a LOT of time worrying about lock
> problems. The really big issue here isn't grabbing an exclusive loc
On 11/13/14, 3:50 PM, Andres Freund wrote:
On November 13, 2014 10:23:41 PM CET, Peter Eisentraut wrote:
On 11/12/14 7:31 PM, Andres Freund wrote:
Yes, it sucks. But it beats not being able to reindex a relation with
a
primary key (referenced by a fkey) without waiting several hours by a
cou
On November 13, 2014 10:23:41 PM CET, Peter Eisentraut wrote:
>On 11/12/14 7:31 PM, Andres Freund wrote:
>> Yes, it sucks. But it beats not being able to reindex a relation with
>a
>> primary key (referenced by a fkey) without waiting several hours by a
>> couple magnitudes. And that's the current
On 11/12/14 7:31 PM, Andres Freund wrote:
> Yes, it sucks. But it beats not being able to reindex a relation with a
> primary key (referenced by a fkey) without waiting several hours by a
> couple magnitudes. And that's the current situation.
That's fine, but we have, for better or worse, defined
On Wed, Nov 12, 2014 at 7:31 PM, Andres Freund wrote:
> But I think it won't work realistically. We have a *lot* of
> infrastructure that refers to indexes using it's primary key. I don't
> think we want to touch all those places to also disambiguate on some
> other factor. All the relevant APIs a
On Thu, Nov 13, 2014 at 10:26 AM, Michael Paquier
wrote:
> On Thu, Nov 13, 2014 at 9:31 AM, Andres Freund wrote:
>> I don't recall what the problem with just swapping the names was - but
>> I'm pretty sure there was one... Hm. The index relation oids are
>> referred to by constraints and dependen
On Thu, Nov 13, 2014 at 9:31 AM, Andres Freund wrote:
> I don't recall what the problem with just swapping the names was - but
> I'm pretty sure there was one... Hm. The index relation oids are
> referred to by constraints and dependencies. That's somewhat
> solvable. But I think there was somethi
Andres Freund wrote:
> On 2014-11-12 18:23:38 -0500, Robert Haas wrote:
> > > The problem is that it's very hard to avoid the wrong index's
> > > relfilenode being used when swapping the relfilenodes between two
> > > indexes.
> >
> > How about storing both the old and new relfilenodes in the sam
On 2014-11-12 18:23:38 -0500, Robert Haas wrote:
> On Wed, Nov 12, 2014 at 4:39 PM, Andres Freund wrote:
> > On 2014-11-12 16:11:58 -0500, Robert Haas wrote:
> >> On Wed, Nov 12, 2014 at 4:10 PM, Robert Haas wrote:
> >> > On Thu, Nov 6, 2014 at 9:50 AM, Peter Eisentraut wrote:
> >> >> If REINDEX
On Wed, Nov 12, 2014 at 4:39 PM, Andres Freund wrote:
> On 2014-11-12 16:11:58 -0500, Robert Haas wrote:
>> On Wed, Nov 12, 2014 at 4:10 PM, Robert Haas wrote:
>> > On Thu, Nov 6, 2014 at 9:50 AM, Peter Eisentraut wrote:
>> >> If REINDEX cannot work without an exclusive lock, we should invent so
On 2014-11-12 16:11:58 -0500, Robert Haas wrote:
> On Wed, Nov 12, 2014 at 4:10 PM, Robert Haas wrote:
> > On Thu, Nov 6, 2014 at 9:50 AM, Peter Eisentraut wrote:
> >> If REINDEX cannot work without an exclusive lock, we should invent some
> >> other qualifier, like WITH FEWER LOCKS.
> >
> > What
On Wed, Nov 12, 2014 at 4:10 PM, Robert Haas wrote:
> On Thu, Nov 6, 2014 at 9:50 AM, Peter Eisentraut wrote:
>> If REINDEX cannot work without an exclusive lock, we should invent some
>> other qualifier, like WITH FEWER LOCKS.
>
> What he said.
But more to the point why, precisely, can't t
On Thu, Nov 6, 2014 at 9:50 AM, Peter Eisentraut wrote:
> If REINDEX cannot work without an exclusive lock, we should invent some
> other qualifier, like WITH FEWER LOCKS.
What he said.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgs
On Tue, Nov 11, 2014 at 3:24 AM, Jeff Janes wrote:
>
> On Wed, Nov 5, 2014 at 8:49 PM, Michael Paquier
> wrote:
>>
>> On Thu, Oct 30, 2014 at 5:19 PM, Michael Paquier
>> wrote:
>> > Updated patch is attached.
>> Please find attached an updated patch with the following things changed:
>> - Addit
On Wed, Nov 5, 2014 at 8:49 PM, Michael Paquier
wrote:
> On Thu, Oct 30, 2014 at 5:19 PM, Michael Paquier
> wrote:
> > Updated patch is attached.
> Please find attached an updated patch with the following things changed:
> - Addition of tab completion in psql for all new commands
> - Addition of
On 10/1/14 3:00 AM, Michael Paquier wrote:
> - Use of AccessExclusiveLock when swapping relfilenodes of an index and
> its concurrent entry instead of ShareUpdateExclusiveLock for safety. At
> the limit of my understanding, that's the consensus reached until now.
I'm very curious about this point.
On Thu, Oct 30, 2014 at 5:19 PM, Michael Paquier
wrote:
> Updated patch is attached.
Please find attached an updated patch with the following things changed:
- Addition of tab completion in psql for all new commands
- Addition of a call to WaitForLockers in index_concurrent_swap to
ensure that the
On 10/30/14, 3:19 AM, Michael Paquier wrote:
Thanks for your input, Jim!
On Wed, Oct 29, 2014 at 7:59 AM, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote:
> Patch applies against current HEAD and builds, but I'm getting 37 failed
> tests (mostly parallel, but also misc and WITH; results atta
On 10/30/14, 3:19 AM, Michael Paquier wrote:
On Wed, Oct 29, 2014 at 7:59 AM, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote:
> Patch applies against current HEAD and builds, but I'm getting 37 failed
> tests (mostly parallel, but also misc and WITH; results attached). Is that
> expected?
T
Thanks for your input, Jim!
On Wed, Oct 29, 2014 at 7:59 AM, Jim Nasby wrote:
> Patch applies against current HEAD and builds, but I'm getting 37 failed
> tests (mostly parallel, but also misc and WITH; results attached). Is that
> expected?
This is caused by the recent commit 7b1c2a0 (that I act
On Wed, Aug 27, 2014 at 3:53 PM, Michael Paquier
wrote:
> On Wed, Aug 27, 2014 at 3:41 PM, Andres Freund
> wrote:
> > Can you add it to the next CF? I'll try to look earlier, but can't
> > promise anything.
> >
> > I very much would like this to get committed in some form or another.
> Added it
On Wed, Aug 27, 2014 at 3:41 PM, Andres Freund wrote:
> Can you add it to the next CF? I'll try to look earlier, but can't
> promise anything.
>
> I very much would like this to get committed in some form or another.
Added it here to keep track of it:
https://commitfest.postgresql.org/action/patch
On 2014-08-27 11:00:56 +0900, Michael Paquier wrote:
> On Tue, Jan 21, 2014 at 10:12 PM, Michael Paquier
> wrote:
> > I have realigned this patch with latest head (d2458e3)... In case
> > someone is interested at some point...
> Attached is a patch for REINDEX CONCURRENTLY rebased on HEAD
> (d7938
On Tue, Jan 21, 2014 at 10:12 PM, Michael Paquier
wrote:
> I have realigned this patch with latest head (d2458e3)... In case
> someone is interested at some point...
Attached is a patch for REINDEX CONCURRENTLY rebased on HEAD
(d7938a4), as some people are showing interest in it by reading recent
Sorry for the lateness of this...
On 11/14/13, 8:40 PM, Michael Paquier wrote:
+ /*
+* Phase 4 of REINDEX CONCURRENTLY
+*
+* Now that the concurrent indexes have been validated could be used,
+* we need to swap each concurrent index with its corresponding ol
Michael Paquier escribió:
> Hi all,
>
> Please find attached updated patches for the support of REINDEX
> CONCURRENTLY, renamed 2.0 for the occasion:
> - 20131114_1_index_drop_comments.patch, patch that updates some
> comments in index_drop. This updates only a couple of comments in
> index_drop b
On 2013-11-18 19:52:29 +0900, Michael Paquier wrote:
> On Sat, Nov 16, 2013 at 5:09 AM, Andres Freund wrote:
> > On 2013-11-15 11:40:17 +0900, Michael Paquier wrote:
> >> - 20131114_3_reindex_concurrently.patch, providing the core feature.
> >> Patch 3 needs to have patch 2 applied first. Regressi
Hi,
On 2013-11-15 11:40:17 +0900, Michael Paquier wrote:
> - 20131114_3_reindex_concurrently.patch, providing the core feature.
> Patch 3 needs to have patch 2 applied first. Regression tests,
> isolation tests and documentation are included with the patch.
Have you addressed my concurrency conce
On 11/14/13, 9:40 PM, Michael Paquier wrote:
> Hi all,
>
> Please find attached updated patches for the support of REINDEX
> CONCURRENTLY, renamed 2.0 for the occasion:
> - 20131114_1_index_drop_comments.patch, patch that updates some
> comments in index_drop. This updates only a couple of comment
74 matches
Mail list logo
