EnРабота с временными таблицами InterBase 7.5
KDV, 19.01.2005, последнее обновление – 09.02.2005
В InterBase 7.5 появилась возможность работы с временными таблицами. В отличие от временных системных таблиц (tmp$) данные таблицы могут быть созданы и использованы в момент работы приложений. До настоящего момента разработчикам приходилось хранить временные данные в обычных таблицах, что требовало постоянного слежения за содержимым таблиц, а также специфической организации работы с данными (см. статью LINK). Чаще всего, конечно, временные таблицы требовались разработчикам, которые до работы с InterBase/Firebird имели опыт работы с MS SQL.
Давайте рассмотрим, что же именно представляют собой временные таблицы в InterBase 7.5.
Метаданные
Сами по себе временные таблицы на самом деле постоянные, то есть, когда вы их создаете, информация о них сохраняется в системной таблице RDB$RELATIONS, как и для обычных таблиц, и эти таблицы не только будут храниться в базе данных постоянно, но и "переживут" backup/restore (в отличие от любых других попыток расширить или изменить структуру системных таблиц rdb$).Синтаксис создания временных таблиц следующий:
CREATE GLOBAL TEMPORARY TABLE (
table-element-comma-list )
[ON COMMIT { PRESERVE | DELETE } ROWS]
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
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
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
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
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.
Как только вы закроете текущее соединение и откроете новое, вставленные данные пропадут.
Связи между temporary-таблицами
Интересно, что вы можете создавать 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. Обратное допускается).DETAIL, on commit preserve
Для этого, и вообще для смены типа "временности" записей, оператор ALTER TABLE имеет расширение:
ALTER TABLE <table> ON COMMIT {PRESERVE | DELETE} ROWS
{RESTRICT| CASCADE}
{RESTRICT| CASCADE}
Данный оператор меняет тип таблицы (preserve/delete) и может также каскадно поменять тип связанных по FK таблиц (cascade) на корректный, чтобы не возникало вышеописанных ситуаций с несоответствием времени жизни записей в master и detail. Указание RESTRICT сообщит об ошибке, если на данную таблицу ссылаются другие временные таблицы.
Временные таблицы любого типа не могут ссылаться по FK на постоянные таблицы.
Сборка мусора
Как уже говорилось выше, несмотря на "временность" содержимого таблиц on commit delete и on commit preserve записи, тем не менее, хранятся в базе, как и данные обычных таблиц. Соответственно, сервер должен когда-нибудь убирать их (как мусорные). Это происходит в следующие моменты| Тип таблицы | Когда собирается мусор |
| ON COMMIT DELETE | При первом "монопольном" подсоединении к БД |
| ON COMMIT PRESERVE | При отсоединении коннекта, создавшего записи |
- для таблиц on commit delete мусор собирается при первом монопольном соединении к БД. Например, работает 10 приложений, которые заполняют временные таблицы. Чтобы записи во временных таблицах были удалены, нужно чтобы все 10 приложений отсоединились, и хоть одно подсоединилось – именно в этот момент начнется сборка мусора в таблицах on commit delete. В этот момент "зависнут" все коннекты, которые пытаются подсоединиться к серверу до момента окончания сборки мусора.
- Выводы:
- работа с временными таблицами on commit delete может привести к сильному росту базы данных в течение дня, т.к. в это время редко бывают случаи, когда все пользователи отсоединяются от базы данных
- чем больше мусора накоплено во временных таблицах on commit delete, тем дольше пауза между первым коннектом и работой. Для удаления 1 миллиона записей сервер тратит ~25 секунд, для удаления трех миллионов – ~120 секунд.
- Выводы:
- для таблиц on commit preserve мусор собирается в момент отсоединения того коннекта, который создавал эти записи.
- Выводы:
- Чем больше записей создал коннект во временных таблицах – тем дольше приложение будет "висеть" при disconnect. Удаление 1 миллиона записей как и в предыдущем случае – ~25-35 секунд.
- Выводы:
Итог
Временные таблицы InterBase 7.5 – достаточно полезная функциональность для приложений, которые формируют сложные отчеты или производят промежуточные вычисления на сервере. Однако, из-за странного поведения on commit delete временные таблицы контекста транзакции оказывается возможным использовать только в тех приложениях, которые работают не более чем с одной транзакцией единовременно, или с несколькими транзакциями но с условием запрета commit конкурирующих транзакций, пока транзакция, работающая с временной таблицей, не сделает commit. Кроме того, on commit delete приводит к накоплению мусорных записей при многопользовательской работе (рост размера БД), и к сборке мусора при первом коннекте к БД, что может быть нежелательной задержкой начала работы пользователей.
ON COMMIT PRESERVE представляется более выгодным вариантом, при котором единственной платой является более долгий disconnect приложений (если они создавали записи во временных таблицах, конечно). Вполне вероятно, чтобы избежать жалоб пользователей, disconnect приложения придется обрабатывать специально, выводя некое сообщение с просьбой подождать некоторое время.
P.S. Во время тестов временных таблиц наблюдалась самопроизвольная загрузка процессора сервером IB75 при отсутствии активности в приложениях. Причем, загрузка появлялась при определенном порядке старта и завершения транзакций, которые не содержали ни одного выполняемого оператора. Причина данного эффекта выясняется в настоящий момент.
Обсудить статью на форуме.