PostgreSQL 15: Часть 4 или Коммитфест 2022-01

Первые три коммитфеста 15-й версии (2021-07, 2021-09, 2021-11) не баловали нас крупными изменениями. Не стал исключением и четвертый, январский коммитфест. Но всё-таки найдется о чем рассказать.

psql: автодополнение ключевых слов и имен объектов в разных регистрах
commit: 02b8048b, 020258fb

Практически в каждом коммитфесте встречаются патчи, улучшающие автодополнение по табуляции для отдельных команд. Начиная с 15-й версии я перестал их упоминать.

Однако этот патч стоит выделить. Речь не об автодополнении для отдельных команд. Это общее улучшение обработки имен объектов/ключевых слов, в том числе в разных регистрах.

Для себя отметил, что нажатие на табуляцию наконец-то сохраняет регистр:

vacuum ana[tab] -> vacuum analyze

раньше было:

vacuum ana[tab] -> vacuum ANALYZE

psql: команда \getenv и определение домашнего каталога пользователя
commit: 33d3eead и 376ce3e4

Команда \setenv для установки переменной окружения в psql существует давно, а вот обратной команды для получения значения не было.

Первый коммит добавляет команду \getenv в psql:

\getenv home_saved HOME
\echo :home_saved

/home/pluzanov

Вообще-то, того же самого можно добиться так:

\set home_saved `echo $HOME`

Но этот вариант работает не во всех операционных системах.

А вот второй коммит предназначен только для UNIX-систем. Он меняет способ определения домашнего каталога пользователя. Вместо getpwuid(getuid())->pw_dir теперь проверяется переменная $HOME, которую при желании можно менять:

\setenv HOME /tmp
\cd
\! pwd

/tmp

Восстановим исходное значение:

\setenv HOME :home_saved
\cd
\! pwd

/home/pluzanov

psql: \dl+ для вывода привилегий на большие объекты
commit: 328dfbda

Суть патча в названии. Но кому нужны большие объекты и права доступа к ним? Этот патч не попал бы в обзор, если бы не одно обстоятельство. Это мой патч :-)

Зачем мне большие объекты? Вообще-то не особо. Но изучая документацию здесь, стало за них обидно. Почему единственная пустая клетка в таблице относится к команде psql для работы с большими объектами? Исправлено.

Но самое удивительное, что знаний языка C на уровне первой главы из книжки Кернигана и Ритчи оказалось вполне достаточно. Так что буквально каждый может внести свой вклад в разумное, доброе, вечное. Не стесняйтесь!

postgresql: непрерывная интеграция (CI)
commit: 93d97349

Продолжая тему разработки. В исходный код сервера добавили поддержку непрерывной интеграции Cirrus CI. Это позволяет для больших и сложных проектов клонировать репозиторий postgresql на github, вносить изменения и смотреть как проект собирается и тестируется на разных платформах. Сейчас поддерживаются: FreeBSD, Linux, macOS and Windows.

Удобно тем, что для проверки работоспособности на разных платформах, патч не нужно отправлять в -hackers/commitfest, где встроен: http://cfbot.cputube.org/

Детали в src/tools/ci/README.

pg_log_backend_memory_contexts показывает распределение памяти для фоновых процессов сервера
commit: 790fbda9

Появившаяся в 14-й версии функция pg_log_backend_memory_contexts записывает в журнал сервера информацию о памяти, занятой указанным обслуживающим процессом.

Теперь можно посмотреть распределение памяти и для фоновых процессов сервера (bgwriter, wal writer, archiver, checkpointer…), кроме logger и stats collector.

Унификация вывода VACUUM VERBOSE и log_autovacuum_min_duration
commit: 49c9d9fc

В подробный отчет об очистке команды VACUUM VERBOSE включили полезную информацию, которая раньше попадала только в журнал сервера при срабатывании log_autovacuum_min_duration.

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

Ускорение обработки строк в UTF-8
commit: 911588a3

Согласно тестам автора, проверка строк в кодировке UTF-8 выполняется в разы быстрее. Это изменение точно поможет ускорить загрузку больших объемов текстовых данных командой COPY FROM.

postgres_fdw: установка application_name для сеанса на стороннем сервере
commit: 449ab635, 6e0cb3de

Для демонстрации в базе данных postgres настроен сторонний сервер на базу данных demo в этом же кластере. Выполним запрос к сторонней таблице и посмотрим на список процессов:

SELECT count(*) FROM bookings;

SELECT datname, pid, usename, application_name
FROM pg_stat_activity
WHERE datname IN ('demo','postgres');

 datname  |  pid   | usename  | application_name
