key size exceeds implementation restriction for index on expression with UDF call

Question

Firebird allows indexing on expressions since version 2.0. That includes using calls to user defined functions (UDF).

Currently, I am trying to add an expression index to this table:

CREATE TABLE M_ADSN_STRING_DATA (
    ID             DMN_AUTOINC NOT NULL /* DMN_AUTOINC = INTEGER NOT NULL */,
    CLTREF         DMN_REFID /* DMN_REFID = INTEGER NOT NULL */,
    ATTRIBUTEDATA  DMN_AFT_STRING /* DMN_AFT_STRING = VARCHAR(320) NOT NULL */
);

/******************************************************************************/
/****                          Unique constraints                          ****/
/******************************************************************************/

ALTER TABLE M_ADSN_STRING_DATA ADD CONSTRAINT UNQ_M_ADSN_STRING_DATA UNIQUE (CLTREF, ATTRIBUTEDATA);


/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE M_ADSN_STRING_DATA ADD CONSTRAINT PK_M_ADSN_STRING_DATA PRIMARY KEY (ID);


/******************************************************************************/
/****                             Foreign keys                             ****/
/******************************************************************************/

ALTER TABLE M_ADSN_STRING_DATA ADD CONSTRAINT FK_M_ADSN_STRING_DATA_CLT FOREIGN KEY (CLTREF) REFERENCES M_CLIENT (ID) ON DELETE CASCADE ON UPDATE CASCADE;


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX M_ADSN_STRING_DATA_AD_UC ON M_ADSN_STRING_DATA COMPUTED BY (UPPER(ATTRIBUTEDATA));

Note, that it already has an expression index called M_ADSN_STRING_DATA_AD_UC.

The index I want to use should look like this:

CREATE INDEX M_ADSN_STRING_DATA_AD_DIG
  ON M_ADSN_STRING_DATA
  COMPUTED BY (F_DIGITS(ATTRIBUTEDATA));

Unfortunately, this gives me an error message.

Unsuccessful metadata update key size exceeds implementation restriction for index "M_ADSN_STRING_DATA_AD_DIG"

I read Firebird FAQ entrys #213 and #211, and this SO question as well. F_DIGITS is a UDF of FreeAdhocUDF library. Initially, it was declared as

DECLARE EXTERNAL FUNCTION F_DIGITS
        CSTRING(32760)
RETURNS CSTRING(32760) FREE_IT
ENTRY_POINT 'digits' MODULE_NAME 'FreeAdhocUDF';

As my maximum input and output length is only 320 chars, I changed it to

DECLARE EXTERNAL FUNCTION F_DIGITS
    CSTRING(320)
RETURNS CSTRING(320) FREE_IT
ENTRY_POINT 'digits' MODULE_NAME 'FreeAdhocUDF';

to fit the index size requirements. My databases pagesize is 16384. So, I'd think, my key can be up to 4096 bytes.

The domain DMN_AFT_STRING of column ATTRIBUTEDATA is declared as

CREATE DOMAIN DMN_AFT_STRING AS
VARCHAR(320) CHARACTER SET ISO8859_1
NOT NULL
COLLATE DE_DE_CS_SF;

Why does the key size exceed?

score 1 · Accepted Answer · answered Jan 24 '17 at 08:09

1

Long story short: Have you tried to turn it off and on again?

It looks like one has to disconnect and connect after changing the UDF declaration and before adding the expression index.

Now, it works properly. The key size does not exceed anymore.

answered Jan 24 '17 at 08:09

René Hoffmann

2,766
2
20
43

yep, metadata cache is sometimes transactions bound and sometimes not, and also different between superserver and classic. Once you called that function, it got "warmed up". However.... Are you sure your change is acceptable ? you sliced down memory buffer of the function without actually changing its implementation. I wonder.... Maybe i'd try to set index on `cast (myfunction(x) as VarChar(320))` explicitly trimming the buffer in SQL realm, not at the SQL/binary boundary. – Arioch 'The Jan 24 '17 at 11:12
I think the change is okay. I know how the UDF is implemented. It is totally independent in terms of input and output length. – René Hoffmann Jan 24 '17 at 12:48

key size exceeds implementation restriction for index on expression with UDF call

1 Answers1