[Feature Request]: Support for INDEX with IGNORE_DUP_KEY=ON

[PauloHMattos]

The applications of my company rely heavily on this option and without it, we are effectively unable to move to babelfish.

Is there any plan to support in the near future?

[Deepesh125]

@PauloHMattos We are already aware about this limitation and is under assessment. Just to get more clarity, how is it absolutely important for your use case, have you tried any alternatives, and (if so) why don't workaround works for your use case? Thank you!

[PauloHMattos]

@Deepesh125 right now we are evaluating performing this inserts directly in the PG INSERT ON CONFLICT DO NOTHING, but we are not sure if there's a recommended way to handle this limitation. Do you know how other users of Babelfish handle this?

To us seems a viable option, but our team is not very knowledgeable of PG and Babelfish in general so there are a lot of unknows. This being supported directly by Babelfish would reduce considerably the risk

Thanks for the help!

[tanscorpio7]

I think you could try creating a rule from postgres endpoint to workaround this

-- Babelfish endpoint
1> CREATE TABLE t (id INT PRIMARY KEY)
2> GO

-- Postgres endpoint
-- CREATE RULE from postgres endpoint to ignore an insert based on some conditions

babelfish_db=# CREATE OR REPLACE RULE ignore_insert_when_dup_pk AS ON INSERT
TO master_dbo.t WHERE EXISTS (SELECT 1 FROM master_dbo.t WHERE id = NEW.id)
DO INSTEAD NOTHING;
CREATE RULE

-- Babelfish endpoint
1> INSERT INTO t VALUES (1), (3)
2> GO

(2 rows affected)

1> INSERT INTO t VALUES (1), (2), (3), (4)
2> GO

(2 rows affected)

1> SELECT * FROM t
2> GO
id         
-----------
          1
          3
          2
          4

(4 rows affected)

This is still not same as IGNORE_DUP_KEY since the rule is defined at table level and not index level.

[robverschoor]

Warning: There is a big difference in transactional behaviour between this workaround and IGNORE_DUP_KEY, especially when multiple rows are inserted in the transaction. In addition, there is no guarantee that the rule will be correctly processed when upgrading to a future version of Babelfish (it may work, and you'd be lucky).

[tanscorpio7]

Right, if the insert statement has the same key occurring more than once and the table did not have the key before the insert statement, we will still hit the duplicate key error. So in the same example some thing like this would still fail

1> INSERT INTO t VALUES (5), (5)
2> GO
Msg 2627, Level 14, State 1, Server BABELFISH, Line 1
duplicate key value violates unique constraint "t_pkey"

because id = 5 did not exists before the insert statement.