2017-05-18 22:39 GMT+02:00 Rob Brucks <rob.bru...@rackspace.com>: > Thanks. > > > > 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. > > > > Thanks, > > Rob > > > > *From: *"David G. Johnston" <david.g.johns...@gmail.com> > *Date: *Thursday, May 18, 2017 at 3:31 PM > *To: *Rob Brucks <rob.bru...@rackspace.com> > *Cc: *"pgsql-general@postgresql.org" <pgsql-general@postgresql.org> > *Subject: *Re: [GENERAL] Error that shouldn't happen? > > > > On Thu, May 18, 2017 at 1:18 PM, Rob Brucks <rob.bru...@rackspace.com> > wrote: > > 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+TgmoZAdYVtwBfp1FL2sMZbiHCWT4UP > rzrlnnx1nb30ku3...@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. > > > > > > 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. > > > > David J. > > >
