Firebird 4.0.5 Release Notes

#8181 — Ensure the standalone CS listener on Linux uses the SO_REUSEADDR socket option

Implemented by Dmitry Yemanov

#8165 — Added shutdown handler for Classic Server

Implemented by Alexander Peshkov, Alexander

#8104 — More efficient evaluation of expressions like RDB$DB_KEY <= ? after mass delete

Implemented by Vlad Khorsun

#8066 — Make protocol schemes case-insensitive

Implemented by Vlad Khorsun

#8042 — Improve conflict resolution on replica when table has both primary and unique keys

Implemented by Vlad Khorsun

#8030 — Better cardinality estimation when empty data pages exist

Implemented by Vlad Khorsun

#8010 — Remove gfix -cache option

Implemented by Vlad Khorsun

#7978 — Update Windows distributions with zlib version 1.3.1

Implemented by Vlad Khorsun

#7928 —  Make TempCacheLimit setting to be per-database (not per-attachment) for SuperClassic

Implemented by Vlad Khorsun

#7854 — Performance issue with time zones

Implemented by Adriano dos Santos Fernandes, Vlad Khorsun

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

Implemented by Vlad Khorsun

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

Implemented by Vlad Khorsun

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

Implemented by Vlad Khorsun

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

Implemented by Vlad Khorsun

#7494 — Avoid non necessary index reads

Implemented by Vlad Khorsun

#7468 — Add switch to control in guardian timeout before killing firebird server process

Implemented by Alex Peshkoff

#7437 — Updated zlib to version 1.2.13 (released 2022-10-13)

Implemented by Vlad Khorsun

#7425 — Add REPLICA MODE to the output of the isql SHOW DATABASE command

Implemented by Dmitry Yemanov

#7418 — Improve reliability of plugin manager

Implemented by Alex Peshkoff

#7294 — Allow FB-known macros in replication.conf

Implemented by Dmitry Yemanov

#7259 — Remove TcpLoopbackFastPath and use of SIO_LOOPBACK_FAST_PATH

Implemented by Vlad Khorsun

#7186 — NBackup RDB$BACKUP_HISTORY cleanup

Implemented by Vlad Khorsun

#6983 — New built-in function BLOB_APPEND

Implemented by V. Khorsun

#7208 — Trace: provide performance statistics for DDL statement.

Implemented by V. Khorsun

#7194 — Make it possible to avoid fbclient dependency in Pascal programs using firebird.pas.

Implemented by A. Peshkov

#7168 — Ignore missing UDR libraries during restore.

Implemented by A. dos Santos Fernandes

#7161 — Update zlib to version 1.2.12.

Implemented by V. Khorsun

#7093 — Improve indexed lookup speed of strings when the last keys characters are part of collated contractions.

Implemented by A. dos Santos Fernandes

#7092 — Improve performance of CURRENT_TIME.

Implemented by A. dos Santos Fernandes

#7042 — ON DISCONNECT triggers are not executed during forced attachment shutdown.

Implemented by I. Eremin

#7041 — Firebird port for Apple M1 architecture.

Implemented by J. Frutos, A. Peshkoff, A. dos Santos Fernandes

#7038 — Improve performance of STARTING WITH with insensitive collations.

Implemented by A. dos Santos Fernandes

#6730 — Trace: provide ability to see STATEMENT RESTART events (or their count).

Implemented by V. Khorsun

#6910 — Add way to retrieve statement BLR with Statement::getInfo() and ISQL’s SET EXEC_PATH_DISPLAY BLR.

Implemented by A. dos Santos Fernandes

#6959 — Add getInfo() method to the Batch interface in the API.

Implemented by A. Peshkov

#6954 — Add fb_info_protocol_version information request to Attachment::getInfo().

Implemented by A. Peshkov

#6929 — Add support of PKCS v.1.5 padding to RSA functions, needed for backward compatibility with old systems.

Implemented by A. Peshkov

#6915 — Allow attribute DISABLE-COMPRESSIONS in UNICODE collations.

Implemented by A. dos Santos Fernandes

#6903 — Make it possible to create ICU-based collations with locales keywords.

Implemented by tkeinz, A. dos Santos Fernandes

#6872 — Faster indexed STARTING WITH execution with UNICODE collations.

Implemented by A. dos Santos Fernandes

#6810 — Use precise limit of salt length when signing messages and verifying the sign.

Implemented by A. Peshkov

#6809 — Integer hex-literal support for INT128.

Implemented by A. Peshkov

Notes about compatibility with older Firebird versions are collated in the “Compatibility Issues” chapter.

As the name implies, the session time zone, can be different for each database attachment. It can be set explicitly in the DPB or SPB with the item isc_dpb_session_time_zone; otherwise, by default, it uses the same time zone as the operating system of the Firebird server process. This default can be overridden in firebird.conf, see DefaultTimeZone setting in the Configuration Additions and Changes chapter.

Subsequently, the time zone can be changed to a given time zone using a SET TIME ZONE statement or reset to its original value with SET TIME ZONE LOCAL.

A time zone is a string, either a time zone region (for example, 'America/Sao_Paulo') or a displacement from GMT in hours:minutes (for example, '-03:00').

A time/timestamp with time zone is considered equal to another time/timestamp with time zone if their conversions to UTC are equivalent. For example, time '10:00 -02:00' and time '09:00 -03:00' are equivalent, since both are the same as time '12:00 GMT'.

The syntax for declaring the data types TIMESTAMP and TIME has been extended to include arguments defining whether the field should be defined with or without time zone adjustments, i.e.,

The default for both TIME and TIMESTAMP is WITHOUT TIME ZONE. For more details, see Data Type Extensions for Time Zone Support in the Data Definition Language chapter.

Additions and enhancements to syntax in DDL and DML are listed in this section. Follow the links indicated to the details in the DDL and DML chapters.

A virtual table listing time zones supported in the engine. Columns:

A package of time zone utility functions and procedures:

Time zones are often changed: of course, when it happens, it is desirable to update the time zone database as soon as possible.

Firebird stores WITH TIME ZONE values translated to UTC time. Suppose a value is created with one time zone database and a later update of that database changes the information in the range of our stored value. When that value is read, it will be returned as different to the value that was stored initially.

Firebird uses the IANA time zone database through the ICU library. The ICU library presented in the Firebird kit (Windows), or installed in a POSIX operating system, can sometimes have an outdated time zone database.

An updated database can be found on this page on the FirebirdSQL GitHub. Filename le.zip stands for little-endian and is the necessary file for most computer architectures (Intel/AMD compatible x86 or x64), while be.zip stands for big-endian architectures and is necessary mostly for RISC computer architectures. The content of the zip file must be extracted in the /tzdata sub-directory of the Firebird installation, overwriting existing *.res files belonging to the database.

Both synchronous and asynchronous modes are available.

There are two access modes for replica databases: read-only and read-write.

Asynchronous replication is implemented with journaling. Replicated changes are written into the journal which consists of multiple files, known as replication segments. The Firebird server writes segments continuously, one after another. Every segment has a unique number which is generated sequentially. This number, known as a segment sequence, is combined with the database UUID to provide globally unique identification of journal segments. The global sequence counter is stored inside the replicated database and is reset only when the database is restored from backup.

Segments are rotated regularly, a process that is controlled by either maximum segment size or timeout. Both thresholds are configurable. Once the active segment reaches the threshold, it is marked as “full” and writing switches to the next available segment.

Full segments are archived and then reused for subsequent writes. Archiving consists of copying the segment in preparation for transferring it to the replica host and applying it there. Copying can be done by the Firebird server itself or, alternatively, by a user-specified custom command.

On the replica side, journal segments are applied in the replication sequence order. The Firebird server periodically scans for new segments appearing in the configured directory. Once the next segment is found, it gets replicated. For each replication source, the replication state is stored in a local file named for the UUID and the replication source. It contains markers for

All replication errors and warnings (such as detected conflicts) are written into the replication.log file. It may also include detailed descriptions of the operations performed by the replicator.

