What is then SNAPSHOT_CN? Is this last snapshot commited before my transaction 
start?

Consider sample – what i try to accomplish (get deltas)

CREATE TABLE NAMES
(
  ID integer NOT NULL,
  NAME varchar(100),
  TR_ID bigint,
  CONSTRAINT INTEG_2 PRIMARY KEY (ID)
);

CREATE TABLE NAMES__DEL
(
  ID integer NOT NULL,
  CONSTRAINT INTEG_4 PRIMARY KEY (ID)
);

SET TERM ^ ;
CREATE TRIGGER TRIGGAD_NAMES FOR NAMES ACTIVE
AFTER delete POSITION 0
AS
BEGIN
  INSERT INTO NAMES__DEL(ID) VALUES(OLD.ID);
END^
SET TERM ; ^
SET TERM ^ ;
CREATE TRIGGER TRIGGAIU_NAMES FOR NAMES ACTIVE
BEFORE insert OR update POSITION 0
AS
BEGIN
  NEW.TR_ID = RDB$RECORD_VERSION;
END^
SET TERM ; ^

1. Start application
2. Start readonly transaction read commited transaction
3. Retrive TransactinStartCN and remember it as last_retrived_data_CN
SELECT
*
FROM
NAMES N
INNER JOIN RDB$CN RCN ON RCN.TN=N.TR_ID
WHERE 
RCN.TR_CN>=last_retrived_data_CN
4. Repeat point 3 in some interval to get deltas

TR_ID field is not needed as we can instead RCN.TN=N.TR_ID use 
RCN.TN=N.RDB$RECORD_VERSION.
But this is for optimization only. We can add index there. And we can add to 
the WHERE clause „AND RCN.TR_ID>=last_retrived_OAT”
This can add suport for real deltas. 
With only OAT this is not possible as OAT is not so progressive. And in the 
worst case we can have same value throught whole day (when some long running 
transaction exists).
Then asking for delta will got same resultset as previous delta which is not so 
usefull.

With only SNAPSHOT_CN we have same problem as with OAT.

Regards,
Karol Bieniaszewski
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Reply via email to