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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
