BUG #15575: Using RDS upgrade option from 9.6 to 10.4 Upsert will not insert 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