Re: \if, \elseif, \else, \endif (was Re: [HACKERS] PSQL commands: \quit_if, \quit_unless)

[Corey Huinker]

>
>
> As I understood it, the negative feeback was really about sh inspiration
> "if/fi", not about elif/elsif/elseif. I do not think that there was an
> expressed consensus about the later.
>

True


> The closest case is CPP with its line-oriented #-prefix syntax, and I
> still think that we should do it like that.


Given that the pl/pgsql syntax has a space between "end" and "if", I have
to agree. It's easy enough to change back if others can make a convincing
argument for something else.

Re: \if, \elseif, \else, \endif (was Re: [HACKERS] PSQL commands: \quit_if, \quit_unless)

[Fabien COELHO]

1: unrecognized value "whatever" for "\if"; assuming "on"

I do not think that the script should continue with such an assumption.


I agree, and this means we can't use ParseVariableBool() as-is. I 
already broke out argument reading to it's own function, knowing that 
it'd be the stub for expressions. So I guess we start that now. What 
subset of true-ish values do you think we should support? If we think 
that real expressions are possible soon, we could only allow 'true' and 
'false' for now, but if we expect that expressions might not make it 
into v10, then perhaps we should support the same text values that 
coerce to booleans on the server side.


Hmmm. I would text value that coerce to true? I would also accept non-zero 
integers (eg SELECT 1::BOOL; -- t).


I would suggest to assume false on everything else, and/or maybe to ignore 
the whole if/endif section in such cases.



All valid issues. Will add those to the regression as well (with
ON_ERROR_STOP disabled, obviously).


ISTM that with TAP test you can check for error returns, so maybe this can 
be done.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: \if, \elseif, \else, \endif (was Re: [HACKERS] PSQL commands: \quit_if, \quit_unless)

[Fabien COELHO]

Hello,


I'm personally indifferent to elif vs elsif vs elseif, but I think elseif
was the consensus. It's easy enough to change.


Went with elsif to follow pl/pgsql. In reviewing the original thread it
seemed that "elif" was linked to "fi" and that got some negative feedback.


As I understood it, the negative feeback was really about sh inspiration 
"if/fi", not about elif/elsif/elseif. I do not think that there was an

expressed consensus about the later.

Else-if variants from different languages include:

 VB: If ElseIf Else End If (with optional Then)
 PHP: if {} elseif {} else {}
 Tcl: if {} elseif {} else {}

 PL/pgSQL: IF ... THEN ... ELSIF ... ELSE ... END IF;
 Perl: if {} elsif {} else {}
 Ruby: if elsif else end

 CPP: #if #elif #else #endif
 Python: if : elif: else:
 bash: if [] then ... elif ... else ... fi

The closest case is CPP with its line-oriented #-prefix syntax, and I 
still think that we should do it like that.



I went looking for other examples of explicit /* PASSTHROUGH */ comments
and could find none. Could you explain what it is you want me to fix with
the switch statements?


Sorry, I got it wrong. The comment (which is FALLTHROUGH or FALL THROUGH, 
not PASSTHROUGH) is required if there is specific code in a case and the 
case is expected to continue with executing the next case code as well. 
This is quite rare, and it is not the case for your code, which just has 
some comments in one case.


--
Fabien


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: \if, \elseif, \else, \endif (was Re: [HACKERS] PSQL commands: \quit_if, \quit_unless)

[Corey Huinker]

On Mon, Jan 23, 2017 at 4:12 PM, Corey Huinker 
wrote:

> I do not like the choice of "elseif", which exists in PHP & VB. PL/pgSQL
>> has "ELSIF" like perl, that I do not like much, though. Given the syntax
>> and behavioral proximity with cpp, I suggest that "elif" would be a better
>> choice.
>>
>
> I'm personally indifferent to elif vs elsif vs elseif, but I think elseif
> was the consensus. It's easy enough to change.
>

Went with elsif to follow pl/pgsql. In reviewing the original thread it
seemed that "elif" was linked to "fi" and that got some negative feedback.


>
>
>>
>> Documentation: I do not think that the systematic test-like example in
>> the documentation is relevant, a better example should be shown. The list
>> of what is considered true or false should be told explicitely, not end
>> with "etc" that is everyone to guess.
>>
>
> It was copied from the regression test. I knew there would be follow-up
> suggestions for what should be shown.
>

Work on this is pending discussion of what true/false values we should
allow, and if values outside of those is an error.


>>
>>   case ...: case ...:
>>
>> The project rules are to add explicit /* PASSTHROUGH */ comment.
>>
>
> Will do.
>

I went looking for other examples of explicit /* PASSTHROUGH */ comments
and could find none. Could you explain what it is you want me to fix with
the switch statements?

Re: \if, \elseif, \else, \endif (was Re: [HACKERS] PSQL commands: \quit_if, \quit_unless)

[Corey Huinker]

>
> I do not like the choice of "elseif", which exists in PHP & VB. PL/pgSQL
> has "ELSIF" like perl, that I do not like much, though. Given the syntax
> and behavioral proximity with cpp, I suggest that "elif" would be a better
> choice.
>

I'm personally indifferent to elif vs elsif vs elseif, but I think elseif
was the consensus. It's easy enough to change.


>
> Documentation: I do not think that the systematic test-like example in the
> documentation is relevant, a better example should be shown. The list of
> what is considered true or false should be told explicitely, not end with
> "etc" that is everyone to guess.
>

It was copied from the regression test. I knew there would be follow-up
suggestions for what should be shown.


>
> Tests: On principle they should include echos in all non executed branches
> to reassure that they are indeed not executed.
>

Will do.


>
> Also, no error cases are tested. They should be. Maybe it is not very easy
> to test some cases which expect to make psql generate errors, but I feel
> they are absolutely necessary for such a feature.
>

Will do.


>
> 1: unrecognized value "whatever" for "\if"; assuming "on"
>
> I do not think that the script should continue with such an assumption.
>

I agree, and this means we can't use ParseVariableBool() as-is. I already
broke out argument reading to it's own function, knowing that it'd be the
stub for expressions. So I guess we start that now. What subset of true-ish
values do you think we should support? If we think that real expressions
are possible soon, we could only allow 'true' and 'false' for now, but if
we expect that expressions might not make it into v10, then perhaps we
should support the same text values that coerce to booleans on the server
side.


>
> 2: encountered \else after \else ... "# ERROR BEFORE"
>
> Even with ON_ERROR_STOP activated the execution continues.


> 3: no \endif
>
> Error reported, but it does not stop even with ON_ERROR_STOP.
>
> 4: include with bad nesting...
>
> Again, even with ON_ERROR_STOP, the execution continues...
>

All valid issues. Will add those to the regression as well (with
ON_ERROR_STOP disabled, obviously).


>
>
> Code:
>
>   -   if (status != PSQL_CMD_ERROR)
>   +   if ((status != PSQL_CMD_ERROR) && psqlscan_branch_active(scan_st
> ate))
>
> Why the added parenthesis?
>

Will fix.


>
>   case ...: case ...:
>
> The project rules are to add explicit /* PASSTHROUGH */ comment.
>

Will do.


>
> There are spaces at the end of one line in a comment about
> psqlscan_branch_end_state.
>
> There are multiple "TODO" comments in the file... Is it done or work in
> progress?
>

I forgot to remove them. But it would be wildly optimistic of me to think
there would be no more work for me after the first patch submission.


>
> Usually in pg comments about a function do not repeat the function name.
> For very short comment, they are /* inlined */ on one line. Use pg comment
> style.


In that case, I was copying the style found in other functions psqlscan.l -
I'm happy to remove it.

Re: \if, \elseif, \else, \endif (was Re: [HACKERS] PSQL commands: \quit_if, \quit_unless)

[Fabien COELHO]

A few comments:


Argh, better with the attachements:-(

--
Fabien.

if-error-1.sql
Description: application/sql


if-error-2.sql
Description: application/sql


if-error-3.sql
Description: application/sql


if-error-4.sql
Description: application/sql

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: \if, \elseif, \else, \endif (was Re: [HACKERS] PSQL commands: \quit_if, \quit_unless)

[Fabien COELHO]

And here's the patch. I've changed the subject line and will be submitting
a new entry to the commitfest.


A few comments:

Patch applies, make check is ok, but currently really too partial.

I do not like the choice of "elseif", which exists in PHP & VB. PL/pgSQL 
has "ELSIF" like perl, that I do not like much, though. Given the syntax 
and behavioral proximity with cpp, I suggest that "elif" would be a better 
choice.


Documentation: I do not think that the systematic test-like example in the 
documentation is relevant, a better example should be shown. The list of 
what is considered true or false should be told explicitely, not end with 
"etc" that is everyone to guess.


Tests: On principle they should include echos in all non executed branches 
to reassure that they are indeed not executed.


Also, no error cases are tested. They should be. Maybe it is not very easy 
to test some cases which expect to make psql generate errors, but I feel 
they are absolutely necessary for such a feature.


1: unrecognized value "whatever" for "\if"; assuming "on"

I do not think that the script should continue with such an assumption.

2: encountered \else after \else ... "# ERROR BEFORE"

Even with ON_ERROR_STOP activated the execution continues.

3: no \endif

Error reported, but it does not stop even with ON_ERROR_STOP.

4: include with bad nesting...

Again, even with ON_ERROR_STOP, the execution continues...


Code:

  -   if (status != PSQL_CMD_ERROR)
  +   if ((status != PSQL_CMD_ERROR) && psqlscan_branch_active(scan_state))

Why the added parenthesis?

  case ...: case ...:

The project rules are to add explicit /* PASSTHROUGH */ comment.

There are spaces at the end of one line in a comment about 
psqlscan_branch_end_state.


There are multiple "TODO" comments in the file... Is it done or work in 
progress?


Usually in pg comments about a function do not repeat the function name. 
For very short comment, they are /* inlined */ on one line. Use pg comment 
style.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

\if, \elseif, \else, \endif (was Re: [HACKERS] PSQL commands: \quit_if, \quit_unless)

[Corey Huinker]

>
>
> Fabien is pressed for time, so I've been speaking with him out-of-thread
> about how I should go about implementing it.
>
> The v1 patch will be \if , \elseif , \else, \endif, where
>  will be naively evaluated via ParseVariableBool().
>
> \ifs and \endifs must be in the same "file" (each MainLoop will start a
> new if-stack). This is partly for sanity (you can see the pairings unless
> the programmer is off in \gset meta-land), partly for ease  of design (data
> structures live in MainLoop), but mostly because it would an absolute
> requirement if we ever got around to doing \while.
>
> I hope to have something ready for the next commitfest.
>
> As for the fate of \quit_if, I can see it both ways. On the one hand,
> it's super-simple, already written, and handy.
>
> On the other hand, it's easily replaced by
>
> \if 
> \q
> \endif
>
>
> So I'll leave that as a separate reviewable patch.
>
> As for loops, I don't think anyone was pushing for implementing \while
> now, only to have a decision about what it would look like and how it would
> work. There's a whole lot of recording infrastructure (the input could be a
> stream) needed to make it happen. Moreover, I think \gexec scratched a
> lot of the itches that would have been solved via a psql looping structure.
>


And here's the patch. I've changed the subject line and will be submitting
a new entry to the commitfest.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 9915731..2c3fccd 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2007,6 +2007,83 @@ hello 10
 
   
 
