babelfish_extensions icon indicating copy to clipboard operation
babelfish_extensions copied to clipboard
verified publisher icon babelfish-for-postgresql

[Question]: MERGE support

Open Belorus opened this issue 2 years ago • 7 comments

What happened?

Hi,

First of all - huge thanks for the tool. It lets projects get out of per-core licensing burden and try amazing open source database with baby steps. This is so helpful when project is huge and simple rewrite is impossible

I noticed in your repo that you've already create version on top of PG15 which supports MERGE syntax. May i ask if you guys have plans & ETA of release with support of MERGE? Our project unfortunatelly heavily depends on this syntax, and according to compass it is the last barrier between past and future for us.

Version

BABEL_3_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

No response

Relevant log output

No response

Code of Conduct

  • [X] I agree to follow this project's Code of Conduct.

Belorus avatar Mar 04 '23 07:03 Belorus

MERGE is on the long-term roadmap but without any specific timeline. Note that PG15's implementation of MERGE does not fully cover all sides of T-SQL's MERGE. Among other things, PG does not support NOT MATCHED BY SOURCE or the RETURNING clause, both of which we see being used quite a bit by SQL Server apps. Today, you may want to look at the -rewrite option of the Babelfish Compass tool (https://github.com/babelfish-for-postgresql/babelfish_compass/releases/latest), which will rewrite MERGE as a series of INSERT/UPDATE/DELETE T-SQL statements which Babelfish does support.

robverschoor avatar Mar 04 '23 13:03 robverschoor

Hi @robverschoor

Just curious if dates became slightly more clear on MERGE support?

We have big legacy project (200k LoC SQL) and use your compass to clean the code step by step, and from 2500 errors 6mo ago we now have only 50.

It seems that we only miss MERGE. One more magic that is hard to get rid of is indexed views, but we have no choice here with postgres :)

Thank you!

Belorus avatar Aug 24 '23 07:08 Belorus

There is no change to PG's support for unsupported MERGE aspects, which is the main issue. Using Compass -rewrite remains the best workaround.

robverschoor avatar Aug 24 '23 11:08 robverschoor

Hi @robverschoor , Have your plans changed for PG17 taking into account support of mentioned MERGE features in PG? https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c649fa24a42ba89bf5460c7110e4fc8eeca65959 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0294df2f1f842dfb0eed79007b21016f486a3c6c

Thanks!

Belorus avatar Jun 05 '24 18:06 Belorus

Once Babelfish supports the new PG 17 features and thus supports T-SQL MERGE, the Compass -rewrite option will be retired for the Babelfish version in which that support is padded.

robverschoor avatar Jun 05 '24 19:06 robverschoor

So do you plan supporting MERGE after PG17 release? Or it is still "to be decided?

Belorus avatar Jun 06 '24 06:06 Belorus

Compass follows the actual implementation of features in Babelfish. As to whether/when MERGE will be fully supported, it is on the long-term roadmap.

robverschoor avatar Jun 06 '24 08:06 robverschoor