Dieses Statement:
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
sagt folgendes:
blocked_pid blocked_user blocking_pid blocking_user blocked_statement current_statement_in_blocking_process
3624 qa 2404 qa REINDEX INDEX ix_uid_ksp170622 SELECT s.NAME, r.ID, TO_DATE(to_char(r.TEST_DATE, '99999999'), 'YYYYMMDD'), r.EXEC_FINISHED FROM TASK_SUITE_RUN r, TASK_SUITE s WHERE r.TASKSUITE_ID=s.ID AND TEST_DATE <= 20170623 AND TEST_DATE >= 20170609
Der reindex soll auf der Tabelle signalplan_range_xxxxxx durchgeführt werden.
Mit folgender Query kann ich mir die aktiven locks ansehen:
CREATE OR REPLACE VIEW public.active_locks AS
SELECT t.schemaname,
t.relname,
l.locktype,
l.page,
l.virtualtransaction,
l.pid,
l.mode,
l.granted
FROM pg_locks l
JOIN pg_stat_all_tables t ON l.relation = t.relid
WHERE t.schemaname <> 'pg_toast'::name AND t.schemaname <> 'pg_catalog'::name
ORDER BY t.schemaname, t.relname;
SELECT * FROM active_locks;
Darin finde ich folgends:
schemaname relname locktype page virtualtransaction pid mode granted
qa signalplan_range_170622 relation <null> 8/2759 2404 AccessShareLock true
und
schemaname relname locktype page virtualtransaction pid mode granted
qa signalplan_range_170622 relation <null> 11/1531 3624 ShareLock true
Leider kann ich zur pid = 2404 die dazugehörige Query nicht finden.
Die Query = SELECT s.NAME, r.ID, TO_DATE(to_char(r.TEST_DATE, ‘99999999’), ‘YYYYMMDD’), r.EXEC_FINISHED FROM TASK_SUITE_RUN r, TASK_SUITE s WHERE r.TASKSUITE_ID=s.ID AND TEST_DATE <= 20170623 AND TEST_DATE >= 20170609
kann ich mir kaum vorstellen, da diese Query überhaupt nicht auf die Tabelle signalplan_range_xxxx zugreift.
Wo liegt mein Denkfehler?