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
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thursday, May 18, 2017 at 3:05 PM
To: Rob Brucks <rob.brucks@rackspace.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Error that shouldn't happen?
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.
I don't fully comprehend the locking involved here but if you want a failure while creating the table you shouldn't use "IF NOT EXISTS". On the other side adding "IF NOT EXISTS" to the CREATE INDEX will supposedly prevent the error you are seeing.
The trigger that failed to create the index also failed to create the table - it just didn't care because of the IF NOT EXISTS. At least this is what I am observing from your description.
