Postgresql – Using NEW on tables with generated columns

computed-columnpostgresqlpostgresql-12trigger

I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially this:

BEFORE UPDATE 
ON public.some_table
FOR EACH ROW
WHEN (NEW IS DISTINCT FROM OLD)
EXECUTE PROCEDURE public.update_modified_column();

However, this code despite being correct does not work with generated columns, giving the following error:

BEFORE trigger's WHEN condition cannot reference NEW generated columns

So my question is this: Is there any way to use NEW with a table that has generated columns without having to specify every single column of that table that isn't generated?

It can be something that requires specifying the columns that were generated to be excluded, just not the other way around.

Also, I know a potential alternative to generated tables is views (which are not the same thing but close enough for my use case), but I can't use views, since I need something that keeps track if the columns allows or doesn't allow for null values, which unfortunately views do not do that.

Using normal columns with a before insert/update trigger to calculate the columns, along with setting the columns as NOT NULL with a Default (for my use case ofc), has essentially fixed the issue; however, I will keep the question open to see if there is any solution to the generated columns specifically, since they are a bit more of an elegant solution.

Best Answer

That is simple: use an AFTER trigger instead. The column value will have been computed then.

On the other hand, your answers reveals that you need a BEFORE trigger because you want to modify NEW.

So probably the best solution is to use a BEFORE trigger with a WHEN condition that excludes the generated column:

WHEN ((NEW.col1, NEW.col2, ...)
      IS DISTINCT FROM
      (OLD.col1, OLD.col2, ...))

Related Solutions

PostgreSQL pl/perl trigger, differentiate null vs empty

You did not specify PostgreSQL version. On 9.0.5 I have observed the same behavior (not sure if it's a bug, see comments below).

This is easy to workaround - you can first test for definedness to catch NULL, and if it passed, test for empty string.

if ( not defined $_TD->{ new }{ $column } ) {
    elog( NOTICE, "New value in column $column is not defined" );
}
elsif ( $_TD->{ new }{ $column } eq '' ) {
    elog( NOTICE, "New value in column $column is empty" );
}
if ( not defined $_TD->{ old }{ $column } ) {
    elog( NOTICE, "Old value in column $column is not defined" );
}
elsif ( $_TD->{ old }{ $column } eq '' ) {
    elog( NOTICE, "Old value in column $column is empty" );
}

Mysql – Synchronise data from one table to another & vice-versa using triggers

I concur with @NathanJolly, this is a potential data nightmare and seems ill-advised... but there is a way to implement this that is, at least conceptually, fairly straightforward.

You need not worry about the possibility of recursion in triggers, because this won't happen... MySQL doesn't support it.

I built this fiddle that creates tables t1 and t2, each of which has an AFTER INSERT trigger that tries to insert the record into the other table... then tries to do an insert. Theoretically, there's a potential for recursion, but MySQL prevents this:

ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already
                    used by statement which invoked this stored function/trigger.

So it won't run in a loop, but it still throws an exception. Technically, it's not trigger recursion causing the exception, because we haven't actually gotten that far... it's the fact that the trigger in t2 tries to modify table t1, which was involved in the currently-executing statement.

Then again, technically, it's not just the recursion of the triggers we need to avoid -- we have to stop one step before that -- if the client modifies t1, the t1 trigger modifies t2, then t2's trigger needs to not even try to do anything to t1 (including, but not limited to, firing the trigger again on t1).

Implementing this is actually fairly straightforward... in each trigger, we toggle the value of a session to tell the other trigger not to run.

CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW BEGIN
  IF @__disable_trigger_t1t2 = 1 THEN
    SET @__disable_trigger_t1t2 = NULL;
  ELSE
    SET @__disable_trigger_t1t2 = 1;
    -- trigger logic goes in here
    INSERT INTO t2 (id,things) VALUES (NEW.id,NEW.stuff);
  END IF;
END //

CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW BEGIN
  IF @__disable_trigger_t1t2 = 1 THEN
    SET @__disable_trigger_t1t2 = NULL;
  ELSE
    SET @__disable_trigger_t1t2 = 1;
    -- trigger logic goes in here
    INSERT INTO t1 (id,stuff) VALUES (NEW.id,NEW.things);
  END IF;
END //

A test case for this logic working can be seen in this fiddle.

Essentially, when a trigger fires, it checks the value of the @__disable_trigger_t1t2 variable (this variable name is, of course, arbitrary). If '1', it sets it back to NULL (the default for session variables) and does no further processing, because the '1' means this row is being manipulated by the trigger on the other table. If already NULL, then it sets the value to 1 and executes the trigger logic -- which will update the other table, whose trigger will see the '1', will not execute its own logic, and will reset the variable back to NULL, so that the next execution of either trigger -- whether for the next row in the same statement, or for a subsequent statement -- will see the NULL and execute normally.

In MySQL, triggers are only supported FOR EACH ROW, meaning that we can define a MySQL trigger as "a stored program that is executed before or after each row is inserted, updated, or deleted"... which means that no matter how many rows your query affects, the trigger will run once for each row. Actions against other tables will cause the triggers on those tables to fire, and the execution of the trigger on t1 for the first row will not complete until all of the triggers it caused to fire have also completed... and this is why we can use this variable like this -- only one row is actually being handled at a time, so we set and clear this variable for each row, always leaving it NULL after each statement terminates.

A significant caveat is that this approach fails if you need to trigger on cascading deletes or updates related to foreign keys, because:

"Currently, cascaded foreign key actions do not activate triggers."

^{-- http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html}

So, while I have misgivings about the long-term viability of maintaining database synchronization with this approach because of the number of things that could go wrong with two applications manipulating each other's data or logical errors in the trigger definitions... it seems technically possible.

You also mentioned:

views cannot have triggers in MySQL

No, but updating an updatable view will fire the triggers on the underlying base table as if the table had been directly manipulated.

Best Answer

Related Solutions

PostgreSQL pl/perl trigger, differentiate null vs empty

Mysql – Synchronise data from one table to another & vice-versa using triggers

Related Question