Creating table in Firebird script causes "unsuccessful metadata update" with deadlock

Question

I have the following script that I run using "isql -i scriptfile.sql":

CONNECT C:\Databasefile.fdb USER user PASSWORD password;

SET TERM !! ;
EXECUTE BLOCK AS BEGIN
IF (EXISTS(SELECT 1 FROM rdb$relations WHERE rdb$relation_name = 'MYTABLE')) THEN
EXECUTE STATEMENT 'DROP TABLE MYTABLE;';
END!!
SET TERM ; !!

CREATE TABLE MYTABLE 
(
  MYCOLUMN      VARCHAR(14) NOT NULL
);

The very first time I run this (when the table does not already exist) the table is created as expected. If I run the script again I get the following error:

Statement failed, SQLCODE = -607
unsuccessful metadata update
-STORE RDB$RELATIONS failed
-deadlock
After line 8 in file d:\myscript.sql

When the script exits, MYTABLE has been deleted and can no longer be found in the database. If I run the script a third time the table is once again created and no errors are thrown.

Why can't the script both delete and then recreate a table?

Answer 1

DDL from PSQL is not allowed, using EXECUTE STATEMENT it is not directly forbidden, and usually possible, but still not wise exactly because of these kinds of problems. I am not exactly sure about the reasons, but part of it have to do with how DDL changes are applied in Firebird; the use of execute statement adds additional locks iirc which conflict with a subsequent DDL for the same table name.

Instead of dropping and creating this way, you should use the DDL statement RECREATE TABLE instead.

Note that the word deadlock in this error is actually a bit of a misnomer (there is no real deadlock).