Submitted by: PEAKTOP (peaktop)

Votes: 1

The database consists of two files:
1) MYDATABASE.FDB - contains all user data
2) MYDATABASE_BLOB.FDB - contains only BLOB field values.

--------------------------------------------------------------
2) Here is MYDATABASE_BLOB.FDB DDL

CREATE TABLE TABL$R_CS_PHOTO (
CS_ID INTEGER NOT NULL,
ID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR(255),
FLAG_DELETE SMALLINT DEFAULT 0,
DATE_COMMIT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PHOTO BLOB SUB_TYPE 0 SEGMENT SIZE 8192
);

1) Here is MYDATABASE.FDB DDL

CREATE TABLE TABL$R_CS_PHOTO (
CS_ID INTEGER NOT NULL,
ID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR(255),
FLAG_DELETE SMALLINT DEFAULT 0,
DATE_COMMIT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PHOTO BLOB SUB_TYPE 0 SEGMENT SIZE 8192
);

CREATE OR ALTER TRIGGER TRIG$R_CS_PHOTO_BIU_240 FOR TABL$R_CS_PHOTO
ACTIVE BEFORE INSERT OR UPDATE POSITION 240
AS
DECLARE VARIABLE P_DB_BLOB TYPE OF COLUMN TABL$R_FILIALS.DATABASE_NAME_REMOTE;
DECLARE VARIABLE P_SQL_STMT TYPE OF COLUMN TABL$J_4.DOCSTR;
BEGIN
P_DB_BLOB = 'MYDATABASE_BLOB.FDB';

IF(NEW.PHOTO IS NULL)THEN
BEGIN
P_SQL_STMT =
'UPDATE OR INSERT INTO TABL$R_CS_PHOTO (CS_ID, ID, NAME, FLAG_DELETE, DATE_COMMIT) '||
' VALUES (?Q_CS_ID, ?Q_ID, ?Q_NAME, ?Q_FLAG_DELETE, ?Q_DATE_COMMIT) '||
' MATCHING(ID) ';
EXECUTE STATEMENT (:P_SQL_STMT)(
Q_CS_ID := NEW.CS_ID
,Q_ID := http://NEW.ID
,Q_NAME := http://NEW.NAME
,Q_FLAG_DELETE := NEW.FLAG_DELETE
,Q_DATE_COMMIT := NEW.DATE_COMMIT
)ON EXTERNAL DATA SOURCE :P_DB_BLOB AS USER 'SYSDBA' PASSWORD 'masterkey';
END
ELSE
BEGIN
P_SQL_STMT =
'UPDATE OR INSERT INTO TABL$R_CS_PHOTO (CS_ID, ID, NAME, FLAG_DELETE, DATE_COMMIT, PHOTO) '||
' VALUES (?Q_CS_ID, ?Q_ID, ?Q_NAME, ?Q_FLAG_DELETE, ?Q_DATE_COMMIT, ?Q_PHOTO) '||
' MATCHING(ID) ';
EXECUTE STATEMENT (:P_SQL_STMT)(
Q_CS_ID := NEW.CS_ID
,Q_ID := http://NEW.ID
,Q_NAME := http://NEW.NAME
,Q_FLAG_DELETE := NEW.FLAG_DELETE
,Q_DATE_COMMIT := NEW.DATE_COMMIT
,Q_PHOTO := NEW.PHOTO
)ON EXTERNAL DATA SOURCE :P_DB_BLOB AS USER 'SYSDBA' PASSWORD 'masterkey';
NEW.PHOTO = NULL;
END
END
------------------------------

Description:

When the client application inserts records into table TABL$R_CS_PHOTO the BLOB records, I import them into an external database 'MYDATABASE_BLOB.FDB'. In the main database 'MYDATABASE.FDB', I had to assign BLOB field value to NULL.

But, the main database 'MYDATABASE.FDB' continues to grow when inserting new records. Every record was inserted with separate transaction.

It seems like: the Firebird-Engine does not mark the pages with NULL-BLOB as deleted.