Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kMGhU-0006Tz-KE for pgsql-bugs@arkaria.postgresql.org; Sat, 26 Sep 2020 20:21:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kMGhQ-0000jD-Rg for pgsql-bugs@arkaria.postgresql.org; Sat, 26 Sep 2020 20:21:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kMGhQ-0000ic-Ld for pgsql-bugs@lists.postgresql.org; Sat, 26 Sep 2020 20:21:40 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kMGhN-0007eM-Rm for pgsql-bugs@lists.postgresql.org; Sat, 26 Sep 2020 20:21:40 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 08QKLYbn062164; Sat, 26 Sep 2020 16:21:34 -0400 From: Tom Lane To: kulluadhikari@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16637: Postgressql throws exception 'relation already exists' when trying to execute partition from code In-reply-to: <16637-4374b25aac0e72a4@postgresql.org> References: <16637-4374b25aac0e72a4@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Sat, 26 Sep 2020 15:19:47 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <62162.1601151694.1@sss.pgh.pa.us> Date: Sat, 26 Sep 2020 16:21:34 -0400 Message-ID: <62163.1601151694@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > I noticed that sometimes the npgsql provider is throwing exceptions > "relation already exists" when i try to create a partition with parallel > threads in functioning. > Ex: > printed sql that resulted in exception on console : > CREATE TABLE IF NOT EXISTS Messages_2020_d_268_cid_45 PARTITION OF > "public".Messages_2020_d_268 FOR VALUES in (45) IF NOT EXISTS is not intended to provide a hard guarantee against concurrent-creation race conditions. It just looks to see if there's such a table right now, and if not it proceeds with creation. So if two sessions do the same thing at more or less the same instant, they will both conclude the table's not there yet. It's possible to imagine adding new forms of locking to prevent that, but it would be a lot of overhead that would be quite wasted for most usage. So I doubt we'll ever do that. Note that if you are doing this inside a long-running transaction, you're more or less shooting yourself in the foot, because the new table will not become visible to other transactions until you commit. (Which is a feature, not a bug.) So it's advisable to commit the table creation as quickly as possible to reduce the length of the race-condition window. regards, tom lane