----------+--------+----------+------------------
 postgres | 103897 | postgres | psql
 demo     | 103898 | postgres | postgres_fdw

По умолчанию postgres_fdw устанавливает значение параметра application_name для удаленных сеансов в «postgres_fdw». Но если подключены сразу несколько удаленных сеансов, то разобраться кто откуда пришел не очень просто.

Новый параметр расширения postgres_fdw.application_name позволяет настроить контекст подключения.

\c
SET postgres_fdw.application_name = 'fdw: %u@%d(%p)';
SELECT count(*) FROM bookings;

Теперь посмотрим на список процессов:

SELECT datname, pid, usename, application_name
FROM pg_stat_activity
WHERE datname IN ('demo','postgres');

 datname  |  pid   | usename  |        application_name        
----------+--------+----------+--------------------------------
 demo     | 104073 | postgres | fdw: postgres@postgres(104009)
 postgres | 104009 | postgres | psql

Описание спецпоследовательностей можно найти в документации. В этом примере используются имя локального пользователя(%u), имя локальной базы данных(%d) и номер процесса(%p), по которому легко найти сеанс, выполняющий запрос.

Логическая репликация без суперпользователя
commit: a2ab9c06, 96a6f11c

Процесс logical replication worker выполняется с привилегиями владельца подписки, который больше не обязан быть суперпользователем.

Настройка выполняется в два этапа. Для создания подписки по-прежнему требуются права суперпользователя. Но затем их можно забрать. Главное, чтобы у владельца подписки были права на запись в таблицы, куда применяются изменения.

Для примера будем реплицировать таблицу test из базы данных postgres в базу db, расположенную в этом же кластере.

\с postgres postgres
CREATE TABLE test (id int);
CREATE ROLE alice LOGIN SUPERUSER;
CREATE DATABASE db OWNER alice;

Создаем публикацию и слот:

CREATE PUBLICATION test_pub FOR TABLE test;
SELECT pg_create_logical_replication_slot('testslot','pgoutput');

На стороне подписки, пока alice является суперпользователем:

\c db alice
CREATE TABLE test (id int);
CREATE SUBSCRIPTION test_sub
    CONNECTION 'user=postgres dbname=postgres'
    PUBLICATION test_pub
    WITH (create_slot = false, slot_name = testslot);

Забираем права суперпользователя и проверяем работу репликации:

\с postgres postgres
ALTER ROLE alice NOSUPERUSER;
INSERT INTO test VALUES(1);

\c db alice
SELECT * FROM test;

 id
----
  1
(1 row)

Пока есть ограничение, которое возможно будет снято в следующих версиях. Если для таблицы test на стороне подписки создать политики защиты строк (RLS), то логическая репликация перестанет работать, даже если политики не мешают alice изменять таблицу. Нужно или вернуть атрибут superuser для alice, или установить атрибут bypassrls.

Журнал сервера в формате JSON
commit: dc686681

У параметра log_destination новое значение ― jsonlog. А это значит, что журнал сервера теперь поддерживает формат JSON. Просматривать глазами журнал в этом формате сложнее, чем в stderr. Однако для разработчиков инструментов анализа журнала это хорошая новость. Формат записи, конечно же, есть в документации.

Описание от Depesz.

Настройки по умолчанию: log_checkpoints и log_autovacuum_min_duration
commit: 64da07c4

Когда что-то пошло не так с производительностью системы, хорошо бы иметь побольше информации для анализа.

С этой целью, начиная с 15-й версии, меняются значения по умолчанию двух параметров:

SELECT name, boot_val, unit
FROM pg_settings
WHERE name IN ('log_checkpoints','log_autovacuum_min_duration');

            name             | boot_val | unit
-----------------------------+----------+------
 log_autovacuum_min_duration | 600000   | ms
 log_checkpoints             | on       |

А это значит, что информация о выполнении контрольных точек и долгих (>10мин) запусках автоматической очистки будет записываться в журнал сервера.

Но не обязательно ждать перехода на 15-ю версию. Если параметры не включены сейчас, их стоит включить.

Составные внешние ключи с ON DELETE SET NULL
commit: d6f96ed9

При создании внешних ключей можно указать действия при удалении родительской записи. Например сбросить значения внешнего ключа у всех ссылающихся записей в NULL. Однако в ситуациях, когда первичный/внешний ключ являются составными, могут возникнуть неожиданные проблемы.

В следующем примере таблица сотрудников имеет составной первичный ключ, в который помимо кода сотрудника включен еще и код компании. Кроме того есть и внешний ключ ― код руководителя сотрудника, опять же составной.

