-
Problem report
-
Resolution: Fixed
-
Minor
-
3.4.6
-
Database: PostgreSQL 9.5
-
Sprint 27, Sprint 28, Sprint 29
-
1
When Housekeeper is enabled and MaxHousekeeperDelete is not 0 (defaults to 5000), it is found that SQLs like this are issued, to cleanup deleted items:
delete from history_uint where ctid = any(array(select ctid from history_uint where itemid=36109 limit 5000))
However, if partitioning is done, such as https://www.zabbix.org/wiki/Higher_performant_partitioning_in_PostgreSQL, the ctid may not be unique across inherited tables, demonstrated in http://sqlfiddle.com/#!17/b9e7d/3/0, preparing the test table as below:
CREATE OR REPLACE FUNCTION main_insert_trigger() RETURNS TRIGGER AS 'BEGIN IF (NEW.id < 10) THEN INSERT INTO child_1 VALUES (NEW.*); ELSIF (NEW.id >= 10 and NEW.id < 20) THEN INSERT INTO child_2 VALUES (NEW.*); ELSE RETURN NEW; END IF; RETURN NULL; END;' LANGUAGE plpgsql; create table main (id INTEGER, value INTEGER); create table child_1 (check (id < 10)) INHERITS (main); create table child_2 (check (id >=10 and id < 20)) INHERITS (main); CREATE TRIGGER insert_main_trigger BEFORE INSERT ON main FOR EACH ROW EXECUTE PROCEDURE main_insert_trigger(); insert into main values (1,1),(10,2),(100,3);
Which
select tableoid, ctid,* from main;
would produce result like:
tableoid ctid id value 17361 (0,1) 100 3 17364 (0,1) 1 1 17368 (0,1) 10 2
