Thread: createdb: ... duplicate key value violates unique constraint "pg_database_datname_index"

Hi all,

On a new x86_64 windows 7 SMP, a new database server was being set up by
some test automation.  We are using official 8.4.2 binaries.

It runs:
initdb -D c:\... --no-locale

... eventually this outputs the "Success. You can now start the database
server using ..." message.  Once that exits (with 0) it proceeds to
start postgres:

postgres -D c:\...

Then we wait for the .pid file to show up.

Once that happens we do createdb:
createdb -h 127.0.0.1 -p 1234 abc
createdb: database creation failed: ERROR: duplicate key value violates
unique constraint "pg_database_datname_index"

This is not reproducible and only happens occasionally.  We do sometimes
get the "database is starting up" error (and we just retry if that
happens).  For technical reasons I no longer remember, we found it
problematic to use pg_ctl -w on windows, and I do not remember whether
it always waited long enough anyway.

Is there a proper way to wait for postgres to start up other than
waiting until the pid file exists and retrying things whenever you get
the "database is starting up" error?  Is the error I am describing
indicative of something else?

Once, we got this slightly different error when creating the second
table in the freshly created database:

duplicate key value violates unique constraint "pg_type_typname_nsp_index"

It had some primitive columns and a foreign key reference to the first
table (which only had primitive columns).

We run this test automation quite a few platforms and have never
encountered this problem on any of them:
- All flavors of windows NT from win2k onwards, including an older win7 box
- linux: x86 + x86_64
- macos: x86_64
- solaris: sparc + x86 + x86_64

- Dave

Hi again,

I'm going to answer my own question.  I think there is a race where two
database servers are being setup on the same port at the same time and
two different tests end up trying to create the same DB on the same
server at the same time.

Let me know if you think I'm wrong :).

Dave Vitek wrote:
> Hi all,
>
> On a new x86_64 windows 7 SMP, a new database server was being set up
> by some test automation.  We are using official 8.4.2 binaries.
>
> It runs:
> initdb -D c:\... --no-locale
>
> ... eventually this outputs the "Success. You can now start the
> database server using ..." message.  Once that exits (with 0) it
> proceeds to start postgres:
>
> postgres -D c:\...
>
> Then we wait for the .pid file to show up.
>
> Once that happens we do createdb:
> createdb -h 127.0.0.1 -p 1234 abc
> createdb: database creation failed: ERROR: duplicate key value
> violates unique constraint "pg_database_datname_index"
>
> This is not reproducible and only happens occasionally.  We do
> sometimes get the "database is starting up" error (and we just retry
> if that happens).  For technical reasons I no longer remember, we
> found it problematic to use pg_ctl -w on windows, and I do not
> remember whether it always waited long enough anyway.
>
> Is there a proper way to wait for postgres to start up other than
> waiting until the pid file exists and retrying things whenever you get
> the "database is starting up" error?  Is the error I am describing
> indicative of something else?
>
> Once, we got this slightly different error when creating the second
> table in the freshly created database:
>
> duplicate key value violates unique constraint
> "pg_type_typname_nsp_index"
>
> It had some primitive columns and a foreign key reference to the first
> table (which only had primitive columns).
>
> We run this test automation quite a few platforms and have never
> encountered this problem on any of them:
> - All flavors of windows NT from win2k onwards, including an older
> win7 box
> - linux: x86 + x86_64
> - macos: x86_64
> - solaris: sparc + x86 + x86_64
>
> - Dave
>
>