Thread: 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, 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

Hello Tom,

>>   - 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?

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.