Nested Exceptions in PL/pgSQL

Our Zabbix monitoring system uses PostgreSQL as the underlying database and we decided to implement partitioning since we’re expecting it to grow fairly large, plus we’re already seeing the Zabbix housekeeper CPU usage going fairly high.

So my colleague found this article about auto partitioning for Zabbix. I tested it out on our test system and everything appeared to work perfectly.

Then we installed it in production. Everything looked fine until I checked the DB logs the next day and discovered these errors from midnight:

ERROR:  relation "history_p2017_05_17" already exists
CONTEXT:  SQL statement "CREATE TABLE IF NOT EXISTS partitions.history_p2017_05_17 (CHECK ((clock >= '1494979200' AND clock < '1495065600'))) INHERITS (history)"
PL/pgSQL function trg_partition() line 39 at EXECUTE
insert into history (itemid,clock,ns,value) values (36958,1494979198,640977203,0.000000);

I immediately realized what was happening.  Our production Zabbix was much busier than our test Zabbix and what we were seeing were race conditions occurring when it was time to create new partitions. Two threads were both executing the trigger function to create the new partition table at the same time and one succeeded and the other failed.  The failure resulted in the insert being discarded… not a disaster for monitoring but not good either.

I decided to fix the procedure, and here’s what I did:

Original Function Pseudo-Code:

BEGIN
  INSERT INTO table_partN values(x,x,x);
  RETURN null;
EXCEPTION
  WHEN undefined_table THEN
    CREATE TABLE table_partN ... INHERITS(table);
    CREATE INDEX table_partN_1 on table_partN ...;
    INSERT INTO table_partN values(x,x,x); 
    RETURN null;
END;

What I needed to do was trap the error when two or more threads all tried to create the table at the same time and only one succeeded.  I needed a nested exception trap.

New Function Pseudo-Code:

BEGIN
  BEGIN
    INSERT INTO table_partN values(x,x,x);
    RETURN null;
  EXCEPTION
    WHEN undefined_table THEN
      CREATE TABLE table_partN ... INHERITS(table);
      CREATE INDEX table_partN_1 on table_partN ...;
      INSERT INTO table_partN values(x,x,x); 
      RETURN null;
  END;
EXCEPTION
  WHEN duplicate_table THEN
  INSERT INTO table_partN values(x,x,x);
  RETURN null;
END;

Now if the race condition occurs then the winner will create the table and index and perform the insert, but the losers will get a “duplicate_table” exception and re-try the insert.

Any other SQL errors encountered will still propagate back out to the caller.

I’ve put together my own version of these functions and wrapped them in Bash scripts to make things easier. You can find them at my github site here.