BUG #9956: Concurrency bug in CREATE TABLE IF NOT EXISTS statement - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      9956
Logged by:          Christian Schlichtherle
Email address:      christian@schlichtherle.de
PostgreSQL version: 9.3.4
Operating system:   Mac OS X 10.9.2
Description:

Apparently, the CREATE TABLE IF NOT EXISTS statement is not properly
isolated. The following test exposes the issue using JDBC:

<pre><code>
package com.company.product.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
import org.junit.Test;

/** @author Christian Schlichtherle */
public class CreateTableIfNotExistsTest {

    private static final String CONNECTION_STRING =
"jdbc:postgresql:postgres";
    private static final int NUM_THREADS =
Runtime.getRuntime().availableProcessors() * 2;

    @Test
    public void createTableIfNotExists() throws InterruptedException,
ExecutionException {
        final ExecutorService executor =
Executors.newFixedThreadPool(NUM_THREADS);
        try {
            final CountDownLatch startSignal = new
CountDownLatch(NUM_THREADS);
            final List<Future<Void>> tasks = new ArrayList<>(NUM_THREADS);
            for (int i = 0; i < NUM_THREADS; i++) {
                tasks.add(executor.submit(new Callable<Void>() {
                    @Override
                    public Void call() throws InterruptedException,
SQLException {
                        try (Connection c =
DriverManager.getConnection(CONNECTION_STRING)) {
                            assert c.getAutoCommit();
                            new Object() {
                                void run() throws InterruptedException,
SQLException {
                                    startSignal.countDown();
                                    startSignal.await();
                                    executeUpdate("CREATE TABLE IF NOT
EXISTS foo(a INT)");
                                    // If you comment out this statement,
then
                                    // the test will fail on the first
execution,
                                    // but not on any subsequent execution.
                                    executeUpdate("DROP TABLE foo");
                                }

                                int executeUpdate(String sql) throws
SQLException {
                                    try (Statement s = c.createStatement())
{
                                        return s.executeUpdate(sql);
                                    }
                                }
                            }.run();
                        }
                        return null;
                    }
                }));
            }
            for (Future<Void> task : tasks) task.get();
        } finally {
            executor.shutdown();
            executor.awaitTermination(10, TimeUnit.SECONDS);
        }
    }
}
</code></pre>

The result of running this test is always the following exception stack
trace.

<pre><code>
java.util.concurrent.ExecutionException: org.postgresql.util.PSQLException:
ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
  Detail: Key (typname, typnamespace)=(foo, 2200) already exists.
    at java.util.concurrent.FutureTask.report(FutureTask.java:122)
    at java.util.concurrent.FutureTask.get(FutureTask.java:188)
    at
com.company.product.demo.CreateTableIfNotExistsTest.createTableIfNotExists(CreateTableIfNotExistsTest.java:58)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at
org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:252)
    at
org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:141)
    at
org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:112)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at
org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:189)
    at
org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:165)
    at
org.apache.maven.surefire.booter.ProviderFactory.invokeProvider(ProviderFactory.java:85)
    at
org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:115)
    at
org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:75)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value
violates unique constraint "pg_type_typname_nsp_index"
  Detail: Key (typname, typnamespace)=(foo, 2200) already exists.
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)
    at
com.company.product.demo.CreateTableIfNotExistsTest$1$1.executeUpdate(CreateTableIfNotExistsTest.java:49)
    at
com.company.product.demo.CreateTableIfNotExistsTest$1$1.run(CreateTableIfNotExistsTest.java:40)
    at
com.company.product.demo.CreateTableIfNotExistsTest$1.call(CreateTableIfNotExistsTest.java:36)
    at
com.company.product.demo.CreateTableIfNotExistsTest$1.call(CreateTableIfNotExistsTest.java:31)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:744)
</code></pre>

Note that the DROP TABLE statement just exists to make the test failing
consistently.