Trigger with conditional predicates

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 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.

--
-- Christophe Pettus
xof@thebuild.com

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 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

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.

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

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 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;

Unfortunately, this doesn't quite fit the logic I need. I don't need to know if the value was changed, but if the application that 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 the version.
And it is also regularly the case that SQL statements are executed directly in an SQL client by hand. And in the event that 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.

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

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.

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 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;

Unfortunately, this doesn't quite fit the logic I need. I don't need to know if the value was changed, but if the application that 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 the version.
And it is also regularly the case that SQL statements are executed directly in an SQL client by hand. And in the event that 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:

/messages/by-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff@tc7-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

--
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

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 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;

Unfortunately, this doesn't quite fit the logic I need. I don't need to know if the value was changed, but if the application that 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 the version.
And it is also regularly the case that SQL statements are executed directly in an SQL client by hand. And in the event that 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:

/messages/by-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff@tc7-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

On Jan 4, 2021, at 11:06, Dirk Mika <Dirk.Mika@mikatiming.de> wrote:

See thread below:

/messages/by-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff@tc7-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 of 0.
-- 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 when run. 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 value desired 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 logic change that can happen here.

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

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 happens by default. That certainly makes sense to me when you’re dealing with an application that doesn’t know about the existence of 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 approach works for some fields, but other fields (such as a status change date) always need to be updated (regardless of whether a 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.