Re: [HACKERS] Undefined psql variables - Mailing list pgsql-hackers

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.