Why release index relation lock

DT <kurt023@hotmail.com> writes:

1. What's the rule of index relation locking?
2. Releasing lock is for higher concurrency, but for INSERT/UPDATE, i did not find
any operation that could get benefit from releasing index relation lock? Or to
say : what will happen if we treat index relation lock like heap relation lock?

The reason we hold relation locks till end of transaction is mainly to
avoid transactional behavior surprises, eg an in-progress transaction
finding that a relation's schema has changed underneath it. There is
no corresponding risk for indexes, because there is no such thing as
a schema-definition change for an index --- short of dropping it,
which we disallow without having AccessExclusiveLock on the parent rel.
However, there are *physical* changes to indexes, such as REINDEX or
ALTER INDEX TABLESPACE, which require locking out other accesses till
they finish. So the point of locking indexes in use by a query is
just to interlock against those types of operations, and there's no
need to continue holding the lock once the query is done.

VACUUM might look like an exception, but it's not since it can't
run inside a transaction block. There's no meaningful difference
between statement end and transaction end for it.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi kurt023,

1. What's the rule of index relation locking?
2. Releasing lock is for higher concurrency, but for INSERT/UPDATE, i

did not find
any operation that could get benefit from releasing index relation lock?
Or to
say : what will happen if we treat index relation lock like heap
relation lock?

Ans]The reason we hold relation locks till end of transaction is mainly to
avoid transactional behavior surprises, eg an in-progress transaction
finding that a relation's schema has changed underneath it. There is
no corresponding risk for indexes, because there is no such thing as
a schema-definition change for an index --- short of dropping it,
which we disallow without having AccessExclusiveLock on the parent rel.
However, there are *physical* changes to indexes, such as REINDEX or
ALTER INDEX TABLESPACE, which require locking out other accesses till
they finish. So the point of locking indexes in use by a query is
just to interlock against those types of operations, and there's no
need to continue holding the lock once the query is done.

VACUUM might look like an exception, but it's not since it can't
run inside a transaction block. There's no meaningful difference
between statement end and transaction end for it.

Hope this helps.

With regards,
Sivananda Reddy.

On Sat, Nov 2, 2013 at 11:03 AM, DT <kurt023@hotmail.com> wrote: