pgbench - allow backslash-continuations in custom scripts

On 05/14/2015 12:10 PM, Fabien COELHO wrote:

Add backslash continuations to pgbench custom scripts.

The benefit of this approach is that it is upward compatible, and it is
also pretty simple to implement. The downside is that backslash
continuation is not the best syntax ever invented, but then you do not
have to use it if you do not like it.

The alternative would be to make semi-colon a mandatory end-of-line
marker, which would introduce an incompatibility and requires more
efforts to implement, including some kind of SQL-compatible lexer.

IMHO this approach is the best compromise.

I don't personally agree. I believe that it it worth breaking backwards
compatibility to support line breaks in pgbench statements, and that if
we're not going to do that, supporting \ continuations is of little value.

As someone who actively uses pgbench to write custom benchmarks, I need
to write queries which I can test. \ continuation does NOT work on the
psql command line, so that's useless for testing my queries; I still
have to reformat and troubleshoot. If we added \ continuation, I
wouldn't use it.

I think we should support line breaks, and require semicolons for
end-of-statement. Backwards-compatability in custom pgbench scripts is
not critical; pgbench scripts are neither used in produciton, nor used
in automated systems much that I know of.

I'm not clear on why we'd need a full SQL lexer.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Josh Berkus <josh@agliodbs.com> writes:

On 05/14/2015 12:10 PM, Fabien COELHO wrote:

Add backslash continuations to pgbench custom scripts.

I don't personally agree. I believe that it it worth breaking backwards
compatibility to support line breaks in pgbench statements, and that if
we're not going to do that, supporting \ continuations is of little value.

I tend to agree on that bottom line; having this be inconsistent with psql
does not seem like a win.

I'm not clear on why we'd need a full SQL lexer.

So you don't get fooled by semicolons embedded in string literals or
comments.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 06/19/2015 02:51 PM, Tom Lane wrote:

Josh Berkus <josh@agliodbs.com> writes:

On 05/14/2015 12:10 PM, Fabien COELHO wrote:

Add backslash continuations to pgbench custom scripts.

I don't personally agree. I believe that it it worth breaking backwards
compatibility to support line breaks in pgbench statements, and that if
we're not going to do that, supporting \ continuations is of little value.

I tend to agree on that bottom line; having this be inconsistent with psql
does not seem like a win.

I'm not clear on why we'd need a full SQL lexer.

So you don't get fooled by semicolons embedded in string literals or
comments.

I take it we ignore those now? I mean, personally, it wouldn't break
anything for me but since some other benhcmarks involve random text
generators ....

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I tend to agree on that bottom line; having this be inconsistent with psql
does not seem like a win.

I'm not clear on why we'd need a full SQL lexer.

So you don't get fooled by semicolons embedded in string literals or
comments.

I take it we ignore those now? I mean, personally, it wouldn't break
anything for me but since some other benhcmarks involve random text
generators ....