+  
+\if expr
+\elseif expr
+\else
+\endif
+
+
+This group of commands implements nestable conditional blocks, like
+this:
+
+
+\if true
+\if 1
+\if yes
+\if on
+\echo 'all true'
+all true
+\endif
+\endif
+\endif
+\endif
+\if false
+\elseif 0
+\elseif no
+\elseif off
+\else
+\echo 'all false'
+all false
+\endif
+\if true
+\else
+\echo 'should not print #1'
+\endif
+\if false
+\elseif true
+\else
+\echo 'should not print #2'
+\endif
+
+
+Conditional blocks must begin with a \if and end
+with an \endif, and the pairs must be found in
+the same source file. If an EOF is reached on the main file or an
+\include-ed file before all 
+\if-\endif are matched, then
+psql will raise an error.
+
+
+The \if and \elseif commands
+read the rest of the line and evaluate it as a boolean expression.
+Currently, expressions are limited to the same values allowed for
+other option booleans (true, false, 1, 0, on, off, yes, no, etc).
+
+
+Queries within a false branch of a conditional block will not be
+sent to the server.
+
+
+Non-conditional \-commands within a false branch
+of a conditional block will not be evaluated for correctness. The
+command will be ignored along with all remaining input to the end
+of the line.
+
+
+Expressions on \if and \elseif
+commands within a false branch of a conditional block will not be
+evaluated.
+
+
+A conditional block can at most one \else command.
+
+
+The \elseif command cannot follow the
+\else command.
+
+
+  
 
   
 \ir or \include_relative 
filename
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 4139b77..d4e0bb8 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -49,6 +49,7 @@
 #include "psqlscanslash.h"
 #include "settings.h"
 #include "variables.h"
+#include "fe_utils/psqlscan_int.h"
 
 /*
  * Editable database object types.
@@ -132,7 +133,7 @@ HandleSlashCmds(PsqlScanState scan_state,
status = PSQL_CMD_ERROR;
}
 
-   if (status != PSQL_CMD_ERROR)
+   if ((status != PSQL_CMD_ERROR) && psqlscan_branch_active(scan_state))
{
/* eat any remaining arguments after a valid command */
/* note we suppress evaluation of backticks here */
@@ -194,6 +195,32 @@ read_connect_arg(PsqlScanState scan_state)
return result;
 }
 
+/*
+ * Read and interpret argument as a boolean expression.
+ */
+static bool
+read_boolean_expression(PsqlScanState scan_state, const char *action)
+{
+   boolresult = false;
+   char*expr = psql_scan_slash_option(scan_state,
+   
OT_NORMAL, NULL, false);
+   if (expr)
+   {
+   if (ParseVariableBool(expr, action))
+   result = true;
+   free(expr);
+   }
+   return result;
+}
+
+static bool
+is_branching_command(const char *cmd)
+{
+ 

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

On Wed, Jan 18, 2017 at 12:08 AM, Michael Paquier  wrote:

> On Wed, Jan 18, 2017 at 3:24 AM, Robert Haas 
> wrote:
> > On Sat, Jan 14, 2017 at 12:22 AM, Tom Lane  wrote:
> >>
> >> $ cat loop.sql
> >> \if :x < 1000
> >>   \echo :x
> >>   \set x :x + 1
> >>   \include loop.sql
> >> \fi
> >> $ psql --set x=0 -f loop.sql
> >>
> >> Somebody is going to think of that workaround for not having loops, and
> >> then whine about how psql runs out of file descriptors and/or stack.
> >
> > Hmm, I think somebody just DID think of it.
> >
> > But personally this doesn't upset me a bit.  If somebody complains
> > about that particular thing, I think that would be an excellent time
> > to suggest that they write a patch to add a looping construct.
>
> Agreed.
>
> As far as I can see on this thread, something could be done, it is
> just that we don't know yet at which extent things could be done with
> the first shot. There are many things that could be done, but at least
> I'd suggest to get \if, \fi and \quit to satisfy the first
> requirements of this thread, and let loops out of it. I have switched
> the patch as "returned with feedback" as getting a new patch is going
> to require some thoughts to get the context handling done correctly on
> psql side.
> --
> Michael
>

Fabien is pressed for time, so I've been speaking with him out-of-thread
about how I should go about implementing it.

The v1 patch will be \if , \elseif , \else, \endif, where 
will be naively evaluated via ParseVariableBool().

\ifs and \endifs must be in the same "file" (each MainLoop will start a new
if-stack). This is partly for sanity (you can see the pairings unless the
programmer is off in \gset meta-land), partly for ease  of design (data
structures live in MainLoop), but mostly because it would an absolute
requirement if we ever got around to doing \while.

I hope to have something ready for the next commitfest.

As for the fate of \quit_if, I can see it both ways. On the one hand, it's
super-simple, already written, and handy.

On the other hand, it's easily replaced by

\if 
\q
\endif


So I'll leave that as a separate reviewable patch.

As for loops, I don't think anyone was pushing for implementing \while now,
only to have a decision about what it would look like and how it would
work. There's a whole lot of recording infrastructure (the input could be a
stream) needed to make it happen. Moreover, I think \gexec scratched a lot
of the itches that would have been solved via a psql looping structure.

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Michael Paquier]

On Wed, Jan 18, 2017 at 3:24 AM, Robert Haas  wrote:
> On Sat, Jan 14, 2017 at 12:22 AM, Tom Lane  wrote:
>>
>> $ cat loop.sql
>> \if :x < 1000
>>   \echo :x
>>   \set x :x + 1
>>   \include loop.sql
>> \fi
>> $ psql --set x=0 -f loop.sql
>>
>> Somebody is going to think of that workaround for not having loops, and
>> then whine about how psql runs out of file descriptors and/or stack.
>
> Hmm, I think somebody just DID think of it.
>
> But personally this doesn't upset me a bit.  If somebody complains
> about that particular thing, I think that would be an excellent time
> to suggest that they write a patch to add a looping construct.

Agreed.

As far as I can see on this thread, something could be done, it is
just that we don't know yet at which extent things could be done with
the first shot. There are many things that could be done, but at least
I'd suggest to get \if, \fi and \quit to satisfy the first
requirements of this thread, and let loops out of it. I have switched
the patch as "returned with feedback" as getting a new patch is going
to require some thoughts to get the context handling done correctly on
psql side.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Sat, Jan 14, 2017 at 12:22 AM, Tom Lane  wrote:
>
> $ cat loop.sql
> \if :x < 1000
>   \echo :x
>   \set x :x + 1
>   \include loop.sql
> \fi
> $ psql --set x=0 -f loop.sql
>
> Somebody is going to think of that workaround for not having loops, and
> then whine about how psql runs out of file descriptors and/or stack.

Hmm, I think somebody just DID think of it.

But personally this doesn't upset me a bit.  If somebody complains
about that particular thing, I think that would be an excellent time
to suggest that they write a patch to add a looping construct.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Jim Nasby]

On 1/13/17 11:22 PM, Tom Lane wrote:

I wonder what depth of include-file nesting
psql can support, or whether we'll be able to fix it to optimize tail
recursion of an include file.  Because somebody will be asking for that
if this is the toolset you give them.


I think the solution to that is straightforward: tell users that we hope 
to eventually support loops and that in the meantime if you try to work 
around that with recursion you get to keep both pieces when it breaks. 
While not ideal I think that's a lot better than throwing the whole idea 
out because some people will abuse it...

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

$ cat loop.sql
\if :x < 1000
 \echo :x
 \set x :x + 1
 \include loop.sql
\fi
$ psql --set x=0 -f loop.sql


Nice one! CPP does not have arithmetic, so it is harder to do that because 
one must reimplement arithmetic with #if...



Somebody is going to think of that workaround for not having loops, and
then whine about how psql runs out of file descriptors and/or stack.


One can already have "include nested too deeply" errors, I guess, without 
a recursion.


I would say that's this consequence is acceptable, and that this is a 
feature.


I think having some kind of client-side test brings significant value 
because it would help writing application schema upgrades for instance, 
and that the this potential whining source is worth handling.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Tom Lane]

Pavel Stehule  writes:
> 2017-01-14 0:20 GMT+01:00 Corey Huinker :
>> - leaving loops out for now?

> +1

I'm just going to say one thing about that: some people will remember
that you can build a Turing machine with either conditionals+iteration
or conditionals+recursion.  I wonder what depth of include-file nesting
psql can support, or whether we'll be able to fix it to optimize tail
recursion of an include file.  Because somebody will be asking for that
if this is the toolset you give them.

regards, tom lane

PS: if I'm being too obscure for you, consider:

$ cat loop.sql
\if :x < 1000
  \echo :x
  \set x :x + 1
  \include loop.sql
\fi
$ psql --set x=0 -f loop.sql

Somebody is going to think of that workaround for not having loops, and
then whine about how psql runs out of file descriptors and/or stack.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

Hi

2017-01-14 0:20 GMT+01:00 Corey Huinker :

> Ok, so activity on this thread has died down. I'm not sure if that's
> consensus or exhaustion.
>

the original idea \quit_if is leaved? It is pity - it is common use case -
and because we cannot to implement macros in psql, then can be very useful


>
> Are we good with:
> - implementing basic \if EXPR \elseif EXPR \else \endif, where the EXPR is
> an expression but is currently limited to a simple string that will be
> evaluated for truth via ParseVariableBool()?
>

+1


> - moving beyond trivial expressions in a later commit?
>

the expressions are in nice to have category - there can be a logic

 if there is only a variable, check the variable; else eval on server and
check the result.


> - leaving loops out for now?
>

+1

Regards

Pavel

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

Ok, so activity on this thread has died down. I'm not sure if that's
consensus or exhaustion.

Are we good with:
- implementing basic \if EXPR \elseif EXPR \else \endif, where the EXPR is
an expression but is currently limited to a simple string that will be
evaluated for truth via ParseVariableBool()?
- moving beyond trivial expressions in a later commit?
- leaving loops out for now?

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[David G. Johnston]

On Mon, Dec 19, 2016 at 11:23 AM, Alvaro Herrera 
wrote:

> David G. Johnston wrote:
>
> > Being able to do more conditional work in psql would make setting up more
> > robust scripts easier and without either losing transaction capabilities
> or
> > session pooling for improved performance when large numbers of small
> > commands are run in between flow control in done in bash.
>
> Have you tried to script processes in shell using a single background
> psql process with which the shell code communicates using a pipe?  I've
> long been curious about that approach, but never had a strong need
> enough to actually write the code.  It should be possible.
>

​I've envisioned and read up a bit on the approach but the cost-benefit
hasn't yet made actually doing it worthwhile.

I do pretty much myself run all of the scripts I've been writing - the
cost-benefit ratio is likely to change once they are turned over to a
non-programmer to run.

David J.​

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Alvaro Herrera]

David G. Johnston wrote:

> Being able to do more conditional work in psql would make setting up more
> robust scripts easier and without either losing transaction capabilities or
> session pooling for improved performance when large numbers of small
> commands are run in between flow control in done in bash.

Have you tried to script processes in shell using a single background
psql process with which the shell code communicates using a pipe?  I've
long been curious about that approach, but never had a strong need
enough to actually write the code.  It should be possible.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

>
> >
> >   -- this is how I fake an 'exit 0' now:
> >   \set work_needs_to_be_done t
> >   select
> > case
> >   when :'work_needs_to_be_done'::boolean then ''
> >   else '\q'
> > end as cmd
> >   \gset
> >   :cmd
> >
> >   -- ridiculous example to illustrate complications in remembering past
> > commands
> >   select
> > case
> >when random() < 0.5 then '\ir my_script.sql'
> >when random() < 0.7 'select * from a_table; select count(*) from
> > another_table;'
> >else 'select null as foo;'
> > end as cmd
> >   \gset
> >   :cmd
> >
> > And even then, things get complicated, because an \ir include which
> makes it
> > this iteration might not make it the next, and the \endwhile might have
> been
> > inside that include, or vice-versa, an included file starts a \while it
> > doesn't finish.
>
> I see your point.  Just out of curiosity, why in the world don't you
> use something other than psql for scripting?  I mean, if you accessed
> the data from Perl or Python or
> $INSERT_YOUR_FAVORITE_SCRIPTING_LANGUAGE_HERE, you could do all of
> this stuff very easily without any contortions.  I've always thought
> of psql as something that's fine for interactive use and goofy trivial
> scripting but not really suitable for serious work.  I grant that you
> seem to be making it serve the purpose, but, man.
>

