This is the effect of inserting or updating a row in a table that has a foreign key. See the following example:
CREATE TABLE a (id integer PRIMARY KEY);
CREATE TABLE b (id integer REFERENCES a);
INSERT INTO a VALUES (1);
Now in one session, start a transaction and lock the row in
SELECT id FROM a FOR UPDATE;
Then, in another session, try to insert a row into
INSERT INTO b VALUES (1); -- hangs!
SELECT ... FOR UPDATE took a
FOR UPDATE lock on the row in
INSERT has to make sure that no concurrent transaction can delete the referenced row in
a (or modify any of its key columns) to avoid inconsistencies.
For that purpose, it runs
FROM ONLY "public"."a" x
WHERE "id" OPERATOR(pg_catalog.=) 1
FOR KEY SHARE OF x;
ONLY makes sure that no other tables are affected if
a is part of an inheritance hierarchy, and
OPERATOR(pg_catalog.=) is PostgreSQL syntax for a schema-qualified operator (since operators are also subject to
search_path, PostgreSQL has to make sure that the correct
= operator is used).
FOR UPDATE and
FOR KEY SHARE locks conflict (see the documentation), the second session is blocked.
You can avoid that block by using
SELECT ... FOR NO KEY UPDATE instead of
SELECT ... FOR UPDATE. The latter is only needed if you plan to update a primary or unique key column or if you intend to delete the row.