Setup involves tasks on both the primary and replica sides.

The implementation of connection pooling in Firebird 4 eliminates the problem of interminable external connections by controlling and limiting the number of idle connections. The same pool is used for all external connections to all databases and all local connections handled by a given Firebird process. It supports a quick search of all pooled connections using four parameters, described below in New Connections.

Every successful connection is associated with a pool, which maintains two lists — one for idle connections and one for active connections. When a connection in the “active” list has no active requests and no active transactions, it is assumed to be “unused”. A reset of the unused connection is attempted using an ALTER SESSION RESET statement and,

A new SQL statement has been introduced to manage the pool during run-time from any connection, between Firebird restarts, i.e., changes made with ALTER EXTERNAL CONNECTIONS POOL are not persistent.

This is the syntax pattern:

For a full descriptions and examples of the variants, see ALTER EXTERNAL CONNECTIONS POOL Statement in the chapter Management Statements.

The state of the external connections pool can be queried using a set of new context variables in the 'SYSTEM' namespace:

Two new parameters, for firebird.conf only, are for configuring the connection pool at process start. Follow the links for details.

An idle session timeout allows a user connection to close automatically after a specified period of inactivity. A database administrator can use it to enforce closure of old connections that have become inactive, to reduce unnecessary consumption of resources. It can also be used by application and tools developers as an alternative to writing their own modules for controlling connection lifetime.

By default, the idle timeout is not enabled. No minimum or maximum limit is imposed but a reasonably large period, such as a few hours, is recommended.

The statement timeout feature allows execution of a statement to be stopped automatically when it has been running longer than a given timeout period. It gives the database administrator an instrument for limiting excessive resource consumption from heavy queries.

Statement timeouts can also be useful to application developers when creating and debugging complex queries without advance knowledge of execution time. Testers and others could find them handy for detecting long-running queries and establishing finite run times for test suites.

Firebird 4 takes a new approach to establishing a consistent view of the database state visible to running transactions. This new approach uses the concept of commit order.

It is sufficient to know the order of commits in order to capture the state of any transaction at the moment when a snapshot is created.

The existing implementation of READ COMMITTED isolation for transactions suffers from an important problem: a single statement, such as a SELECT, could see different views of the same data during execution.

For example, imagine two concurrent transactions, where the first inserts 1000 rows and commits, while the second runs SELECT COUNT(*) over the same table.

If the isolation level of the second transaction is READ COMMITTED, its result is hard to predict. It could be any of:

Which of those results is actually returned depends on how the two transactions interact:

CASE 3 is the problem referred to as inconsistent read at the statement level. It matters because, by definition, each statement in a READ COMMITTED transaction has its own distinct view of database state. In the existing implementation, the statement’s view is not certain to remain stable for the duration of its execution: it could change between the start of execution and the completion.

Statements running in a SNAPSHOT transaction do not have this problem, since every statement runs against a consistent view of database state. Also, different statements that run within the same READ COMMITTED transaction could see different views of database state but this is “as designed” and is not a source of statement-level inconsistency.

The record version visibility rule provides the following logic for identifying record versions as garbage:

The last part of the rule reproduces the legacy rule, whereby all record versions at the tail of the versions chain start from some “mature” record version. The rule allows that mature record version to be identified so that the whole tail after it can be cut.

However, with snapshots based on commit-order, version chains can be further shortened because it enables some record versions located in intermediate positions in the versions chain to be identified as eligible for GC. Each record version in the chain is marked with the value of the oldest active snapshot that can see it. If several consecutive versions in a chain are marked with the same oldest active snapshot value, then all those following the first one can be removed.

The engine performs garbage collection of intermediate record versions during the following processes:

To make it work, the engine maintains in shared memory an array of all active database snapshots. When it needs to find the oldest active snapshot that can see a given record version, it just searches for the CN of the transaction that created that record version.

The default initial size of this shared memory block is 64KB but it will grow automatically when required. The initial block can be set to a custom size in firebird.conf and/or databases.conf using the new parameter SnapshotsMemSize.

