Commented by: @dyemanov

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

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>

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.

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.

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.