如何从火鸟数据库读取所有"last_changed"记录？

Question

我的问题有点棘手，因为这主要是一个逻辑问题。
我试图通过将所有内容读入内存来优化我的应用程序速度，但只有那些记录，自"上次读取">= 上次加载记录的最大时间戳以来发生了变化。

FirebirdSQL数据库引擎不允许直接更新"触发后"中的字段，因此它显然使用"更新或插入之前"触发器来更新字段new.last_changed = current_timestamp;

问题：

事实证明，这是一个完全错误的方法，因为这些触发器在交易开始时触发！
因此，如果存在一个事务比其他事务花费更多时间，则保存的"上次更改时间"将低于触发并在两者之间完成的短时间突发事务。
1. tr.: 13:00:01.400 .............................Commit<<此记录将被跳过！
2. tr.: 13:00.01.500......Commit<<读取数据将在这里进行。
下一次读取将>= 13:00.01.500

我试过：

重写所有触发器，因此它们在之后触发并调用UPDATE orders SET ...<<但这会导致循环的、自调用的无休止的事件。
SET_CONTEXT锁是否会干扰多行更新和嵌套触发器？(如果在同一事务中运行多个更新，我认为此方法没有任何可能有效。

这一切的常见解决方案是什么？

编辑1：

我想发生的是只读取自上次读取以来实际更改的数据库中的那些记录。为此，我需要引擎在提交后更新记录。(不是在它期间，"在中间"。
这个触发器不好，因为它会在更改的那一刻触发(不是在提交之后)：

alter trigger SYNC_ORDERS active after insert or update position 999 AS
declare variable N timestamp; 
begin
N = cast('NOW' as timestamp);
if (new.last_changed <> :N) then
update ORDERS set last_changed= :N where ID=new.ID;
end

从应用程序中，我做到了：

Query1.SQL.Text := 'SELECT * FROM orders WHERE last_changed >= ' + DateTimeToStr( latest_record );  
Query1.Open;  
latest_record := Query1.FieldByName('last_changed').asDateTime;   

.. 此代码将仅列出第 2 个事务(较早)中提交的记录，而不会列出第一个运行时间较长的事务(稍后提交)。

编辑2：

似乎我有和这里相同的问题......，但专门针对FirebirdSQL。
那里真的没有任何好的解决方案，但给了我一个想法：
- 如果我创建一个额外的表并在每个表之前记录更改超过 5 分钟怎么办？
- 在每个SQL查询之前，首先我会要求该表中的任何更改，通过ID grow排序！
- 删除超过 23 小时的行

ID  TableID  Changed
===========================
1   5   2019.11.27 19:36:21
2   5   2019.11.27 19:31:19

编辑3：

正如Arioch已经建议的那样，一种解决方案是：

• 创建一个填充在每BEFORE INSERT OR UPDATE上的"记录表" 由每个表触发
• 并更新它的"last_changed"序列 通过ON TRANSACTION COMMIT触发器

但是，不会...

更好的方法？

• 向每个表添加 1-1last_sequence INT64 DEFAULT NULL列
• 创建全局生成器LAST_GEN
• 在ON TRANSACTION COMMIT触发器内使用 gen_id(LAST_GEN，1) 更新每个表的每个 NULL 行
• 在每个BEFORE INSERT OR UPDATE触发器上再次设置为 NULL

所以基本上将记录的last_sequence列切换为：
NULL > 1 > NULL > 34...每次修改时。
这样我：

• 不必用日志数据填充数据库，
• 我可以直接用WHERE last_sequence>1;查询表.
• 无需先预查询"记录表"。

我只是害怕：如果ON TRANSACTION COMMIT触发器尝试更新last_sequence字段，而第 2 个事务的 ON BEFORE 触发器锁定(另一个表的)记录，会发生什么？
这真的会发生吗？

Answer 1

最终的解决方案基于以下想法：

1. 每个表的BEFORE INSERT OR UPDATE触发器可以推送一个事务时间：RDB$SET_CONTEXT('USER_TRANSACTION', 'table31', current_timestamp);
2. 全局ON TRANSACTION COMMIT触发器可以将序列 + 时间插入到"日志记录表"中(如果收到此类上下文)。
3. 它甚至可以处理"夏令时更改"和"间隔"，通过仅记录"大时差"(如>=1分钟)来减少记录量。
4. 存储过程可以简化和加快每个查询的"LAST_QUERY_TIME"计算。

例：

1.)

create trigger ORDERS_BI active before insert or update position 0 AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_ORDERS,1);
RDB$SET_CONTEXT('USER_TRANSACTION', 'orders_table', current_timestamp);  
END

