[DOC] Document concurrent index builds waiting on each other

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
70 messages Options
1234
Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

James Coleman
On Wed, Jan 13, 2021 at 12:33 PM Alvaro Herrera <[hidden email]> wrote:

>
> On 2021-Jan-13, James Coleman wrote:
>
> > On Wed, Jan 13, 2021 at 12:58 AM Michael Paquier <[hidden email]> wrote:
> > >
> > > On Tue, Jan 12, 2021 at 04:51:39PM -0300, Alvaro Herrera wrote:
> > > > I looked into this again, and I didn't like what I had added to
> > > > maintenance.sgml at all.  It seems out of place where I put it; and I
> > > > couldn't find any great spots.  Going back to your original proposal,
> > > > what about something like this?  It's just one more para in the "notes"
> > > > section in CREATE INDEX and REINDEX pages, without any additions to the
> > > > VACUUM pages.
> > >
> > > +1.
> >
> > I think one more para in the notes is good. But shouldn't we still
> > clarify the issue is specific to CONCURRENTLY?
>
> How is it specific to concurrent builds?  What we're documenting here is
> the behavior of vacuum, and that one is identical in both regular builds
> and concurrent builds (since vacuum has to avoid removing rows from
> under either of them).  The only reason concurrent builds are
> interesting is because they take longer.
>
> What was specific to concurrent builds was the fact that you can't have
> more than one at a time, and that one is what was added in 58ebe967f.

Ah, right. I've mixed those up at least once on this thread already.

> > Also that it's not just the table being indexed seems fairly significant.
>
> This is true.  So I propose
>
>     Like any long-running transaction, <command>REINDEX</command> can
>     affect which tuples can be removed by concurrent <command>VACUUM</command>
>     on any table.

That sounds good to me.

James


Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

Álvaro Herrera
On 2021-Jan-13, James Coleman wrote:

> On Wed, Jan 13, 2021 at 12:33 PM Alvaro Herrera <[hidden email]> wrote:

> > This is true.  So I propose
> >
> >     Like any long-running transaction, <command>REINDEX</command> can
> >     affect which tuples can be removed by concurrent <command>VACUUM</command>
> >     on any table.
>
> That sounds good to me.

Great, pushed with one more wording tweak: "REINDEX on any table can
affect ... on any other table".  To pg12 and up.

I wondered about noting whether only processes in the current database
are affected, but then I noticed that the current code since commit
dc7420c2c927 uses a completely different algorithm than what we had with
GetOldestXmin() and does not consider database boundaries at all.
This doesn't sound great to me, since a misbehaved database can now
affect others ...  Maybe I misunderstand that code.

--
Álvaro Herrera                            39°49'30"S 73°17'W
"This is what I like so much about PostgreSQL.  Most of the surprises
are of the "oh wow!  That's cool" Not the "oh shit!" kind.  :)"
Scott Marlowe, http://archives.postgresql.org/pgsql-admin/2008-10/msg00152.php


Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

James Coleman
On Wed, Jan 13, 2021 at 4:05 PM Alvaro Herrera <[hidden email]> wrote:

>
> On 2021-Jan-13, James Coleman wrote:
>
> > On Wed, Jan 13, 2021 at 12:33 PM Alvaro Herrera <[hidden email]> wrote:
>
> > > This is true.  So I propose
> > >
> > >     Like any long-running transaction, <command>REINDEX</command> can
> > >     affect which tuples can be removed by concurrent <command>VACUUM</command>
> > >     on any table.
> >
> > That sounds good to me.
>
> Great, pushed with one more wording tweak: "REINDEX on any table can
> affect ... on any other table".  To pg12 and up.

Looks like what got committed is "REINDEX on a table" not "on any",
but I'm not sure that matters too much.

James


Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

Álvaro Herrera
In reply to this post by Álvaro Herrera
On 2021-Jan-13, Alvaro Herrera wrote:

> I wondered about noting whether only processes in the current database
> are affected, but then I noticed that the current code since commit
> dc7420c2c927 uses a completely different algorithm than what we had with
> GetOldestXmin() and does not consider database boundaries at all.
> This doesn't sound great to me, since a misbehaved database can now
> affect others ...  Maybe I misunderstand that code.

This appears to be false, per ComputeXidHorizons.

--
Álvaro Herrera                            39°49'30"S 73°17'W
"Ni aún el genio muy grande llegaría muy lejos
si tuviera que sacarlo todo de su propio interior" (Goethe)


Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

Álvaro Herrera
In reply to this post by James Coleman
On 2021-Jan-13, James Coleman wrote:

> On Wed, Jan 13, 2021 at 4:05 PM Alvaro Herrera <[hidden email]> wrote:
> >
> > On 2021-Jan-13, James Coleman wrote:
> >
> > > On Wed, Jan 13, 2021 at 12:33 PM Alvaro Herrera <[hidden email]> wrote:
> >
> > > > This is true.  So I propose
> > > >
> > > >     Like any long-running transaction, <command>REINDEX</command> can
> > > >     affect which tuples can be removed by concurrent <command>VACUUM</command>
> > > >     on any table.
> > >
> > > That sounds good to me.
> >
> > Great, pushed with one more wording tweak: "REINDEX on any table can
> > affect ... on any other table".  To pg12 and up.
>
> Looks like what got committed is "REINDEX on a table" not "on any",
> but I'm not sure that matters too much.

Ouch.  The difference seems slight enough that it doesn't matter; is it
ungrammatical?

Either way I'm gonna close this CF entry now, finally.  Thank you for
your patience!

--
Álvaro Herrera       Valdivia, Chile
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu.  Five minutes later I realize that it's also talking
about food" (Donald Knuth)


Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

Tom Lane-2
Alvaro Herrera <[hidden email]> writes:
> On 2021-Jan-13, James Coleman wrote:
>>>> This is true.  So I propose
>>>> Like any long-running transaction, <command>REINDEX</command> can
>>>> affect which tuples can be removed by concurrent <command>VACUUM</command>
>>>> on any table.

>> Looks like what got committed is "REINDEX on a table" not "on any",
>> but I'm not sure that matters too much.

> Ouch.  The difference seems slight enough that it doesn't matter; is it
> ungrammatical?

I'd personally have written "on other tables" or "on another table",
or left out that clause altogether and just said "concurrent
<command>VACUUM</command>".  I'm not sure it's ungrammatical exactly,
but the antecedent of "a table" is a bit unclear; people might
wonder if it means the table being reindexed.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

James Coleman
On Wed, Jan 13, 2021 at 4:29 PM Tom Lane <[hidden email]> wrote:

>
> Alvaro Herrera <[hidden email]> writes:
> > On 2021-Jan-13, James Coleman wrote:
> >>>> This is true.  So I propose
> >>>> Like any long-running transaction, <command>REINDEX</command> can
> >>>> affect which tuples can be removed by concurrent <command>VACUUM</command>
> >>>> on any table.
>
> >> Looks like what got committed is "REINDEX on a table" not "on any",
> >> but I'm not sure that matters too much.
>
> > Ouch.  The difference seems slight enough that it doesn't matter; is it
> > ungrammatical?
>
> I'd personally have written "on other tables" or "on another table",
> or left out that clause altogether and just said "concurrent
> <command>VACUUM</command>".  I'm not sure it's ungrammatical exactly,
> but the antecedent of "a table" is a bit unclear; people might
> wonder if it means the table being reindexed.

It does mean the table being reindexed; the last phrase says "any
table" meaning "any other table".

James


Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

Tom Lane-2
James Coleman <[hidden email]> writes:
> On Wed, Jan 13, 2021 at 4:29 PM Tom Lane <[hidden email]> wrote:
>> but the antecedent of "a table" is a bit unclear; people might
>> wonder if it means the table being reindexed.

> It does mean the table being reindexed; the last phrase says "any
> table" meaning "any other table".

[ raised eyebrow ]  Surely REINDEX and VACUUM can't run on the same
table at the same time.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

James Coleman
On Wed, Jan 13, 2021 at 5:00 PM Tom Lane <[hidden email]> wrote:

>
> James Coleman <[hidden email]> writes:
> > On Wed, Jan 13, 2021 at 4:29 PM Tom Lane <[hidden email]> wrote:
> >> but the antecedent of "a table" is a bit unclear; people might
> >> wonder if it means the table being reindexed.
>
> > It does mean the table being reindexed; the last phrase says "any
> > table" meaning "any other table".
>
> [ raised eyebrow ]  Surely REINDEX and VACUUM can't run on the same
> table at the same time.

+ Like any long-running transaction, <command>CREATE INDEX</command> on a
+ table can affect which tuples can be removed by concurrent
+ <command>VACUUM</command> on any other table.

The "on a table" is the table on which the REINDEX/CREATE INDEX is
occurring. The "any other table" is where VACUUM might run.

James


Reply | Threaded
Open this post in threaded view
|

Re: [DOC] Document concurrent index builds waiting on each other

Tom Lane-2
James Coleman <[hidden email]> writes:
> On Wed, Jan 13, 2021 at 5:00 PM Tom Lane <[hidden email]> wrote:
>> [ raised eyebrow ]  Surely REINDEX and VACUUM can't run on the same
>> table at the same time.

> + Like any long-running transaction, <command>CREATE INDEX</command> on a
> + table can affect which tuples can be removed by concurrent
> + <command>VACUUM</command> on any other table.

> The "on a table" is the table on which the REINDEX/CREATE INDEX is
> occurring. The "any other table" is where VACUUM might run.

I still think it'd be just as clear without the auxiliary clauses,
say

+ Like any long-running transaction, <command>CREATE INDEX</command>
+ can affect which tuples can be removed by concurrent
+ <command>VACUUM</command> operations.

                        regards, tom lane


1234