BUG #15575: Using RDS upgrade option from 9.6 to 10.4 Upsert will notinsert records but complete successfully - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #15575: Using RDS upgrade option from 9.6 to 10.4 Upsert will notinsert records but complete successfully
Date
Msg-id 15575-08e99001a3dfa56a@postgresql.org
Whole thread Raw
Responses Re: BUG #15575: Using RDS upgrade option from 9.6 to 10.4 Upsertwill not insert records but complete successfully
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      15575
Logged by:          Ash Marath
Email address:      makmarath@hotmail.com
PostgreSQL version: 10.4
Operating system:   RDS (on AWS)
Description:

Scenario :
1.Have 2 tables created. 
    * main table
    * audit Table  
2. Create an Update trigger on Main table  => The trigger will make a new
entry in the audit table
The above structure is created in v9.6
-------------------------------
ISSUE STARTS HERE
3. Use the AWS console and upgraded your PostgreSQL to ver 10x 
4. Run the upsert SQL statement (update a particular column) on Main
table.
5. BUG: The column in Main table will not be updated but a new entry in the
audit table can be observed (update trigger will execute). Execute step 4
multiple times and you will notice new entries in the Audit table but record
in the main table will not be updated.
----------------------------------------
Workaround Steps to resolve this bug:
S1: Take a backup of the existing database from v9.6, generate the script
using "pg_dump" (prefer to dump output as .sql file).
S2. Create a new instance in RDS for v10.x 
S3. Restore the database from the backupfile generated by pg_dump in step
S1.
-----------------------------------------------------------------
Now run the upset statement of Step 4 and you will see postgreSQL
functioning correctly. Upsert will work and existing records will be
updated.
---------------------- End of report
----------------------------------------

Thanks
Ash Marath
makmarath@hotmail.com


pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #15574: Upgrading postgis broke pg_dump
Next
From: Dmitry Molotkov
Date:
Subject: Re: BUG #15446: Crash on ALTER TABLE