SQLメモ

SELECT date_format(date_sub(current_date(), interval num day), '%Y/%m/%d') as day
FROM (SELECT row_number() OVER (ORDER BY TABLE_NAME) - 1 as num
      FROM information_schema.COLUMNS
      LIMIT 35) as nums
order by day;

SELECT to_char(generate_series, 'YYYY/MM/DD') as day
FROM generate_series(current_date - interval '35 days', current_date, '24 hours')
ORDER BY day;

select date_format(t.d, '%Y/%m/%d') as day
from (select sequence(current_date - interval '35' day,
                      current_date,
                      interval '1' day
             ) dates),
     unnest(dates) as t(d)
order by day;

SET SESSION max_execution_time=1000;

WITH mysleep(value) AS (SELECT SLEEP(3)) SELECT * FROM mysleep;

set statement_timeout to 1000;

SELECT pg_sleep(3);

begin;
explain (costs off, analyze on, verbose on)
delete
from workflow_step
where id = 10466387
  and version = 0;
rollback;

Delete on public.workflow_step (actual time=0.044..0.045 rows=0 loops=1)
  ->  Index Scan using workflow_step_pkey on public.workflow_step (actual time=0.018..0.019 rows=1 loops=1)
        Output: ctid
        Index Cond: (workflow_step.id = 10466387)
        Filter: (workflow_step.version = 0)
Planning Time: 0.059 ms
Trigger RI_ConstraintTrigger_a_16521 for constraint fkipk3uik8eqp54cf0ul996bu4o: time=790.544 calls=1
Trigger RI_ConstraintTrigger_a_16536 for constraint fkrln65du8n7hwu3qbinnea748g: time=0.156 calls=1
Execution Time: 790.767 ms

SELECT
  base.namespace_name
  , base.constraint_name
  , base.table_name
  , array_agg(pg_attribute.attname ORDER BY base.order) AS column_names
FROM (
  SELECT
    pg_constraint.conname AS constraint_name
    , pg_constraint.conrelid
    , pg_namespace.nspname AS namespace_name
    , pg_constraint.contype
    , pg_class.relname AS table_name
    , unnest(pg_constraint.conkey) AS conkey
    , generate_subscripts(pg_constraint.conkey, 1) AS order
  FROM
    pg_constraint
    INNER JOIN pg_class
      ON (pg_constraint.conrelid = pg_class.oid)
    INNER JOIN pg_namespace
      ON (pg_class.relnamespace = pg_namespace.oid)
) base
  INNER JOIN pg_attribute
    ON (base.conrelid = pg_attribute.attrelid AND base.conkey = pg_attribute.attnum)
WHERE
  base.contype = 'f' -- FKで絞る
GROUP BY
  base.namespace_name
  , base.constraint_name
  , base.table_name
;

CREATE INDEX CONCURRENTLY issue_xxxxx_workflow_step_error_handler_id on workflow_step (error_handler_id);

select
    w.*,
    w_trx.*,
    b_t.THREAD_ID as blocking_thread_id,
    w_t.THREAD_ID as waiting_thread_id
from
    sys.innodb_lock_waits w
left join
    information_schema.INNODB_TRX as b_trx
on
    w.blocking_trx_id = b_trx.trx_id
left join
    performance_schema.threads as b_t
on
    b_trx.trx_mysql_thread_id = b_t.PROCESSLIST_ID
left join
    information_schema.INNODB_TRX as w_trx
on
    w.waiting_trx_id = w_trx.trx_id
left join
    performance_schema.threads as w_t
on
    w_trx.trx_mysql_thread_id = w_t.PROCESSLIST_ID
-- 一定秒以上待ってるものに絞りたいなら
where w.wait_age > 3
order by
    w.waiting_trx_id;

SELECT *
FROM performance_schema.events_statements_history
WHERE thread_id = {blocking_thread_id or waiting_thread_id}
order by event_id desc
limit 5;

select * from performance_schema.setup_consumers where NAME like '%events_statements%';

SHOW VARIABLES LIKE 'performance_schema_events_statements_%';

select
    waiting.pid as w_pid,
    waiting.usename as w_username,
    now() - waiting.query_start as w_age,
    waiting.query as w_query,
    blocking.pid as b_pid,
    blocking.usename as b_username,
    now() - blocking.query_start as b_age,
    blocking.query as b_query
from (
    select distinct
        *,
        unnest(pg_blocking_pids(pid)) as blocking_pid
    from
        pg_stat_activity
    -- 一定秒以上待ってるものに絞りたいなら
    where now() - waiting.query_start > 3
) as waiting
left join
    pg_stat_activity as blocking
on
    waiting.blocking_pid = blocking.pid
order by
    waiting.pid,
    waiting.query,
    blocking.pid,
    blocking.query;

select
 l.pid,
 l.locktype,
 c.relname as table_name,
 l.page,
 l.tuple,
 l.transactionid,
 l.mode,
 l.granted,
 s.state
from
 pg_locks l
 left join pg_class c  on (l.relation=c.oid)
 left join pg_stat_activity s on (l.pid=s.pid)
where
 l.mode <> 'AccessShareLock'
 and
 l.pid  = {w_pid}
order by
 l.pid,
 l.locktype,
 c.relname,
 l.page,
 l.tuple,
 l.mode,
 l.granted,
 s.state;