recreate exception ex_bad_error_encountered 'at least one forbidden error found, gdscode=@1';
commit;
create or alter procedure p_tmx_hadle_doc as begin end;
create or alter procedure p_tmx_add_doc as begin end;
create or alter function fn_halt_test returns int as begin end;
set term ^;
execute block as
begin
  begin execute statement 'drop trigger tdx_bi'; when any do begin end end
  begin execute statement 'drop trigger tdx_bud'; when any do begin end end
  begin execute statement 'drop trigger tmx_bud'; when any do begin end end
end
^set term ;^
commit;

recreate table tq1(
    id int primary key using index tq1_pk,
    pid int,
    x int default 1
);
create index tq1_pid on tq1(pid);
commit;

recreate table tq2(
    id int primary key using index tq2_pk,
    pid int,
    x int default 1
);
create index tq2_pid on tq2(pid);
commit;


recreate table tdx(
    id int primary key using index tdx_pk,
    pid int,
    op int not null check(op in(1,2)),
    qty int not null check(qty>0)
);
recreate table tlog(
    sel_id int,
    unit varchar(31),
    handle_mode varchar(50),
    fb_gdscode int,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
create index tlog_fb_gdscode on tlog(fb_gdscode);
commit;

recreate table tmx(
    id int primary key using index tmx_pk,
    op int not null check(op in(1,2)),
    unique(id, op) using index tmx_unq_for_tdx_fk
);
create descending index tmx_id_desc on tmx(id);
commit;

recreate sequence g;
commit;

alter table tdx add constraint tdx_fk
    foreign key (pid, op) references tmx(id, op)
    on delete cascade
    on update cascade;
commit;

set term ^;
create or alter trigger tdx_bi for tdx active before insert as
    declare v_qty int;
    declare v_stt varchar(128);
begin
    -- Multiply rows in either in TQ1 or TQ2 - depending on new.op value
    -- (which is generated randomly and can be 1 or 2).
    -- Number of created rows = int values of new.qty,
    --  which is being inserted in tdx.qty field:
    v_stt = 'insert into ' || iif( new.op=1, 'tq1', 'tq2' ) || '(id, pid) values(:a_id, :a_pid)';
    v_qty = new.qty;
    while (v_qty>0) do
    begin
        execute statement (v_stt) ( a_id := gen_id(g,1), a_pid := new.pid );
        v_qty = v_qty - 1;
    end
end
^

create or alter trigger tdx_bud for tdx active before update or delete as
    declare v_sel_id int;
    declare v_handle_mode varchar(50);
begin
    if (deleting) then  -- remove all rows in TQ1 and TQ2 which have pid = old.pid
        begin
            v_sel_id=old.pid;
            v_handle_mode='del in tq1'; delete from tq1 q where q.pid = old.pid;
            v_handle_mode='del in tq2'; delete from tq2 q where q.pid = old.pid;
        end
    else -- updating: MOVE all rows related to document, with DELETION in SOURCE
        begin
            v_sel_id=new.pid;
            if ( new.op = 1 ) then begin
              -- SOURCE = TQ2, TARGET = TQ1
              v_handle_mode='ins tq1, del in tq2 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq2 where pid = new.pid as cursor c
              do begin
                  v_handle_mode='try: ins tq1, del in tq2, id='||c.id;
                  insert into tq1(id, pid) values (c.id, c.pid);
                  delete from tq2 where current of c;
              end
            end
            else begin
              -- SOURCE = TQ1, TARGET = TQ2
              v_handle_mode='ins tq2, del in tq1 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq1 where pid = new.pid as cursor c
              do begin
                  v_handle_mode='try: ins tq2, del in tq1, id='||c.id;
                  insert into tq2(id, pid) values (c.id, c.pid);
                  delete from tq1 where current of c;
              end
            end
        end -- deleting or updating branch
when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :v_sel_id, 'tdx_bud', :v_handle_mode, gdscode);

      exception;

    end
