Невидимый syntax error в PostgreSQL: как одна строка SQL съела полтора дня дебага

[Akina]

Сюрприз. PostgreSQL такое не понимает. Хотя по идее всё разумно.

Да нет тут ничего разумного. И вы дальше даже сами объясняете, по какой именно причине.

При наличии ORDER BY / LIMIT в UNION правило архипростое. Есть скобки - применяется к подзапросу, нет скобок - ко всему запросу, если в тексте последнего подзапроса, иначе ошибка синтаксиса.

Подвох: если первая часть нашла запись, то после UNION ALL получится 2 строки (найденная + NULL-fallback), а внешний LIMIT 1 обрежет до первой. Но порядок строк в UNION ALL без явной сортировки не гарантирован. То есть теоретически вы можете получить NULL-строку даже когда лид существует.

Читаем правило выше и составляем правильный запрос:

(
    SELECT summary, escalation_reason, created_at FROM leads
    WHERE session_id = '...'
    ORDER BY created_at DESC LIMIT 1
)
UNION ALL
(
    SELECT NULL::text, NULL::text, NULL::timestamptz
)
ORDER BY created_at NULLS LAST -- вот он, фикс
LIMIT 1

Если первый подзапрос вернул запись, то ORDER BY, применяемый к объединённому набору записей, сначала вернёт запись с имеющимся значением поля, и только после неё запись со значением NULL. NULLS LAST в данном случае формально необязателен (но обязателен, если присутствует DESC - такова особенность сортировки у Postgres), но и беды от него никакой, а, поскольку сортируется не более 2 записей, то на производительность это не влияет.

Обрамление ВСЕХ подзапросов скобками делает запрос ну совсем корректным. Хотя (опять же формально) обрамление скобками у последнего подзапроса можно и опустить. Но я советую делать наоборот, и каждый подзапрос обрамлять скобками, даже если у вас ни ORDER BY, ни LIMIT не присутствуют.

[viktdo]

Поправка по делу, спасибо. Действительно, UNION ALL без внешнего ORDER BY порядок не гарантирует, и LIMIT 1 в моём варианте 1 теоретически может срезать найденную запись. Это баг, в продакшене стрельнул бы рано или поздно.

Правильный вариант, как вы и привели: ORDER BY created_at NULLS LAST LIMIT 1 поверх объединённого набора. Тогда найденная запись всегда выигрывает у NULL-fallback'а.

У меня в работающем флоу в итоге стоит RIGHT JOIN (вариант 2 в статье), там проблема снимается конструктивно: если внутренний SELECT пустой, RIGHT JOIN сам даёт NULL'ы, если вернул строку, она и есть результат. Но для «правильного UNION ALL» ваш вариант каноничный.

Про обрамление скобками каждого подзапроса даже без ORDER BY/LIMIT согласен. Парсер прощает, но через полгода ты сам не помнишь, какое правило приоритета у UNION с LIMIT, и скобки сильно облегчают чтение. Особенно когда запрос дорастает до WITH или вложенных UNION.

[Akina]

Это баг

Да не баг это, а совершенно нормальное, полностью законное и даже документированное поведение. Просто у вас оно выпало из памяти - не иначе, редко надобится. Или вы вообще с SQL "на вы", и не видите недетерминированности написанной вами конструкции. Я бы, наоборот, просто на автомате, даже не задумываясь, написал именно показанный мной запрос, для меня недетерминированность вашего запроса просто бросается в глаза.

[viktdo]

Соглашусь, тут вы попали в точку. Я не профильный SQL-специалист, мой основной стек это n8n / интеграции / API-обвязка, а Postgres использую в роли хранилища данных для воркфлоу, иногда для обработки данных на стороне Posgres. То есть пишу SELECT’ы и JOIN’ы регулярно, а в углы спецификации UNION захожу раз в полгода. Эта недетерминированность для меня действительно не была очевидной из чтения запроса, я думал про неё на уровне «обычно работает» и не пошёл дальше.

Ваш разбор как раз ценен тем, что показывает другой уровень владения инструментом: у человека с настоящим SQL-бэкграундом MAX-aggregate-обёртка уже в моторике, а не выводится из соображений. У меня так с n8n или JavaScript внутри Code-нод, где косяки чужих воркфлоу видны с первого взгляда. С Postgres мне до этого далеко, и комменты вроде ваших как раз тот ускоренный апдейт моторики, ради которого и пишутся такие статьи.

