Re: interrupted createdb leaves dirty system tables

Reece Hart <reece(at)berkeley(dot)edu> writes:
> Josh Berkus asked that I send the following observation to bugs:
> I executed and quickly interrupted (^C) createdb that was connected to a
> remote cluster via TCP/IP. The interruption left system tables in a
> dirty state such that a subsequent invocation warned of a uniqueness
> violation in pg_database_datname_index. The transcript follows:

> unison$ sudo -u postgres createdb reece
> ^C
> unison$ sudo -u postgres createdb -O reece reece
> createdb: database creation failed: ERROR: duplicate key value violates
> unique constraint "pg_database_datname_index"
> unison$ sudo -u postgres createdb -O reece reece
> createdb: database creation failed: ERROR: database "reece" already exists

How long does createdb normally take on that machine?

I believe what actually happened here was:

1. Your control-C killed the createdb client program, but the CREATE
DATABASE command continued to run on the server.

2. While it was still running, you tried again. That got the duplicate
key violation.

3. After it completed, you tried a third time, and that got the expected
"database already exists" message.

The dup-key violation is expected when two CREATE DATABASE commands for
the same DB name are issued concurrently. As the code remarks:

/*
* Check for db name conflict. This is just to give a more friendly error
* message than "unique index violation". There's a race condition but
* we're willing to accept the less friendly message in that case.
*/
if (OidIsValid(get_database_oid(dbname)))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_DATABASE),
errmsg("database \"%s\" already exists", dbname)));

That is, your second try got past this error check because the
conflicting pg_database entry wasn't committed yet. When it got to the
point of making its own pg_database entry, the unique-index mechanism
blocked on the concurrent insertion and reported an error after that
committed.

This doesn't happen just for databases --- there's a similar coding
pattern for nearly every SQL object type. It's a little easier to run
into for databases because the CREATE operation is so slow.

> This bug is likely to be very sensitive to timing and I didn't bother
> trying to recreate it.

No, it's very easy to reproduce, if your machine is slow enough that
CREATE DATABASE takes a few seconds.

regards, tom lane