Re: When/if to Reindex

On 8/22/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:

> 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