[viktdo]

Могу использовать ваш комент, если соберусь написать статью по этой теме. Если будет применимо? С указанием на вас, конечно же

[ptr128]

Если уже заниматься перфекционизмом, то так. Внимание на последний запрос.

Скрытый текст

DROP TABLE IF EXISTS tmp_test;
CREATE TABLE tmp_test (
  id serial PRIMARY KEY,
  session_id int,
  summary varchar,
  escalation_reason varchar,
  created_at timestamp
);

INSERT INTO tmp_test (session_id, summary,
  escalation_reason, created_at) 
SELECT G.n/1000 AS session_id, G.n::text AS summary,
  'some reason' AS escalation_reason,
  transaction_timestamp()+'1 ms'::interval*G.n
FROM generate_series(1,100000,1) G(n);

CREATE INDEX tmp_test_session_id_created_at_idx
  ON tmp_test (session_id, created_at); 

Сравниваем

EXPLAIN ANALYZE
SELECT summary, escalation_reason, created_at FROM (
  SELECT summary, escalation_reason, created_at
  FROM tmp_test
  WHERE session_id = 500
  ORDER BY created_at DESC LIMIT 1
) t
RIGHT JOIN (SELECT 1 AS d) s ON true;

Nested Loop Left Join  (cost=0.42..2.65 rows=1 width=25) (actual time=0.019..0.020 rows=1.00 loops=1)
  Buffers: shared hit=3
  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1.00 loops=1)
  ->  Limit  (cost=0.42..2.64 rows=1 width=25) (actual time=0.017..0.017 rows=0.00 loops=1)
        Buffers: shared hit=3
        ->  Index Scan Backward using tmp_test_session_id_created_at_idx on tmp_test  (cost=0.42..2.64 rows=1 width=25) (actual time=0.016..0.016 rows=0.00 loops=1)
              Index Cond: (session_id = 500)
              Index Searches: 1
              Buffers: shared hit=3
Planning Time: 0.121 ms
Execution Time: 0.037 ms

EXPLAIN ANALYZE
(
  SELECT summary, escalation_reason, created_at
  FROM tmp_test
  WHERE session_id = 500
  ORDER BY created_at DESC LIMIT 1
)
UNION ALL
(
  SELECT NULL::text, NULL::text, NULL::timestamptz
)
ORDER BY created_at NULLS LAST -- вот он, фикс
LIMIT 1;

Limit  (cost=2.69..2.69 rows=1 width=72) (actual time=0.023..0.024 rows=1.00 loops=1)
  Buffers: shared hit=3
  ->  Sort  (cost=2.69..2.69 rows=2 width=72) (actual time=0.023..0.023 rows=1.00 loops=1)
        Sort Key: (("*SELECT* 1".created_at)::timestamp with time zone)
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=3
        ->  Append  (cost=0.42..2.68 rows=2 width=72) (actual time=0.016..0.017 rows=1.00 loops=1)
              Buffers: shared hit=3
              ->  Subquery Scan on "*SELECT* 1"  (cost=0.42..2.65 rows=1 width=25) (actual time=0.015..0.015 rows=0.00 loops=1)
                    Buffers: shared hit=3
                    ->  Limit  (cost=0.42..2.64 rows=1 width=25) (actual time=0.014..0.015 rows=0.00 loops=1)
                          Buffers: shared hit=3
                          ->  Index Scan Backward using tmp_test_session_id_created_at_idx on tmp_test  (cost=0.42..2.64 rows=1 width=25) (actual time=0.014..0.014 rows=0.00 loops=1)
                                Index Cond: (session_id = 500)
                                Index Searches: 1
                                Buffers: shared hit=3
              ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=72) (actual time=0.001..0.001 rows=1.00 loops=1)
                    ->  Result  (cost=0.00..0.01 rows=1 width=72) (actual time=0.001..0.001 rows=1.00 loops=1)
Planning Time: 0.109 ms
Execution Time: 0.048 ms

EXPLAIN ANALYZE
SELECT MAX(T.summary) AS summary,
  MAX(T.escalation_reason) AS escalation_reason,
  MAX(T.created_at) AS created_at
