Content-Type: multipart/mixed; boundary="------------TTrVIgfWV1kfX1fjcuy0T0CH"
Message-ID: <0b3bf24c-cc44-b25e-244e-cd740a8f8a67@postgrespro.ru>
Date: Fri, 24 Jun 2022 12:21:45 +0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.1
Subject: Re: Errors on CREATE TABLE IF NOT EXISTS
Content-Language: en-US
To: pgsql-bugs@postgresql.org, Robert Haas <rhaas@postgresql.org>
References: <4F9541D3.1000202@beccati.com>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <4F9541D3.1000202@beccati.com>
Archived-At: 
 <https://www.postgresql.org/message-id/0b3bf24c-cc44-b25e-244e-cd740a8f8a67%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------TTrVIgfWV1kfX1fjcuy0T0CH
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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
--------------TTrVIgfWV1kfX1fjcuy0T0CH
Content-Type: text/x-patch; charset=UTF-8; name="poc.diff"
Content-Disposition: attachment; filename="poc.diff"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3Rjb3AvdXRpbGl0eS5jIGIvc3JjL2JhY2tlbmQv
dGNvcC91dGlsaXR5LmMKaW5kZXggNmE1YmNkZWQ1NS4uMGQ0MTdhYzdmZCAxMDA2NDQKLS0t
IGEvc3JjL2JhY2tlbmQvdGNvcC91dGlsaXR5LmMKKysrIGIvc3JjL2JhY2tlbmQvdGNvcC91
dGlsaXR5LmMKQEAgLTU5LDEyICs1OSwxNCBAQAogI2luY2x1ZGUgImNvbW1hbmRzL3VzZXIu
aCIKICNpbmNsdWRlICJjb21tYW5kcy92YWN1dW0uaCIKICNpbmNsdWRlICJjb21tYW5kcy92
aWV3LmgiCisjaW5jbHVkZSAiY29tbW9uL2hhc2hmbi5oIgogI2luY2x1ZGUgIm1pc2NhZG1p
bi5oIgogI2luY2x1ZGUgInBhcnNlci9wYXJzZV91dGlsY21kLmgiCiAjaW5jbHVkZSAicG9z
dG1hc3Rlci9iZ3dyaXRlci5oIgogI2luY2x1ZGUgInJld3JpdGUvcmV3cml0ZURlZmluZS5o
IgogI2luY2x1ZGUgInJld3JpdGUvcmV3cml0ZVJlbW92ZS5oIgogI2luY2x1ZGUgInN0b3Jh
Z2UvZmQuaCIKKyNpbmNsdWRlICJzdG9yYWdlL2xtZ3IuaCIKICNpbmNsdWRlICJ0Y29wL3Bx
dWVyeS5oIgogI2luY2x1ZGUgInRjb3AvdXRpbGl0eS5oIgogI2luY2x1ZGUgInV0aWxzL2Fj
bC5oIgpAQCAtMTE0MiwxMCArMTE0NCwxOSBAQCBQcm9jZXNzVXRpbGl0eVNsb3coUGFyc2VT
dGF0ZSAqcHN0YXRlLAogCQkJCXsKIAkJCQkJTGlzdAkgICAqc3RtdHM7CiAJCQkJCVJhbmdl
VmFyICAgKnRhYmxlX3J2ID0gTlVMTDsKKwkJCQkJQ3JlYXRlU3RtdCAqc3RtdCA9IChDcmVh
dGVTdG10ICopIHBhcnNldHJlZTsKKwkJCQkJdWludDMyCQl0YWc7CisKKwkJCQkJaWYgKHN0
bXQtPmlmX25vdF9leGlzdHMpCisJCQkJCXsKKwkJCQkJCXRhZyA9IHRhZ19oYXNoKHN0bXQt
PnJlbGF0aW9uLT5yZWxuYW1lLAorCQkJCQkJCQkJICAgc3RybGVuKHN0bXQtPnJlbGF0aW9u
LT5yZWxuYW1lKSk7CisJCQkJCQlMb2NrRGF0YWJhc2VPYmplY3QoUmVsYXRpb25SZWxhdGlv
bklkLCB0YWcsIDAsCisJCQkJCQkJCQkJICAgRXhjbHVzaXZlTG9jayk7CisJCQkJCX0KIAog
CQkJCQkvKiBSdW4gcGFyc2UgYW5hbHlzaXMgLi4uICovCi0JCQkJCXN0bXRzID0gdHJhbnNm
b3JtQ3JlYXRlU3RtdCgoQ3JlYXRlU3RtdCAqKSBwYXJzZXRyZWUsCi0JCQkJCQkJCQkJCQlx
dWVyeVN0cmluZyk7CisJCQkJCXN0bXRzID0gdHJhbnNmb3JtQ3JlYXRlU3RtdChzdG10LCBx
dWVyeVN0cmluZyk7CiAKIAkJCQkJLyoKIAkJCQkJICogLi4uIGFuZCBkbyBpdC4gIFdlIGNh
bid0IHVzZSBmb3JlYWNoKCkgYmVjYXVzZSB3ZSBtYXkKQEAgLTEyNjQsNiArMTI3NSwxMCBA
QCBQcm9jZXNzVXRpbGl0eVNsb3coUGFyc2VTdGF0ZSAqcHN0YXRlLAogCQkJCQkJCUNvbW1h
bmRDb3VudGVySW5jcmVtZW50KCk7CiAJCQkJCX0KIAorCQkJCQlpZiAoc3RtdC0+aWZfbm90
X2V4aXN0cykKKwkJCQkJCVVubG9ja0RhdGFiYXNlT2JqZWN0KFJlbGF0aW9uUmVsYXRpb25J
ZCwgdGFnLCAwLAorCQkJCQkJCQkJCQkgRXhjbHVzaXZlTG9jayk7CisKIAkJCQkJLyoKIAkJ
CQkJICogVGhlIG11bHRpcGxlIGNvbW1hbmRzIGdlbmVyYXRlZCBoZXJlIGFyZSBzdGFzaGVk
CiAJCQkJCSAqIGluZGl2aWR1YWxseSwgc28gZGlzYWJsZSBjb2xsZWN0aW9uIGJlbG93Lgo=

--------------TTrVIgfWV1kfX1fjcuy0T0CH
Content-Type: application/sql; name="6727v.sql"
Content-Disposition: attachment; filename="6727v.sql"
Content-Transfer-Encoding: base64

DQpDUkVBVEUgT1IgUkVQTEFDRSBGVU5DVElPTiBnZW5lcmF0ZV90YWJsZXMobiBpbnQpIFJF
VFVSTlMgdm9pZCBMQU5HVUFHRSBwbHBnc3FsDQpBUyAkYm9keSQNCkRFQ0xBUkUNCiAgICBp
IGludDsNCiAgICBxcnkgdmFyY2hhcjsNCkJFR0lODQoJRk9SIGkgSU4gMS4ubiBMT09QDQoJ
ICAgIHFyeSA9ICdDUkVBVEUgVEFCTEUgSUYgTk9UIEVYSVNUUyB0ZXN0X3RhYmxlX3dpdGhf
bGVuXzYzX2FhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYWFhYXgnIHx8IHRvX2No
YXIoaSwgJ0ZNMDAwJykgfHwgJyAoaSBpbnQpJzsNCgkJUkFJU0UgTk9USUNFICdFeGVjdXRp
bmc6ICUnLCBxcnk7DQoJCUVYRUNVVEUgcXJ5Ow0KCUVORCBMT09QOw0KRU5EOw0KJGJvZHkk
Ow0KDQpTRUxFQ1QgZ2VuZXJhdGVfdGFibGVzKDYzKTsNCg0K

--------------TTrVIgfWV1kfX1fjcuy0T0CH--