Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #18762: Update script produces distinct outputs before and after vacuum analyze operation - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: BUG #18762: Update script produces distinct outputs before and after vacuum analyze operation
Date
Msg-id CAKFQuwawkG2FV_4DOqvOpwRB3kxCzp3YCvh_4toc-K_JeN3=pA@mail.gmail.com
Whole thread Raw
In response to BUG #18762: Update script produces distinct outputs before and after vacuum analyze operation  (PG Bug reporting form <noreply@postgresql.org>)
List pgsql-bugs
Tree view
On Wed, Jan 1, 2025 at 12:39 PM PG Bug reporting form <noreply@postgresql.org> wrote:
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;


This seems to be a near-duplicate of Bug Report # 18761.  The answer is the same.  You have written a query that has a non-deterministic outcome.  You need to add a condition to the where clause relating the table being updated (stgtbl_837465r4) and the from clause.

https://www.postgresql.org/message-id/18761-624bbdc5e7434bdb@postgresql.org

David J.

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #18762: Update script produces distinct outputs before and after vacuum analyze operation
Next
From: Noah Misch
Date:
Subject: Re: Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically.