BLOB parameters in batch commands are disposed before 'AFTER INSERT' trigger is run

[andrewjw1995]

I'm using Firebird HQBird 5.0.4 with the .Net provider. We have encountered an issue where batch commands seem to be cleaning up blob parameters before the 'AFTER INSERT' trigger is run.

Steps to reproduce:

• Create a table with a BLOB column
• Create an 'AFTER INSERT' trigger on the table which reads the new value of the blob column
• Execute a batch command to insert a row into the table

This seems specific to the FbBatchCommand implementation. Running a normal command works as expected. We had to switch to batch commands for performance reasons when inserting large amounts of data into our table.

We've tried to reproduce this running Firebird in a docker container but it's working as expected. We are able to consistently reproduce this in our production and shared dev environments and are still trying to identify the exact conditions to reproduce. I'll update this issue once we find it.

Here's an example schema:

CREATE TABLE EXAMPLE_X (
    "SAMPLE_ID" INTEGER NOT NULL,
    "VALUE" BLOB SUB_TYPE TEXT,
    CONSTRAINT PK_EXAMPLE_X PRIMARY KEY ("SAMPLE_ID")
);

CREATE TABLE EXAMPLE_X_LOG (
    "TIMESTAMP" TIMESTAMP NOT NULL,
    "OLD_VALUE" BLOB SUB_TYPE TEXT,
    "NEW_VALUE" BLOB SUB_TYPE TEXT,
    CONSTRAINT PK_EXAMPLE_X_LOG PRIMARY KEY ("TIMESTAMP")
);

CREATE TRIGGER EXAMPLE_X_AFTER_INSERT FOR EXAMPLE_X AFTER INSERT
AS
BEGIN
    INSERT INTO EXAMPLE_X_LOG ("TIMESTAMP", "OLD_VALUE", "NEW_VALUE")
    VALUES (LocalTimeStamp, NULL, NEW."VALUE");
END;

And a simple program to demonstrate the problem:

using FirebirdSql.Data.FirebirdClient;

using var connection = new FbConnection("Database=localhost:/var/lib/firebird/data/MyDatabase.fdb;User=SYSDBA;Password=P@ssw0rd");
await connection.OpenAsync();

using var command = new FbBatchCommand
{
    Connection = connection,
    CommandText = "UPDATE OR INSERT INTO EXAMPLE_X (SAMPLE_ID, \"VALUE\") VALUES (@p0, @p1);",
};

var parameters = command.AddBatchParameters();
parameters.Add("p0", FbDbType.Integer).Value = 1;
parameters.Add("p1", FbDbType.Text).Value = "";

var results = command.ExecuteNonQuery();
results.EnsureSuccess();

This example throws an exception with the following message from Firebird:

BLOB not found
At trigger 'EXAMPLE_X_AFTER_INSERT' line: 5, col: ...

[mrotteveel]

If you have this problem with HQBird, but can't reproduce it with Firebird, you should probably report this to IBSurgeon instead.

[sim1984]

If you have this problem with HQBird, but can't reproduce it with Firebird, you should probably report this to IBSurgeon instead.

In this case, the behavior is unlikely to differ between HQbird and Firebird

[mrotteveel]

In this case, the behavior is unlikely to differ between HQbird and Firebird

@sim1984 Yet the initial comment states they have this problem with HQbird, and cannot reproduce it with Firebird on Docker. So unless this is also reproducible with Firebird, signs point to this problem being HQbird specific.

[andrewjw1995]

Yes, I believe this is specific to HQBird. We've upgraded to the latest version and the problem has been resolved. I'm going to close this.

[andrewjw1995]

From HQBird's website (emphasis mine):

HQbird includes a lot of performance optimizations at the engine level, like optimizer improvements, BLOB operations optimization, smart sweep and maintenance to clean old garbage versions and keep databases fast

It seems like they introduced a regression and then fixed it in a later version

[mrotteveel]

Thanks for confirming, and good to hear it's already solved in HQbird.