Since you asked:

Heh. I *don't* do the second example, I was just pointing out that those
things could be done, not that they should be done, and how hard it would
be to implement loops when the source code is potentially coming from a
stream.

My current client does use mostly python, but also perl, and ruby, and PHP
and, reactjs, and $NEW_THING.

Here are the reasons I often prefer psql:
- Wiring up a python script to do one if-then in the middle of 40 SQL
statements goes a long way toward obfuscating what SQL is going to be run.
- Packaging up the SQL statements in a DO $$ $$; block conceals what
statements were run, and how long they took.
- In python, etc, it's up to me to show rowcounts and timings.
- On very small docker-ish systems, the fewer things I have to install, the
better, and golly, python is large these days.
- When doing work for regulated industry clients (SOX,HIPAA, school
district PII, etc), the auditors like seeing clearly what SQL _will_ run,
what SQL _did_ run, and what was affected. psql scripts with echo-queries
set and captured output do that nicely. Installing extra scripting
languages gives them the vapors, and now we need an auditor that thinks
they know two languages, not one. I'm not saying it makes sense, I'm saying
fewer dependencies gets a auditor's checkbox checked sooner.


> Right, I think that while/for can be left for another time, as long as
> the plan doesn't preclude doing it someday.


+1

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[David G. Johnston]

On Mon, Dec 19, 2016 at 10:30 AM, Robert Haas  wrote:

>
> I see your point.  Just out of curiosity, why in the world don't you
> use something other than psql for scripting?  I mean, if you accessed
> the data from Perl or Python or
> $INSERT_YOUR_FAVORITE_SCRIPTING_LANGUAGE_HERE, you could do all of
> this stuff very easily without any contortions.  I've always thought
> of psql as something that's fine for interactive use and goofy trivial
> scripting but not really suitable for serious work.  I grant that you
> seem to be making it serve the purpose, but, man.
>

I'm coming to the realization that this sentiment, when applied to my
primary application, is probably correct...

In my situation the scripting language of choice is Bash - which largely
acts as glue for programs such as psql, pdftk, enscript, and the R language.

Being able to do more conditional work in psql would make setting up more
robust scripts easier and without either losing transaction capabilities or
session pooling for improved performance when large numbers of small
commands are run in between flow control in done in bash.

David J.

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-12-19 18:30 GMT+01:00 Robert Haas :

> On Sat, Dec 17, 2016 at 3:39 PM, Corey Huinker 
> wrote:
> >> To implement \while, we'd also need to remember previous commands so
> >> that when we reach the end of the loop, we can rewind and put all of
> >> those commands back on the stack to be executed again, or perhaps to
> >> be skipped if the \while condition turns out now to be false.
> >
> > This might be what you meant when you said "those commands back on the
> > stack", but I think we'd have to remember not a list of commands, but the
> > raw string of bytes from the start of the \while to the \endwhile (or
> > equivalent), because any psql vars within that block could themselves be
> a
> > non-parameter part of a command:
> >
> >   -- this is how I fake an 'exit 0' now:
> >   \set work_needs_to_be_done t
> >   select
> > case
> >   when :'work_needs_to_be_done'::boolean then ''
> >   else '\q'
> > end as cmd
> >   \gset
> >   :cmd
> >
> >   -- ridiculous example to illustrate complications in remembering past
> > commands
> >   select
> > case
> >when random() < 0.5 then '\ir my_script.sql'
> >when random() < 0.7 'select * from a_table; select count(*) from
> > another_table;'
> >else 'select null as foo;'
> > end as cmd
> >   \gset
> >   :cmd
> >
> > And even then, things get complicated, because an \ir include which
> makes it
> > this iteration might not make it the next, and the \endwhile might have
> been
> > inside that include, or vice-versa, an included file starts a \while it
> > doesn't finish.
>
> I see your point.  Just out of curiosity, why in the world don't you
> use something other than psql for scripting?  I mean, if you accessed
> the data from Perl or Python or
> $INSERT_YOUR_FAVORITE_SCRIPTING_LANGUAGE_HERE, you could do all of
> this stuff very easily without any contortions.  I've always thought
> of psql as something that's fine for interactive use and goofy trivial
> scripting but not really suitable for serious work.  I grant that you
> seem to be making it serve the purpose, but, man.
>

The integration of any scripting environment with SQL is much more less
than in psql - just some easy scenarios are in psql natural.

It is similar why some people like me, prefer PLpgSQL against Java, Perl,
...

years ago there was a bash integrated with postgres - for me nice idea, but
this project is dead.


>
> > So maybe what we store is a stack of buffers that are currently open
> (STDIN
> > being captured as a buffer only when a \while starts, everything else
> being
> > files), and additionally have a stack of positions where a \while started
> > (buffer_id, position in buffer).
>
> Yeah, sounds about right.
>
> > Additionally, we could assert that all \while-\endwhile pairs must
> happen in
> > the same MainLoop (aka file), and mismatches are an error.
>
> Sounds prudent.
>
> > I'm happy to keep sketching out what control structures might look like
> and
> > how to implement them. But I'm also happy to leave while/for loops out
> for
> > now.
>
> Right, I think that while/for can be left for another time, as long as
> the plan doesn't preclude doing it someday.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Sat, Dec 17, 2016 at 3:39 PM, Corey Huinker  wrote:
>> To implement \while, we'd also need to remember previous commands so
>> that when we reach the end of the loop, we can rewind and put all of
>> those commands back on the stack to be executed again, or perhaps to
>> be skipped if the \while condition turns out now to be false.
>
> This might be what you meant when you said "those commands back on the
> stack", but I think we'd have to remember not a list of commands, but the
> raw string of bytes from the start of the \while to the \endwhile (or
> equivalent), because any psql vars within that block could themselves be a
> non-parameter part of a command:
>
>   -- this is how I fake an 'exit 0' now:
>   \set work_needs_to_be_done t
>   select
> case
>   when :'work_needs_to_be_done'::boolean then ''
>   else '\q'
> end as cmd
>   \gset
>   :cmd
>
>   -- ridiculous example to illustrate complications in remembering past
> commands
>   select
> case
>when random() < 0.5 then '\ir my_script.sql'
>when random() < 0.7 'select * from a_table; select count(*) from
> another_table;'
>else 'select null as foo;'
> end as cmd
>   \gset
>   :cmd
>
> And even then, things get complicated, because an \ir include which makes it
> this iteration might not make it the next, and the \endwhile might have been
> inside that include, or vice-versa, an included file starts a \while it
> doesn't finish.

I see your point.  Just out of curiosity, why in the world don't you
use something other than psql for scripting?  I mean, if you accessed
the data from Perl or Python or
$INSERT_YOUR_FAVORITE_SCRIPTING_LANGUAGE_HERE, you could do all of
this stuff very easily without any contortions.  I've always thought
of psql as something that's fine for interactive use and goofy trivial
scripting but not really suitable for serious work.  I grant that you
seem to be making it serve the purpose, but, man.

> So maybe what we store is a stack of buffers that are currently open (STDIN
> being captured as a buffer only when a \while starts, everything else being
> files), and additionally have a stack of positions where a \while started
> (buffer_id, position in buffer).

Yeah, sounds about right.

> Additionally, we could assert that all \while-\endwhile pairs must happen in
> the same MainLoop (aka file), and mismatches are an error.

Sounds prudent.

> I'm happy to keep sketching out what control structures might look like and
> how to implement them. But I'm also happy to leave while/for loops out for
> now.

Right, I think that while/for can be left for another time, as long as
the plan doesn't preclude doing it someday.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

>
> > (Or in other words, let's see \while ... \endloop in the minimal proposal
> > as well, or at least a sketch of how to get there.)
>
> It seems to me that we could implement \if ... \else ...\endif by
> having some kind of stack that indicates which constructs we're inside
> of and whether we're currently executing commands or discarding them.
> I think we want to avoid waiting until we see \endif to start running
> commands; it's better to execute or skip/discard each command as we
> see it.
>

+1


>
> To implement \while, we'd also need to remember previous commands so
> that when we reach the end of the loop, we can rewind and put all of
> those commands back on the stack to be executed again, or perhaps to
> be skipped if the \while condition turns out now to be false.
>

This might be what you meant when you said "those commands back on the
stack", but I think we'd have to remember not a list of commands, but the
raw string of bytes from the start of the \while to the \endwhile (or
equivalent), because any psql vars within that block could themselves be a
non-parameter part of a command:

  -- this is how I fake an 'exit 0' now:
  \set work_needs_to_be_done t
  select
case
  when :'work_needs_to_be_done'::boolean then ''
  else '\q'
end as cmd
  \gset
  :cmd

  -- ridiculous example to illustrate complications in remembering past
commands
  select
case
   when random() < 0.5 then '\ir my_script.sql'
   when random() < 0.7 'select * from a_table; select count(*) from
another_table;'
   else 'select null as foo;'
end as cmd
  \gset
  :cmd

And even then, things get complicated, because an \ir include which makes
it this iteration might not make it the next, and the \endwhile might have
been inside that include, or vice-versa, an included file starts a \while
it doesn't finish.

So maybe what we store is a stack of buffers that are currently open (STDIN
being captured as a buffer only when a \while starts, everything else being
files), and additionally have a stack of positions where a \while started
(buffer_id, position in buffer).

Additionally, we could assert that all \while-\endwhile pairs must happen
in the same MainLoop (aka file), and mismatches are an error.

I'm happy to keep sketching out what control structures might look like and
how to implement them. But I'm also happy to leave while/for loops out for
now.

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Fri, Dec 16, 2016 at 12:40 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> So I think it would be reasonable for somebody to implement \if,
>> \elseif, \endif first, with the argument having to be, precisely, a
>> single variable and nothing else (not even a negator).  Then a future
>> patch could allow an expression there instead of a variable.  I don't
>> think that would be any harder to review than going all the way to #5
>> in one shot, and actually it might be simpler.
>
> This seems like a reasonable implementation plan to me, not least because
> it tackles the hard part first.  There's no doubt that we can build an
> expression evaluator, but I'm not entirely sure how we're going to wedge
> conditional eval or loops into psql's command reader.
>
> (Or in other words, let's see \while ... \endloop in the minimal proposal
> as well, or at least a sketch of how to get there.)

It seems to me that we could implement \if ... \else ...\endif by
having some kind of stack that indicates which constructs we're inside
of and whether we're currently executing commands or discarding them.
I think we want to avoid waiting until we see \endif to start running
commands; it's better to execute or skip/discard each command as we
see it.

To implement \while, we'd also need to remember previous commands so
that when we reach the end of the loop, we can rewind and put all of
those commands back on the stack to be executed again, or perhaps to
be skipped if the \while condition turns out now to be false.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-12-17 16:26 GMT+01:00 Fabien COELHO :

>
> Hello Tom,
>
> So I think it would be reasonable for somebody to implement \if,
>>> \elseif, \endif first, with the argument having to be, precisely, a
>>> single variable and nothing else (not even a negator). [...]
>>>
>>
> This seems like a reasonable implementation plan to me, not least because
>> it tackles the hard part first.  There's no doubt that we can build an
>> expression evaluator, but I'm not entirely sure how we're going to wedge
>> conditional eval or loops into psql's command reader.
>>
>> (Or in other words, let's see \while ... \endloop in the minimal proposal
>> as well, or at least a sketch of how to get there.)
>>
>
> My 0.02 €:
>
> I have not seen any use case for a loop... Does someone have something
> convincing? I could think of some use in benchmarking (aka in pgbench), but
> not psql... But I may lack imagination.
>
> If one realistic case is found, then from a syntactic point of view
> "\while expr ... \endwhile/loop/whatever" looks straightforward enough.
>

maybe iteration over cursor can be interesting - but now with with \gexec
it is not important.


