Hello Heikki,
>>> I'm not clear on why we'd need a full SQL lexer.
>>
>> Attached is a "without lexer" version which does ;-terminated SQL commands
>> and \-continuated meta commands (may be useful for \shell and long \set
>> expressions).
>
> As Tom pointed out, you need the full lexer to do this correctly. You can
> argue that something that handles the most common cases is enough, but
> realistically, by the time you've handled all the common cases correctly,
> you've just re-invented the lexer.
Sure. I understand that part of Josh argument is that we are discussing
pgbench test scripts here, not real full-blown applications, and these are
expected to be quite basic, plain mostly SQL things.
> The home-grown lexer is missing e.g. dollar-quoting support, so this is not
> be parsed correctly:
>
> do $$
> begin
> ...
> end;
> $$;
Hmmm, good one, if indeed you want to use PL/pgSQL or even any arbitrary
language in a pgbench scripts... I would rather have created functions
(once, outside of pgbench) and would call them from the script, so that
would be a simple SELECT.
> That would be very nice to handle correctly, I've used DO-blocks in pgbench
> scripts many times, and it's a pain to have to write them in a single line.
Yep. With some languages I'm not sure that it is even possible.
> Also worth noting that you can currently test so-called multi-statements
> with pgbench, by putting multiple statements on a single line.
Yes indeed, behind the hood pgbench expects just one line, or you have to
change significantly the way statements are handled, which is way beyond
my initial intentions on this one, and this would mean quite a lot of
changes for more or less corner cases.
> Your patch seems to still do that, but if we went with a full-blown SQL
> lexer, they would probably be split into two statements.
> I think we should either bite the bullet and include the full SQL lexer in
> pgbench, or come up with some new syntax for marking the beginning and end of
> a statement. We could do something like bash here-documents or Postgres
> dollar-quoting, for example:
>
> \set ...
> select 1234; -- A statement on a single line, no change here
>
> -- Begin a multi-line statement
> \multi-line-statement END_TOKEN
> select *
> from complicated;
> END_TOKEN
I do not like the aesthetic of the above much. I really liked the idea of
simply writing SQL queries just as in psql.
So maybe just handling $$-quoting would be enough to handle reasonable
use-cases without troubling pgbench internal working too much? That would
be a simple local changes in the line reader.
--
Fabien.