Suppose a DB contains two tables A and B as given below:

 id        SERIAL
 id_b      INTEGER -- NOT NULL reference to B.id
 ts_update TIMESTAMP

 id             SERIAL
 ts_last_update TIMESTAMP

Now let's say that we'd want Postgres to automatically update ts_last_update in B such that B.ts_last_update is always equal to MAX(A.ts_update) WHERE A.id_b=B.id whenever A receives new rows, updates to existing rows or rows are deleted. Similarly, it would be desirable to ensure that any updates to ts_last_update in B conform to the same rule above.

I thought of introducing a trigger that is executed on insert, update and delete operations on A. But it seems that there'd be a need to also introduce a CHECK constraint to ts_last_update in B, however this would mean that whenever the trigger in A runs, the check constraint in B would also run which means that two queries would be executed.

Is my reasoning above correct or is there a better way of enforcing this strange rule? Is there perhaps a way of specifying some sort of exclusivity where when the trigger runs, the check constraint is ignored?

closed as unclear what you're asking by ypercubeᵀᴹ, hot2use, RDFozz, Erik Darling, SqlWorldWide Mar 7 '18 at 13:19

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Please lay out what the purpose of the check constraint would be. – RDFozz Mar 5 '18 at 18:15

The requirements are not clear in at least three places:

  • What should happen if B is updated but no related row in A is updated? Should B.ts_last_update be updated with current timestamp or not?
  • What should happen if a row in B has 0 related rows in A (due to that it was just inserted or the related rows from A were deleted? Should B.ts_last_update hold the latest update timestamp of B/A rows or be NULL?
  • I can't see how a CHECK constraint would help in your case. CHECK constraints are row constraints, they can only be used for checking data from one table and each row independently of other rows, not from two tables.

If the aim is for that column to always hold the MAX(A.ts_update) WHERE A.id_b = B.id value and not to be affected by updates in table B, then you don't really need a trigger at all. You can query both tables every time you need the value or have a view:

  ( id        SERIAL PRIMARY KEY,
    -- more columns
    -- but no timestamp
  ) ;

  ( id        SERIAL PRIMARY KEY, 
    ts_update TIMESTAMP
  ) ;

-- View: B with  ts_last_update
    SELECT B.*,
           ( SELECT MAX(A.ts_update) 
             FROM A
             WHERE A.id_b = B.id
           ) AS ts_last_update
    FROM B ;
  • I actually have a CHECK constraint on the ts_last_update attribute that invokes a function that checks that it is either NULL (if no rows in A exist) or equal to MAX(A.ts_update). Your suggestion to use a view instead is an interesting one -- thanks! – miguelg Mar 4 '18 at 10:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.