Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.

On 14th of November 2020, Tom Lane committed patch:

Provide the OR REPLACE option for CREATE TRIGGER.
 
This is mostly straightforward.  However, we disallow replacing
constraint triggers or changing the is-constraint property; perhaps
that can be added later, but the complexity versus benefit tradeoff
doesn't look very good.
 
Also, no special thought is taken here for whether replacing an
existing trigger should result in changes to queued-but-not-fired
trigger actions.  We just document that if you're surprised by the
results, too bad, don't do that.  (Note that any such pending trigger
activity would have to be within the current session.)
 
Takamichi Osumi, reviewed at various times by Surafel Temesgen,
Peter Smith, and myself
 
Discussion: https://postgr.es/m/0DDF369B45A1B44B8A687ED43F06557C010BC362@G01JPEXMBYT03

Triggers are one of the features that scare some developers. So they probably see less use then they really should.

With this new change, it will be simpler to do changes without having to worry if all rows were processed.

Let's assume simple table:

CREATE TABLE test (
    id        serial       PRIMARY  KEY,
    payload   text,
    some_int  int4,
    some_ts   timestamptz
);

Now, let's make a trigger. In normal situation, you first make a function, and then you make a trigger, so:

CREATE FUNCTION test_trg() RETURNS TRIGGER AS $$
DECLARE
BEGIN
    raise notice 'Trigger called % % on %, for each %', TG_WHEN, TG_OP, TG_TABLE_NAME, TG_LEVEL;
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

and now the trigger itself:

CREATE TRIGGER xx
    BEFORE INSERT ON test
    FOR each ROW EXECUTE FUNCTION
    test_trg();

All great, trigger is in place:

=$ \d test
                                      TABLE "public.test"
  COLUMN  |           TYPE           | Collation | NULLABLE |             DEFAULT              
----------+--------------------------+-----------+----------+----------------------------------
 id       | INTEGER                  |           | NOT NULL | NEXTVAL('test_id_seq'::regclass)
 payload  | text                     |           |          | 
 some_int | INTEGER                  |           |          | 
 some_ts  | TIMESTAMP WITH TIME zone |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Triggers:
    xx BEFORE INSERT ON test FOR EACH ROW EXECUTE FUNCTION test_trg()

But what if it should be also on updates?

I could drop the trigger, and make new one with proper list of operations. But that leaves short window when there is no trigger. I could use transaction, but that's not as nice. And – what's more important: create trigger on a table requires access exclusive lock, which blocks virtually everything out. Not so good.

But now, we can:

=$ CREATE OR REPLACE TRIGGER xx
    BEFORE INSERT OR UPDATE ON test
    FOR each ROW EXECUTE FUNCTION
    test_trg();
CREATE TRIGGER

and trigger was updated correctly:

\d test
                                      TABLE "public.test"
  COLUMN  |           TYPE           | Collation | NULLABLE |             DEFAULT              
----------+--------------------------+-----------+----------+----------------------------------
 id       | INTEGER                  |           | NOT NULL | NEXTVAL('test_id_seq'::regclass)
 payload  | text                     |           |          | 
 some_int | INTEGER                  |           |          | 
 some_ts  | TIMESTAMP WITH TIME zone |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Triggers:
    xx BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE FUNCTION test_trg()

I can even change level of trigger (row/statement):

=$ CREATE OR REPLACE TRIGGER xx
    BEFORE INSERT OR UPDATE ON test
    FOR each statement EXECUTE FUNCTION
    test_trg();
 
=$ \d test
                                      TABLE "public.test"
  COLUMN  |           TYPE           | Collation | NULLABLE |             DEFAULT              
----------+--------------------------+-----------+----------+----------------------------------
 id       | INTEGER                  |           | NOT NULL | NEXTVAL('test_id_seq'::regclass)
 payload  | text                     |           |          | 
 some_int | INTEGER                  |           |          | 
 some_ts  | TIMESTAMP WITH TIME zone |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Triggers:
    xx BEFORE INSERT OR UPDATE ON test FOR EACH STATEMENT EXECUTE FUNCTION test_trg()

All this with less invasive SHARE ROW EXCLUSIVE instead of ACCESS EXCLUSIVE lock

This is great addition, thanks to everybody involved in making it real.