postgres=# create table test (i integer);
CREATE TABLE
postgres=# insert into test select generate_series(1,1000);
INSERT 0 1000
postgres=# create or replace function slow(integer) returns integer as 'begin perform pg_sleep(0); return $1; end' language plpgsql immutable strict;
CREATE FUNCTION
postgres=# create index slowi on test (slow(i));
CREATE INDEX
postgres=# create or replace function slow(integer) returns integer as 'begin perform pg_sleep(1); return $1; end' language plpgsql immutable strict;
CREATE FUNCTION
postgres=# reindex index slowi;
While that's running I ran:
postgres=# select count(*) from test;
count
-------
1000
(1 row)
Interestingly enough, the example you've given does not work for me either. The select count(*) from test blocks until the reindex completes. Are we using the same pg version?
# select version();
version
--------------------------------------------------------------------------------
----------------
PostgreSQL 8.2.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518
(1 row)
Looking at the pg_locks table, I see:
# select locktype,relation,mode,granted from pg_locks where not granted;
locktype | relation | mode | granted
----------+----------+-----------------+---------
relation | 69293 | AccessShareLock | f
(1 row)
# select relname from pg_class where oid = 69293;
relname
---------
slowi
(1 row)
# select locktype,relation,mode,granted from pg_locks where relation = 69293;
locktype | relation | mode | granted
----------+----------+---------------------+---------
relation | 69293 | AccessShareLock | f
relation | 69293 | AccessExclusiveLock | t
(2 rows)
So the reindex statement has an AccessExclusiveLock on the index, which seems right, and this blocks the select count(*) from getting an AccessShareLock on the index. Why does the select count(*) need a lock on the index? Is there some Postgres setting that could cause this behaviour? I can't even do an "explain select count(*) from test" without blocking.
Any ideas?
Steve