How to run a trigger on existing rows

Question

I have a process that inserts records into a table.

The table is defined with a trigger like this ...

CREATE TRIGGER myTrig AFTER INSERT ON myTable FOR EACH ROW EXECUTE PROCEDURE myProcedure();

The process that does the INSERT into myTable is outside of my control, it basically just spews records into the table throughout the day 24/7.

The functionality in myProcedure() requires database resources. During busy periods the rate that records arrive is greater than the database can cope with (about 500 records per second is the limit). If I disable the trigger using ALTER TABLE myTable DISABLE TRIGGER myTrig then the system can cope with maybe 1000 records per second.

I can then re-enable the trigger when the system isn't as busy, but obviously those records added whilst the trigger was disabled won't have been processed correctly.

Is there any way to re-run the trigger on selected records ALREADY in the table?

My current best guess is that during busy periods I should divert incoming records to a copy of myTable thats identical to the original (but without the trigger) and then when the system quiets down later I can insert from the copy, back to the original where they should be processed by the trigger (albeit later than they should have been)

Answer 1

A trigger function requires the triggering action. When triggered FOR EACH ROW it also typically needs the triggering row as input. But a plain function can replay almost anything a trigger function does.

The best course of action depends on whether the trigger function myProcedure() alters the inserted row itself.

If so, your idea with a separate, staging table sounds good. A later UPDATE would write a new row version anyway. So it's efficient overall to write to a staging table first and INSERT into the target table later. If it's acceptable to lose data in case of a server crash, you might even use an UNLOGGED table for that, and cope with even more rows per second.
The eventual INSERT to the main table will also fire the trigger, all done.

If not (and if an UNLOGGED table isn't an option anyway), I would write to the destination table directly. Everything the trigger function does can now be replayed with a plain function. If you can't duplicate the functionality of the trigger function, the next best idea would be to write to a copy of myTable, with the same trigger on it. Only make it RETURN NULL; instead of RETURN NEW; to skip the triggering row itself. (So nothing is written to the copy.)

If you can't or won't make a modified copy of the trigger function, alternatively add another trigger on the temp table like this:

CREATE OR REPLACE FUNCTION public.trg_cancel_insert()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
BEGIN
RETURN NULL;
END
$func$;
CREATE TRIGGER zzz_last_before_insert
BEFORE INSERT ON pg_temp.myTable_copy -- the copy!
FOR EACH ROW EXECUTE PROCEDURE public.trg_cancel_insert();

This also skips the triggering insert itself. But everything that's executed in myProcedure() from the earlier trigger still sticks.

The manual:

If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name.

• ConanTheGerbil: I think I'll go with a staging table - thanks for the tip about UNLOGGED, I wasn't aware of this option.