On 2021-Nov-14, Amit Langote wrote: > The only problem caused by the code block that follows the buggy if > statement unconditionally executing is wasted cycles. Fortunately, > there's no correctness issue, because rootRelInfo is the same as the > input result relation in the cases where the latter is not partitioned > and there'd be no map to convert the tuple, so the block is basically > a no-op. I was afraid about the case where the input relation is a > regular inheritance parent, though apparently we don't support MERGE > in that case to begin with.
Well, I noticed that if we simply remove the ERROR that prevents that case, it works fine as far as I can tell. Example (partly cribbed from the documentation): CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ); CREATE TABLE measurement_y2006m02 ( CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2007m01 ( filler text, peaktemp int, logdate date not null, city_id int not null, unitsales int CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2007-02-01') ); ALTER TABLE measurement_y2007m01 DROP COLUMN filler; ALTER TABLE measurement_y2007m01 INHERIT measurement; CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2007-01-01' AND NEW.logdate < DATE '2007-02-01' ) THEN INSERT INTO measurement_y2007m01 (city_id, logdate, peaktemp, unitsales) VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql ; CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger(); INSERT INTO measurement VALUES (1, '2006-02-10', 35, 10); INSERT INTO measurement VALUES (1, '2006-02-16', 45, 20); INSERT INTO measurement VALUES (1, '2006-03-17', 25, 10); INSERT INTO measurement VALUES (1, '2006-03-27', 15, 40); INSERT INTO measurement VALUES (1, '2007-01-15', 10, 10); INSERT INTO measurement VALUES (1, '2007-01-17', 10, 10); alvherre=# select tableoid::regclass, * from measurement order by city_id, logdate; tableoid | city_id | logdate | peaktemp | unitsales ----------------------+---------+------------+----------+----------- measurement_y2006m02 | 1 | 2006-02-10 | 35 | 10 measurement_y2006m02 | 1 | 2006-02-16 | 45 | 20 measurement_y2006m03 | 1 | 2006-03-17 | 25 | 10 measurement_y2006m03 | 1 | 2006-03-27 | 15 | 40 measurement_y2007m01 | 1 | 2007-01-15 | 10 | 10 measurement_y2007m01 | 1 | 2007-01-17 | 10 | 10 CREATE TABLE new_measurement (LIKE measurement); INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10); INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10); INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20); INSERT INTO new_measurement VALUES (1, '2006-03-27', NULL, NULL); INSERT INTO new_measurement VALUES (1, '2007-01-17', NULL, NULL); INSERT INTO new_measurement VALUES (1, '2007-01-15', 5, NULL); INSERT INTO new_measurement VALUES (1, '2007-01-16', 10, 10); MERGE into measurement m USING new_measurement nm ON (m.city_id = nm.city_id and m.logdate=nm.logdate) WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE WHEN MATCHED THEN UPDATE SET peaktemp = greatest(m.peaktemp, nm.peaktemp), unitsales = m.unitsales + coalesce(nm.unitsales, 0) WHEN NOT MATCHED THEN INSERT (city_id, logdate, peaktemp, unitsales) VALUES (city_id, logdate, peaktemp, unitsales); alvherre=# select tableoid::regclass, * from measurement order by city_id, logdate; tableoid | city_id | logdate | peaktemp | unitsales ----------------------+---------+------------+----------+----------- measurement_y2006m02 | 1 | 2006-02-10 | 35 | 10 measurement_y2006m02 | 1 | 2006-02-16 | 50 | 30 measurement_y2006m03 | 1 | 2006-03-01 | 20 | 10 measurement_y2006m03 | 1 | 2006-03-17 | 25 | 10 measurement_y2007m01 | 1 | 2007-01-15 | 10 | 10 measurement_y2007m01 | 1 | 2007-01-16 | 10 | 10 measurement_y2006m02 | 2 | 2006-02-10 | 20 | 20 Even the DELETE case worked correctly (I mean, it deletes from the right partition). So I'm considering adding this case to the regression tests and remove the limitation. If anybody wants to try some more sophisticated examples, I'll welcome proposed additions to the regression tests. (Don't get me wrong -- I don't think this is terribly useful functionality. I just think that if the code is already prepared to handle it, we may as well let it.) One thing I didn't quite like is that the count of affected tuples seems off, but IIRC that's already the case for trigger-redirected tuples in legacy-style partitioning, so I'm not too worried about that. -- Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/