Вопрос по database, postgresql, sql – Как снять возможные блокировки строк Postgres?

27

Я запустил оператор обновления для большой таблицы PostgreSQL через интерфейс phpPgAdmin. Время истекло, так как оно длилось слишком долго.

Теперь я могу обновить некоторые строки из этой таблицы, но не все. Попытка обновить некоторые строки будет зависать.

Строки заблокированы? Как я могу разрешить обновление этих строк?

Ваш Ответ

5   ответов
-2

Error: User Rate Limit Exceeded

11

Simple:

Get the active locks from pg_locks:

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc;

Copy the pid(ex: 14210) from above result and substitute in the below command.

SELECT pg_terminate_backend('14210')

Error: User Rate Limit Exceeded
0

Error: User Rate Limit Exceeded

  1. Find long-running queries in your DB by running the following query. This will help you fetch the PIDs of the long-running query which is blocking your update.

    SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
    FROM pg_stat_activity
    WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
    
  2. or if you can find out which processes are holding a lock on a particular table by running this query

    SELECT *
    FROM pg_locks l
    JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'
    WHERE t.relname = 'Bill';
    
  3. Once you figure out the PID which is 'active' and blocking your update you can kill it by running this query. It takes some time to kill the process.

    SELECT pg_cancel_backend(__pid__);
    
  4. Check by running Query 2 if the process is killed. If it still is active then kill this process by running this query.

    SELECT pg_terminate_backend(__pid__);
    
70

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

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;

Error: User Rate Limit Exceeded

SELECT * FROM active_locks;

Error: User Rate Limit Exceeded

SELECT pg_cancel_backend('%pid%');

Error: User Rate Limit ExceededError: User Rate Limit Exceeded

Error: User Rate Limit Exceededactive_locksError: User Rate Limit Exceededpg_locks?
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceededpg_cancel_backendError: User Rate Limit Exceededstackoverflow.com/a/10317371/301277Error: User Rate Limit Exceeded
20

Error: User Rate Limit Exceeded

Error: User Rate Limit ExceededError: User Rate Limit ExceededError: User Rate Limit Exceeded

ps auxwww|grep ^postgres

Error: User Rate Limit ExceededpostmasterError: User Rate Limit Exceeded

Error: User Rate Limit ExceededSELECTError: User Rate Limit Exceededpg_stat_activityError: User Rate Limit Exceededcurrent_queryError: User Rate Limit Exceededquery_startError: User Rate Limit Exceeded

Error: User Rate Limit ExceededpostgresError: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded Liam
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceededpostgres 15398 ... idle in transactionError: User Rate Limit Exceededkill 15398Error: User Rate Limit Exceeded

Похожие вопросы