Как написать Postgres SELECT FOR UPDATE при использовании оператора набора EXCEPT?
В Postgres (11, если это имеет значение) мне нужно сделать SELECT FOR UPDATE, чтобы получить набор строк, которые я впоследствии буду вносить в некоторые изменения, и которые я не хочу, чтобы кто-то за пределами моей транзакции возился, пока я делаю эти изменения.
Однако набор строк, которые я хочу заблокировать, на самом деле определяется разностью наборов, т. Е.
SELECT <columns> FROM table1 t1 JOIN table2 t2 ON ... WHERE ...
EXCEPT
SELECT <columns> FROM table1 t1 JOIN table3 t3 ON ... WHERE ...
Я хочу, чтобы результирующий набор этой разности наборов определял набор заблокированных строк; то есть те строки, которые выбраны вторым SELECT, в идеале не должны блокироваться.
Но я не совсем уверен, куда поместить пункт FOR UPDATE, чтобы добиться этого. Кажется, что если поставить FOR UPDATE сразу после любой из строк SELECT выше, это не даст мне того, что я хочу. И на самом деле я подозреваю, что не могу по закону поставить его после первой из этих строк SELECT (то есть непосредственно перед EXCEPT).
Одна идея, которая пришла мне в голову, заключалась в том, чтобы заключить в скобки второй SELECT (тот, который является предметом EXCEPT), чтобы FOR UPDATE не интерпретировался как часть этого второго SELECT:
SELECT <columns> FROM table1 t1 JOIN table2 t2 ON ... WHERE ...
EXCEPT
(SELECT <columns> FROM table1 t1 JOIN table3 t3 ON ... WHERE ...)
FOR UPDATE
Но я не уверен, что это дает мне то, что я хочу, даже если это окажется синтаксически приемлемым.
Вполне возможно, что если бы я имел представление о форме дерева синтаксического анализа для оператора select (Postgres), я мог бы легко понять это сам; но, как это, я немного потерял прямо сейчас.
Ответы 1
Вы не можете использовать FOR UPDATE вместе с UNION, INTERSECT или EXCEPT, потому что в общем случае это может вызвать двусмысленность.
Я могу думать о двух подходах:
Используйте
EXISTSиNOT EXISTS:SELECT ... FROM table1 WHERE EXISTS (SELECT 1 FROM table2 ... WHERE table2.x = table1.x AND ...) AND NOT EXISTS (SELECT 1 FROM table3 ... WHERE table3.y = table1.y AND ...) FOR UPDATE OF table1;Используйте подзапрос:
SELECT ... FROM table1 WHERE id IN (SELECT t1.id FROM table1 t1 JOIN table2 t2 ON ... WHERE ... EXCEPT SELECT t1.id FROM table1 t1 JOIN table3 t3 ON ... WHERE ...) FOR UPDATE OF table1;
Ах, действительно, теперь я вижу это, в конце раздела о пункте КРОМЕ: «В настоящее время FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя указать ни для результата EXCEPT, ни для любой ввод ИСКЛЮЧЕНИЯ." То же самое и с другими операторами, работающими с множествами. Спасибо, что обратили на это мое внимание.