CODEWITHSUNDEEP
sqlpostgresqlpostgresql-12
BananaBot
BananaBot

Reputation: 3

PostgreSQL Generated Column is not always up to date

Version: PostgreSQL 12.4

Schema:

CREATE TABLE table1 (
    id int16 NOT NULL,
    placed_count int4 NOT NULL DEFAULT 0,
    picked_count int4 NOT NULL DEFAULT 0,
    packed_count int4 NOT NULL DEFAULT 0,
    generated_status int2 NULL GENERATED ALWAYS AS (calculate_status(placed_count, picked_count, packed_count)) STORED,
    CONSTRAINT table1_pk PRIMARY KEY (id),
);

Function:

CREATE OR REPLACE FUNCTION calculate_status(
placed_count integer, 
picked_count integer, 
packed_count integer
)
 RETURNS integer
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$
select
    case
        when placed_count <= 0 then 1 --pending
        when packed_count = placed_count then 3 --packed
        when picked_count = placed_count then 2 --picked
        else 4 --else
    end
$function$
;

The generated_status generated column is not up to date on some records and doesn't match the result of running the function with the same values found on that row.

SELECT count(*) FROM Table1 tt
WHERE generated_status  != 
calculate_status
(
  tt.placed_count, 
  tt.picked_count, 
  tt.packed_count
)

returns a positive count.

The rows I usually have trouble with are:

ID placed_count picked_count packed_count generated_status
1 3 3 3 2
2 4 4 4 2

When I input the values into the function they do return 3, but it seems to me that the generated column is not updating for the last placed count update sometimes or its value gets over-written by some concurrency issue/ race condition.

What could be wrong, this doesn't happen a lot (~30 in 100k) but I need this status to be always up to date or it will cause some trouble.

Upvotes: 0

Views: 965

Answers (1)

jjanes
jjanes

Reputation: 44285

The only way this could happen (barring bugs) is if you changed the definition of the function. Doing so will not cause the value for the pre-existing rows to be recomputed.

UPDATE: I was able to reproduce this and filed a bug report, which has the reproducer case attached to it.

Upvotes: 4

Related Questions