Завершаем обзор мартовского коммитфеста 19-й версии. В четвертой и последней статье серии рассмотрим изменения, относящиеся к языку SQL и материалам курсов DEV1 и DEV2.
Напоминаю план статей о последнем коммитфесте 19-й версии:
Часть 5.4 (SQL, DEV1, DEV2) – мы здесь ?
Об изменениях в предыдущих коммитфестах рассказано здесь: 2025-07, 2025-09, 2025-11, 2026-01.
В этом обзоре:
postgres_fdw: удаленные транзакции наследуют режимы READ ONLY и DEFERRABLE
Ограничения целостности: включение/отключение ENFORCED для ограничений CHECK
Ограничения целостности: изменение выражения виртуальных столбцов с ограничением CHECK
Преобразование между uuid и bytea и поддержка формата base32hex
INSERT … ON CONFLICT DO SELECT
commit: 88327092ff0
Команда INSERT … ON CONFLICT DO NOTHING RETURNING корректно обрабатывает конфликт, но не возвращает существующую строку. В следующем примере после добавления новой строки команда ее возвращает:
CREATE TABLE test ( id int PRIMARY KEY, value numeric(4,2) DEFAULT random() );
INSERT INTO test VALUES (1) ON CONFLICT (id) DO NOTHING RETURNING id, value;
id | value ----+------- 1 | 0.74 (1 row)
Но если строка уже есть, то фраза RETURNING ничего не вернет:
INSERT INTO test VALUES (1) ON CONFLICT (id) DO NOTHING RETURNING id, value;
id | value ----+------- (0 rows)
Если приложению требуется, чтобы команда INSERT всегда возвращала строку с указанным идентификатором, то приходилось проверять статус завершения команды и в случае конфликта делать отдельный SELECT. Это лишний запрос, а между двумя командами строку может удалить или изменить другая транзакция.
Новый вариант ON CONFLICT DO SELECT гарантированно возвращает через RETURNING либо новую, либо уже существующую конфликтующую строку:
INSERT INTO test VALUES (1) ON CONFLICT (id) DO SELECT RETURNING id, value;
id | value ----+------- 1 | 0.74 (1 row)
Столбцы для выборки по-прежнему перечисляются во фразе RETURNING, а после SELECT можно указать режим блокировки строки (FOR UPDATE, FOR SHARE и др.) и условие WHERE.
COPY FROM: замена ошибочных значений на NULL
commit: 2a525cc97e1
В 17-й версии COPY научилась игнорировать строки с ошибками преобразования формата данных. Для этого в команду нужно добавить параметр ON_ERROR ignore, и ошибочные строки исключаются из загрузки. Теперь ошибочные значения можно заменять на NULL и загружать строку. Ограничения NOT NULL (в том числе у доменов) при этом проверяются: если в такой столбец попадет NULL, команда завершится ошибкой.
CREATE TABLE test (col int);
COPY test FROM STDIN WITH (on_error set_null);
Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> 1 >> два >> 3 >> \. NOTICE: in 1 row, columns were set to null due to data type incompatibility COPY 3
SELECT * FROM test;
col ----- 1 3 (3 rows)
COPY TO: выгрузка данных в формате json
commit: 7dadd38cda9, 4c0390ac53b
Команда COPY теперь может выгружать данные не только в форматах text, csv и binary, но и в формате json:
COPY (SELECT * FROM bookings LIMIT 3) TO STDOUT WITH (format json);
{"book_ref":"2EW1SQ","book_date":"2025-09-01T03:00:12.557744+03:00","total_amount":8125.00} {"book_ref":"3ZY3O5","book_date":"2025-09-03T03:25:35.278453+03:00","total_amount":7500.00} {"book_ref":"756UAS","book_date":"2025-09-10T19:24:02.978559+03:00","total_amount":6325.00}
А дополнительный параметр FORCE_ARRAY оборачивает вывод в массив:
COPY (SELECT * FROM bookings LIMIT 3) TO STDOUT WITH (format json, force_array);
[ {"book_ref":"2EW1SQ","book_date":"2025-09-01T03:00:12.557744+03:00","total_amount":8125.00} ,{"book_ref":"3ZY3O5","book_date":"2025-09-03T03:25:35.278453+03:00","total_amount":7500.00} ,{"book_ref":"756UAS","book_date":"2025-09-10T19:24:02.978559+03:00","total_amount":6325.00} ]
Речь только о выгрузке (COPY TO), а не о загрузке (COPY FROM). Кроме того, с форматом json не поддерживаются некоторые параметры команды, а именно HEADER, DEFAULT, NULL, DELIMITER, FORCE QUOTE, FORCE NOT NULL и FORCE NULL.
jsonpath: новые методы работы со строками
commit: bd4f879a9cd
В язык jsonpath добавили новые методы для работы со строками: lower(), upper(), initcap(), replace(), split_part(), а также btrim(), ltrim() и rtrim():
SELECT jsonb_path_query('" hello, world! "', '$.btrim()'), jsonb_path_query('"hello, world!"', '$.upper()'), jsonb_path_query('"HELLO, WORLD!"', '$.lower()'), jsonb_path_query('"hello, world!"', '$.initcap()'), jsonb_path_query('"hello, world?"', '$.replace("?","!")'), jsonb_path_query('"hello, world!"', '$.split_part(", ",1)') \gx
-[ RECORD 1 ]----+---------------- jsonb_path_query | "hello, world!" jsonb_path_query | "HELLO, WORLD!" jsonb_path_query | "hello, world!" jsonb_path_query | "Hello, World!" jsonb_path_query | "hello, world!" jsonb_path_query | "hello"
Предикат IS JSON с доменными типами
commit: 3b4c2b9db25
Предикат IS JSON раньше не принимал домены, базовым типом которых являются json, jsonb, bytea или text — сервер не определял базовый тип домена.
CREATE DOMAIN js AS jsonb;
18=# SELECT '"Hello, World!"'::js IS JSON;
ERROR: cannot use type js in IS JSON predicate
Проверка исправлена:
19=# SELECT '"Hello, World!"'::js IS JSON;
?column? ---------- t (1 row)
postgres_fdw: удаленные транзакции наследуют режимы READ ONLY и DEFERRABLE
commit: de28140ded8
Удаленная транзакция, открываемая postgres_fdw на внешнем сервере, раньше не наследовала режимы READ ONLY и DEFERRABLE локальной транзакции. Из-за этого локальная транзакция, объявленная как READ ONLY, могла изменить данные на внешнем сервере, например, через вызов функций. А транзакция, объявленная как DEFERRABLE, могла прерваться из-за ошибки сериализации на внешнем сервере.
Теперь эти параметры локальной транзакции передаются на внешний сервер для открытия удаленной транзакции. Это несовместимое изменение с предыдущими версиями: если приложение в транзакциях READ ONLY изменяло данные через postgres_fdw, такие транзакции придется объявить как READ WRITE.
Ограничения целостности: включение/отключение ENFORCED для ограничений CHECK
commit: 342051d73b3
В 18-й версии стало возможным создавать ограничения целостности CHECK как NOT ENFORCED:
CREATE TABLE test (id int); ALTER TABLE test ADD CONSTRAINT check_id CHECK (id > 0) NOT ENFORCED;
Но изменить ограничение на ENFORCED было нельзя.
18=# ALTER TABLE test ALTER CONSTRAINT check_id ENFORCED;
ERROR: cannot alter enforceability of constraint "check_id" of relation "test"
Требовалось его удалить и создать заново. В 19-й версии это стало возможным. При переводе в ENFORCED таблица сканируется, чтобы проверить существующие строки:
19=# ALTER TABLE test ALTER CONSTRAINT check_id ENFORCED;
ALTER TABLE
Ограничения целостности: изменение выражения виртуальных столбцов с ограничением CHECK
commit: f80bedd52b1
В 18-й версии появилась поддержка виртуальных вычисляемых столбцов. Например:
CREATE TABLE test ( id int, amount numeric, tax numeric GENERATED ALWAYS AS (amount*0.2) VIRTUAL, CONSTRAINT check_tax CHECK (tax > 0) );
Однако изменить выражение для вычисляемого столбца было нельзя, если для этого столбца определено ограничение CHECK:
18=# ALTER TABLE test ALTER COLUMN tax SET EXPRESSION AS (amount*0.22);
ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints DETAIL: Column "tax" of relation "test" is a virtual generated column.
В 19-й версии это ограничение снято. Таблица при этом не перезаписывается, а только сканируется для проверки ограничений CHECK:
19=# ALTER TABLE test ALTER COLUMN tax SET EXPRESSION AS (amount*0.22);
ALTER TABLE
Преобразование между uuid и bytea и поддержка формата base32hex
commit: 497c1170cb1, ba21f5bf8af
Преобразование между uuid и bytea раньше требовало обходных путей через текстовое представление, а функции encode/decode не поддерживали формат base32hex. Теперь доступно явное приведение типов. Формат base32hex удобен для UUIDv7: строка получается короче шестнадцатеричной и сохраняет порядок сортировки (при использовании правила сортировки C). Значения UUID после конвертации в base32hex дополняются справа знаками «=». Для более компактного представления эти знаки можно обрезать функцией rtrim, ведь функция decode для формата base32hex понимает оба варианта:
WITH cte(val) AS ( VALUES (uuidv7()) ) SELECT val "text", val::bytea "bytea", encode(val::bytea, 'base32hex') "base32hex", rtrim(encode(val::bytea, 'base32hex'),'=') "base32hex trimmed", decode(rtrim(encode(val::bytea, 'base32hex'),'='), 'base32hex') "bytea decoded" FROM cte \gx
-[ RECORD 1 ]-----+------------------------------------- text | 01a013f7-edf0-760d-a654-f2ec6b3a9709 bytea | \x01a013f7edf0760da654f2ec6b3a9709 base32hex | 06G17TVDU1R0R9IKUBM6MEKN14====== base32hex trimmed | 06G17TVDU1R0R9IKUBM6MEKN14 bytea decoded | \x01a013f7edf0760da654f2ec6b3a9709
PL/pgSQL: оптимизация SELECT … INTO
commit: ce8d5fe0e28
В PL/pgSQL обе конструкции делают одно и то же: вычисляют выражение и присваивают значение переменной:
DECLARE x int; BEGIN x := 2+2; SELECT 2+2 INTO x; END;
Но делают это по-разному. В первом случае выражение вычисляется прямо в PL/pgSQL, а во втором выполняется полноценный запрос через SPI, что значительно медленнее. Теперь выражения, не обращающиеся к таблицам, вычисляются напрямую, без накладных расходов SPI, если результат присваивается одной переменной. Побочный эффект: такие выражения больше не попадают в статистику pg_stat_statements.
Функции tid_block и tid_offset
commit: df6949ccf7a
Извлечь номер блока и смещение внутри блока из значения ctid раньше можно было через приведение к строке, а затем к типу point:
SELECT ctid, (ctid::text::point)[0]::bigint AS block, (ctid::text::point)[1]::int AS offset FROM bookings LIMIT 1;
ctid | block | offset -------+-------+-------- (0,1) | 0 | 1 (1 row)
Что не совсем удобно. Новые функции делают это напрямую:
SELECT ctid, tid_block(ctid), tid_offset(ctid) FROM bookings LIMIT 1;
ctid | tid_block | tid_offset -------+-----------+------------ (0,1) | 0 | 1 (1 row)
TOAST: алгоритм lz4 для сжатия по умолчанию
commit: 7c1849311e4, 34dfca29343
Исторически для сжатия TOAST используется встроенный в сервер алгоритм pglz. Однако еще в 14-й версии появилась поддержка более эффективного (как по степени сжатия, так и по использованию процессорного времени) алгоритма lz4.
В 19-й версии, если сервер собран с поддержкой lz4, то для сжатия TOAST-таблиц будет использоваться именно этот алгоритм:
SELECT setting ~ '--with-lz4' FROM pg_config() WHERE name = 'CONFIGURE';
?column? ---------- t (1 row)
\dconfig default_toast_compression
List of configuration parameters Parameter | Value ---------------------------+------- default_toast_compression | lz4 (1 row)
На этом обзор изменений 19-й версии завершен. Остается дождаться официального выпуска PostgreSQL 19, запланированного на 29 октября, и не забыть проверить финальный список изменений в Замечаниях к выпуску.