Thread: Create Index Locking Question

Create Index Locking Question

From
Mike C
Date:
I need to create a new index on an existing table. The table is constantly inserted into every second. Does CREATE INDEX require an exclusive write lock on the table? If it does have a write lock what happens to the insert statements at the time, are they simply played later or do they fail?

From http://www.postgresql.org/docs/8.1/interactive/sql-reindex.html I see CREATE INDEX apparently requires a write lock, but I'm not 100% certain because the CREATE INDEX was used to describe part of the REINDEX command.

The CREATE INDEX page makes no mention of locking issues: http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html

Could someone please clarify for me: Will I lose the INSERT statements performed while a CREATE INDEX is running?

Regards,

Mike C.

Re: Create Index Locking Question

From
Tom Lane
Date:
Mike C <smith.not.western@gmail.com> writes:
> I need to create a new index on an existing table. The table is constantly
> inserted into every second. Does CREATE INDEX require an exclusive write
> lock on the table? If it does have a write lock what happens to the insert
> statements at the time, are they simply played later or do they fail?

The inserting transactions are blocked until the CREATE INDEX is done.

            regards, tom lane