Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-13362

Housekeeper potentially deleting the wrong row when PostgreSQL partitioning is used

    Details

    • Team:
      Team C
    • Sprint:
      Sprint 27, Sprint 28, Sprint 29
    • Story Points:
      1

      Description

      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
      

        Attachments

          Activity

            People

            • Assignee:
              s.paskevics Sergejs Paskevics
              Reporter:
              rtau Raymond Tau
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: