Check your database for new reserved words in identifiers, columns, and variables. In the first SQL dialect, such words cannot be used at all (you will have to rename them), in the third - they can be used, but must be enclosed in double quotes.

You can find the list of new keywords and reserved words in the Release Notes 3.0 and 4.0 in the chapter "Reserved Words and Changes". Keywords can be used as identifiers, although this is not recommended.

Starting from Firebird 5.0, you can view the full list of keywords and reserved words using the query:

This query can be executed on any database with ODS 13.1, for example on employee.db, included in the Firebird 5.0 distribution.

The column RDB$KEYWORD_NAME contains the keyword itself, and RDB$KEYWORD_RESERVED - the flag whether the keyword is reserved.

Relevant: when migrating from Firebird 2.5.

All output columns in PSQL cursors declared as DECLARE CURSOR must have an explicit name or alias. The same applies to PSQL cursors used as FOR SELECT …​ AS CURSOR <cursor name> DO …​.

Relevant: when migrating from Firebird versions 2.5, 3.0.

Firebird 4.0 introduces new data types:

The last two types do not cause much trouble, since you did not use them before, and usually expressions do not return them.

Some expressions can now return types NUMERIC(38, x), DECIMAL(38, x) and INT128. We will talk about solving this problem later, since at the stage of changing the ODS they usually do not manifest themselves.

Expressions CURRENT_TIMESTAMP and CURRENT_TIME now return types TIMESTAMP WITH TIME ZONE and TIME WITH TIME ZONE.

For old client libraries and applications, you can set the data type compatibility mode, but this will not help inside stored procedures, functions, and triggers. You need to use expressions LOCALTIMESTAMP and LOCALTIME instead of CURRENT_TIMESTAMP and CURRENT_TIME where you do not want to get data types with time zones. These expressions were specially introduced in the corrective releases Firebird 2.5.9 and Firebird 3.0.4, so that you could prepare your databases for migration to Firebird 4.0 and higher in advance.

When assigning a variable (column) of type TIMESTAMP the value of the expression CURRENT_TIMESTAMP, a type conversion will be performed, that is, an implicit CAST(CURRENT_TIMESTAMP AS TIMESTAMP), so even without replacing CURRENT_TIMESTAMP and CURRENT_TIME with LOCALTIMESTAMP and LOCALTIME everything will continue to work, but performance in some cases may drop. For example:

Here the field t has the type TIMESTAMP, and CURRENT_TIMESTAMP returns TIMESTAMP WITH TIME ZONE, which reduces the performance of INSERT into such a table.

Initially, the performance drop was 30%, which is quite significant, but after a series of optimizations, the overhead was reduced to 3-5%.If you do not want extra costs, it is better to use LOCALTIMESTAMP where it is not supposed to operate with time with a time zone.

Relevant: when migrating from Firebird versions 2.5, 3.0.

In Firebird 4.0, the syntax of date and time literals is tightened.

Literals 'NOW', 'TODAY', 'TOMORROW', 'YESTERDAY' with prefixes TIMESTAMP, DATE, TIME are now prohibited.The fact is that the value of such literals was calculated at the time of preparing the DSQL query or compiling the PSQL modules, which led to unexpected results.

If something like TIMESTAMP 'NOW' was used in DSQL queries in the application code or in the transferred PSQL, there will be a compatibility problem with Firebird 4 and higher.

You need to clean up such literals, for example, replace them with an explicit conversion CAST('NOW' AS TIMESTAMP), in the code of your procedures and functions before converting your database to a new ODS.

In addition, you need to check other date and time literals with an explicit assignment of a known date time. Previously, separators of parts of the date and time that did not correspond to the standard were allowed in such literals. Now such separators are prohibited. You can read more about the allowed formats of date and time literals in the "Firebird 5.0 SQL Language Reference" in the chapter "Date and Time Literals".

Relevant: when migrating from Firebird versions 2.5, 3.0.

Starting from Firebird 4.0, if any INSERT statement contains a RETURNING clause that references columns of the base table, the calling party must be granted the corresponding SELECT privilege.

Relevant: when migrating from Firebird 2.5.

In Firebird 3.0, an important improvement was made, which is called "cursor stability". As a result of this improvement, some queries may work differently. Thisprimarily concerns queries that modify the table and read it in the same cursor. Cursor stability allows you to eliminate many errors that were present in previous versions of Firebird, the most famous of which is the infinite loop in the query:

It is unlikely that your applications contain exactly such queries, however, cursor stability can manifest itself in not quite obvious cases:

If your code contains such cases, we recommend rewriting these parts taking into account the effect of "cursor stability".

After the restore, you may see the following warnings in the restore.log file

This means that you have UDFs that are declared in the database, but their library is missing. It has already been described above what to do in this case. But this mainly concerned your UDF libraries. However, if you used UDFs from the package supplied with Firebird, namely ib_udf and fbudf, then you can replace them with built-in functions or with safe UDR analogues located in the udf_compat.dll library. To do this, you need to run the SQL migration script supplied with Firebird 5.0, which is located in misc/upgrade/v4.0/udf_replace.sql. This is done by the following command

If you are migrating from Firebird 4.0, there is a faster way to upgrade ODS than backup/restore.

The traditional way of upgrading ODS (On-Disk Structure) is to perform a backup on the old version of Firebird and a restore on the new one. This is a rather lengthy process, especially on large databases.

However, in the case of upgrading a minor version of ODS (the number after the dot) backup/restore is redundant (it is only necessary to add the missing system tables and fields, as well as some packages). An example of such an upgrade is upgrading ODS 13.0 (Firebird 4.0) to ODS 13.1 (Firebird 5.0), since the major version of ODS 13 remained the same.

Starting from Firebird 5.0, there is a possibility to upgrade the minor version of ODS without the lengthy backup and restore operations. For this, the gfix utility is used with the -upgrade switch.

Key points:

Thus, for a fast ODS upgrade, you need to do the following steps:

This way of upgrading ODS, unlike backup/restore, takes seconds (we are talking about gfix -upgrade), not minutes or hours.

Relevant: when migrating from Firebird versions 2.5, 3.0.

Starting from Firebird 4.0, a new access method Refetch was introduced, which allows to optimize the sorting of wide data sets. A wide data set is a data set in which the total length of the record fields is large.

Historically, when performing external sorting, Firebird writes both key fields (i.e., those specified in the ORDER BY or GROUP BY clause) and non-key fields (all other fields that have references within the query) to sorting blocks, which are either stored in memory or in temporary files. After the sorting is completed, these fields are read back from the sorting blocks. Usually this approach is considered faster, since records are read from temporary files in the order corresponding to the sorted records, rather than selected randomly from the data page. However, if the non-key fields are large (for example, long VARCHARs are used), this increases the size of the sorting blocks and, thus, leads to more I/O operations for temporary files. Firebird 4 offers an alternative approach (the Refetch access method), when only key fields and DBKEY records are stored inside the sorting blocks, and non-key fields are extracted from the data pages after sorting. This improves the performance of sorting in the case of long non-key fields.

Thus, the plans of your queries using sorting may change. To control this access method, a new configuration parameter InlineSortThreshold was introduced. The value specified for InlineSortThreshold determines the maximum size of the sorting record (in bytes) that can be stored inline, i.e. inside the sorting block. Zero means that records are always refetched. The optimal value of this parameter should be determined experimentally. The default value is 1000 bytes.

Consider the following example:

Before Firebird 4.0, all 4 fields were always included in the sorting blocks. Starting from Firebird 4.0, if the total length of the fields field_1 .. field_4 exceeds the value of InlineSortThreshold, then only field_1 will be included in the sorting blocks, and then Refetch will be performed.

There are a number of problems with optimizing OUTER JOINs in Firebird.

First, currently OUTER JOIN can only be performed by one join algorithm NESTED LOOP JOIN, which may be changed in future versions.

Second, when joining streams with outer joins, the join order is strictly fixed, i.e., the optimizer cannot change it to keep the result correct.

However, if there is a predicate in the WHERE condition for the field of the "right" (joined) table, which explicitly does not handle the NULL value, then there is no point in the outer join. In this case, starting from Firebird 5.0, such a join will be converted to an inner one, which allows the optimizer to apply the full range of available join algorithms.

Suppose you have the following query:

In Firebird 5.0, such a query will be implicitly converted to an equivalent form:

If LEFT JOIN was used as a hint to indicate the join order very actively, then rewriting a lot of queries in a new way may be problematic. For such developers, there is a configuration parameter OuterJoinConversion in firebird.conf or database.conf. Setting the parameter OuterJoinConversion to false disables the transformation of Outer Join to inner join. Note that this parameter is a temporary solution to facilitate migration and may be removed in future versions of Firebird.