Firebird 4.0 creates databases with an ODS (On-Disk Structure) version of 13.

System tables added in ODS13:

Column RDB$SQL_SECURITY was added to the following system tables in ODS13:

For RDB$DATABASE, it defines the default SQL SECURITY mode (DEFINER or INVOKER) applied to the newly created objects. For other system tables, it defines the SQL SECURITY mode active for the appropriate objects.

Also, column RDB$SYSTEM_PRIVILEGES is added to the system table RDB$ROLES. It stores system privileges granted to a role.

Alex Peshkov

Apart from the widely-known Services Manager (service_mgr), Firebird has a group of so-called “version 1” service managers. Backup and gsec are examples, along with a number of other services related to shared cache control and the unused journaling feature. Since at least Firebird 3 they seem to be in a semi-working state at best, so they have undergone a cleanup.

A visible effect is that the constant service_mgr is no longer required in the connection string for a service request. The request call will ignore anything in that field, including an empty string. The remote client will do the right thing just by processing the host name, such as localhost:, inet://localhost/ or inet://localhost.

Provides API support for the new READ COMMITTED READ CONSISTENCY isolation sub-level for READ COMMITTED transactions. To start a READ COMMITTED READ CONSISTENCY transaction via the ISC API, use the new constant isc_tpb_read_consistency in the Transaction Parameter Buffer.

Alex Peshkov

The OO-API in Firebird 4 supports execution of statements with more than a single set of parameters — batch execution. The primary purpose of the batch interface design is to satisfy JDBC requirements for batch processing of prepared statements, but it has some fundamental differences:

The methods described below illustrate how to implement everything needed for JDBC-style prepared statement batch operations. Almost all of the methods described are used in 11.batch.cpp. Please refer to it to see a live example of batching in Firebird.

Alex Peshkov

DecFloat16 and DecFloat34 are helper interfaces that simplify working with the DECFLOAT (16-digit and 34-digit respectively) data types. Available methods in the DecFloat16 interface are the following:

The DecFloat34 interface shares the same methods, just using the FB_DEC34 structure.

Int128 is a helper interface for 128-bit integers (used internally as a base type for INT128, and also for NUMERIC and DECIMAL data types with precision > 18), it contains the following methods:

Structures used by the aforementioned interfaces are defined below:

In order to work with these new interfaces, the Util interface has been extended with the following methods:

Alex Peshkov

A number of new methods have been added to the following interfaces.

Alex Peshkov

A few functions have been added to the ISC API.

They can be used to get an OO API object from the corresponding ISC API handle.

The value is integer, expressing minutes. Study the notes on idle session timeouts carefully to understand how this configuration fits in with related settings via SQL and the API.

See Setting the Session Timeout in the chapter Changes in the Firebird Engine.

The value is integer, expressing seconds. Study the notes on statement timeouts carefully to understand how this configuration fits in with related settings via SQL and the API.

See Setting a Statement Timeout in the chapter Changes in the Firebird Engine.

Configures the maximum number of idle connections allowed in the pool. It is an integer, from 0 to 1000. The installation default is 0, which disables the connection pool.

Configures the number of seconds a connection should stay available after it has gone idle. The installation default is 7200 seconds.

Sets a limit for the number of bytes allowed in an object identifier. It is an integer, defaulting to 252 bytes, i.e., 63 characters * 4, 4 being the maximum number of bytes for each character.

To set it to the limit in previous Firebird versions, use 31.

Sets a limit for the number of characters allowed in an object identifier. It is an integer, defaulting to 63, the new limit implemented in Firebird 4.

For now, existing applications can be tested with and without the new READ COMMITTED READ CONSISTENCY isolation level by setting this parameter. Possible values are 1 and 0.

This behaviour can be defined in firebird.conf and/or databases.conf.

The list of all known transactions with associated Commit Numbers is maintained in shared memory. It is implemented as an array whose index is a transaction ID and its item value is the corresponding Commit Number.

