Thread: BUG #18762: Update script produces distinct outputs before and after vacuum analyze operation

The following bug has been logged on the website:

Bug reference:      18762
Logged by:          Siva Thalaimalai
Email address:      siva.thalaimalai@chainsys.com
PostgreSQL version: 13.15
Operating system:   Oracle SQL
Description:

Issue Report: For a same update script produces distinct outputs before and
after vacuum analyze operation.

SQL Statement - UPDATE stgtbl_837465r4 STGTBL SET ATTR_7 =
PVQUERY.batch_number, ATTR_1 = PVQUERY.batch_number , TF_1 ='S' FROM (SELECT
CAST(BATCH_SEQ AS NUMERIC) AS BATCH_NUMBER, CAST(BATCH_SEQ AS VARCHAR(50))
AS BATCH, BATCH_TEXT FROM ( SELECT NEXTVAL('sample_sequence__2899') AS
BATCH_SEQ,  'A' AS BATCH_TEXT
FROM test_act ) A) PVQUERY WHERE PVQUERY.batch_text IS NOT NULL and
STGTBL.Execution_id = 0;

Before executing the Vacuum Analyze operation on the target table, the
output of the update statement (assuming the target table contains 3 rows,
all of which satisfy the WHERE condition) results in different sequence
numbers (e.g., 1, 2, 3, ..., n) being generated and updated for each record
in the target table.

Query Execution Plan executing Vacuum Analyze Operation on the target
table
Update on stgtbl_2798778 stgtbl  (cost=0.00..78.55 rows=1360 width=1216)
(actual time=0.172..0.174 rows=0 loops=1)
  ->  Nested Loop  (cost=0.00..78.55 rows=1360 width=1216) (actual
time=0.097..0.125 rows=3 loops=1)
    ->  Seq Scan on stgtbl_2798778 stgtbl  (cost=0.00..10.75 rows=1
width=1168) (actual time=0.036..0.040 rows=3 loops=1)
        Filter: (execution_id = 1015939)
    ->  Subquery Scan on a  (cost=0.00..40.60 rows=1360 width=40) (actual
time=0.019..0.020 rows=1 loops=3)
        ->  Seq Scan on test_act  (cost=0.00..27.00 rows=1360 width=40)
(actual time=0.015..0.015 rows=1 loops=3)
Planning Time: 0.310 ms 
Execution Time: 0.373 ms

After executing the Vacuum Analyze operation on the target table
(stgtbl_2798778), the update statement produced the same sequence number for
all records (e.g., all records were updated with a sequence value of 4).

Query Plan After Vacuum:

Update on stgtbl_2798778 stgtbl  (cost=0.00..133.45 rows=4080 width=1216)
(actual time=0.143..0.144 rows=0 loops=1)
  ->  Nested Loop  (cost=0.00..133.45 rows=4080 width=1216) (actual
time=0.087..0.093 rows=3 loops=1)
    ->  Subquery Scan on a  (cost=0.00..40.60 rows=1360 width=40) (actual
time=0.052..0.053 rows=1 loops=1)
        ->  Seq Scan on test_act  (cost=0.00..27.00 rows=1360 width=40)
(actual time=0.040..0.040 rows=1 loops=1)
    ->  Materialize  (cost=0.00..1.05 rows=3 width=1168) (actual
time=0.030..0.033 rows=3 loops=1)
    ->  Seq Scan on stgtbl_2798778 stgtbl  (cost=0.00..1.04 rows=3
width=1168) 
    (actual time=0.027..0.028 rows=3 loops=1)
        Filter: (execution_id = 1015939)
Planning Time: 0.187 ms
Execution Time: 0.217 ms

Steps to reproduce:
Run the UPDATE query as stated above.
Observe the sequence values returned for each record before VACUUM ANALYZE
is executed.
Run the VACUUM ANALYZE operation on the table stgtbl_837465r4.
Re-run the same UPDATE query.
Observe that the sequence returns the same value (e.g., 4) for all updated
records.

Kindly review the scenario and What could cause discrepancies in the results
for the same update script? Additionally, what actions should be taken if
the script yields identical outputs in both scenarios (with and without the
Vacuum Analyze operation)? Is there any option to have the same query
planner for both the scenarios?