If backward compatibility is not an issue (I'm surprised:-), and failure
is acceptable in contrived cases, a simple implementation would be to
accumulate lines till one ends with ";\s*$",

Otherwise maybe the "states" management or the lexer are enough (in simple
quotes, in double quotes, in comment, in stuff), so this can implemented
without actually requiring another lexer in pgbench and be robust.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Josh,

Add backslash continuations to pgbench custom scripts.
[...]
IMHO this approach is the best compromise.

I don't personally agree. I believe that it it worth breaking backwards
compatibility to support line breaks in pgbench statements, and that if
we're not going to do that, supporting \ continuations is of little value.

As someone who actively uses pgbench to write custom benchmarks, I need
to write queries which I can test. \ continuation does NOT work on the
psql command line, so that's useless for testing my queries; I still
have to reformat and troubleshoot. If we added \ continuation, I
wouldn't use it.

I think we should support line breaks, and require semicolons for
end-of-statement. Backwards-compatability in custom pgbench scripts is
not critical; pgbench scripts are neither used in produciton, nor used
in automated systems much that I know of.

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

Also attached is a small pgbench script to test the feature.

Without a lexer it is possible to fool pgbench with somehow contrived
examples, say with:

SELECT 'hello;
world';

The ";" within the string will be considered as end-of-line.

Also, comments intermixed with sql on the same line would generate errors.

SELECT 1 -- one
+ 3;

Would fail, but comments on lines of their own are ok.

It may be argued that these are not a likely scripts and that this
behavior could be declared as a "feature" for keeping the code simple.

ISTM that it would be an overall improvement, but also the ;-termination
requirement breaks backward compatibility.

--
Fabien.

Fabien,

Without a lexer it is possible to fool pgbench with somehow contrived
examples, say with:

SELECT 'hello;
world';

The ";" within the string will be considered as end-of-line.

Also, comments intermixed with sql on the same line would generate errors.

SELECT 1 -- one
+ 3;

Would fail, but comments on lines of their own are ok.

It may be argued that these are not a likely scripts and that this
behavior could be declared as a "feature" for keeping the code simple.

Yeah, these seem pretty contrived. I would personally be OK with
breaking them.

ISTM that it would be an overall improvement, but also the ;-termination
requirement breaks backward compatibility.

Look, how many people in the world develop their own pgbench scripts?
And how many of those aren't on this list right now, reading this
thread? I expect I could count them on my fingers and toes.
Backwards-compatability for pgdump, pg_basebackup, initdb, etc. matters.
The worst case with pgbench is that we break two people's test scripts,
they read the release notes, and fix them.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

The worst case with pgbench is that we break two people's test scripts,
they read the release notes, and fix them.

Sure, I agree that breaking pgbench custom scripts compatibility is no big
deal, and having pgbench consistent with psql is useful.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 06/21/2015 01:37 PM, Fabien COELHO wrote:

The worst case with pgbench is that we break two people's test scripts,
they read the release notes, and fix them.

Sure, I agree that breaking pgbench custom scripts compatibility is no
big deal, and having pgbench consistent with psql is useful.

... apparently nobody disagrees ...

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Look, how many people in the world develop their own pgbench scripts?
And how many of those aren't on this list right now, reading this
thread? I expect I could count them on my fingers and toes.

I'm not against you break the backward compatibility of pgbench custom
scripts.

However I just want to let you know that PostgreSQL Enterprise
Consortium has been published couple of scripts along with reports on
evaluating PostgreSQL, which have been downloaded considerable
numbers.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 24, 2015 at 1:22 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 06/21/2015 01:37 PM, Fabien COELHO wrote:

The worst case with pgbench is that we break two people's test scripts,
they read the release notes, and fix them.

Sure, I agree that breaking pgbench custom scripts compatibility is no
big deal, and having pgbench consistent with psql is useful.

... apparently nobody disagrees ...

I'm fine re-punctuating my current scripts. And since pgbench doesn't have
to be version-matched to the server it connects to, people can just keep
using the older version if they want to against a new server.

Are there other breaking changes we have been wanting to make? If so,
should we try to get them all in during the same release?

Cheers,

Jeff

On Thu, Jun 25, 2015 at 10:51 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:

Look, how many people in the world develop their own pgbench scripts?
And how many of those aren't on this list right now, reading this
thread? I expect I could count them on my fingers and toes.

I'm not against you break the backward compatibility of pgbench custom
scripts.

However I just want to let you know that PostgreSQL Enterprise
Consortium has been published couple of scripts along with reports on
evaluating PostgreSQL, which have been downloaded considerable
numbers.

pgbench is a tool for dedicated to developers. Hence people who should
be able to fix scripts properly as long as we inform them by
documenting it in the release notes so I am not sure that this is
actually a problem.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I'm not against you break the backward compatibility of pgbench custom
scripts.

However I just want to let you know that PostgreSQL Enterprise
Consortium has been published couple of scripts along with reports on
evaluating PostgreSQL, which have been downloaded considerable
numbers.

pgbench is a tool for dedicated to developers. Hence people who should
be able to fix scripts properly as long as we inform them by
documenting it in the release notes so I am not sure that this is
actually a problem.

I'm not sure what you mean by "developers" here but if that means
people who are developing PostgreSQL itself, I am strongly against
"pgbench is a tool for dedicated to developers" concept. Pgbench has
been heavily used by users who want to measure PostgreSQL's
performance.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jun 26, 2015 at 9:01 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:

I'm not against you break the backward compatibility of pgbench custom
scripts.

However I just want to let you know that PostgreSQL Enterprise
Consortium has been published couple of scripts along with reports on
evaluating PostgreSQL, which have been downloaded considerable
numbers.

pgbench is a tool for dedicated to developers. Hence people who should
be able to fix scripts properly as long as we inform them by
documenting it in the release notes so I am not sure that this is
actually a problem.

I'm not sure what you mean by "developers" here but if that means
people who are developing PostgreSQL itself, I am strongly against
"pgbench is a tool for dedicated to developers" concept. Pgbench has
been heavily used by users who want to measure PostgreSQL's
performance.

I meant "people who can write SQL". Sorry for the misunderstanding.
Please do not take any offense ;)
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 06/21/2015 11:12 AM, Fabien COELHO wrote:

Hello Josh,

Add backslash continuations to pgbench custom scripts.
[...]
IMHO this approach is the best compromise.

I don't personally agree. I believe that it it worth breaking backwards
compatibility to support line breaks in pgbench statements, and that if
we're not going to do that, supporting \ continuations is of little value.

As someone who actively uses pgbench to write custom benchmarks, I need
to write queries which I can test. \ continuation does NOT work on the
psql command line, so that's useless for testing my queries; I still
have to reformat and troubleshoot. If we added \ continuation, I
wouldn't use it.

I think we should support line breaks, and require semicolons for
end-of-statement. Backwards-compatability in custom pgbench scripts is
not critical; pgbench scripts are neither used in produciton, nor used
in automated systems much that I know of.

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.

The home-grown lexer is missing e.g. dollar-quoting support, so this is
not be parsed correctly:

do $$
begin
...
end;
$$;

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.

Also worth noting that you can currently test so-called multi-statements
with pgbench, by putting multiple statements on a single line. 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

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2015-07-03 13:50:02 +0300, Heikki Linnakangas wrote:

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.

Yes.

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.

I'm pretty clearly in favor of doing correct lexing. I think we should
generalize that and make it reusable. psql has it's own hacked up
version already, there seems little point in having variedly good copies
around.

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

Not pretty imo. I could see including something esimpler, in addition to
the lexer, to allow sending multiple statements in one go.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I'm pretty clearly in favor of doing correct lexing. I think we should
generalize that and make it reusable. psql has it's own hacked up
version already, there seems little point in having variedly good copies
around.

I must admit that I do not know how to share lexer rules but have
different actions on them (psql vs sql parser vs ...), as the action code
is intrinsically intertwined with expressions. Maybe some hack is
possible. Having yet another SQL-lexer to maintain seems highly
undesirable, especially just for pgbench.

I could see including something esimpler, in addition to the lexer, to
allow sending multiple statements in one go.

Currently, probably

SELECT 1; SELECT 1;

Does 2 statements in one go, but it is on one line.

May by allowing both continuations and ";" at the same time:

-- two statements in one go
SELECT 1; \
SELECT 1;
-- next statement on it's own
SELECT
1;

Which could be reasonnably neat, and easy to implement.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

The home-grown lexer is missing e.g. dollar-quoting support, so this is not
be parsed correctly:

do $$
begin
...
end;
$$;

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.

Attached is a version which does that (I think), and a test script.

- backslash-commands can be \-continuated
- sql-commands may include $$-quotes and must end with a ';'
- double-dash comments and blank line are skipped.

Obviously it is still a non-lexer hack which can be easily defeated, but
ISTM that it handles non-contrived cases well. Anyway ISTM that dollar
quoting cannot be handle as such and simply by a lexer, it is really an
exception mechanism.

--
Fabien.

Fabien COELHO <coelho@cri.ensmp.fr> writes:

I'm pretty clearly in favor of doing correct lexing. I think we should
generalize that and make it reusable. psql has it's own hacked up
version already, there seems little point in having variedly good copies
around.

I must admit that I do not know how to share lexer rules but have
different actions on them (psql vs sql parser vs ...), as the action code
is intrinsically intertwined with expressions.

Obviously this is scope creep of the first magnitude, but ISTM that
it would be possible to share a lexer between psql and pgbench, since
in both of them the basic requirement is "break SQL commands apart and
identify newline-terminated backslash commands". If we're gonna break
pgbench's backwards compatibility anyway, there would be a whole lot
to be said for just going over to psql's input parsing rules, lock
stock 'n barrel; and this would be a good way to achieve that.

As it stands, psqlscan.l has some external dependencies on the rest of
psql, but we could perhaps refactor some of those away, and provide dummy
implementations to satisfy others (eg pgbench could provide a dummy
GetVariable() that just always returns NULL).

So I'm imagining symlinking psqlscan.l into src/bin/pgbench and using it
as-is (possibly after refactoring in psql). A possible issue is avoiding
unnecessary invocations of flex, though. Maybe symlinking the .c file
would work better.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers