Postgres: SELECT blocks INSERT for an unrelated table

Question

We are facing some database (postgres) issues at the moment and got to a point where the information we have doesn't lead to new insights. Hopefully, somebody can help us out here.

Some context

The tool we are working on is basically a generic database system. So the user can add tables and columns dynamically. After inserting an entry into one of the tables, we return a "full" version of the entry. For example, if the entry contains a user column (represented as an array of user ids), we query some information about theses users and add it to the entry before sending it to the client. Adding the information is done using JavaScript and not via a JOIN.

The problem

The SELECT query on the users table seems to be blocking INSERTs on some generated tables. There is no foreign key relationship between the two tables (user ids inside an entry are stored as an integer array). Also, the INSERTs seem to be blocking each other. But maybe this is caused by the first problem?

In order to see which queries are blocking which other queries, we ran the following statement:

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;

The result looks like this (except that I altered the table and column names a bit):

 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

Does anyone know why these queries should be blocking each other? If you need any more info to answer the question, please let me know.

Any help would be highly appreciated!

Thanks a lot in advance,

Jesse

Edit

Table defintion of the generated tables (static parts):

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

Workaround

By manually killing any transaction that runs longer than 30s we managed to work around this problem for now. It seems like there were only a few transactions that did never resolve and therefore more and more new transactions were blocked and piled up.

Afterwards, we changed the related code that starts these transactions to use promises instead of callbacks and since then the error did not occur anymore. So maybe we forgot to call a callback in one of the error cases. We'll investigate further and see if this actually fixed the issue. Thanks for your help so far!

Postgres: SELECT blocks INSERT for an unrelated table

Answers (1)

Related Questions