The whole array is split into fixed-size blocks containing the CN’s for all transactions between the OIT and Next Transaction markers. When the “Next Transaction” marker moves out of the scope of the highest block, a new block is allocated. An old block is released when the “Oldest [Interesting] Transaction” (OIT) marker moves out of the scope of the lowest block.

The default size for a TIP cache block is 4MB, providing capacity for 512 * 1024 transactions. Use this parameter to configure a custom TIP cache block size in firebird.conf and/or databases.conf.

To handle garbage collection of record versions younger than the Oldest Snapshot, (“intermediate record versions”) the engine maintains in shared memory an array that it can search for the Commit Number (CN) of a particular record version. See the Garbage Collection topic the chapter Changes in the Firebird Engine.

The default initial size of this shared memory block is 64KB but it will grow automatically when required. The initial block can be set to a custom size in firebird.conf and/or databases.conf.

Defines the buffer size used by the client connection for batch-mode transmission to the server (when Batch API is used). See the Support for Batch Insert and Update Operations in the API topic for more details.

Specifies the compatibility level that defines what SQL data types can be exposed to the client API. Currently two options are available: "3.0" and "2.5". The "3.0" emulation mode hides data types introduced after Firebird 3.0 release, in particular DECIMAL/NUMERIC with precision 19 or higher, DECFLOAT, TIME/TIMESTAMP WITH TIME ZONE. The corresponding values are returned via data types already supported by Firebird 3.0. The "2.5" emulation mode also converts the BOOLEAN data type. See the Native to Legacy Coercion Rules table for details. This setting allows legacy client applications to work with Firebird 4.0 without recompiling and adjusting them to understand the new data types.

Defines the time zone used when the client session does not specify it explicitly. If left empty, the default is the operating system time zone. When set at the server side, it’s the default session time zone for attachments. When set at the client side, it’s the default time zone used with client-side API functions.

Allows to (optionally) redirect server’s stdout/stderr streams to some user-defined file. By default, these streams are opened by the server but the output is discarded. Available as a global setting inside firebird.conf.

See Tracker ticket CORE-5788

The Secure Remote Password authentication plugin now uses the SHA-256 algorithm to calculate the client’s proof for both server and client sides (see AuthServer and AuthClient settings in firebird.conf). For backward compatbility, the client is configured to use the old Srp plugin (which implements the SHA-1 algorithm) as a fallback. This setup allows to communicate with Firebird 3 servers that are not configured to use Srp256 (available since v3.0.4).

WireCryptPlugin setting now defaults to ChaCha#20 as a wire encryption algorithm. If the appropriate plugin is missing, then Alleged RC4 (aka ARC4) algorithm is used.

See Tracker ticket CORE-5718

TempCacheLimit, for setting the maximum amount of temporary space that can be cached in memory, can now be configured at database level, i.e., in databases.conf. Previously, it was available only as a global setting for all databases.

See Tracker ticket CORE-6332

New boolean setting UseFileSystemCache provides an explicit control whether the OS filesystem cache is used for the database. The value is customizable at the database level. The old setting FileSystemCacheThreshold is preserved, but it is taken into account only if value for UseFileSystemCache is not specified explicitly. Setting FileSystemCacheThreshold becomes deprecated and will be removed in future Firebird versions.

See Tracker ticket CORE-2650

Controls how non-key fields are processed during sorting: stored inside the sort block or refetched from data pages after the sorting.

Historically, when the external sorting is performed, Firebird writes both key fields (those specified in the ORDER BY or GROUP BY clause) and non-key fields (all others referenced inside the query) to the sort blocks, either stored in memory or swapped to temporary files. Once the sorting is completed, these fields are read back from the sort blocks. This approach is generally considered being faster, because records are read in storage order instead of randomly fetching data pages corresponding to the sorted records. However, if non-key fields are large (e.g. long VARCHARs are involved), this increases the size of the sort blocks and thus causes earlier swapping and more I/O for temporary files. Firebird 4 provides an alternative approach, when only key fields and record DBKEY's are stored inside the sort blocks and non-key fields are refetched from data pages after the sorting. This improves sorting performance in the case of longish non-key fields.

