Re: When/if to Reindex

["Steven Flatt" <steven.flatt@xxxxxxxxx>]

On 8/22/07, Gregory Stark <stark@xxxxxxxxxxxxxxxx> 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