We have a web application that sends emails with a queue worker. Before an email is sent we write to a table:
INSERT INTO user_email (user_id, email_key, sent_at) VALUES (
175413579283955991,
'reminder-2017-07-06',
'2017-07-06T14:35:56.329185'::timestamp
) RETURNING user_email.id
The table definition looks like this:
CREATE TABLE user_email (
id bigserial primary key,
user_id bigint references "user"(id),
key varchar(64),
sent_at timestamp without time zone,
UNIQUE (user_id, key)
);
We've started seeing writes to this table take a very long time, it appears that SELECT
statements to the user
table are blocking it:
locked_item | transactionid
waiting_duration | 00:00:22.516621
blocked_pid | 7409
blocked_query | INSERT INTO user_email (user_id, key, sent_at) VALUES (175413579283955991, 'reminder-2017-07-06', '2017-07-06T14:35:56.329185'::timestamp) RETURNING user_email.id
blocked_mode | ShareLock
blocking_pid | 4345
blocking_query | SELECT "user".id AS user_id, "user".first_name AS user_first_name, "user".last_name AS user_last_name, "user".title AS user_title, ... FROM "user" WHERE "user".id = 683220705055081414
blocking_mode | ExclusiveLock
We're at a loss on why a SELECT
statement is causing an ExclusiveLock
, any insights on what we should look into?
TABLE pg_stat_activity
, all of it, when the block occurs.