Hello Stephen,
bitwise: <<, >>, &, |, ^/#, ~
comparisons: =/==, <>/!=, <, <=, >, >=
logical: and/&&, or/||, xor/^^, not, !
I'm not sure that we want to introduce operators '&&', '||' as logical
'and' and 'or' when those have specific meaning in PG which is different
(array overlaps and concatenation, specifically). I can certainly see
how it could be useful to be able to perform string concatenation in a
\set command in pgbench, for example..
Also, are we sure that we want to have both '=' and '==' for comparison?
The reason I added C operators is that Pg SQL has '!=' and a few others,
so once some are there I do not see why others should not be there as well
for consistency.
Now I agree that having operators which behave differently from psql to
pgbench is a bad idea.
In the attached patched I only included pg operators, plus "xor" which I
feel is missing and does not seem to harm.
[...] I certainly understand how the if() function could be useful
Indeed, some kind of "if" is needed, for instance to implement "tpc-b"
correctly.
, but I'm not entirely sure that the if(expression, true-result,
false-result) is the best approach. In particular, I recall cases with
other languages where that gets to be quite ugly when there are multiple
levels of if/else using that approach.
I think that pgbench is not a programming language, but an expression
language, which means that you have to provide a result, so you can only
have balanced if/then/else, which simplifies things a bit compared to
"full" language.
The SQL syntax for CASE is on the very heavy side and would be quite
complicated to implement in pgbench, so I rejected that and selected the
simplest possible function for the job.
Maybe the "if" function could be named something fancier to avoid possible
future clash on an eventual "if" keyword? Note that excel has an IF
function. Maybe "conditional"... However, as I do not envision pgbench
growing to a full language, I would be fine keeping "if" as it is.
The table should really have a row per operator, which is what we do in
pretty much all of the core documention. [...]
Ok for one line per operator.
The question which was brought up about having a line-continuation
(eg: '\') character would be useful,
:-) I submitted a patch for that, which got rejected and resulted in Tom
making pgbench share psql lexer. This is a good thing, although the
continuation extension was lost in the process. Also this means that now
such logic would probably be shared with psql, not necessarily a bad thing
either, but clearly material for another patch.
which really makes me wonder if we shouldn't try to come up with a way
to create functions in a pgbench script that can later be used in a \set
command.
I do not think that pgbench script is a good target to define functions,
because the script is implicitely in a very large loop which is executing
it over and over again. I do not think that it would make much sense to
redefine functions on each transaction... So my opinion is that without a
compeling use case, I would avoid such a feature, which would need some
careful thinking on the design side, and the implementation of which is
non trivial.
With such an approach, we could have proper control structures
for conditionals (if/else), loops (while/for), etc, and not complicate
the single-statement set of operators with those constructs.
If you want control, you can already use a DO & PL/pgsql script, although
it is interpreted server-side. Yet again, I'm not convince that pgbench is
material for such features, and it would take a compeling use case for me
to add such a thing. Moreover, the currently simple internal data
structures and executor would have to be profoundly reworked and extended
to handle a full language and functions.
Lastly, we should really add some regression tests to pgbench. We
already have the start of a TAP test script which it looks like it
wouldn't be too hard to add on to.
I agree that pgbench should be tested systematically. I think that this is
not limited to these functions and operators but a more general and
desirable feature, thus is really material for another patch.
Attached version changes:
- removes C operators not present in psql
- document operators one per line
--
Fabien.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 285608d..c29966f 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -821,9 +821,8 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
The expression may contain integer constants such as <literal>5432</>,
double constants such as <literal>3.14159</>,
references to variables <literal>:</><replaceable>variablename</>,
- unary operators (<literal>+</>, <literal>-</>) and binary operators
- (<literal>+</>, <literal>-</>, <literal>*</>, <literal>/</>,
- <literal>%</>) with their usual precedence and associativity,
+ <link linkend="pgbench-builtin-operators">operators</>
+ with their usual precedence and associativity,
<link linkend="pgbench-builtin-functions">function calls</>, and
parentheses.
</para>
@@ -909,6 +908,171 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
</variablelist>
</refsect2>
+ <refsect2 id="pgbench-builtin-operators">
+ <title>Built-In Operators</title>
+
+ <para>
+ The arithmetic, bitwise, comparison and logical operators listed in
+ <xref linkend="pgbench-operators"> are built into <application>pgbench</>
+ and may be used in expressions appearing in
+ <link linkend="pgbench-metacommand-set"><literal>\set</literal></link>.
+ </para>
+
+ <table id="pgbench-operators">
+ <title>pgbench Operators by increasing precedence</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Operator</>
+ <entry>Description</>
+ <entry>Example</>
+ <entry>Result</>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>or</></>
+ <entry>logical or</>
+ <entry><literal>5 or 0</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>xor</></>
+ <entry>logical exclusive-or</>
+ <entry><literal>4 xor 0</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>and</></>
+ <entry>logical and</>
+ <entry><literal>3 and 0</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal>|</></>
+ <entry>integer bitwise OR</>
+ <entry><literal>1 | 2</></>
+ <entry><literal>3</></>
+ </row>
+ <row>
+ <entry><literal>#</></>
+ <entry>integer bitwise XOR</>
+ <entry><literal>1 # 3</></>
+ <entry><literal>2</></>
+ </row>
+ <row>
+ <entry><literal>&</></>
+ <entry>integer bitwise AND</>
+ <entry><literal>1 & 3</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>=</></>
+ <entry>is equal</>
+ <entry><literal>5 = 4</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal><></></>
+ <entry>is not equal</>
+ <entry><literal>5 <> 4</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>!=</></>
+ <entry>is not equal</>
+ <entry><literal>5 != 5</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal><</></>
+ <entry>lower than</>
+ <entry><literal>5 < 4</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal><=</></>
+ <entry>lower or equal</>
+ <entry><literal>5 <= 4</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal>></></>
+ <entry>greater than</>
+ <entry><literal>5 > 4</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>>=</></>
+ <entry>greater or equal</>
+ <entry><literal>5 >= 4</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal><<</></>
+ <entry>left shift</>
+ <entry><literal>1 << 2</></>
+ <entry><literal>4</></>
+ </row>
+ <row>
+ <entry><literal>>></></>
+ <entry>right shift</>
+ <entry><literal>8 >> 2</></>
+ <entry><literal>2</></>
+ </row>
+ <row>
+ <entry><literal>+</></>
+ <entry>addition</>
+ <entry><literal>5 + 4</></>
+ <entry><literal>9</></>
+ </row>
+ <row>
+ <entry><literal>-</></>
+ <entry>substraction</>
+ <entry><literal>3 - 2.0</></>
+ <entry><literal>1.0</></>
+ </row>
+ <row>
+ <entry><literal>*</></>
+ <entry>multiplication</>
+ <entry><literal>5 * 4</></>
+ <entry><literal>20</></>
+ </row>
+ <row>
+ <entry><literal>/</></>
+ <entry>division (integer truncates the results)</>
+ <entry><literal>5 / 3</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>%</></>
+ <entry>modulo</>
+ <entry><literal>3 % 2</></>
+ <entry><literal>1</></>
+ </row>
+ <row>
+ <entry><literal>not</></>
+ <entry>logical NOT</>
+ <entry><literal>not 7</></>
+ <entry><literal>0</></>
+ </row>
+ <row>
+ <entry><literal>~</></>
+ <entry>bitwise NOT</>
+ <entry><literal>~ 1</></>
+ <entry><literal>-2</></>
+ </row>
+ <row>
+ <entry><literal>-</></>
+ <entry>opposite</>
+ <entry><literal>- 2.0</></>
+ <entry><literal>-2.0</></>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </refsect2>
+
<refsect2 id="pgbench-builtin-functions">
<title>Built-In Functions</title>
@@ -955,6 +1119,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
<entry><literal>5432.0</></>
</row>
<row>
+ <entry><literal><function>exp(<replaceable>x</>)</></></>
+ <entry>double</>
+ <entry>exponential</>
+ <entry><literal>exp(1.0)</></>
+ <entry><literal>2.718281828459045</></>
+ </row>
+ <row>
<entry><literal><function>greatest(<replaceable>a</> [, <replaceable>...</> ] )</></></>
<entry>double if any <replaceable>a</> is double, else integer</>
<entry>largest value among arguments</>
@@ -962,6 +1133,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
<entry><literal>5</></>
</row>
<row>
+ <entry><literal><function>if(<replaceable>c</>,<replaceable>e1</>,<replaceable>e2</>)</></></>
+ <entry>same as <replaceable>e*</></>
+ <entry>if <replaceable>c</> is not zero then <replaceable>e1</> else <replaceable>e2</></>
+ <entry><literal>if(0,1.0,2.0)</></>
+ <entry><literal>2.0</></>
+ </row>
+ <row>
<entry><literal><function>int(<replaceable>x</>)</></></>
<entry>integer</>
<entry>cast to int</>
@@ -976,6 +1154,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
<entry><literal>2.1</></>
</row>
<row>
+ <entry><literal><function>ln(<replaceable>x</>)</></></>
+ <entry>double</>
+ <entry>natural logarithm</>
+ <entry><literal>ln(2.718281828459045)</></>
+ <entry><literal>1.0</></>
+ </row>
+ <row>
<entry><literal><function>pi()</></></>
<entry>double</>
<entry>value of the constant PI</>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 0cc665b..f8dbbaf 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -52,11 +52,21 @@ static PgBenchExpr *make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *
%type <str> VARIABLE FUNCTION
%token INTEGER_CONST DOUBLE_CONST VARIABLE FUNCTION
-
-/* Precedence: lowest to highest */
+%token AND_OP OR_OP XOR_OP NE_OP LE_OP GE_OP LS_OP RS_OP
+
+/* Precedence: lowest to highest, taken from C */
+%left OR_OP
+%left XOR_OP
+%left AND_OP
+%left '|'
+%left '#'
+%left '&'
+%left '=' NE_OP
+%left '<' '>' LE_OP GE_OP
+%left LS_OP RS_OP
%left '+' '-'
%left '*' '/' '%'
-%right UMINUS
+%right UNARY
%%
@@ -68,14 +78,32 @@ elist: { $$ = NULL; }
;
expr: '(' expr ')' { $$ = $2; }
- | '+' expr %prec UMINUS { $$ = $2; }
- | '-' expr %prec UMINUS { $$ = make_op(yyscanner, "-",
+ | '+' expr %prec UNARY { $$ = $2; }
+ | '-' expr %prec UNARY { $$ = make_op(yyscanner, "-",
make_integer_constant(0), $2); }
+ | '~' expr %prec UNARY { $$ = make_op(yyscanner, "#",
+ make_integer_constant(-1), $2); }
+ | '!' expr %prec UNARY { $$ = make_op(yyscanner, "^^",
+ make_integer_constant(1), $2); }
| expr '+' expr { $$ = make_op(yyscanner, "+", $1, $3); }
| expr '-' expr { $$ = make_op(yyscanner, "-", $1, $3); }
| expr '*' expr { $$ = make_op(yyscanner, "*", $1, $3); }
| expr '/' expr { $$ = make_op(yyscanner, "/", $1, $3); }
| expr '%' expr { $$ = make_op(yyscanner, "%", $1, $3); }
+ | expr '<' expr { $$ = make_op(yyscanner, "<", $1, $3); }
+ | expr LE_OP expr { $$ = make_op(yyscanner, "<=", $1, $3); }
+ | expr '>' expr { $$ = make_op(yyscanner, "<", $3, $1); }
+ | expr GE_OP expr { $$ = make_op(yyscanner, "<=", $3, $1); }
+ | expr '=' expr { $$ = make_op(yyscanner, "=", $1, $3); }
+ | expr NE_OP expr { $$ = make_op(yyscanner, "<>", $1, $3); }
+ | expr '&' expr { $$ = make_op(yyscanner, "&", $1, $3); }
+ | expr '|' expr { $$ = make_op(yyscanner, "|", $1, $3); }
+ | expr '#' expr { $$ = make_op(yyscanner, "#", $1, $3); }
+ | expr LS_OP expr { $$ = make_op(yyscanner, "<<", $1, $3); }
+ | expr RS_OP expr { $$ = make_op(yyscanner, ">>", $1, $3); }
+ | expr AND_OP expr { $$ = make_op(yyscanner, "&&", $1, $3); }
+ | expr OR_OP expr { $$ = make_op(yyscanner, "||", $1, $3); }
+ | expr XOR_OP expr { $$ = make_op(yyscanner, "^^", $1, $3); }
| INTEGER_CONST { $$ = make_integer_constant($1); }
| DOUBLE_CONST { $$ = make_double_constant($1); }
| VARIABLE { $$ = make_variable($1); }
@@ -177,6 +205,12 @@ static const struct
"sqrt", 1, PGBENCH_SQRT
},
{
+ "ln", 1, PGBENCH_LN
+ },
+ {
+ "exp", 1, PGBENCH_EXP
+ },
+ {
"int", 1, PGBENCH_INT
},
{
@@ -191,6 +225,45 @@ static const struct
{
"random_exponential", 3, PGBENCH_RANDOM_EXPONENTIAL
},
+ {
+ "&&", 2, PGBENCH_AND
+ },
+ {
+ "||", 2, PGBENCH_OR
+ },
+ {
+ "^^", 2, PGBENCH_XOR
+ },
+ {
+ "&", 2, PGBENCH_BITAND
+ },
+ {
+ "|", 2, PGBENCH_BITOR
+ },
+ {
+ "#", 2, PGBENCH_BITXOR
+ },
+ {
+ "<<", 2, PGBENCH_LSHIFT
+ },
+ {
+ ">>", 2, PGBENCH_RSHIFT
+ },
+ {
+ "=", 2, PGBENCH_EQ
+ },
+ {
+ "<>", 2, PGBENCH_NE
+ },
+ {
+ "<=", 2, PGBENCH_LE
+ },
+ {
+ "<", 2, PGBENCH_LT
+ },
+ {
+ "if", 3, PGBENCH_IF
+ },
/* keep as last array element */
{
NULL, 0, 0
diff --git a/src/bin/pgbench/exprscan.l b/src/bin/pgbench/exprscan.l
index 20891a3..f62740f 100644
--- a/src/bin/pgbench/exprscan.l
+++ b/src/bin/pgbench/exprscan.l
@@ -112,7 +112,24 @@ newline [\n]
"-" { return '-'; }
"*" { return '*'; }
"/" { return '/'; }
-"%" { return '%'; }
+"%" { return '%'; } /* C version, also in psql */
+"=" { return '='; }
+"<>" { return NE_OP; }
+"!=" { return NE_OP; } /* C version, also in psql */
+"<=" { return LE_OP; }
+">=" { return GE_OP; }
+"<<" { return LS_OP; }
+">>" { return RS_OP; }
+"<" { return '<'; }
+">" { return '>'; }
+"|" { return '|'; }
+"&" { return '&'; }
+"#" { return '#'; }
+"~" { return '~'; }
+"and" { return AND_OP; }
+"or" { return OR_OP; }
+"xor" { return XOR_OP; } /* should exist */
+"not" { return '!'; }
"(" { return '('; }
")" { return ')'; }
"," { return ','; }
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index d44cfda..d15c94e 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -1262,6 +1262,19 @@ coerceToDouble(PgBenchValue *pval, double *dval)
}
}
+static bool coerceToBool(PgBenchValue *pval)
+{
+ if (pval->type == PGBT_DOUBLE)
+ {
+ return pval->u.dval != 0.0;
+ }
+ else
+ {
+ Assert(pval->type == PGBT_INT);
+ return pval->u.ival != 0;
+ }
+}
+
/* assign an integer value */
static void
setIntValue(PgBenchValue *pv, int64 ival)
@@ -1313,6 +1326,10 @@ evalFunc(TState *thread, CState *st,
case PGBENCH_MUL:
case PGBENCH_DIV:
case PGBENCH_MOD:
+ case PGBENCH_EQ:
+ case PGBENCH_NE:
+ case PGBENCH_LE:
+ case PGBENCH_LT:
{
PgBenchValue *lval = &vargs[0],
*rval = &vargs[1];
@@ -1348,6 +1365,22 @@ evalFunc(TState *thread, CState *st,
setDoubleValue(retval, ld / rd);
return true;
+ case PGBENCH_EQ:
+ setIntValue(retval, ld == rd);
+ return true;
+
+ case PGBENCH_NE:
+ setIntValue(retval, ld != rd);
+ return true;
+
+ case PGBENCH_LE:
+ setIntValue(retval, ld <= rd);
+ return true;
+
+ case PGBENCH_LT:
+ setIntValue(retval, ld < rd);
+ return true;
+
default:
/* cannot get here */
Assert(0);
@@ -1376,6 +1409,22 @@ evalFunc(TState *thread, CState *st,
setIntValue(retval, li * ri);
return true;
+ case PGBENCH_EQ:
+ setIntValue(retval, li == ri);
+ return true;
+
+ case PGBENCH_NE:
+ setIntValue(retval, li != ri);
+ return true;
+
+ case PGBENCH_LE:
+ setIntValue(retval, li <= ri);
+ return true;
+
+ case PGBENCH_LT:
+ setIntValue(retval, li < ri);
+ return true;
+
case PGBENCH_DIV:
case PGBENCH_MOD:
if (ri == 0)
@@ -1416,7 +1465,57 @@ evalFunc(TState *thread, CState *st,
}
}
- /* no arguments */
+ /* integer operators */
+ case PGBENCH_BITAND:
+ case PGBENCH_BITOR:
+ case PGBENCH_BITXOR:
+ case PGBENCH_LSHIFT:
+ case PGBENCH_RSHIFT:
+ {
+ int64 li, ri;
+
+ if (!coerceToInt(&vargs[0], &li) || !coerceToInt(&vargs[1], &ri))
+ return false;
+
+ if (func == PGBENCH_BITAND)
+ setIntValue(retval, li & ri);
+ else if (func == PGBENCH_BITOR)
+ setIntValue(retval, li | ri);
+ else if (func == PGBENCH_BITXOR)
+ setIntValue(retval, li ^ ri);
+ else if (func == PGBENCH_LSHIFT)
+ setIntValue(retval, li << ri);
+ else if (func == PGBENCH_RSHIFT)
+ setIntValue(retval, li >> ri);
+ else /* cannot get here */
+ Assert(0);
+
+ return true;
+ }
+
+ /* logical operators */
+ case PGBENCH_AND:
+ case PGBENCH_OR:
+ case PGBENCH_XOR:
+ {
+ bool lb, rb;
+
+ lb = coerceToBool(&vargs[0]);
+ rb = coerceToBool(&vargs[1]);
+
+ if (func == PGBENCH_AND)
+ setIntValue(retval, lb && rb);
+ else if (func == PGBENCH_OR)
+ setIntValue(retval, lb || rb);
+ else if (func == PGBENCH_XOR)
+ setIntValue(retval, lb ^ rb);
+ else /* cannot get here */
+ Assert(0);
+
+ return true;
+ }
+
+ /* no arguments */
case PGBENCH_PI:
setDoubleValue(retval, M_PI);
return true;
@@ -1470,6 +1569,8 @@ evalFunc(TState *thread, CState *st,
/* 1 double argument */
case PGBENCH_DOUBLE:
case PGBENCH_SQRT:
+ case PGBENCH_LN:
+ case PGBENCH_EXP:
{
double dval;
@@ -1480,6 +1581,11 @@ evalFunc(TState *thread, CState *st,
if (func == PGBENCH_SQRT)
dval = sqrt(dval);
+ else if (func == PGBENCH_LN)
+ dval = log(dval);
+ else if (func == PGBENCH_EXP)
+ dval = exp(dval);
+ /* else is cast: do nothing */
setDoubleValue(retval, dval);
return true;
@@ -1631,6 +1737,14 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_IF:
+ Assert(nargs == 3);
+ if (coerceToBool(&vargs[0]))
+ *retval = vargs[1];
+ else
+ *retval = vargs[2];
+ return true;
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index ab0f822..5306c40 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -73,9 +73,24 @@ typedef enum PgBenchFunction
PGBENCH_DOUBLE,
PGBENCH_PI,
PGBENCH_SQRT,
+ PGBENCH_LN,
+ PGBENCH_EXP,
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
- PGBENCH_RANDOM_EXPONENTIAL
+ PGBENCH_RANDOM_EXPONENTIAL,
+ PGBENCH_AND,
+ PGBENCH_OR,
+ PGBENCH_XOR,
+ PGBENCH_BITAND,
+ PGBENCH_BITOR,
+ PGBENCH_BITXOR,
+ PGBENCH_LSHIFT,
+ PGBENCH_RSHIFT,
+ PGBENCH_EQ,
+ PGBENCH_NE,
+ PGBENCH_LE,
+ PGBENCH_LT,
+ PGBENCH_IF
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
