Обсуждение: [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

According to this post, adding "if not exists" won't really help for race conditions.

"The bottom line is that CREATE TABLE IF NOT EXISTS doesn't pretend to

handle concurrency issues any better than regular old CREATE TABLE,

which is to say not very well." - Robert Haas

https://www.postgresql.org/message-id/CA+TgmoZAdYVtwBfp1FL2sMZbiHCWT4UPrzRLNnX1Nb30Ku3-gg@mail.gmail.com

It still doesn't explain how the function got past creating the table, but failed on the index.  If another thread was also creating the table then there should have been lock contention on the create table statement.

Thanks,

Rob

Thanks.

I can code an exception block to handle the table problem, and probably one for the index collision too.

My point is how did two concurrent threads successfully create the same table?  That had to have happened if one of the threads hit a duplicate index error.

It almost seems like Postgres skipped checking for duplicate tables due to some timing issue.  I don't want my DB to ending up hosed by something like that.

Thanks,

Rob