The value specified for InlineSortThreshold defines the maximum sort record size (in bytes) that can be stored inline, i.e. inside the sort block. Zero means that records are always refetched.

The following table lists the names of the valid system privileges that can be granted to and revoked from roles.

At a lower level, a new grantee type SYSTEM PRIVILEGE enables the SYSDBA to grant and revoke specific access privileges on database objects to a named system privilege. For example,

grants to users having USER_MANAGEMENT privilege all rights to the view that is used in the SRP user management plug-in.

To put all this to use, we have some new clauses in the syntax of the CREATE ROLE and ALTER ROLE statements for attaching a list of the desired system privileges to a new or existing role.

To accompany all this delegation of power is a new built-in function, RDB$SYSTEM_PRIVILEGE(). It takes a valid system privilege as an argument and returns True if the current attachment has the given system privilege.

Example

If the optional DEFAULT keyword is included, the role will be used every time the user logs in, even if the role is not specified explicitly in the login credentials. During attachment, the user will get the privileges of all roles that have been granted to him/her with the DEFAULT property. This set will include all the privileges of all the embedded roles that have been granted to the role_name role with the DEFAULT property.

Setting (or not setting) a role in the login does not affect the default role. The set of rights, given (by roles) to the user after login is the union of the login role (when set), all default roles granted to the user and all roles granted to this set of roles.

If a user is to be allowed to grant a role to another user or to another role, the WITH ADMIN OPTION should be included. Subsequently, the user will be able to grant any role in the sequence of roles granted to him, provided every role in the sequence has WITH ADMIN OPTION.

To remove the DEFAULT property of a role assignment without revoking the role itself, include the DEFAULT keyword in the REVOKE statement:

Otherwise, revoking a role altogether from a user is unchanged. However, now a role can be revoked from a role. For example,

Roman Simakov

Tracker ticket CORE-2762

A new built-in function lets the current user check whether a specific role is available under his/her current credentials. It takes a single-quoted role name as a string argument of arbitrary length and returns a Boolean result.

Triggers inherit the setting of the SQL SECURITY property from the table, but it can be overridden explicitly. If the property is changed for a table, triggers that do not carry the overridden property will not see the effect of the change until next time the trigger is loaded into the metadata cache.

To remove an explicit SQL SECURITY option from a trigger, e.g. one named tr_ins, you can run

To set it again to SQL SECURITY INVOKER, run

For encrypting/decrypting data using a symmetric cipher.

Returns an RSA private key of specified length (in bytes) in PKCS#1 format as a VARBINARY string.

Returns the RSA public key for a specified RSA private key. Both keys are in PKCS#1 format.

Pads data using OAEP padding and encrypts it using an RSA public key. Normally used to encrypt short symmetric keys which are then used in block ciphers to encrypt a message.

KEY should be a value returned by the RSA_PUBLIC function. LPARAM is an additional system-specific tag that can be applied to identify which system encrypted the message. Its default value is NULL.

Default is SHA256.

Decrypts using the RSA private key and OAEP de-pads the resulting data.

KEY should be a value returned by the RSA_PRIVATE function. LPARAM is the same variable passed to RSA_ENCRYPT. If it does not match what was used during encryption, RSA_DECRYPT will not decrypt the packet.

Default is SHA256.

Performs PSS encoding of the message digest to be signed and signs using the RSA private key.

message digest is an already hashed message.

KEY should be a value returned by the RSA_PRIVATE function.

Default is SHA256. hash should match the hash algorithm used to generate the message hash value.

SALT_LENGTH indicates the length of the desired salt, and should typically be small. A good value is between 8 and 16.

Performs PSS encoding of message digest to be signed and verifies its digital signature using the RSA public key.

message digest is an already hashed message.

SIGNATURE should be a value returned by the RSA_SIGN_HASH function.

KEY should be a value returned by RSA_PUBLIC function.

Default is SHA256. The hash should match the hash algorithm used to generate the message digest value and the original signature.

SALT_LENGTH indicates the length of the desired salt, and should typically be small. A good value is between 8 and 16.

