Re: psql metaqueries with \gexec - Mailing list pgsql-hackers

From Corey Huinker
Subject Re: psql metaqueries with \gexec
Date
Msg-id CADkLM=eNFpZoMtFxavbcGtzbTZsbGkYCDpXWBtJUEe4KuWf78w@mail.gmail.com
Whole thread Raw
In response to Re: psql metaqueries with \gexec  (Corey Huinker <corey.huinker@gmail.com>)
Responses Re: psql metaqueries with \gexec
List pgsql-hackers
On Mon, Feb 22, 2016 at 11:30 AM, Corey Huinker <corey.huinker@gmail.com> wrote:
On Mon, Feb 22, 2016 at 10:08 AM, Daniel Verite <daniel@manitou-mail.org> wrote:
        Corey Huinker wrote:

> ...and query text visibility, and result visibility, and error handling,
> etc. In this case, we're leveraging the psql environment we'd already set
> up, and if there's an error, \set ECHO queries shows us the errant SQL as
> if we typed it ourselves..

BTW, about error handling, shouldn't it honor ON_ERROR_STOP ?

With the patch when trying this:

=> set ON_ERROR_STOP on
=> select * from (values ('select 1/0', 'select 1/0')) AS n \gexec

it produces two errors:
ERROR:  division by zero
ERROR:  division by zero

I'd rather have the execution stop immediately after the first error,
like it's the case with successive queries entered normally via the
query buffer:

=> \set ON_ERROR_STOP on
=> select 1/0; select 1/0;
ERROR:  division by zero

as opposed to:

=> \set ON_ERROR_STOP off
=> select 1/0; select 1/0;
ERROR:  division by zero
ERROR:  division by zero


Yes, I would like it to honor ON_ERROR_STOP. I'll look into that.


Well, that was easy enough. Turns out that pset.on_error_stop is checked in MainLoop, whereas the other pset.on_* vars are checked in SendQuery().

My original idea had been to push each cell into a in-memory temp file handle and call MainLoop() on each. Pavel suggested that temp files of any sort were a bad idea, hence using SendQuery instead. It's probably for the best.


# select 'select 1,2,3', 'select 1/0', 'select 4,5,6'
... # \gexec
 ?column? | ?column? | ?column?
----------+----------+----------
        1 |        2 |        3
(1 row)

Time: 0.151 ms
ERROR:  22012: division by zero
LOCATION:  int4div, int.c:719
Time: 0.528 ms
 ?column? | ?column? | ?column?
----------+----------+----------
        4 |        5 |        6
(1 row)

Time: 0.139 ms
Time: 0.595 ms
# \set ON_ERROR_STOP 1
# select 'select 1,2,3', 'select 1/0', 'select 4,5,6' \gexec
 ?column? | ?column? | ?column?
----------+----------+----------
        1 |        2 |        3
(1 row)

Time: 0.137 ms
ERROR:  22012: division by zero
LOCATION:  int4div, int.c:719
Time: 0.165 ms
Time: 0.284 ms

Does \set ON_ERROR_STOP mess up regression tests? If not, I'll add the test above (minus the \set VERBOSITY verbose-isms) to the regression.

In the mean time, update patch attached.


pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: psql metaqueries with \gexec
Next
From: Corey Huinker
Date:
Subject: Re: psql metaqueries with \gexec