Library

HQbird 2024R2 Update 10 Detailed What is New

Materialized Views (New Feature in 5.0)

HQbird now includes full support for Materialized Views — a new database object type that combines the definition of a view with the physical storage of a regular table.

Overview

A Materialized View (MV) is defined using a standard SQL query, which the engine uses to populate an underlying MV table with data rows. Unlike regular views, MV data is persisted on disk and can be indexed. DML statements (INSERT, UPDATE, DELETE) are not allowed directly on MVs — data must be updated using the new REFRESH command.

Creating and Managing Materialized Views

  • CREATE [OR ALTER] MATERIALIZED VIEW — creates or replaces an MV with optional initial data load (WITH DATA / WITH NO DATA).
  • ALTER MATERIALIZED VIEW — modifies the MV definition; automatically drops all existing indices.
  • RECREATE MATERIALIZED VIEW — drops any existing view (regular or materialized) with the same name and creates a fresh MV.
  • DROP VIEW — used to drop a materialized view (no separate DROP MATERIALIZED VIEW command).
  • Indices of any kind can be created on MVs. Constraints and triggers are not supported.

Converting Between View Types

  • ALTER MATERIALIZED VIEW ... TO NOT MATERIALIZED — converts an MV to a regular view. All indices and data are dropped.
  • ALTER VIEW ... TO MATERIALIZED — converts a regular view to a materialized view. Dependent objects are not affected.

Refreshing Materialized View Data

The new REFRESH MATERIALIZED VIEW statement updates MV content. It is a DDL command — execution is deferred until the current transaction is committed. Four modes are available:

  • Full reload (exclusive mode) — REFRESH MATERIALIZED VIEW
    • Deactivates indices, deletes all existing data, reloads from the MV query, reactivates indices.
    • Fastest option when many rows have changed.
    • Requires exclusive lock on the MV table.
    • On data load error: newly loaded data is deleted, indices are re-activated as empty.
    • On index activation error: affected index is left inactive; data and other indices are not affected. Index build errors are reported as warnings.
  • Concurrent update — REFRESH MATERIALIZED VIEW CONCURRENTLY
    • No exclusive lock required — concurrent queries on the MV are allowed during refresh.
    • Uses a merge algorithm with two ordered cursors: one for current MV data, one for MV query results.
    • Inserts new rows, updates changed rows, deletes removed rows — all under transaction control.
    • Requires a unique index on the MV.
    • Preferred when concurrent access is needed or when few rows have changed since last refresh.
    • Note: if the MV query contains ORDER BY, row order may not be preserved after concurrent refresh.
  • Delete all data — REFRESH MATERIALIZED VIEW DROP DATA
    • Deletes all MV data and index contents. Useful to free disk space. Requires exclusive lock.
  • Cascading refresh — append CASCADE to any of the above forms
    • Refreshes all MVs that the given MV depends on, in correct dependency order.
    • Each MV is processed only once.
    • CONCURRENTLY CASCADE requires all affected MVs to have a unique index.
    • DROP DATA CASCADE deletes data of all affected MVs.
    • Affected MV names and refresh order are reported as warnings in the status-vector before commit.

Backup and Restore (gbak)

  • Backup: MV data is not stored in the backup file, same as regular views — no special handling required.
  • Restore: all MVs are automatically refreshed at the end of the restore process, in correct dependency order.
  • New gbak switch -NO_MATVIEWS (can be abbreviated to -NO_M) — skips MV refresh during restore.
  • Corresponding Services API option: isc_spb_res_no_matviews.
  • Important: a backup containing MVs cannot be restored on a Firebird version that does not support MVs. Use ALTER MATERIALIZED VIEW ... TO NOT MATERIALIZED before backup when migrating to an older version.

Known Limitations / Planned Work

  • Replication support — not yet implemented.
  • DDL triggers — not yet implemented.
  • Access rights check — not yet implemented.
  • Error messages — under review.

Backup and Recovery

  • Verification backup task: added revolving deletion before compression; disk space calculation coefficients are now configurable.
  • nBackup task: corrected skipping logic; journal rename algorithm changed to atomic rename with retry to prevent partial renames.
  • Backup directory and filename template for BRZ tasks are now configurable:
    • backup-directory — allows specifying the target directory per database and job.
    • backup-name — allows defining a filename template, e.g. brr_{0,date,yyyyMMdd_HH-mm-ss}.fbk.
    • Input validator added: checks directory name validity and creates the directory if it does not exist.

Cloud Backup / File Transfer

  • Added support for file upload via Socket protocol in the cloudbackup (filetransfer) task.
  • Selective per-segment file compression option removed — file compression for corresponding tasks is now always enabled.

Trace

  • Added connection_id as an additional field in trace configuration.
  • Trace report processing: added case-insensitive grouping and filtering by process name.

VSS Writer

  • Additional diagnostic logging added — database state information is now recorded including database size, date and time, and gstat -h output.

Web Interface

  • Localization improvements: corrected translations and adjusted localization algorithms across the web console.
  • Added a visual divider element with label text to web dialogs — enables logical grouping of form elements.
  • Changed cache-busting strategy for client-side resources (web pages, JS, CSS) to prevent browsers from using outdated files after an update.

Configuration

  • Server authorization field names updated (sysdba/owner, serviceAPI).
  • Reserved configuration parameter added for the server-side (Java) language/locale setting.
  • Default configuration files corrected to match new default settings for file transfer tasks:
    • Port changed from 8721 to 8722.
    • Default user changed from admin2/admin3 to socketuser.
    • Password strength setting unified to strong password across all configurations.
  • When registering a KA license file, specifying the HTTP/HTTPS protocol prefix in the host field is no longer required.

Bug Fixes

  • Fixed a false critical alert triggered when a database has all tasks temporarily disabled.
  • Fixed an error when retrieving the list of active trace sessions for systems where the configured SYSDBA user does not have permission to list trace sessions.
  • Fixed a bug in a third-party decryption component that did not correctly handle UTF-8 encoded paths. Note: until the mon/trace service is updated and rebuilt, UTF-8 characters in file or database paths may still cause errors in mon/trace output.
  • Fixed an issue in the replication configuration handler that incorrectly retained keys with empty values.
  • Fixed journal_archive_command handling for Firebird 4 and later (aligned with upstream fix in firebird/firebird PR #8381).
  • Fixed errors in the dg2fbcc.jar configuration generator plugin and its invocation code.

Installer and Build

  • Windows installer updated with revised scripts and logic.
  • Client library binaries updated.
  • OpenSSL updated to a newer version.
  • Conditional code branches added for product-specific build variants.
  • File signing added to installer build process.

Server-Side Localization Infrastructure

A localization mechanism for server-side (Java) text messages has been implemented. Locale files are prepared but not yet populated with translations, and the mechanism is not yet activated in production. This infrastructure is intended for future localization of server messages.