Trigger updatedAt=NOW() for hunded-thousand rows will not actual

All we need is an easy explanation of the problem, so here it is.

I am using SQL Trigger to update updatedAt column if there is a new data inserted/updated in PG 9.6.

Here is the function that will be called by trigger

CREATE FUNCTION sp_updatedatstamp() RETURNS trigger AS $updatedat_stamp$
  BEGIN
    NEW."updatedAt" := now();
    RETURN NEW;
  END;
$updatedat_stamp$ LANGUAGE plpgsql;

Here is the trigger script

create trigger tg_de_installment_cdcupdatedat before
insert or update
    on
    public.table for each row execute procedure sp_updatedatstamp()

Here is the Table Definition

CREATE TABLE table (
    id serial NOT NULL,
    "type" text NULL,
    "transactionDate" timestamptz NULL,
    remark text NULL,
    "createdAt" timestamptz NULL,
    "updatedAt" timestamptz NULL,
    "deletedAt" timestamptz NULL,
    CONSTRAINT table_pkey PRIMARY KEY (id)
);

This code is working fine with small data, until an application execute bulk update query (100K+ rows will be updated) that takes 30 minutes.

The trigger will makes all 100k+ rows uses updatedAt of first rows, which makes the other rows updatedAt not correct. At the end, our data pipeline with schedule 5 min, will lose this data. Is there a way to makes the SQL Trigger uses it actual timestamp for huge data?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As stated in the manual

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.

(emphasis mine)

So if you want a different value for each call, use clock_timestamp()

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply