create index concurrently blocks on transactions in other databases
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
On Thu, 2007-09-06 at 21:47 +0100, Simon Riggs wrote:
On Thu, 2007-09-06 at 15:55 -0400, Tom Lane wrote:
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 enclosed. Tested manually on same/different databases.
No docs changed, nor test cases added.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
Attachments:
cic.v1.patchtext/x-patch; charset=UTF-8; name=cic.v1.patchDownload+10-6
Simon Riggs <simon@2ndquadrant.com> writes:
On Thu, 2007-09-06 at 15:55 -0400, Tom Lane wrote:
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 enclosed. Tested manually on same/different databases.
No docs changed, nor test cases added.
Applied. I changed the docs thus
- addition it must wait for all existing transactions to terminate. Thus
+ addition it must wait for all existing transactions that could potentially
+ use the index to terminate. Thuswhich is a bit vague about the details but perhaps that's just as well.
regards, tom lane