pgtt

Описание

pgtt — это расширение QHB для создания, управления и использования глобальных временных таблиц (Global Temporary Table, GTT) в стиле Oracle и других реляционных СУБД.

Цель этого расширения — предоставить QHB функционал глобальных временных таблиц, ожидающий реализации в ядре. Основная задача этого расширения состоит в том, чтобы имитировать поведение Oracle с GTT, когда нет возможности или желания переписывать код приложения при переходе на QHB. Во всех остальных случаях лучше всего переписать код для использования стандартных временных таблиц QHB.

В этой версии расширения GTT в качестве таблицы-«шаблона» используется обычная нежурналируемая таблица и внутренняя переадресация на временную таблицу. Подробную информацию см. в параграфе Как работает расширение.

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

Обычно это не является проблемой, поскольку пользователь уже знаком с поведением временных таблиц в QHB, но при переносе в QHB базы данных Oracle начинаются трудности. Чтобы соответствовать логике этого приложения, придется переписать код SQL и Pl/pgSQL и использовать временные таблицы QHB что подразумевает пересоздание временных таблиц везде, где они используются.

Другое преимущество объекта такого рода состоит в том, что когда приложение создает и удаляет множество временных таблиц, каталоги QHB «разбухают», и производительность начинает падать. Обычно GTT предотвращают «разбухание» каталогов, но с этой реализацией даже если у нас есть постоянная таблица, все команды DML переадресуются в обычную временную таблицу, созданную при первом обращении. Более подробную информацию см. ниже в параграфе Как работает расширение.

Оператор DECLARE TEMPORARY TABLE не поддерживается QHB и этим расширением. Тем не менее этот оператор определяет временную таблицу для текущего подключения/сеанса, создает таблицы, которые не хранятся в системных каталогах и не являются долговременными. Их нельзя использовать совместно с другими сеансами. Этот оператор равнозначен стандартной команде QHB CREATE TEMPORARY TABLE, так что нужно только заменить ключевое слово DECLARE на CREATE.

Все поведение GTT в Oracle сохраняется, в том числе различные предложения, за исключением того, что не поддерживается QHB:

ON COMMIT {DELETE | PRESERVE} ROWS

Задает действие, применяемое к глобальной временной таблице, когда выполняется операция фиксации (COMMIT).

  • DELETE ROWS: все строки таблицы будут удалены, если для нее не открыто ни одного удерживаемого курсора.

  • PRESERVE ROWS: после фиксации (COMMIT) строки таблицы будут сохранены.

LOGGED или NOT LOGGED [ ON ROLLBACK {DELETE | PRESERVE} ROWS ]

Определяет, протоколируются ли операции для этой таблицы. Значение по умолчанию — NOT LOGGED ON ROLLBACK DELETE ROWS.

  • NOT LOGGED: если указано, то совершаемые над таблицей операции добавления, изменения или удаления не протоколируются, но создание или удаление самой таблицы должно протоколироваться. Во время операции ROLLBACK или ROLLBACK TO SAVEPOINT:

    • Если таблица была создана в рамках транзакции, она удаляется.
    • Если таблица была удалена в рамках транзакции, она воссоздается, но без каких-либо данных.

  • ON ROLLBACK: задает действие, которое должно совершиться над созданной нежурналируемой временной таблицей при выполнении операции ROLLBACK или ROLLBACK TO SAVEPOINT. Значение по умолчанию — DELETE ROWS.

    • DELETE ROWS: если данные таблицы были изменены, все строки будут удалены.
    • PRESERVE ROWS: строки таблицы будут сохранены.

  • LOGGED: если указано, то совершаемые над таблицей операции добавления, изменения или удаления данных, а также создание или удаление самой таблицы должны протоколироваться.

В QHB может поддерживаться только NOT LOGGED ON ROLLBACK DELETE ROWS. Создание или удаление глобальной временной таблицы протоколируется; подробную информацию см. ниже в параграфе Как работает расширение.

Установка

Для установки расширения pgtt требуется QHB версии 1.5.1 или выше.

Модуль управления глобальными временными таблицами для QHB поставляется в виде пакета qhb-1.5.2-pgtt.

Подключите репозиторий пакетов QHB и установите пакет расширения для выбранной платформы со страницы загрузки.

После этого его можно будет применить, установив параметр session_preload_libraries = 'pgtt' в файле qhb.conf.

Чтобы создавать и управлять GTT из-под обычной роли, потребуется дать пользователю право CREATE для схемы pgtt_schema. Например:

GRANT ALL ON SCHEMA pgtt_schema TO pgtt_user1;

Для выполнения теста выполните от имени суперпользователя следующую команду:

make installcheck

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

