Thread: Errors on CREATE TABLE IF NOT EXISTS
Hi, I've recently seen a few errors on our continuous integration system during a test using a badly written partitioning trigger. The function was basically checking for the existence of the partition table at every insert and was running a CREATE TABLE IF NOT EXISTS statement in case it was needed. What baffled me was that the function was exiting with an ERROR, rather than succeeding with a NOTICE, e.g.: ERROR: relation "orders_2012_03" already exists CONTEXT: SQL statement "CREATE UNLOGGED TABLE IF NOT EXISTS history.orders_2012_03(CHECK (store_t_stamp >= '2012-03-01 00:00:00' AND store_t_stamp < '2012-04-01 00:00:00')) INHERITS (history.orders)" Since then I've made the partitioning functions a bit smarter and I'm also catching the exception just in case. 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). postgres@spritz:~$ cat crtest.sh #!/bin/sh for i in `seq 1 10`; do psql -c 'CREATE TABLE IF NOT EXISTS _foo (x int PRIMARY KEY)' 2>&1 & done sleep 2 psql -c 'DROP TABLE _foo' postgres@spritz:~$ ./crtest.sh NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_foo_pkey" for table "_foo" CREATE TABLE ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. DROP TABLE I'm not sure if the two failures are related in some way, but I thought it was good to report them both anyway. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/
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. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi Robert, On 27/04/2012 20:24, Robert Haas wrote: > 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. Thanks for the thorough reply. I guessed it was something like that, but I thought it was better to report that anyway, just in case. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/
Excerpts from Matteo Beccati's message of lun abr 23 08:49:39 -0300 2012: > Hi, >=20 > I've recently seen a few errors on our continuous integration system=20 > during a test using a badly written partitioning trigger. The function=20 > was basically checking for the existence of the partition table at every= =20 > insert and was running a CREATE TABLE IF NOT EXISTS statement in case it= =20 > was needed. What baffled me was that the function was exiting with an=20 > ERROR, rather than succeeding with a NOTICE, e.g.: The question you were asking has already been answered, but I think it's worth pointing out that a partitioned-insert trigger that has to check whether the partition exist beforehand is a lot slower than one that doesn't have to. Our usual suggestion is to create the partitions by some other means, e.g. create a couple months worth of weekly partitions, a couple of months ahead of time, via cron. The insert trigger is then assured that the partition exists, and it can become faster by not having to check. --=20 =C3=81lvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hi Alvaro, On 01/05/2012 21:36, Alvaro Herrera wrote: > The question you were asking has already been answered, but I think it's > worth pointing out that a partitioned-insert trigger that has to check > whether the partition exist beforehand is a lot slower than one that > doesn't have to. Our usual suggestion is to create the partitions by > some other means, e.g. create a couple months worth of weekly > partitions, a couple of months ahead of time, via cron. The insert > trigger is then assured that the partition exists, and it can become > faster by not having to check. Thanks for the suggestion. I will surely follow the advice as soon as the load starts to grow. For now catching the "table not found" exception within the insert trigger and creating the table on the fly seems a good balance between performance and ease of use. Cheers -- Matteo Beccati Development & Consulting - http://www.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