Thread: BUG #15575: Using RDS upgrade option from 9.6 to 10.4 Upsert will notinsert records but complete successfully

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

Hi,

On 2019-01-04 20:31:10 +0000, PG Bug reporting form wrote:
> 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
> ----------------------------------------

You got to take this up with Amazon. We don't know how their internal
stuff works, and their bugs aren't our bugs.

Greetings,

Andres Freund

On Fri, Jan 04, 2019 at 01:14:03PM -0800, Andres Freund wrote:
> You got to take this up with Amazon. We don't know how their internal
> stuff works, and their bugs aren't our bugs.

If you can get a bug reproducible with vanilla Postgres, that's of
course a different story.
--
Michael