pgtt

Описание

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

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

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

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

Обычно это не является проблемой, поскольку вы уже знакомы с поведением временных таблиц в QHB, но трудности начинаются, когда вы переносите в QHB базу данных Oracle. Вам придется переписать код SQL и PlPgSQL, чтобы соответствовать логике этого приложения и использовать временные таблицы 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. Создание или удаление глобальной временной таблицы протоколируется; подробную информацию см. ниже в параграфе Как работает расширение.

Установка

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

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

Если это расширение будет использоваться обычной ролью (не суперпользователем), необходимо добавить в каталог $libdir/plugins/ эту библиотеку.

export libdir=$(pg_config --pkglibdir)
sudo mkdir $libdir/plugins/
cd $libdir/plugins/
sudo ln -s ../pgtt.so

После этого ее можно будет применить с помощью команды LOAD '$libdir/plugins/ pgtt.so'. Чтобы можно было создавать и управлять GTT из-под обычной роли, потребуется дать пользователю право CREATE для схемы pgtt_schema.

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

make installcheck

Для проверки использования расширения в выделенной схеме SESSION предоставляется дополнительный отдельный тест. Нужно удалить из файла pgtt.control следующие две строки:

schema = 'pgtt_schema'
relocatable = false
Then the tests can be executed using:

mkdir results
createdb gtt_relocation
psql -d gtt_relocation -f test/relocation.sql > results/relocation.out 2>&1
diff results/relocation.out test/expected/relocation.out
dropdb gtt_relocation

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

pgtt.enabled

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

SET pgtt.enabled TO off;

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

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

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

CREATE EXTENSION pgtt;

Суперпользователь может загрузить расширение, используя:

LOAD 'pgtt';

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

LOAD '$libdir/plugins/pgtt';

Обязательно следуйте приведенным выше инструкциям по установке, чтобы создать символическую ссылку из каталога 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.

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

Чтобы создать 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 (по умолчанию) и расширение было загружено (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 тоже будет выгружено, как и все шаблонные нежурналируемые таблицы. При восстановлении из дампа база данных будет воссоздана в неизменном состоянии.

См. также

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

Copyright © KVANTOM LLC, 2019-2024