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

On Wed, Jan 1, 2025 at 12:39 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18762
> Logged by: Siva Thalaimalai
> Email address: siva(dot)thalaimalai(at)chainsys(dot)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.