0

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)

9
  • 1
    Does the generated column’s formula refer to values from outside its row?
    – dwhitemv
    Oct 5 '21 at 19:49
  • 1
    Please edit your question and add the complete create table statement for the table in question Oct 5 '21 at 20:25
  • @dwhitemv no it doesn't refer to any values from outside it's row. If it did I think it would always fail (as it is, it works fine 99% of the time)
    – Joshmaker
    Oct 6 '21 at 16:05
  • 1
    Did you meant to quote 'default_sort_date' in your query? That's a string literal, not a column name.
    – dwhitemv
    Oct 6 '21 at 16:53
  • 1
    Please edit your question and post the table definition. Or \d threads from psql? If one of your columns is type timestamp without time zone then that can cause the problem you are seeing.
    – dwhitemv
    Oct 6 '21 at 17:31
2

Sure, one obvious scenario is that you falsely declared a function to be immutable, and then used it in the generation expression.

Another obvious scenario, though less likely, is a bug. What is the full version of PostgreSQL?

Based on our edit, this does seem like a bug. But I cannot reproduce it. Can you discern any pattern to the incorrect rows? For example, is it storing NULL when it should be non-NULL, or storing a non-NULL when it should be NULL, or is it correctly non-NULL just the wrong non-NULL? Does it have an old value of one of the underlying columns, or does it have a current value of one of the columns, but not the column which it should be?

I don't see anything between 13.3 and 13.4 which would have fixed this, but it wouldn't hurt to see if you can reproduce it in 13.4.

2
  • I've updated my question to include more information about the column and generation express. It doesn't look to me like I am doing anything invalid in the generation_expression.
    – Joshmaker
    Oct 6 '21 at 16:28
  • The version is: PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1 , 64-bit
    – Joshmaker
    Oct 6 '21 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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