Postgresql – Postgres: Trigger always uses the same value for now()

postgresqltrigger

I would like to create a trigger that keeps a last-updated time on each row. It would fire on CREATE and UPDATE, and store the current time into a column. I'm following basically the exact recipe from here: https://www.postgresql.org/docs/9.2/plpgsql-trigger.html

When I run it, the value in the column is always the same, no matter how many times I update the field. It's as if 'now()' was only evaluated once, and the value was cached.

Here is my code:

CREATE FUNCTION alex_trigger() RETURNS trigger AS $alex_trigger$
    BEGIN
        NEW.v := now();
        RETURN NEW;
    END;
$alex_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER alex_trigger BEFORE INSERT OR UPDATE ON alex
    FOR EACH ROW EXECUTE PROCEDURE alex_trigger();

I must be doing something dumb. Any ideas?

Best Answer

Manual:https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

statement_timestamp() / now() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client).

clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.

Related Solutions

Postgresql – Inherit audit columns and triggers

From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

Cannot update parent rows with trigger on the same table

It looks like what you want to do is actually have a summary of the sum of used bytes in each folder, right? I believe that triggers in Oracle are not the best choice of technology to provide you this functionality. This could be better modeled using a view, or even better, a materialized view.

The problem with triggers is that they will serialize the DML to the table and will not scale too well. Imagine a situation where you are updating and inserting lots of rows... for each one of them you'll have an update to the folder table.

If you model your problem with a view instead you would not incur the overhead of the extra DML on the folder table. A simple query like this should do the trick:

SELECT fd.name AS folder_name,
       SUM(f.bytes) AS folder_size
  FROM folders fd, files f
 WHERE fd.id = f.folderId
 GROUP BY fd.name;

You may then make this query persistent by creating a view (the sum will run every time you query the view) or a materialized view with fast refresh on commit (in this case the materialized view stores the calculation as a pre-computed table, useful if the table is huge and the sum is costly).

The materialized view "fast refresh on commit" feature delegates to Oracle the task of recalculating the agregation every time the underlying table(s) changes.

The Data Warehousing Guide has an in dept explanation on this feature: https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG015

Best Answer

Related Solutions

Postgresql – Inherit audit columns and triggers

Cannot update parent rows with trigger on the same table

Related Question