Re: Trigger with conditional predicates

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Christophe Pettus <xof(at)thebuild(dot)com>
Cc: Dirk Mika <Dirk(dot)Mika(at)mikatiming(dot)de>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Trigger with conditional predicates
Date: 2021-01-01 16:27:47
Message-ID: 2524881.1609518467@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Christophe Pettus <xof(at)thebuild(dot)com> writes:
> PostgreSQL doesn't have an exact equivalent. Typically, the OLD and NEW values are compared and then action is taken based on that. For example, in PL/pgSQL:
> IF NEW.is_canceled IS NOT DISTINCT FROM OLD.is_canceled THEN
> NEW.is_canceled := etc etc ;
> ENDIF;
> There's currently no way to detect if the column was simply not mentioned at all in the UPDATE statement.

That's not completely true: you can make the whole trigger firing
dependent on that, by writing something like

CREATE TRIGGER tgname BEFORE UPDATE OF column_name [, ... ] ON table ...

and then the trigger won't fire if the column is not mentioned.

This is not without downsides though:

* If you've got several columns of concern, this would lead you to
write a separate trigger for each one, and maybe another trigger
for unconditional actions. My gut feel is that the trigger firing
overhead is enough to make this less performant than one trigger
with IF-conditions. I could be wrong though, never measured it.

* When dealing with multiple triggers, you need to keep firmly
in mind that the filter condition is whether the *original SQL
text* listed the column as an update target. You can't tell
this way whether some earlier trigger changed the column's value.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jack Orenstein 2021-01-01 17:20:49 Btree vs. GIN
Previous Message Christophe Pettus 2021-01-01 16:05:26 Re: Trigger with conditional predicates