无法在执行块中删除先前修改的新表

Question

我对FB数据库及其微妙之处不是很熟悉。 在脚本执行时，出现问题：

EXECUTE ibeblock
AS
BEGIN
-- 1. Create temporary table
execute statement 'recreate GLOBAL TEMPORARY table TMPTBL (ID bigint) /*on commit delete rows*/;';
commit;
-- 2. dummy fill of temporary table
insert into tmptbl (ID)
values (0xFE);
commit; -- not necessary
-- 3. perform some actions...
-- 4. Delete temporary table
execute statement 'drop table TMPTBL;';
commit; -- FAILURE!
END

脚本的想法是原始的：1)创建临时表;2)填写记录;3) 使用填充的记录对其他数据库对象执行操作;4)放置临时表。 对于模拟，步骤 3 是无用的(跳过)。步骤 4 会导致提交时出错："此操作未为系统表定义。元数据更新失败。对象表 "TMPTBL" 正在使用中。 触发器或约束均不应用于表。显然，不应该有任何锁定临时表。

请帮助解决。希望我错过了什么。 附言：FB 2.5，IBExpert 2017.12.13.1 用作数据库管理工具

Answer 1

您的代码存在许多问题：

1. 全局临时表旨在作为永久对象，它只是临时内容(事务或连接持续时间)。因此，通常您会创建一个全局临时表一次，而不是删除它，而是重用其定义。

2. 虽然从技术上讲，您可以使用execute statement执行 DDL，但您不应该这样做，也不能保证它能正常工作。您的代码是行不通的其中一个例子。

   这里的问题是，您正在尝试将表放在使用它的同一事务中(尽管说实话，我很惊讶插入甚至有效，因为通常您无法插入到在同一事务中创建的表中)。

   您在TMPTBL上执行的插入会将表标记为正在使用中，并且如果事务尚未提交，则无法删除该表：它正在使用中。

3. 你不应该在 PSQL 代码中调用 commit (老实说，我认为这甚至是不可能的)。

简而言之，您需要重新考虑如何使用全局临时表：定义一次，并且不要使用execute statement来创建它，而是单独创建它。

如果您确实想要创建和删除它并且不保留全局临时表的定义，请在execute block之前创建它，提交，然后是execute block(仅包含插入和"执行一些操作")，提交，然后删除它(并提交)。

或者，您可以使用execute statement ... with autonomous transaction执行创建，插入和在另一个execute statement ... with autonomous transaction中"执行一些操作"，最后在另一个execute statement ... with autonomous transaction中下降。但是，这会使您的代码非常脆弱，这不是推荐的方法。

Answer 2

DevOps 人员再次迫使我找到强大的解决方案来提供数据库结构升级。要求：安全地组合DDL和DML语句;能够创建临时表(用于大量选择);不留下垃圾。当然，升级是在单个连接中处理的。

参考马克给出的线索，进行了更深入的见解和大量的实验。

这是真正解决的模板文件脚本(使用了isql本机实用程序)：

SET TERM #;
-- 1. Create temporary table
EXECUTE BLOCK
AS
BEGIN
execute statement 'recreate GLOBAL TEMPORARY table TMPTBL (ID bigint) /*on commit preserve rows*/;';
END#
commit#
-- Data manipulations
EXECUTE BLOCK
AS
declare xid bigint;
BEGIN
-- 2. dummy fill of temporary table
begin
insert into TMPTBL (ID) values (0xFE);
end
-- 3. perform some actions...
for
select tt.ID
from TMPTBL tt
into :xid
do
begin
-- use :xid var
end
END#
commit#

-- 4. Delete temporary table
EXECUTE BLOCK
AS
BEGIN
execute statement 'drop table TMPTBL;';
END#
commit#
SET TERM ;#

可能对某人有用。 该死的，火鸟确实发疯了！