|
Hello Everyone, I've run into a strange error on the PostgreSQL 9.5.4 DB we use for our Zabbix Server. I implemented auto-partitioning based on the design from this wiki article:
https://www.zabbix.org/wiki/Docs/howto/zabbix2_postgresql_autopartitioning I implemented auto-partitioning for the history_uint table using the following trigger function: CREATE FUNCTION zbx_part_trigger_func() RETURNS trigger LANGUAGE plpgsql AS $_$ DECLARE prefix text := 'partitions'; timeformat text; selector text; _interval interval; tablename text; startdate text; enddate text; create_table_part text; create_index_part text; BEGIN selector = TG_ARGV[0]; IF selector = 'hour' THEN timeformat := 'YYYY_MM_DD_HH24'; ELSIF selector = 'day' THEN timeformat := 'YYYY_MM_DD'; ELSIF selector = 'month' THEN timeformat := 'YYYY_MM'; ELSE RAISE EXCEPTION 'zbx_part_trigger_func: Specify "hour", "day", or "month" for interval selector instead of "%"', selector; END IF; _interval := '1 ' || selector; tablename := TG_TABLE_NAME || '_p' || to_char(to_timestamp(NEW.clock), timeformat); EXECUTE 'INSERT INTO ' || quote_ident(prefix) || '.' || quote_ident(tablename) || ' SELECT ($1).*' USING NEW; RETURN NULL; EXCEPTION WHEN undefined_table THEN startdate := extract(epoch FROM date_trunc(selector, to_timestamp(NEW.clock))); enddate := extract(epoch FROM date_trunc(selector, to_timestamp(NEW.clock) + _interval )); create_table_part := 'CREATE TABLE IF NOT EXISTS ' || quote_ident(prefix) || '.' || quote_ident(tablename) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS (' || TG_TABLE_NAME || ')'; create_index_part := 'CREATE INDEX ' || quote_ident(tablename) || '_1 on ' || quote_ident(prefix) || '.' || quote_ident(tablename) || '(itemid,clock)'; EXECUTE create_table_part; EXECUTE create_index_part; --insert it again EXECUTE 'INSERT INTO ' || quote_ident(prefix) || '.' || quote_ident(tablename) || ' SELECT ($1).*' USING NEW; RETURN NULL; END; $_$; With this trigger (no other triggers defined): zbx_partition_trg BEFORE INSERT ON history_uint FOR EACH ROW EXECUTE PROCEDURE zbx_part_trigger_func('day'); I had fully expected race conditions to occur on a very busy system and throw errors trying to create the table, but instead I got the following index creation error: ERROR: relation "history_uint_p2017_05_17_1" already exists CONTEXT: SQL statement "CREATE INDEX history_uint_p2017_05_17_1 on partitions.history_uint_p2017_05_17(itemid,clock)" PL/pgSQL function zbx_part_trigger_func() line 43 at EXECUTE STATEMENT: insert into history_uint (itemid,clock,ns,value) values (73800,1494979201,11841804,99382669312),(30061,1494979201,17605067,0); I am unable to figure out how the trigger was able to successfully create the table, but then fail creating the index. I would have expected one thread to "win" and create both the table and index, but other
threads would fail when creating the table… but NOT when creating the index. The only other function defined in the system is the "cleanup" function which was not running at the time. The target table and index were still created. Can anyone shed any light on how this could have occurred? Is this a bug or am I missing something? Pertinent details: · PostgreSQL 9.5.4 installed from PGDG packages on Centos 7.3.1611 · Zabbix 3.2 server Thanks, Rob Brucks |