FROM (
  SELECT summary, escalation_reason, created_at
  FROM tmp_test
  WHERE session_id = 500
  ORDER BY created_at
  DESC LIMIT 1
) T;

Aggregate  (cost=2.64..2.65 rows=1 width=72) (actual time=0.019..0.020 rows=1.00 loops=1)
  Buffers: shared hit=3
  ->  Limit  (cost=0.42..2.64 rows=1 width=25) (actual time=0.017..0.017 rows=0.00 loops=1)
        Buffers: shared hit=3
        ->  Index Scan Backward using tmp_test_session_id_created_at_idx on tmp_test  (cost=0.42..2.64 rows=1 width=25) (actual time=0.016..0.016 rows=0.00 loops=1)
              Index Cond: (session_id = 500)
              Index Searches: 1
              Buffers: shared hit=3
Planning Time: 0.103 ms
Execution Time: 0.044 ms

[viktdo]

Спасибо за бенчмарк, MAX-aggregate подход я не пробовал и он действительно элегантнее моего RIGHT JOIN-варианта. План у него получается короче на одну ноду (Aggregate + Limit + Index Scan против Nested Loop Left Join + Result + Limit + Index Scan), и это гарантированно одна строка по семантике агрегата, без танцев с константной правой таблицей.

Один момент про safety этого паттерна: трюк работает только потому, что внутренний LIMIT 1 гарантирует не более одной строки на входе у MAX(). Если кто-то скопирует приём и уберёт LIMIT (или не заметит что подзапрос может вернуть >1 строки), то MAX(summary) и MAX(created_at) перестанут соответствовать друг другу. Это будут максимумы независимых колонок, и фронт получит «франкенштейн-строку» из несвязанных полей разных лидов. Я бы в продакшене вокруг такого запроса оставил коммент прямо в SQL: -- Aggregate trick: requires inner LIMIT 1, otherwise MAX() per-column desyncs rows.

С этой оговоркой соглашусь, что вариант чище RIGHT JOIN'а. Обновлённая иерархия для случая «вернуть ровно одну строку или fallback с NULL»: Aggregate-обёртка > UNION ALL с NULLS LAST > RIGHT JOIN с константной таблицей. На практике разница в 0.01 ms незначима, но план короче, значит читать и поддерживать проще, и Postgres в более сложных запросах будет оптимизировать предсказуемее.

[ptr128]

трюк работает только потому, что внутренний LIMIT 1 гарантирует не более одной строки на входе у MAX()

Ну так задача изначально звучала так. Была бы другая задача - был бы другой запрос.

[viktdo]

Могу использовать ваш комент, если соберусь написать статью по этой теме. Если будет применимо? С указанием на вас, конечно же

[viktdo]

Могу использовать ваш комент, если соберусь написать статью по этой теме. Если будет применимо? С указанием на вас, конечно же

[Zotann]

В чем посыл статьи? Так сразу понятно что последний лимит применяется неизвестно куда - то ли ко всему запросу, то ли ко второму (так же и с условиями и прочим). Поставить скобочки для ясности и все.

[viktdo]

Если коротко: посыл не про сам SQL, а про то, сколько слоёв проглатывают syntax error прежде чем он доходит до разработчика. Сам фикс действительно тривиальный, в статье я и пишу, что баг искал не в нём, а в системе вокруг.

Цепочка получилась такая: PostgreSQL-нода n8n с настройкой «Continue (using error output)» молча отдала пустой массив вместо исключения, downstream-нода с пустым массивом не упала а пробросила пустой объект, Respond to Webhook вернул HTTP 200 и пустое тело, фронт показал «нет данных», логи n8n executions показали статус success. Пять уровней, и ни на одном ошибка не материализовалась. Про SQL-pitfall я в итоге узнал последним, когда руками скопировал запрос в psql.

Полезный для меня вывод оттуда не «не забывай про скобки», а «не используй Continue using error output без подключённого error-выхода». В проектах где много динамического SQL эта настройка превращает любую опечатку в тихий 200 OK.

Кому скобки очевидны сразу, статья в первую очередь не для них. Для тех у кого в стеке n8n / Zapier / Make или любой low-code в продакшене, история про silent failures поверх SQL-ошибки полезнее самого SQL.