>
> However, the implementation issues are pretty different from "if" which
> can be managed pretty simply on the fly with a stack and a little
> automaton. A loop needs to store its body and evaluate it over and over,
> which means having processed the input up to the end of the loop before
> proceeding, including nesting and so... it is a much less interactive
> friendly construct.
>
> Note that although "cpp" has an if, but it does not have any loop.
>
> In my opinion psql should stay at that same simple level: ISTM that the
> typical psql-script requirement is to be able to test some things, eg for
> installing or upgrading the schema of an application, and for that
> variables, expressions server side and maybe client side, and conditions
> are mostly enough. A lot of "IF EXISTS" added to many commands recently are
> motivated to handle this kind of use-case at the command per command level,
> which is not necessarily the right place.
>
> A while loops turns a simple thing into a potential Turing-complete beast,
> without a strong incentive I think that it should be avoided.
>

+1

Pavel

>
> --
> Fabien.

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello Tom,


So I think it would be reasonable for somebody to implement \if,
\elseif, \endif first, with the argument having to be, precisely, a
single variable and nothing else (not even a negator). [...]



This seems like a reasonable implementation plan to me, not least because
it tackles the hard part first.  There's no doubt that we can build an
expression evaluator, but I'm not entirely sure how we're going to wedge
conditional eval or loops into psql's command reader.

(Or in other words, let's see \while ... \endloop in the minimal proposal
as well, or at least a sketch of how to get there.)


My 0.02 €:

I have not seen any use case for a loop... Does someone have something 
convincing? I could think of some use in benchmarking (aka in pgbench), 
but not psql... But I may lack imagination.


If one realistic case is found, then from a syntactic point of view 
"\while expr ... \endwhile/loop/whatever" looks straightforward enough.


However, the implementation issues are pretty different from "if" which 
can be managed pretty simply on the fly with a stack and a little 
automaton. A loop needs to store its body and evaluate it over and over, 
which means having processed the input up to the end of the loop before 
proceeding, including nesting and so... it is a much less interactive 
friendly construct.


Note that although "cpp" has an if, but it does not have any loop.

In my opinion psql should stay at that same simple level: ISTM that the 
typical psql-script requirement is to be able to test some things, eg for 
installing or upgrading the schema of an application, and for that 
variables, expressions server side and maybe client side, and conditions 
are mostly enough. A lot of "IF EXISTS" added to many commands recently 
are motivated to handle this kind of use-case at the command per command 
level, which is not necessarily the right place.


A while loops turns a simple thing into a potential Turing-complete beast, 
without a strong incentive I think that it should be avoided.


--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-12-16 21:18 GMT+01:00 Robert Haas :

> On Fri, Dec 16, 2016 at 12:28 PM, Pavel Stehule 
> wrote:
> > why do you need special operator for negation? there is only one use
> case.
> > It can be solved by \if_not
>
> That's exactly the kind of thing I *don't* want to do.  If you
> absolutely must have that and you can't wait until we get a full-blown
> expression evaluator, then just swap the \if side with the \else side
> and call it good.  The whole point here is to avoid introducing weird
> hacks for lack of a full expression evaluator that will just become
> annoyances once we have one.
>

I don't need it. Because we don't expect expression there, then "not" or
"if_not" is not necessary.

Regards

Pavel


>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Fri, Dec 16, 2016 at 12:28 PM, Pavel Stehule  wrote:
> why do you need special operator for negation? there is only one use case.
> It can be solved by \if_not

That's exactly the kind of thing I *don't* want to do.  If you
absolutely must have that and you can't wait until we get a full-blown
expression evaluator, then just swap the \if side with the \else side
and call it good.  The whole point here is to avoid introducing weird
hacks for lack of a full expression evaluator that will just become
annoyances once we have one.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-12-16 18:33 GMT+01:00 David G. Johnston :

> On Fri, Dec 16, 2016 at 10:28 AM, Pavel Stehule 
> wrote:
>
>> 2016-12-16 18:21 GMT+01:00 David G. Johnston 
>> :
>>
>>> On Fri, Dec 16, 2016 at 9:55 AM, Robert Haas 
>>> wrote:
>>>

 If the expected committed patch set includes #5 then this becomes a
>>> matter for reviewer convenience so never mind.  But if its at all possible
>>> for #5 to be punted down the road incorporating the eventual "not var" and
>>> "not(var)" syntax into #1 as a kind of shim would seem desirable.
>>>
>>
>> why do you need special operator for negation? there is only one use
>> case. It can be solved by \if_not
>>
>
> ​Not following the thread that closely and the section Robert quoted
> didn't include "\if_not" as a syntax option.  I figured the idea was to
> limit the number of backslash commands and leave the power in the
> expression evaluation.
>

without a expression you can store a negation to variable

I can imagine simple functional only expressions evaluated on client side.

\if not(table_exists('table_name'))

full expressions are not easy implemented without bigger changes in psql
parser design - and I don't see any reason why do some too complex there. I
would not to replace bash, perl, python or lua.


>
> David J.
> ​
>

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Tom Lane]

Robert Haas  writes:
> So I think it would be reasonable for somebody to implement \if,
> \elseif, \endif first, with the argument having to be, precisely, a
> single variable and nothing else (not even a negator).  Then a future
> patch could allow an expression there instead of a variable.  I don't
> think that would be any harder to review than going all the way to #5
> in one shot, and actually it might be simpler.

This seems like a reasonable implementation plan to me, not least because
it tackles the hard part first.  There's no doubt that we can build an
expression evaluator, but I'm not entirely sure how we're going to wedge
conditional eval or loops into psql's command reader.

(Or in other words, let's see \while ... \endloop in the minimal proposal
as well, or at least a sketch of how to get there.)

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[David G. Johnston]

On Fri, Dec 16, 2016 at 10:28 AM, Pavel Stehule 
wrote:

> 2016-12-16 18:21 GMT+01:00 David G. Johnston :
>
>> On Fri, Dec 16, 2016 at 9:55 AM, Robert Haas 
>> wrote:
>>
>>>
>>> If the expected committed patch set includes #5 then this becomes a
>> matter for reviewer convenience so never mind.  But if its at all possible
>> for #5 to be punted down the road incorporating the eventual "not var" and
>> "not(var)" syntax into #1 as a kind of shim would seem desirable.
>>
>
> why do you need special operator for negation? there is only one use case.
> It can be solved by \if_not
>

​Not following the thread that closely and the section Robert quoted didn't
include "\if_not" as a syntax option.  I figured the idea was to limit the
number of backslash commands and leave the power in the expression
evaluation.

David J.
​

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-12-16 18:21 GMT+01:00 David G. Johnston :

> On Fri, Dec 16, 2016 at 9:55 AM, Robert Haas 
> wrote:
>
>> On Mon, Dec 5, 2016 at 12:32 PM, Robert Haas 
>> wrote:
>> >>  - possible incremental implemention steps on this path:
>> >>
>> >>   (1) minimal condition and expression, compatible with
>> >>   a possible future full-blown expression syntax
>> >>
>> >>  \if :variable
>> >>  \if not :variable -- maybe \if ! :variable
>>
>
> We don't presently have a unary boolean operator named "!" so adding this
> variant would create an inconsistency
>

If we allow some complex expressions there, then it should be a SQL
expressions evaluated on server side.

There are two variants - 1. simple client side expression - can be
functional only, 2. complex server side expression.


>
>> So I think it would be reasonable for somebody to implement \if,
>> \elseif, \endif first, with the argument having to be, precisely, a
>> single variable and nothing else (not even a negator).  Then a future
>> patch could allow an expression there instead of a variable.  I don't
>> think that would be any harder to review than going all the way to #5
>> in one shot, and actually it might be simpler.
>
>
> ​I  worry about the case of disallowing negation in #1 and then not
> getting to #5 (in the same version) where the expression "not(var)" becomes
> possible.​
>
> If the expected committed patch set includes #5 then this becomes a matter
> for reviewer convenience so never mind.  But if its at all possible for #5
> to be punted down the road incorporating the eventual "not var" and
> "not(var)" syntax into #1 as a kind of shim would seem desirable.
>

why do you need special operator for negation? there is only one use case.
It can be solved by \if_not


>
> David J.
>
>
>
>

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[David G. Johnston]

On Fri, Dec 16, 2016 at 9:55 AM, Robert Haas  wrote:

> On Mon, Dec 5, 2016 at 12:32 PM, Robert Haas 
> wrote:
> >>  - possible incremental implemention steps on this path:
> >>
> >>   (1) minimal condition and expression, compatible with
> >>   a possible future full-blown expression syntax
> >>
> >>  \if :variable
> >>  \if not :variable -- maybe \if ! :variable
>

We don't presently have a unary boolean operator named "!" so adding this
variant would create an inconsistency


> So I think it would be reasonable for somebody to implement \if,
> \elseif, \endif first, with the argument having to be, precisely, a
> single variable and nothing else (not even a negator).  Then a future
> patch could allow an expression there instead of a variable.  I don't
> think that would be any harder to review than going all the way to #5
> in one shot, and actually it might be simpler.


​I  worry about the case of disallowing negation in #1 and then not getting
to #5 (in the same version) where the expression "not(var)" becomes
possible.​

If the expected committed patch set includes #5 then this becomes a matter
for reviewer convenience so never mind.  But if its at all possible for #5
to be punted down the road incorporating the eventual "not var" and
"not(var)" syntax into #1 as a kind of shim would seem desirable.

David J.

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Mon, Dec 5, 2016 at 12:32 PM, Robert Haas  wrote:
>>  - possible incremental implemention steps on this path:
>>
>>   (1) minimal condition and expression, compatible with
>>   a possible future full-blown expression syntax
>>
>>  \if :variable
>>  \if not :variable -- maybe \if ! :variable
>>...
>>  \endif
>>
>>   (2) add "\else"
>>
>>   (3) add "\elif ..." (or maybe "\elsif ..."?)
>>
>>   (4) add greater but limited expressions, compatible with a full blown
>>   expression syntax (eg \if :var/const  :var/const)
>>
>>   (5) add full-blown  support for \if, which suggest that
>>   it would also be available for \set
>>
>>
>> Does this looks okay, or does it need to be amended?
>>
>> A few comments:
>>
>> Given the experience with pgbench and the psql context, I do not think that
>> it would really need to go beyond step 2 above, but I agree that I may be
>> wrong and it is best to be prepared for that from the start. Given the
>> complexity and effort involved with (5), it seems wise to wait for a clearer
>> motivation with actual use-cases before going that far.
>
> Well, my vote would be to go all the way to #5 in one commit.
> Stopping short of that doesn't seem to me to save enough work to make
> much sense.  I don't think we're talking about anything all that
> complex, and it will make future improvements a lot simpler.

After having thought about this a little bit further and reread this a
bit more carefully, I would like to revise my position.  Really, what
I don't want to end up with is a hand-coded expression syntax that is
very limited which then has to be replaced with a full-blown lexer and
parser.  That is, I do not want to ever be at step "4" of this
proposal.

So I think it would be reasonable for somebody to implement \if,
\elseif, \endif first, with the argument having to be, precisely, a
single variable and nothing else (not even a negator).  Then a future
patch could allow an expression there instead of a variable.  I don't
think that would be any harder to review than going all the way to #5
in one shot, and actually it might be simpler.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

But if it does make sense to share, then that's another reason for not 
designing something ad-hoc for psql: integrating it later will be more 
work in total.


Yes, but not much: evaluating "[!] :var" special syntax would be dropped, 
but I do not think that it is the main issue with these features.


I understand that your conclusion is to require psql expressions + 
conditions as one (large) patch.


Given my personnal experience with the patch process, my conclusion is 
that I will very probably not do it. Just adding logical expressions to 
pgbench, a minor feature for a minor client, has already been spread over 
3 CF, and this is a part of what is required for the condition & 
expression in psql.


Hopefully someone else will do it, and I'll do some reviewing then.

--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Tue, Dec 6, 2016 at 8:45 AM, Fabien COELHO  wrote:
> There are some differences: pgbench already had one operator at a time
> expressions, while psql has survived till today with none, which suggest a
> less pressing need.

I don't really think so.  People have been wanting expressions in psql
since I got involved in the project.

> Moreover the features are partly orthogonal and would touch psql
> significantly in different although probably overlapping areas:
>  - expressions is rather about \set, even if reused with \if as well
>  - condition is about \if ... \endif and ignoring some input lines

I don't think that difference is very relevant, really.

> The current expression evaluation in pgbench is about 1000 lines for
> scanning, parsing & evaluating, and does not yet support boolean
> expressions, although a patch for that has been in the queue for some time.
> I foresee that someone will suggest/require/demand... that the expression
> code be shared between pgbench and psql, which is another argument for
> dissociating these two features (expression and conditional in psql) from
> the start.

That seems like an argument the other way, from here.  I'm not sure
that Tom is right in wanting so much to be shared between psql and
pgbench.  They're different tools with different purposes, and I'm not
sure sharing between them makes much sense.  But if it does make sense
to share, then that's another reason for not designing something
ad-hoc for psql: integrating it later will be more work in total.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello Robert,

Your experience as an seasoned core developer and a committer is 
probably different:-)


Well, everybody can have their own opinion on what is reasonable.


Sure.


There are times I argue for making a patch smaller (frequent) and
times when I argue for making it bigger (rare).


Yep.

We had pretty much this exact same argument about the pgbench lexer and 
parser and in the event I coded something up in less than a day.  This 
argument feels like a rerun of that one.


There are some differences: pgbench already had one operator at a time 
expressions, while psql has survived till today with none, which suggest a 
less pressing need.


Moreover the features are partly orthogonal and would touch psql 
significantly in different although probably overlapping areas:

 - expressions is rather about \set, even if reused with \if as well
 - condition is about \if ... \endif and ignoring some input lines

The current expression evaluation in pgbench is about 1000 lines for 
scanning, parsing & evaluating, and does not yet support boolean 
expressions, although a patch for that has been in the queue for some 
time. I foresee that someone will suggest/require/demand... that the 
expression code be shared between pgbench and psql, which is another 
argument for dissociating these two features (expression and conditional 
in psql) from the start.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Tue, Dec 6, 2016 at 1:28 AM, Fabien COELHO  wrote:
> First, my experience as a basic patch submitter is that any patch which does
> more than one thing at a time, even somehow closely related changes, is
> asked to be split into distinct sub-patches, and is harder to get through.
>
> Second, requiring more advanced features is a recipee for getting nothing in
> the end, because even if not "that complex" it requires significant more
> time to develop. The first step I outlined is enough to handle the submitted
> use case and is compatible with grand plans which would change significantly
> psql, so seems a reasonnable intermediate target.
>
> Your experience as an seasoned core developer and a committer is probably
> different:-)

Well, everybody can have their own opinion on what is reasonable.
There are times I argue for making a patch smaller (frequent) and
times when I argue for making it bigger (rare).  We had pretty much
this exact same argument about the pgbench lexer and parser and in the
event I coded something up in less than a day.  This argument feels
like a rerun of that one.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello Robert,


Given the experience with pgbench and the psql context, I do not think that
it would really need to go beyond step 2 above, but I agree that I may be
wrong and it is best to be prepared for that from the start. Given the
complexity and effort involved with (5), it seems wise to wait for a clearer
motivation with actual use-cases before going that far.


Well, my vote would be to go all the way to #5 in one commit.
Stopping short of that doesn't seem to me to save enough work to make
much sense.  I don't think we're talking about anything all that
complex, and it will make future improvements a lot simpler.


First, my experience as a basic patch submitter is that any patch which 
does more than one thing at a time, even somehow closely related changes, 
is asked to be split into distinct sub-patches, and is harder to get 
through.


Second, requiring more advanced features is a recipee for getting nothing 
in the end, because even if not "that complex" it requires significant 
more time to develop. The first step I outlined is enough to handle the 
submitted use case and is compatible with grand plans which would change 
significantly psql, so seems a reasonnable intermediate target.


Your experience as an seasoned core developer and a committer is probably 
different:-)


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Sat, Dec 3, 2016 at 2:16 AM, Fabien COELHO  wrote:
>> My guess is that something comparable to where pgbench is would be a
>> reasonable target --- not least because I think we should strive to
>> reduce unnecessary differences between psql and pgbench metalanguages.
>>
>> I'm not sure that I'm ready to propose that they should share the same
>> expression engine, but perhaps it's not a totally wacky idea.
>
>
> I'm trying to summarize a proposal for a conditional structure:
>
>  - existing psql ":"-variables can be used, allowing anything from SQL
>   (eg querying about available objects, features, extensions,
>current settings...)
>
>  - target psql conditional syntax could be:
>
> \if 
>   ...
> \elif <...>
>   ...
> \else
>   ...
> \endif
>
>  - possible incremental implemention steps on this path:
>
>   (1) minimal condition and expression, compatible with
>   a possible future full-blown expression syntax
>
>  \if :variable
>  \if not :variable -- maybe \if ! :variable
>...
>  \endif
>
>   (2) add "\else"
>
>   (3) add "\elif ..." (or maybe "\elsif ..."?)
>
>   (4) add greater but limited expressions, compatible with a full blown
>   expression syntax (eg \if :var/const  :var/const)
>
>   (5) add full-blown  support for \if, which suggest that
>   it would also be available for \set
>
>
> Does this looks okay, or does it need to be amended?
>
> A few comments:
>
> Given the experience with pgbench and the psql context, I do not think that
> it would really need to go beyond step 2 above, but I agree that I may be
> wrong and it is best to be prepared for that from the start. Given the
> complexity and effort involved with (5), it seems wise to wait for a clearer
> motivation with actual use-cases before going that far.

Well, my vote would be to go all the way to #5 in one commit.
Stopping short of that doesn't seem to me to save enough work to make
much sense.  I don't think we're talking about anything all that
complex, and it will make future improvements a lot simpler.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-12-04 20:55 GMT+01:00 Fabien COELHO :

>
> Yes, that is a possibility, but this can already be queried into a
>>> :-variable, so it is less indispensable.
>>>
>>
>> can you show some examples, please?
>>
>
>  SELECT COUNT(*) AS has_unit_extension
>FROM pg_extension WHERE extname='unit' \gset
>  \echo :has_unit_extension
>  1
>
> So that
>
>  \if ! :hash_unit_extension
>  CREATE TABLE foo(id SERIAL, stuff UNIT);
>  \else
>  \echo "unit extension is not loaded"
>  \quit
>  \fi
>
> Ok, for this example one may try:
>
>  CREATE EXTENSION IF NOT EXISTS unit;
>
> Or use the "ON_ERROR_STOP" setting, but that is the idea, SQL can be used
> to test anything server-side.
>

understand

I am thinking so first step can be simply and much more friendly replaced
by specialized function:

\if has_extension(...)

the functions are supported by pgbench already, so we can take code from
there.

I don't think so psql script language should be too rich - it should be DSL
and often use cases should be supported with friendly syntax.

The set of functions can be small in first stage - we can support only one
function.

Regards

Pavel

>
> --
> Fabien.
>

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Yes, that is a possibility, but this can already be queried into a 
:-variable, so it is less indispensable.


can you show some examples, please?


 SELECT COUNT(*) AS has_unit_extension
   FROM pg_extension WHERE extname='unit' \gset
 \echo :has_unit_extension
 1

So that

 \if ! :hash_unit_extension
 CREATE TABLE foo(id SERIAL, stuff UNIT);
 \else
 \echo "unit extension is not loaded"
 \quit
 \fi

Ok, for this example one may try:

 CREATE EXTENSION IF NOT EXISTS unit;

Or use the "ON_ERROR_STOP" setting, but that is the idea, SQL can be used 
to test anything server-side.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-12-04 17:35 GMT+01:00 Fabien COELHO :

>
> Hello Pavel,
>
> Some possibilities from pgbench can have sense in psql too - generating
>> some random numbers from a range.
>>
>
> Could you expand on the use case where this would be useful?
>

writing test cases


>
> In the end we use one parser for psql and for pgbench.
>>
>
> Note that "master" lexer is already shared, thanks to Tom, so as to detect
> consistently where a query ends.
>
> I agree, so step 2 should be enough, and I accept so there is opened door
>> for any future enhancing.
>>
>
> Good, because that was the idea:-)
>
> We can implement some client side boolean functions (similar to pgbench
>> functions that can cover often tasks: version_less, version_greather,
>> user_exists, tables_exists, index_exists, variable_exists, schema_exists,
>>
>
> Yes, that is a possibility, but this can already be queried into a
> :-variable, so it is less indispensable.


can you show some examples, please?

Regards

Pavel



>
>
> --
> Fabien.
>

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello Pavel,


Some possibilities from pgbench can have sense in psql too - generating
some random numbers from a range.


Could you expand on the use case where this would be useful?


In the end we use one parser for psql and for pgbench.


Note that "master" lexer is already shared, thanks to Tom, so as to detect 
consistently where a query ends.



I agree, so step 2 should be enough, and I accept so there is opened door
for any future enhancing.


Good, because that was the idea:-)


We can implement some client side boolean functions (similar to pgbench
functions that can cover often tasks: version_less, version_greather,
user_exists, tables_exists, index_exists, variable_exists, schema_exists,


Yes, that is a possibility, but this can already be queried into a 
:-variable, so it is less indispensable.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-12-03 8:16 GMT+01:00 Fabien COELHO :

>
> Hello,
>
> My guess is that something comparable to where pgbench is would be a
>> reasonable target --- not least because I think we should strive to
>> reduce unnecessary differences between psql and pgbench metalanguages.
>>
>> I'm not sure that I'm ready to propose that they should share the same
>> expression engine, but perhaps it's not a totally wacky idea.
>>
>
> I'm trying to summarize a proposal for a conditional structure:
>
>  - existing psql ":"-variables can be used, allowing anything from SQL
>   (eg querying about available objects, features, extensions,
>current settings...)
>
>  - target psql conditional syntax could be:
>
> \if 
>   ...
> \elif <...>
>   ...
> \else
>   ...
> \endif
>
>  - possible incremental implemention steps on this path:
>
>   (1) minimal condition and expression, compatible with
>   a possible future full-blown expression syntax
>
>  \if :variable
>  \if not :variable -- maybe \if ! :variable
>...
>  \endif
>
>   (2) add "\else"
>
>   (3) add "\elif ..." (or maybe "\elsif ..."?)
>
>   (4) add greater but limited expressions, compatible with a full blown
>   expression syntax (eg \if :var/const 
> :var/const)
>
>   (5) add full-blown  support for \if, which suggest that
>   it would also be available for \set
>
>
> Does this looks okay, or does it need to be amended?
>
> A few comments:
>
> Given the experience with pgbench and the psql context, I do not think
> that it would really need to go beyond step 2 above, but I agree that I may
> be wrong and it is best to be prepared for that from the start. Given the
> complexity and effort involved with (5), it seems wise to wait for a
> clearer motivation with actual use-cases before going that far.
>
> In the benchmarking context, the point is to test performance for a
> client-server scenario, in which case the client has to do some things,
> thus needs miminal computation capabilities which were available early in
> pgbench (\setrandom, \set with one arithmetic operation...) because they
> were necessary. Probably \if ... would make sense in pgbench, so I'll think
> about it.


> In psql interactive context, conditions and expressions do not make sense
> as the user typing the command knows what they want, thus will do
> evaluations in their head to avoid typing stuff...
>
> In psql scripting context, conditions are likely to be about what to do
> with the database, and what I understand of the use-case which started this
> discussion is that it is about versions, settings, available objects,
> typically "if this is already installed, skip this part" or "if version
> beyond YYY, cannot install because of missing features" when installing and
> initializing an application. For this purpose, ISTM that the query is
> necessarily performed server-side, thus the actual need for a full-blown
> client-side expression is limited or void, although as already said being
> prepared is a good thing.
>

Some possibilities from pgbench can have sense in psql too - generating
some random numbers from a range.  In the end we use one parser for psql
and for pgbench.

I agree, so step 2 should be enough, and I accept so there is opened door
for any future enhancing.

We can implement some client side boolean functions (similar to pgbench
functions that can cover often tasks: version_less, version_greather,
user_exists, tables_exists, index_exists, variable_exists, schema_exists,

Regards

Pavel

>
> --
> Fabien.
>

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello,


My guess is that something comparable to where pgbench is would be a
reasonable target --- not least because I think we should strive to
reduce unnecessary differences between psql and pgbench metalanguages.

I'm not sure that I'm ready to propose that they should share the same
expression engine, but perhaps it's not a totally wacky idea.


I'm trying to summarize a proposal for a conditional structure:

 - existing psql ":"-variables can be used, allowing anything from SQL
  (eg querying about available objects, features, extensions,
   current settings...)

 - target psql conditional syntax could be:

\if 
  ...
\elif <...>
  ...
\else
  ...
\endif

 - possible incremental implemention steps on this path:

  (1) minimal condition and expression, compatible with
  a possible future full-blown expression syntax

 \if :variable
 \if not :variable -- maybe \if ! :variable
   ...
 \endif

  (2) add "\else"

  (3) add "\elif ..." (or maybe "\elsif ..."?)

  (4) add greater but limited expressions, compatible with a full blown
  expression syntax (eg \if :var/const  :var/const)

  (5) add full-blown  support for \if, which suggest that
  it would also be available for \set


Does this looks okay, or does it need to be amended?

A few comments:

Given the experience with pgbench and the psql context, I do not think 
that it would really need to go beyond step 2 above, but I agree that I 
may be wrong and it is best to be prepared for that from the start. Given 
the complexity and effort involved with (5), it seems wise to wait for a 
clearer motivation with actual use-cases before going that far.


In the benchmarking context, the point is to test performance for a 
client-server scenario, in which case the client has to do some things, 
thus needs miminal computation capabilities which were available early in 
pgbench (\setrandom, \set with one arithmetic operation...) because they 
were necessary. Probably \if ... would make sense in pgbench, so I'll 
think about it.


In psql interactive context, conditions and expressions do not make sense 
as the user typing the command knows what they want, thus will do 
evaluations in their head to avoid typing stuff...


In psql scripting context, conditions are likely to be about what to do 
with the database, and what I understand of the use-case which started 
this discussion is that it is about versions, settings, available objects, 
typically "if this is already installed, skip this part" or "if version 
beyond YYY, cannot install because of missing features" when installing 
and initializing an application. For this purpose, ISTM that the query is 
necessarily performed server-side, thus the actual need for a full-blown 
client-side expression is limited or void, although as already said being 
prepared is a good thing.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Jim Nasby]

On 12/2/16 9:24 AM, Robert Haas wrote:

On Fri, Dec 2, 2016 at 11:12 AM, Corey Huinker  wrote:

In order for me to understand how high the bar has been set, can you
(Robert/Tom mostly, but I welcome any responses) explain what you mean by
"full-blown expression language"? What constructs must it include, etc?


I mostly mean it should be based on flex and bison, not "this is so
simple I can just hand-roll it".  I don't think it has much chance of
staying that simple.


It might be worth looking at other simplistic languages for guidance. 
bash comes to mind, though there's GPL issues there. csh/tcsh don't have 
those problems, and perhaps some of their grammar could be stolen.


I find it interesting that this is kind of the opposite problem that 
most pl's face: how to fit the database access paradigm into the 
language with the minimal amount of extra effort for users.


http://cvsweb.netbsd.org/bsdweb.cgi/src/bin/csh/?only_with_tag=MAIN
https://github.com/tcsh-org/tcsh
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Fri, Dec 2, 2016 at 11:12 AM, Corey Huinker  wrote:
> In order for me to understand how high the bar has been set, can you
> (Robert/Tom mostly, but I welcome any responses) explain what you mean by
> "full-blown expression language"? What constructs must it include, etc?

I mostly mean it should be based on flex and bison, not "this is so
simple I can just hand-roll it".  I don't think it has much chance of
staying that simple.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Tom Lane]

Corey Huinker  writes:
> In order for me to understand how high the bar has been set, can you
> (Robert/Tom mostly, but I welcome any responses) explain what you mean by
> "full-blown expression language"? What constructs must it include, etc?

My guess is that something comparable to where pgbench is would be a
reasonable target --- not least because I think we should strive to
reduce unnecessary differences between psql and pgbench metalanguages.

I'm not sure that I'm ready to propose that they should share the same
expression engine, but perhaps it's not a totally wacky idea.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

>
>
> The other problem with not thinking about that general case is that
> people will keep on proposing little bitty features that nibble at
> the problem but may or may not be compatible with a general solution.
> To the extent that such patches get accepted, we'll be forced into
> either backwards-compatibility breakage or sub-optimal solutions when
> we do get to the point of wanting a general answer.  I'd much rather
> start with a generalized design and then implement it piece by piece.
>
> (This is more or less the same point as my nearby stand against localized
> hacking of backslash parsing rules.)
>
> regards, tom lane
>


In order for me to understand how high the bar has been set, can you
(Robert/Tom mostly, but I welcome any responses) explain what you mean by
"full-blown expression language"? What constructs must it include, etc?

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Tom Lane]

Robert Haas  writes:
> Given the precedent in pgbench (cf.
> 878fdcb843e087cc1cdeadc987d6ef55202ddd04), I think it requires an
> amazing level of optimism to suppose we won't eventually end up with a
> full-blown expression language here.  I would suggest designing one
> from the beginning and getting it over with.  Even if you manage to
> hold the line and exclude it from whatever gets committed initially,
> somebody's going to propose it 2 years from now.  And again 4 years
> from now.  And again 2 years after that.

The other problem with not thinking about that general case is that
people will keep on proposing little bitty features that nibble at
the problem but may or may not be compatible with a general solution.
To the extent that such patches get accepted, we'll be forced into
either backwards-compatibility breakage or sub-optimal solutions when
we do get to the point of wanting a general answer.  I'd much rather
start with a generalized design and then implement it piece by piece.

(This is more or less the same point as my nearby stand against localized
hacking of backslash parsing rules.)

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Robert Haas]

On Tue, Nov 29, 2016 at 4:43 AM, Pavel Stehule  wrote:
> I prefer the commands instead symbols - the parsing and processing symbols
> should be more complex than it is now. A psql parser is very simple - and
> any complex syntax enforces lot of code.
>
> \if_not

Given the precedent in pgbench (cf.
878fdcb843e087cc1cdeadc987d6ef55202ddd04), I think it requires an
amazing level of optimism to suppose we won't eventually end up with a
full-blown expression language here.  I would suggest designing one
from the beginning and getting it over with.  Even if you manage to
hold the line and exclude it from whatever gets committed initially,
somebody's going to propose it 2 years from now.  And again 4 years
from now.  And again 2 years after that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Andrew Dunstan]

On 11/30/2016 03:47 AM, Fabien COELHO wrote:


Hello Andrew,

I cannot remember a language with elseif* variants, and I find them 
quite ugly, so from an aethetical point of view I would prefer to 
avoid that... On the other hand having an "else if" capability makes 
sense (eg do something slightly different for various versions of 
pg), so that would suggest to stick to a simpler "if" without 
variants, if possible.


FTR I *strongly* disagree with this. (And if you can't remember a 
language that comes with them then you need to get out more. The 
Bourne shell, where it's spelled "elif", and Ada are two obvious 
examples.)


There may be a misunderstanding somewhere.

I'm rather in favor of having "elif/elsif/elseif/..." constructs, 
especially if they can be useful in realistic examples, which is not 
clear yet for psql scripts.


I'm arguing against "if/elif" *variants* in the sense of various 
conditional semantics: e.g. in cpp you have several "if"s (ifdef 
ifndef if), but you do not have all the corresponding "elif"s (elifdef 
elifndef...), there is only one "elif". In cpp "ifdef"/"ifndef" were 
obsoleted by "if" with minimal expression support (#if 
!defined(HAS_SOMETHING) ...) and only this "if" has its "elif".




Oh, I see. Sorry for the misunderstanding.

I agree about generally sticking with one pattern, but I wouldn't want 
to exclude shorthand pieces like


\quit_if cond

which could be more convenient than

\if cond
\quit
\endif

c.f. perl's "foo if bar;" as shorthand for "if (bar) { foo; }"

Still, that might be a refinement to add later on.

cheers

andrew




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello Andrew,

I cannot remember a language with elseif* variants, and I find them quite 
ugly, so from an aethetical point of view I would prefer to avoid that... 
On the other hand having an "else if" capability makes sense (eg do 
something slightly different for various versions of pg), so that would 
suggest to stick to a simpler "if" without variants, if possible.


FTR I *strongly* disagree with this. (And if you can't remember a language 
that comes with them then you need to get out more. The Bourne shell, where 
it's spelled "elif", and Ada are two obvious examples.)


There may be a misunderstanding somewhere.

I'm rather in favor of having "elif/elsif/elseif/..." constructs, 
especially if they can be useful in realistic examples, which is not clear 
yet for psql scripts.


I'm arguing against "if/elif" *variants* in the sense of various 
conditional semantics: e.g. in cpp you have several "if"s (ifdef ifndef 
if), but you do not have all the corresponding "elif"s (elifdef 
elifndef...), there is only one "elif". In cpp "ifdef"/"ifndef" were 
obsoleted by "if" with minimal expression support (#if 
!defined(HAS_SOMETHING) ...) and only this "if" has its "elif".


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello Tom,

But really, \fi is something that could only be loved by a certain 
academic breed of hackers.


Ah ah:-) I'll take that as a compliment:-)

I'd go for \endif, probably. That still doesn't relate well with 
\unless, so +1 for \if, \ifnot, \else, and \endif.


Ok, that is a clear opinion.


I'm not really convinced that we need an \elseif at this point.
It could be added later if people find it compelling, but I'm
having a hard time believing that it's essential.


My experience with cpp is that #elif is quite useful in some cases, 
typically with alternate implementations depending on dependences, to 
avoid this:


 #ifdef HAVE_XXX
   ...
 #else
 #ifdef HAVE_YYY
   ...
 #else
 #ifdef HAVE ZZZ
   ...
 #else
 #error "oops!"
 #endif // HAVE_ZZZ
 #endif // HAVE_YYY
 #endif // HAVE_XXX

I think that some realistic use cases examples for psql would be great...

If the else-if is a common pattern then it should be at least in the 
design, even if not implemented right away. Also ISTM that having else-if 
is not too compatible with multiplying \if* because it leads to pretty 
ugly \el*if* variants which are quite hard to parse and understand, so 
there is an impact on the design.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Tom Lane]

David Fetter  writes:
> On Tue, Nov 29, 2016 at 01:10:06PM +0100, Fabien COELHO wrote:
>> If there is a negative condition syntax, I would slightly prefer \ifnot to
>> \if_not or worse \unless. I would disaprove strongly of \unless because it
>> looses the clear symmetry with a closing \fi.

> I take it \sselnu is right out.

[ splorf... ]  But really, \fi is something that could only be loved
by a certain academic breed of hackers.  I'd go for \endif, probably.
That still doesn't relate well with \unless, so +1 for \if, \ifnot,
\else, and \endif.

I'm not really convinced that we need an \elseif at this point.
It could be added later if people find it compelling, but I'm
having a hard time believing that it's essential.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[David Fetter]