CREATE TABLE employees (
    company_code text,
    employee_code text,
    manager_code text,
    PRIMARY KEY (company_code, employee_code),
    CONSTRAINT employees_manager_fkey FOREIGN KEY (company_code, manager_code)
            REFERENCES employees (company_code, employee_code)
            ON DELETE SET NULL
);

INSERT INTO employees VALUES
    ('Головной офис', 'Директор', NULL),
    ('Филиал', 'Директор', NULL),
    ('Филиал', 'Зам.директора', 'Директор');
SELECT * FROM employees;

 company_code  | employee_code | manager_code
---------------+---------------+--------------
 Головной офис | Директор      |
 Филиал        | Директор      |
 Филиал        | Зам.директора | Директор
(3 rows)

Предположим, что мы решили удалить строку с директором филиала, а благодаря опции ON DELETE SET NULL надеемся, что его подчиненные пока останутся без руководителя:

DELETE FROM employees
    WHERE company_code = 'Филиал' AND employee_code = 'Директор';

ERROR:  null value in column "company_code" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (null, Зам.директора, null).

Но в составном внешнем ключе столбец company_id является еще и частью первичного ключа, поэтому сбросить его значение в NULL не получится.

В 15-й версии, определяя внешний ключ с ON DELETE SET NULL|DEFAULT можно дополнительно указать список столбцов, к которым это указание будет относится:

ALTER TABLE employees
    DROP CONSTRAINT employees_manager_fkey,   
    ADD CONSTRAINT employees_manager_fkey
            FOREIGN KEY (company_code, manager_code)
            REFERENCES employees (company_code, employee_code)
             ON DELETE SET NULL (manager_code);

Т.е. мы хотим, чтобы только код руководителя сбрасывался в NULL, а код компании пусть остается без изменений.

DELETE FROM employees
    WHERE company_code = 'Филиал' AND employee_code = 'Директор';
SELECT * FROM employees;

 company_code  | employee_code | manager_code
---------------+---------------+--------------
 Головной офис | Директор      |
 Филиал        | Зам.директора |
(2 rows)

Такой подход может пригодиться для приложений, которые используются разными клиентами и где требуется четкое разграничение данных. Для этих целей в каждую таблицу в состав первичного ключа можно смело добавлять некий tenant_id не боясь получить вышеописанную проблему.

UNIQUE и NULL
commit: 94aa7cc5

Ограничение уникальности допускает значения NULL. Начиная с 15-й версии для этого утверждения требуется уточнение.

Создавая ограничение уникальности можно указать как трактовать значения NULL:

• NULLS DISTINCT ― значения NULL считаются разными, это поведение по умолчанию.
• NULLS NOT DISTINCT ― значения NULL считаются одинаковыми.

Посмотрим на новое поведение:

CREATE TABLE test (
    c1 int,
    c2 int,
    UNIQUE NULLS NOT DISTINCT (c1,c2)
);

В описании ограничения UNIQUE появилось соответствующее уточнение:

\d test

               Table "bookings.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 c1     | integer |           |          |
 c2     | integer |           |          |
Indexes:
    "test_c1_c2_key" UNIQUE CONSTRAINT, btree (c1, c2) NULLS NOT DISTINCT

Записи вставляются в таблицу, пока нет дублирования значений NULL.

INSERT INTO test VALUES
    (1, NULL),
    (NULL, 1),
    (NULL, NULL);

Однако повторно добавить любую из этих строк не получится, они не пройдут проверку на уникальность:

INSERT INTO test VALUES (1, NULL);

ERROR:  duplicate key value violates unique constraint "test_c1_c2_key"
DETAIL:  Key (c1, c2)=(1, null) already exists.

INSERT INTO test VALUES (NULL, 1);

ERROR:  duplicate key value violates unique constraint "test_c1_c2_key"
DETAIL:  Key (c1, c2)=(null, 1) already exists.

INSERT INTO test VALUES (NULL, NULL);

ERROR:  duplicate key value violates unique constraint "test_c1_c2_key"
DETAIL:  Key (c1, c2)=(null, null) already exists.

Аналогичное указание при создании уникальных индексов:

CREATE UNIQUE INDEX index_name ON table_name (column_list) NULLS NOT DISTINCT;

Формально это патч из мартовского коммитфеста, но случился в начале февраля, поэтому пусть завершит январский.

---

На этом пока всё. Впереди финальный мартовский коммитфест, после которого можно подводить итоги всего релизного цикла 15-й версии.