创建新表时 Postgres 客户端锁定 [英] Postgres client locking up when creating new table

问题描述

我正在尝试在 postgres 中创建一个新表，但是当我这样做时，它只是在 CREATE TABLE 调用后挂起.

$ sudo usermod -s/bin/bash postgres$ sudo su - postgrespostgres@host:~$ psql rating_analyticspsql (8.4.8)输入帮助"以获得帮助.排名分析=#开始；开始ranking_analytics=# 创建表about_contactmessage"(ranking_analytics(#"id" 序列号 NOT NULL PRIMARY KEY,排名分析(#user_id"整数非空引用auth_user"(id")DEFERRABLE INITIALLY DEFERRED，ranking_analytics(# "subject" text NOT NULL,ranking_analytics(# "message" text NOT NULL,ranking_analytics(#recorded_time"时间戳，时区非空排名分析(#)排名分析-#;注意:CREATE TABLE 将为串行列about_contactmessage.id"创建隐式序列about_contactmessage_id_seq"

然后它会一直呆在这里直到我CTRL-C它.

数据库中还有其他表，而这个表尚不存在:

ranking_analytics=# \d about_contactmessage没有找到任何名为about_contactmessage"的关系.

我可以毫无问题地对数据库中的其他表执行插入和删除查询:

ranking_analytics=#insert intolocations_continent (continent_name) VALUES ('testing');插入 0 1ranking_analytics=# 从locations_continent 中删除其中 continent_name = 'testing';删除 1

机器上有足够的驱动器空间:

$ df -H已使用的文件系统大小 Avail Use% Mounted on/dev/xvda 21G 2.3G 18G 12%/devtmpfs 255M 132k 255M 1%/dev无 257M 476k 256M 1%/dev/shm无 257M 54k 257M 1%/var/run无 257M 0 257M 0%/var/lock

有什么想法可能出问题了吗?

解决方案

如果重新启动 postgres 是一种选择，那么这很可能会解决问题，并且可以节省您阅读本答案其余部分的时间:-)

检查 pg_stat_activity 视图，可能有一些其他事务阻止了架构更改.

select * from pg_stat_activity在哪里wait_event_type 为 NULL 且 xact_start 不是 NULL 顺序由 xact_start ；

(pg_stat_activity 在每个主要的 pg 版本中都有一点改变，对于旧版本试试这个):

select * from pg_stat_activity在哪里不等待并且 xact_start 不是 xact_start 的 NULL 顺序；

显示的第一行可能是导致问题的行.它通常是交易中的空闲".- 这可能很好地持有锁，如果它是一个旧事务，它也可能会降低性能.可能程序员忘记确保以提交"结束事务.或回滚"，或者某些数据库会话由于网络问题而卡住.

使用 pid 1234 终止事务，使用 select pg_cancel_backend(1234);，如果失败，select pg_terminate_backend(1234).通过 shell 访问，等效的命令是 kill -INT 1234 和 kill 1234.(请记住，kill -9 1234 是一个非常糟糕的主意.

还有一个视图 pg_locks 可以提供一些见解，尽管从中获取任何有用的信息可能并不那么容易.如果 granted 为真，则锁定被持有，当 granted 为假时，表示查询正在等待锁定.这里有一些关于如何从 pg_locks 中提取有用信息的更多提示:http://wiki.postgresql.org/wiki/Lock_Monitoring

如果其他一切都失败了，那么可能是时候寻求简单的解决方案了，重新启动该数据库服务器.

I'm trying to create a new table in postgres but when I do it just hangs after the CREATE TABLE call.

$ sudo usermod -s /bin/bash postgres
$ sudo su - postgres
postgres@host:~$ psql ranking_analytics
psql (8.4.8)
Type "help" for help.

ranking_analytics=# BEGIN;
BEGIN
ranking_analytics=# CREATE TABLE "about_contactmessage" (
ranking_analytics(#     "id" serial NOT NULL PRIMARY KEY,
ranking_analytics(#     "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
ranking_analytics(#     "subject" text NOT NULL,
ranking_analytics(#     "message" text NOT NULL,
ranking_analytics(#     "recorded_time" timestamp with time zone NOT NULL
ranking_analytics(# )
ranking_analytics-# ;
NOTICE:  CREATE TABLE will create implicit sequence "about_contactmessage_id_seq" for serial column "about_contactmessage.id"

It will then just sit here indefinately until I CTRL-C it.

There are other tables in the database and this one doesn't already exist:

ranking_analytics=# \d about_contactmessage
Did not find any relation named "about_contactmessage".

I'm able to do insert and delete queries on other tables in the database without a problem:

ranking_analytics=# insert into locations_continent (continent_name) VALUES ('testing');
INSERT 0 1
ranking_analytics=# delete from locations_continent where continent_name = 'testing';
DELETE 1

There is plenty of drive space on the machine:

$ df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/xvda               21G   2.3G    18G  12% /
devtmpfs               255M   132k   255M   1% /dev
none                   257M   476k   256M   1% /dev/shm
none                   257M    54k   257M   1% /var/run
none                   257M      0   257M   0% /var/lock

Any ideas what could be wrong?

解决方案

If restarting postgres is an option, then that will most likely solve the issue and will save you from spending time reading the rest of this answer :-)

Check the pg_stat_activity view, there is probably some other transaction blocking the schema change.

select * from pg_stat_activity 
where 
wait_event_type is NULL and xact_start is not NULL order by xact_start;

(the pg_stat_activity is changed a bit in every major pg release, try this for elder versions):

select * from pg_stat_activity 
where 
not waiting and xact_start is not NULL order by xact_start;

The first row to show up is probably the one causing problems. It is often an "idle in transaction" - this may very well hold locks, and if it's an old transaction it may as well kill performance. Probably the programmer forgot to ensure ending the transaction with "commit" or "rollback", or maybe some db session got stuck due to network problems.

To terminate transaction with pid 1234, use select pg_cancel_backend(1234);, if that fails, select pg_terminate_backend(1234). With shell access, the equivalent commands are kill -INT 1234 and kill 1234. (keep in mind, kill -9 1234 is a really bad idea).

There is also a view pg_locks which may give some insight, though it may probably not be that easy to get any useful info out from it. If granted is true, the lock is held, when granted is false it means the query is waiting for the lock. Here are some more hints here on how to extract useful info from pg_locks: http://wiki.postgresql.org/wiki/Lock_Monitoring

If everything else fails, then it's probably time to go for the simple solution, restart that database server.

这篇关于创建新表时 Postgres 客户端锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！