Am Freitag, den 31.10.2014, 14:43 +0000 schrieb Greg Stark: > On Fri, Oct 31, 2014 at 2:28 PM, Mark Woodward > <mark.woodw...@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.ba...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