Alex Peshkov

Tracker ticket CORE-5770

A user that was created with user account administration privileges in the security database (via the …​ GRANT ADMIN ROLE clause) no longer has to hold the RDB$ADMIN role in the connected database and pass it explicitly in order to create, alter or drop other users.

The new statement ALTER EXTERNAL CONNECTIONS POOL has been added to the repertoire for managing the external connections pool.

The syntax is:

When prepared it is described like a DDL statement but its effect is immediate — it is executed immediately and completely, without waiting for transaction commit.

The statements can be issued from any connection, and changes are applied to the in-memory instance of the pool in the current Firebird process. If the process is a Classic one, a change submitted there does not affect other Classic processes.

Changes made with ALTER EXTERNAL CONNECTIONS POOL are not persistent: after a restart, Firebird will use the pool settings configured in firebird.conf by ExtConnPoolSize and ExtConnPoolLifeTime.

Full syntax for the variants follows.

To set the maximum number of idle connections:

Valid values are from 0 to 1000. Setting it to zero disables the pool. The default value is set using the parameter ExtConnPoolSize in firebird.conf.

To set the lifetime of an idle connection:

Valid values are from 1 SECOND to 24 HOUR. The default value (in seconds) is set using the parameter ExtConnPoolLifetime in firebird.conf.

To close all idle connections and instigate dissociation of all active connections so they are immediately closed when they become unused:

To close expired idle connections:

Any error raised by ON DISCONNECT triggers aborts session reset and leaves the session state unchanged. Such errors are reported using primary error code isc_session_reset_err and error text "Cannot reset user session".

Any error raised after ON DISCONNECT triggers (including the ones raised by ON CONNECT triggers) aborts both session reset statement execution and connection itself. Such errors are reported using primary error code isc_session_reset_failed and error text "Reset of user session failed. Connection is shut down.". Subsequent operations on connection (except detach) will fail with isc_att_shutdown error.

Changes the session time zone.

If, for some reason, you need to restrict the maximum size of object names, either globally or for individual databases, two new configuration parameters are available in firebird.conf and/or databases.conf: see Parameters to Restrict Length of Object Identifiers in the Configuration chapter for further details.

Alex Peshkov

Tracker ticket CORE-6342

For details, see Increased Precision for Exact Numeric Types later in this chapter.

Adriano dos Santos Fernandes

Tracker tickets CORE-694

For details, see Data Type Extensions for Time Zone Support later in this chapter.

Alex Peshkov

Tracker ticket CORE-5525

DECFLOAT is an SQL:2016 standard-compliant numeric type that stores floating-point numbers precisely (decimal floating-point type), unlike FLOAT or DOUBLE PRECISION that provide a binary approximation of the purported precision. Firebird 4 accords with the IEEE 754-1985 standard types Decimal64 and Decimal128 by providing both 16-digit and 34-digit precision for this type.

All intermediate calculations are performed with 34-digit values.

The default precision is 34 digits, i.e., if DECFLOAT is declared with no parameter, it will be defined as DECFLOAT(34). Storage complies with IEEE 754, storing data as 64 and 128 bits, respectively.

Alex Peshkov

Fixed types NUMERIC and DECIMAL can now be defined with up to 38 digits precision. Any value with precision higher than 18 digits will be stored as a 38-digit number. There’s also an explicit INT128 integer data type with 128-bit (up to 38 decimal digits) storage.

where P is precision (P <= 38, previously limited to 18 digits), and the optional S is scale, as previously, i.e., the number of digits after the decimal separator.

Storage for P >= 19 is a 128-bit signed integer.

Mark Rotteveel

FLOAT data type was enhanced to support precision in binary digits as defined in the SQL:2016 specification. The approximate numeric types supported by Firebird are a 32-bit single precision and a 64-bit double precision binary floating-point type. These types are available with the following SQL standard type names:

In addition the following non-standard type names are supported:

These non-standard type names are deprecated and they may be removed in a future version.

Adriano dos Santos Fernandes

