Thread: create index concurrently blocks on transactions in other databases

create index concurrently blocks on transactions in other databases

From
hubert depesz lubaczewski
Date:
steps to reproduce:
session1: connect using user1 to database1.
session2: connect using user2 to database2.
session1: create table x ( y int);
session2: begin;
session1: create index concurrently q on x(y);
it hangs.
session2: rollback/commit;
session1: finishes.

i dont really see why this would be neccessary - i understand that we
need to wait for transactions in the same db, but waiting for
transaction in another database?

i tested it on 8.2.4 and 8.3devel from cvs-head, fetched 3 days ago.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Re: create index concurrently blocks on transactions in other databases

From
Simon Riggs
Date:
On Thu, 2007-09-06 at 12:43 +0200, hubert depesz lubaczewski wrote:
> steps to reproduce:
> session1: connect using user1 to database1.
> session2: connect using user2 to database2.
> session1: create table x ( y int);
> session2: begin;
> session1: create index concurrently q on x(y);
> it hangs.
> session2: rollback/commit;
> session1: finishes.
>
> i dont really see why this would be neccessary - i understand that we
> need to wait for transactions in the same db, but waiting for
> transaction in another database?
>
> i tested it on 8.2.4 and 8.3devel from cvs-head, fetched 3 days ago.

Manual says "and in addition it must wait for all existing transactions
to terminate." This is needed for MVCC correctness and its not a bug.

http://www.postgresql.org/docs/8.2/static/sql-createindex.html

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Re: create index concurrently blocks on transactions in other databases

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> On Thu, 2007-09-06 at 12:43 +0200, hubert depesz lubaczewski wrote:
>> i dont really see why this would be neccessary - i understand that we
>> need to wait for transactions in the same db, but waiting for
>> transaction in another database?

> Manual says "and in addition it must wait for all existing transactions
> to terminate." This is needed for MVCC correctness and its not a bug.

No, it isn't needed for MVCC correctness.  We're just out-waiting
transactions that might try to use the index and not find what they
should in it.  AFAICS Hubert is right: there is no need to wait for
xacts in other DBs.  (At least for non-shared indexes, but we can't
support C.I.C. on shared tables anyway.)

This would have been painful to fix in 8.2 but it seems relatively
trivial in HEAD --- we just need another filter option in
GetCurrentVirtualXIDs.

            regards, tom lane

Re: create index concurrently blocks on transactions in other databases

From
Simon Riggs
Date:
On Thu, 2007-09-06 at 15:55 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Thu, 2007-09-06 at 12:43 +0200, hubert depesz lubaczewski wrote:
> >> i dont really see why this would be neccessary - i understand that we
> >> need to wait for transactions in the same db, but waiting for
> >> transaction in another database?
>
> > Manual says "and in addition it must wait for all existing transactions
> > to terminate." This is needed for MVCC correctness and its not a bug.
>
> No, it isn't needed for MVCC correctness.  We're just out-waiting
> transactions that might try to use the index and not find what they
> should in it.  AFAICS Hubert is right: there is no need to wait for
> xacts in other DBs.  (At least for non-shared indexes, but we can't
> support C.I.C. on shared tables anyway.)

My mistake; I misread the question. Apologies, Hubert.

> This would have been painful to fix in 8.2 but it seems relatively
> trivial in HEAD --- we just need another filter option in
> GetCurrentVirtualXIDs.

Patch posted to pgsql-patches.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Re: create index concurrently blocks on transactions in other databases

From
Simon Riggs
Date:
On Thu, 2007-09-06 at 12:43 +0200, hubert depesz lubaczewski wrote:
> steps to reproduce:
> session1: connect using user1 to database1.
> session2: connect using user2 to database2.
> session1: create table x ( y int);
> session2: begin;
> session1: create index concurrently q on x(y);
> it hangs.
> session2: rollback/commit;
> session1: finishes.

BTW, this is a brilliant example of why we need Concurrent psql.

It's very natural to express test cases in the manner shown.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com