Обсуждение: Status of gist locking in 8.1.3?
The release notes for 8.1, http://www.postgresql.org/docs/whatsnew, states about GIST >>indexing mechanism has improved to support the high-speed concurrency, >>recoverability and update performance As I write this I am creating an index with gist and trying to do a select on the table froze. Using Postgresql 8.1.3 The documents for GIST seem to imply that by 8.1 the lock issue would be resolved, http://www.sai.msu.su/~megera/oddmuse/index.cgi/GiST_Concurrency_Recovery. Is the locing an issue mostly at index creation time?
Francisco Reyes <lists@stringsutils.com> writes:
> As I write this I am creating an index with gist and trying to do a select
> on the table froze. Using Postgresql 8.1.3
CREATE INDEX shouldn't block any concurrent SELECT, regardless of which
index AM is involved. Can you provide a reproducible test case?
regards, tom lane
Tom Lane writes: > CREATE INDEX shouldn't block any concurrent SELECT, regardless of which > index AM is involved. The problem was that the table needed a "vacuum full". It was a large table and had done a massive update. It is not that it was blocked, but that it was just taking a very long time. Is there a way to tell what tables have locks on them?
Francisco Reyes wrote: > Tom Lane writes: > >> CREATE INDEX shouldn't block any concurrent SELECT, regardless of which >> index AM is involved. > > The problem was that the table needed a "vacuum full". It was a large > table and had done a massive update. It is not that it was blocked, but > that it was just taking a very long time. > > Is there a way to tell what tables have locks on them? SELECT * from pg_locks ; (version 7.4 and above at least, don't have an install earlier than that). -- Postgresql & php tutorials http://www.designmagick.com/
Chris writes: >> Is there a way to tell what tables have locks on them? > > SELECT * from pg_locks ; > > (version 7.4 and above at least, don't have an install earlier than that). And this is per DB right? Any way to tell locks in all DBs? In particular if planning to bounce back the DB would be nice to know if anyone had any locks open..
Francisco Reyes wrote: > Chris writes: > >>> Is there a way to tell what tables have locks on them? >> >> SELECT * from pg_locks ; >> >> (version 7.4 and above at least, don't have an install earlier than >> that). > > And this is per DB right? No, this is per system. Not sure how it works with permissions (eg if you have access to one db but not another does it still show?) but as a superuser, it will show all locks across the whole system. -- Postgresql & php tutorials http://www.designmagick.com/
Chris writes: >>> SELECT * from pg_locks ; >> And this is per DB right? > No, this is per system. On a DB doing no/little work I always see two records returned. One has a value in the 'database' column. How can I find what database it is? Looking for it in pg_database did not yield any databases with a matching number. The number in the 'database' column did not match any number of the columns in pg_database. The two records I always see are: locktype |....| mode | granted ---------------+....+-----------------+--------- transactionid |....| ExclusiveLock | t relation |....| AccessShareLock | t
Francisco Reyes <lists@stringsutils.com> writes: > The two records I always see are: > locktype |....| mode | granted > ---------------+....+-----------------+--------- > transactionid |....| ExclusiveLock | t > relation |....| AccessShareLock | t That would be your own transaction's lock on its own XID, and its share lock on the pg_locks view. Joining to pg_database.oid and pg_class.oid will help you interpret the numbers --- see http://www.postgresql.org/docs/8.1/static/view-pg-locks.html regards, tom lane