Thread: Re: Variable substitution in psql backtick expansion

Fabien COELHO wrote:

> My 0.02 € about server-side expressions: ISTM that there is nothing
> obvious/easy to do to include these:
>
>   - 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.

\set differs in that it already exists in released versions,
so we have the backward compatibility to consider.
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.


>   - 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.
Queries can be as complex as necessary, they just have to fit in one line.

>   - how would error detection and handling work from a script?

The same as SELECT..\gset followed by \if, when the SELECT fails.

>   - 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.

>   - 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.
They are mutually exclusive for a single \if invocation.

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 :)

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.

>     (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.
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.
I agree that we don't need a full-featured built-in evaluator, 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.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

"Daniel Verite" <daniel@manitou-mail.org> writes:
> 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']

I really dislike this syntax proposal.  It would get us into having
to count nested brackets, and distinguish quoted from unquoted brackets,
and so on ... and for what?  It's not really better than
  \if sql select 1 from pg_catalog.pg_extension where extname='pgcrypto'

(ie, "\if sql ...text to send to server...").

If you're worried about shaving keystrokes, make the "select" be implicit.
That would be particularly convenient for the common case where you're
just trying to evaluate a boolean expression with no table reference.
        regards, tom lane

    Tom Lane wrote:

> I really dislike this syntax proposal.  It would get us into having
> to count nested brackets, and distinguish quoted from unquoted brackets,
> and so on ... and for what?  It's not really better than
>
>   \if sql select 1 from pg_catalog.pg_extension where extname='pgcrypto'
>
> (ie, "\if sql ...text to send to server...").

That's fine by me. The advantage of enclosing the query is to leave
open the possibility of accepting additional contents after the query,
like options (as \copy does), or other expression terms to combine
with the query's result. But we can live without that.

> If you're worried about shaving keystrokes, make the "select" be implicit.
> That would be particularly convenient for the common case where you're
> just trying to evaluate a boolean expression with no table reference.

These expressions look more natural without the SELECT keyword,
besides the size, but OTOH "\if sql 1 from table where expr"
looks awkward. Given an implicit select, I would prefer
"\if exists (select 1 from table where expr)" but now it's not shorter.

An advantage of prepending the SELECT automatically, is that it
would prevent people from abusing this syntax by putting
update/insert/delete or even DDL in there, imagining that this would
be a success/failure test for these operations.
Having these fail to execute in the first place, when called by \if,
seems like a sane failure mode that we would gain incidentally.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

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.

Hello Tom,

>>   \if [select current_setting('server_version_num')::int < 110000]
>
> I really dislike this syntax proposal.

> It would get us into having to count nested brackets, and distinguish 
> quoted from unquoted brackets, and so on ... and for what?  It's not 
> really better than
>
>   \if sql select 1 from pg_catalog.pg_extension where extname='pgcrypto'

Hmmm. On the positive side, it looks more expression-like, and it allows 
to think of handling a multi-line expression on day.

ISTM that the lexer already counts parentheses, so maybe using external 
parentheses might work without much effort?

> (ie, "\if sql ...text to send to server...").
>
> If you're worried about shaving keystrokes, make the "select" be implicit.
> That would be particularly convenient for the common case where you're
> just trying to evaluate a boolean expression with no table reference.

I'm looking for a solution to avoid the suggested "sql" prefix, because "I 
really dislike this proposal", as you put it.

-- 
Fabien.

> [...] but OTOH "\if sql 1 from table where expr" looks awkward. Given an 
> implicit select, I would prefer "\if exists (select 1 from table where 
> expr)" but now it's not shorter.

Possibly, but it is just an SQL expression, which looks good in the middle 
of an sql script.

> An advantage of prepending the SELECT automatically, is that it
> would prevent people from abusing this syntax by putting
> update/insert/delete or even DDL in there, imagining that this would
> be a success/failure test for these operations.

> Having these fail to execute in the first place, when called by \if,
> seems like a sane failure mode that we would gain incidentally.

Yes, it should be avoided.

-- 
Fabien.

Hello Pavel,

> The expression evaluation is interesting question, but there is a
> workaround - we can use \gset already.

Yes, that is a good point. It is a little bit inconvenient because it 
requires a dummy variable name each time for testing.
  SELECT whatever AS somename \gset  \if :somename

But this is an already functional solution to use server-side expressions, 
so there is no hurry.

> What is more important, because there is not any workaround, is detection
> if some variable exists or not.
>
> So possibilities
>
> 1. \if defined varname

Yep, and as Tom pointed it should handle NOT as well.

My issue with this version is that Lane Tom convinced me some time ago 
that client side expressions should look like SQL expressions, so that 
everything in the script is somehow in the same language. I think that he 
made a good point.

However "defined varname" is definitely not an SQL expression, so I do not 
find that "intuitive", for a given subjective idea of intuitive.

Then there is the question of simple comparisons, which would also make 
sense client-side, eg simple things like:
  \if :VERSION_NUM >= 110000

Should not need to be executed on the server.

> 2. \ifdefined or \ifdef varname

I think that we want to avoid that if possible, but it is a cpp-like 
possibility. This approach does not allow to support comparisons.

> 3. \if :?varname

Tom suggested that there is a special namespace problem with this option. 
I did not understand what is the actual issue.

> I like first two, and I can live with @3 - although it is not intuitive

For me @3 is neither worth nor better than the already existing :'varname' 
and :"varname" hacks, it is consistent with them, so it is just an 
extension of the existing approach.

It seems easy to implement because the substitution would be handled by 
the lexer, so there is no need for anything special like looking at the 
first or second word, rewinding, whatever.

Basically I agree with everything Tom suggested (indeed, some client side 
definition & comparison tests are really needed), but not with the 
proposed prefix syntax because it does not look clean and SQL.

-- 
Fabien.