View Issue Details

IDProjectCategoryView StatusLast Update
0005356SymmetricDSBugpublic2022-10-03 14:38
Reporterpmarzullo Assigned Topmarzullo  
Status assignedResolutionopen 
Product Version3.12.5 
Summary0005356: INSERT transform are getting converted into UPDATE and drops INSERT event actions
DescriptionWe see the INSERT transactions are missing audit_insert_dt/audit_insert_login/audit_insert_pgm fields. We populate those fields only in case of INSERT event. For some reason INSERTs are being converted into UPDATE (even though target is missing the row) and accordingly audit_update_* fields are being populated. It doesn't create a problem for audit_insert_dt/audit_insert_login due to default values enforced at database level in target, however we do not have any default for audit_insert/update_pgm fields. Lot of our transforms use constant value of ' SymmetricDS ' for audit_insert_pgm/audit_update_pgm field.

2021-03-22 15:10:26,269 INFO [CPE_POSTGRES-CPE_PG] [DefaultDatabaseWriter] [cpe_postgres-cpe_pg-dataloader-1] Failed to process update event in batch MF_PG-1098733 on channel 'reload'.
Failed sql was: insert into "cli_enroll"."service_fee_code_xref" ("fee_cd", "contract_typ_cd", "pgm_cd", "audit_update_dt", "audit_update_login", "audit_update_pgm", "row_removed_from_use_ind") values ('P20F',0,0,{ts '2021-03-2215:10:26.267'},'gen_sym_user','SymmetricDS',0) on conflict do nothing Failed raw sql was: insert into "cli_enroll"."service_fee_code_xref" ("fee_cd", "contract_typ_cd", "pgm_cd", "audit_update_dt", "audit_update_login", "audit_update_pgm", "row_removed_from_use_ind") values (?,?,?,?,?,?,?) on conflict do nothing Failed sql parameters: ['P20F', 0, 0, {ts '2021-03-22 15:10:26.267'}, 'gen_sym_user', 'SymmetricDS', 0] Failed sql parameters types: [CHAR, SMALLINT, SMALLINT, TIMESTAMP, VARCHAR, VARCHAR, BIT] Failed sql state and code: 23502 (0) Failed pk data was: "P20F"
Failed row data was: "P20F",,"0","0","151","0","PTAX 20 F","FEE - PAYMENT OF PTAX ALL","ONE"," ","false"," ","2021-03-22 15:10:26.267","gen_sym_user","SymmetricDS","false"
 StackTraceKey.init [SqlException:798647874] org.jumpmind.db.sql.SqlException: ERROR: null value in column "audit_insert_pgm" violates not-null constraint
  Detail: Failing row contains (P20F, 0, 0, 2021-03-22 15:10:18.393891, gen_sym_user, null, 2021-03-22 15:10:26.267, gen_sym_user, SymmetricDS, f).
Additional InformationThis is on the reload channel, so this must be an issue on the reload of a table.
Possibly because of "on conflict do nothing"?
Tagsdialect: postgresql


There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2022-07-11 15:58 pmarzullo New Issue
2022-07-11 15:58 pmarzullo Status new => assigned
2022-07-11 15:58 pmarzullo Assigned To => pmarzullo
2022-07-11 15:58 pmarzullo Tag Attached: dialect: postgresql
2022-07-25 16:52 elong Target Version 3.14.0 => 3.14.1
2022-08-24 18:56 elong Target Version 3.14.1 => 3.14.2
2022-10-03 14:38 elong Target Version 3.14.2 =>