WL#4770: Online Backup: Test firing of triggers and events during backup/restore

Affects: Server-6.0   —   Status: Complete   —   Priority: Medium

Create tests that verify that triggers and events behave correctly during backup
and restore.

Backup is a read-only operation and shall not fire triggers itself. But
modifying statements in other sessions shall fire the appropriate triggers as
usual during an online backup operation, regardless if the trigger influences a
table in backup or not. However, triggers that modify tables in backup shall be
blocked during the validity point, and they shall not influence the backup
contents when completing after the validity point. Events likewise.

Restore shall not fire triggers itself. It tries to restore tables to a former
state. This requires inserts into the tables in restore. These inserts shall not
fire triggers. But modifying statements in other sessions shall fire the
appropriate triggers as usual during a restore operation. However, triggers that
modify tables in restore shall be blocked during restore. Events likewise.

Also test that triggers on restored database objects work as expected after
restore (already done).
In this worklog we will be testing the following test scenarios,

PART 1: Triggers and Backup
---------------------------

1. Perform backup on a database. Fire trigger from another database which
influences a table where both trigger and table are not included in backup.

Fire the trigger at different phases of validity point (before, during,
after). Note that since the database is not included in the backup,
there will be no influence in the backup image. Trigger that will fired
before and after validity point shall work and will be blocked during
validity point.

2. Perform backup on a database. Fire trigger from another database (not
included in backup) which influences a table that is included in backup image.

Trigger will be fired at different phases of validity point.
Result of trigger that is fired before validity point will be included
in the backup image.
Result of trigger fired during and after validity point will not be
included in the backup image.

3. Perform backup on a database. Fire trigger from this database (included in
backup) which influences a table that is not included in backup image.

Trigger will be fired at different phases of validity point.
Result of trigger that is fired before validity point will not be included
in the backup image.
Result of trigger fired during and after validity point will not be
included in the backup image.

4. Perform backup on a database. Fire trigger from this database (included in
backup) which influences a table that is included in backup image.

Trigger will be fired at different phases of validity point.
Result of trigger that is fired before validity point will be included
in the backup image.
Result of trigger fired during and after validity point will not be
included in the backup image.


PART 2: Triggers and Restore
----------------------------

1. Perform restore of a database. Fire trigger from another database which
influences a table where both trigger and table are not included in backup.

Result of trigger shall be present in target table after restore.

2. Perform restore of a database. Fire trigger from another database (not
included in backup) which influences a table that is included in backup image.

Result of trigger shall be present in target table after restore.

3. Perform restore of a database. Fire trigger from this database (included in
backup) which influences a table that is not included in backup image.

Result of trigger shall be present in target table after restore.

4. Perform restore of a database. Fire trigger from this database (included in
backup) which influences a table that is included in backup image.

Result of trigger shall be present in target table after restore.


PART 3: Events and Backup
-------------------------

1. Perform backup on a database. Execute event from another database which
influences a table where both event and table are not included in backup.

Execute the event at different phases of validity point (before, during,
after). Note that since the database is not included in the backup,
there will be no influence in the backup image. Events that will execute
before and after validity point shall work and will be blocked during
validity point.

2. Perform backup on a database. Execute event from this database (included in
backup) which influences a table that is included in backup image.

Execute the event at different phases of validity point.
Result of event that is executed before validity point will be included
in the backup image.
Result of event executed during and after validity point will not be
included in the backup image.

PART 4: Events and Restore
--------------------------

1. Perform restore of a database. Execute event from another database which
influences a table where both trigger and table are not included in backup.

Result of event shall be present in target table after restore.

2. Perform restore of a database. Execute event from this database (included in
backup) which influences a table that is included in backup image.

Result of event shall be present in target table after restore.

The new tests will go into the existing test case
backup_triggers_and_events. It contains just one test, which is slightly
different but still similar enough to bundle it with the new tests.

Triggers
========

Two databases shall be created:
1. bup_trigevt_backup - to be included in BACKUP/RESTORE,
2. bup_trigevt_indept - independent (not included in backup).

Three tables shall be created per database:
1. t1_same - shall have INSERT, UPDATE, and DELETE triggers,
   which affect the third table of the same database.
2. t2_othr - shall have INSERT, UPDATE, and DELETE triggers,
   which affect the third table of the other database.
3. t3_vict - "victim" table to be affected by other tables's triggers.

Triggers and BACKUP
-------------------

To avoid a giant amount of tests, combine several tests into one. use
the INSERT triggers before a validity point, UPDATE triggers at a
validity point, and DELETE triggers after a validity point within one
test.

Furthermore, the four backup tests from the HLS can be combined by
running the INSERT, UPDATE, and DELETE into each of the trigger-bearing
tables at the respective states within one test.

There shall be extra connections to run these statements:
1. UPDATE bup_trigevt_backup.t1_same
2. UPDATE bup_trigevt_backup.t2_othr
3. UPDATE bup_trigevt_indept.t2_othr
All these statements affect tables that are included in BACKUP.
All these statements run at the validity point and thus block.

There shall be a RESTORE, unaffected by triggers, to show, what's in the
tables.

Triggers and RESTORE
--------------------

There shall be another RESTORE, during which one statement per table
shall be executed. The same three statements will block again, so the
three extra connections are required again.

Events
======

Two databases shall be created:
1. bup_trigevt_backup - to be included in BACKUP/RESTORE,
2. bup_trigevt_indept - independent (not included in backup).

One table shall be created per database.

One event shall be created per database, affecting the table in the same
database. The events shall execute every second.

Events and BACKUP
-----------------

BACKUP shall wait before, at, and after the validity point until the
event on its database has executed in each phase.

There shall be a RESTORE, unaffected by events, to show, what's in the
tables.

Events and RESTORE
------------------

There shall be another RESTORE, during which the events shall be executed.