From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: pgsql-bugs@postgresql.org, Robert Haas <rhaas@postgresql.org>
Subject: Re: Errors on CREATE TABLE IF NOT EXISTS
Date: Fri, 24 Jun 2022 12:21:45 +0500
Message-ID: <0b3bf24c-cc44-b25e-244e-cd740a8f8a67@postgrespro.ru> (raw)
In-Reply-To: <4F9541D3.1000202@beccati.com>
References: <4F9541D3.1000202@beccati.com>
On 4/23/12 17:49, Matteo Beccati wrote:
> On Mon, Apr 23, 2012 at 7:49 AM, Matteo Beccati <php@beccati.com> wrote:
>> I've tried to come up with a self-contained test case but I haven't been
>> able to replicate the error above. However the following script
>> performs =
> a
>> few concurrent CREATE TABLE IF NOT EXISTS statements that produce some
>> unexpected errors (using 9.1.2).
>> ERROR: =A0duplicate key value violates unique constraint
>> "pg_type_typname_nsp_index"
>
> This is normal behavior for CREATE TABLE either with or without IF NOT
> EXISTS. CREATE TABLE does a preliminary check to see whether a name
> conflict exists. If so, it either errors out (normally) or exits with
> a notice (in the IF NOT EXISTS case). But there's a race condition: a
> conflicting transaction can create the table after we make that check
> and before we create it ourselves. If this happens, then you get the
> failure you're seeing, because the btree index machinery catches the
> problem when we do the actual system catalog inserts.
>
> Now, this is not very user-friendly, but we have no API to allow
> inserting into a table with a "soft" error if uniqueness would be
> violated. Had we such an API we could handle a number of situations
> more gracefully, including this one. Since we don't, the only option
> is to let the btree machinery error out if it must.
>
> 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. You should use some other system to
> coordinate near-simultaneous creation of tables, such as perhaps doing
> pg_advisory_lock/CINE/pg_advisory_unlock.
>
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
Working on a problem with conflicts in type names [1] I played with the
script (6727v.sql) and pgbench with many clients/threads.
Using "IF NOT EXISTS" we can get many different ERROR messages because
of races in table creation, type creation, index insertion and so on. It
may be not a critical problem, but may be it can be solved by some
simplistic way? See poc.diff as a demo of a solution.
[1]
https://www.postgresql.org/message-id/b84cd82c-cc67-198a-8b1c-60f44e1259ad@postgrespro.ru
--
Regards
Andrey Lepikhov
Postgres Professional
Attachments:poc.diff (text/x-patch, 1.6K) downloaddiff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.cindex 6a5bcded55..0d417ac7fd 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -59,12 +59,14 @@
#include "commands/user.h"
#include "commands/vacuum.h"
#include "commands/view.h"
+#include "common/hashfn.h"
#include "miscadmin.h"
#include "parser/parse_utilcmd.h"
#include "postmaster/bgwriter.h"
#include "rewrite/rewriteDefine.h"
#include "rewrite/rewriteRemove.h"
#include "storage/fd.h"
+#include "storage/lmgr.h"
#include "tcop/pquery.h"
#include "tcop/utility.h"
#include "utils/acl.h"
@@ -1142,10 +1144,19 @@ ProcessUtilitySlow(ParseState *pstate,
{
List *stmts;
RangeVar *table_rv = NULL;
+ CreateStmt *stmt = (CreateStmt *) parsetree;+ uint32 tag;++ if (stmt->if_not_exists)+ {+ tag = tag_hash(stmt->relation->relname,+ strlen(stmt->relation->relname));+ LockDatabaseObject(RelationRelationId, tag, 0,+ ExclusiveLock);+ }
/* Run parse analysis ... */
- stmts = transformCreateStmt((CreateStmt *) parsetree,- queryString);+ stmts = transformCreateStmt(stmt, queryString);
/*
* ... and do it. We can't use foreach() because we may
@@ -1264,6 +1275,10 @@ ProcessUtilitySlow(ParseState *pstate,
CommandCounterIncrement();
}
+ if (stmt->if_not_exists)+ UnlockDatabaseObject(RelationRelationId, tag, 0,+ ExclusiveLock);+
/*
* The multiple commands generated here are stashed
* individually, so disable collection below.
6727v.sql (application/sql, 414B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: a.lepikhov@postgrespro.ru, rhaas@postgresql.org
Subject: Re: Errors on CREATE TABLE IF NOT EXISTS
In-Reply-To: <0b3bf24c-cc44-b25e-244e-cd740a8f8a67@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox