Выполняется только одно из двух UPDATE в CTE?
У меня возникла ситуация, когда мне приходится использовать CTE в этой форме.
WITH
update_field_a AS (
update super_table SET
a = ....
WHERE condition to update a hold true
RETURNING TRUE
),
update_field_b AS (
update super_table SET
b = ....
WHERE condition to update b hold true
RETURNING TRUE
)
SELECT
(SELECT true FROM update_field_a) as updated_a,
(SELECT true FROM update_field_b) as updated_b,
Таким образом, в основном два CTE обновляют разные поля одной и той же таблицы, каждый из которых имеет свое собственное условие обновления в предложении WHERE.
Проблема в том, что поведение, которое я наблюдаю, заключается в том, что только CTE запускается в зависимости от того, какой из SELECT я вызываю первым в последнем запросе SELECT.
Это учитывая это
SELECT
(SELECT true FROM update_field_a) as updated_a, <-- first select
(SELECT true FROM update_field_b) as updated_b,
update_field_a CTE будет запускаться и обновлять таблицу, в то время как update_field_b не запускается, поскольку (SELECT true FROM update_field_a) as updated_a вызывается первым.
Но если у меня есть:
SELECT
(SELECT true FROM update_field_b) as updated_b, <-- first select
(SELECT true FROM update_field_a) as updated_a,
update_field_b CTE будет запускаться и обновлять таблицу, в то время как update_field_a не запускается, поскольку (SELECT true FROM update_field_a) as updated_a вызывается первым.
Как я могу обеспечить работу обоих CTE? Я думал, что ссылка на них в инструкции SELECT вызовет их оба, но по какой-то странной причине запускается только первая ссылка, которая получает ссылку.
Обратите внимание, что этот запрос является частью приложения Rust, использующего библиотеку sqlx. Обновление: я вижу такое же поведение, если запрос выполняется непосредственно в терминале psql, поэтому детали Rust/Sqlx не имеют никакого влияния.
Я уверен, потому что проверяю таблицу в базе данных после выполнения запроса. И, к сожалению, в этом случае мне нужно сделать все за один запрос, поэтому его нельзя разделить на два. Обратите внимание, что приведенный выше пример представляет собой упрощенную версию реального запроса, поэтому существуют другие ограничения, которые заставляют это делать в одном запросе с CTE.
Из какого инструмента вы выполняете Postgres SQL? Вы делаете это из администратора pg, из кода или где-то еще?
Фактически из кода. Растет с помощью sqlx. Я обновлю вопрос с этими подробностями
Я создал упрощенную версию запроса и выполнил его непосредственно в psql и получил то же самое поведение.
Вы упомянули, что включите свой код Rust... но этого еще не произошло.
Почему бы не использовать два простых оператора UPDATE в одной транзакции? Или одно ОБНОВЛЕНИЕ с условием CASE о том, что обновлять? Это значительно упростит задачу, и ваша проблема исчезнет.
Подтверждено, что такое же поведение наблюдается, когда запрос выполняется непосредственно в psql, поэтому код Rust не имеет значения.
@FrankHeikens ОП вызывает Postgres из кода приложения Rust. Поэтому я попросил показать код, чтобы подсказать, как использовать одну транзакцию.
@FrankHeikens есть и другие ограничения, согласно которым это следует делать в одном запросе с использованием CTE. Кроме того, это не помогает объяснить странное поведение, которое я наблюдаю, потому что теоретически имеющиеся у меня CTE должны работать нормально, странно, что только один из них, который запрашивается первым, запускается, а другой игнорируется. Я хотел бы знать, почему это так.
> Поэтому я попросил показать код, чтобы я мог предложить, как использовать одну транзакцию @FrankHeikens, которая не имеет смысла :( и отвечает на совершенно другой вопрос. Вопрос не в том, как разделить этот CTE на транзакции, а в том, как объяснение того, почему у меня есть CTE, который теоретически должен работать, но демонстрирует поведение, которое тот, который работает, зависит от того, на который ссылаются первым в последнем операторе SELECT, - и как исправить CTE, чтобы не делать этого
Ответы 1
Все CTE и основной запрос видят один и тот же снимок базовых таблиц. Обновление одной и той же строки несколько раз не работает.
Как я могу обеспечить работу обоих CTE?
Вы не можете. Либо разделите запрос на два отдельных запроса, либо объедините оба обновления в одно. Нравиться:
UPDATE super_table
SET a = CASE WHEN condition_a THEN ... ELSE a END
, b = CASE WHEN condition_b THEN ... ELSE b END
WHERE (condition_a OR condition_b)
RETURNING condition_a AS updated_a
, condition_b AS updated_b;
Связанный:
> Все CTE и основной запрос видят один и тот же снимок базовых таблиц. Обновление одной и той же строки несколько раз не работает. - Но это работает для первого обновления. Итак, я все еще пытаюсь понять, как тот факт, что все они видят один и тот же снимок, гарантирует, что первый, на который ссылается последний выбор, фактически обновляет снимок.
@FinlayWeber: Это всего лишь деталь реализации, и она ненадежна. CTE, хотя и перечислены последовательно, на самом деле не имеют порядка. Они могут выполняться в любом произвольном порядке — если только вы не объедините их с помощью предложения RETURNING. Тогда первый элемент SELECT первым активирует CTE. Другой не может снова обновить ту же строку. Но именно так решает планировщик запросов. Во второй добавленной мной ссылке обсуждается аналогичный случай.
Как вы можете быть уверены, что действительно запустилось только одно из обновлений? Почему вы помещаете оператор обновления в CTE? Почему бы просто не запустить два обновления напрямую, возможно, в рамках одной транзакции?