我们目前正面临一些数据库（postgres）问题，我们所掌握的信息无法带来新的见解。希望有人能帮我们。
一些背景
我们正在开发的工具基本上是一个通用的数据库系统。这样用户可以动态地添加表和列。
在向其中一个表中插入一个条目后，我们将返回该条目的“完整”版本。例如，如果条目包含一个用户列（表示为一个用户id数组），我们将查询这些用户的一些信息，并将其添加到条目中，然后再将其发送到客户端。添加信息是使用JavaScript而不是通过JOIN完成的。
问题
users表上的SELECT查询似乎阻止了对某些生成的表的插入。两个表之间没有外键关系（条目中的用户id存储为整数数组）。
而且，插入物似乎相互阻塞。但也许这是第一个问题造成的？
为了查看哪些查询正在阻止哪些其他查询，我们运行了以下语句：
SELECT
blockeda.pid AS blocked_pid,
blockeda.query as blocked_query,
blockinga.pid AS blocking_pid,
blockinga.query as blocking_query,
blockedl.mode as blocked_mode,
blockeda.query_start as blocked_query_start,
blockinga.query_start as blocking_query_start
FROM
pg_catalog.pg_locks blockedl
JOIN
pg_stat_activity blockeda
ON
blockedl.pid = blockeda.pid
JOIN
pg_catalog.pg_locks blockingl
ON
(blockingl.transactionid=blockedl.transactionid AND blockedl.pid != blockingl.pid)
JOIN
pg_stat_activity blockinga
ON
blockingl.pid = blockinga.pid
WHERE NOT
blockedl.granted
LIMIT
10;
结果如下所示（除了我稍微更改了表和列名之外）：
blocked_pid | blocked_query | blocking_pid | blocking_query | blocked_mode | blocked_query_start | blocking_query_start
-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
96170 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99736 | select "id", "display_name", "user_name", "initials", "image_link", "is_image_preferred" from "users" where "id" in ($1) and "deprecated_at" is null | ShareLock | 2017-12-13 15:41:07.707156+00 | 2017-12-13 15:40:27.217027+00
96341 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99033 | select "id", "display_name", "user_name", "initials", "image_link", "is_image_preferred" from "users" where "id" in ($1) and "deprecated_at" is null | ShareLock | 2017-12-13 15:39:04.585587+00 | 2017-12-13 15:13:19.079758+00
96341 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99427 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:39:04.585587+00 | 2017-12-13 15:13:27.534743+00
96341 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99439 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:39:04.585587+00 | 2017-12-13 15:13:22.371772+00
96667 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99127 | select "id", "display_name", "user_name", "initials", "image_link", "is_image_preferred" from "users" where "id" in ($1) and "deprecated_at" is null | ShareLock | 2017-12-13 15:39:00.840949+00 | 2017-12-13 15:36:17.303978+00
96667 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 97108 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:39:00.840949+00 | 2017-12-13 15:36:35.485022+00
96667 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 96822 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:39:00.840949+00 | 2017-12-13 15:38:57.932188+00
96677 | insert into "generated_table_2" ("generated_column_2_searchText", "generated_column_2_text", "generated_column_5_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 96338 | select "id", "display_name", "user_name", "initials", "image_link", "is_image_preferred" from "users" where "id" in ($1) and "deprecated_at" is null | ShareLock | 2017-12-13 15:12:22.040108+00 | 2017-12-13 15:11:36.813946+00
96822 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99127 | select "id", "display_name", "user_name", "initials", "image_link", "is_image_preferred" from "users" where "id" in ($1) and "deprecated_at" is null | ShareLock | 2017-12-13 15:38:57.932188+00 | 2017-12-13 15:36:17.303978+00
96822 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 96667 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:38:57.932188+00 | 2017-12-13 15:39:00.840949+00
96822 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 97108 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:38:57.932188+00 | 2017-12-13 15:36:35.485022+00
97110 | insert into "generated_table_3" ("generated_column_3_searchText", "generated_column_3_text", "generated_column_3_textType", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 96422 | select "id", "display_name", "user_name", "initials", "image_link", "is_image_preferred" from "users" where "id" in ($1) and "deprecated_at" is null | ShareLock | 2017-12-13 15:47:12.530821+00 | 2017-12-13 15:42:08.271596+00
97108 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99127 | select "id", "display_name", "user_name", "initials", "image_link", "is_image_preferred" from "users" where "id" in ($1) and "deprecated_at" is null | ShareLock | 2017-12-13 15:36:35.485022+00 | 2017-12-13 15:36:17.303978+00
97108 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 96667 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:36:35.485022+00 | 2017-12-13 15:39:00.840949+00
97108 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 96822 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:36:35.485022+00 | 2017-12-13 15:38:57.932188+00
99427 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99033 | select "id", "display_name", "user_name", "initials", "image_link", "is_image_preferred" from "users" where "id" in ($1) and "deprecated_at" is null | ShareLock | 2017-12-13 15:13:27.534743+00 | 2017-12-13 15:13:19.079758+00
99427 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99439 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:13:27.534743+00 | 2017-12-13 15:13:22.371772+00
99427 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 96341 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:13:27.534743+00 | 2017-12-13 15:39:04.585587+00
99439 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99033 | select "id", "display_name", "user_name", "initials", "image_link", "is_image_preferred" from "users" where "id" in ($1) and "deprecated_at" is null | ShareLock | 2017-12-13 15:13:22.371772+00 | 2017-12-13 15:13:19.079758+00
99439 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 99427 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:13:22.371772+00 | 2017-12-13 15:13:27.534743+00
99439 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | 96341 | insert into "generated_table_1" ("generated_column_1_searchText", "generated_column_1_text", "generated_column_4_categories", "created_at", "created_by", "display_string", "listId", "shortId", "sort_order", "updated_at", "updated_by", "uuid") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning * | ShareLock | 2017-12-13 15:13:22.371772+00 | 2017-12-13 15:39:04.585587+00
有人知道为什么这些查询应该相互阻塞吗？
如果你需要更多的信息来回答这个问题，请告诉我。
任何帮助都将不胜感激！
提前多谢了，
杰西
编辑
生成表的表定义（静态部分）：
id: integer
shortId: character varying(255)
uuid: uuid
display_string: text
sort_order: numeric
parent_id: integer
created_by: integer
updated_by: integer
deprecated_by: integer
created_at: timestamp with time zone
updated_at: timestamp with time zone
deprecated_at: timestamp with time zone
解决办法
通过手动终止任何运行时间超过30秒的事务，我们现在设法解决了这个问题。似乎只有少数交易没有解决，因此越来越多的新交易被阻塞和堆积。
之后，我们将启动这些事务的相关代码更改为使用promises而不是回调，从此不再发生错误。所以也许我们忘记在一个错误案例中调用回调。
我们会进一步调查，看看这是否真的解决了问题。谢谢你到目前为止的帮助！
最佳答案
您正在运行的查询返回由阻塞事务运行的最新语句，但这不一定是导致锁的语句（在SELECT的情况下，很可能不是）。
首先应该弄清楚哪些SQL语句是实际运行的。
您可以设置log_statements='all'并重现问题。
确保log_line_prefix包含%c，以便您可以识别会话。
然后找出每个相关事务运行的语句。
请记住，如果涉及外键或触发器，看起来毫不相关的表可能会被语句锁定。
关于postgresql - Postgres:SELECT阻止对不相关表的INSERT，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47798078/