end

^set term ;^
commit;
--------------------------------------------------------------------------------
set term ^;
create or alter procedure p_tmx_add_doc(m_rows int) returns(sel_id int)
as
    declare v_op int;
    declare i int;
    declare const_qty int = 100;
begin
    -- NB: trigger tdx_bi will "multiply" rows in either TQ1 or TQ2,
    -- number of added rows = new.qty:
    insert into tmx(id, op) values( gen_id(g,1), iif(rand()<0.5, 1, 2))
    returning id, op into sel_id, v_op;
    i=0;
    while (i < m_rows) do
    begin
        insert into tdx(id, pid, op, qty) values( gen_id(g,1), :sel_id, :v_op, 1+rand()*(:const_qty-1) );
        i=i+1;
    end
    suspend;
end
^

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.25 then add new "doc"
    -- else if :r < 0.75 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r < 0.25) then
        begin
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);
            select id from tmx where id >= :v_rnd_id order by id rows 1 into sel_id;
            if ( r < 0.75 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id;
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id;
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end

    suspend;

when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode);

      exception;

    end
end

^

create or alter function fn_halt_test returns int
as
    declare result int;
begin
    -- Used in .sql to immediatelly stop test (via set bail on) if one of the
    -- following errors registered in tlog: PK / UQ / FK violation.
    select first 1 g.fb_gdscode
    from tlog g
                       where g.fb_gdscode
                       in (
                                 335544665 -- unique_key_violation (violation of PRIMARY or UNIQUE KEY constraint "T1_XY" on table "T1")
                                ,335544349 -- no_dup (attempt to store duplicate value (visible to active transactions) in unique index "T2_XY") - without UNQ constraint
                                ,335544466 -- violation of FOREIGN KEY constraint @1 on table @2
                                ,335544838 -- Foreign key reference target does not exist (when attempt to ins/upd in DETAIL table FK-field with value for which parent ID has been changed or deleted - even in uncommitted concurrent Tx)
                                ,335544839 -- Foreign key references are present for the record  (when attempt to upd/del in PARENT table PK-field and rows in DETAIL (no-cascaded!) exists for old value)
                           ) -- in
    into result;
    return result;
end
^
set term ;^
commit;

------------------------------- initial data filling ---------------------------
set term ^;
execute block as
    declare n int = 500;
    declare const_qty int = 100;
    declare i int;
begin
    while (n>0) do
    begin
        execute procedure p_tmx_add_doc(1 + rand()*(:const_qty-1)) returning_values i;
        n = n-1;
    end
end
^set term ;^
commit;

set term ^;
execute block as
    declare n int = -1;
begin
    n=fn_halt_test();
    if ( n > 0 ) then
      exception ex_bad_error_encountered using (:n);
end
^ set term ;^
set bail off;

commit; set transaction snapshot no wait;
set term ^;
execute block returns(trn_id int, dts varchar(12), sel_id int, result varchar(3))
as
  declare n int = 10;
begin
  trn_id = current_transaction;
  while ( n>0 ) do begin
    select sel_id, result from p_tmx_hadle_doc into sel_id, result;
    dts=substring(cast(cast('now' as timestamp) as varchar(24)) from 12 for 12);
    suspend;
    n=n-1;
  end
end
^ set term ;^
--------------------

setlocal enabledelayedexpansion enableextensions

@rem path to ISQL utility, without trailing backslash:
set fbc=C:\MIX\firebird\fb25\bin

@rem path where temp logs will be created:
set tmpdir=c:\temp\tq

md %tmpdir% 2>nul
del %tmpdir%\tq_run_*.log 2>nul
del %tmpdir%\tq_run_*.err 2>nul
if not .%1.==.. set /a winq=%1
if .%winq%.==.0. set winq=10
@echo off
for /l %%i in (1, 1, %winq%) do (
  set str=%fbc%\isql 192.168.0.220/3333:/var/db/fb30/tq.fdb -n -i tq_run.sql 1^>%tmpdir%\tq_run_%%i.log 2^>%tmpdir%\tq_run_%%i.err
  echo str = !str!
  start /min cmd /c !str!
)
goto end
:end