On Tue, Nov 29, 2016 at 01:10:06PM +0100, Fabien COELHO wrote:
> If there is a negative condition syntax, I would slightly prefer \ifnot to
> \if_not or worse \unless. I would disaprove strongly of \unless because it
> looses the clear symmetry with a closing \fi.

I take it \sselnu is right out.

Best,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Andrew Dunstan]

On 11/29/2016 05:25 PM, David Steele wrote:

On 11/29/16 5:08 PM, Andrew Dunstan wrote:

On 11/29/2016 03:07 PM, Fabien COELHO wrote:


I cannot remember a language with elseif* variants, and I find them
quite ugly, so from an aethetical point of view I would prefer to
avoid that... On the other hand having an "else if" capability makes
sense (eg do something slightly different for various versions of pg),
so that would suggest to stick to a simpler "if" without variants, if
possible.

FTR I *strongly* disagree with this. (And if you can't remember a
language that comes with them then you need to get out more. The Bourne
shell, where it's spelled "elif", and Ada are two obvious examples.)

Not to mention PL/pgSQL and Perl.




Indeed.

cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[David Steele]

On 11/29/16 5:08 PM, Andrew Dunstan wrote:
> 
> On 11/29/2016 03:07 PM, Fabien COELHO wrote:
>>
>>
>> I cannot remember a language with elseif* variants, and I find them
>> quite ugly, so from an aethetical point of view I would prefer to
>> avoid that... On the other hand having an "else if" capability makes
>> sense (eg do something slightly different for various versions of pg),
>> so that would suggest to stick to a simpler "if" without variants, if
>> possible.
> 
> FTR I *strongly* disagree with this. (And if you can't remember a
> language that comes with them then you need to get out more. The Bourne
> shell, where it's spelled "elif", and Ada are two obvious examples.)

Not to mention PL/pgSQL and Perl.

-- 
-David
da...@pgmasters.net


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Andrew Dunstan]

On 11/29/2016 03:07 PM, Fabien COELHO wrote:



I cannot remember a language with elseif* variants, and I find them 
quite ugly, so from an aethetical point of view I would prefer to 
avoid that... On the other hand having an "else if" capability makes 
sense (eg do something slightly different for various versions of pg), 
so that would suggest to stick to a simpler "if" without variants, if 
possible.






FTR I *strongly* disagree with this. (And if you can't remember a 
language that comes with them then you need to get out more. The Bourne 
shell, where it's spelled "elif", and Ada are two obvious examples.)


cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

>
>
> Hmmm. Would you have an example use case that could not be done simply
> with the previous ifs? cpp did that end ended up with a single if in the
> end.


I think this is what you're asking for...

$ cat not_defined.sql
select :'foo';

$ psql postgres --no-psqlrc -f not_defined.sql --set foo=bar
 ?column?
--
 bar
(1 row)

$ psql postgres --no-psqlrc -f not_defined.sql
psql:not_defined.sql:3: ERROR:  syntax error at or near ":"
LINE 1: select :'foo';
   ^


Now, if we instead added a way for psql to test whether or not a psql var
was defined and set that boolean as ANOTHER variable, then we could avoid
\ifdef and \ifndef.


>
> For consistency, the possible sources of inspiration for a syntax with an
> explicit end marker are:
>
>  - PL/pgSQL: if / then / elsif / else / endif
>  - cpp: if / elif / else / endif
>  - sh: if / then / elif / else / fi
>
> Now "then" is useless in a line oriented syntax, for which the closest
> example above is cpp, which does not have it. I think that we should stick
> to one of these.
>
> I like the shell syntax (without then), but given the line orientation
> maybe it would make sense to use the cpp version, close to what you are
> proposing.
>

I think we should use pl/pgsql as our inspiration, though there's no need
for the "then" because psql commands end the linewhich makes it
identical to the C++ version.
But if we can get this thing done, I really don't care which we use.


> I cannot remember a language with elseif* variants, and I find them quite
> ugly, so from an aethetical point of view I would prefer to avoid that...
> On the other hand having an "else if" capability makes sense (eg do
> something slightly different for various versions of pg), so that would
> suggest to stick to a simpler "if" without variants, if possible.
>

We need to keep things easy to parse. Earlier someone said no psql command
should ever have more than 2 parameters, and generally only one. Increasing
the number of commands allows us to avoid multi-parameter commands. So it's
a trade-off, we have more, simpler commands, or fewer, more complicated
ones.

\if [not] [defined] []
\elsif [not] [defined] []


is problematic if string is ever "not" or "defined". If someone can show me
a way around that, I'm game.


>
> Then seems like we need an if-state-stack to handle nesting. [...]
>>
>
> Yes, a stack or recursion is needed for implementing nesting.
>
> States: None, If-Then, If-Skip, Else-Then, Else-Skip.
>>
>
> With an "else if" construct you probably need some more states: You have
> to know whether you already executed a block in which case a subsequent
> condition is ignored, so there is a state "skip all to end" needed.
>

Right, we'd have to check every level of the stack for a skip-state, not a
big deal.

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello Corey,


I agree that the boolean tests available should be *very* simple, and all
of the weight of complex calculation should be put in SQL, like we do with
\gset


Yes.


I propose those be:

\if STRING :  true if STRING evaluates to true via ParseVariableBool, empty
means false


Yep.


\ifnot STRING: true if STRING evaluates to false via ParseVariableBool,
empty means false


I'd like other opinions about having unique \if and an not operator, vs 
multiplying \if* and possibly \elif* keywords.



\ifdef psql_var: true if psql_var is defined
\ifndef psql_var: true if psql_var is not defined, helpful for when --set
var=val was omitted and should be defaulted


Hmmm. Would you have an example use case that could not be done simply 
with the previous ifs? cpp did that end ended up with a single if in the 
end.



A full compliment of \elseif \elseifnot \elseifdef \elseifndef, each
matching the corresponding \if* above. I'm ok with these being optional in
the first revision.



\else   - technically we could leave this out as well
\endif


For consistency, the possible sources of inspiration for a syntax with an 
explicit end marker are:


 - PL/pgSQL: if / then / elsif / else / endif
 - cpp: if / elif / else / endif
 - sh: if / then / elif / else / fi

Now "then" is useless in a line oriented syntax, for which the closest 
example above is cpp, which does not have it. I think that we should stick 
to one of these.


I like the shell syntax (without then), but given the line orientation 
maybe it would make sense to use the cpp version, close to what you are 
proposing.


I cannot remember a language with elseif* variants, and I find them quite 
ugly, so from an aethetical point of view I would prefer to avoid that... 
On the other hand having an "else if" capability makes sense (eg do 
something slightly different for various versions of pg), so that would 
suggest to stick to a simpler "if" without variants, if possible.



Then seems like we need an if-state-stack to handle nesting. [...]


Yes, a stack or recursion is needed for implementing nesting.


States: None, If-Then, If-Skip, Else-Then, Else-Skip.


With an "else if" construct you probably need some more states: You have 
to know whether you already executed a block in which case a subsequent 
condition is ignored, so there is a state "skip all to end" needed.



Does that seem work-able?


Sure. I think the priority is to try to agree about a syntax, the 
implementation is a detail.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

>
> My 0,02 €, which is just a personal opinion:
>
> I think that handling manually "!/not" would be worth the effort rather
> than having two commands, especially if the boolean expression syntax may
> be extended some day and the negative if would become obsolete.
>
> If there is a negative condition syntax, I would slightly prefer \ifnot to
> \if_not or worse \unless. I would disaprove strongly of \unless because it
> looses the clear symmetry with a closing \fi.
>
> --
> Fabien.


Pavel had previously written this patch
http://ftp.pgpi.org/pub/databases/postgresql/projects/pgFoundry/epsql/epsql/epsql-8.5-develop/psql-enhanced-macros.diff
which I leave here as a point of reference.

Obviously, there's a lot more in there than we'd need, and it's back quite
a few versions.

I agree that the boolean tests available should be *very* simple, and all
of the weight of complex calculation should be put in SQL, like we do with
\gset

I propose those be:

\if STRING :  true if STRING evaluates to true via ParseVariableBool, empty
means false
\ifnot STRING: true if STRING evaluates to false via ParseVariableBool,
empty means false
\ifdef psql_var: true if psql_var is defined
\ifndef psql_var: true if psql_var is not defined, helpful for when --set
var=val was omitted and should be defaulted

A full compliment of \elseif \elseifnot \elseifdef \elseifndef, each
matching the corresponding \if* above. I'm ok with these being optional in
the first revision.

\else   - technically we could leave this out as well
\endif


Then seems like we need an if-state-stack to handle nesting. At any given
point, the mode could be:

1. None

psql currently isn't in an if-then structure, no non-conditional statements
are skipped. any conditional commands other than \if* are an error.
For that matter, the script can always add a new level to the stack with an
\if* command.

2. If-Then

psql is currently executing statements in an \if* branch that evaluated to
true. valid conditional commands are: \if*, \else* \endif

3. If-Skip

psql is currently in a block that evaluated to false, and will still parse
commands for psql-correctness, but will skip them until it encounters an
\endif or \else*

4. Else-Then

Just like If-Then, but encountering an \else* command would be an error.

5. Else-Skip

Just like If-Skip, but encountering an \else* command would be an error.



The only data structure we'd need is the stack of enums listed above. Any
commands would check against the stack-state before executing, but would
otherwise be parsed and checked as they are now. The new \commands would
manipulate that stack.

Does that seem work-able?

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello Pavel,


Now, the psql statements are designed do nothing in syntax error. I am not
sure about be more strict in this case. I see strong advantages - but it
can be little bit different than current behave.


Indeed, an error on a conditional construct should stop the script, which 
is slightly different that "go on whatever", which is okay in the 
interactive mode.


I do not see that as an issue, just as features which are more interactive 
vs script oriented and behave accordingly: typing a \if in interactive 
does not makes much sense, because you have tested something is there, so 
you know the answer and can act directly, so there is no point using a 
condition.



 \if ! :has_xxx_feature


I prefer the commands instead symbols - the parsing and processing 
symbols should be more complex than it is now. A psql parser is very 
simple - and any complex syntax enforces lot of code. \if_not


My 0,02 €, which is just a personal opinion:

I think that handling manually "!/not" would be worth the effort rather 
than having two commands, especially if the boolean expression syntax may 
be extended some day and the negative if would become obsolete.


If there is a negative condition syntax, I would slightly prefer \ifnot to 
\if_not or worse \unless. I would disaprove strongly of \unless because it 
looses the clear symmetry with a closing \fi.


--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-11-29 8:44 GMT+01:00 Fabien COELHO :

>
> Hello,
>
> I think it's really time we seriously considered adding some flow
>>> control logic, though.
>>>
>>
>> Yeah, maybe.  I'd be interested to see a fully worked out proposal
>> for that.
>>
>
> I agree that designing a fuller proposal before including individual parts
> would be great and result in a more consistent result.
>
> In order to bootstrap the discussion, I suggest the following:
>
>  - boolexpr is a simple "boolean" (t, non 0 int, non empty string.. as
>proposed by Corey and Pavel) or !/not boolexp ; it could be extended if
>necessary, but I would try to avoid that, as
>

Now, the psql statements are designed do nothing in syntax error. I am not
sure about be more strict in this case. I see strong advantages - but it
can be little bit different than current behave.



>
>  - actual more complex expressions could be left to the server through SQL
>which simplifies the client a lot by avoiding an expression language
>altogether
>
>  - then having a conditional block is very versatile and can be adapted to
>many use cases... maybe all
>
>  - \quit CODE, or I would prefer \exit CODE, could be used to exit while
>controlling the status
>
> It could look like (although I do not like gset in this context, but
> anyway):
>
>  SELECT ... AS has_foo_extension \gset
>  SELECT ... AS has_bla_extension \gset
>  \if :has_foo_extension
>...
>  \elif :has_bla_extension
>...
>  \else -- no foo nor bla extension
>\echo please install foo or bla extension
>\exit 1
>  \fi -- extension
>  ...
>  SELECT ... AS has_xxx_feature \gset
>  \if ! :has_xxx_feature
>

I prefer the commands instead symbols - the parsing and processing symbols
should be more complex than it is now. A psql parser is very simple - and
any complex syntax enforces lot of code.

\if_not

Regards

Pavel


>   \echo "feature xxx is needed, aborting"
>   \exit 2
>  \fi
>  ...
>
> --
> Fabien
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello,


I think it's really time we seriously considered adding some flow
control logic, though.


Yeah, maybe.  I'd be interested to see a fully worked out proposal
for that.


I agree that designing a fuller proposal before including individual parts 
would be great and result in a more consistent result.


In order to bootstrap the discussion, I suggest the following:

 - boolexpr is a simple "boolean" (t, non 0 int, non empty string.. as
   proposed by Corey and Pavel) or !/not boolexp ; it could be extended if
   necessary, but I would try to avoid that, as

 - actual more complex expressions could be left to the server through SQL
   which simplifies the client a lot by avoiding an expression language
   altogether

 - then having a conditional block is very versatile and can be adapted to
   many use cases... maybe all

 - \quit CODE, or I would prefer \exit CODE, could be used to exit while
   controlling the status

It could look like (although I do not like gset in this context, but 
anyway):


 SELECT ... AS has_foo_extension \gset
 SELECT ... AS has_bla_extension \gset
 \if :has_foo_extension
   ...
 \elif :has_bla_extension
   ...
 \else -- no foo nor bla extension
   \echo please install foo or bla extension
   \exit 1
 \fi -- extension
 ...
 SELECT ... AS has_xxx_feature \gset
 \if ! :has_xxx_feature
  \echo "feature xxx is needed, aborting"
  \exit 2
 \fi
 ...

--
Fabien


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Tom Lane]

Andrew Dunstan  writes:
> On 11/28/2016 04:07 PM, Tom Lane wrote:
>> ... We've generally refrained from inventing any control flow
>> metacommands for psql

> I think it's really time we seriously considered adding some flow 
> control logic, though.

Yeah, maybe.  I'd be interested to see a fully worked out proposal
for that.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Andrew Dunstan]

