Alvaro Herrera <alvherre@commandprompt.com> writes:
> Hannu Krosing wrote:
>> Would this take effect even inside a single command ? in other words, if
>> it were possible that an index appeared in middle of a big update, would
>> the tuples updated after the index becomes visible be also added to the
>> index ?
> This can't happen, because an UPDATE to a table requires acquiring a
> lock (RowExclusiveLock) which conflicts with a lock taken by the CREATE
> INDEX (ShareLock).
Right. By and large, schema-changing operations take AccessExclusive
lock on the table they are changing, which guarantees (a) no concurrent
operation is is touching the table, and (b) by the time the lock is
released, the schema-changing command is already committed and so its
catalog changes appear valid to any subsequent transactions touching the
table, since they look at the catalogs with SnapshotNow rules.
CREATE INDEX is the only schema-changing op that I can think of offhand
that takes a non-exclusive lock, and so its little problem with two
concurrent operations on the same table is unique.
regards, tom lane
