Skip to content
Advertisement

Why is the current time constant when executing for every row (using a LATERAL JOIN)?

Why is it the case that I can see the same timestamp when wrapping a now() function within a lateral join.

I formulated a code sample to illustrate by example:

SELECT *
FROM generate_series(1, 10000)
       LEFT JOIN LATERAL (
  SELECT now() AS now
  ) sub ON TRUE

Results in the same timestamp, for example (trimmed):

1, 2020-07-22 08:48:55.038668
2, 2020-07-22 08:48:55.038668
3, 2020-07-22 08:48:55.038668
4, 2020-07-22 08:48:55.038668

I would have expected that each row contains a new timestamp (in increasing order) as LATERAL is executed on a row-by-row basis.

Advertisement

Answer

This is a documented behavior (emphasis mine):

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.

The purpose here is to guarantee the consistency of the value returned by the function within a given transaction.

If you need something that changes for every row, use clock_timestamp()

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement