Can't drop table if there is an AFTER INSERT trigger updating the same table [CORE3279]

[firebird-automations]

Submitted by: Thomas Steinmaurer (tsteinmaurer)

Someone in a German newsgroup reported a problem where he can't drop a table due to an AFTER INSERT trigger updating records in the same table.

Test case:

CREATE TABLE TESTIT (
ID INTEGER NOT NULL,
SEQ BIGINT NOT NULL,
TEXT VARCHAR(100)
);

SET TERM ^ ;
CREATE OR ALTER TRIGGER TESTIT_AI0 FOR TESTIT
ACTIVE AFTER INSERT POSITION 0
AS
begin
UPDATE TESTIT SET TEXT=new.TEXT WHERE SEQ=new.SEQ and ID<>http://NEW.ID;
end
^
SET TERM ; ^

commit;

Open a first isql session, connect and execute:

insert into testit values (1, 1, 'Test 1');
commit;

Open a second isql session, connect and execute:

drop table testit;

At this place, the second isql session "hangs", so possibly a deadlock, object in use thingy or whatever.

I found various open issues in the tracker in respect to object in use problems etc., but I'm not sure if the above falls into the same category.

Platform: Win7Prof 64-bit, Firebird 2.5 64-bit, SuperClassic.

[firebird-automations]

Commented by: @dyemanov

Execute DROP TABLE in a no-wait transaction and let us know what error is reported.

[firebird-automations]

Commented by: Thomas Steinmaurer (tsteinmaurer)

This is the output of the second fsql session:

SQL> set transaction no wait;
SQL> drop table testit;
drop table testit;

ERROR:
Statement failed, SQLCODE = -913
deadlock
-unsuccessful metadata update
-object TESTIT is in use

SQL>

[firebird-automations]

Commented by: @dyemanov

So we can conclude that:

1) Such a trigger is not tracked as an external dependency (no record in RDB$DEPENDENCIES), i.e. the explicit table access is treated similar to the NEW/OLD access. Thus no appropriate error is thrown and one would expect that the relation can be dropped. I'm not sure whether it's correct or not, perhaps it's okay.

2) The trigger is loaded into the metadata cache and keeps an existence lock on that table, while the OLD/NEW access doesn't seem to lock the underlying table. This lock cannot be released, hence the "object in use" situation.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Commented by: @AlexPeshkoff

Accessing table in new/old sets of fields is much different from update statement. I think that RDB$DEPENDENCIES should contain appropriate dependency - this will make behavior consistent.

[firebird-automations]

Commented by: @dyemanov

Well, in fact the dependency exist and are checked. However:

/\* If the found object is also being deleted, there's no dependency \*/

if \(\!find\_depend\_in\_dfw\(tdbb, DEP\.RDB$DEPENDENT\_NAME,
			      DEP\.RDB$DEPENDENT\_TYPE, 0, transaction\)\)
\{
	\+\+dep\_counts\[DEP\.RDB$DEPENDENT\_TYPE\*\];
\}

As the trigger gets deleted together with the relation, it's ignored during the dependency check.

The problem is that delete_relation() acquires the EX lock on the relation at stage 2. At this point the trigger still holds the SR lock itself. But the delivered AST does not release the relation triggers and I really doubt it should. So everything works similar like some stored procedure would be using the relation instead of the trigger.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ] =>