PostgreSQL是什么锁定了我的表/模式

798qvoo8 于 2023-04-05 发布在 PostgreSQL

关注(0)|答案(2)|浏览(150)

我们有一个PostgreSQL 12.7数据库，它有多个模式，每个模式都被分配给一个特定的测试环境。
在我们的数据模型演化过程中，我们有一个ALTER TABLE my_table DROP COLUMN my_column SQL需要通过Flyway BD演化工具执行。我们的Sping Boot 应用程序无法启动，因为Flyway超时失败。
以DBA身份连接到数据库我尝试手动运行DROP COLUMN语句，但没有结果。它一直在运行。该表是一个非常小的表，只有112行，我们以前在不同的PostgreSQL数据库上使用DROP TABLE语句时遇到过非常类似的问题（相同版本），唯一的解决方法是销毁数据库并在AWS中重新创建它。由于连接到它的多个测试环境，我们无法对该数据库示例执行相同的操作。它会影响每个人。我尝试使用DROP SCHEMA my_schema CASCADE删除该特定环境的模式，等了10多分钟后，我又不得不取消了。
另一方面，我的感觉是，这只是一个时间问题，当这将发生在PROD，所以我们需要找到一种方法来解决这类问题。
任何帮助都将不胜感激。

UPDATE我运行了建议查询：

SELECT pid,
       usename,
       pg_blocking_pids(pid) as blocked_by
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0
   AND query ='ALTER TABLE my_table DROP COLUMN my_column;';

结果是

pid  | usename  |               blocked_by
-------+----------+-----------------------------------------
 29688 | dba_root | {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
(1 row)

如果取消ALTER TABLE ...，则上述查询不会产生任何结果。

postgresql

来源：https://stackoverflow.com/questions/75872684/postgresql-what-is-locking-my-table-schema

2条答案

按热度按时间

yquaqz181#

由于阻塞进程ID显示为0，因此它们可能是已准备好的事务。查看pg_prepared_xacts，看看是否有任何已准备好的事务被错误的应用程序未能清理。

赞(0）回复(0）举报 2023-04-05

g6ll5ycj2#

您可以尝试运行此查询：

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;

赞(0）回复(0）举报 2023-04-05

我来回答

PostgreSQL是什么锁定了我的表/模式

2条答案

相关问题

热门标签

最新问答