Re: When/if to Reindex

"Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:

> However I'm seeing that all readers of that table are blocked until the
> reindex finishes, even reads that do not attempt to use the index. Is this
> a problem with the docs or a bug?

You'll have to describe in more detail what you're doing so we can see what's
causing it to not work for you because "works for me":

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)

> I'm considering creating a new index with the same definition as the first
> (different name), so while that index is being created, read access to the
> table, and the original index, is not blocked. When the new index is
> created, drop the original index and rename the new index to the original,
> and we've essentially accomplished the same thing. In fact, why isn't
> reindex doing this sort of thing in the background anways?

It is but one level lower down. But the locks which block people from using
the index must be at this level. Consider for example that one of the
operations someone might be doing is creating a foreign key which depends on
this index. If we created a new index and then tried to drop this one the drop
would fail because of the foreign key which needs it. It's possible these
problems could all be worked out but it would still take quite a bit of work
to do so.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com