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
Last activity: Oct 1, 2024, 12:10 PM