[Enhancement]: Support MERGE with update/delete actions on a compressed hypertable

[Ansssss]

What type of enhancement is this?

User experience

What subsystems and features will be improved?

Compression

What does the enhancement do?

The MERGE sql command is supported on hypertables. However, if the hypertable has compression enabled, then the update/delete actions are not supported in a merge. This change would allow those to be used in a merge statement.

Test showing the existing limitation:

DROP TABLE IF EXISTS test_table_1;

CREATE TABLE test_table_1 
(
  dt        TIMESTAMPTZ       NOT NULL,
  asset_id    int             NOT NULL,
  temperature DOUBLE PRECISION  NULL
);

SELECT create_hypertable('test_table_1', 'dt');

--****comment out next line to see that merge works on hypertable without compression enabled****/
ALTER TABLE test_table_1 SET (timescaledb.compress, timescaledb.compress_segmentby = 'asset_id');

INSERT INTO test_table_1(dt, asset_id, temperature) VALUES ('2001-01-01', 123, 25.0);

MERGE INTO test_table_1 AS target
USING
(
	SELECT '2001-01-01'::timestamp, 123, 42.0
) AS src(dt,asset_id,temperature)
ON(target.asset_id = src.asset_id AND target.dt = src.dt)
WHEN MATCHED THEN UPDATE
    SET temperature = src.temperature
WHEN NOT MATCHED THEN INSERT(dt, asset_id, temperature)
VALUES (src.dt, src.asset_id, src.temperature);
/*
ERROR:  The MERGE command with UPDATE/DELETE merge actions is not support on compressed hypertables 

SQL state: 0A000
*/

select * from test_table_1;

Possibly related to: #4113

Implementation challenges

No response

[Ansssss]

Also related - #5150

[aarondglover]

Should this be closed as #5150 has been merged?

[Ansssss]

@aarondglover No - I linked to 5510 because that added initial merge support. However, this is requesting additional merge functionality that is not currently present.