mkdir results
createdb gtt_privilege
LANG=C psql -d gtt_privilege -f test/privilege.sql > results/privilege.out 2>&1
diff results/privilege.out test/expected/privilege.out
dropdb gtt_privilege
dropuser pgtt_user1

Конфигурация

pgtt.enabled

С помощью данного параметра можно включать и отключать расширение. По умолчанию параметр включен. Чтобы отключить расширение, используйте:

SET pgtt.enabled TO off;

Отключить или включить расширение можно в любой момент сеанса.

Использование расширения

Во всех базах данных, где вы желаете использовать глобальные временные таблицы, потребуется создать расширение, используя:

CREATE EXTENSION pgtt;

Загрузить расширение можно, установив в qhb.conf:

session_preload_libraries = 'pgtt';

или установив его на уровне базы данных следующим образом:

DO $$
BEGIN
    EXECUTE format('ALTER DATABASE %I SET session_preload_libraries = ''pgtt''', current_database());
END
$$;

Обычный пользователь должен загрузить библиотеку, используя каталог plugins/ следующим образом:

DO $$
BEGIN
    EXECUTE format('ALTER DATABASE %I SET session_preload_libraries = ''$libdir/plugins/pgtt''', current_database());
END
$$;

Обязательно следуйте приведенным выше инструкциям по установке, чтобы создать символическую ссылку из каталога plugins/ на файл библиотеки расширений.

Расширение pgtt использует для хранения связанных объектов выделенную схему; по умолчанию это pgtt_schema. Расширение следит за тем, чтобы эта схема всегда находилась в конце пути поиска (search_path).

gtt_testdb=# LOAD '$libdir/plugins/pgtt';
LOAD
gtt_testdb=# SHOW search_path;
    search_path
--------------------
 public,pgtt_schema
(1 row)

gtt_testdb=# SET search_path TO appschema,public;
SET
gtt_testdb=# SHOW search_path;
	  search_path
--------------------------------
 appschema, public, pgtt_schema
(1 row)

Схема pgtt автоматически добавляется в search_path, когда загружается расширение и в случае последующих изменений search_path.

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

Создание глобальной временной таблицы

Чтобы создать GTT-таблицу с именем test_gtt_table, выполните следующий оператор:

CREATE GLOBAL TEMPORARY TABLE test_gtt_table (
	id integer,
	lbl text
) ON COMMIT { PRESERVE | DELETE } ROWS;

Ключевое слово GLOBAL устарело, но его можно безопасно использовать, хотя оно и выдаст предупреждение:

WARNING:  GLOBAL is deprecated in temporary table creation
-- ПРЕДУПРЕЖДЕНИЕ: при создании временной таблицы слово GLOBAL является устаревшим

Для исключения этого предупреждающего сообщения допускается использовать GLOBAL в виде комментария:

CREATE /*GLOBAL*/ TEMPORARY TABLE test_gtt_table (
	LIKE other_table LIKE
	INCLUDING DEFAULTS
	INCLUDING CONSTRAINTS
	INCLUDING INDEXES
) ON COMMIT { PRESERVE | DELETE } ROWS;

Расширение обнаружит ключевое слово GLOBAL.

Как видно из приведенного выше примера, поддерживается предложение LIKE, а также предложение AS с WITH DATA или с WITH NO DATA (по умолчанию):

CREATE /*GLOBAL*/ TEMPORARY TABLE test_gtt_table
AS SELECT * FROM source_table WITH DATA;

В случае WITH DATA расширение заполнит GTT данными, возвращенными из оператора SELECT, только для текущего сеанса.

Предложение временной таблицы QHB ON COMMIT DROP данным расширением не поддерживается, GTT сохраняются в транзакциях. При использовании этого предложения возникнет ошибка.

Строки временной таблицы удаляются или сохраняются при фиксации транзакций в соответствии с предложением:

ON COMMIT { PRESERVE | DELETE } ROWS

Удаление глобальной временной таблицы

Чтобы удалить глобальную временную таблицу, выполняются те же действия, что и для обычной таблицы:

DROP TABLE test_gtt_table;

Глобальную временную таблицу можно удалить, даже если она используется другим сеансом.

Создание индексов в глобальной временной таблице

В глобальной временной таблице можно создавать индексы:

CREATE INDEX ON test_gtt_table (id);

так же, как и в любых других таблицах.

Ограничения для глобальной временной таблицы

В глобальную временную таблицу можно добавить любое ограничение, кроме ограничения внешнего ключа (FOREIGN KEYS).

CREATE GLOBAL TEMPORARY TABLE t2 (
	c1 serial PRIMARY KEY,
	c2 VARCHAR (50) UNIQUE NOT NULL,
	c3 boolean DEFAULT false
)

Использование FOREIGN KEYS в глобальной временной таблице не допускается.

