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