recreate exception ex_bad_error_encountered 'at least one forbidden error found, gdscode=@1';
commit;
create or alter procedure p_tmx_hadle_doc as begin end;
create or alter procedure p_tmx_add_doc as begin end;
create or alter function fn_halt_test returns int as begin end;
set term ^;
execute block as
begin
  begin execute statement 'drop trigger tdx_bi'; when any do begin end end
  begin execute statement 'drop trigger tdx_bud'; when any do begin end end
  begin execute statement 'drop trigger tmx_bud'; when any do begin end end
end
^set term ;^
commit;

recreate table tq1(
    id int primary key using index tq1_pk,
    pid int,
    x int default 1
);
-- replaced with FK: create index tq1_pid on tq1(pid);
commit;

recreate table tq2(
    id int primary key using index tq2_pk,
    pid int,
    x int default 1
);
-- replaced with FK: create index tq2_pid on tq2(pid);
commit;

recreate table tdx( -- detail for `tmx` table
    id int primary key using index tdx_pk,
    pid int,
    op int not null check(op in(1,2)),
    qty int not null check(qty>0)
);
commit;

recreate table tmx(
    id int primary key using index tmx_pk,
    op int not null check(op in(1,2)),
    unique(id, op) using index tmx_unq_for_tdx_fk
);
create descending index tmx_id_desc on tmx(id);
commit;

recreate table tlog(
    sel_id int,
    unit varchar(31),
    handle_mode varchar(50),
    fb_gdscode int,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
create index tlog_fb_gdscode on tlog(fb_gdscode);
commit;

recreate sequence g;
commit;

-- FK constraints:
alter table tdx add constraint tdx_fk
    foreign key (pid, op) references tmx(id, op)
    on delete cascade
    on update cascade;
commit;
 alter table tq1 add constraint tq1_fk
    foreign key (pid) references tdx(id);
alter table tq2 add constraint tq2_fk
    foreign key (pid) references tdx(id); 
commit;

-- Triggers:
set term ^;
create or alter trigger tdx_ a i for tdx active  after  insert as 
    -- NB: changed 'before' to 'after' due to FK from TQ1 & TQ2 ==> TDX
    declare v_qty int;
    declare v_stt varchar(128);
begin
    -- Multiply rows in either in TQ1 or TQ2 - depending on new.op value
    -- (which is generated randomly and can be 1 or 2).
    -- Number of created rows = int values of new.qty,
    -- which is being inserted in tdx.qty field:
    v_stt = 'insert into ' || iif( new.op=1, 'tq1', 'tq2' ) || '(id, pid) values(:a_id, :a_pid)';
    v_qty = new.qty;
    while (v_qty>0) do
    begin
        execute statement (v_stt) ( a_id := gen_id(g,1), a_pid := new .id  );
        v_qty = v_qty - 1;
    end
end
^

create or alter trigger tdx_bud for  tdx  active before update or delete as
    declare v_sel_id int;
    declare v_handle_mode varchar(50);
begin
    if (deleting) then -- remove all rows in TQ1 and TQ2 which have pid = old.pid
        begin
            v_sel_id=old.pid; -- to be logged in case of exc.
            v_handle_mode='del in tq1, pid='||old.id; -- to be logged in case of exc.
            delete from tq1 q where q.pid = old .id ;

            v_handle_mode='del in tq2, pid='||old.id; -- to be logged in case of exc.
            delete from tq2 q where q.pid = old .id ;
        end
    else -- updating: MOVE all rows related to document, with DELETION in SOURCE
        begin
            v_sel_id=new.pid; -- to be logged in case of exc.
            if ( new.op = 1 ) then begin
              -- SOURCE = TQ2, TARGET = TQ1
              --v_handle_mode='ins tq1, del in tq2 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq2 where pid = new .id  as cursor c
              do begin
                  v_handle_mode='try ins tq1, del in tq2, id='||c.id; -- to be logged in case of exc.
                  insert into tq1(id, pid) values (c.id, c.pid); -- ::: NB ::: PK  c.id - does NOT change!
                  delete from tq2 where current of c;
              end
            end
            else begin
              -- SOURCE = TQ1, TARGET = TQ2
              --v_handle_mode='ins tq2, del in tq1 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq1 where pid = new .id  as cursor c
              do begin
                  v_handle_mode='try ins tq2, del in tq1, id='||c.id; -- to be logged in case of exc.
                  insert into tq2(id, pid) values (c.id, c.pid);  -- ::: NB ::: PK  c.id - does NOT change!
                  delete from tq1 where current of c;
              end
            end
        end -- deleting or updating branch
when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :v_sel_id, 'tdx_bud', :v_handle_mode, gdscode);

      exception;

    end
end
^
set term ;^
commit;
--------------------------------------------------------------------------------
set term ^;
create or alter procedure p_tmx_add_doc(m_rows int) returns(sel_id int)
as
    declare v_op int;
    declare i int;
    declare const_qty int = 100;
begin
    -- Creates new "doc" and filling it with random number of rows:
    insert into tmx(id, op) values( gen_id(g,1), iif(rand()<0.5, 1, 2))
    returning id, op into sel_id, v_op;
    i=0;
    while (i < m_rows) do
    begin
        -- NB: trigger tdx_bi will "multiply" rows in either TQ1 or TQ2,
        -- number of added rows = new.qty:
        insert into tdx(id, pid, op, qty) values( gen_id(g,1), :sel_id, :v_op, 1+rand()*(:const_qty-1) );
        i=i+1;
    end
    suspend;
end
^

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.10 then add new "doc"
    -- else if :r < 0.90 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r <  0.10  ) then
        begin
          v_handle_mode='before call p_tmx_add_doc'; -- to be logged in case of exc.
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);
            select id from tmx where id >= :v_rnd_id order by id rows 1 into sel_id;
            if ( r <  0.90  ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id; -- to be logged in case of exc.
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id; -- to be logged in case of exc.
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end

    suspend;

when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode);

      exception;

    end
end

^

create or alter function fn_halt_test returns int
as
    declare result int;
begin
    -- Used in .sql to immediatelly stop test (via set bail on) if one of the
    -- following errors registered in tlog: PK / UQ / FK violation.
    select first 1 g.fb_gdscode
    from tlog g
                       where g.fb_gdscode
                       in (
                                 335544665 -- unique_key_violation (violation of PRIMARY or UNIQUE KEY constraint "T1_XY" on table "T1")
                                ,335544349 -- no_dup (attempt to store duplicate value (visible to active transactions) in unique index "T2_XY") - without UNQ constraint
                                ,335544466 -- violation of FOREIGN KEY constraint @1 on table @2
                                ,335544838 -- Foreign key reference target does not exist (when attempt to ins/upd in DETAIL table FK-field with value for which parent ID has been changed or deleted - even in uncommitted concurrent Tx)
                                ,335544839 -- Foreign key references are present for the record  (when attempt to upd/del in PARENT table PK-field and rows in DETAIL (no-cascaded!) exists for old value)
                           ) -- in
    into result;
    return result;
end
^
set term ;^
commit;

------------------------------- initial data filling ---------------------------
set term ^;
execute block as
    declare n int = 100;
    declare const_qty int = 100;
    declare i int;
begin
    while (n>0) do
    begin
        execute procedure p_tmx_add_doc(1 + rand()*(:const_qty-1)) returning_values i;
        n = n-1;
    end
end
^set term ;^
commit;

