When it happens
- The script writes meta-commands with
echo inside double quotes, the usual way to escape the backslash:
{
echo "SELECT NOT EXISTS (SELECT 1 FROM schema_migrations WHERE filename = '$f') AS todo \\gset"
echo "\\if :todo"
echo "\\echo 'migrate: applying $f'"
echo "\\i $DIR/$f"
echo "\\endif"
} > "$SCRIPT"
psql "$DATABASE_URL" -X -q -t -A -v ON_ERROR_STOP=1 -f "$SCRIPT"
- It is run by dash. That is
/bin/sh on Debian and Ubuntu, including the pgvector/pgvector:pg17 image and GitHub's ubuntu-latest runners, so sh script.sh there means dash. The #! line doesn't matter when the script is started as sh script.sh. - Under bash and under BusyBox
sh (Alpine images) the same script writes what you meant. In the incident this unit comes from, the script ran as an init container on an Alpine-based image without trouble, and failed only when a CI job ran it with sh on an Ubuntu runner.
Which branch of \if was taken decides the error. If the condition is true, psql runs the line ESC cho … as SQL (the syntax error above). If it is false, the broken \endif is never seen as one, so the block never closes (the EOF error).
Cause
"\\endif" in double quotes becomes the string \endif in every shell. The difference is in echo. dash's built-in echo always interprets backslash escapes, bash's doesn't unless asked (-e or xpg_echo), and BusyBox's didn't here. What dash 0.5.12 (Ubuntu 24.04) made of echo "<meta-command> done", read with od -c:
| written | became |
|---|
\if, \i, \ir, \gset, \set, \pset, \o, \q, \x, \! | unchanged |
\echo, \endif, \else, \elif | ESC + the rest (cho, ndif, …) |
\timing, \t | TAB + the rest |
\f, \a | form feed, bell |
\copy, \connect | nothing at all: \c ends echo's output, newline included |
So the commands that break are exactly the ones whose second letter is an escape letter. A script that only uses \if, \i and \gset can pass for a long time, and break the day someone adds an \echo or an \endif. Single quotes don't help, because the escape is done by echo, not by the shell's quoting: echo '\endif' gave ESC + ndif as well.
Fix
Either run the script with bash explicitly (what the reference project did in its CI step):
- run: DATABASE_URL=... MIGRATIONS_DIR="$PWD/db/init" bash db/migrate.sh # not sh: dash's echo turns \endif into ESC + ndif
or, better, stop depending on echo and write those lines with printf '%s\n', which prints its arguments as they are in every shell:
printf '%s\n' "\\if :todo" "\\echo 'migrate: applying $f'" "\\i $DIR/$f" "\\endif"
Under dash, printf '%s\n' "\\endif" "\\echo 'hi'" wrote \endif and \echo 'hi' unchanged. Changing only the shebang to #!/bin/bash isn't enough when callers run sh script.sh.
Verify
sh gen.sh /tmp/out.sql && od -c /tmp/out.sql | grep -c 033 # 0 when nothing was mangled
Run the generator under the shell it really gets in every place it runs (CI runner, init container, developer laptop), and apply the result with ON_ERROR_STOP=1 to a scratch database. In the reproduction for this unit, the same generator run by dash failed under psql 17.11 (exit 3) and run by bash applied cleanly (exit 0), in the pgvector/pgvector:pg17 image.
Notes
\c is the dangerous one: echo "\\copy t FROM 'x.csv'" under dash writes nothing, not even a newline, so the next line is glued to whatever came before and there may be no error at all.- The same applies to any generated file that contains backslashes: regular expressions, Windows paths,
printf formats passed through echo. - If you can, keep meta-commands in a static
.sql file and pass the varying parts with psql -v name=value instead of generating the script.