Thread: [HACKERS] Undefined psql variables
I was giving some thought to how psql handles undefined variables.
I would like an option where either psql can provide an alternate value when an undefined variable is referenced, or a way to detect that a specific variable is undefined and replace it with a defined variable.
My first thought thought was to have a
\set_if_undefined var_name 'default_value'
Another idea adding a \pset parameter that would return a specific value when an undefined psql variable is referenced instead of raising an error. Like this:
# select :'x' as value_of_x;ERROR: syntax error at or near ":"LINE 1: select :'x' as value_of_x;^# \pset variable_default ''analytics=# select :'x' as value_of_x;value_of_x------------(1 row)# \pset variable_default ''# select :'x' as value_of_x;ERROR: syntax error at or near ":"LINE 1: select :'x' as value_of_x;^
This would end up having behavior somewhat similar to +e/-e in bash, where a paranoid script could do something like this:
\pset variable_default 'default1'select :'required_var1' as required_var1 \gset\pset variable_default 'default2'select :'required_var2' as required_var2 \gset-- reset to default behavior\pset variable_default error
Thus setting sane defaults to vars that weren't assigned at invocation time.
Thoughts?
I was giving some thought to how psql handles undefined variables.I would like an option where either psql can provide an alternate value when an undefined variable is referenced, or a way to detect that a specific variable is undefined and replace it with a defined variable.My first thought thought was to have a\set_if_undefined var_name 'default_value'
\coalesce var_name [maybe other var names?] 'default_value' --sets the value of var_name to itself or the first defined/non-null result of the subsequent items. Probably only useful if you allow expressions. I have followed the "\if" thread that closely but IIRC that was part of the discussion there.
Another idea adding a \pset parameter that would return a specific value when an undefined psql variable is referenced instead of raising an error. Like this:# select :'x' as value_of_x;ERROR: syntax error at or near ":"LINE 1: select :'x' as value_of_x;^# \pset variable_default ''analytics=# select :'x' as value_of_x;value_of_x------------(1 row)
-1
I unconvinced that setting this in the global environment is a good thing.
David J.
On Mon, Jan 23, 2017 at 12:49 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
I have followed the "\if" thread that closely but IIRC that was part of the discussion there.
Yes, I'm trying to split some of those side-thoughts into their own threads.
Corey Huinker <corey.huinker@gmail.com> writes:
> I was giving some thought to how psql handles undefined variables.
> I would like an option where either psql can provide an alternate value
> when an undefined variable is referenced, or a way to detect that a
> specific variable is undefined and replace it with a defined variable.
This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps
\if not defined(x) \set x y \fi
Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.
regards, tom lane
On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Corey Huinker <corey.huinker@gmail.com> writes:
> I was giving some thought to how psql handles undefined variables.
> I would like an option where either psql can provide an alternate value
> when an undefined variable is referenced, or a way to detect that a
> specific variable is undefined and replace it with a defined variable.
This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps
\if not defined(x)
\set x y
\fi
Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.
regards, tom lane
That'd work too, if \if and defined(psql_var_name) come to fruition.
A use case we have now is where a script has several values that are almost always a default value.
Instead of
A use case we have now is where a script has several values that are almost always a default value.
Instead of
I can just specify the non-default values:PGSERVICE=foo psql -f script.sql --set var1=a --set var2=b --set var3=d ... --set varN=n
PGSERVCE=foo psql -f script.sql --set var3=unusual_value
and the code then shows the unusual needle in the default haystack.
2017-01-23 18:53 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Corey Huinker <corey.huinker@gmail.com> writes:
> I was giving some thought to how psql handles undefined variables.
> I would like an option where either psql can provide an alternate value
> when an undefined variable is referenced, or a way to detect that a
> specific variable is undefined and replace it with a defined variable.
This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps
\if not defined(x)
\set x y
\fi
Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.
Some form of DEFINED has more sense than COALESCE in this case. We can introduce some predefined client side functions available only in \if \elsif statements.
Regards
Pavel
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
>> [...] Obviously the \if stuff is things we don't have yet either, but >> it seems less likely to have surprising side-effects. I agree, a more generic solution seems better than an ad-hoc one. Currently the value of a non existing psql-variable is... its own reference:-( psql> \echo :x > :x I'm not sure of the rational, apart from the probable lexer implementation point of view. Maybe an empty string or 0 or some configurable value would provide better alternative. > PGSERVICE=foo psql -f script.sql --set var1=a --set var2=b --set var3=d ... > --set varN=n How are the variable typically used in the script? -- Fabien.
[...] Obviously the \if stuff is things we don't have yet either, but it seems less likely to have surprising side-effects.
I agree, a more generic solution seems better than an ad-hoc one.
Currently the value of a non existing psql-variable is... its own reference:-(
psql> \echo :x
> :x
I'm not sure of the rational, apart from the probable lexer implementation point of view. Maybe an empty string or 0 or some configurable value would provide better alternative.
The fundamental problem is that:
SELECT 'testing' AS ":tablename"
is perfectly valid SQL code. psql, by design, attempts to resolve all strings of the form <:['"]?\w> in a purely textual manner.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jan 23, 2017 at 11:16 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
>> Currently the value of a non existing psql-variable is... its own
>> reference:-(
>>
>> psql> \echo :x
>> :x
>>
>> I'm not sure of the rational, apart from the probable lexer implementation
>> point of view. Maybe an empty string or 0 or some configurable value would
>> provide better alternative.
> The fundamental problem is that:
> SELECT 'testing' AS ":tablename"
> is perfectly valid SQL code.
Yeah, but psql does know not to try to resolve :something inside a quoted
literal or identifier. The actual problem is with constructs like
SELECT somearray[lower:upper] FROM ...
If the user is thinking that's an array subscript not a variable
reference, we don't want to break their query when we don't even have
a useful thing to contribute.
Back in the day, PG allowed ":" as a generic operator name, making
this even worse; but I think the only remaining SQL syntax that could
include a colon is array slicing.
regards, tom lane
> Back in the day, PG allowed ":" as a generic operator name, making
> this even worse; but I think the only remaining SQL syntax that could
> include a colon is array slicing.
Ok, so the behavior of replacing ":unknown" by same cannot be changed.
Some fun:
\set 1 1 SELECT ('{1,2,3,4,5,6,7,8,9,10,11,12}'::INT[])[1:1]; -- yields 11
--
Fabien.
On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Corey Huinker <corey.huinker@gmail.com> writes:
> I was giving some thought to how psql handles undefined variables.
> I would like an option where either psql can provide an alternate value
> when an undefined variable is referenced, or a way to detect that a
> specific variable is undefined and replace it with a defined variable.
This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps
\if not defined(x)
\set x y
\fi
Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.
regards, tom lane
In light of the backticks variable expansion thread, I'm reviving this thread in the hopes that a defined()-ish psql function can make it into v10.
It's something that cannot be solved with a query and \gset, so adding it to psql boolean expressions is the only option I can see.
Corey Huinker <corey.huinker@gmail.com> writes:
> On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This seems pretty bizarre. What's the use case? Why would it not
>> be better to build the behavior out of other spare parts, along the
>> lines of COALESCE or perhaps
>> \if not defined(x)
> In light of the backticks variable expansion thread, I'm reviving this
> thread in the hopes that a defined()-ish psql function can make it into v10.
> It's something that cannot be solved with a query and \gset, so adding it
> to psql boolean expressions is the only option I can see.
I'm fairly hesitant to add stuff in advance of having a fairly clear
sketch of the boolean expression language we want. I don't mind
implementing such a language piece-by-piece, but if we just throw in
one or two features that seem like good ideas, I'm afraid we'll be
painting ourselves into a corner.
The only thing that seems locked down so far is that "a single argument
is a simple boolean value". If we were hot to support expr-style
comparison behavior, we could define cases with exactly three arguments
as being "\if value operator value". But I'm afraid that that would
cause problems because there would be other desirable behaviors (like
"\if not defined varname") that would also involve three arguments,
creating ambiguity.
I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions. So you could imagine
\if defined varname\if sql boolean expression to send to server\if compare value operator value
It would be easy to allow "not" in front of any one of these, but
it's less clear how to do AND or OR combinations. You can always
fake AND with nested \if's, but OR is a bit more of a problem.
Maybe we don't need it.
Other ideas about how to design this?
regards, tom lane
Hello Tom, > I'm inclined to suggest that we should require all extensions beyond the > boolean-literal case to be set up as a keyword followed by appropriate > argument(s); that seems like it's enough to prevent syntax conflicts from > future additions. So you could imagine > > \if defined varname > \if sql boolean expression to send to server > \if compare value operator value > > It would be easy to allow "not" in front of any one of these, but > it's less clear how to do AND or OR combinations. You can always > fake AND with nested \if's, but OR is a bit more of a problem. > Maybe we don't need it. > > Other ideas about how to design this? My 0.02 €: I have convinced myself that, unlike pgbench, psql does not really need an advanced client-side-implemented language, so the smaller the better. What I mean by this is that from psql point of view it is ok that the actual expression evaluation is performed server-side. From a user experience point of view it would look similar to pgbench, just the evaluator does not need to be client-side. So I would suggest something close but maybe simpler than what you suggest above. If there is just one thing, it is true or false, checked client side, well, this is already implemented:-). \if something If there are more than one argument, or maybe if previous true/false evaluation failed, then: \if sql expression to be evaluated server side Then the result is checked for true or false client-side. It would be equivalent to: SELECT sql expression to be evaluted server side AS is_ok \gset \if :is_ok Finally I would suggest that client to server would only communicate by variable substitution, as the backtick patch with external processes. For checking variable definition, I would suggest to extend the variable access syntax so that there is no exception to the one thing rule between client side and server side evaluation: \if :?variable the :?... is subsituted by true or false depending on whether the variable exists. \if NOT :?variable would work by executing "NOT ..." on the server. No need for "defined" which would not look like SQL function calls anyway, no need for any operator client side or clumsy rules. -- Fabien.
2017-04-02 18:29 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Corey Huinker <corey.huinker@gmail.com> writes:
> On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This seems pretty bizarre. What's the use case? Why would it not
>> be better to build the behavior out of other spare parts, along the
>> lines of COALESCE or perhaps
>> \if not defined(x)
> In light of the backticks variable expansion thread, I'm reviving this
> thread in the hopes that a defined()-ish psql function can make it into v10.
> It's something that cannot be solved with a query and \gset, so adding it
> to psql boolean expressions is the only option I can see.
I'm fairly hesitant to add stuff in advance of having a fairly clear
sketch of the boolean expression language we want. I don't mind
implementing such a language piece-by-piece, but if we just throw in
one or two features that seem like good ideas, I'm afraid we'll be
painting ourselves into a corner.
The only thing that seems locked down so far is that "a single argument
is a simple boolean value". If we were hot to support expr-style
comparison behavior, we could define cases with exactly three arguments
as being "\if value operator value". But I'm afraid that that would
cause problems because there would be other desirable behaviors (like
"\if not defined varname") that would also involve three arguments,
creating ambiguity.
I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions. So you could imagine
\if defined varname
\if sql boolean expression to send to server
\if compare value operator value
These possibilities looks well.
if defined varname is perfectly intuitive
Maybe it can be shorter - def, undef
\if def var, \if undef var
Regards
Pavel
It would be easy to allow "not" in front of any one of these, but
it's less clear how to do AND or OR combinations. You can always
fake AND with nested \if's, but OR is a bit more of a problem.
Maybe we don't need it.
Other ideas about how to design this?
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
2017-04-02 18:56 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:
Hello Tom,I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions. So you could imagine
\if defined varname
\if sql boolean expression to send to server
\if compare value operator value
It would be easy to allow "not" in front of any one of these, but
it's less clear how to do AND or OR combinations. You can always
fake AND with nested \if's, but OR is a bit more of a problem.
Maybe we don't need it.
Other ideas about how to design this?
My 0.02 €:
I have convinced myself that, unlike pgbench, psql does not really need an advanced client-side-implemented language, so the smaller the better. What I mean by this is that from psql point of view it is ok that the actual expression evaluation is performed server-side. From a user experience point of view it would look similar to pgbench, just the evaluator does not need to be client-side.
I am sorry - I disagree - I don't expect hard scripting in psql too. But psql is much more widely used than pgbench - and scripting should be intuitive.
Regards
Pavel
So I would suggest something close but maybe simpler than what you suggest above. If there is just one thing, it is true or false, checked client side, well, this is already implemented:-).
\if something
If there are more than one argument, or maybe if previous true/false evaluation failed, then:
\if sql expression to be evaluated server side
Then the result is checked for true or false client-side. It would be equivalent to:
SELECT sql expression to be evaluted server side AS is_ok \gset
\if :is_ok
Finally I would suggest that client to server would only communicate by variable substitution, as the backtick patch with external processes.
For checking variable definition, I would suggest to extend the variable access syntax so that there is no exception to the one thing rule between client side and server side evaluation:
\if :?variable
the :?... is subsituted by true or false depending on whether the variable exists.
\if NOT :?variable
would work by executing "NOT ..." on the server. No need for "defined" which would not look like SQL function calls anyway, no need for any
operator client side or clumsy rules.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello Pavel,
>> I have convinced myself that, unlike pgbench, psql does not really need an
>> advanced client-side-implemented language, so the smaller the better. What
>> I mean by this is that from psql point of view it is ok that the actual
>> expression evaluation is performed server-side. From a user experience
>> point of view it would look similar to pgbench, just the evaluator does not
>> need to be client-side.
>
> I am sorry - I disagree - I don't expect hard scripting in psql too. But
> psql is much more widely used than pgbench - and scripting should be
> intuitive.
I am ok with that objective.
I'm just arguing that for pgbench the evaluator needs to be on the client
side, which implies a lexer, parser and executor. For psql, it does not
really matter where the evaluator is, thus relying on the server should be
fine and simpler and also powerful, provided the necessary information can
be transfered from the client, eg through variable expansion, and maybe
back in the form of special variables to test for errors for instance.
It would not change anything from a syntactic point of view, i.e. it
should indeed be intuitive as you put it, i.e. SQL-like, for instance:
\if current_setting('something') = 'whatever' AND :VERSION_NUM >= 100000 ...
--
Fabien.
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I'm just arguing that for pgbench the evaluator needs to be on the client
> side, which implies a lexer, parser and executor. For psql, it does not
> really matter where the evaluator is, thus relying on the server should be
> fine and simpler and also powerful, provided the necessary information can
> be transfered from the client, eg through variable expansion, and maybe
> back in the form of special variables to test for errors for instance.
I don't really buy this. Certainly it'd be fine for many use-cases,
but there will be cases where what you're trying to script around
is server-side errors. An expression evaluation facility that goes
belly-up as soon as the server is in an aborted transaction is not
going to be very useful in that scenario.
I think that we need just a relatively primitive facility in order
to meet that sort of need, but we do need something.
So my view of this is that "send the expression to the server" ought
to be just one option for \if, not the only way to do it. Hence my
suggestion of "\if sql ...text to send to server...". Probably someone
can think of a better keyword than "sql" for that.
regards, tom lane
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> For checking variable definition, I would suggest to extend the variable
> access syntax so that there is no exception to the one thing rule between
> client side and server side evaluation:
> \if :?variable
Don't like that one bit; you're going to run out of namespace there
in no time. And you don't have a very good way to say "if not defined",
either.
regards, tom lane
Hello Tom, >> I'm just arguing that for pgbench the evaluator needs to be on the client >> side, which implies a lexer, parser and executor. For psql, it does not >> really matter where the evaluator is, thus relying on the server should be >> fine and simpler and also powerful, provided the necessary information can >> be transfered from the client, eg through variable expansion, and maybe >> back in the form of special variables to test for errors for instance. > > I don't really buy this. Certainly it'd be fine for many use-cases, but > there will be cases where what you're trying to script around is > server-side errors. An expression evaluation facility that goes belly-up > as soon as the server is in an aborted transaction is not going to be > very useful in that scenario. "Going belly-up" suggests testing/checking for errors, which could be eased through special variables à la errno and more than simplistic client-side expression evaluation. > I think that we need just a relatively primitive facility in order > to meet that sort of need, but we do need something. Hmmm. Yes, I was thinking of that kind of thing. The question is how large the necessary "something". I'm arguying for the smallest possible solution. Maybe handling direct booleans (as already implemented) and the NOT operator could be enough (clear enough to understand for the user, would cover needed cases, and would be easy to implement)? i.e. \if NOT :IS_CONNECTED ... SELECT ... \gset \if :SQL_ERROR_OCCURED ... \if :CURRENT_TRANSACTION_ABORTED ... > So my view of this is that "send the expression to the server" ought > to be just one option for \if, not the only way to do it. Hence my > suggestion of "\if sql ...text to send to server...". Probably someone > can think of a better keyword than "sql" for that. That is the kind of (ugly) thing I would really like to avoid, if possible. As pavel argued, it should be "intuitive", and having a explicit syntactic marker and/or possibly two distinct syntaxes does not strike me as a desirable user-experience. -- Fabien.
Hello,
>> For checking variable definition, I would suggest to extend the variable
>> access syntax so that there is no exception to the one thing rule between
>> client side and server side evaluation:
>
>> \if :?variable
>
> Don't like that one bit;
Possibly:-)
This is kind of a shell-like hack ${VAR:?error-message-if-not-defined},
or ${#VAR} to get a length.
They are not likable but they do the job.
> you're going to run out of namespace there in no time.
I do not undestand where there would be a namespace issue. Is that under
the assumption that ":?xxx" is frequently used in SQL?
> And you don't have a very good way to say "if not defined", either.
Indeed. I'm afraid that handling "NOT" client-side would be necessary with
this approach, so the decision would be 1 thing or 2 things where the
first one is "NOT" would be handled client-side.
--
Fabien.
> I'm inclined to suggest that we should require all extensions beyond the > boolean-literal case to be set up as a keyword followed by appropriate > argument(s); that seems like it's enough to prevent syntax conflicts from > future additions. So you could imagine > > \if defined varname > \if sql boolean expression to send to server > \if compare value operator value I'm still thinking:-) Independently of the my aethetical complaint against having a pretty unusual keyword prefix syntax, how would you envision a \set assignment variant? Would \if have a different expression syntax somehow? -- Fabien.
On Sun, Apr 2, 2017 at 4:57 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions. So you could imagine
\if defined varname
\if sql boolean expression to send to server
\if compare value operator value
I'm still thinking:-)
Independently of the my aethetical complaint against having a pretty unusual keyword prefix syntax, how would you envision a \set assignment variant? Would \if have a different expression syntax somehow?
Any further thoughts?
At Thu, 6 Apr 2017 19:21:21 -0400, Corey Huinker <corey.huinker@gmail.com> wrote in
<CADkLM=eJi_4E9DuhUPq6T_-nh+eX0OY0Sa631Uu4o6cq6bq8cQ@mail.gmail.com>
corey.huinker> On Sun, Apr 2, 2017 at 4:57 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> >> I'm inclined to suggest that we should require all extensions beyond the
> >> boolean-literal case to be set up as a keyword followed by appropriate
> >> argument(s); that seems like it's enough to prevent syntax conflicts from
> >> future additions. So you could imagine
> >>
> >> \if defined varname
> >> \if sql boolean expression to send to server
> >> \if compare value operator value
> >>
> >
> > I'm still thinking:-)
> >
> > Independently of the my aethetical complaint against having a pretty
> > unusual keyword prefix syntax, how would you envision a \set assignment
> > variant? Would \if have a different expression syntax somehow?
>
> Any further thoughts?
If I read Fabien's words correctly, I think \if is different from
\set in what they take and the parameters will be interpreted.
I suppose that we are assuming a very simple parser here. If
there's a possibility to use the first parameter of '\if' as the
kind/interpretation of the following parameters, expressions in
'\if <expr>' should be distinguishable from them only by reading
the first word.
This means that expression also requires to be prefixed by a kind
word (as the "compare" in Tom's suggestion) or prefixed or
enclosed with non-word characters. '\if (<expr>)' for
example. But I'm not sure how represent 'not defined' in this
way.
Or, as Pavel suggested upthread, we could add dedicate keyword
for each type of 'if's. Such as '\if <expr>', "\if(n/un)def
<varname>' and other '\if_blah_blah's.
Otherwise we could introduce pseudo-functions instead, but this
is unnecessarily complex for this case.
| \if defined(<varname>) && execute('select version()')) ~= / 10 devel/
# Ugh.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
Hello Corey, >>> \if defined varname >>> \if sql boolean expression to send to server >>> \if compare value operator value >> >> I'm still thinking:-) >> >> Independently of the my aethetical complaint against having a pretty >> unusual keyword prefix syntax, how would you envision a \set assignment >> variant? Would \if have a different expression syntax somehow? > > Any further thoughts? My current opinion: - I'm fine if \set stays as it is, i.e. no expression. - I agree that some client-side expressions are needed, along the semantics suggested by Tom, i.e. definition and comparisons. - I'm really against the prefix syntax suggested by Tom I wish I could have an explanation about why the :?varname (or some other variant) syntax I suggested has a "namespace" issue. The advantage that I see is that although it is obviously ugly, it is ugly in the continuity of the various :["'?]varname syntaxes already offered and it allows to get rid of "defined varname" which does not look like SQL. A second advantage is that with the "defined" proposal \if defined var1 and defined var2 or defined var3 and sqlrt() >= .. Would probably never work work, as it cannot be embedded in another expression, while it would work with \if :?var1 and :?var2 or :?var3 and ... Moreover, I would like the condition syntax to be basically SQL & psql variables, without explicit prefixes, with a transparent decision whether it is evaluated client side or server side. As client-side expressions are pretty simple, ISTM that some regex could be used for this purpose, eg for integer and boolean comparisons: ^\s*\d+\s*(=|<>|!=|<|<=|>|>=)\s*\d+\s*$ ^\s*(bool...)\s*(=|<>|!=)\s*(bool...)\s*$ ^\s*(NOT\s*)?(bool...)\s*$ So that one could just write the expressions without having to tell where it is executed, eg \if :VERSION_NUM < 110000 Would lead to \if 100000 < 110000 Caught by the first regex, and evaluated with a few lines of code. -- Fabien.
2017-04-07 9:52 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:
Hello Corey,\if defined varname
\if sql boolean expression to send to server
\if compare value operator value
I'm still thinking:-)
Independently of the my aethetical complaint against having a pretty
unusual keyword prefix syntax, how would you envision a \set assignment
variant? Would \if have a different expression syntax somehow?
Any further thoughts?
My current opinion:
- I'm fine if \set stays as it is, i.e. no expression.
- I agree that some client-side expressions are needed, along the
semantics suggested by Tom, i.e. definition and comparisons.
- I'm really against the prefix syntax suggested by Tom
I wish I could have an explanation about why the :?varname (or some other variant) syntax I suggested has a "namespace" issue.
The advantage that I see is that although it is obviously ugly, it is ugly in the continuity of the various :["'?]varname syntaxes already offered and it allows to get rid of "defined varname" which does not look like SQL. A second advantage is that with the "defined" proposal
I don't think so this argument is valid - \if doesn't look like SQL too.
\if defined var1 and defined var2 or defined var3 and sqlrt() >= ..
Would probably never work work, as it cannot be embedded in another expression, while it would work with
\if :?var1 and :?var2 or :?var3 and ...
I don't see any reason why first should not work and second should to work
Moreover, I would like the condition syntax to be basically SQL & psql variables, without explicit prefixes, with a transparent decision whether it is evaluated client side or server side.
As client-side expressions are pretty simple, ISTM that some regex could be used for this purpose, eg for integer and boolean comparisons:
^\s*\d+\s*(=|<>|!=|<|<=|>|>=)\s*\d+\s*$
^\s*(bool...)\s*(=|<>|!=)\s*(bool...)\s*$
^\s*(NOT\s*)?(bool...)\s*$
So that one could just write the expressions without having to tell where it is executed, eg
\if :VERSION_NUM < 110000
Would lead to
\if 100000 < 110000
Caught by the first regex, and evaluated with a few lines of code.
I have a different opinion - the condition expression should not be SQL necessary. This language is oriented on client side operations. What is benefit from server side expression?
Regards
Pavel
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello Pavel,
>> I wish I could have an explanation about why the :?varname (or some other
>> variant) syntax I suggested has a "namespace" issue.
>>
>> The advantage that I see is that although it is obviously ugly, it is ugly
>> in the continuity of the various :["'?]varname syntaxes already offered and
>> it allows to get rid of "defined varname" which does not look like SQL. A
>> second advantage is that with the "defined" proposal
>
> I don't think so this argument is valid - \if doesn't look like SQL too.
Sure. I'm talking about the expressions after the "\if" which should be
as close as SQL, I think. At least that is what Tom required about the
expression syntax in pgbench, and I tend to agree that psql should avoid
to mix in another language if possible.
>> \if defined var1 and defined var2 or defined var3 and sqlrt() >= ..
>>
>> Would probably never work work, as it cannot be embedded in another
>> expression, while it would work with
>>
>> \if :?var1 and :?var2 or :?var3 and ...
>>
> I don't see any reason why first should not work and second should to work
Because of the mix of client-side and server-side stuff which needs to be
interpreted. Let us consider:
\if EXISTS (SELECT * FROM tbl WHERE id=3) AND defined foo
The "exists" is obviously executed server-side, but "defined foo" needs to
be interpreted client-side, and it means that some parser client side
would have been able to catch it in the middle of everything else. This
example also illustrate my "does not look like SQL" point, as the first
part is clearly SQL and the part after AND is not.
With the second approach, ... "AND :?foo", the ":?foo" reference would be
substituted directly by psql lexer and replaced on the fly by the answer,
resulting in "AND TRUE" or "AND FALSE" depending, then the whole result
(from EXISTS to TRUE/FALSE) could be interpreted server side to get an
answer.
Basically, catching :?varname seems easier/safer than catching "defined
varname". I think that Tom's intent is that the defined expressions could
not be mixed with SQL server side stuff, but I do not see why not, it is
easy to imagine some use case where it would make sense.
> I have a different opinion - the condition expression should not be SQL
> necessary. This language is oriented on client side operations. What is
> benefit from server side expression?
Because I think it is legitimate to be able to write things like:
\if NOT pg_extension_is_loaded('units') \echo 'this application requires the great units extension' \q \endif
\if (SELECT version FROM app_version) >= 2.0 \echo 'application already installed at 2.0' \q \endif
--
Fabien.
2017-04-07 21:04 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:
Hello Pavel,I wish I could have an explanation about why the :?varname (or some other
variant) syntax I suggested has a "namespace" issue.
The advantage that I see is that although it is obviously ugly, it is ugly
in the continuity of the various :["'?]varname syntaxes already offered and
it allows to get rid of "defined varname" which does not look like SQL. A
second advantage is that with the "defined" proposal
I don't think so this argument is valid - \if doesn't look like SQL too.
Sure. I'm talking about the expressions after the "\if" which should be as close as SQL, I think. At least that is what Tom required about the expression syntax in pgbench, and I tend to agree that psql should avoid to mix in another language if possible.\if defined var1 and defined var2 or defined var3 and sqlrt() >= ..I don't see any reason why first should not work and second should to work
Would probably never work work, as it cannot be embedded in another
expression, while it would work with
\if :?var1 and :?var2 or :?var3 and ...
Because of the mix of client-side and server-side stuff which needs to be interpreted. Let us consider:
\if EXISTS (SELECT * FROM tbl WHERE id=3) AND defined foo
The "exists" is obviously executed server-side, but "defined foo" needs to be interpreted client-side, and it means that some parser client side would have been able to catch it in the middle of everything else. This example also illustrate my "does not look like SQL" point, as the first part is clearly SQL and the part after AND is not.
With the second approach, ... "AND :?foo", the ":?foo" reference would be substituted directly by psql lexer and replaced on the fly by the answer, resulting in "AND TRUE" or "AND FALSE" depending, then the whole result (from EXISTS to TRUE/FALSE) could be interpreted server side to get an answer.
Basically, catching :?varname seems easier/safer than catching "defined varname". I think that Tom's intent is that the defined expressions could not be mixed with SQL server side stuff, but I do not see why not, it is easy to imagine some use case where it would make sense.I have a different opinion - the condition expression should not be SQL
necessary. This language is oriented on client side operations. What is
benefit from server side expression?
Because I think it is legitimate to be able to write things like:
\if NOT pg_extension_is_loaded('units')
\echo 'this application requires the great units extension'
\q
\endif
\if (SELECT version FROM app_version) >= 2.0
\echo 'application already installed at 2.0'
\q
\endif
you proposal disallow client side expressions. I agree so is not possible to mix server side and client side expressions. But I am sceptic so benefit of server side expression is higher than a lost of client side expressions. If we disallow server side expressions, then your examples are only two lines longer, but the implementation can be more simpler.
SELECT version FROM app_version
\gset
\if :version >= 2.0
...
...
Still I don't think so server side expression in \if is good idea.
Regards
Pavel
--
Fabien.
Hello Pavel, n > you proposal disallow client side expressions. I do agree that some client side expressions are necessary. I do not want to disallow them. > I agree so is not possible to mix server side and client side > expressions. My point is that a minimal of cross-support is possible. > But I am sceptic so benefit of server side expression is higher than a > lost of client side expressions. There is a misunderstanding. I am not against client side expression. I do want to allow the same server & client side capabilities suggested by Tom, I'm just arguing about the syntax, to avoid a prefix oriented approach. > If we disallow server side expressions, then your examples are only two > lines longer, but the implementation can be more simpler. > > SELECT version FROM app_version > \gset > \if :version >= 2.0 > ... > > Still I don't think so server side expression in \if is good idea. Ok, so you do not like server-side expression capabities integrated to \if. I understood that you were in favor of Tom's proposal. From a semantical point of view they are not necessary because the same effect can be obtained through \gset, at the price of an intermediate variable. So the server-side thing is just a syntax convenience. I think that independently of whether they are added, Tom's point is that it should be possible to add those features later on, hence the discussion about a design. -- Fabien.
2017-04-08 12:25 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:
Hello Pavel,
nyou proposal disallow client side expressions.
I do agree that some client side expressions are necessary. I do not want to disallow them.I agree so is not possible to mix server side and client side expressions.
My point is that a minimal of cross-support is possible.But I am sceptic so benefit of server side expression is higher than a lost of client side expressions.
There is a misunderstanding. I am not against client side expression. I do want to allow the same server & client side capabilities suggested by Tom, I'm just arguing about the syntax, to avoid a prefix oriented approach.If we disallow server side expressions, then your examples are only two
lines longer, but the implementation can be more simpler.
SELECT version FROM app_version
\gset
\if :version >= 2.0
...
Still I don't think so server side expression in \if is good idea.
Ok, so you do not like server-side expression capabities integrated to \if. I understood that you were in favor of Tom's proposal.
In this moment I see difficult implementation of \if expressions if we should to separate server side and client side expressions. The prefix oriented approach is used well in PLpgSQL
FOR IN SELECT, FOR IN ARRAY, FOR IN 1..x
Tom's design should be more simply for implementation and can be simply extended. There is clean if expr is client side or server side.
From a semantical point of view they are not necessary because the same effect can be obtained through \gset, at the price of an intermediate variable. So the server-side thing is just a syntax convenience. I think that independently of whether they are added, Tom's point is that it should be possible to add those features later on, hence the discussion about a design.
We are talking about primitive scripting language - that should be simple how it is possible. One command more, or performance there are not important - if the performance will not be terrible. The overhead of intermediate variable is +/- zero against remote expression. But back to main issue. We should to find syntax if some variable is defined and can be used or not.
Maybe the solution should not be directly joined with \if command.
what do you think about special \set command, that can be active only when variable is not defined.
some like
\setifempty xxx default
Regards
Pavel
--
Fabien.
On Sun, Apr 2, 2017 at 3:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> So my view of this is that "send the expression to the server" ought
> to be just one option for \if, not the only way to do it.
I heartily agree. There should be some kind of client-side expression
language, and one thing it should allow is calling out to the server.
Then people who only want to call out to the server can do that, but
people who want to do something else have the option. Insisting that
this facility isn't allowed to do anything other than consult the
server is (1) inconsistent with what we've already got in v10 and (2)
boxing ourselves into a corner for no very good reason.
Now, the optimal shape for that client-side expression language is not
very clear to me. Do we want to invent our own language, or maybe
consider using something that already exists? It's been previously
suggested that we should somehow embed Lua, and this might not be a
bad place to consider doing something like that. That might be a way
to add a lot of power without having to invent an entirely new
programming language one bit at a time. If we want to invent our own
expression language, what kind of syntax should it use? Upon what
kind of design principles should it be based? There's likely to be
vigorous debate on these topics, and probably also complaints that the
good designs are too much work and the easy-to-implement designs are
too limiting. (Regular readers of this mailing list will likely be
able to guess which side of those debates I'll be on, but we need to
have them all the same.)
Regarding the ostensible topic of this thread, one thought I had while
reading through these various responses is that the original need
would be well-served by the (somewhat dubious) syntax that bash uses
for variable substitution. Obviously, we aren't going to change the
interpolate-this-variable character from : to $, but bash has
${parameter:-word} to substitute a default for an unset parameter,
${parameter:=word} to substitute a default for an unset parameter and
also set the parameter to that value, ${parameter:?word} to error out
with word as the error mesage if parameter is not set, and so forth.
If we decide to roll our own, we might consider taking inspiration
from those constructs.
I think that one of the general problems of language design is, as
Larry Wall once said, that a good language should make simple things
simple and complex things possible. But simple is not an absolute; it
depends on context. The things which a language needs to make simple
are those things which will be done frequently *in that language*. So
for example in this case, out-calls to SQL need to be very easy to
write. Maybe the empty-parameter thing needs to be easy; not sure.
Coming up with a good solution here will involve understanding what
people typically want to do with a language of this type and then
making sure that stuff can be done succinctly - and ideally also
making sure that other stuff is also possible if you're willing to put
in more legwork.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
2017-04-12 17:05 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:
On Sun, Apr 2, 2017 at 3:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> So my view of this is that "send the expression to the server" ought
> to be just one option for \if, not the only way to do it.
I heartily agree. There should be some kind of client-side expression
language, and one thing it should allow is calling out to the server.
Then people who only want to call out to the server can do that, but
people who want to do something else have the option. Insisting that
this facility isn't allowed to do anything other than consult the
server is (1) inconsistent with what we've already got in v10 and (2)
boxing ourselves into a corner for no very good reason.
Now, the optimal shape for that client-side expression language is not
very clear to me. Do we want to invent our own language, or maybe
consider using something that already exists? It's been previously
suggested that we should somehow embed Lua, and this might not be a
bad place to consider doing something like that. That might be a way
to add a lot of power without having to invent an entirely new
programming language one bit at a time. If we want to invent our own
expression language, what kind of syntax should it use? Upon what
kind of design principles should it be based? There's likely to be
vigorous debate on these topics, and probably also complaints that the
good designs are too much work and the easy-to-implement designs are
too limiting. (Regular readers of this mailing list will likely be
able to guess which side of those debates I'll be on, but we need to
have them all the same.)
Integration Lua engine can help lot of - and it can change the design significantly. For this purpose it is maybe overkill, but it can be fresh air in psql customisation and usage.
\setlua varname one line lua expression
or
\lua
...
lua code
psqlvar.set("xxxx", somevalue)
\endlua
I like this idea. We can use Math libraries, random generators, ...
If Lua engine and dependency are too strong cafe - very basic calculator like https://www.l2f.inesc-id.pt/~david/w/pt/The_YACC_Parser_Generator/Example:_Calculator_with_Variables can be good enough (Don't need a variables there)
Regarding the ostensible topic of this thread, one thought I had while
reading through these various responses is that the original need
would be well-served by the (somewhat dubious) syntax that bash uses
for variable substitution. Obviously, we aren't going to change the
interpolate-this-variable character from : to $, but bash has
${parameter:-word} to substitute a default for an unset parameter,
${parameter:=word} to substitute a default for an unset parameter and
also set the parameter to that value, ${parameter:?word} to error out
with word as the error mesage if parameter is not set, and so forth.
If we decide to roll our own, we might consider taking inspiration
from those constructs.
It is great and it can work
\set varname :{varname?some error message} ..
\set varname :{varname:-0} ..
Good ideas
Regards
Pavel
I think that one of the general problems of language design is, as
Larry Wall once said, that a good language should make simple things
simple and complex things possible. But simple is not an absolute; it
depends on context. The things which a language needs to make simple
are those things which will be done frequently *in that language*. So
for example in this case, out-calls to SQL need to be very easy to
write. Maybe the empty-parameter thing needs to be easy; not sure.
Coming up with a good solution here will involve understanding what
people typically want to do with a language of this type and then
making sure that stuff can be done succinctly - and ideally also
making sure that other stuff is also possible if you're willing to put
in more legwork.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hello Robert,
My 0.02€ about your interesting questions and points.
> On Sun, Apr 2, 2017 at 3:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> So my view of this is that "send the expression to the server" ought
>> to be just one option for \if, not the only way to do it.
>
> I heartily agree. There should be some kind of client-side expression
> language, and one thing it should allow is calling out to the server.
Calling the server is already available:
SELECT <whatever> AS varname \gset
What is missing is some client-side expressions.
As \if is a client-side thing, I now think that it should just rely on
client-side evaluation. Note that it is possible to do better, but
solutions are either ugly (strange prefixes) or too clever and possibly
not extensible (regex filtering), and from a user experience point of view
I finally thing that ugly or clever should be avoided.
> Then people who only want to call out to the server can do that, but
> people who want to do something else have the option. Insisting that
> this facility isn't allowed to do anything other than consult the
> server is (1) inconsistent with what we've already got in v10 and (2)
> boxing ourselves into a corner for no very good reason.
>
> Now, the optimal shape for that client-side expression language is not
> very clear to me. Do we want to invent our own language, or maybe
> consider using something that already exists?
> It's been previously suggested that we should somehow embed Lua, and
> this might not be a bad place to consider doing something like that.
I somewhat disagree: Does building postgres should depend on lua? I think
adding such a mandatory dependency would not be a good idea. If it is not
mandatory, then it would mean that psql could be compiled with or without
lua embedding, thus psql would not be dependable because features may or
may not be available when writing a "psql script".
For me, client embedded language pg-{lua,pl,tcl,i?py,bf...} (chose your
favorite:-) projects could make sense, but it does not have to be done
within the existing psql client, especially with trying to keep upward
compatibility... If started, such a thing should be a distinct project,
possibly hosted within postgres source tree if it works well at some
point.
> That might be a way to add a lot of power without having to invent an
> entirely new programming language one bit at a time.
Does programming as such in psql is such a good idea?
ISTM that cpp-like capabilities (include, if, variables, some expressions)
are somewhat both useful and enough for the limited use cases I have
encountered. Similar languages are offered in other instances, such as
readline inputrc or vim vimrc.
If I have something really complicated, then I really want a programming
language, probably I do not want to learn a new one just for this purpose,
so I switch to something else that I already know which will do some SQL
when necessary.
> If we want to invent our own expression language, what kind of syntax
> should it use?
After about 35 years of programming, I've convinced myself that mixing
languages is most often a bad idea (think HTML/CSS/JS/PHP/SQL all in one
file). Currently psql has SQL, backslash commands, :* client-side
variables, all with good justifications. That is somewhat 3 languages (or
2.5 if counting variable substitions for half a language), and I think it
should not go up if avoidable.
This leads to the opinion that if there is a client side language (or
client-side expressions as we are considering here), then it should look
like SQL, hence my constant ranting about the "defined varname" somehow
perlish thing. Tom helped forge this opinion when argumenting about some
pgbench changes I submitted, and he is the one suggesting this.
> Upon what kind of design principles should it be based?
I submit that client side expressions should be a subset of SQL and
possible existing or extended variable substitution.
> There's likely to be vigorous debate on these topics, and probably also
> complaints that the good designs are too much work and the
> easy-to-implement designs are too limiting. (Regular readers of this
> mailing list will likely be able to guess which side of those debates
> I'll be on, but we need to have them all the same.)
I suggest to reuse pgbench expression engine, developed by Haas Robert:-)
I have submitted a patch to add some functions and boolean support, which
seems like a definite requirement for "\if". Although pgbench expressions
are a bit overkill for psql, I think that developing another expression
engine is a bad idea, just reuse the one.
> Regarding the ostensible topic of this thread, one thought I had while
> reading through these various responses is that the original need
> would be well-served by the (somewhat dubious) syntax that bash uses
> for variable substitution.
> Obviously, we aren't going to change the interpolate-this-variable
> character from : to $, but bash has ${parameter:-word} to substitute a
> default for an unset parameter, ${parameter:=word} to substitute a
> default for an unset parameter and also set the parameter to that value,
> ${parameter:?word} to error out with word as the error mesage if
> parameter is not set, and so forth. If we decide to roll our own, we
> might consider taking inspiration from those constructs.
That is somehow what I'm trying to do with the ":?varname" proposal, which
would return TRUE or FALSE depending on whether the variable is defined.
Note that there are a number of issue with bash detailed approach, so the
inspiration needs to be cautious. For instance, bash does not distinguish
easily between undefined and empty, and I think that for a cpp-like
language it should be "easy", as discussed below. The only way to test for
undefined with bash seems to be:
[ "${foo:-bar}" = 'bar' -a "${foo:-foo}" = 'foo' ]
Generating a error message with ${foo:?} is nice, but what psql need is
just a way to test whether a variable is defined or not. Another
difference is that sh substitution escaping are quite different from what
is needed for SQL. The shell escapes when substituting within "", but pg
has two kind of strings with escaping ('' and "") already addressed by the
:'varname' and :"varname" syntaxes. So I think that keeping things
consistent with the current :* syntax is desirable, but it is somehow too
late for redesigning variables, as even if something consistent is thought
of, there would still be the upward compatibility with the :* design which
would make things weird.
> I think that one of the general problems of language design is, as
> Larry Wall once said, that a good language should make simple things
> simple and complex things possible. But simple is not an absolute; it
> depends on context. The things which a language needs to make simple
> are those things which will be done frequently *in that language*. So
> for example in this case, out-calls to SQL need to be very easy to
> write.
If I understand out-calls as server-side, it has been available for years
with gset. I do not like it particularly, but it is there and I do not see
a point to replace it.
> Maybe the empty-parameter thing needs to be easy; not sure.
Empty vs undefined?
> Coming up with a good solution here will involve understanding what
> people typically want to do with a language of this type and then
> making sure that stuff can be done succinctly - and ideally also
> making sure that other stuff is also possible if you're willing to put
> in more legwork.
Basically my current opinion, influenced by Tom, Pavel, Robert and others,
is that:
- server side expression use SELECT & \gset nothing to do on that front. - client side expressions are based on an
SQLsubset such expressions would appear: . after \if and \elif . after \let varname ... - variable definition is
testedwith :?varname which is a boolean constant - some escaping should be allowed if someone really wants to write
:?varnameas such in SQL.
--
Fabien.
On Thu, Apr 13, 2017 at 8:56 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> Calling the server is already available:
>
> SELECT <whatever> AS varname \gset
Sure, but people are going to want to do it inline with the \if.
Anything that can be done that way can also be done this way, but
people will want it just to make the code look nicer. I don't think
we should restrict \if to be ONLY an SQL callout, but if people want
that as an option, and I bet they do, then I think we should give it
to them.
> I somewhat disagree: Does building postgres should depend on lua? I think
> adding such a mandatory dependency would not be a good idea. If it is not
> mandatory, then it would mean that psql could be compiled with or without
> lua embedding, thus psql would not be dependable because features may or may
> not be available when writing a "psql script".
That's true, but you could say the same thing about SSL or NLS. In
practice, any vendor distribution of PostgreSQL will be built with
those options even though, for good reason, they are not hard
dependencies. I don't see why this should be any different, assuming
the dependency is something that those vendors are for the most part
already packaging anyway.
> Does programming as such in psql is such a good idea?
I think we've pretty much crossed that line already with \if.
> ISTM that cpp-like capabilities (include, if, variables, some expressions)
> are somewhat both useful and enough for the limited use cases I have
> encountered. Similar languages are offered in other instances, such as
> readline inputrc or vim vimrc.
Yeah, but nobody would claim that cpp is a fun thing to program with,
and it's pretty clear from discussions here that some people are
squeezing last possible bit of juice out of every existing facility
and still wanting more. I don't think it's likely that adding one or
two additional simple constructs is going to be sufficient to keep
people from wanting more. I mean, I would have switched to Perl and
DBD::Pg rather than write some of the crazy psql scripts that have
been posted here, but we're not building PostgreSQL to meet my needs
particularly.
> I suggest to reuse pgbench expression engine, developed by Haas Robert:-)
Not a bad idea (though I'm sure there are other reasonable options, too).
> Generating a error message with ${foo:?} is nice, but what psql need is just
> a way to test whether a variable is defined or not.
I'm not saying we should slavishly follow bash, but don't confuse what
you need with what other people need. bash (well, sh, really) grew
that syntax for a reason, and it may be more than "there was this one
guy back in the seventies who wanted it, and ...".
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
> I suggest to reuse pgbench expression engine, developed by Haas Robert:-)
Not a bad idea (though I'm sure there are other reasonable options, too).
I don't want to stand in the way of any progress in getting expressions into \if and some subspecies of \set. But, assuming we don't get it into v10, our documentations currently says this about expressions:
Expressions that do not properly evaluate to true or false will generate a warning and be treated as false.
Expressions that do not properly evaluate to true or false will generate a warning and be treated as false, though that behavior is subject to change in future versions of psql.
That should guard us against people getting too attached to that behavior in the interim.
With that said, I'm starting to like the idea of not boxing ourselves into one type of expression. Maybe the first token could be the expression context with the expression to follow
The expression type we have now
\if true
"defined" is a context (or "mode") that expects one token that might be a psql varname
\if defined varname
"sql" is a context that treats the rest of the line as a SQL statement to the current connection, and looks at the first column of first row for "truth"
\if sql SELECT EXISTS(SELECT null FROM item WHERE manufacturer = 'Frobozz')
"pgbench" could invoke the pgbench expression engine.
\if pgbench <valid pgbench expression>
Anything else is treated as an external expression evaluator. If an expression has an unknown context "foo", check the ENV vars for EXPR_FOO, and pipe the remaining expression tokens to $PSQL_EXPR_FOO if it exists, and read the output of that for psql-boolean truth. I think having a context/mode token could allow us to have lots of pluggable expression types with minimal effort to psql itself.
"python" invokes a python interpreter (if PSQL_EXPR_PYTHON is defined, fails otherwise)
\if python print(:'varname' == 'Approved' or :'othervar' == 'Special')
which would echo
print('Approved' == 'Approved' or 'Regular' == 'Special')
print('Approved' == 'Approved' or 'Regular' == 'Special')
to python, which would give the response "True", which is true
likewise with "bash" (assuming PSQL_EXPR_BASH=bash)
\if bash expr :'varname' = 'Approved'
would echo
expr 'Approved' = 'Approved'
to bash, which would return 1, which would be true.
expr 'Approved' = 'Approved'
to bash, which would return 1, which would be true.
So we'd get all that, with only having to internally code for an external launcher, naked booleans, pgbench, defined, and I suppose we should have a negation
\if not <some context> <expression matching that context>
Which I guess would allow
\if not not not <some context> <expression>
For consistency, we might want to change the default context to require an explicit "bool", so
\if bool true
but if we want to do that, we should change it very soon.
tl;dr:
My proposal is:
* do the bare minimum of expression testing in psql (simple scalar truth, variable definition, negation)
* do platform independent client-only-expressions in pgbench mode
* allow inline \gset-ish expressions with sql-mode
* and allow for platform/install dependent expressions via PSQL_EXPR_* env vars.
My proposal is:
* do the bare minimum of expression testing in psql (simple scalar truth, variable definition, negation)
* do platform independent client-only-expressions in pgbench mode
* allow inline \gset-ish expressions with sql-mode
* and allow for platform/install dependent expressions via PSQL_EXPR_* env vars.
> I suggest to reuse pgbench expression engine, developed by Haas Robert:-)
Not a bad idea (though I'm sure there are other reasonable options, too).
I checked the pgbench code - and I think it can work well - just add logical operators and compare operators.
Don't need to create more complex language there.
> Generating a error message with ${foo:?} is nice, but what psql need is just
> a way to test whether a variable is defined or not.
I'm not saying we should slavishly follow bash, but don't confuse what
you need with what other people need. bash (well, sh, really) grew
that syntax for a reason, and it may be more than "there was this one
guy back in the seventies who wanted it, and ...".
2017-04-13 19:46 GMT+02:00 Corey Huinker <corey.huinker@gmail.com>:
> I suggest to reuse pgbench expression engine, developed by Haas Robert:-)
Not a bad idea (though I'm sure there are other reasonable options, too).I don't want to stand in the way of any progress in getting expressions into \if and some subspecies of \set. But, assuming we don't get it into v10, our documentations currently says this about expressions:Expressions that do not properly evaluate to true or false will generate a warning and be treated as false.We should probably amend that to say something about the potential future directions of expressions, perhaps this:Expressions that do not properly evaluate to true or false will generate a warning and be treated as false, though that behavior is subject to change in future versions of psql.That should guard us against people getting too attached to that behavior in the interim.With that said, I'm starting to like the idea of not boxing ourselves into one type of expression. Maybe the first token could be the expression context with the expression to followThe expression type we have now\if true"defined" is a context (or "mode") that expects one token that might be a psql varname\if defined varname"sql" is a context that treats the rest of the line as a SQL statement to the current connection, and looks at the first column of first row for "truth"\if sql SELECT EXISTS(SELECT null FROM item WHERE manufacturer = 'Frobozz')"pgbench" could invoke the pgbench expression engine.\if pgbench <valid pgbench expression>Anything else is treated as an external expression evaluator. If an expression has an unknown context "foo", check the ENV vars for EXPR_FOO, and pipe the remaining expression tokens to $PSQL_EXPR_FOO if it exists, and read the output of that for psql-boolean truth. I think having a context/mode token could allow us to have lots of pluggable expression types with minimal effort to psql itself."python" invokes a python interpreter (if PSQL_EXPR_PYTHON is defined, fails otherwise)\if python print(:'varname' == 'Approved' or :'othervar' == 'Special')which would echo
print('Approved' == 'Approved' or 'Regular' == 'Special')to python, which would give the response "True", which is truelikewise with "bash" (assuming PSQL_EXPR_BASH=bash)\if bash expr :'varname' = 'Approved'would echo
expr 'Approved' = 'Approved'
to bash, which would return 1, which would be true.So we'd get all that, with only having to internally code for an external launcher, naked booleans, pgbench, defined, and I suppose we should have a negation
\if not <some context> <expression matching that context>Which I guess would allow\if not not not <some context> <expression>
it looks like overengineering - I don't think so string comparation should be supported in two three languages.
Can live with it, but more prefer simple pgbench only language there
The one line commands is limited due readability
we can introduce "language blocks" where can be possible set some values. This looks really scary.
bash is supported already
probably you can write today
\if `basexpr`
For consistency, we might want to change the default context to require an explicit "bool", so\if bool truebut if we want to do that, we should change it very soon.tl;dr:
My proposal is:
* do the bare minimum of expression testing in psql (simple scalar truth, variable definition, negation)
* do platform independent client-only-expressions in pgbench mode
* allow inline \gset-ish expressions with sql-mode
* and allow for platform/install dependent expressions via PSQL_EXPR_* env vars.
Hello Robert,
>> Calling the server is already available:
>>
>> SELECT <whatever> AS varname \gset
>
> Sure, but people are going to want to do it inline with the \if.
Yes... and my changed opinion is that the answer to this approach should
be "no", only client side after if.
> Anything that can be done that way can also be done this way, but
> people will want it just to make the code look nicer.
That is what I thought, but I have not seen any sane/nice solution, and I
wish to avoid the opposite.
I now think that whether an expression is server side or client side
should be cristal clear, thus the rule "write a SELECT for server-side"
and "write a backslash command" for client-side is pretty attractive.
I do not think it is so bad: this is probably a rare occurence (psql spent
22 years without "\if") ; for server side expressions, it means that an
intermediate meaningful variable name must be thought for, which is not
necessarily a bad thing ; any significant SQL query would not fit cleanly
on one line, especially if made longer by a special prefix.
Finally, it does not bring any new semantics.
> I don't think we should restrict \if to be ONLY an SQL callout, but if
> people want that as an option, and I bet they do, then I think we should
> give it to them.
I changed my mind on this one. I think we should not for the reason stated
above.
Now it would be possible to have some compromise, and we could accept some
ugly prefix to mark server-side expressions after \if and no special
prefix would mean client-side, but the I would prefer if we avoid that.
>> I somewhat disagree: Does building postgres should depend on lua? I think
>> adding such a mandatory dependency would not be a good idea. If it is not
>> mandatory, then it would mean that psql could be compiled with or without
>> lua embedding, thus psql would not be dependable because features may or may
>> not be available when writing a "psql script".
>
> That's true, but you could say the same thing about SSL or NLS.
Hmmm. I'm not sure how NLS or SSL would show up inside a psql-script.
Another point I would like to make is that lua popularity is somewhere
between COBOL and Fortran on the Tiobe index.
> [...] I don't think it's likely that adding one or two additional simple
> constructs is going to be sufficient to keep people from wanting more.
I think that the next hurdle is high enough for not being jumped over in a
hurry: for getting a while, one need to re-execute the body over and over,
which requires holding the lines somewhere, meaning an significant
infrastructure which does not exists. So someone would have to need it
really badly to spend time on this one.
>> Generating a error message with ${foo:?} is nice, but what psql need is just
>> a way to test whether a variable is defined or not.
>
> I'm not saying we should slavishly follow bash, but don't confuse what
> you need with what other people need. bash (well, sh, really) grew
> that syntax for a reason, and it may be more than "there was this one
> guy back in the seventies who wanted it, and ...".
Sure. I think that the reason is to be able to write shell scripts without
bothering with undefined variables error handling. Lazy programmers:-)
--
Fabien.