Thread: Trigger with conditional predicates

Trigger with conditional predicates

From

Dirk Mika

Date:

01 January 2021, 18:56:32

Hi all and a happy new Year!

We have an Oracle schema that is to be converted to PostgreSQL, where conditional predicates are used in some
triggers.

In particular, columns are populated with values if they are not specified in the update statement which is used.
Usually with an expression like this:

      IF NOT UPDATING('IS_CANCELED')
      THEN
         :new.is_canceled := ...;
      END IF;

I have not found anything similar in PostgreSQL. What is the common approach to this problem?

BR
Dirk


--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika



## 2020 - Das Jahr der Veränderungen
## https://now.mikatiming.de/story/2020-das-jahr-der-veraenderungen/

Re: Trigger with conditional predicates

From

Christophe Pettus

Date:

01 January 2021, 19:05:26

> On Jan 1, 2021, at 07:56, Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
> In particular, columns are populated with values if they are not specified in the update statement which is used.
> Usually with an expression like this:
>
>      IF NOT UPDATING('IS_CANCELED')
>      THEN
>         :new.is_canceled := ...;
>      END IF;
>
> I have not found anything similar in PostgreSQL. What is the common approach to this problem?

PostgreSQL doesn't have an exact equivalent.  Typically, the OLD and NEW values are compared and then action is taken
basedon 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.

--
-- Christophe Pettus
   xof@thebuild.com

Re: Trigger with conditional predicates

From

Tom Lane

Date:

01 January 2021, 19:27:47

Christophe Pettus <xof@thebuild.com> writes:
> PostgreSQL doesn't have an exact equivalent.  Typically, the OLD and NEW values are compared and then action is taken
basedon 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

Re: Trigger with conditional predicates

From

Dirk Mika

Date:

02 January 2021, 13:17:55

> > PostgreSQL doesn't have an exact equivalent.  Typically, the OLD and NEW values are compared and then action is
takenbased 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.

Well, this works if I've something like

  IF UPDATING(...) THEN

But since I've

  IF NOT UPDATING(...) THEN

This isn't working. Or am I missing something?

BR
Dirk


--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

Re: Trigger with conditional predicates

From

Dirk Mika

Date:

02 January 2021, 13:23:34

> > In particular, columns are populated with values if they are not specified in the update statement which is used.
> > Usually with an expression like this:
> >
> >      IF NOT UPDATING('IS_CANCELED')
> >      THEN
> >         :new.is_canceled := ...;
> >      END IF;
> >
> > I have not found anything similar in PostgreSQL. What is the common approach to this problem?
>
> PostgreSQL doesn't have an exact equivalent.  Typically, the OLD and NEW values are compared and then action is taken
basedon that.  For example, in PL/pgSQL:
 
>
> IF NEW.is_canceled IS NOT DISTINCT FROM OLD.is_canceled THEN
>NEW.is_canceled := etc etc ;
> ENDIF;

Unfortunately, this doesn't quite fit the logic I need. I don't need to know if the value was changed, but if the
applicationthat sent the UPDATE statement knows the column or not.
 
In our case, a number of different applications access the database, which may or may not know the column depending on
theversion.
 
And it is also regularly the case that SQL statements are executed directly in an SQL client by hand. And in the event
thatthe column was not specified in these statements, a trigger is supposed to fill the value.
 

> There's currently no way to detect if the column was simply not mentioned at all in the UPDATE statement.

Hmm, that's odd.

BR
Dirk



--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

Re: Trigger with conditional predicates

From

Alban Hertroys

Date:

02 January 2021, 14:31:09

> On 1 Jan 2021, at 16:56, Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
>
> Hi all and a happy new Year!
>
> We have an Oracle schema that is to be converted to PostgreSQL, where conditional predicates are used in some
triggers.
>
> In particular, columns are populated with values if they are not specified in the update statement which is used.
> Usually with an expression like this:
>
>      IF NOT UPDATING('IS_CANCELED')
>      THEN
>         :new.is_canceled := ...;
>      END IF;
>
> I have not found anything similar in PostgreSQL. What is the common approach to this problem?
>
> BR
> Dirk

Can't you use column defaults to handle these cases?

Alban Hertroys
--
There is always an exception to always.

Re: Trigger with conditional predicates

From

Adrian Klaver

Date:

02 January 2021, 18:36:21

On 1/2/21 2:23 AM, Dirk Mika wrote:
>>> In particular, columns are populated with values if they are not specified in the update statement which is used.
>>> Usually with an expression like this:
>>>
>>>       IF NOT UPDATING('IS_CANCELED')
>>>       THEN
>>>          :new.is_canceled := ...;
>>>       END IF;
>>>
>>> I have not found anything similar in PostgreSQL. What is the common approach to this problem?
>>
>> PostgreSQL doesn't have an exact equivalent.  Typically, the OLD and NEW values are compared and then action is
takenbased 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;
> 
> Unfortunately, this doesn't quite fit the logic I need. I don't need to know if the value was changed, but if the
applicationthat sent the UPDATE statement knows the column or not.
 
> In our case, a number of different applications access the database, which may or may not know the column depending
onthe version.
 
> And it is also regularly the case that SQL statements are executed directly in an SQL client by hand. And in the
eventthat the column was not specified in these statements, a trigger is supposed to fill the value.
 
> 
>> There's currently no way to detect if the column was simply not mentioned at all in the UPDATE statement.
> 
> Hmm, that's odd.

See thread below:

https://www.postgresql.org/message-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff%40tc7-visena

> 
> BR
> Dirk
> 
> 
> 
> --
> Dirk Mika
> Software Developer
> 
> mika:timing GmbH
> Strundepark - Kürtener Str. 11b
> 51465 Bergisch Gladbach
> Germany
> 
> fon +49 2202 2401-1197
> dirk.mika@mikatiming.de
> www.mikatiming.de
> 
> AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
> Geschäftsführer: Harald Mika, Jörg Mika
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Trigger with conditional predicates

From

Dirk Mika

Date:

04 January 2021, 22:02:21

--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


> > On 1 Jan 2021, at 16:56, Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
> >
> > Hi all and a happy new Year!
> >
> > We have an Oracle schema that is to be converted to PostgreSQL, where conditional predicates are used in some
triggers.
> >
> > In particular, columns are populated with values if they are not specified in the update statement which is used.
> > Usually with an expression like this:
> >
> >      IF NOT UPDATING('IS_CANCELED')
> >      THEN
> >         :new.is_canceled := ...;
> >      END IF;
> >
> > I have not found anything similar in PostgreSQL. What is the common approach to this problem?

> Can't you use column defaults to handle these cases?

That would work for inserts, but not for updates.

BR
Dirk

Re: Trigger with conditional predicates

From

Dirk Mika

Date:

04 January 2021, 22:06:03

>>> In particular, columns are populated with values if they are not specified in the update statement which is used.
>>> Usually with an expression like this:
>>>
>>>       IF NOT UPDATING('IS_CANCELED')
>>>       THEN
>>>          :new.is_canceled := ...;
>>>       END IF;
>>>
>>> I have not found anything similar in PostgreSQL. What is the common approach to this problem?
>>
>> PostgreSQL doesn't have an exact equivalent.  Typically, the OLD and NEW values are compared and then action is
takenbased 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;
>
> Unfortunately, this doesn't quite fit the logic I need. I don't need to know if the value was changed, but if the
applicationthat sent the UPDATE statement knows the column or not.
 
> In our case, a number of different applications access the database, which may or may not know the column depending
onthe version.
 
> And it is also regularly the case that SQL statements are executed directly in an SQL client by hand. And in the
eventthat the column was not specified in these statements, a trigger is supposed to fill the value.
 
>
>> There's currently no way to detect if the column was simply not mentioned at all in the UPDATE statement.
>
> Hmm, that's odd.

See thread below:

https://www.postgresql.org/message-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff%40tc7-visena

I found that thread already, but It doesn't not provide a solution to my problem.


--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

Re: Trigger with conditional predicates

From

Christophe Pettus

Date:

04 January 2021, 22:22:48

> On Jan 4, 2021, at 11:06, Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
>
> See thread below:
>
> https://www.postgresql.org/message-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff%40tc7-visena
>
> I found that thread already, but It doesn't not provide a solution to my problem.

One possibility, which is admittedly very hacky, is:

-- Create a new column which is a flag (or bitmap) of other columns that need to be managed in this way, with a default
of0. 
-- Have two EACH ROW triggers:

* The first is ON UPDATE OF the actual column to managed, and sets the appropriate flag or bitmap in the flag column
whenrun.  This flags that the application has updated the column. 

* The second, which runs always, checks that flag, and if it is set, clears it; otherwise, it sets the column to the
valuedesired if the application didn't change it. 

Of course, the order of execution of these triggers matters; PostgreSQL executes triggers at the same level
alphabetically.

Now, this is a pretty high-overhead way of handling it, and it is probably better to see if there is an application
logicchange that can happen here. 

Best,
--
-- Christophe Pettus
   xof@thebuild.com

Re: Trigger with conditional predicates

From

Alban Hertroys

Date:

04 January 2021, 23:34:16

> On 4 Jan 2021, at 20:02, Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
>
>>> On 1 Jan 2021, at 16:56, Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
>>>
>>> Hi all and a happy new Year!
>>>
>>> We have an Oracle schema that is to be converted to PostgreSQL, where conditional predicates are used in some
triggers.
>>>
>>> In particular, columns are populated with values if they are not specified in the update statement which is used.
>>> Usually with an expression like this:
>>>
>>>     IF NOT UPDATING('IS_CANCELED')
>>>     THEN
>>>        :new.is_canceled := ...;
>>>     END IF;
>>>
>>> I have not found anything similar in PostgreSQL. What is the common approach to this problem?
>
>> Can't you use column defaults to handle these cases?
>
> That would work for inserts, but not for updates.

Usually, if you don’t mention a column in an UPDATE, you want the value to remain as it was, which is precisely what
happensby default. That certainly makes sense to me when you’re dealing with an application that doesn’t know about the
existenceof said column; overwriting an existing value that some other application put there looks like a problem to
me.But of course, that depends a lot on what you’re trying to achieve. 

What is your use-case that that is not the desired behaviour? Or are we talking about a mixed problem here, where this
approachworks for some fields, but other fields (such as a status change date) always need to be updated (regardless of
whethera value was specified)? 

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.