1

Topic: The basis from BLOB swells

The input:
Is ABS (Automated Bank System) in which photos of clients, and also skan-copies of their passports among other things are added.
That not  the basis containing Financial Data, the separate basis for BLOB is created. Thus, to deceive the client application, stacking in separate basis of photos happens in triggers through Cross-Database requests.

-- Pictures I store so:
CREATE DOMAIN DOMN$BLOB_IMAGE AS BLOB SUB_TYPE 0 SEGMENT SIZE [PAGE_SIZE bases];
-- This table of photos, is in both bases
CREATE TABLE TABL$R_CS_PHOTO (
CS_ID DOMN$INTEGER_ID, - the code of the client
ID DOMN$INTEGER_ID, - the photo code, strictly trigger through GEN_ID
NAME DOMN$PSTRING, - the remark
FLAG_DELETE DOMN$BOOLEAN, - a mark on removal
DATE_COMMIT DOMN$DATETIME, - date/time = TIMESTAMP
PHOTO DOMN$BLOB_IMAGE - a picture
);
-- Flag in a database that it is necessary to store a photo in exterior basis
CREATE GENERATOR GENR$R_CS_PHOTO_EXTERNAL;
-- Further in the basis goes cross-database request
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
IF (GEN_ID (GENR$R_CS_PHOTO_EXTERNAL, 0) <> 1) THEN EXIT;
SELECT FIRST 1 P.DATABASE_PATH FROM PROC $ _ DB_PATH_BLOB P INTO:P_DB_BLOB;
IF (TRIM (:P_DB_BLOB) = ") THEN EXIT;
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: = NEW.ID
,Q_NAME: = 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: = NEW.ID
,Q_NAME: = 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

The description:
I at level of triggers intercept BLOB, I rick it in separate basis, and in the main for-NULL-jaju.
Expected behavior:
Since the photo at first gets to the basis the basis on DatabaseGrowIncrement from a config swells up a little to contain a photo, the hell with her.
Then it stores all in the exterior.
Actual behavior:
The basis all the same swells with very great speed.
Here, for a week on separation it swelled to 2,5 a basis file.
Ordinary backup-restore resulted basis in a normal state 403 MB.
A question:
I so understood, what my decision to add a photo in separate basis for this point in question (growth of a file of a DB) - does not help?

2

Re: The basis from BLOB swells

I will specify a question:
It turns out, what remote pages Firebird repeatedly does not use, if allows to contain a place the data?
It all the same captures the new?

3

Re: The basis from BLOB swells

Did not check, but such impression that assignment NULL in a blob-field in the trigger does not lead . To removal .

4

Re: The basis from BLOB swells

You can make  such swelled DB?

5

Re: The basis from BLOB swells

hvlad wrote:

you Can make  such swelled DB?

gfix.exe-validate...... >1.log
gfix.exe-validate-full .....>full.log
Produce empty files. To the console too write nothing.
Version 2.5.7.27050, collected itself standard BAT "" by means of M $ a Visual Studio 2010

6

Re: The basis from BLOB swells

PEAKTOP wrote:

produce empty files.

firebird.log read.

7

Re: The basis from BLOB swells

Dimitry Sibiryakov;
And there generally anything is not present, except a standard socket-error

10054 unable to complete network request to <a name of the local machine>

Something stablly arising time a day somewhere at 18:00. 18:30.
I on it hammered.
It is more - anything.
---------
And load a photo in basis permanently from 07:00 till.

8

Re: The basis from BLOB swells

PEAKTOP wrote:

it is more - anything.

it is strange.
I will try  a bit later.

9

Re: The basis from BLOB swells

One more example in studio.
So, earlier we analyzed a database of remote "Separation".
Among other things, is also "GlavnajaBaza" which are the algebraic total of bases of "Separations".
Through Cross-Database requests in "GlavnujuBazu" the scheduler of jobs through BAT+isql on the timer tightens the data from all remote "Separations", including, and photos of clients.
The mechanism of storage of photos in a separate database is similarly implemented, i.e. near to a file "GlavnajaBaza.FDB" the monster of "GlavnajaBaza_BLOB.FDB"  on 100 lies.
Regular file size of "GlavnajaBaza.FDB" of the order 4 .
After a week of operation the file swelled to 8.
Ordinary backup-restore returns all on a place, the size again the order 4 .
---
I.e. on a course it turns out that pages for-NULL-ennogo  a kernel are not marked, as remote.
Or the kernel does not want for some reason them to reuse then.

10

Re: The basis from BLOB swells

And leak  on the second DB cannot be?
There are any utilities to look how many active BLOB HANDLE is selected by the server?
There can be there a client does not close transaction or does update or insert.... RETURNING BLOB_COLUMN?
Well and,  if to RESTART service Firebird which is responsible for a DB, and to set on this DB / these tables   sweep?

11

Re: The basis from BLOB swells

Arioch wrote:

and leaks  on the second DB cannot be?

On the first, certainly

12

Re: The basis from BLOB swells

PEAKTOP;
Write, probably, in . With a simple example - it would be ideal.

13

Re: The basis from BLOB swells

Meanwhile I can advise only as it to bypass.
Or and itself you know? smile

14

Re: The basis from BLOB swells

hvlad wrote:

Meanwhile I can advise only as it to bypass.
Or and itself you know? smile

Yes on figs it is necessary still this hogwash .
At 02:00 the robot does now not only backup, but also restore.
While so.
And there, further - "either the donkey dies, or the padishah". smile

15

Re: The basis from BLOB swells

hvlad wrote:

write, probably, in . With a simple example - it would be ideal.

Well, went in .
The simple example, probably, not to turn out, it is necessary to result full DDL-SQL as it has been written above.

16

Re: The basis from BLOB swells

PEAKTOP wrote:

I at level of triggers intercept BLOB, I rick it in separate basis, and in the main for-NULL-jaju.

I would advise to re-read
http://www.ibase.ru/dbgrowth/

17

Re: The basis from BLOB swells

Read once.
, article actual, but a little incomplete.
I would add (purely from practice) there zero point:
0. To look the mechanism !!! 
I.e. if in basis pens, a script or   triggers like

CREATE OR ALTER TRIGGER TRG$MY_TABLE_ADIU_255 FOR MY_TABLE
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 255
AS
BEGIN
INSERT INTO MY_LOG_TABLE (<FIELDS>)
VALUES (<VALUES>);
END

It is necessary to ask a question: instead of whether was mass UPDATE any mega-table?
Practically in 99 % of cases there was any re-structuring of a DB (added a field and decided to initialize), and  forgot to disconnect.

18

Re: The basis from BLOB swells

PEAKTOP;
In article speech not about single cases on which it is possible to spit. And about constant growth of a DB because of temporal .

19

Re: The basis from BLOB swells

kdv wrote:

And about constant growth of a DB because of temporal .

Formally, they at it should not breed, because outside they do not leave the trigger (exterior connection lives "in" the trigger and consequently too it is not counted).
And it is real - it seems that in FB somewhere passed operation with counters of links in BLOB handle.
As it is good in Delphi where it is operation the compiler is engaged:-D

20

Re: The basis from BLOB swells

Arioch wrote:

And it is real - it seems that in FB somewhere passed operation with counters of links in BLOB handle.

There is no counter of links.

21

Re: The basis from BLOB swells

Arioch wrote:

it is formal, they at it should not breed, because outside they do not leave the trigger

What means "formally"? At it to the trigger there come record contents, with .  not in air exists, it is written already down as temporary. Well appropriated to it null in the trigger, and what? If  temporary so it is deleted or at transaction closing, or at connection closing.
By the way, the played back example could be made easily. In sense to make nulling  in the trigger, and update th to push there permanently kilobyte till 10-20. Well and to look, when swells, and when it is released.

22

Re: The basis from BLOB swells

23

Re: The basis from BLOB swells

Masterkeevich wrote:

About as happens!

24

Re: The basis from BLOB swells

hvlad wrote:

it is passed...
About as happens!

The second five-years period ("to operation") i.e. already went to this side.:D

25

Re: The basis from BLOB swells

Guest7777;
I do not see it in