The syntax for declaring the data types TIMESTAMP and TIME has been extended to include arguments defining whether the column, domain, parameter or variable should be defined with or without time zone adjustments, i.e.,

Data of types TIME/TIMESTAMP WITH TIME ZONE are stored respectively with the same storage as TIME/TIMESTAMP WITHOUT TIME ZONE plus two extra bytes for the time zone identifier or displacement.

The default for both TIME and TIMESTAMP is WITHOUT TIME ZONE.

See also Management Statements Pertaining to Time Zone Support in the Management Statements chapter.

Dimitry Sibiryakov

Tracker ticket CORE-5064

Data types named BINARY(n), VARBINARY(n) and BINARY VARYING(n) have been added to the lexicon as optional aliases for defining string columns in CHARACTER SET OCTETS.

BINARY(n) is an alias for CHAR(n) CHARACTER SET OCTETS, while VARBINARY(n) and BINARY VARYING(n) are aliases for VARCHAR(n) CHARACTER SET OCTETS and for each other.

Adriano dos Santos Fernandes

An IDENTITY column is one that is formally associated with an internal sequence generator and has its value set automatically when omitted from an INSERT statement.

The IDENTITY sub-type was introduced in Firebird 3 and has undergone a number of extensions in version 4, including implementation of DROP IDENTITY, the GENERATED ALWAYS and OVERRIDE directives, and the INCREMENT BY option.

Vlad Khorsun

Input parameters of the EXECUTE STATEMENT command may be prefixed by the EXCESS keyword. If EXCESS is specified, then the given parameter may be omitted from the query text.

Dmitry Yemanov

Once replication is set up in the replication.conf configuration file, it can be enabled/disabled at runtime using the special extension to the ALTER DATABASE statement. Also, the replication set (i.e. tables to be replicated) can be customized using the extensions to the ALTER DATABASE and CREATE/ALTER TABLE statements.

Since version 2.1, Firebird has supported the DEFAULT VALUES clause. The two clauses are not the same. The DEFAULT clause applies to an individual column in the VALUES list, while DEFAULT VALUES applies to the row to be inserted as a whole. A statement like INSERT INTO sometable DEFAULT VALUES is equivalent to INSERT INTO sometable VALUES (DEFAULT, …​) with as many DEFAULT in the VALUES list as there are columns in sometable.

Tracker ticket CORE-3647

A frame can be specified, within which certain window functions are to work.

The following extract from the syntax pattern above explains the elements that affect frames:

The frame comprises three pieces: unit, start bound and end bound. The unit can be RANGE or ROWS and defines how the bounds will work. The bounds are:

UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING work identically with RANGE and ROWS. UNBOUNDED PRECEDING looks for the first row and UNBOUNDED FOLLOWING the last one, always inside the partition.

The frame syntax with <window frame start> specifies the start frame, with the end frame being CURRENT ROW.

Some window functions discard frames:

Tracker ticket CORE-5346

In a query with the WINDOW clause, a window can be explicitly named to avoid repetitive or confusing expressions.

A named window can be used

In a query with multiple SELECT and WINDOW clauses (for example, with subqueries), the scope of the window name is confined to its query context. That means a window name from an inner context cannot be used in an outer context, nor vice versa. However, the same window name definition can be used independently in different contexts.

Adriano dos Santos Fernandes; Hajime Nakagami

Tracker ticket CORE-1688

More SQL:2003 window functions — the ranking functions PERCENT_RANK, CUME_DIST and NTILE.

Ranking functions compute the ordinal rank of a row within the window partition. The basic functions in this category, present since Firebird 3, are DENSE_RANK, RANK and ROW_NUMBER. These function enable creation of various types of incremental counters to generate sets in ways that are analogous with operations such as SUM(1) OVER (ORDER BY SALARY).

The new functions implemented in Firebird 4 are:

The following example illustrates the behaviour of ranking functions. SUM is included for comparison.

The result set looks something like the following, although trailing zeroes have been truncated here in order to fit the lines to the document page:

Built-in functions and expressions added in Firebird 4.0.