Re: pgbench - allow backslash-continuations in custom scripts - Mailing list pgsql-hackers

From Fabien COELHO
Subject Re: pgbench - allow backslash-continuations in custom scripts
Date
Msg-id alpine.DEB.2.10.1507031317150.16176@sto
Whole thread Raw
In response to Re: pgbench - allow backslash-continuations in custom scripts  (Heikki Linnakangas <hlinnaka@iki.fi>)
List pgsql-hackers
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.



pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: Synch failover WAS: Support for N synchronous standby servers - take 2
Next
From: Amit Kapila
Date:
Subject: Re: assessing parallel-safety