Предложение RETURNING в SQLite: как работает и когда использовать

Что такое RETURNING в SQLite

RETURNING — необязательное предложение, которое размещается ближе к концу операторов DELETE, INSERT и UPDATE, возвращая одну строку результата для каждой изменённой базы данных.

RETURNING не является стандартным SQL — это расширение. Синтаксис RETURNING в SQLite основан на синтаксисе PostgreSQL.

Поддержка RETURNING появилась в SQLite начиная с версии 3.35.0

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Типичное использование: возврат значений после INSERT

Предложение RETURNING предназначено для того, чтобы предоставить приложению значения столбцов, которые заполняются автоматически средствами SQLite. Например:

CREATE TABLE t0( a INTEGER PRIMARY KEY, b DATE DEFAULT CURRENT_TIMESTAMP, c INTEGER
);
INSERT INTO t0(c) VALUES(random()) RETURNING *;

В данном операторе INSERT SQLite вычисляет значения для всех столбцов. RETURNING передаёт эти значения обратно приложению, избавляя его от необходимости выполнять отдельный запрос для определения вставленных значений

Как работает предложение RETURNING: подробности

После RETURNING следует список выражений, разделённых запятыми. Эти выражения аналогичны тем, что идут после SELECT в операторе SELECT и определяют значения столбцов в результирующем наборе.

Для операторов INSERT и UPDATE ссылки на столбцы изменяемой таблицы указывают на значение этого столбца после применения изменения. Для операторов DELETE ссылки на столбцы означают значение до выполнения удаления.

RETURNING возвращает изменённые строки, затронутые операцией DELETE, INSERT или UPDATE.

Предложение RETURNING для UPSERT сообщает как о вставленных, так и об обновлённых строках.

Порядок обработки: sqlite3_step и накопление вывода

Когда выполняется оператор DELETE, INSERT или UPDATE с предложением RETURNING, все изменения базы данных происходят в ходе первого вызова sqlite3_step(). Вывод предложения RETURNING накапливается в памяти. Первый вызов sqlite3_step() возвращает одну строку вывода RETURNING, а последующие строки вывода RETURNING возвращаются последующими вызовами sqlite3_step().

Иными словами, весь вывод предложения RETURNING сохраняется в памяти до завершения всех изменений в базе данных.

Это означает, что если оператор содержит предложение RETURNING, генерирующее большой объём вывода — либо много строк, либо большие строковые значения или значения BLOB, — то оператор может использовать значительный объём временной памяти для хранения этих значений в процессе выполнения

SQLite гарантирует, что все изменения происходят до вывода RETURNING, однако порядок строк в выводе не соответствует порядку их изменений в базе.

Самореференциальные подзапросы в RETURNING: неопределённое поведение

Хотя все изменения завершаются до вывода RETURNING, порядок их выполнения и получение значений могут варьироваться.

Если в столбце вывода RETURNING есть подзапрос, ссылающийся на изменяемую таблицу, результат может изменяться в зависимости от непредсказуемого поведения.

Рекомендуется избегать самореференциальных подзапросов в RETURNING, так как они могут привести к непредсказуемым и различным результатам.

2.3. Изменения ACID

Когда в предыдущем разделе «Порядок обработки» говорится, что «изменения базы данных происходят в ходе первого вызова sqlite3_step()», это означает, что изменения сохраняются в приватном кэше страниц соединения с базой данных, выполняющего оператор. Это не означает, что изменения фактически фиксируются.

Фиксация не происходит до завершения оператора, а возможно, и не тогда, если оператор является частью более крупной транзакции. Изменения базы данных по-прежнему являются атомарными, согласованными, изолированными и долговечными (ACID — Atomicity, Consistency, Isolation, Durability).

Когда в предыдущем разделе говорится «изменения происходят», это означает, что внутренние структуры данных корректируются в ожидании фиксации транзакции. Часть этих изменений может быть или не быть перенесена в журнал упреждающей записи (WAL), в зависимости от степени нагрузки на кэш страниц.

Если внутренний кэш страниц соединения не испытывает нехватки памяти, то, вероятно, ничего не будет записано на диск до завершения транзакции, то есть до того, как sqlite3_step() вернёт SQLITE_DONE

Иными словами, когда говорится «изменения базы данных происходят», это означает, что изменения происходят в памяти конкретного соединения с базой данных, выполняющего оператор, а не то, что изменения записываются на диск.

Ограничения предложения RETURNING в SQLite

Предложение RETURNING недоступно в операторах DELETE и UPDATE, применяемых к виртуальным таблицам. Это ограничение может быть снято в будущих версиях SQLite.

Предложение RETURNING доступно только в операторах DELETE, INSERT и UPDATE верхнего уровня. Предложение RETURNING не может использоваться операторами внутри триггеров.