2， 3.)

create trigger TRG_SYNC_AFTER_COMMIT ACTIVE ON transaction commit POSITION 1 as 
declare variable N TIMESTAMP;
declare variable T VARCHAR(255);
begin
N = cast('NOW' as timestamp);
T = RDB$GET_CONTEXT('USER_TRANSACTION', 'orders_table');
if (:T is not null) then begin
if (:N < :T) then T = :N; --system time changed eg.: daylight saving" -1 hour
if (datediff(second from :T to :N) > 60 ) then --more than 1min. passed
insert into "SYNC_PAST_TIMES" (ID, TABLE_NUMBER, TRG_START, SYNC_TIME, C_USER)
values (GEN_ID(GEN_SYNC_PAST_TIMES, 1), 31, cast(:T as timestamp), :N, CURRENT_USER);
end;  
-- other tables too:
T = RDB$GET_CONTEXT('USER_TRANSACTION', 'details_table');
-- ...
when any do EXIT;
end 

编辑1：

借助存储过程，可以加快从我们的SYNC_PAST_TIMES表中读出"上次更改"值的速度。从逻辑上讲，您必须在内存中存储 IDPT_ID+ 程序中的时间PT_TM，以便为每个表调用它。

CREATE PROCEDURE SP_LAST_MODIF_TIME (
TABLE_NUMBER SM_INT,
LAST_PASTTIME_ID BG_INT,
LAST_PASTTIME TIMESTAMP)
RETURNS (
PT_ID BG_INT,
PT_TM TIMESTAMP)
AS
declare variable TEMP_TIME TIMESTAMP;
declare variable TBL       SMALLINT;
begin
PT_TM   = :LAST_PASTTIME;
FOR SELECT p.ID, p.SYNC_TIME, p.TABLA FROM SYNC_PAST_TIMES p WHERE (p.ID > :LAST_PASTTIME_ID)
ORDER by p.ID ASC
INTO PT_ID, TEMP_TIME, TBL DO --the PT_ID gets an increasing value immediately
begin
if (:TBL = :TABLE_NUMBER) then
if (:TEMP_TIME< :MI_TIME) then 
PT_TM = :TEMP_TIME; --searching for the smallest
end

if (:PT_ID IS NULL) then begin
PT_ID  = :LAST_PASTTIME_ID;
PT_TM = :LAST_PASTTIME;
end

suspend;
END

您可以通过在选择中包含以下内容来使用此过程，使用WITH .. AS格式：

with UTLS as (select first 1 PT_ID, PT_TM from SP_LAST_MODIF_TIME (55, -- TABLE_NUMBER
0, '1899.12.30 00:00:06.000') ) -- last PT_ID, PT_TM from your APP 
select first 1000 u.PT_ID, current_timestamp as NOWWW, r.*
from UTLS u, "Orders" r
where (r.SYNC_TIME >= u.PT_TM);

必须使用FIRST 1000来防止在一次更改所有值时读取整个表。升级 SQL、添加新列等会使表的所有行SYNC_TIME同时更改为NOW。
您可以按表单独调整它，就像监视更改的秒间隔一样。在你的APP上加一张支票，如何处理，如果新数据一次达到1000行......