Trigger generation for PostgreSQL is not idempotent

[hlampert]

The SQL generation script for PostgreSQL can't be repeatedly run, because the triggers are not dropped before the corresponding CREATE TRIGGER statement. There are DROP TRIGGER IF EXISTS statements in the code, however they are commented out. There are also commented out DROP FUNCTION IF EXISTS statements for the corresponding trigger functions, however the functions have are managed by CREATE OR REPLACE FUNCTION blocks, so there is no need for them to be dropped first. Unfortunately, there is no CREATE OR REPLACE TRIGGER statement, so the triggers do need to be dropped and recreated if they exist.

My guess was the original flow was to drop and recreate both the triggers and the functions. The flow was then changed so that functions were created or replaced, however the triggers were not and this was not tested by repeatedly running a generated script on a database.

The solution that seems to work is to not comment out the DROP TRIGGER IF EXISTS statements during script generation.

[munichpavel]

I am considering switching to anchor modeling, and face the same issue.