在PostgreSQL的小表上ALTER查询非常慢 [英] ALTER query very slow on tiny table in PostgreSQL

问题描述

我有PostgreSQL 9.2和一个很小的数据库，其中只有一个种子数据用于我正在工作的网站。

I've got PostgreSQL 9.2 and a tiny database with just a bit of seed data for a website that I'm working on.

以下查询似乎永远运行：

The following query seems to run forever:

ALTER TABLE diagnose_bodypart ADD description text NOT NULL;

diagnose_bodypart 是一个小于10的表行。我让查询运行了超过一分钟没有任何结果。可能是什么问题呢？有任何调试建议吗？

diagnose_bodypart is a table with less than 10 rows. I've let the query run for over a minute with no results. What could be the problem? Any recommendations for debugging this?

推荐答案

添加列不需要重写表（除非您指定默认）。这是没有任何锁的快速操作。正如Craig所指出的那样，pg_locks是检查的地方。

Adding a column does not require rewriting a table (unless you specify a DEFAULT). It is a quick operation absent any locks. pg_locks is the place to check, as Craig pointed out.

通常，最可能的原因是长时间的事务处理。我将查看哪些工作流正在冲击这些表，以及事务开放的时间。这种锁通常是事务性的，因此提交事务通常可以解决问题。

In general the most likely cause are long-running transactions. I would be looking at what work-flows are hitting these tables and how long the transactions are staying open for. Locks of this sort are typically transactional and so committing transactions will usually fix the problem.

这篇关于在PostgreSQL的小表上ALTER查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！