kdv, 19.01.2005, last updated 09.02.2005
В InterBase 7.5 появилась возможность работы с временными таблицами. В отличие от временных системных таблиц (tmp$) данные таблицы могут быть созданы и использованы в момент работы приложений. До настоящего момента разработчикам приходилось хранить временные данные в обычных таблицах, что требовало постоянного слежения за содержимым таблиц, а также специфической организации работы с данными (см. статью). Чаще всего, конечно, временные таблицы требовались разработчикам, которые до работы с InterBase/Firebird имели опыт работы с MS SQL.
Давайте рассмотрим, что же именно представляют собой временные таблицы в InterBase 7.5
Сами по себе временные таблицы на самом деле постоянные, то есть, когда вы их создаете, информация о них сохраняется в системной таблице RDB$RELATIONS, как и для обычных таблиц, и эти таблицы не только будут храниться в базе данных постоянно, но и "переживут" backup/restore (в отличие от любых других попыток расширить или изменить структуру системных таблиц rdb$).
Синтаксис создания временных таблиц следующий:
CREATE GLOBAL TEMPORARY TABLE <table> (
table-element-comma-list )
[ON COMMIT { PRESERVE | DELETE } ROWS]
Как видите, от обычных таблиц это отличается фразой global temporary и добавкой on commit. В IB 7.5 в системной таблице RDB$RELATIONS добавлен столбец RDB$RELATION_TYPE, который содержит
| RDB$RELATION_TYPE | Описание |
|---|---|
| PERSISTENT | Обычные таблицы (пользовательские или системные), в которых записи удаляются только при delete+commit. |
| GLOBAL TEMPORARY | Временные системные таблицы, отображающие состояние сервера, подключение к базам, выполняемые запросы и т.п. (TMP$DATABASE и другие) |
| GLOBAL TEMPORARY DELETE | Временные таблицы, для которых указано ON COMMIT DELETE ROWS, т.е. записи будут безусловно удалены по commit. |
| GLOBAL TEMPORARY PRESERVE | Временные таблицы, для которых указано ON COMMIT PRESERVE ROWS, т.е. записи будут безусловно удалены только при disconnect. |
Не стоит пытаться самостоятельно модифицировать этот столбец - ничего хорошего из этого не выйдет, то есть превратить обычную таблицу во временную или наоборот не удастся.
Таблицы GLOBAL TEMPORARY DELETE хранят записи только до ближайшего commit, причем не только транзакции, которая их создала, но и любой другой транзакции в этом же коннекте (данное поведение похоже на bug, т.к. commit конкурирующих в коннекте транзакций не должен сбрасывать видимость записей. При этом временные системные таблицы работают точно так же, то есть показывают обновленную информацию сразу, как только любая конкурирующая транзакция сделает commit). При этом созданные в таблице записи не видны никому кроме текущей транзакции. Rollback в данном случае эквивалентен commit, однако понятно, что rollback отменит и все изменения, произведенные над обычными таблицами. А в случае commit изменения транзакции будут подтверждены, а записи во временных таблицах "исчезнут".
Давайте создадим такую таблицу и попробуем с ней поработать.
CREATE GLOBAL TEMPORARY table TMPTRANS (
ID int not null,
NAME varchar(20),
constraint PK_TMPTRANS primary key (id) )
ON COMMIT DELETE ROWS
Теперь можно создать процедуру, которая будет заполнять эту таблицу какими-либо данными
CREATE PROCEDURE XTRANS
AS
DECLARE variable I INT;
BEGIN
I = 0;
WHILE (:I < 10000) DO
BEGIN
INSERT INTO detail VALUES (:I, 'asdfasdfasdfasdfasdf');
I = :I+1;
END
END
количество вставляемых записей выбирайте по вкусу - если собираетесь просто проверить работу, то хватит 100-10К. Если быстродействие - то можно начать со 100К записей или миллиона (например, на моем компьютере этой процедурой в базу с размером страницы 4К 1 миллион записей заливается примерно 47 секунд).
Будьте внимательны, после вставки записей не делайте commit, иначе записи пропадут. Сделайте select * from tmptran, записи будут видны. После commit повтор запроса выдаст пустую таблицу.
замечание: если вы выполняете эти операции в IBExpert, то вы не увидите никаких временных записей, т.к. IBExpert при выполнении очередного оператора в SQLEditor делает start/commit 3-4 других транзакций, commit которых вызывает потерю видимости записей в on commit delete таблице.
В этот момент может возникнуть вопрос - а где эти записи находятся на самом деле? Ответ - несмотря на "временность" записей во временных таблицах записи хранятся точно так же, как и в обычных таблицах - на диске. Причем, если после вставки записей и до commit собрать статистику например IBAnalyst-ом, то статистика будет примерно следующей:
Table Records RecLength VerLen Versions Max Vers Data Pages Slots Avg fill% TMPTRANS 1000000 31.00 0.00 0 0 51725 51725 68
Таблицы GLOBAL TEMPORARY PRESERVE хранят записи до отсоединения коннекта, в котором они были добавлены, причем их видимость ограничена только этим коннектом.
Создадим таблицу
CREATE GLOBAL TEMPORARY table TMPCONN (
ID int not null,
NAME varchar(20),
constraint PK_TMPTRANS primary key (id) )
ON COMMIT PRESERVE ROWS
Создайте запись в этой таблице (можно и в IBExpert)
INSERT INTO TMPCONN VALUES (1, 'a')
Сделайте commit. Теперь, в пределах данного коннекта, запись будет видна из разных транзакций. Если запустить еще один экземпляр IBExpert или другой инструмент, и выполнить тот же самый оператор insert - он будет выполнен без ошибки PK or UNUQIE key violation.
Как только вы закроете текущее соединение и откроете новое, вставленные данные пропадут.
Интересно, что вы можете создавать Foreign Key между временными таблицами. Между временной и постоянной этого сделать нельзя. Однако, при создании FK нужно учитывать область видимости записей в обоих таблицах. Например, вы создаете две таблицы:
MASTER, on commit delete
DETAIL, on commit preserve
и создаете FK от DETAIL на MASTER. В результате получилось бы (на самом деле IB не даст создать такой FK) что после создания записей в master и detail первый же коммит уничтожил бы записи в master, что вызвало бы наличие записей с отсутствующей связью в DETAIL (собственно, не допускается тип связи on commit preserve -> on commit delete. Обратное допускается).
Для этого, и вообще для смены типа "временности" записей, оператор ALTER TABLE имеет расширение:
ALTER TABLE <table> ON COMMIT {PRESERVE | DELETE} ROWS
{RESTRICT| CASCADE}
Данный оператор меняет тип таблицы (preserve/delete) и может также каскадно поменять тип связанных по FK таблиц (cascade) на корректный, чтобы не возникало вышеописанных ситуаций с несоответствием времени жизни записей в master и detail. Указание RESTRICT сообщит об ошибке, если на данную таблицу ссылаются другие временные таблицы.
Временные таблицы любого типа не могут ссылаться по FK на постоянные таблицы.
Как уже говорилось выше, несмотря на "временность" содержимого таблиц on commit delete и on commit preserve записи, тем не менее, хранятся на диске, как и в обычных таблицах. Соответственно, сервер должен когда-нибудь убирать их (как мусорные). Это происходит в следующие моменты
| Тип таблицы | Когда собирается мусор |
|---|---|
| ON COMMIT DELETE | При первом "монопольном" подсоединении к БД |
| ON COMMIT PRESERVE | При отсоединении коннекта, создавшего записи |
Пример процедуры, автоматически наполняющей временную таблицу записями, приведен не случайно. Тесты проводились на объеме в 1 миллион записей. Для тестов использовалось 2 запущенных экземпляра IBExpert и один IB_SQL. Не вдаваясь в подробности теста, сразу приведем его результаты и выводы
Временные таблицы InterBase 7.5 - достаточно полезная функциональность для приложений, которые формируют сложные отчеты или производят промежуточные вычисления на сервере. Однако, из-за странного поведения on commit delete временные таблицы контекста транзакции оказывается возможным использовать только в тех приложениях, которые работают не более чем с одной транзакцией единовременно, или с несколькими транзакциями но с условием запрета commit конкурирующих транзакций, пока транзакция, работающая с временной таблицей, не сделает commit. Кроме того, on commit delete приводит к накоплению мусорных записей при многопользовательской работе (рост размера БД), и к сборке мусора при первом коннекте к БД, что может быть нежелательной задержкой начала работы пользователей.
ON COMMIT PRESERVE представляется более выгодным вариантом, при котором единственной платой является более долгий disconnect приложений (если они создавали записи во временных таблицах, конечно). Вполне вероятно, чтобы избежать жалоб пользователей, disconnect приложения придется обрабатывать специально, выводя некое сообщение с просьбой подождать некоторое время.
p.s. во время тестов временных таблиц наблюдалась самопроизвольная загрузка процессора сервером IB75 при отсутствии активности в приложениях. Причем, загрузка появлялась при определенном порядке старта и завершения транзакций, которые не содержали ни одного выполняемого оператора. Причина данного эффекта выясняется в настоящий момент.
Обсудить статью на форуме.