recreate exception ex_bad_error_encountered 'at least one forbidden error found, gdscode=@1';
commit;
create or alter procedure p_tmx_hadle_doc as begin end;
create or alter procedure p_tmx_add_doc as begin end;
create or alter function fn_halt_test returns int as begin end;
set term ^;
execute block as
begin
  begin execute statement 'drop trigger tdx_ai'; when any do begin end end
  begin execute statement 'drop trigger tdx_bud'; when any do begin end end
  begin execute statement 'drop trigger tmx_bud'; when any do begin end end
end
^set term ;^
commit;

recreate table tq1(
    id int primary key using index tq1_pk,
    pid int,
    x int default 1
);
-- replaced with FK: create index tq1_pid on tq1(pid);
commit;

recreate table tq2(
    id int primary key using index tq2_pk,
    pid int,
    x int default 1
);
-- replaced with FK: create index tq2_pid on tq2(pid);
commit;

recreate table tdx( -- detail for `tmx` table
    id int primary key using index tdx_pk,
    pid int,
    op int not null check(op in(1,2)),
    qty int not null check(qty>0)
);
commit;

recreate table tmx(
    id int primary key using index tmx_pk,
    op int not null check(op in(1,2)),
    unique(id, op) using index tmx_unq_for_tdx_fk
);
create descending index tmx_id_desc on tmx(id);
commit;

recreate table tlog(
    sel_id int,
    unit varchar(31),
    handle_mode varchar(50),
    fb_gdscode int,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
create index tlog_fb_gdscode on tlog(fb_gdscode);
commit;

recreate sequence g;
commit;

-- FK constraints:
alter table tdx add constraint tdx_fk
    foreign key (pid, op) references tmx(id, op)
    on delete cascade
    on update cascade;
commit;
alter table tq1 add constraint tq1_fk
    foreign key (pid) references tdx(id);
alter table tq2 add constraint tq2_fk
    foreign key (pid) references tdx(id);
commit;

-- Triggers:
set term ^;
create or alter trigger tdx_ai for tdx active after insert as
    declare v_qty int;
    declare v_stt varchar(128);
begin
    -- Multiply rows in either in TQ1 or TQ2 - depending on new.op value
    -- (which is generated randomly and can be 1 or 2).
    -- Number of created rows = int values of new.qty,
    -- which is being inserted in tdx.qty field:
    v_stt = 'insert into ' || iif( new.op=1, 'tq1', 'tq2' ) || '(id, pid) values(:a_id, :a_pid)';
    v_qty = new.qty;
    while (v_qty>0) do
    begin
        execute statement (v_stt) ( a_id := gen_id(g,1), a_pid := new.id );
        v_qty = v_qty - 1;
    end
end
^

create or alter trigger tdx_bud for tdx active before update or delete as
    declare v_sel_id int;
    declare v_handle_mode varchar(50);
begin
    if (deleting) then -- remove all rows in TQ1 and TQ2 which have pid = old.pid
        begin
            v_sel_id=old.pid; -- to be logged in case of exc.
            v_handle_mode='del in tq1, pid='||old.id; -- to be logged in case of exc.
            delete from tq1 q where q.pid = old.id;

            v_handle_mode='del in tq2, pid='||old.id; -- to be logged in case of exc.
            delete from tq2 q where q.pid = old.id;
        end
    else -- updating: MOVE all rows related to document, with DELETION in SOURCE
        begin
            v_sel_id=new.pid; -- to be logged in case of exc.
            if ( new.op = 1 ) then begin
              -- SOURCE = TQ2, TARGET = TQ1
              --v_handle_mode='ins tq1, del in tq2 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq2 where pid = new.id as cursor c
              do begin
                  v_handle_mode='try ins tq1, del in tq2, id='||c.id; -- to be logged in case of exc.
                  insert into tq1(id, pid) values (c.id, c.pid);
                  delete from tq2 where current of c;
              end
            end
            else begin
              -- SOURCE = TQ1, TARGET = TQ2
              --v_handle_mode='ins tq2, del in tq1 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq1 where pid = new.id as cursor c
              do begin
                  v_handle_mode='try ins tq2, del in tq1, id='||c.id; -- to be logged in case of exc.
                  insert into tq2(id, pid) values (c.id, c.pid);
                  delete from tq1 where current of c;
              end
            end
        end -- deleting or updating branch
when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :v_sel_id, 'tdx_bud', :v_handle_mode, gdscode);

      exception;

    end
end
^
create or alter trigger  tmx_bud for tmx  active before update or delete as
  declare v_id int;
begin
    for
        select id from tdx d
        where d.pid = old.id
        for update with lock
        into v_id
    do begin
        -- nop! just ensure that we really lock all detail records --
    end
end
^
set term ;^
commit;
--------------------------------------------------------------------------------
set term ^;
create or alter procedure p_tmx_add_doc(m_rows int) returns(sel_id int)
as
    declare v_op int;
    declare i int;
    declare const_qty int = 100;
begin
    -- Creates new "doc" and filling it with random number of rows:
    insert into tmx(id, op) values( gen_id(g,1), iif(rand()<0.5, 1, 2))
    returning id, op into sel_id, v_op;
    i=0;
    while (i < m_rows) do
    begin
        -- NB: trigger tdx_bi will "multiply" rows in either TQ1 or TQ2,
        -- number of added rows = new.qty:
        insert into tdx(id, pid, op, qty) values( gen_id(g,1), :sel_id, :v_op, 1+rand()*(:const_qty-1) );
        i=i+1;
    end
    suspend;
end
^

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.10 then add new "doc"
    -- else if :r < 0.90 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r < 0.10) then
        begin
          v_handle_mode='before call p_tmx_add_doc'; -- to be logged in case of exc.
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);
            select id from tmx where id >= :v_rnd_id order by id rows 1 into sel_id;
            if ( r < 0.90 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id; -- to be logged in case of exc.
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id; -- to be logged in case of exc.
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end

    suspend;

when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode);

      exception;

    end
end

^

create or alter function fn_halt_test returns int
as
    declare result int;
begin
    -- Used in .sql to immediatelly stop test (via set bail on) if one of the
    -- following errors registered in tlog: PK / UQ / FK violation.
    select first 1 g.fb_gdscode
    from tlog g
                       where g.fb_gdscode
                       in (
                                 335544665 -- unique_key_violation (violation of PRIMARY or UNIQUE KEY constraint "T1_XY" on table "T1")
                                ,335544349 -- no_dup (attempt to store duplicate value (visible to active transactions) in unique index "T2_XY") - without UNQ constraint
                                ,335544466 -- violation of FOREIGN KEY constraint @1 on table @2
                                ,335544838 -- Foreign key reference target does not exist (when attempt to ins/upd in DETAIL table FK-field with value for which parent ID has been changed or deleted - even in uncommitted concurrent Tx)
                                ,335544839 -- Foreign key references are present for the record  (when attempt to upd/del in PARENT table PK-field and rows in DETAIL (no-cascaded!) exists for old value)
                           ) -- in
    into result;
    return result;
end
^
set term ;^
commit;

------------------------------- initial data filling ---------------------------
set term ^;
execute block as
    declare n int = 30;
    declare const_qty int = 100;
    declare i int;
begin
    while (n>0) do
    begin
        execute procedure p_tmx_add_doc(1 + rand()*(:const_qty-1)) returning_values i;
        n = n-1;
    end
end
^set term ;^
commit;

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.25 then add new "doc"
    -- else if :r < 0.75 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r < 0.25) then
        begin
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);

            select m.id
            from tmx m
            where m.id >= :v_rnd_id
                   and not exists(select * from gtt_trn_log g where g.sel_id = m.id) 
            order by id rows 1
            FOR UPDATE WITH LOCK
            into sel_id;

            if ( r < 0.75 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id;
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id;
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end

     insert into gtt_trn_log(sel_id) values(:sel_id); 

    suspend;

when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode);

      exception;

    end
end