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.
This is a documented behavior (emphasis mine):
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()