Hello Stephen,
I'm pretty sure we should hold off on adding 'xor' [...]
So I removed "xor" in the attached version.
[...] I certainly understand how the if() function could be useful
Indeed, some kind of "if" is needed, for instance to implement
"tpc-b" correctly.
That's an interesting point.. Have you thought about ripping out the
built-in TPC-B-like functionality of pgbench and making that into a
script instead? Doing so would likely point out places where we need to
add functionality or cases which aren't handled very well. [...]
My point is not that pgbench should do tcp-b-real by default, but that it
should be possible to do it, and currently this is not possible. People
rely on what pgbench is doing so it should not be (significantly) changed,
at least in its default behavior.
I'm not quite sure that I follow why you feel that CASE would be too
difficult to implement here..?
Obviously, everything is possible, it just takes more time. I've added the
generic case expression syntax in the attached version.
[...] If we're going to replace the built-in TPC-B functionality
As stated above, I do *NOT* want to replace the existing functionality, I
just want to make pgbench able to do more in a sensible & useful way for
benchmarking.
I'm not currently planing to add user functions and control structures
outside of expressions in pgbench without a very strong user case.
[...] Perhaps it'd be difficult to rework pgbench to support it, but I
do think it's something we should at least be considering and making
sure we don't wall ourselves off from implementing in the future. Most
scripting languages do support functions of one form or another.
Sure.
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.
I don't agree with this- at a minimum, this patch should add regression
tests for the features that it's adding.
This has not been the case with the previous dozens of patches about
pgbench, but maybe it should start somewhere:-).
There was already one TAP script for pgbench (for testing concurrent oid
insertion, not really a pgbench functionnality), I added another one which
focusses on expressions.
Changes in v6:
- remove xor operator
- remove if function
- make keywords case insensitive (more like SQL)
- add generic case expression syntax
(note that the typing is the same strange one as pg, i.e.
CASE WHEN (RANDOM() > 0.5) THEN 1 ELSE 1.0 END;
can be either an int or a double, depending, this is not statically typed...
- add TAP test about pgbench expressions
--
Fabien.diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 285608d..75575ad 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -821,11 +821,11 @@ 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-functions">function calls</>, and
- parentheses.
+ <link linkend="pgbench-builtin-operators">operators</>
+ with their usual precedence and associativity,
+ <link linkend="pgbench-builtin-functions">function calls</>,
+ SQL <literal>CASE</> generic conditional expressions
+ and parentheses.
</para>
<para>
@@ -909,6 +909,165 @@ 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>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 +1114,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 +1128,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 +1149,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..3487958 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -24,8 +24,10 @@ static PgBenchExpr *make_double_constant(double dval);
static PgBenchExpr *make_variable(char *varname);
static PgBenchExpr *make_op(yyscan_t yyscanner, const char *operator,
PgBenchExpr *lexpr, PgBenchExpr *rexpr);
+static PgBenchExpr *make_uop(yyscan_t yyscanner, const char *operator, PgBenchExpr *expr);
static int find_func(yyscan_t yyscanner, const char *fname);
static PgBenchExpr *make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *args);
+static PgBenchExpr *make_case(yyscan_t yyscanner, PgBenchExprList *when_then_list, PgBenchExpr *else_part);
%}
@@ -45,18 +47,28 @@ static PgBenchExpr *make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *
PgBenchExprList *elist;
}
-%type <elist> elist
-%type <expr> expr
+%type <elist> elist when_then_list
+%type <expr> expr case_control
%type <ival> INTEGER_CONST function
%type <dval> DOUBLE_CONST
%type <str> VARIABLE FUNCTION
%token INTEGER_CONST DOUBLE_CONST VARIABLE FUNCTION
-
-/* Precedence: lowest to highest */
+%token AND_OP OR_OP NE_OP LE_OP GE_OP LS_OP RS_OP
+%token CASE_KW WHEN_KW THEN_KW ELSE_KW END_KW
+
+/* Precedence: lowest to highest, taken from C */
+%left OR_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,20 +80,45 @@ 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_uop(yyscanner, "!", $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); }
| INTEGER_CONST { $$ = make_integer_constant($1); }
| DOUBLE_CONST { $$ = make_double_constant($1); }
| VARIABLE { $$ = make_variable($1); }
| function '(' elist ')' { $$ = make_func(yyscanner, $1, $3); }
+ | case_control { $$ = $1; }
;
+when_then_list:
+ when_then_list WHEN_KW expr THEN_KW expr { $$ = make_elist($5, make_elist($3, $1)); }
+ | WHEN_KW expr THEN_KW expr { $$ = make_elist($4, make_elist($2, NULL)); }
+
+case_control:
+ CASE_KW when_then_list END_KW { $$ = make_case(yyscanner, $2, NULL); }
+ | CASE_KW when_then_list ELSE_KW expr END_KW { $$ = make_case(yyscanner, $2, $4); }
+
function: FUNCTION { $$ = find_func(yyscanner, $1); pg_free($1); }
;
@@ -119,6 +156,7 @@ make_variable(char *varname)
return expr;
}
+/* binary operators */
static PgBenchExpr *
make_op(yyscan_t yyscanner, const char *operator,
PgBenchExpr *lexpr, PgBenchExpr *rexpr)
@@ -127,6 +165,13 @@ make_op(yyscan_t yyscanner, const char *operator,
make_elist(rexpr, make_elist(lexpr, NULL)));
}
+/* unary operator */
+static PgBenchExpr *
+make_uop(yyscan_t yyscanner, const char *operator, PgBenchExpr *expr)
+{
+ return make_func(yyscanner, find_func(yyscanner, operator), make_elist(expr, NULL));
+}
+
/*
* List of available functions:
* - fname: function name
@@ -177,6 +222,12 @@ static const struct
"sqrt", 1, PGBENCH_SQRT
},
{
+ "ln", 1, PGBENCH_LN
+ },
+ {
+ "exp", 1, PGBENCH_EXP
+ },
+ {
"int", 1, PGBENCH_INT
},
{
@@ -191,6 +242,45 @@ static const struct
{
"random_exponential", 3, PGBENCH_RANDOM_EXPONENTIAL
},
+ {
+ "&&", 2, PGBENCH_AND
+ },
+ {
+ "||", 2, PGBENCH_OR
+ },
+ {
+ "!", 1, PGBENCH_NOT
+ },
+ {
+ "&", 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
+ },
+ {
+ "!case_end", -2, PGBENCH_CASE
+ },
/* keep as last array element */
{
NULL, 0, 0
@@ -279,6 +369,14 @@ make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *args)
elist_length(args) == 0)
expr_yyerror_more(yyscanner, "at least one argument expected",
PGBENCH_FUNCTIONS[fnumber].fname);
+ /* special case: case (when ... then ...)+ (else ...)? end */
+ if (PGBENCH_FUNCTIONS[fnumber].nargs == -2)
+ {
+ int len = elist_length(args);
+ if (len < 3 || len % 2 != 1)
+ expr_yyerror_more(yyscanner, "odd and >= 3 number of arguments expected",
+ "case control structure");
+ }
expr->etype = ENODE_FUNCTION;
expr->u.function.function = PGBENCH_FUNCTIONS[fnumber].tag;
@@ -291,6 +389,15 @@ make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *args)
return expr;
}
+static PgBenchExpr *
+make_case(yyscan_t yyscanner, PgBenchExprList *when_then_list, PgBenchExpr *else_part)
+{
+ if (else_part == NULL)
+ else_part = make_integer_constant(0);
+ when_then_list = make_elist(else_part, when_then_list);
+ return make_func(yyscanner, find_func(yyscanner, "!case_end"), when_then_list);
+}
+
/*
* exprscan.l is compiled as part of exprparse.y. Currently, this is
* unavoidable because exprparse does not create a .h file to export
diff --git a/src/bin/pgbench/exprscan.l b/src/bin/pgbench/exprscan.l
index 20891a3..ebf0fe3 100644
--- a/src/bin/pgbench/exprscan.l
+++ b/src/bin/pgbench/exprscan.l
@@ -66,6 +66,16 @@ space [ \t\r\f\v]
nonspace [^ \t\r\f\v\n]
newline [\n]
+/* case insensitive keywords */
+and [Aa][Nn][Dd]
+or [Oo][Rr]
+not [Nn][Oo][Tt]
+case [Cc][Aa][Ss][Ee]
+when [Ww][Hh][Ee][Nn]
+then [Tt][Hh][Ee][Nn]
+else [Ee][Ll][Ss][Ee]
+end [Ee][Nn][Dd]
+
/* Exclusive states */
%x EXPR
@@ -112,11 +122,35 @@ 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 '~'; }
+
"(" { return '('; }
")" { return ')'; }
"," { return ','; }
+{and} { return AND_OP; }
+{or} { return OR_OP; }
+{not} { return '!'; }
+
+{case} { return CASE_KW; }
+{when} { return WHEN_KW; }
+{then} { return THEN_KW; }
+{else} { return ELSE_KW; }
+{end} { return END_KW; }
+
:{alnum}+ {
yylval->str = pg_strdup(yytext + 1);
return VARIABLE;
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index d44cfda..f5d61bd 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:
+ {
+ 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 /* cannot get here */
+ Assert(0);
+
+ return true;
+ }
+ case PGBENCH_NOT:
+ setIntValue(retval, !coerceToBool(&vargs[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,24 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_CASE:
+ {
+ int n_when = nargs / 2, i;
+ Assert(nargs >= 3 && nargs % 2 == 1);
+ /* return on first true when condition */
+ for (i = 0; i < n_when; i++)
+ {
+ if (coerceToBool(&vargs[2*i]))
+ {
+ *retval = vargs[2*i+1];
+ return true;
+ }
+ }
+ /* else value is last */
+ *retval = vargs[nargs-1];
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index ab0f822..8b2c01e 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_NOT,
+ PGBENCH_BITAND,
+ PGBENCH_BITOR,
+ PGBENCH_BITXOR,
+ PGBENCH_LSHIFT,
+ PGBENCH_RSHIFT,
+ PGBENCH_EQ,
+ PGBENCH_NE,
+ PGBENCH_LE,
+ PGBENCH_LT,
+ PGBENCH_CASE
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/002_pgbench.pl b/src/bin/pgbench/t/002_pgbench.pl
new file mode 100644
index 0000000..3e58884
--- /dev/null
+++ b/src/bin/pgbench/t/002_pgbench.pl
@@ -0,0 +1,56 @@
+use strict;
+use warnings;
+
+use PostgresNode;
+use TestLib;
+use Test::More tests => 4;
+
+# Test pgbench custom expressions
+my $node = get_new_node('main');
+$node->init;
+$node->start;
+$node->safe_psql('postgres',
+ 'CREATE UNLOGGED TABLE pgbench_expr(id SERIAL PRIMARY KEY, val TEXT NOT NULL);');
+
+my $script = $node->basedir . '/pgbench_expressions';
+
+# test a number of expressions, which must produce increasing results
+my @value_exprs = (
+ # 1 .. 8: arithmetic and other operators
+ '1', '1+1', '1.5*2', '8/2', '15%10', '6.0E0', '(1<<3) - (-(-1))', '16 >> 1',
+ # 9 .. 17: various functions
+ 'abs(-9)', 'least(5432, 10, 111)', 'greatest(11.0, -1, 7, 9)', 'int(12.0)',
+ 'double(13)', 'int(pi() * 100)-300', 'sqrt(225)', 'ln(65536)/ln(2)',
+ 'int(exp(3.0) - 3)',
+ # 18 .. 19: case
+ 'CASE WHEN 1 AND 1 OR 0 THEN 18 END', 'case when not 0 then 19 else 0 end',
+ # 20: more case with comparisons
+ 'CASE WHEN 4 < 5 THEN 5 END * ' .
+ ' (CASE WHEN 3 <= 3 THEN 0.9 END + CASE WHEN 1 <> 0 THEN 0.8 ELSE 0 END + ' .
+ ' CASE WHEN 1 = 1 THEN 0.2 END + CASE WHEN 123 > 12 THEN 0.4 END + ' .
+ ' CASE WHEN 12 >= 12 THEN 0.6 END + CASE WHEN 17 < 18 THEN 0.1 END + 1)',
+ # 21 .. 24: bitwise operators
+ '16 | 4 | 1', '31 # 9', '55 & 151', '~(-25)'
+ # not tested: random functions
+);
+
+for my $expr (@value_exprs)
+{
+ append_to_file($script,
+ "\\set e $expr\nINSERT INTO pgbench_expr(val) VALUES (:e);\n");
+}
+
+$node->command_like(
+ [ qw(pgbench -n --transactions=1 --file), $script ],
+ qr{1/1},
+ 'pgbench expressions');
+
+my $stdout = $node->safe_psql(
+ 'postgres',
+ "SELECT COUNT(*) FILTER (WHERE id::FLOAT8 = val::FLOAT8) || '/' || COUNT(*)
+ FROM pgbench_expr;");
+
+#diag("stdout=$stdout");
+
+my $ntests = @value_exprs;
+like($stdout, qr{\b$ntests/$ntests\b}, 'pgbench expressions: results');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
