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

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

PG Doc comments form
The following bug has been logged on the website:

Bug reference:      15575
Logged by:          Ash Marath
Email address:      [hidden email]
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15575: Using RDS upgrade option from 9.6 to 10.4 Upsert will not insert records but complete successfully

Andres Freund
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:      [hidden email]
> 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

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15575: Using RDS upgrade option from 9.6 to 10.4 Upsert will not insert records but complete successfully

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment