I can code an exception block to handle the table problem, and probably one for the index collision too.
Creating partitions dynamically is pretty bad idea. You have to handle a exceptions - it enforces implicit subtransaction (some slowdown) or you have to use a explicit locks - or some mix of all.
Writing this code without race condition is not too easy.
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.
PostgreSQL is based on processes. The reason was described by David well.
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.
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.
AT1: Insert, failed, cannot find table
AT2: Insert, failed, cannot find table
BT2: Create Table, succeeds
BT1: Create Table; fails, it exists now, if exists converts to a warning
CT2: Create Index, succeeds
CT1: Create Index, fails , hard error
DT2: Insert, succeeds
DT1: Never Happens
What that post seems to be describing is that it is possible the "BT1" actually hard errors instead of just being converted into a notice. There is no statement visible action to show that interleave but there is an underlying race condition since both BT1 and BT2 are executing concurrently.
In short even with IF NOT EXISTS you are not guaranteed to not fail. But at least IF NOT EXISTS makes the probability of not failing > 0. It doesn't handle the concurrency any better - but it does change the outcome in some of those less-than-ideally handled situations.