What does KEY SHARE OF in a PostgreSQL statement mean?

Given the following SQL, what does "ONLY", "OPERATOR(pg_catalog.=)" and "FOR KEY SHARE" do?

SELECT 1 
FROM ONLY "public"."acmeinstanceinfo" x 
WHERE "widgetid" OPERATOR(pg_catalog.=) $1 
FOR KEY SHARE OF x

I have a fair amount of experience with Microsoft SQL server but zero with PostgreSQL. Any insights into what this query is doing would be great.

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 a:

BEGIN;

SELECT id FROM a FOR UPDATE;

 id 
════
  1
(1 row)

Then, in another session, try to insert a row into b:

INSERT INTO b VALUES (1);  -- hangs!

What happened?

-
The SELECT ... FOR UPDATE took a FOR UPDATE lock on the row in a.

-
The 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

SELECT 1
FROM ONLY "public"."a" x 
WHERE "id" OPERATOR(pg_catalog.=) 1 
FOR KEY SHARE OF x;

Here 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).

-
Since 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.

StackExchange Database Administrators Q#271167, answer score: 21