Thread: Desirability of client-side expressions in psql?

Desirability of client-side expressions in psql?

From
Fabien COELHO
Date:
Hello devs,

This is a discussion without actual patch intended for pg12, to be added 
to CF 2018-09. The expected end result is either "returned with feedback", 
meaning proceed to send some big patch(es), or "rejected", meaning the 
project does not want this, no point in submitting something.

Client "psql" has an "\if" which can test a boolean value and has 
":"-prefixed variables, including special presets such as ":VERSION_NUM" 
and ":SERVER_VERSION_NUM".

The features are already usable because one can do server-side expressions 
(if connected), which is a little cumbersome and ugly but nevertheless 
functional, eg:

   SELECT :VERSION_NUM = :SERVER_VERSION_NUM AS "same_version" \gset
   \if :same_version
     ...

However, when the "\if" patch was discussed, there was the underlying idea 
to extend psql so as to add client-side expression. That would allow 
things like:

   \let i <some arithmetic or logical expression...>
   \if :VERSION_NUM = :SERVER_VERSION_NUM
     ...

Before eventually starting on this subject with a loose objective of 
targeting 12.0, I would like to ascertain, especially from committers, but 
also from others, that:

(1) the objective is desirable (i.e. avoid ending with "we do not want
     this feature on principle, the cost-benefit balance is not good
     enough").

(2) maybe have a feedback on the proposed changes (not necessarily
     distinct patches, opinions are welcome), which would be to:
     (a) extend pgbench expressions so that they can handle what
         psql can do (eg variable-exists test which are available in psql)
     (b) maybe do some preliminary refactoring (eg create
         "pgbench/expression.c", "pgbench/variable.c")
     (c) move the pgbench expression engine to fe-utils
         (lexer, parser, execution...),
     (d) do some renaming (eg some "feex" prefix for "front-end
         expressions" to the various functions & types?),
     (e) abstract pgbench and psql variables so that they can be used
         transparently by expressions (i.e. some API alignment)
     (f) connect the engine to "psql"
     (g) create a shared documentation about these expressions,
         referenced from both psql and pgbench documentations.
     (h) provide non-regression tests on psql side as well.

The overall transformation would be quite large (about 2000 lines moved 
around) but "quite" simple (it is moving an existing, working and tested 
feature to allow using it elsewhere), not a lot of new code per se.

-- 
Fabien.


Re: Desirability of client-side expressions in psql?

From
Pavel Stehule
Date:


2018-03-03 11:35 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello devs,

This is a discussion without actual patch intended for pg12, to be added to CF 2018-09. The expected end result is either "returned with feedback", meaning proceed to send some big patch(es), or "rejected", meaning the project does not want this, no point in submitting something.

Client "psql" has an "\if" which can test a boolean value and has ":"-prefixed variables, including special presets such as ":VERSION_NUM" and ":SERVER_VERSION_NUM".

The features are already usable because one can do server-side expressions (if connected), which is a little cumbersome and ugly but nevertheless functional, eg:

  SELECT :VERSION_NUM = :SERVER_VERSION_NUM AS "same_version" \gset
  \if :same_version
    ...

However, when the "\if" patch was discussed, there was the underlying idea to extend psql so as to add client-side expression. That would allow things like:

  \let i <some arithmetic or logical expression...>
  \if :VERSION_NUM = :SERVER_VERSION_NUM
    ...

Before eventually starting on this subject with a loose objective of targeting 12.0, I would like to ascertain, especially from committers, but also from others, that:

(1) the objective is desirable (i.e. avoid ending with "we do not want
    this feature on principle, the cost-benefit balance is not good
    enough").

(2) maybe have a feedback on the proposed changes (not necessarily
    distinct patches, opinions are welcome), which would be to:
    (a) extend pgbench expressions so that they can handle what
        psql can do (eg variable-exists test which are available in psql)
    (b) maybe do some preliminary refactoring (eg create
        "pgbench/expression.c", "pgbench/variable.c")
    (c) move the pgbench expression engine to fe-utils
        (lexer, parser, execution...),
    (d) do some renaming (eg some "feex" prefix for "front-end
        expressions" to the various functions & types?),
    (e) abstract pgbench and psql variables so that they can be used
        transparently by expressions (i.e. some API alignment)
    (f) connect the engine to "psql"
    (g) create a shared documentation about these expressions,
        referenced from both psql and pgbench documentations.
    (h) provide non-regression tests on psql side as well.

The overall transformation would be quite large (about 2000 lines moved around) but "quite" simple (it is moving an existing, working and tested feature to allow using it elsewhere), not a lot of new code per se.

I understand the request of some simple expression evaluation for pgbench and conditional execution for psql. Because syntax is same, then share code is really good idea. Lexer, parser, variable processing should be moved to fe-utils, other implemented on place. We don't need all commands of pgbench in psql, and we don't need interactive loop and psql commands in pgbench. But the syntax of input commands is same on both environments, and all on this level can be shared via some library. Some shared commands can be implemented in other library, and called from final positions.

Using some simple expressions evaluations is much more simple, then integration some full functional VM like lua, Python - and still good enough.

I have not the feedback from psql users about missing strong integrated language. What is current weak place of psql is tab complete and readline multiline editor. The pgcli is better - and second hand, everything else is better in psql.

Regards

Pavel

--
Fabien.


Re: Desirability of client-side expressions in psql?

From
Pavel Stehule
Date:
Hi

2018-03-03 13:32 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:


2018-03-03 11:35 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello devs,

This is a discussion without actual patch intended for pg12, to be added to CF 2018-09. The expected end result is either "returned with feedback", meaning proceed to send some big patch(es), or "rejected", meaning the project does not want this, no point in submitting something.

Client "psql" has an "\if" which can test a boolean value and has ":"-prefixed variables, including special presets such as ":VERSION_NUM" and ":SERVER_VERSION_NUM".

The features are already usable because one can do server-side expressions (if connected), which is a little cumbersome and ugly but nevertheless functional, eg:

  SELECT :VERSION_NUM = :SERVER_VERSION_NUM AS "same_version" \gset
  \if :same_version
    ...

However, when the "\if" patch was discussed, there was the underlying idea to extend psql so as to add client-side expression. That would allow things like:

  \let i <some arithmetic or logical expression...>
  \if :VERSION_NUM = :SERVER_VERSION_NUM
    ...

Before eventually starting on this subject with a loose objective of targeting 12.0, I would like to ascertain, especially from committers, but also from others, that:

(1) the objective is desirable (i.e. avoid ending with "we do not want
    this feature on principle, the cost-benefit balance is not good
    enough").

(2) maybe have a feedback on the proposed changes (not necessarily
    distinct patches, opinions are welcome), which would be to:
    (a) extend pgbench expressions so that they can handle what
        psql can do (eg variable-exists test which are available in psql)
    (b) maybe do some preliminary refactoring (eg create
        "pgbench/expression.c", "pgbench/variable.c")
    (c) move the pgbench expression engine to fe-utils
        (lexer, parser, execution...),
    (d) do some renaming (eg some "feex" prefix for "front-end
        expressions" to the various functions & types?),
    (e) abstract pgbench and psql variables so that they can be used
        transparently by expressions (i.e. some API alignment)
    (f) connect the engine to "psql"
    (g) create a shared documentation about these expressions,
        referenced from both psql and pgbench documentations.
    (h) provide non-regression tests on psql side as well.

The overall transformation would be quite large (about 2000 lines moved around) but "quite" simple (it is moving an existing, working and tested feature to allow using it elsewhere), not a lot of new code per se.

I understand the request of some simple expression evaluation for pgbench and conditional execution for psql. Because syntax is same, then share code is really good idea. Lexer, parser, variable processing should be moved to fe-utils, other implemented on place. We don't need all commands of pgbench in psql, and we don't need interactive loop and psql commands in pgbench. But the syntax of input commands is same on both environments, and all on this level can be shared via some library. Some shared commands can be implemented in other library, and called from final positions.

Using some simple expressions evaluations is much more simple, then integration some full functional VM like lua, Python - and still good enough.

I have not the feedback from psql users about missing strong integrated language. What is current weak place of psql is tab complete and readline multiline editor. The pgcli is better - and second hand, everything else is better in psql.

please, can you port expression evaluation from pgbench to psql? I miss a patch.

I am sure so psql expression evaluation has strong benefit - mainly for expressions like

\if SERVER_VERSION_NUM >= xxxx


\endif

Regards

Pavel

Regards

Pavel


Regards

Pavel

--
Fabien.



Re: Desirability of client-side expressions in psql?

From
Fabien COELHO
Date:
Hello Pavel,

>>> This is a discussion without actual patch intended for pg12, to be added
>>> to CF 2018-09. The expected end result is either "returned with feedback",
>>> meaning proceed to send some big patch(es), or "rejected", meaning the
>>> project does not want this, no point in submitting something.
>
> please, can you port expression evaluation from pgbench to psql? I miss a
> patch.

Indeed, I have not done it yet:-)

This is a pretty large series of patches, which would take a significant 
time for developing (me), rewiewing (not me) and committing (with overload 
committers). I would not like to end in the "we do not want this feature 
at all" state *after* the time has been spent, hence this pre-patch call 
for discussion about the feature itself, before diving into coding.

I take note that you are interested in actually having this feature.

> I am sure so psql expression evaluation has strong benefit - mainly for
> expressions like
>
> \if SERVER_VERSION_NUM >= xxxx
>  ...
> \endif

Note that this can already be done by relying on server-side expressions:

  SELECT :SERVER_VERSION_NUM >= 110000 AS "version_11_plus" \gset
  \if :version_11_plus
    ...
  \endif

Not very elegant, but functional. I'm not sure there is a compelling 
reason to have this feature beyond elegance.

-- 
Fabien.


Re: Desirability of client-side expressions in psql?

From
Pavel Stehule
Date:


2018-06-24 16:13 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

This is a discussion without actual patch intended for pg12, to be added
to CF 2018-09. The expected end result is either "returned with feedback",
meaning proceed to send some big patch(es), or "rejected", meaning the
project does not want this, no point in submitting something.

please, can you port expression evaluation from pgbench to psql? I miss a
patch.

Indeed, I have not done it yet:-)

This is a pretty large series of patches, which would take a significant time for developing (me), rewiewing (not me) and committing (with overload committers). I would not like to end in the "we do not want this feature at all" state *after* the time has been spent, hence this pre-patch call for discussion about the feature itself, before diving into coding.

I take note that you are interested in actually having this feature.

I am sure so psql expression evaluation has strong benefit - mainly for
expressions like

\if SERVER_VERSION_NUM >= xxxx
 ...
\endif

Note that this can already be done by relying on server-side expressions:

 SELECT :SERVER_VERSION_NUM >= 110000 AS "version_11_plus" \gset
 \if :version_11_plus
   ...
 \endif

Not very elegant, but functional. I'm not sure there is a compelling reason to have this feature beyond elegance.

not elegant, not readable, and not user friendly.

the possibility of simple expression evaluation is just practical.

Regards

Pavel


--
Fabien.

Re: Desirability of client-side expressions in psql?

From
Fabien COELHO
Date:
>> Note that this can already be done by relying on server-side expressions:
>>
>>  SELECT :SERVER_VERSION_NUM >= 110000 AS "version_11_plus" \gset
>>  \if :version_11_plus
>>    ...
>>  \endif
>>
>> Not very elegant, but functional. I'm not sure there is a compelling
>> reason to have this feature beyond elegance.
>
> not elegant, not readable, and not user friendly.

Sure, I can only agree.

> the possibility of simple expression evaluation is just practical.

Ok. Good.

I do not mind spending some time for elegance, but I mind spending time 
for nothing.

I hope some committers will also express their views about the feature.

-- 
Fabien.


Re: Desirability of client-side expressions in psql?

From
Ashutosh Bapat
Date:
On Mon, Jun 25, 2018 at 1:29 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
>
> I do not mind spending some time for elegance, but I mind spending time for
> nothing.
>
> I hope some committers will also express their views about the feature.
>

There's a commitfest entry for this but no patch. So I am marking this
as RWF. The discussion here can continue. Please submit a patch (may
be a PoC) to the commitfest whenever there's one.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


Re: Desirability of client-side expressions in psql?

From
Andrew Dunstan
Date:
On Tue, Jul 3, 2018 at 8:43 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
> On Mon, Jun 25, 2018 at 1:29 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
>>
>> I do not mind spending some time for elegance, but I mind spending time for
>> nothing.
>>
>> I hope some committers will also express their views about the feature.
>>
>
> There's a commitfest entry for this but no patch. So I am marking this
> as RWF. The discussion here can continue. Please submit a patch (may
> be a PoC) to the commitfest whenever there's one.
>


I set it to Rejected. This seems like a misuse of the CF process,
which is about discussing actual patches.

cheers

andrew

-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: Desirability of client-side expressions in psql?

From
Fabien COELHO
Date:
Hello Andrew,

> I set it to Rejected. This seems like a misuse of the CF process,
> which is about discussing actual patches.

Somehow.

As I have not received feedback from committers about the desirability of 
the feature, I interpret that as "the feature is not desirable", and I 
will not develop it, because of the probability that this would be time 
down the drain.

-- 
Fabien.


Re: Desirability of client-side expressions in psql?

From
Stephen Frost
Date:
Greeting Fabien,

* Fabien COELHO (coelho@cri.ensmp.fr) wrote:
> >I set it to Rejected. This seems like a misuse of the CF process,
> >which is about discussing actual patches.
>
> Somehow.

So, I tend to agree w/ Andrew that while this is a good topic to have on
-hackers, it shouldn't be a CF entry.  I wouldn't draw any conclusions
from Andrew closing it out as "not appropriate for CF".

> As I have not received feedback from committers about the desirability of
> the feature, I interpret that as "the feature is not desirable", and I will
> not develop it, because of the probability that this would be time down the
> drain.

Personally, I'm definitely in favor of having a lot more flexibility and
capability in psql, that's an area which I think we don't focus on
nearly enough.  Having to fight with bash or another language to make
calls to psql to get things done is downright annoying.

So, +1 from me on the overall idea.  The challenge here will almost
certainly be in the details.  I do like the proposal you have of
building out a common set of capabilities which are shared between psql
and pgbench.

The other big challenge here is figuring out how to distinguish between
SQL which should be sent to the server and something which needs to be
client-side processed.  I've never liked the ':var' approach and it
really sucks when you want to combine that variable with something else,
but it's what we've got and therefore has history behind it.  If you can
find a way to improve on that without breaking existing code, that'd be
fantastic.  If not, definitely try to minimize the impact.

Thanks!

Stephen

Attachment

Re: Desirability of client-side expressions in psql?

From
Fabien COELHO
Date:
Hello Stephen,

> [...] So, I tend to agree w/ Andrew that while this is a good topic to 
> have on -hackers, it shouldn't be a CF entry.  I wouldn't draw any 
> conclusions from Andrew closing it out as "not appropriate for CF".

Sure. As I had no committer feedback on the discussion for 3 months, I 
tried this as an ineffective way to get some. It did not work up to now.

>> As I have not received feedback from committers about the desirability of
>> the feature, I interpret that as "the feature is not desirable", and I will
>> not develop it, because of the probability that this would be time down the
>> drain.
>
> Personally, I'm definitely in favor of having a lot more flexibility and
> capability in psql, that's an area which I think we don't focus on
> nearly enough.  Having to fight with bash or another language to make
> calls to psql to get things done is downright annoying.
>
> So, +1 from me on the overall idea.

Good, that is one committer opinion, an infinite improvement over the 
previous status:-)

> The challenge here will almost certainly be in the details.

Yep. I'm fine with "your code is not good and creates problems so it is 
rejected". I'm trying to avoid "your code was a loss from the start, 
whatever you did, because we do not want such a feature".

> I do like the proposal you have of building out a common set of 
> capabilities which are shared between psql and pgbench.

Good.

> The other big challenge here is figuring out how to distinguish between 
> SQL which should be sent to the server and something which needs to be 
> client-side processed.

The current great idea is to use backslash commands to define existing 
variables:

   psql> \let i  1 + 2 * 3
   psql> SELECT :i ;
   psql> \if :i >= 5
   psql>   ...
   psql> \endif

> I've never liked the ':var' approach and it really sucks when you want 
> to combine that variable with something else, but it's what we've got 
> and therefore has history behind it.

Indeed. I do not think that changing this would make much sense.

> If you can find a way to improve on that without breaking existing code, 
> that'd be fantastic.  If not, definitely try to minimize the impact.

I was just planning to set existing :-variables with expressions, I have 
no great other idea. Mixing languages is always a pain.

Thanks for your feedback.

-- 
Fabien.


Re: Desirability of client-side expressions in psql?

From
Corey Huinker
Date:

   psql> \if :i >= 5


I think we're ok with that so long as none of the operators or values has a \ in it.
What barriers do you see to re-using the pgbench grammar?

 

Re: Desirability of client-side expressions in psql?

From
Fabien COELHO
Date:
Hello Corey,

>>    psql> \if :i >= 5
>>
> I think we're ok with that so long as none of the operators or values has a
> \ in it.
> What barriers do you see to re-using the pgbench grammar?

The pgbench expression grammar mimics SQL expression grammar,
on integers, floats, booleans & NULL.

I'm unsure about some special cases in psql (`shell command`,
'text' "identifier"). They can be forbidden on a new commande (\let),
but what happens on "\if ..." which I am afraid allows them is unclear.

-- 
Fabien.


Re: Desirability of client-side expressions in psql?

From
Corey Huinker
Date:


>>    psql> \if :i >= 5
>>
> I think we're ok with that so long as none of the operators or values has a
> \ in it.
> What barriers do you see to re-using the pgbench grammar?

The pgbench expression grammar mimics SQL expression grammar,
on integers, floats, booleans & NULL.

I'm unsure about some special cases in psql (`shell command`,
'text' "identifier"). They can be forbidden on a new commande (\let),
but what happens on "\if ..." which I am afraid allows them is unclear.

--
Fabien.

(raising this thread from hibernation now that I have the bandwidth)

It seems like the big barriers to just using pgbench syntax are:
  - the ability to indicate that the next thing to follow will be a pgbench expression
  - a way to coax pgbench truth-y values into psql truthy values (t/f, y/n, 1/0)

For that, I see a few ways forward:

1. A suffix on \if, \elif, -exp suffix (or even just -x) to existing commands to indicate that a pgbench expression would follow
This would look something like
    \ifx \elifx \setx
    \if$ \elif$ \set$

2. A command-line-esque switch or other sigil to indicate that what follows is a pgbench expression with psql vars to interpolate
Example:
    \set foo -x 1 + 4
    \set foo \expr 1 + 4
    \if -x :limit > 10
    \if \expr :limit > 10

3. A global toggle to indicate which mode should be used by \if, \elif, and \set
Example:
     \pset expressions [on | off]

4. A combination of #2 and #3 with a corresponding switch/sigil to indicate "do not evaluate pgbench-style
   This is particularly appealing to me because it would allow code snippets from pgbench to be used without modification, while still allowing the user to mix-in old/new style to an existing script.

5. A special variant of `command` where variables are interpolated before being sent to the OS, and allow that on \if, \elif
    \set foo ``expr :y + :z``
    \set foo $( expr :y + :z )
    \if ``expr :limit > 10``
    \if $( expr :limit > 10 )

    This also has some appeal because it allows for a great amount of flexibility, but obviously constrains us with OS-dependencies. The user might have a hard time sending commands with ')' in them if we go the $( ) route

6. Option #5, but we add an additional executable (suggested name: pgexpr) to the client libs, which encapsulates the pgbench expression library as a way around OS-dependent code.

7. I believe someone suggested introducing the :{! pgbench-command} or :{{ pgbench-command }} var-mode 
    \set foo :{! :y + :z }
    \set foo :{{ :y + :z }}
    \if :{! :limit > 10 }
    \if :{{ :limit > 10 }}

    This has some appeal as well, though I prefer the {{...}}  syntax because "!" looks like negation, and {{ resembles the [[ x + y ]] syntax in bash

One nice thing is that most of these options are not mutually exclusive.

Thoughts?

Re: Desirability of client-side expressions in psql?

From
Pavel Stehule
Date:


so 24. 11. 2018 v 22:03 odesílatel Corey Huinker <corey.huinker@gmail.com> napsal:


>>    psql> \if :i >= 5
>>
> I think we're ok with that so long as none of the operators or values has a
> \ in it.
> What barriers do you see to re-using the pgbench grammar?

The pgbench expression grammar mimics SQL expression grammar,
on integers, floats, booleans & NULL.

I'm unsure about some special cases in psql (`shell command`,
'text' "identifier"). They can be forbidden on a new commande (\let),
but what happens on "\if ..." which I am afraid allows them is unclear.

--
Fabien.

(raising this thread from hibernation now that I have the bandwidth)

thank you for it :)


It seems like the big barriers to just using pgbench syntax are:
  - the ability to indicate that the next thing to follow will be a pgbench expression
  - a way to coax pgbench truth-y values into psql truthy values (t/f, y/n, 1/0)

For that, I see a few ways forward:

1. A suffix on \if, \elif, -exp suffix (or even just -x) to existing commands to indicate that a pgbench expression would follow
This would look something like
    \ifx \elifx \setx
    \if$ \elif$ \set$

2. A command-line-esque switch or other sigil to indicate that what follows is a pgbench expression with psql vars to interpolate
Example:
    \set foo -x 1 + 4
    \set foo \expr 1 + 4
    \if -x :limit > 10
    \if \expr :limit > 10

3. A global toggle to indicate which mode should be used by \if, \elif, and \set
Example:
     \pset expressions [on | off]

4. A combination of #2 and #3 with a corresponding switch/sigil to indicate "do not evaluate pgbench-style
   This is particularly appealing to me because it would allow code snippets from pgbench to be used without modification, while still allowing the user to mix-in old/new style to an existing script.

5. A special variant of `command` where variables are interpolated before being sent to the OS, and allow that on \if, \elif
    \set foo ``expr :y + :z``
    \set foo $( expr :y + :z )
    \if ``expr :limit > 10``
    \if $( expr :limit > 10 )

    This also has some appeal because it allows for a great amount of flexibility, but obviously constrains us with OS-dependencies. The user might have a hard time sending commands with ')' in them if we go the $( ) route

6. Option #5, but we add an additional executable (suggested name: pgexpr) to the client libs, which encapsulates the pgbench expression library as a way around OS-dependent code.

7. I believe someone suggested introducing the :{! pgbench-command} or :{{ pgbench-command }} var-mode 
    \set foo :{! :y + :z }
    \set foo :{{ :y + :z }}
    \if :{! :limit > 10 }
    \if :{{ :limit > 10 }}

    This has some appeal as well, though I prefer the {{...}}  syntax because "!" looks like negation, and {{ resembles the [[ x + y ]] syntax in bash

I think so your proposed syntax {{ }} can be great.

\if {{ :SERVER_NUM > 100000 }}

looks perfect.

I am not sure, how difficult is implement this syntax. Another good possibility can be `` expr ``.

Regards

Pavel


One nice thing is that most of these options are not mutually exclusive.

Thoughts?