Sample Header Ad - 728x90

Occasional incorrect values for generated columns in PostgreSQL 13

0 votes
1 answer
508 views
I have a PostgreSQL 13 server running in Google Cloud and one of our tables has a few generated columns and occasionally I find rows where the value saved in the generated column isn't up-to-date. For example, if I run a query like:
SELECT * FROM [ table ]
WHERE [ column_name ] != [ generation_expression value ]
Where [ generation_expression value ] is the SQL statement copy-pasted directly from information_schema.columns for that table, I would expect to always find 0 results. Unfortunately, this is not the case. When I find a row where the generated column isn't up-to-date, I can UPDATE a column in that row and then the generated column will automatically update to the expected value. However, I don't understand why the generated column could ever have an out of date value in the first place. I don't have any triggers setup for this database (which I confirmed by checking SELECT * FROM information_schema.triggers), and looking at the PostgreSQL docs I don't see any scenario where a generated column wouldn't have an up-to-date value. Does anyone know any scenario that could explain why the generated columns in my PostgreSQL database might occasionally contain an incorrect value? EDIT: Here's a little more information about the table:
Column           |           Type           | Collation | Nullable |                                                                           Default
----------------------------+--------------------------+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
 id                         | uuid                     |           | not null |
 snooze_until               | timestamp with time zone |           |          |
 latest_message_drafted_at  | timestamp with time zone |           |          |
 latest_message_received_at | timestamp with time zone |           |          |
 latest_message_sent_at     | timestamp with time zone |           |          |
 default_sort_date          | timestamp with time zone |           |          | generated always as (GREATEST(snooze_until, COALESCE(latest_message_received_at, latest_message_sent_at, latest_message_drafted_at))) stored
I don't understand how it is possible for:
SELECT COUNT(*)
FROM threads
WHERE default_sort_date != GREATEST(snooze_until, COALESCE(latest_message_received_at, latest_message_sent_at, latest_message_drafted_at))
to ever return anything other than 0 results (or, if I made a mistake in setting up the generation_expression, why it wouldn't match on 100% of the rows)
Asked by Joshmaker (173 rep)
Oct 5, 2021, 07:03 PM
Last activity: Oct 1, 2024, 12:10 PM