DELETE, MERGE statement problems in Trino 396 with PostgreSQL connector

[kaizenjinco]

I have encountered some unexpected errors when using Trino 396 (lastest version) with PostgreSQL connector

1. DELETE
   According to this document: https://trino.io/docs/396/sql/delete.html#limitations. Trino also support DELETE statement in various ways.
   Everything works well when WHERE condition doesn't contains any subquery
   delete from catalog.schema.table where 1 = 2
   But it doesn't work when I insert a subquery like this:
   delete from catalog.schema.table where 1 in (select 1)
   The error is: SQL Error [13]: Query failed (#20220920_082123_00038_z922t): Unsupported delete
2. MERGE
   dbt-trino also add MERGE statement in their list. But PostgreSQL 14 does not support it right now. Any body has the list of connectors supporting MERGE would be great. Many thanks :D

[hashhar]

re: DELETE

DELETE in JDBC connectors is only supported when the predicate of the query can be pushed down to the connector. Subqueries can't be pushed down yet but predicates on columns like where some_id IN (1,2,3,4) etc. can be.

This is mentioned at https://trino.io/docs/current/connector/postgresql.html#sql-delete. And improvement is tracked under #6993

re: MERGE

Only Delta Lake, Hive, Iceberg, Kudu and Raptor connectors support MERGE right now. There is some work in progress to bring it to other connectors as well - #13621

It will also have nice side effect that DELETE will also become more powerful for JDBC connectors as well.

I'm closing this issue since issues exist to track both improvements.

[kaizenjinco]

Thanks for you reply <3

[kaizenjinco]

@hashhar After many tries, I have discovered somethings wrong in DELETE statement. Trino only support to delete only 32 predicates, if I exceed this limit then SQL Error [13]: Query failed (#20220920_121019_00306_z922t): Unsupported delete
Error query here:
delete from "psql_cbquant"."dmt"."dmt_customer" where (id) in ( '1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32', '' )

[hashhar]

See https://trino.io/docs/current/connector/postgresql.html#domain-compaction-threshold.

Some databases (Oracle, Postgres etc.) quickly degrade performance with a large IN list. When size of IN list exceeds domain-compaction-threshold (default 32) we simplify the IN to some other operation which may or may not be able to be pushed down.

You can increase domain-compaction-threshold to a higher value but beware that it can reduce performance of queries for larger values.

[kaizenjinco]

@hashhar wow, thanks, that's look new to me