2024-11-09 · 208 words · postgresql, psql, idempotency

In PostgreSQL certain commands are forbidden in transactions or functions, and lack IF NOT EXISTS variants. Examples include CREATE DATABASE and CREATE ROLE. How can one create a database idempotently?

The psql documentation says this about exit codes:

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g., out of memory, file not found), 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

Based on that description, I tried this:

$ psql postgresql://postgres@localhost:5432/postgres -c 'CREATE DATABASE fubar;' -v ON_ERROR_STOP=off

But this exits 1 if the database already exists!

After this surprise I tried numerous variations on the theme. These versions use -c command:

$ psql postgresql://postgres@localhost:5432/postgres -c "CREATE DATABASE fubar;" -v ON_ERROR_STOP=off; echo $?
ERROR:  database "fubar" already exists
1
$ psql postgresql://postgres@localhost:5432/postgres -A -t -c "CREATE DATABASE fubar;" -v ON_ERROR_STOP=off; echo $?
ERROR:  database "fubar" already exists
1
$ psql postgresql://postgres@localhost:5432/postgres -A -t -c "CREATE DATABASE fubar; select 'after';" -v ON_ERROR_STOP=off; echo $?
ERROR:  CREATE DATABASE cannot run inside a transaction block
1
$ psql postgresql://postgres@localhost:5432/postgres -A -t -c "CREATE DATABASE fubar;" -c "select 'after';" -v ON_ERROR_STOP=off; echo $?
ERROR:  database "fubar" already exists
after
0

These versions read commands from standard input:

$ echo "CREATE DATABASE fubar;" | psql postgresql://postgres@localhost:5432/postgres -A -t -v ON_ERROR_STOP=off; echo $?
ERROR:  database "fubar" already exists
0
$ echo "CREATE DATABASE fubar; select 'after';" | psql postgresql://postgres@localhost:5432/postgres -A -t -v ON_ERROR_STOP=off; echo $?
ERROR:  database "fubar" already exists
after
0
$ echo "CREATE DATABASE fubar;" | psql postgresql://postgres@localhost:5432/postgres -A -t -v ON_ERROR_STOP=on; echo $?
ERROR:  database "fubar" already exists
3
$ echo "CREATE DATABASE fubar; select 'after';" | psql postgresql://postgres@localhost:5432/postgres -A -t -v ON_ERROR_STOP=on; echo $?
ERROR:  database "fubar" already exists
3

These versions read commands from a file that contains only CREATE DATABASE fubar;:

$ psql postgresql://postgres@localhost:5432/postgres -A -t -f test0.sql -v ON_ERROR_STOP=off; echo $?
psql:test0.sql:1: ERROR:  database "fubar" already exists
0
$ psql postgresql://postgres@localhost:5432/postgres -A -t -f test0.sql -v ON_ERROR_STOP=on; echo $?
psql:test0.sql:1: ERROR:  database "fubar" already exists
3
$ psql postgresql://postgres@localhost:5432/postgres -A -t -f test1.sql -v ON_ERROR_STOP=off; echo $?
psql:test1.sql:1: ERROR:  database "fubar" already exists
after in file
0
$ psql postgresql://postgres@localhost:5432/postgres -A -t -f test1.sql -v ON_ERROR_STOP=on; echo $?
psql:test1.sql:1: ERROR:  database "fubar" already exists
3

The presence of additional commands in the file appears to make no difference.

In case it makes any difference, my database connection runs through a pgbouncer instance. I believe this has no impact on psql exit codes.

There are ways to accomplish the goal. I imagine that the under-the-hood logic is clearer. From the outside, this degree of inconsistency seems impressive.