CREATE GLOBAL TEMPORARY TABLE t1 (c1 integer, FOREIGN KEY (c1) REFERENCES source (id));
ERROR:  attempt to create referential integrity constraint on global temporary table
-- ОШИБКА: попытка создания ограничения ссылочной целостности для глобальной временной
-- таблицы

ALTER TABLE t2 ADD FOREIGN KEY (c1) REFERENCES source (id);
ERROR:  attempt to create referential integrity constraint on global temporary table

Даже если QHB допускает во временной таблице внешние ключи, расширение pgtt пытается максимально имитировать поведение Oracle и других СУБД, таких как DB2, SQL Server или MySQL.

ORA-14455: attempt to create referential integrity constraint on temporary table.

Партиционирование

Партиционирование для глобальной временной таблицы не поддерживается, опять же не потому, что QHB не допускает партиционирование временных таблиц, а потому, что другие СУБД, такие как Oracle, DB2 и MySQL, его не поддерживают. SQL Server поддерживает партиционирование глобальных временных таблиц.

Как работает расширение

Использование глобальных временных таблиц

Если параметр pgtt.enabled равен true (по умолчанию) и расширение было загружено любым из этих трех способов:

  • session_preload_libraries = 'pgtt' в qhb.conf
  • ALTER DATABASE mydb SET session_preload_libraries = 'pgtt'
  • LOAD 'pgtt'; в сеансе

первое обращение к таблице с применением оператора SELECT, UPDATE или DELETE приведет к созданию временной таблицы с помощью определения таблицы-«шаблона», созданной при вызове оператора CREATE GLOBAL TEMPORARY TABLE.

Сразу после создания временной таблицы при первом обращении исходный оператор SELECT, UPDATE или DELETE автоматически перенаправляется в новую обычную временную таблицу. При всех остальных обращениях будет задействована новая временная таблица, при этом первой в пути поиска всегда будет просматриваться схема pg_temp*, где создается эта таблица. Именно поэтому последующие обращения не затрагивают таблицу-«шаблон».

Создание, переименование и удаление GTT является административной задачей и не должно осуществляться в сеансе приложения.

Обратите внимание, что переадресация останется активна, даже если дополнить таблицу именем табличного пространства. Например, просмотр внутренней нежурналируемой таблицы-шаблона:

bench=# LOAD 'pgtt';
LOAD
bench=# CREATE /*GLOBAL*/ TEMPORARY TABLE test_tt (id int, lbl text) ON COMMIT PRESERVE ROWS;
CREATE TABLE
bench=# INSERT INTO test_tt VALUES (1, 'one'), (2, 'two'), (3, 'three');
INSERT 0 3
bench=# SELECT * FROM pgtt_schema.test_tt;
 id |  lbl
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

фактически даст тот же результат, что и просмотр связанной временной таблицы, как в следующих примерах:

bench=# SELECT * FROM test_tt;
 id |  lbl
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

или

bench=# SELECT * FROM pg_temp.test_tt;
 id |  lbl
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

Если вы желаете действительно просмотреть таблицу-шаблон, чтобы убедиться, что она не содержит строк, следует выключить переадресацию расширения:

bench=# SET pgtt.enabled TO off;
SET
bench=# SELECT * FROM pgtt_schema.test_tt;
 id | lbl
----+-----
(0 rows)

bench=# SET pgtt.enabled TO on;
SET
bench=# SELECT * FROM pgtt_schema.test_tt;
 id |  lbl
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

Дополнительные примеры можно найти в тестовом файле.

Это также означает, что вы можете перенести данное расширение в выделенное пространство имен. Это может быть полезно, если в запросах вашего приложения для обращения к GTT имя таблицы дополняется схемой, и вы не можете этого изменить. Пример см. в t/sql/relocation.sql. По умолчанию это расширение нельзя перенести в другую схему, поэтому чтобы воспользоваться этим функционалом, понадобятся некоторые изменения конфигурации.

Если вы используете форму CREATE AS с предложением WITH DATA, как в этом примере:

CREATE /*GLOBAL*/ TEMPORARY TABLE test_gtt_table
AS SELECT * FROM source_table WITH DATA;

данное расширение сначала создаст нежурналируемую таблицу-шаблон, а затем немедленно создаст связанную временную таблицу, заполненную всеми данными, возвращенными оператором SELECT. Тогда при первом обращении не понадобится создавать таблицу — она уже существует и имеет данные.

Создание таблицы

Данное расширение перехватывает вызов оператора CREATE TEMPORARY TABLE ... и проверяет, имеется ли в нем ключевое слово GLOBAL или комментарий /*GLOBAL*/. При его обнаружении, вместо создания временной таблицы, расширение создает нежурналируемую хранимую таблицу-«шаблон», следуя определению временной таблицы. После создания шаблона оно регистрирует эту таблицу в таблицу-«каталог» pg_global_temp_tables.

