[GENERAL] 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