At 08:45 PM 25/10/2005 +0000, you wrote:

>Hello,
>At page 263 of Operation Guide I've found script to generate
>statements that rebuild indices:
>
>SELECT 'ALTER INDEX ' || RDB$INDEX_NAME || ' ACTIVE;'
>FROM RDB$INDICES
>WHERE RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL;

First thing: that tip was provided as a quick way to generate a list of
ALTER INDEX statements. Although the doc doesn't say so explicitly at that
point, this kind of trick is intended to be output to a script file
(although the OUTPUT command of isql is described elsewhere...)

Second thing: since IB6 had no EXECUTE STATEMENT syntax, there is no
warning there telling you not to do such operations via stored
procedures. However - you should NEVER do DDL operations in stored
procedures (or triggers!) The engine will reject DDL statements in PSQL at
compile time for good reason. The compiler has absolutely no control over
what is allowed inside an EXECUTE STATEMENT string. Read the warnings for
the EXECUTE STATEMENT syntax in the Fb 1.5 release notes.

However, there are now more issues with the way the Firebird engine stores
information in the system tables, so even these old (and probably untested)
Borland scripts are likely to get wrong results in Firebird.

A problem you have encountered (the actual situation that caused your
specific exception to be thrown) is that the search clause in the Borland
script does not exclude the indexes that the system created for primary and
foreign keys. It is illegal to perform ALTER INDEX on these indexes -
hence the primary reason you get this exception. There is only one way to
rebuild the indexes for constraints (PRIMARY KEY, FOREIGN and UNIQUE) and
that is to drop the constraint. You won't be able to drop any PK or unique
constraint that has a foreign key constraint referring to it; and it won't
work in a SP, even with EXECUTE STATEMENT.

>I've tried to create stored procedure
>
>CREATE PROCEDURE SERVICE_INDEX_REBUILD
>AS
> DECLARE VARIABLE S VARCHAR(200);
> DECLARE VARIABLE S2 VARCHAR(200);
>BEGIN
>
> FOR select RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0
>OR RDB$SYSTEM_FLAG IS NULL INTO :S DO
> BEGIN
> S = 'ALTER INDEX ' || s || 'inactive;';
> EXECUTE STATEMENT :S;
>
> S2 = 'ALTER INDEX ' || s || 'active;';
> EXECUTE STATEMENT :S2;
> END
> SUSPEND;
>END
>
>Unfortunatelly when I try to execute procedure I got following error:
>
>EXECUTE PROCEDURE SERVICE_INDEX_REBUILD;
>Statement failed, SQLCODE = -607
>
>unsuccessful metadata update
>-MODIFY RDB$INDICESS failed
>-action cancelled by trigger (2) to preserve data integrity
>-Cannot deactivate index used by an Integrity Constraint

As someone pointed out, the SUSPEND is not valid in a procedure that has no
output arguments, though this error is by-the-by. This is an executable
procedure and the "freedom" that EXECUTE STATEMENT provides to bypass the
engine's rules will bite painfully. There are not many ways to corrupt
Firebird databases, but EXECUTE STATEMENT performing operations on the
system tables - either directly through DDL or indirectly through write ops
on the tables - is one of them.

DON'T USE EXECUTE STATEMENT TO PERFORM DDL OPERATIONS

There *is* another issue arising from this that concerns me. At some
point, someone has caused the engine to stop flagging primary, foreign and
unique key indexes as "system-defined". This is an undocumented change
which it behoves me to follow up and document, if it is intentional; or to
report as a bug if it is an unintentional side-effect of other internal
changes to indexing in the Firebird era.

As far as I can tell, the only way currently to find out whether a unique
or primary key constraint index has dependent foreign keys is to self-join
or do a correlated self-referencing subquery to find a match for that index
in RDB$FOREIGN_KEY.

But perservere with this trick *only* if you intend to use the query to
create a SCRIPT (not a stored procedure!!!) that you can run as Owner or
SYSDBA when the database is off-line.

./heLen