Оба объекта создаются в схеме расширения pgtt_schema.

Если параметр pgtt.enabled равен false, ничего не происходит.

Ниже приводится описание таблицы-каталога:

          Table « pgtt_schema.pg_global_temp_tables »
  Colonne  |  Type   | Collationnement | NULL-able | Par défaut
-----------+---------+-----------------+-----------+------------
 relid     | integer |                 | not null  |
 nspname   | name    |                 | not null  |
 relname   | name    |                 | not null  |
 preserved | boolean |                 |           |
 code      | text    |                 |           |
Index :
    "pg_global_temp_tables_nspname_relname_key" UNIQUE CONSTRAINT, btree (nspname, relname)
  • relid: OID нежурналируемой таблицы-«шаблона».
  • nspname: пространство имен pgtt_schema расширения по умолчанию.
  • relname: имя отношения GTT.
  • preserved: true или false для ON COMMIT { PRESERVE | DELETE }.
  • code: код, использованный во время создания глобальной временной таблицы.

Удаление таблицы

Данное расширение перехватывает вызов оператора DROP TABLE и просматривает таблицу pg_global_temp_tables, проверяя, объявлена ли целевая таблица. Найдя ее, оно удаляет нежурналируемую таблицу-«шаблон» и соответствующую запись из таблицы-каталога pgtt pg_global_temp_tables.

Если параметр pgtt.enabled равен false, ничего не происходит.

Удаление используемой GTT, когда временная таблица уже была создана, вызовет ошибку. Это не разрешено.

Переименование таблицы

Данное расширение перехватывает вызов оператора ALTER TABLE ... RENAME и просматривает таблицу pg_global_temp_tables, проверяя, объявлена ли целевая таблица. Найдя ее, оно переименовывает таблицу-«шаблон» и меняет имя отношения в таблице pg_global_temp_tables. Если GTT уже использовалась в сеансе, соответствующая временная таблица уже существует, и в этом случае расширение откажется ее переименовывать. Для переименования она должна быть неактивна.

Если параметр pgtt.enabled равен false, ничего не происходит.

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

qhb_dump / qhb_restore

Если используется расширение pgtt, то при создании дампа базы данных, содержимое таблицы-каталога pg_global_temp_tables тоже будет выгружено, как и все нежурналируемые таблицы-шаблоны. При восстановлении из дампа база данных будет воссоздана в неизмененном состоянии.

Производительность

Издержки при загрузке расширения, но без использования его в сценарии, подобном pgbench tpcb.

Без загрузки приложения:

$ pgbench -h localhost bench -c 20 -j 4 -T 60 -f test/bench/bench_noload.sql
starting vacuum...end.
transaction type: test/bench/bench_noload.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 4
duration: 60 s
number of transactions actually processed: 51741
latency average = 23.201 ms
tps = 862.038042 (including connections establishing)
tps = 862.165341 (excluding connections establishing)

С загрузкой приложения:

$ pgbench -h localhost bench -c 20 -j 4 -T 60 -f test/bench/bench_load.sql
starting vacuum...end.
transaction type: test/bench/bench_load.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 4
duration: 60 s
number of transactions actually processed: 51171
latency average = 23.461 ms
tps = 852.495877 (including connections establishing)
tps = 852.599010 (excluding connections establishing)

Сравнение использования обычной временной таблицы и PGTT в сценарии, подобном pgbench.

Использование обычной временной таблицы:

$ pgbench -h localhost bench -c 20 -j 4 -T 60 -f test/bench/bench_use_rtt.sql
starting vacuum...end.
transaction type: test/bench/bench_use_rtt.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 4
duration: 60 s
number of transactions actually processed: 17153
latency average = 70.058 ms
tps = 285.477860 (including connections establishing)
tps = 285.514186 (excluding connections establishing)

Использование глобальной временной таблицы:

CREATE GLOBAL TEMPORARY TABLE test_tt (id int, lbl text) --Создание тестовой таблицы
		ON COMMIT DELETE ROWS;

$ pgbench -h localhost bench -c 20 -j 4 -T 60 -f test/bench/bench_use_gtt.sql
starting vacuum...end.
transaction type: test/bench/bench_use_gtt.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 4
duration: 60 s
number of transactions actually processed: 17540
latency average = 68.495 ms
tps = 291.993502 (including connections establishing)
tps = 292.028832 (excluding connections establishing)

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

Авторы

  • Гиллес Дарольд (Gilles Darold)

  • Жюльен Руо (Julien Rouhaud)

См. также

Документация по pgtt на Github

Copyright © KVANTOM LLC, 2019-2025