Re: CREATE INDEX CONCURRENTLY?

Am Freitag, den 31.10.2014, 14:43 +0000 schrieb Greg Stark:
> On Fri, Oct 31, 2014 at 2:28 PM, Mark Woodward
> <mark.woodward@actifio.com> wrote:
> > I have not kept up with PostgreSQL changes and have just been using it. A
> > co-worker recently told me that you need to word "CONCURRENTLY" in "CREATE
> > INDEX" to avoid table locking. I called BS on this because to my knowledge
> > PostgreSQL does not lock tables. I referenced this page in the
> > documentation:
> 
> You can read from tables while a normal index build is in progress but
> you can't insert, update, or delete from them. CREATE INDEX
> CONCURRENTLY allows you to insert, update, and delete data while the
> index build is running at the expense of having the index build take
> longer.

I believe there is one caveat: If there is an idle-in-transaction
backend from before the start of CREATE INDEX CONCURRENTLY, it can hold
up the index creation indefinitely as long as it doesn't commit.

src/backend/access/heap/README.HOT mentions this WRT CIC: "Then we wait
until every transaction that could have a snapshot older than the second
reference snapshot is finished.  This ensures that nobody is alive any
longer who could need to see any tuples that might be missing from the
index, as well as ensuring that no one can see any inconsistent rows in
a broken HOT chain (the first condition is stronger than the second)."

I have seen CIC stall at clients when there were (seemlingy) unrelated
idle-in-transactions open (their locks even touching only other
schemas). I believe it depends on the specific locks that the other
backend acquired, but at least with a DECLARE CURSOR I can trivially
reproduce it:

first session:

postgres=# CREATE SCHEMA foo1;
CREATE SCHEMA
postgres=# CREATE TABLE foo1.foo1 (id int);
CREATE TABLE
postgres=# CREATE SCHEMA foo2;
CREATE SCHEMA
postgres=# CREATE TABLE foo2.foo2 (id int);
CREATE TABLE

second session:

postgres=# BEGIN; DECLARE c1 CURSOR FOR SELECT * FROM foo1.foo1;
BEGIN
DECLARE CURSOR

first session:

postgres=# CREATE INDEX CONCURRENTLY ixfoo2 ON foo2.foo2(id);
(hangs)

I wonder whether that is pilot error (fair enough), or whether something
could be done about this?


Michael

-- 
Michael Banck
Projektleiter / Berater
Tel.: +49 (2161) 4643-171
Fax:  +49 (2161) 4643-100
Email: michael.banck@credativ.de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Hohenzollernstr. 133, 41061 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer