BUG #9956: Concurrency bug in CREATE TABLE IF NOT EXISTS statement

The following bug has been logged on the website:

Bug reference: 9956
Logged by: Christian Schlichtherle
Email address: christian(at)schlichtherle(dot)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.