FirebirdSQL logo
 General NotesChanges in the Firebird Engine 

Summary of New Features

Firebird 5.0 introduces many improvements without any changes in architecture or operation, the most important are:

  • Parallel (multi-threaded) operation for backup/restore, sweep and index creation;

  • Partial indices;

  • SKIP LOCKED clause for SELECT WITH LOCK, UPDATE and DELETE statements;

  • Inline minor ODS upgrade;

  • Compiled statement cache;

  • PSQL and SQL profiler;

  • Support for WHEN NOT MATCHED BY SOURCE for MERGE statement;

  • Support multiple rows for DML RETURNING;

  • New built-in functions and packages;

  • Denser record-level compression;

  • Network support for scrollable cursors;

The following list summarises the features and changes, with links to the chapters and topics where more detailed information can be found.

Complete In Firebird 5.0 Release Candidate 2

Allow to configure Firebird in POSIX using relative directories with options --with-fb

#7918 by Adriano dos Santos Fernandes

Add backward compatibility option that disables joins transformation

#7910 by Dmitry Yemanov

For more details, see [rnfb50-config-outer-join-conversion].

Resolve performance issue with time zones

#7854 by Adriano dos Santos Fernandes, Vlad Khorsun

Resolve difficulty returning the product version with the legacy connection

#7819 by Vlad Khorsun

Extend RDB$GET_CONTEXT('SYSTEM', '*') with other info from MON$ATTACHMENTS

#7818 by Vlad Khorsun

Don’t update database-level statistics on every page cache operation

#7814 by Vlad Khorsun

Improve SKIP LOCKED implementation

#7810 by Vlad Khorsun

For more details, see [rnfb50-dml-skip-locked].

Update Windows distribution with new zlib version 1.3 (released 2023-08-18)

#7755 by Vlad Khorsun

Complete In Firebird 5.0 Release Candidate 1

Avoid truncation of the access path information inside the PLG$PROF_RECORD_SOURCES table to 255 characters

#7752 by Adriano dos Santos Fernandes

MacOS: build libicu and static libc++ using vcpkg

#7720 by Adriano dos Santos Fernandes

Better processing and optimization of IN <list> predicates

#7707 by Dmitry Yemanov

For more details, see [rnfb50-dml-in].

Make trace config parser resolve symlinks in database file path in trace configuration

#7692 by Vlad Khorsun

Allow tracing database events even if the trace/audit is configured using a symbolic link instead of a real database file path.

Profiler should not miss query’s top-level access paths nodes

#7688 by Adriano dos Santos Fernandes

Add LEVEL column to PLG$PROF_RECORD_SOURCES and PLG$PROF_RECORD_SOURCE_STATS_VIEW

#7687 by Adriano dos Santos Fernandes

Column LEVEL is added to the profiler record source tables/views. It returns the actual indentation level that allows proper reconstruction of multi-line access path nodes.

Add overload FbVarChar::set function for non null-terminated string

#7685 by Adriano dos Santos Fernandes

Use ParallelWorkers setting from firebird.conf as default for all parallelized operations

#7682 by Vlad Khorsun

This allows utilities started via the Service Manager to use the server-side ParallelWorkers setting without a need to specify the number of workers explicitly.

Make boot build on Windows a bit more user-friendly

#7680 by Vlad Khorsun

Make the profiler store aggregated requests by default, with an option for the detailed store

#7652 by Adriano dos Santos Fernandes

Make the profiler data aggregated at the statement level by default (with REQUEST_ID = 0), thus making the flushing faster. A detailed (non-aggregated) data may be requested explicitly by specifying the 'DETAILED_REQUESTS' value in the PLUGIN_OPTIONS parameter of the RDB$PROFILER.START_SESSION function.

Getting the current DECFLOAT ROUND/TRAPS settings

#7642 by Alexander Peshkov

New context variables DECFLOAT_ROUND and DECFLOAT_TRAPS are added to the SYSTEM namespace of the RDB$GET_CONTEXT function. They return the current settings of the corresponding session options.

Run as application not specifying switch -a

#7637 by Vlad Khorsun

Command-line switch -a is no longer required on Windows to start the Firebird server as an application.

Include Performance Cores only in default affinity mask

#7634 by Vlad Khorsun

Some modern CPUs may contain two sets of cores - Efficient (E) and Performance (P). By default Firebird runs on all available cores and that may cause performance degradation in CPU-bound tasks. Now, E-cores are excluded from the default affinity mask. If the AffinityMask configuration setting is used explicitly, please pay attention which types of cores are specified to be enabled.

Allow nested parenthesized joined table

#7576 by Mark Rotteveel

Optimize creation of expression and partial indices

#7559 by Dmitry Yemanov

Add support for -parallel in combination with gfix -icu

#7550 by Vlad Khorsun

gfix -icu rebuilds indexes and thus can benefit from parallelization. Now it’s allowed to use the -icu and -parallel switches together, to override the ParallelWorkers setting used by default.

Compiler warnings raise when build cloop-generated Firebird.pas in RAD Studio 11.3

#7542 by Vlad Khorsun

RDB$GET/SET_CONTEXT() — enclosing in apostrophes or double quotes of a missed namespace/variable will make output more readable

#7539 by Vlad Khorsun

Add ability to query current value of parallel workers for an attachment

#7536 by Vlad Khorsun

  • New column MON$PARALLEL_WORKERS INTEGER was added into MON$ATTACHMENTS table.

  • New variable PARALLEL_WORKERS is now available in the SYSTEM context of the RDB$GET_CONTEXT function.

  • New tag fb_info_parallel_workers (value 149) is available in IAttachment::getInfo() and isc_database_info() API calls.

Reduce output of the SHOW GRANTS command

#7506 by Artyom Ivanov

Firebird performance issue — unnecessary index reads

#7494 by Vlad Khorsun

Index scan algorithm has been improved to avoid unnecessary record reads for partial lookups in compound indices.

SHOW SYSTEM command: provide list of functions belonging to system packages

#7475 by Alexander Peshkov

Make Android port (client/embedded) work inside apps

#7469 by Adriano dos Santos Fernandes

Add COMPILE trace events for procedures/functions/triggers

#7466 by Dmitry Yemanov

Allows to trace parse/compile events for the stored modules, corresponding elapsed time and also plans for queries inside those PSQL modules.

Add REPLICA MODE to the output of the isql SHOW DATABASE command

#7425 by Dmitry Yemanov

Surface internal optimization modes (all rows vs first rows) at the SQL and configuration levels

#7405 by Dmitry Yemanov

For more details, see [rnfb50-config-optimize-mode], [rnfb50-dml-select-optimize], and [rnfb50-msql-optimize-set].

Use Windows private namespace for kernel objects used in server-to-server IPC

#7213 by Vlad Khorsun

This improvement allows to synchronize Firebird processes across different Windows sessions.

Add ability to add comment to mapping (COMMENT ON MAPPING …​ IS …​)

#7046 by Alexander Peshkov

For more details, see [rnfb50-ddl-comment-mapping].

ISQL showing publication status

#7001 by Dmitry Yemanov

Add support for QUARTER to EXTRACT, FIRST_DAY and LAST_DAY functions

#5959 by Adriano dos Santos Fernandes

For more details, see [rnfb50-dml-quarter].

Complete In Firebird 5.0 Beta 1

Parallel (multi-threaded) operations

#1783, #3374, #7447 by Vlad Khorsun

Such operations as logical backup/restore, sweeping and CREATE INDEX statement execution can be executed in parallel by multiple threads, thus decreasing the total operation time.

See chapters [rnfb50-engine-parallel], [rnfb50-utils-gbak-parallel] and [rnfb50-utils-gfix-parallel] for more details.

Support for partial indices

#7257 by Dmitry Yemanov

The CREATE INDEX DDL statement has been extended to support partial indices, i.e. an index may now declare a condition that defines the subset of records to be indexed.

SKIP LOCKED clause

#7350 by Adriano dos Santos Fernandes

New clause SKIP LOCKED was introduced for statements SELECT WITH LOCK, UPDATE and DELETE. It allows to skip the already locked records while reading the table.

Inline minor ODS upgrade

#7397 by Dmitry Yemanov

An ability to upgrade the database to the latest minor ODS version has been introduced, it does not require a backup/restore cycle.

Compiled statement cache

#7144 by Adriano dos Santos Fernandes

A per-attachment cache of compiled SQL statements has been implemented.

PSQL and SQL profiler

#7086 by Adriano dos Santos Fernandes

A built-in ability to profile SQL and PSQL statements has been added, making it possible to measure execution time at different levels.

Support for WHEN NOT MATCHED BY SOURCE in the MERGE statement

#6681 by Adriano dos Santos Fernandes

The MERGE statement has been extended to support the WHEN NOT MATCHED BY SOURCE clause.

Built-in functions UNICODE_CHAR and UNICODE_VAL

#6798 by Adriano dos Santos Fernandes

New built-in functions UNICODE_CHAR and UNICODE_VAL have been added to allow conversion between Unicode code point and character.

RDB$BLOB_UTIL new system package

#281 by Adriano dos Santos Fernandes

New system package RDB$BLOB_UTIL has been added to allow various operations with BLOBs in the PSQL modules.

Support multiple rows being returned by DML with the RETURNING clause

#6815 by Adriano dos Santos Fernandes

The RETURNING clause, if used in DSQL queries, now allows multiple rows to be returned.

Optimize the record-level RLE algorithm for a denser compression of shorter-than-declared strings and sets of subsequent NULLs

#4723 by Dmitry Yemanov

The built-in compression algorithm has been improved to allow denser compression of records.

More cursor-related details in the plan output

#7441 by Dmitry Yemanov

Execution plan now contains more information about cursors.

Other improvements are briefly listed below, please follow the tracker references for more information.

Unify display of system procedures & packages with other system objects

#7411 by Alexander Peshkov

System procedures and packages are now shown by SHOW SYSTEM.

Simplify client library build

#7399 by Adriano dos Santos Fernandes

Performance improvement for BLOB copying

#7382 by Adriano dos Santos Fernandes

Cost-based choice between nested loop join and hash join

#7331 by Dmitry Yemanov

Create Android packages with all necessary files in all architectures (x86, x64, arm32, arm64)

#7293 by Adriano dos Santos Fernandes

Unify release filenames

#7284 by Adriano dos Santos Fernandes

Improve ICU version mismatch diagnostics

#7169 by Adriano dos Santos Fernandes

Provide ability to see in the trace log events related to missing security context

#7165 by Alexander Peshkov

For more details, see [rnfb50-security-trace-ctx].

ResultSet.getInfo() new API method

#7083 by Dmitry Yemanov

Network support for scrollable cursors

#7051 by Dmitry Yemanov

Add table MON$COMPILED_STATEMENTS and also column MON$COMPILED_STATEMENT_ID to both MON$STATEMENTS and MON$CALL_STACK tables

#7050 by Adriano dos Santos Fernandes

Results of negation must be the same for each datatype (SMALLINT / INT / BIGINT / INT128) when argument is minimum value for this type

#7025 by Alexander Peshkov

Transform OUTER joins into INNER ones if the WHERE condition violates the outer join rules

#6992 by Dmitry Yemanov

Add way to retrieve statement BLR with Statement.getInfo() and ISQL's SET EXEC_PATH_DISPLAY BLR

#6910 by Adriano dos Santos Fernandes

For isql, see [rnfb50-utils-exec-path-blr].

SIMILAR TO should use index when pattern starts with non-wildcard character (as LIKE does)

#6873 by Adriano dos Santos Fernandes

Add column MON$SESSION_TIMEZONE to the table MON$ATTACHMENTS

#6794 by Adriano dos Santos Fernandes

Allow parenthesized query expression for standard-compliance

#6740 by Adriano dos Santos Fernandes

For more details, see [rnfb50-dml-parens-query].

System table with keywords

#6713 by Adriano dos Santos Fernandes

Support full SQL standard character string literal syntax

#5589 by Adriano dos Santos Fernandes

For more details, see [rnfb50-dml-literals-char].

Support full SQL standard binary string literal syntax

#5588 by Adriano dos Santos Fernandes

For more details, see [rnfb50-dml-literals-binary].

Allow subroutines to access variables/parameters defined at the outer/parent level

#4769 by Adriano dos Santos Fernandes

For more details, see [rnfb50-psql-subroutine-param-access].

Avoid data retrieval if the WHERE clause always evaluates to FALSE

#1708 by Dmitry Yemanov