On 02/16/2017 08:45 AM, Tim Bellis wrote:
Thank you all - that's really useful :-)
The other query that gets blocked behind the vacuum is the below (truncated).
This query is generated by jdbc in this method:
org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseMetaData.java:4023)
Is JDBC doing anything else before issuing this?
Even though this is a read only query, is it also expected to be blocked behind
the vacuum? Is there a way of getting indexes for a table which won't be
blocked behind a vacuum?
Table 13.2 here:
https://www.postgresql.org/docs/9.5/static/explicit-locking.html
shows the conflicts with SHARE UPDATE EXCLUSIVE(vacuum).
pg_locks:
https://www.postgresql.org/docs/9.5/static/view-pg-locks.html
shows locks being held. So next time it happens I would take a look and
see if you can work backwards from there.
You could directly access the index information using:
https://www.postgresql.org/docs/9.6/static/catalog-pg-index.html
https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html
Thank you all again,
Tim
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, NOT
i.indisunique AS NON_UNIQUE, NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME,
CASE i.indisclustered WHEN true THEN 1 ELSE CASE am.amname WHEN 'hash'
THEN 2 ELSE 3 END END AS TYPE, (i.keys).n AS ORDINAL_POSITION,
pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false) AS COLUMN_NAME, CASE
am.amcanorder WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1
WHEN 1 THEN 'D' ELSE 'A' END ELSE NULL END AS ASC_OR_DESC,
ci.reltuples AS CARDINALITY, ci.relpages AS PAGES,
pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION FROM
pg_catalog.pg_class ct JOIN pg_catalog.pg_namespace n ON (ct.relnamespace =
n.oid) JOIN (SELECT i.indexrelid, i.indrelid, i.indoption,
i.indisunique, i.indisclustered, i.indpred, i.indexprs,
information_schema._pg_expandarray(i.indkey) AS keys FROM
pg_catalog.pg_index i) i ON (ct.oid = i.ind
This query is cut off so cannot say whether it is the issue or not.
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
