10

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.

1

1 Answer 1

19
+50

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.

4
  • What if it's generated as part of delete query, and you are sure that there are no records referencing the records you're trying to delete?
    – Dragas
    Oct 16 at 13:51
  • @Dragas If what is generated as part of a DELETE statement? Oct 16 at 13:55
  • The select 1 from table x where ... for key share of x query is being run in the background whenever I am trying to delete records from the table that is being referenced by the fk constraint. Since FK constraint is on delete restrict my process is to clean up the referring table first only to later clean the referred table in the same transaction, but postgres still seems to wait for the mentioned locking query to resolve first.
    – Dragas
    Oct 16 at 14:23
  • 1
    @Dragas Well, PostgreSQL has to run the internal trigger query to make sure that there are no referencing rows, right? If you already deleted all referencing rows in the same transaction, that query cannot block. Oct 16 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.