Deleting duplicate records on source deletes all the records on target.

[JrQ]

Source: Oracle

Table does not have PK/UK. We found duplicate records in table, we deleted duplicate using rowid. So now only duplicate records are delete from the table.  For example there were two rows of record (1,'ONE'). With eliminating duplicates we have only one row of record (1,'ONE'). 

Target: Postgresql

We saw that all the rows of  records (1,'ONE') are gone from target. 

There is no PK/UK on source or target. 

Is this normal with Qlik or its just the bug of my version?

[Heinvandenheuvel · Accepted solution]

Well, there is a documented restriction right in the introduction of the Replicate userguide: "l When replicating tables without a Primary Key, there is no way to verify whether a record already
exists on the target. This may result in data inconsistency when UPDATE and DELETE operations
are performed on the target database"

And again under the PostgreSQL end-point: "l Both the source table and the corresponding target table must have an identical Primary Key. In the
event that one of the tables does not have a Primary Key, the result of DELETE and UPDATE record
operations will be unpredictable. "

So I'd say it is a documented restriction. What you need to understand for this is that Replicate does NOT see the source SQL, only the effects. So it see a row, with certain column values being deleted. It then essentially tells the target "delete from <table> where col1='val1',  col2='val2'...". That's all standard SQL offers. Guess what happens?! all rows that match that will be deleted.

Now SOME sqls have extentions like I believe

• SQLserver has "DELETE TOP 1 FROM TABLE WHERE ..."   (Supported by Replicate - I seem to recall!) 
• MySQL has ""DELETE FROM TABLE WHERE ... [ORDER .. ] LIMIT 1".  
• Oracle needs "delete from table where RowID in (select RowID from table where rownum <= 1)
• PostgreSQL - best I know -  also needs "DELETE FROM YourTable WHERE ctid IN ( SELECT ctid FROM YourTable [ORDER BY ...] LIMIT 1 )

As you see, no standard. Therefor Replicate has to chose how 'reasonable' a workaround solution is and whether to make one for the presumed customer base. This may change over time and versions.

You probably should submit a formal support ticket and  depending on its answer submit an improvement request. Or just live with the perfectly reasonable (IMHO) restriction and inform your application folks about is. Maybe they can come up with a solution quicker than a (typically months if not years long wait for an improvement request).

Hope this helps,

Hein.

[JrQ]

Hein, totally make sense. 

thanks.