Несмотря на то что оператор DML (язык манипулирования данными, Data Manipulation Language) с предложением RETURNING возвращает содержимое таблицы, он не может использоваться как подзапрос. Предложение RETURNING может только возвращать данные приложению. В настоящее время невозможно перенаправить вывод RETURNING в другую таблицу или запрос.

PostgreSQL позволяет использовать оператор DML с предложением RETURNING как представление в обобщённых табличных выражениях (CTE). SQLite в настоящее время не обладает такой возможностью, хотя это то, что может быть добавлено в будущем выпуске.

Строки, выдаваемые предложением RETURNING, появляются в произвольном порядке. Этот порядок может меняться в зависимости от схемы базы данных, конкретного выпуска используемой SQLite или даже от одного выполнения одного и того же оператора к другому. Нет способа заставить выходные строки появляться в определённом порядке.

Даже если SQLite скомпилирован с параметром SQLITE_ENABLE_UPDATE_DELETE_LIMIT, так что предложения ORDER BY разрешены в операторах DELETE и UPDATE, эти предложения ORDER BY не ограничивают порядок вывода RETURNING.

Значения, выдаваемые предложением RETURNING, являются значениями, видимыми оператору DELETE, INSERT или UPDATE верхнего уровня, и не отражают никаких последующих изменений значений, внесённых триггерами. Таким образом, если база данных содержит триггеры AFTER, изменяющие некоторые значения каждой вставленной или обновлённой строки, предложение RETURNING выдаёт исходные значения, вычисленные до запуска этих триггеров.

Предложение RETURNING не может содержать агрегатные функции или оконные функции верхнего уровня. Если в предложении RETURNING есть подзапросы, эти подзапросы могут содержать агрегаты и оконные функции, однако агрегаты не могут встречаться на верхнем уровне.

Предложение RETURNING может ссылаться только на изменяемую таблицу. В операторе UPDATE FROM вспомогательные таблицы, указанные в предложении FROM, не могут участвовать в предложении RETURNING.

На практике я советую воспринимать RETURNING как удобный инструмент для получения автоматически вычисленных значений — первичных ключей, временных меток, случайных значений — сразу после операции изменения, не прибегая к дополнительному SELECT. Мой опыт показывает, что это особенно ценно при работе с таблицами, где первичный ключ генерируется через INTEGER PRIMARY KEY.

На наш взгляд, отсутствие поддержки RETURNING в триггерах — наиболее ощутимое из текущих ограничений.

Синтаксис RETURNING: полное описание

Синтаксис предложения RETURNING следует той же логике, что и список результатов в операторе SELECT. После ключевого слова RETURNING указывается разделённый запятыми список выражений, каждое из которых может иметь псевдоним через AS. Специальный символ * разворачивается во все нескрытые столбцы изменяемой таблицы.

Выражения в предложении RETURNING могут включать:

• Ссылки на столбцы изменяемой таблицы
• Литеральные значения и параметры привязки (bind parameters)
• Унарные и бинарные операторы
• Вызовы функций с необязательными предложениями FILTER и OVER
• Выражения CAST и CASE
• Подзапросы — при условии, что они не ссылаются на изменяемую таблицу непредсказуемым образом

Агрегатные и оконные функции на верхнем уровне предложения RETURNING не допускаются.

Ответы на эти вопросы могут быть для вас полезными

С какой версии SQLite доступно предложение RETURNING?

Предложение RETURNING поддерживается начиная с версии SQLite 3.35.0, выпущенной 2021-03-12.

Отражает ли RETURNING изменения, внесённые триггерами AFTER?

Нет. Предложение RETURNING выдаёт значения, видимые оператору верхнего уровня до запуска триггеров AFTER. Изменения, внесённые триггерами, в выводе RETURNING не отражаются.

Можно ли использовать оператор с RETURNING как подзапрос?

Нет. В SQLite оператор DML с предложением RETURNING не может использоваться как подзапрос или как представление в обобщённых табличных выражениях. PostgreSQL поддерживает такую возможность, SQLite — пока нет.

Гарантирован ли порядок строк в выводе RETURNING?

Нет. Порядок строк в выводе RETURNING произволен и может меняться в зависимости от схемы базы данных, версии SQLite или даже между отдельными выполнениями одного и того же оператора. Предложения ORDER BY в операторах DELETE и UPDATE не влияют на порядок вывода RETURNING.

Что происходит с транзакцией, если оператор с RETURNING не завершён?

Изменения, выполненные в ходе первого вызова sqlite3_step(), сохраняются в приватном кэше страниц соединения, но не фиксируются на диске до завершения транзакции. Свойства ACID при этом полностью сохраняются — данные остаются атомарными, согласованными, изолированными и долговечными.