Re: Variable substitution in psql backtick expansion

Hello Daniel,

>> - how would it work, both with \set ... and \if ...?
>
> The idea is that the need to have two command (a SELECT .. \gset
> followed by an \if) and a temporary variable in-between would be
> lifted by implementing a close equivalent in one command.
> It would behave essentially the same as the two commands.
>
> I don't see that \set must have to be involved in that improvement,
> although it could be indirectly, depending on what exactly we
> implement.

My point is that there was some idea expressed by Tom or Robert (?) at
some point that pgbench & psql should implement the same backslash
commands when appropriate.

Currently pgbench allows client-side expressions in \set, eg

\set d sqrt(1 + random(1000) * 17)

There is a patch pending to allow pgbench's \set a more developed
syntactic subset of pg SQL, including booleans, logical operator and such.
There is another pending patch which implements \gset and variant in
pgbench. If these patches get it, I would probably also implement \if
because it makes sense for benchmarking.

If psql's \if accepts expressions in psql, then it seems logical at some
level that this syntax would be more or less compatible with pgbench
expressions, somehow, and vice-versa. Hence my question. If I implement
\if in pgbench, I will trivially reuse the \set expression parser
developed by Robert, i.e. have "\if expression".

Now it could be decided that \set in psql stays simplistic because it is
not needed as much as it is with pgbench. Fine with me.

> \set differs in that it already exists in released versions,
> so we have the backward compatibility to consider.

Sure.

> With \if we are not bound by that, but what \if will be at feature
> freeze needs to be as convenient as we can make it in this release,
> and not block progress in v11 and later, as these future improvements
> will have to be backward-compatible against v10.

Sure.

>> - should it be just simple expressions or may it allow complex
>> queries?
>
> Let's imagine that psql would support a syntax like this:
> \if [select current_setting('server_version_num')::int < 110000]
> or
> \if [select 1 from pg_catalog.pg_extension where extname='pgcrypto']
>
> where by convention [ and ] enclose an SQL query that's assumed to
> return a single-row, single-column bool-ish value, and in which
> psql variables would be expanded, like they are now in
> backtick expressions.

Hmmm. Why not. or maybe a parenthesis? At least it looks less terrible
than a prefix thing like "\if sql".

> Queries can be as complex as necessary, they just have to fit in one line.

Hmmm. I'm not sure that the one-line constraint is desirable.

>> - how would error detection and handling work from a script?
>
> The same as SELECT..\gset followed by \if, when the SELECT fails.

There is a problem: AFAICS currently there is no way to test whether
something failed. When there was no \if, there was not way to test
anything, so no need to report issues. Now that there is a if, I think
that having some variable reporting would make sense, eg whether an error
occured, how many rows were affected, things like that.

>> - should it have some kind of continuation, as expressions are
>> likely to be longer than a constant?
>
> No, to me that falls into the issue of continuation of backslash
> commands in general, which is discussed separately.

Hmmm. If there is a begin/end syntactic marker, probably psql lexer could
handle waiting for the end of the expression.

>> - how would they interact with possible client-side expressions?
>
> In no way at all,in the sense that, either you choose to use an SQL
> evaluator, or a client-side evaluator (if it exists), or a backtick
> expression.

My strong desire is to avoid an explicit client vs server side evaluator
choice in the form of something like "\if sql ...". Maybe I could buy
brackets or parentheses, though.

> They are mutually exclusive for a single \if invocation.

Sure.

> Client-side evaluation would have to be called with a syntax
> that is unambiguously different. For example it could be
> \if (:SQLSTATE = '23505')
> \echo A record with the unique key :key_id already exists
> rollback
> \endif
>
> AFAIK we don't have :SQLSTATE yet, but we should :)

Yes, that is one of my points, error (and success) reporting through
variables become useful once you have a test available to do something
about it.

> Maybe the parentheses are not required, or we could require a different set
> of brackets to enclose an expression to evaluate internally, like {}, or
> whatever provided it's not ambiguous.

Hmmm. Yep, not ambiguous, and if possible transparent:-)

Another idea I'm toying with is that by default \if whatever... would
be an SQL server side expression, but for some client-side expressions
which could be filtered out by regex. It would be enough to catch
define and simple comparisons:

((not)? defined \w+|\d+ (=|<|>|<=|<>|!=|...) \d+)

That could be interpreted client side easily enough.

>> (on this point, I think that client-side is NOT needed for "psql".
>> It makes sense for "pgbench" in a benchmarking context where the
>> client must interact with the server in some special meaningful
>> way, but for simple scripting the performance requirement and
>> logic is not the same, so server-side could be enough).
>
> Client-side evaluation is useful for the example above, where
> you expect that you might be in a failed transaction, or even
> not connected, and you need to do quite simple tests.

Yep.

> We can do that already with backtick expansion and a shell evaluation
> command, but it's a bit heavy/inelegant and creates a dependency on
> external commands that is detrimental to portability.

Sure. I do not believe that backtick is a good solution.

> I agree that we don't need a full-featured built-in evaluator,

Yep.

> because the cases where it's needed will be rare enough that it's
> reasonable to have to defer to an external evaluator in those cases.

Hmmm.

--
Fabien.