Error that shouldn't happen?

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