[GENERAL] Error that shouldn't happen?

[Rob Brucks]

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