Unable to restore database to Firebird 6.0 (with schemas) from ODS 13.1 if database contains views with system tables used in subqueries.

[sim1984]

Firebird Firebird-6.0.0.800-0-2b9bd0e-windows-x64

I tried to restore the database using gbak from an ODS 13.1 backup and got the following error.

gbak: ERROR:table "PUBLIC"."MON$CONTEXT_VARIABLES" is not defined
gbak: ERROR:    Exiting before completion due to errors
gbak:Exiting before completion due to errors

Investigation into the causes of this error revealed that the problem is in the view:

CREATE OR ALTER VIEW V_ATTACHMENTS(
    ATTACHMENT_ID,
    STAT_ID,
    STATE,
    ATTACHMENT_NAME,
    USER_NAME,
    ROLE_NAME,
    REMOTE_PROTOCOL,
    REMOTE_ADDRESS,
    REMOTE_PID,
    CHARACTER_SET,
    ELAPSED_START,
    GARBAGE_COLLECTION,
    REMOTE_PROCESS,
    APP_NAME,
    ETNI_VERSION,
    ETNI_USERNAME,
    CLIENT_HOSTNAME,
    FBCLIENT_VER,
    REMOTE_VERSION,
    OS_USER,
    AUTH_METHOD,
    IDLE_TIMEOUT,
    IDLE_TIMER,
    STATEMENT_TIMEOUT,
    WIRE_COMPRESSED,
    WIRE_ENCRYPTED,
    WIRE_CRYPT_PLUGIN,
    SESSION_TIMEZONE,
    PARALLEL_WORKERS,
    PAGE_READS,
    PAGE_WRITES,
    PAGE_FETCHES,
    PAGE_MARKS,
    MEMORY_USED,
    MEMORY_ALLOCATED,
    MAX_MEMORY_USED,
    MAX_MEMORY_ALLOCATED)
AS
SELECT
  A.MON$ATTACHMENT_ID AS ATTACHMENT_ID,
  A.MON$STAT_ID AS STAT_ID,
  CASE
    WHEN A.MON$STATE = 0 THEN 'IDLE'
    WHEN A.MON$STATE = 1 THEN 'ACTIVE'
    WHEN A.MON$STATE = 2 THEN 'STALLED'
  END AS STATE,
  A.MON$ATTACHMENT_NAME AS ATTACHMENT_NAME,
  A.MON$USER AS USER_NAME,
  A.MON$ROLE AS ROLE_NAME,
  A.MON$REMOTE_PROTOCOL AS REMOTE_PROTOCOL,
  A.MON$REMOTE_ADDRESS AS REMOTE_ADDRESS,
  A.MON$REMOTE_PID AS REMOTE_PID,
  CS.RDB$CHARACTER_SET_NAME AS CHARACTER_SET,
  A.MON$TIMESTAMP AS ELAPSED_START,
  CASE
    WHEN A.MON$GARBAGE_COLLECTION = 1 THEN 'Да'
    ELSE 'Нет'
  END AS GARBAGE_COLLECTION,
  A.MON$REMOTE_PROCESS AS REMOTE_PROCESS,
  SF_EXTRACT_FILENAME(A.MON$REMOTE_PROCESS) AS APP_NAME,
  /*(SELECT
     CAST(TRIM(CV.MON$VARIABLE_VALUE) AS VARCHAR(20))
   FROM MON$CONTEXT_VARIABLES CV
   WHERE CV.MON$ATTACHMENT_ID = A.MON$ATTACHMENT_ID
     AND CV.MON$VARIABLE_NAME = 'ETNI_VERSION'
  )*/ NULL AS ETNI_VERSION,
  /*(SELECT
     CAST(TRIM(CV.MON$VARIABLE_VALUE) AS VARCHAR(63))
   FROM MON$CONTEXT_VARIABLES CV
   WHERE CV.MON$ATTACHMENT_ID = A.MON$ATTACHMENT_ID
     AND CV.MON$VARIABLE_NAME = 'ETNI_USER'
  )*/ NULL AS ETNI_USERNAME,
  A.MON$REMOTE_HOST AS CLIENT_HOSTNAME,
  A.MON$CLIENT_VERSION AS FBCLIENT_VER,
  A.MON$REMOTE_VERSION AS REMOTE_VERSION,
  A.MON$REMOTE_OS_USER AS OS_USER,
  A.MON$AUTH_METHOD AS AUTH_METHOD,
  A.MON$IDLE_TIMEOUT AS IDLE_TIMEOUT,
  A.MON$IDLE_TIMER AS IDLE_TIMER,
  A.MON$STATEMENT_TIMEOUT AS STATEMENT_TIMEOUT,
  IIF(A.MON$WIRE_COMPRESSED, 'Да', 'Нет') AS WIRE_COMPRESSED,
  IIF(A.MON$WIRE_ENCRYPTED, 'Да', 'Нет') AS WIRE_ENCRYPTED,
  A.MON$WIRE_CRYPT_PLUGIN AS WIRE_CRYPT_PLUGIN,
  A.MON$SESSION_TIMEZONE AS SESSION_TIMEZONE,
  A.MON$PARALLEL_WORKERS AS PARALLEL_WORKERS,
  MON$IO_STATS.MON$PAGE_READS AS PAGE_READS,
  MON$IO_STATS.MON$PAGE_WRITES AS PAGE_WRITES,
  MON$IO_STATS.MON$PAGE_FETCHES AS PAGE_FETCHES,
  MON$IO_STATS.MON$PAGE_MARKS AS PAGE_MARKS,
  M.MON$MEMORY_USED AS MEMORY_USED,
  M.MON$MEMORY_ALLOCATED AS MEMORY_ALLOCATED,
  M.MON$MAX_MEMORY_USED AS MAX_MEMORY_USED,
  M.MON$MAX_MEMORY_ALLOCATED AS MAX_MEMORY_ALLOCATED
FROM
  MON$ATTACHMENTS A
  JOIN RDB$CHARACTER_SETS CS ON CS.RDB$CHARACTER_SET_ID = A.MON$CHARACTER_SET_ID
  JOIN MON$IO_STATS ON MON$IO_STATS.MON$STAT_ID = A.MON$STAT_ID AND MON$IO_STATS.MON$STAT_GROUP = 1
  JOIN MON$MEMORY_USAGE M ON M.MON$STAT_ID = A.MON$STAT_ID AND M.MON$STAT_GROUP = 1
WHERE A.MON$SYSTEM_FLAG = 0
;

The problem is in the commented lines. The same problem occurs when restoring another view:

CREATE OR ALTER VIEW V_REPL_STATE(
    DB_GUID,
    ENABLED_PUBLICATION,
    REPLICA_MODE,
    REPLICATION_SEQUENCE,
    HORSE_ID,
    COVER_ID,
    NOTE_ID,
    LAB_LINE_ID,
    TRIAL_LINE_ID,
    REGISTRATION_ID)
AS
SELECT
  CAST(RDB$GET_CONTEXT('SYSTEM', 'DB_GUID') AS CHAR(38) CHARACTER SET NONE) AS DB_GUID,
  /*(SELECT RDB$ACTIVE_FLAG FROM RDB$PUBLICATIONS WHERE RDB$PUBLICATION_NAME = 'RDB$DEFAULT')*/ NULL AS ENABLED_PUBLICATION,
  CAST(RDB$GET_CONTEXT('SYSTEM', 'REPLICA_MODE') AS VARCHAR(10)) AS REPLICA_MODE,
  CAST(RDB$GET_CONTEXT('SYSTEM', 'REPLICATION_SEQUENCE') AS BIGINT) AS REPLICATION_SEQUENCE,
  GEN_ID(HORSE_GEN, 0) AS HORSE_ID,
  GEN_ID(COVER_GEN, 0) AS COVER_ID,
  GEN_ID(NOTE_GEN, 0) AS NOTE_ID,
  GEN_ID(LAB_LINE_GEN, 0) AS LAB_LINE_ID,
  GEN_ID(TRIAL_LINE_GEN, 0) AS TRIAL_LINE_ID,
  GEN_ID(REGISTRATION_GEN, 0) AS REGISTRATION_ID
FROM RDB$DATABASE
;

Once these views were fixed, the database was successfully restored.

These two views have one thing in common, they use system tables (which have been moved to the SYSTEM schema) in subqueries. It should be noted that the system tables in other parts of the view do not pose a problem.