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)
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
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
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
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