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.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index a808546..7990564 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -697,11 +697,15 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
</para>
<para>
- The format of a script file is one SQL command per line; multiline
- SQL commands are not supported. Empty lines and lines beginning with
- <literal>--</> are ignored. Script file lines can also be
- <quote>meta commands</>, which are interpreted by <application>pgbench</>
- itself, as described below.
+ The format of a script file is composed of lines which are each either
+ one SQL command or one <quote>meta command</> interpreted by
+ <application>pgbench</> itself, as described below.
+ Meta-commands can be spread over multiple lines using backslash
+ (<literal>\</>) continuations, in which case the set of continuated
+ lines is considered as just one line.
+ SQL commands may be spead over several lines and must be
+ <literal>;</>-terminated.
+ Empty lines and lines beginning with <literal>--</> are ignored.
</para>
<para>
@@ -769,7 +773,9 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
Examples:
<programlisting>
\set ntellers 10 * :scale
-\set aid (1021 * :aid) % (100000 * :scale) + 1
+-- update an already defined aid:
+\set aid \
+ (1021 * :aid) % (100000 * :scale) + 1
</programlisting></para>
</listitem>
</varlistentry>
@@ -932,11 +938,15 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
BEGIN;
-UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
+UPDATE pgbench_accounts
+ SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
-UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
-UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
-INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
+UPDATE pgbench_tellers
+ SET tbalance = tbalance + :delta WHERE tid = :tid;
+UPDATE pgbench_branches
+ SET bbalance = bbalance + :delta WHERE bid = :bid;
+INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
+ VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
</programlisting>
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 6f35db4..ff41d91 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -2450,7 +2450,9 @@ process_commands(char *buf, const char *source, const int lineno)
}
/*
- * Read a line from fd, and return it in a malloc'd buffer.
+ * Read a possibly \-continuated (for backslash commands) or ;-terminated
+ * (for SQL statements) lines from fd, and return it in a malloc'd buffer.
+ *
* Return NULL at EOF.
*
* The buffer will typically be larger than necessary, but we don't care
@@ -2463,6 +2465,8 @@ read_line_from_file(FILE *fd)
char *buf;
size_t buflen = BUFSIZ;
size_t used = 0;
+ bool is_sql_statement = false;
+ bool is_backslash_command = false;
buf = (char *) palloc(buflen);
buf[0] = '\0';
@@ -2471,13 +2475,87 @@ read_line_from_file(FILE *fd)
{
size_t thislen = strlen(tmpbuf);
+ /* coldly skip comments and empty lines */
+ {
+ int i = 0;
+
+ while (i < thislen && isspace(tmpbuf[i]))
+ i++;
+
+ if (tmpbuf[i] == '\0') /* blank */
+ continue;
+
+ if (tmpbuf[i] == '-' && tmpbuf[i+1] == '-') /* comment */
+ continue;
+ }
+
/* Append tmpbuf to whatever we had already */
memcpy(buf + used, tmpbuf, thislen + 1);
used += thislen;
- /* Done if we collected a newline */
- if (thislen > 0 && tmpbuf[thislen - 1] == '\n')
- break;
+ if (!is_backslash_command && !is_sql_statement)
+ {
+ /* determined what the current line is */
+ int i = 0;
+
+ while (i < thislen && isspace(tmpbuf[i]))
+ i++;
+
+ if (tmpbuf[i] == '\\')
+ is_backslash_command = true;
+ else if (tmpbuf[i] != '\0')
+ is_sql_statement = true;
+ }
+
+ /* If we collected a newline */
+ if (used > 0 && buf[used - 1] == '\n')
+ {
+ if (is_backslash_command)
+ {
+ /* Handle simple \-continuations */
+ if (used >= 2 && buf[used - 2] == '\\')
+ {
+ buf[used - 2] = '\0';
+ used -= 2;
+ }
+ else if (used >= 3 && buf[used - 2] == '\r' &&
+ buf[used - 3] == '\\')
+ {
+ buf[used - 3] = '\0';
+ used -= 3;
+ }
+ else
+ /* Else we are done */
+ break;
+ }
+ else if (is_sql_statement)
+ {
+ /* look for a terminating ";" */
+ int i = 2;
+
+ /* backward skip blanks */
+ while (used-i >= 0 && isspace(buf[used-i]))
+ i++;
+
+ if (used-i >= 0 && buf[used-i] == ';')
+ break;
+ else
+ {
+ /* scratch newline because process_commands approach to
+ * parsing is simplistic and expects just one line.
+ */
+ if (buf[used-1] == '\n')
+ buf[used-1] = ' ';
+ if (used >= 2 && buf[used-2] == '\r')
+ {
+ buf[used-2] = ' ';
+ buf[used-1] = '\0';
+ used --;
+ }
+ }
+ }
+ /* else it was a blank line */
+ }
/* Else, enlarge buf to ensure we can append next bufferload */
buflen += BUFSIZ;
test.sql
Description: application/sql
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
