From: Steven Flatt <steven.flatt@gmail.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Cc: Vivek Khera <vivek@khera.org>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Date: Thu, 23 Aug 2007 18:25:25 -0400
Message-ID: <357fa7590708231525h2f48ce4dw3e48ecca5ccddf72@mail.gmail.com> (raw)
In-Reply-To: <873aybw2le.fsf@oxford.xeocode.com>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
<357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
<20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
<11801.1186624308@sss.pgh.pa.us>
<357fa7590708090651p71b9bc47n66739f8dbbb4ff57@mail.gmail.com>
<357fa7590708220955h441cd75ap4c0f0342a1c30a14@mail.gmail.com>
<873aybw2le.fsf@oxford.xeocode.com>
On 8/22/07, Gregory Stark <stark@enterprisedb.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: steven.flatt@gmail.com, stark@enterprisedb.com, tgl@sss.pgh.pa.us, wmoran@collaborativefusion.com, vivek@khera.org
Subject: Re: When/if to Reindex
In-Reply-To: <357fa7590708231525h2f48ce4dw3e48ecca5ccddf72@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox