CODEWITHSUNDEEP
sqlperformancepostgresqltriggers
EM0
EM0

Reputation: 6337

More efficient delete triggers in PostgreSQL

My PostgreSQL 9.2 database has many AFTER DELETE row-level triggers defined, which are used to delete child rows when a parent row is deleted. (There are also many FKs with ON DELETE CASCADE in cases where the child row references the parent row.) This works, but it's very slow when many rows need to be deleted. Is there a way to use statement-level triggers for this or some other way to make deletes more efficient? I cannot figure out how to get the deleted rows in a statement-level trigger function - or any useful information about what just happened, really.

Upvotes: 0

Views: 422

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324751

There isn't much at the moment.

You could accumulate information about the rows to delete by inserting them into a side-table in your BEFORE ... FOR EACH ROW trigger, then have an AFTER statement-level trigger do the deletes from child tables, but that won't play well with built-in foreign key enforcement.

There's been periodic discussion of accumulating "change relations" for statement level triggers. That's what you want, but nobody's come up with a viable patch to implement it yet.

Upvotes: 2

Related Questions