Re: does reindex need exclusive table access?

v8.3.4 on linux.

Does "reindex table foo" require no other users accessing the foo table? Trying to understand why this seems to be stalled when I attempt this on a live DB (if runs fine/fast on a copy of the DB that no one uses).

Should I run this inside a transaction?

Thanks in Advance !

On 01/11/11 02:51, Gauthier, Dave wrote:
> v8.3.4 on linux.
>
>
>
> Does "reindex table foo" require no other users accessing the foo
> table? Trying to understand why this seems to be stalled when I attempt
> this on a live DB (if runs fine/fast on a copy of the DB that no one uses).

Yes, it requires an exclusive lock.

It doesn't matter whether or not you run it as part of an explicit
transaction.

There is not currently any 'REINDEX CONCURRENTLY' command - not unless
it's been added in a very recent version and I haven't noticed yet. You
can CREATE INDEX CONCURRENTLY then drop the old index, though.

A workaround for reindexing while live is to begin a transaction, create
the new index with a new name, drop the old one, rename the new one to
the old one, and commit. This only requires an exclusive lock for the
period of the drop and rename. On more recent versions you can even use
this for indexes that implement primary key or unique constrants by
using the 'ADD CONSTRAINT ... USING INDEX ...' syntax; see:

http://www.postgresql.org/docs/current/static/sql-altertable.html

--
Craig Ringer

Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> writes:
> On 01/11/11 02:51, Gauthier, Dave wrote:
>> Does "reindex table foo" require no other users accessing the foo
>> table? Trying to understand why this seems to be stalled when I attempt
>> this on a live DB (if runs fine/fast on a copy of the DB that no one uses).

> Yes, it requires an exclusive lock.
> There is not currently any 'REINDEX CONCURRENTLY' command - not unless
> it's been added in a very recent version and I haven't noticed yet. You
> can CREATE INDEX CONCURRENTLY then drop the old index, though.

Yeah. The hard part of that is not actually the "reindex", it's the
"drop the old index" ... because the old index might be in use by
concurrent SELECTs. So dropping the old index absolutely requires an
exclusive lock, to ensure there are no read-only transactions depending
on that version of the index. Building a new index can be done with
a much weaker lock.

A straight reindex doesn't have a lot of choice here. We could have it
take a lesser lock while it's rebuilding the index, and then try to
upgrade to exclusive lock to move the new version into place --- but
upgrading your lock is a well-known recipe for causing deadlocks.

The one good thing about build-a-new-index-with-REINDEX-CONCURRENTLY-
and-then-drop-the-old-index is that the DROP requires exclusive lock
for only a tiny amount of time, and if the DROP does fail and roll back
because of conflicts, you haven't lost the work of building the new
index version. You can just try the DROP again.

> A workaround for reindexing while live is to begin a transaction, create
> the new index with a new name, drop the old one, rename the new one to
> the old one, and commit. This only requires an exclusive lock for the
> period of the drop and rename.

I'd do that in two transactions, so you don't lose the rebuild work
if there's a problem getting exclusive lock.

regards, tom lane

On Tuesday 01 November 2011 12:00:33 Craig Ringer wrote:
> A workaround for reindexing while live is to begin a transaction, create
> the new index with a new name, drop the old one, rename the new one to
> the old one, and commit. This only requires an exclusive lock for the
> period of the drop and rename. On more recent versions you can even use
> this for indexes that implement primary key or unique constrants by
> using the 'ADD CONSTRAINT ... USING INDEX ...' syntax; see:
>
> http://www.postgresql.org/docs/current/static/sql-altertable.html

Been happily doing this (without worrying about transactions, as Tom
suggested), but couldn't quite figure out how to do it with my primary key
indexes on 8.3. Do I have to bite the bullet and take an exclusive lock (or
upgrade to 9.1) ?

The technique kinda works (with some changes) using unique indexes however. Is
there a functional difference between a unique index and a primary key index
(knowing that my column is not null) ? Or is it just for documentation and ORM
purposes ?

--
Vincent de Phily

Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)fr> writes:
> The technique kinda works (with some changes) using unique indexes however. Is
> there a functional difference between a unique index and a primary key index
> (knowing that my column is not null) ? Or is it just for documentation and ORM
> purposes ?

The only functional difference is that a foreign key declaration
referring to the table ("REFERENCES tabname") will default to the pkey
column list. If you haven't got a declared pkey then you have to spell
out the column list.

regards, tom lane