On 11/28/2016 04:07 PM, Tom Lane wrote:

... We've generally refrained from inventing any control flow
metacommands for psql



I think it's really time we seriously considered adding some flow 
control logic, though. I'm mildly tired of either jumping through hoops 
to get around the lack or having to switch to using some other thing 
that has the required logic mechanism (hello Perl).


cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

>
>
> As far as the original problem goes, I wonder whether what you really
>> want isn't a \quit command that lets you specify psql's exit code.
>>
>
>
The ability to specify an exit code was part of the brainstorming, yes. But
with it was the ability to conditionally quit.


> Actually, I'm seeing this as basically an assertion capability and maybe
> should be named as such
>
> \assert_is
> \assert_isnot ​
>

That came up too! I see real value in the ability to test for error
conditions. I just had a more immediate need for a non-error exit condition.

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[David G. Johnston]

On Mon, Nov 28, 2016 at 2:07 PM, Tom Lane  wrote:

> Corey Huinker  writes:
> > On Mon, Nov 28, 2016 at 2:03 PM, Fabien COELHO 
> wrote:
>
There's no reason to assume a-priori that this patch creates either naming
> conventions or semantics (e.g. what is suitable as a boolean expression)
> that we'd be happy with in a larger context.
>
>
Would we be happy borrowing the definition of FOUND from pl/pgsql?​

As far as the original problem goes, I wonder whether what you really
> want isn't a \quit command that lets you specify psql's exit code.
>

Actually, I'm seeing this as basically an assertion capability and maybe
should be named as such

\assert_is
\assert_isnot ​

​David J.
​

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Tom Lane]

Corey Huinker  writes:
> On Mon, Nov 28, 2016 at 2:03 PM, Fabien COELHO  wrote:
>> I'm wondering if an simplistic interpreted \if \elsif/\else \fi would make
>> more sense:

> The problem is that \if \elsif \else \fi is anything but simplistic, and
> would be a vastly bigger change. Detecting nested if-thens, detecting
> un-matched if-thens, etc.

Yes, but...  We've generally refrained from inventing any control flow
metacommands for psql, and TBH this does *not* seem like the place to
start.  If we take this patch we are very likely to find that it doesn't
fit in at all whenever someone does show up with a proposal for control
flow features.

If we had an agreed-on sketch for what that feature set would look like,
I wouldn't necessarily object to implementing commands like these first.
But as it stands I think we'd be painting ourselves into a corner.
There's no reason to assume a-priori that this patch creates either naming
conventions or semantics (e.g. what is suitable as a boolean expression)
that we'd be happy with in a larger context.

As far as the original problem goes, I wonder whether what you really
want isn't a \quit command that lets you specify psql's exit code.
I'm not quite seeing how this proposal advances the problem of
communicating between psql and shell portions of a script; but I can
see how returning 0 (ok) vs 1 (error) vs 2 (some special case) would
help with that.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

On Mon, Nov 28, 2016 at 2:03 PM, Fabien COELHO  wrote:

>
> Hello Corey,
>
> This patch adds two very simple psql commands: \quit_if and \quit_unless.
>>
>
> A few comments about the feature design:
>
> I'm unsure about the name, esp with '_'. There are some \lo_* commands,
> but others rely on pasted words (\crosstabview, \errverbose, ...).
>

I'm completely flexible with regard to the names.


> I'm wondering if an simplistic interpreted \if \elsif/\else \fi would make
> more sense:
>

The problem is that \if \elsif \else \fi is anything but simplistic, and
would be a vastly bigger change. Detecting nested if-thens, detecting
un-matched if-thens, etc.


> Quitting seems a little bit definitive, and means that if I have some
> alternatives then I have to have something that relaunch another script
> outside...
>

> When \includes are process, does \quit stop the include or the full
> script. I'm afraid it is the script.
>

It behaves exactly as if the script contained \quit at that location.


> Now probably an \if... would have also some drawbacks, but ISTM that there
> could be less of them.
>

It'd be nice, but I'm trying to keep things simple.


>
> There is no test provided with the patch.


Indeed, but testing such a feature is hard within our current test harness.
I welcome suggestions for how to convert the example script in my first
email to tests.

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Pavel Stehule]

2016-11-28 20:03 GMT+01:00 Fabien COELHO :

>
> Hello Corey,
>
> This patch adds two very simple psql commands: \quit_if and \quit_unless.
>>
>
> A few comments about the feature design:
>
> I'm unsure about the name, esp with '_'. There are some \lo_* commands,
> but others rely on pasted words (\crosstabview, \errverbose, ...).
>

There are not any other conditional statements - so using "_" can be better


>
> I'm wondering if an simplistic interpreted \if \elsif/\else \fi would make
> more sense:
>

The \if \ese \elseif is not in contradiction with \quit_if - \if is more
generic, \quit_if is simple for implementation, one liner, and best readable

Pavel


>
> Quitting seems a little bit definitive, and means that if I have some
> alternatives then I have to have something that relaunch another script
> outside...
>
> When \includes are process, does \quit stop the include or the full
> script. I'm afraid it is the script.
>
> Now probably an \if... would have also some drawbacks, but ISTM that there
> could be less of them.
>
> There is no test provided with the patch.
>
> --
> Fabien.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] PSQL commands: \quit_if, \quit_unless

[Fabien COELHO]

Hello Corey,


This patch adds two very simple psql commands: \quit_if and \quit_unless.


A few comments about the feature design:

I'm unsure about the name, esp with '_'. There are some \lo_* commands, 
but others rely on pasted words (\crosstabview, \errverbose, ...).


I'm wondering if an simplistic interpreted \if \elsif/\else \fi would make 
more sense:


Quitting seems a little bit definitive, and means that if I have some 
alternatives then I have to have something that relaunch another script 
outside...


When \includes are process, does \quit stop the include or the full 
script. I'm afraid it is the script.


Now probably an \if... would have also some drawbacks, but ISTM that there 
could be less of them.


There is no test provided with the patch.

--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PSQL commands: \quit_if, \quit_unless

[Corey Huinker]

This patch adds two very simple psql commands: \quit_if and \quit_unless.

Each takes an optional string parameter and evaluates it for truthiness via
ParseVariableBool().

If a true-ish value is passed to \quit_if, psql will behave as if the user
had input \quit.

\quit_unless will do nothing if the value given was true-ish, and will
\quit in any other circumstances.

Examples below show the behavior:

$ psql postgres
psql (10devel)
Type "help" for help.

# \quit_if
# \quit_unless
$ psql postgres
psql (10devel)
Type "help" for help.

# \quit_if f
# \quit_if 0
# \quit_if false
# \quit_if 2
unrecognized value "2" for "\quit_if"; assuming "on"
$ psql postgres
psql (10devel)
Type "help" for help.

# \quit_unless 2
unrecognized value "2" for "\quit_unless"; assuming "on"
# \quit_unless f
$


The need for this patch has arisen from several scripts I've written
recently that were about 97% psql and 3% bash or similar glue language. In
those scripts, there's often a test that says "there is no work to do here,
and that is not an error". I could engineer an optional SELECT 1/0 to
generate an error, but then I have to make the bash script determine
whether that was an error-error or a no-everything-is-ok-error. I also
don't want to wrap hundreds of lines of SQL inside python docstrings (thus
losing syntax highlighting, etc) just to handle one if/then.

Many thanks to Pavel Stehule for brainstorming this one with me.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 2410bee..a9b0732 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2616,6 +2616,23 @@ lo_import 152801
 
   
 
+  
+\quit_if string 
+
+
+Quits the psql program if the string 
evaluates to true.
+
+
+  
+
+  
+\quit_unless string 
+
+
+Quits the psql program unless the string 
evaluates to true.
+
+
+  
 
   
 \qecho text [ ... ] 
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a9a2fdb..7b46fbd 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1251,6 +1251,29 @@ exec_command(const char *cmd,
else if (strcmp(cmd, "q") == 0 || strcmp(cmd, "quit") == 0)
status = PSQL_CMD_TERMINATE;
 
+   /* \quit_if */
+   else if (strcmp(cmd, "quit_if") == 0)
+   {
+   char   *opt = psql_scan_slash_option(scan_state,
+   
 OT_NORMAL, NULL, false);
+
+   if (opt)
+   if (ParseVariableBool(opt, "\\quit_if"))
+   status = PSQL_CMD_TERMINATE;
+   }
+
+   /* \quit_unless */
+   else if (strcmp(cmd, "quit_unless") == 0)
+   {
+   char   *opt = psql_scan_slash_option(scan_state,
+   
 OT_NORMAL, NULL, false);
+
+   if (!opt)
+   status = PSQL_CMD_TERMINATE;
+   else if (! ParseVariableBool(opt, "\\quit_unless"))
+   status = PSQL_CMD_TERMINATE;
+   }
+
/* reset(clear) the buffer */
else if (strcmp(cmd, "r") == 0 || strcmp(cmd, "reset") == 0)
{
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index a69c4dd..db29650 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -177,6 +177,8 @@ slashUsage(unsigned short int pager)
fprintf(output, _("  \\gexec execute query, then 
execute each value in its result\n"));
fprintf(output, _("  \\gset [PREFIX] execute query and store 
results in psql variables\n"));
fprintf(output, _("  \\q quit psql\n"));
+   fprintf(output, _("  \\quit_if [STRING]  quit psql if STRING 
evaluates to true\n"));
+   fprintf(output, _("  \\quit_unless [STRING]  quit psql if STRING does 
not evaluate to true\n"));
fprintf(output, _("  \\crosstabview [COLUMNS] execute query and display 
results in crosstab\n"));
fprintf(output, _("  \\watch [SEC]   execute query every SEC 
seconds\n"));
fprintf(output, "\n");

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers