Thread: BUG #18761: Sequence Value Discrepancy After Running VACUUM ANALYZE

The following bug has been logged on the website:

Bug reference:      18761
Logged by:          Ganeshpandi Eswaran
Email address:      ganeshpandi.eswaran@chainsys.com
PostgreSQL version: 13.15
Operating system:   Oracle sql developer
Description:

Issue Report: Sequence Value Discrepancy After Running VACUUM ANALYZE
Description:
After executing the following UPDATE query, which updates a table using a
PostgreSQL sequence to assign a batch number:

SQL query: 

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;

Expected Behavior:
The sequence (sample_sequence__2899) should return different sequence
numbers (e.g., 1, 2, 3) for each record updated in the UPDATE query.

Observed Behavior:
After running VACUUM ANALYZE on the table stgtbl_837465r4, the sequence
returns the same value (e.g., 4) for all records, even though it should
return unique values for each record.

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 provide a solution to the issue due to this issue affecting our most
functionality

The following bug has been logged on the website:

Bug reference:      18761
Logged by:          Ganeshpandi Eswaran
Email address:      ganeshpandi.eswaran@chainsys.com
PostgreSQL version: 13.15
Operating system:   Oracle sql developer
Description:       

Issue Report: Sequence Value Discrepancy After Running VACUUM ANALYZE
Description:
After executing the following UPDATE query, which updates a table using a
PostgreSQL sequence to assign a batch number:

SQL query:

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;

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, December 31, 2024, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> 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;

> You never equate the rows of PVQUERY to the rows of stgtbl_837465r4.
> Tables are not ordered so unless you write what is basically a join
> condition if the where clause an update-from query is malformed and you end
> up with non-sensical random changes (well, unless the from query only
> produces a single row).

Yeah, this is a faulty query.  But I'd explain it slightly
differently: when you write UPDATE ... FROM ..., you are writing
a join between the update target table and whatever is in FROM.
For predictable results, you must write a WHERE clause that
ensures that each row of the target table joins to at most one
row of FROM.  For any given row of the target table, what
happens is:

* If there is no join partner row, nothing happens.

* If there is one join partner row, the target row is
updated using the values from that partner row.

* If there is more than one join partner row, the target row is
updated using the values from some unspecified partner row.

The described symptoms sound like the same FROM row was used
to update all of the target rows, but that's only one of the
possible outcomes from this underspecified join.

Another problem with the query as written is that I don't think
we guarantee that the sub-select involving NEXTVAL will be
executed exactly once.  I'd put that in a WITH clause to be safe.

            regards, tom lane