How to *really* quit psql?
Hello devs,
I want to abort a psql script. How can I do that? The answer seems to be
\quit, but it is not so simple:
- when the current script is from a terminal, you exit psql, OK
- when the current script is from a file (-f, <), you exit psql, OK
- when the current script is included from something,
you quit the current script and proceed after the \i of next -f, BAD
Question: is there any way to really abort a psql script from an included
file?
I've found "\! kill $PPID" which works with bash, but I'm not sure of the
portability and I was hoping for something straightforward and cleaner.
If there is really no simple way, would it be okay to add a \exit which
does that?
--
Fabien.
Fabien COELHO <coelho@cri.ensmp.fr> writes:
- when the current script is included from something,
you quit the current script and proceed after the \i of next -f, BAD
Question: is there any way to really abort a psql script from an included
file?
Under what circumstances would it be appropriate for a script to take
it on itself to decide that? It has no way of knowing what the next -f
option is or what the user intended.
regards, tom lane
On Sat, Nov 19, 2022 at 12:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Fabien COELHO <coelho@cri.ensmp.fr> writes:
- when the current script is included from something,
you quit the current script and proceed after the \i of next -f, BADQuestion: is there any way to really abort a psql script from an
included
file?
Under what circumstances would it be appropriate for a script to take
it on itself to decide that? It has no way of knowing what the next -f
option is or what the user intended.
Can we add an exit code argument to the \quit meta-command that could be
set to non-zero and, combined with ON_ERROR_STOP, produces the desired
effect of aborting everything just like an error under ON_ERROR_STOP does
(which is the workaround here I suppose, but an ugly one that involves the
server).
David J.
On Sat, 19 Nov 2022 at 14:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Under what circumstances would it be appropriate for a script to take
it on itself to decide that? It has no way of knowing what the next -f
option is or what the user intended.
Presumably when they're written by the same person so the script does
effectively know what the "user" intended because it's written by the
same user.
Off the top of my head I could imagine someone writing something like
report-error-and-exit.sql and wanting to be able to use \i
report-error-and-exit.sql to ensure all scripts report their errors
using some common log file or something.
Not saying that's the only or best way to do that though. And there is
the risk that scripts would start using this functionality
inappropriately which would mean, for example, getting an install
script for something and then not being able to use it within another
script safely :(
--
greg
Greg Stark <stark@mit.edu> writes:
On Sat, 19 Nov 2022 at 14:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Under what circumstances would it be appropriate for a script to take
it on itself to decide that? It has no way of knowing what the next -f
option is or what the user intended.
Presumably when they're written by the same person so the script does
effectively know what the "user" intended because it's written by the
same user.
Even so, embedding that knowledge in the first script doesn't seem
like the sort of design we ought to encourage. It'd be better if
"don't run the next script if the first one fails" were directed
by a command-line switch or the like. I also wonder exactly how
this interacts with existing features like ON_ERROR_STOP.
regards, tom lane
On Sat, Nov 19, 2022 at 12:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Greg Stark <stark@mit.edu> writes:
On Sat, 19 Nov 2022 at 14:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Under what circumstances would it be appropriate for a script to take
it on itself to decide that? It has no way of knowing what the next -f
option is or what the user intended.Presumably when they're written by the same person so the script does
effectively know what the "user" intended because it's written by the
same user.Even so, embedding that knowledge in the first script doesn't seem
like the sort of design we ought to encourage. It'd be better if
"don't run the next script if the first one fails" were directed
by a command-line switch or the like. I also wonder exactly how
this interacts with existing features like ON_ERROR_STOP.
vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -v ON_ERROR_STOP=1 -f two.psql
-f three.psql postgres
psql:two.psql:1: ERROR: division by zero
vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -f two.psql -f three.psql
postgres
psql:two.psql:1: ERROR: division by zero
?column?
----------
2
(1 row)
?column?
----------
3
(1 row)
David J.
On Sat, Nov 19, 2022 at 12:59 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Sat, Nov 19, 2022 at 12:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Greg Stark <stark@mit.edu> writes:
On Sat, 19 Nov 2022 at 14:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Under what circumstances would it be appropriate for a script to take
it on itself to decide that? It has no way of knowing what the next -f
option is or what the user intended.Presumably when they're written by the same person so the script does
effectively know what the "user" intended because it's written by the
same user.Even so, embedding that knowledge in the first script doesn't seem
like the sort of design we ought to encourage. It'd be better if
"don't run the next script if the first one fails" were directed
by a command-line switch or the like. I also wonder exactly how
this interacts with existing features like ON_ERROR_STOP.vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -v ON_ERROR_STOP=1 -f
two.psql -f three.psql postgres
psql:two.psql:1: ERROR: division by zero
vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -f two.psql -f three.psql
postgres
psql:two.psql:1: ERROR: division by zero
?column?
----------
2
(1 row)?column?
----------
3
(1 row)
Sorry, forgot the \quit test:
vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -v ON_ERROR_STOP=1 -f two.psql
-f three.psql postgres
?column?
----------
2
(1 row)
?column?
----------
3
(1 row)
(there is a \quit at the end of two.psql)
David J.
Hello Tom,
- when the current script is included from something,
you quit the current script and proceed after the \i of next -f, BADQuestion: is there any way to really abort a psql script from an included
file?Under what circumstances would it be appropriate for a script to take
it on itself to decide that?
The use case is psql scripts which update or cleanup an application
schema. For security, some of these scripts check for conditions (eg, we
are not in production, the application schema is in the expected version,
whatever…) and should abort if the conditions are not okay. As checking
for the conditions requires a few lines of code and is always the same, a
simple approach is to include another script which does the check and
aborts the run if necessary, eg:
```sql
-- this script should not run in "prod"!
\ir not_in_prod.sql
-- should have aborted if it is a "prod" version.
DROP TABLE AllMyUsers CASCADE;
DROP TABLE QuiteImportantData CASCADE;
```
It has no way of knowing what the next -f option is or what the user
intended.
The intention of the user who wrote the script is to abort in some cases,
to avoid damaging the database contents.
--
Fabien.
Hello David,
vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -v ON_ERROR_STOP=1 -f two.psql
-f three.psql postgres
?column?
----------
2
(1 row)?column?
----------
3
(1 row)(there is a \quit at the end of two.psql)
Yep, that summarizes my issues!
ON_ERROR_STOP is only of SQL errors, so a script can really stop by having
an intentional SQL error.
--
Fabien.
Hello David,
Question: is there any way to really abort a psql script from an
included file?Under what circumstances would it be appropriate for a script to take
it on itself to decide that? It has no way of knowing what the next -f
option is or what the user intended.Can we add an exit code argument to the \quit meta-command that could be
set to non-zero and, combined with ON_ERROR_STOP, produces the desired
effect of aborting everything just like an error under ON_ERROR_STOP does
(which is the workaround here I suppose, but an ugly one that involves the
server).
I like the simple idea of adding an optional exit status argument to
\quit. I'm unsure whether "ON_ERROR_STOP" should or should not change the
behavior, or whether it should just exit(n) with \quit n.
Note that using quit to abort a psql script is already used when loading
extensions to prevent them to be run directly by psql:
-- from some sql files in "contrib/pg_stat_statements/":
\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.10'" to load this file. \quit
But the same trick would fail if the guard is reach with an include.
--
Fabien.