[GitHub] [shardingsphere] sandynz commented on pull request #16837: Support PostgreSQL insert on conflict do update in scaling job

[GitBox <gi...@apache.org>]

sandynz commented on PR #16837:
URL: https://github.com/apache/shardingsphere/pull/16837#issuecomment-1099178159

   For table:
   ```
   CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
   ```
   `order_id` is primary key and shardig key.
   
   A special case:
   If a record with `order_id=101` is deleted, and then a new record is inserted, the new record has the same primary key `order_id=101` and `status='updated'`, record with `order_id=101` won't be updated.
   
   It's found in a testing, related log:
   ```
   [INFO ] 2022-04-14 19:13:41.011 [_finished_check_Worker-1] o.a.s.d.p.c.s.c.c.DataMatchSingleTableDataCalculator - record column value not match, value1=insert2022-04-14T19:12:35.844, value2=insert2022-04-14T18:56:53.799, record1=[3, 950, insert2022-04-14T19:12:35.844], record2=[3, 950, insert2022-04-14T18:56:53.799]
   ```
   The later value is not updated, but it's captured and put into channel:
   ```
   [INFO ] 2022-04-14 19:12:35.812 [ShardingSphere-Scaling-execute-0] o.a.s.d.p.p.i.PostgreSQLWalDumper - dump, event=UpdateRowEvent(super=AbstractRowEvent(super=AbstractWalEvent(logSequenceNumber=org.apache.shardingsphere.data.pipeline.postgresql.ingest.wal.decode.PostgreSQLLogSequenceNumber@5451b6d4), schemaName=public, tableName=t_order_3), afterRow=[3, 950, update2022-04-14T19:12:35.799]), record=DataRecord(columns=[order_id=3, user_id=950, status=update2022-04-14T19:12:35.799], primaryKeyValue=[3], oldPrimaryKeyValues=[3], type=UPDATE, tableName=t_order)
   
   [INFO ] 2022-04-14 19:12:35.853 [ShardingSphere-Scaling-execute-0] o.a.s.d.p.p.i.PostgreSQLWalDumper - dump, event=WriteRowEvent(super=AbstractRowEvent(super=AbstractWalEvent(logSequenceNumber=org.apache.shardingsphere.data.pipeline.postgresql.ingest.wal.decode.PostgreSQLLogSequenceNumber@5d26c895), schemaName=public, tableName=t_order_3), afterRow=[3, 950, insert2022-04-14T19:12:35.844]), record=DataRecord(columns=[order_id=3, user_id=950, status=insert2022-04-14T19:12:35.844], primaryKeyValue=[3], oldPrimaryKeyValues=[3], type=INSERT, tableName=t_order)
   ```
   
   The reason is `PostgreSQLPipelineSQLBuilder.buildConflictSQL` use `ON CONFLICT DO NOTHING`.
   It will be repalced to `ON CONFLICT DO UPDATE SET`.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org