Killing a blocked ALTER ... RESTART WITH can leave a primary key index allowing duplicate values

[LouisCaous]

Description

A primary key index can be left in an inconsistent state when an ALTER TABLE ... ALTER <identity> RESTART WITH statement is blocked by another transaction and the connections are terminated.

Afterwards, Firebird allows duplicate primary key values to be inserted and committed.

I can reproduce this using only ISQL with a newly created database and a minimal table. I originally encountered the issue on Firebird 3.0.7 and reproduced it on Firebird 4.0.7.3271 and Firebird 5.0.4.1812

Steps to reproduce

Start Session 1 using ISQL and create a new database:

CREATE DATABASE  
    'localhost/3050:D:\Databases\Firebird\5\IdentityTest.fdb'  
    USER 'SYSDBA'  
    PASSWORD 'masterkey'  
    DEFAULT CHARACTER SET UTF8;

COMMIT;

CONNECT 'localhost/3050:D:\Databases\Firebird\5\IdentityTest.fdb' USER 'SYSDBA' PASSWORD 'masterkey';

CREATE TABLE TEST (  
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY,  
    TESTVALUE INTEGER,  
    CONSTRAINT PK_TEST PRIMARY KEY (ID)  
);

COMMIT;

In the same session, insert a row but do not commit the transaction:

INSERT INTO TEST (
    TESTVALUE
)
VALUES (
    123
);

Leave Session 1 open.

Start a second ISQL process (Session 2) and connect:

CONNECT
    'localhost/3050:D:\Databases\Firebird\5\IdentityTest.fdb'
    USER 'SYSDBA'
    PASSWORD 'masterkey';

Execute:

ALTER TABLE TEST
    ALTER ID RESTART WITH 1001;

The statement waits because of the open transaction in Session 1.

While the ALTER TABLE statement is waiting:

1. Terminate the Session 2 ISQL process.
2. Then terminate the Session 1 ISQL process.

Start a new ISQL process (Session 3) and connect to the database:

CONNECT
    'localhost/3050:D:\Databases\Firebird\5\IdentityTest.fdb'
    USER 'SYSDBA'
    PASSWORD 'masterkey';

Insert a row with an explicit primary key value and commit:

INSERT INTO TEST (
    ID,
    TESTVALUE
)
VALUES (
    50000,
    1
);

COMMIT;

Now insert another row with the same primary key value:

INSERT INTO TEST (
    ID,
    TESTVALUE
)
VALUES (
    50000,
    2
);

COMMIT;

Expected result

The second insert should fail with a primary key violation because ID = 50000 already exists.

Something similar to:

violation of PRIMARY or UNIQUE KEY constraint "PK_TEST" on table "TEST"
Problematic key value is ("ID" = 50000)

Actual result

The second insert succeeds and the transaction can be committed.

Both rows exist:

 

SELECT
    ID,
    TESTVALUE
FROM TEST
WHERE ID = 50000;

Result:

ID       TESTVALUE
=======  =========
50000    1
50000    2

The duplicate rows remain present after disconnecting/reconnecting to the database and after restarting the Firebird server.

The primary key constraint and its unique index are still present in the metadata.

For example:

SELECT
    rc.RDB$CONSTRAINT_NAME,
    rc.RDB$CONSTRAINT_TYPE,
    rc.RDB$INDEX_NAME,
    i.RDB$UNIQUE_FLAG,
    i.RDB$INDEX_INACTIVE
FROM RDB$RELATION_CONSTRAINTS rc
JOIN RDB$INDICES i
    ON i.RDB$INDEX_NAME = rc.RDB$INDEX_NAME
WHERE rc.RDB$RELATION_NAME = 'TEST';

shows PK_TEST as a PRIMARY KEY backed by a unique index.

Reproducibility

The issue is reproducible with a fresh database using only ISQL; no third-party database client is involved.

I originally encountered the problem with Firebird 3.0.7 and have subsequently reproduced it with:

• Firebird 4.0.7.3271
• Firebird 5 .0.4.1812

The original case involved DBWorkbench, but the minimal ISQL reproduction above demonstrates that DBWorkbench is not required.

Impact

After the interrupted/terminated ALTER TABLE ... RESTART WITH the database continues operating normally, but the primary key no longer reliably enforces uniqueness. Duplicate primary key values can be inserted and committed without an error.

This is particularly concerning because there is no indication during the subsequent INSERT or COMMIT that the primary key index is in an inconsistent state.

Additional diagnostics

Additional diagnostics

Before reproducing the issue, gstat -i reports:

    Index PK_TEST (0)
        Root page: 227, depth: 1, leaf buckets: 1, nodes: 0

After reproducing the issue, the same index is reported as:

    Index PK_TEST (0)
        Root page: 0, depth: 0, leaf buckets: 0, nodes: 0

However, metadata still reports the index as active and unique:

    RDB$UNIQUE_FLAG = 1
    RDB$INDEX_INACTIVE = 0

An INSERT into a child table referencing PK_TEST then fails with:

internal Firebird consistency check
(partner index description not found (175), file: idx.cpp line: 1901)

[AlexPeshkoff]

How does it feel with fb6?

[LouisCaous]

I didn't try it with FB6 since AFAIK it's still beta.