how do you prevent the filter expression from processing a DELETE operation. When my expression processes a delete it causes the table replication to suspend with error message:
Table 'ods_owner'.'ppd_entry_bob' (subtask 0 thread 1) is suspended. Cannot compute expression, not all column values are in the data record.
my expression is: $upsert_user!='qlikreplicate'
where upsert_user is a string field in the ppd_entry_bob table
I happened to have some background information. I first tested this myself on a msSQL source and it worked fine. This problem is specific to the (unspecified) source database which as it turns out was PostgreSQL.
The Qlik Replicate error message was right on the mark, the column used in the test simply was not in the WAL datastream for a DELETE operation. It was there for INSERTs and UPDATEs.
The workaround/solution is to ALTER TABLE xxx REPLICA IDENTITY FULL (which is relatively high overhead)
Or, add the filter column(s) to the PK Constraint and ALTER TABLE xxx REPLICA IDENTITY USING INDEX pk (ugly)
btw... the filter was a second level filter meant for insert/updates where the first level filter excluded DELETE. One might that the second level is not executed when the first level fails, but that's not the case. The whole filter is first evaluated/parsed (by SQLite).
I happened to have some background information. I first tested this myself on a msSQL source and it worked fine. This problem is specific to the (unspecified) source database which as it turns out was PostgreSQL.
The Qlik Replicate error message was right on the mark, the column used in the test simply was not in the WAL datastream for a DELETE operation. It was there for INSERTs and UPDATEs.
The workaround/solution is to ALTER TABLE xxx REPLICA IDENTITY FULL (which is relatively high overhead)
Or, add the filter column(s) to the PK Constraint and ALTER TABLE xxx REPLICA IDENTITY USING INDEX pk (ugly)
btw... the filter was a second level filter meant for insert/updates where the first level filter excluded DELETE. One might that the second level is not executed when the first level fails, but that's not the case. The whole filter is first evaluated/parsed (by SQLite).
