Question 88969 in PostgreSQL Question posted in
The official documentation can be found here.

Tracking time of last update in PostgreSQL without using triggers

I am trying to reproduce example from https://www.alibabacloud.com/blog/598021.

According to that example, the following should work (proven by the c/p of the output there).

create or replace function im_now () returns timestamptz as $$  
  select CURRENT_TIMESTAMP;  
$$ language sql strict immutable;  

create table t1 (id int primary key, info text, crt_time timestamp, 
mod_time timestamp GENERATED ALWAYS AS (im_now()) stored); 

insert into t1 (id, info, crt_time) values (1,'test', now());  

select * from t1;

update t1 set info='a' where id=1;

select * from t1;

But when I try that, mod_time is set when creating new record and not changed after the update.
No errors are reported. I am using PostgreSQL version 15.2.

What might be wrong?

Tags:

2

Answers


  1. 0

    Currently mod_time will only change when the record is created. There is not a single column mentioned in the function im_now() that could behave as a trigger to change this value for mod_time.

    With two minor changes, you can fix this issue:

    create or replace function im_now (text) -- some input
returns timestamptz as 
$$  
  select CURRENT_TIMESTAMP;  
$$ language sql strict immutable;  

create table t1 (
    id int primary key, 
    info text, 
    crt_time timestamp, 
    mod_time timestamp GENERATED ALWAYS AS (im_now(info)) stored -- see the "info" column
    ); 

insert into t1 (id, info, crt_time) values (1,'test', now());  

select * from t1;

update t1 set info='a' where id=1;

    The column "info" will now behave as a trigger to create a new timestamp for the column "mod_time".

    Login or Signup to reply.
  2. 0

    The article uses a trick as it incorrectly, but on purpose, declares the function as immutable.
    Without any paramater, an immutable function only ever returns 1 value; based on the information it is provided, the optimizer decides it does not need to call the function again as its result will be the same as what it had before.

    If you add a parameter to the im_now() function, then everything changes: the function is still declared immutable but its result now depends on some input. The optimizer cannot optimize the call away without first determining that none of the input parameters are being changed. More about that after the code sample.

    The below example demonstrates how to update mod_time when changing id or info, by declaring mod_time depends on id and info, but not when changing crt_time as crt_time is not a parameter in the call.
    In a more realistic test case, I suppose you would want to declare crt_time as GENERATED too, to prevent people from updating it.

    create or replace function im_now () returns timestamptz as $$  
  select CURRENT_TIMESTAMP;  
$$ language sql strict immutable;
create or replace function im_now (VARIADIC text[]) returns timestamptz as $$  
  select im_now();  
$$ language sql strict immutable;

create table t1 (
    id int primary key,
    info text,
    crt_time timestamp GENERATED ALWAYS AS (im_now()) STORED,
    mod_time timestamp GENERATED ALWAYS AS (im_now(id::text, info)) STORED
);

/* INSERT -> OK */
insert into t1 (id, info) values (1,'test');  
select * from t1;

/* Scenario 1: Attempt to update crt_time
-> call is optimized away, crt_time is unchanged. */
update t1 set crt_time=default where id=1;
select * from t1;

/* Scenario 2: First update -> OK */
update t1 set info = 'a' where id=1;
select * from t1;

/* Scenario 3: Second update (`info` is not being changed) -> See details below */
update t1 set info = 'a' where id=1;
select * from t1;

/* Scenario 4: Third update (`info` is not being changed) -> See details below */
update t1 set info = info where id=1;
select * from t1;

    Additional details about the optimizer for your query

    This section is unnecessary if you wish to only copy the sample above and not understand all the kinks behind it.
    But to be perfectly complete, let us analyze the situations where im_now is called, where it is not called and what it may mean if the optimizer is improved in future versions of Postgres.

    In the version of Postgres I use to test:

    • As expected: im_now(id::text, info) is NOT called when neither id not info are being updated
      See scenario 1.
      In the above example, all the columns that you can effectively update (I am not counting crt_time/mod_time since you cannot force a value in them, and updating with crt_time = default or mod_time = default does nothing).
    • As expected: im_now(id::text, info) is called when modifying the value of id or info.
      See scenario 2.
    • ????: im_now(id::text, info) is called when updating id or info with their current value (i.e. the value does not change during the update).
      See scenarios 3 and 4.

    For scenarios 3 and 4, we can argue whether not modifying the record requires mod_time to be refreshed. This is up to every programmer’s design.
    The fact is, in the version of Postgres I am using, it is refreshed (i.e. the call to im_now(id::text, info) is not optimized away).

    This has some consequences:

    1. This may change in a future version of Postgres, or this may be different for other DBMS based on Postgres.
      I recommend you do not create a strong requirement that mod_time should/should not be refreshed in scenarios 3 and 4 otherwise it may (will?) break down in the future.

    2. If a cache is ever implemented, the optimizer may be able to optimize calls away across several queries.
      Let us imagine for a minute I declared the column not to use id::text:

      mod_time timestamp GENERATED ALWAYS AS (im_now(info)) STORED

      Then, it is theoretically possible to have:

      /* Expected behavior: `im_now('a')` is called to refresh `mod_time`. */
update t1 set info = 'a' WHERE id = 1;
/* Some time passes... */
/* Unexpected: `mod_time` is refreshed from the cached result of the previous call to `im_now('a')`, which is the wrong time. */
update t1 set info = 'a' WHERE id = 2;

      This is why I have put the primary key id as a parameter of im_now in the able example, so that even in the future, a record will not be able to use the result cached by the call to im_now for another record, at another time.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search