Schema deletion fails after update due to lingering constraint triggers on array columns

[erikzwart]

Have you checked if this issue already exists?

• yes

Molgenis EMX2 version

13.172.0

Description

After updating EMX2 to the latest version, attempting to delete an existing schema results in a PostgreSQL error:

Transaction failed: Drop schema failed: cannot drop column providers of additional resources.resource of table "Patient Registry"."Individuals" because other objects depend on it. Details: trigger C_Patient Registry_Individuals_providers of additional resource on table "Patient Registry"."Individuals" depends on column providers of additional resources.resource of table "Patient Registry"."Individuals"

The blocking object is a constraint trigger created by a previous EMX2 version to enforce reference integrity on array-type foreign key columns. These triggers are not cleaned up during migration/update operations.

Reproduction steps

1. Setup a emx2 instance with version 13.15.0 (use AWX do downgrade?)
2. Create a schema with tables containing array reference columns (e.g., foreign keys to multiple organisations) (use Template PATIENT_REGISTRY)
3. Load the schema with example data
4. Update EMX2 to the latest version (13.172.0)
5. Attempt to delete the schema via UI or API

Expected behaviour

Expected behavior: Schema deletion succeeds, all objects including triggers are cleaned up.

Actual behavior: Deletion fails with PostgreSQL dependency error about constraint trigger.

Workaround

Manually drop the triggers before deleting the schema:

sql DROP TRIGGER IF EXISTS "C_Patient Registry_Individuals_providers of additional resource" ON "Patient Registry"."Individuals"; DROP FUNCTION IF EXISTS "Patient Registry"."C_Patient Registry_Individuals_providers of additional resource"(); DROP SCHEMA "Patient Registry" CASCADE;

Expected fix

During schema deletion (or migration), EMX2 should:

1. Enumerate and drop all constraint triggers created for array foreign key enforcement
2. Or use DROP SCHEMA ... CASCADE internally to handle dependencies automatically
3. Or implement proper cleanup during version migrations

Environment

• EMX2 version: Updated from [13.15.0] to [13.172.0]
• Schema name: Patient Registry (template PATIENT_REGISTRY)
• Affected columns: providers of additional resources.resource, providers of additional resources.id
• Trigger name pattern: C_<schema>_<table>_<column>

Additional context

From pg_trigger inspection, these triggers are constraint triggers created to simulate foreign key validation on array columns (since PostgreSQL does not support native FK constraints on array elements). The trigger definitions include AFTER INSERT OR UPDATE OF <column_name> clauses that explicitly reference the array columns, causing PostgreSQL's dependency tracking to block column drops.

This is a migration edge case — new schemas likely work fine, but existing schemas upgraded across versions encounter orphaned triggers.

Links

Related issue: #3040 (deleting table column causes failures)

Co-authored-by: Lumo

System configuration

• Device:
• Operating system:
• Browser: