Appendix A. Supplementary Information

In this Appendix are topics that developers may wish to refer to, to enhance understanding of features or changes.

A.1. The RDB$VALID_BLR Field

The field RDB$VALID_BLR in system tables RDB$PROCEDURES, RDB$FUNCTIONS and RDB$TRIGGERS signal possible invalidation of a PSQL module after alteration of a domain or table column on which the module depends. RDB$VALID_BLR is set to 0 for any procedure or trigger whose code is made invalid by such a change.

The field RDB$VALID_BODY_FLAG in RDB$PACKAGES serves a similar purpose for packages.

A.1.1. How Invalidation Works

In PSQL modules, dependencies arise on the definitions of table columns accessed and also on any parameter or variable that has been defined in the module using the TYPE OF clause.

After the engine has altered any domain, including the implicit domains created internally behind column definitions and output parameters, the engine internally recompiles all of its dependencies.

Any module that fails to recompile because of an incompatibility arising from a domain change is marked as invalid (invalidated by setting the RDB$VALID_BLR in its system record (in RDB$PROCEDURES, RDB$FUNCTIONS or RDB$TRIGGERS, as appropriate) to zero.

Revalidation (setting RDB$VALID_BLR to 1) occurs when

  1. the domain is altered again and the new definition is compatible with the previously invalidated module definition, or

  2. the previously invalidated module is altered to match the new domain definition

The following query will find the modules that depend on a specific domain and report the state of their RDB$VALID_BLR fields:

   |SELECT * FROM (
   |  SELECT
   |    'Procedure',
   |    rdb$procedure_name,
   |    rdb$valid_blr
   |  FROM rdb$procedures
   |  UNION ALL
   |  SELECT
   |    'Function',
   |    rdb$function_name,
   |    rdb$valid_blr
   |  FROM rdb$functions
   |  UNION ALL
   |  SELECT
   |    'Trigger',
   |    rdb$trigger_name,
   |    rdb$valid_blr
   |  FROM rdb$triggers
   |) (type, name, valid)
   |WHERE EXISTS
   |  (SELECT * from rdb$dependencies
   |   WHERE rdb$dependent_name = name
   |     AND rdb$depended_on_name = 'MYDOMAIN')
   | 
   |/* Replace MYDOMAIN with the actual domain name.
   |   Use all-caps if the domain was created
   |   case-insensitively. Otherwise, use the exact
   |   capitalisation. */

The following query will find the modules that depend on a specific table column and report the state of their RDB$VALID_BLR fields:

   |SELECT * FROM (
   |  SELECT
   |    'Procedure',
   |    rdb$procedure_name,
   |    rdb$valid_blr
   |  FROM rdb$procedures
   |  UNION ALL
   |  SELECT
   |    'Function',
   |    rdb$function_name,
   |    rdb$valid_blr
   |  FROM rdb$functions
   |  UNION ALL
   |  SELECT
   |    'Trigger',
   |    rdb$trigger_name,
   |    rdb$valid_blr
   |  FROM rdb$triggers) (type, name, valid)
   |WHERE EXISTS
   |  (SELECT *
   |   FROM rdb$dependencies
   |   WHERE rdb$dependent_name = name
   |     AND rdb$depended_on_name = 'MYTABLE'
   |     AND rdb$field_name = 'MYCOLUMN')
☝
Important

All PSQL invalidations caused by domain/column changes are reflected in the RDB$VALID_BLR field. However, other kinds of changes, such as the number of input or output parameters, called routines and so on, do not affect the validation field even though they potentially invalidate the module. A typical such scenario might be one of the following:

  1. A procedure (B) is defined, that calls another procedure (A) and reads output parameters from it. In this case, a dependency is registered in RDB$DEPENDENCIES. Subsequently, the called procedure (A) is altered to change or remove one or more of those output parameters. The ALTER PROCEDURE A statement will fail with an error when commit is attempted.

  2. A procedure (B) calls procedure A, supplying values for its input parameters. No dependency is registered in RDB$DEPENDENCIES. Subsequent modification of the input parameters in procedure A will be allowed. Failure will occur at run-time, when B calls A with the mismatched input parameter set.

Other Notes

  • For PSQL modules inherited from earlier Firebird versions (including a number of system triggers, even if the database was created under Firebird 2.1 or higher), RDB$VALID_BLR is NULL. This does not imply that their BLR is invalid.

  • The isql commands SHOW PROCEDURES and SHOW TRIGGERS display an asterisk in the RDB$VALID_BLR column for any module for which the value is zero (i.e. invalid). However, SHOW PROCEDURE <procname> and SHOW TRIGGER <trigname>, which display individual PSQL modules, do not signal invalid BLR.