Re: [Firebird-devel] ODP: CORE-5921

[Vlad Khorsun]

25.09.2018 19:02, Karol Bieniaszewski wrote:
What is then SNAPSHOT_CN? 


  This is the commit number of currently used snapshot, if present.
Non-RC transactions create snapshot at own start and SNAPSHOT_CN is the CN of 
that snapshot.
RCRC transactions create snapshot when user query execution starts.
Old style RC transactions doesn't create snapshots.

> Is this last snapshot commited before my transaction start?

  For non-RC transactions - yes.


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


...


 1. Start application
 2. Start readonly transaction read commited transaction
 3. Retrive TransactinStartCN and remember it as last_retrived_data_CN


  RC transaction doesn't create snapshot for itself.


SELECT
*
FROM
NAMES N
INNER JOIN RDB$CN RCN ON RCN.TN=N.TR_ID
WHERE
RCN.TR_CN>=last_retrived_data_CN


  Hmm... probably you want here

  SELECT * FROM NAMES
   WHERE RDB$GET_TRANSACTION_CN(RDB$RECORD_VERSION) >= :lastCN


 4. Repeat point 3 in some interval to get deltas


  If you want records inserted\update since last query, you need something like

a) declare variable i application with last CN and initialize it by 0:

int64 lastCN = 0;

b) run query which returns resultset AND currently used CN as one of the 
resultset
column and put it into lastCN variable above:

  SELECT NAMES.*, RDB$GET_CONTEXT('SYSTEM', 'SNAPSHOT_CN') as Current_CN
FROM NAMES
   WHERE RDB$GET_TRANSACTION_CN(RDB$RECORD_VERSION) > :lastCN;

  Note, you must query 'SNAPSHOT_CN' context variable in the same request if you
use RCRC transaction, or you may query it separately if you use SNAPSHOT 
transaction.
In the latter case you should run query (b) in new SNAPSHOT transaction every 
time.


TR_ID field is not needed as we can instead RCN.TN=N.TR_ID use 
RCN.TN=N.RDB$RECORD_VERSION.


  Yes, it is not needed. Probably for indexing purposes, if expression index on
RDB$RECORD_VERSION will not work (i didn't tried).

But this is for optimization only. We can add index there. 


  Expression index, correct ?


And we can add to the WHERE clause „AND RCN.TR_ID>=last_retrived_OAT”


  Hmm... not sure i got this idea


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).


  I not speak about OAT (which relation to the delta i do not understand),
i'd say that we could have empty delta if there was no committed insert\update
operations since the last query.


Then asking for delta will got same resultset as previous delta which is not so 
usefull.


  If we will update our variable lastCN at (a) - we will get empty resultset, 
AFAIU


With only SNAPSHOT_CN we have same problem as with OAT.


  Please, explain

Regards,
Vlad


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

[Firebird-devel] ODP: CORE-5921

[Karol Bieniaszewski]

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