Hello,
For my 2c, at least, while I'm definitely interested in this, it's not nearly high enough on my plate with everything else going on to get any attention in the next few weeks, at least. I do think that, perhaps, this patch may deserve a bit of a break, to allow people to come back to it with a fresh perspective, so perhaps moving it to the next commitfest would be a good idea, in a Needs Review state.
So, let's try again for the next CF...Here is a v9 which includes some more cleanup, hopefully in the expected direction which is to make pgbench expressions behave as SQL expressions, and I hope taking into account all other feedback as well.
CONTEXTPgbench has been given an expression parser (878fdcb8) which allows to use full expressions instead of doing one-at-a-time operations. This parser has been extended with functions (7e137f84) & double type (86c43f4e). The first batch of functions was essentially a poc about how to add new functions with various requirements. Pgbench default "tpcb-like" test takes advantage of these additions to reduce the number of lines it needs.
MOTIVATIONThis patch aims at providing actually useful functions for benchmarking. The functions and operators provided here are usual basic operations. They are not chosen randomly, but are simply taken from existing benchmarks:
In TPC-B 2.0.0 section 5.3.5 and TPC-C 5.11 section 2.5.1.2, the selection of accounts uses a test (if ...), logical conditions (AND, OR) and comparisons (<, =, >=, >).
In TPC-C 5.11 section 2.1.6, a bitwise or (|) is used to skew a distribution based on two uniform distributions.
In TPC-C 5.11 section 5.2.5.4, a log function is used to determine "think time", which can be truncated (i.e. "least" function, already in pgbench).
CONTENTSThe attached patch provides a consistent set of functions and operators based on the above examples, with operator precedence taken from postgres SQL parser:
- "boolean" type support is added, because it has been requested that pgbench should be as close as SQL expressions as possible. This induced some renaming as some functions & struct fields where named "num" because they where expecting an int or a double, but a boolean is not really a numeral.
- SQL comparisons (= <> < > <= >=) plus pg SQL "!=", which result in a boolean.
- SQL logical operators (and or not) on booleans. - SQL bitwise operators taken from pg: | & # << >> ~. - mod SQL function as a synonymous for %. - ln and exp SQL functions. - SQL CASE/END conditional structure. The patch also includes documentation and additional tap tests. A test script is also provided.This version is strict about typing, mimicking postgres behavior. For instance, using an int as a boolean results in a error. It is easy to make it more tolerant to types, which was the previous behavior before it was suggested to follow SQL behavior.
Together with another submitted patch about retrieving query results, the added capabilities allow to implement strictly conforming TPC-B transactions.
-- Fabien.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 1eee8dc..36a63fc 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -827,12 +827,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
from <replaceable>expression</>.
The expression may contain integer constants such as <literal>5432</>,
double constants such as <literal>3.14159</>,
+ boolean constants <literal>TRUE</> and <literal>FALSE</>,
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 SQL precedence and associativity,
+ <link linkend="pgbench-builtin-functions">function calls</>,
+ SQL <literal>CASE</> generic conditional expressions
+ and parentheses.
</para>
<para>
@@ -917,6 +918,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>NOT</></>
+ <entry>logical not</>
+ <entry><literal>not false</></>
+ <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>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>integer bitwise NOT</>
+ <entry><literal>~ 1</></>
+ <entry><literal>-2</></>
+ </row>
+ <row>
+ <entry><literal><<</></>
+ <entry>integer bitwise shift left</>
+ <entry><literal>1 << 2</></>
+ <entry><literal>4</></>
+ </row>
+ <row>
+ <entry><literal>>></></>
+ <entry>integer bitwise shift right</>
+ <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>-</></>
+ <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>
@@ -963,6 +1123,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</>
@@ -984,6 +1151,20 @@ 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>mod(<replaceable>i</>, <replaceable>j</>)</></></>
+ <entry>inteter</>
+ <entry>modulo</>
+ <entry><literal>mod(54, 32)</></>
+ <entry><literal>22</></>
+ </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 b3a2d9b..96f7120 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -21,11 +21,14 @@ PgBenchExpr *expr_parse_result;
static PgBenchExprList *make_elist(PgBenchExpr *exp, PgBenchExprList *list);
static PgBenchExpr *make_integer_constant(int64 ival);
static PgBenchExpr *make_double_constant(double dval);
+static PgBenchExpr *make_boolean_constant(bool bval);
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);
%}
@@ -40,23 +43,32 @@ static PgBenchExpr *make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *
{
int64 ival;
double dval;
+ bool bval;
char *str;
PgBenchExpr *expr;
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 <bval> BOOLEAN_CONST
%type <str> VARIABLE FUNCTION
-%token INTEGER_CONST DOUBLE_CONST VARIABLE FUNCTION
+%token INTEGER_CONST DOUBLE_CONST BOOLEAN_CONST VARIABLE FUNCTION
+%token AND_OP OR_OP NOT_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 */
+/* Precedence: lowest to highest, taken from postgres SQL parser */
+%left OR_OP
+%left AND_OP
+%right NOT_OP
+%nonassoc '<' '>' '=' LE_OP GE_OP NE_OP
+%left '|' '#' '&' LS_OP RS_OP '~'
%left '+' '-'
%left '*' '/' '%'
-%right UMINUS
+%right UNARY
%%
@@ -68,20 +80,46 @@ 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 { $$ = make_op(yyscanner, "#",
+ make_integer_constant(-1), $2); }
+ | NOT_OP expr { $$ = make_uop(yyscanner, "!not", $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, "mod", $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, "!and", $1, $3); }
+ | expr OR_OP expr { $$ = make_op(yyscanner, "!or", $1, $3); }
| INTEGER_CONST { $$ = make_integer_constant($1); }
| DOUBLE_CONST { $$ = make_double_constant($1); }
+ | BOOLEAN_CONST { $$ = make_boolean_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); }
;
@@ -110,6 +148,17 @@ make_double_constant(double dval)
}
static PgBenchExpr *
+make_boolean_constant(bool bval)
+{
+ PgBenchExpr *expr = pg_malloc(sizeof(PgBenchExpr));
+
+ expr->etype = ENODE_CONSTANT;
+ expr->u.constant.type = PGBT_BOOLEAN;
+ expr->u.constant.u.bval = bval;
+ return expr;
+}
+
+static PgBenchExpr *
make_variable(char *varname)
{
PgBenchExpr *expr = pg_malloc(sizeof(PgBenchExpr));
@@ -119,6 +168,7 @@ make_variable(char *varname)
return expr;
}
+/* binary operators */
static PgBenchExpr *
make_op(yyscan_t yyscanner, const char *operator,
PgBenchExpr *lexpr, PgBenchExpr *rexpr)
@@ -127,11 +177,19 @@ 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
+ * - fname: function name, "!..." for special internal functions
* - nargs: number of arguments
* -1 is a special value for least & greatest meaning #args >= 1
+ * -2 is for the "CASE WHEN ..." function, which has #args >= 3 and odd
* - tag: function identifier from PgBenchFunction enum
*/
static const struct
@@ -155,7 +213,7 @@ static const struct
"/", 2, PGBENCH_DIV
},
{
- "%", 2, PGBENCH_MOD
+ "mod", 2, PGBENCH_MOD
},
/* actual functions */
{
@@ -177,6 +235,12 @@ static const struct
"sqrt", 1, PGBENCH_SQRT
},
{
+ "ln", 1, PGBENCH_LN
+ },
+ {
+ "exp", 1, PGBENCH_EXP
+ },
+ {
"int", 1, PGBENCH_INT
},
{
@@ -191,6 +255,45 @@ static const struct
{
"random_exponential", 3, PGBENCH_RANDOM_EXPONENTIAL
},
+ {
+ "!and", 2, PGBENCH_AND
+ },
+ {
+ "!or", 2, PGBENCH_OR
+ },
+ {
+ "!not", 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 +382,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 +402,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 dc1367b..ad2eb32 100644
--- a/src/bin/pgbench/exprscan.l
+++ b/src/bin/pgbench/exprscan.l
@@ -69,6 +69,18 @@ newline [\n]
/* Line continuation marker */
continuation \\{newline}
+/* 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]
+true [Tt][Rr][Uu][Ee]
+false [Ff][Aa][Ll][Ss][Ee]
+
/* Exclusive states */
%x EXPR
@@ -127,15 +139,47 @@ continuation \\{newline}
"-" { return '-'; }
"*" { return '*'; }
"/" { return '/'; }
-"%" { return '%'; }
+"%" { return '%'; } /* C version, also in Pg SQL */
+"=" { return '='; }
+"<>" { return NE_OP; }
+"!=" { return NE_OP; } /* C version, also in Pg SQL */
+"<=" { 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 NOT_OP; }
+
+{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;
}
+{true} {
+ yylval->bval = true;
+ return BOOLEAN_CONST;
+ }
+{false} {
+ yylval->bval = false;
+ return BOOLEAN_CONST;
+ }
{digit}+ {
yylval->ival = strtoint64(yytext);
return INTEGER_CONST;
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index f6cb5d4..e5557b7 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -189,19 +189,18 @@ const char *progname;
volatile bool timer_exceeded = false; /* flag from signal handler */
/*
- * Variable definitions. If a variable has a string value, "value" is that
- * value, is_numeric is false, and num_value is undefined. If the value is
- * known to be numeric, is_numeric is true and num_value contains the value
- * (in any permitted numeric variant). In this case "value" contains the
- * string equivalent of the number, if we've had occasion to compute that,
- * or NULL if we haven't.
+ * Variable definitions. If a variable only has a string value, "svalue" is that
+ * value, "has_value" is false, and "value" is undefined. If the value is
+ * known, "has_value" is true and "value" contains the value (in any variant).
+ * In this case "svalue" contains the string equivalent of the value, if we've had
+ * occasion to compute that, or NULL if we haven't.
*/
typedef struct
{
char *name; /* variable's name */
- char *value; /* its value in string form, if known */
- bool is_numeric; /* is numeric value known? */
- PgBenchValue num_value; /* variable's value in numeric form */
+ char *svalue; /* its value in string form, if known */
+ bool has_value; /* is actual value known? */
+ PgBenchValue value; /* actual variable's value */
} Variable;
#define MAX_SCRIPTS 128 /* max number of SQL scripts allowed */
@@ -449,6 +448,7 @@ static const BuiltinScript builtin_script[] =
/* Function prototypes */
static void setIntValue(PgBenchValue *pv, int64 ival);
static void setDoubleValue(PgBenchValue *pv, double dval);
+static void setBoolValue(PgBenchValue *pv, bool bval);
static bool evaluateExpr(TState *, CState *, PgBenchExpr *, PgBenchValue *);
static void doLog(TState *thread, CState *st,
StatsData *agg, bool skipped, double latency, double lag);
@@ -972,50 +972,62 @@ getVariable(CState *st, char *name)
if (var == NULL)
return NULL; /* not found */
- if (var->value)
- return var->value; /* we have it in string form */
+ if (var->svalue)
+ return var->svalue; /* we have it in string form */
/* We need to produce a string equivalent of the numeric value */
- Assert(var->is_numeric);
- if (var->num_value.type == PGBT_INT)
+ Assert(var->has_value);
+ if (var->value.type == PGBT_INT)
snprintf(stringform, sizeof(stringform),
- INT64_FORMAT, var->num_value.u.ival);
- else
- {
- Assert(var->num_value.type == PGBT_DOUBLE);
+ INT64_FORMAT, var->value.u.ival);
+ else if (var->value.type == PGBT_DOUBLE)
snprintf(stringform, sizeof(stringform),
- "%.*g", DBL_DIG, var->num_value.u.dval);
- }
- var->value = pg_strdup(stringform);
- return var->value;
+ "%.*g", DBL_DIG, var->value.u.dval);
+ else if (var->value.type == PGBT_BOOLEAN)
+ snprintf(stringform, sizeof(stringform),
+ "%s", var->value.u.bval ? "true" : "false");
+ else /* internal error, unexpected type */
+ Assert(0);
+ var->svalue = pg_strdup(stringform);
+ return var->svalue;
}
/* Try to convert variable to numeric form; return false on failure */
static bool
-makeVariableNumeric(Variable *var)
+makeVariableValue(Variable *var)
{
- if (var->is_numeric)
+ if (var->has_value)
return true; /* no work */
- if (is_an_int(var->value))
+ if (pg_strcasecmp(var->svalue, "true") == 0)
+ {
+ setBoolValue(&var->value, true);
+ var->has_value = true;
+ }
+ if (pg_strcasecmp(var->svalue, "false") == 0)
{
- setIntValue(&var->num_value, strtoint64(var->value));
- var->is_numeric = true;
+ setBoolValue(&var->value, false);
+ var->has_value = true;
+ }
+ if (is_an_int(var->svalue))
+ {
+ setIntValue(&var->value, strtoint64(var->svalue));
+ var->has_value = true;
}
else /* type should be double */
{
double dv;
char xs;
- if (sscanf(var->value, "%lf%c", &dv, &xs) != 1)
+ if (sscanf(var->svalue, "%lf%c", &dv, &xs) != 1)
{
fprintf(stderr,
"malformed variable \"%s\" value: \"%s\"\n",
- var->name, var->value);
+ var->name, var->svalue);
return false;
}
- setDoubleValue(&var->num_value, dv);
- var->is_numeric = true;
+ setDoubleValue(&var->value, dv);
+ var->has_value = true;
}
return true;
}
@@ -1073,7 +1085,7 @@ lookupCreateVariable(CState *st, const char *context, char *name)
var = &newvars[st->nvariables];
var->name = pg_strdup(name);
- var->value = NULL;
+ var->svalue = NULL;
/* caller is expected to initialize remaining fields */
st->nvariables++;
@@ -1099,10 +1111,10 @@ putVariable(CState *st, const char *context, char *name, const char *value)
/* dup then free, in case value is pointing at this variable */
val = pg_strdup(value);
- if (var->value)
- free(var->value);
- var->value = val;
- var->is_numeric = false;
+ if (var->svalue)
+ free(var->svalue);
+ var->svalue = val;
+ var->has_value = false;
return true;
}
@@ -1110,7 +1122,7 @@ putVariable(CState *st, const char *context, char *name, const char *value)
/* Assign a numeric value to a variable, creating it if need be */
/* Returns false on failure (bad name) */
static bool
-putVariableNumber(CState *st, const char *context, char *name,
+putVariableValue(CState *st, const char *context, char *name,
const PgBenchValue *value)
{
Variable *var;
@@ -1119,11 +1131,11 @@ putVariableNumber(CState *st, const char *context, char *name,
if (!var)
return false;
- if (var->value)
- free(var->value);
- var->value = NULL;
- var->is_numeric = true;
- var->num_value = *value;
+ if (var->svalue)
+ free(var->svalue);
+ var->svalue = NULL;
+ var->has_value = true;
+ var->value = *value;
return true;
}
@@ -1136,7 +1148,7 @@ putVariableInt(CState *st, const char *context, char *name, int64 value)
PgBenchValue val;
setIntValue(&val, value);
- return putVariableNumber(st, context, name, &val);
+ return putVariableValue(st, context, name, &val);
}
static char *
@@ -1225,6 +1237,19 @@ getQueryParams(CState *st, const Command *command, const char **params)
params[i] = getVariable(st, command->argv[i + 1]);
}
+static char *
+valueTypeName(PgBenchValue *pval)
+{
+ if (pval->type == PGBT_INT)
+ return "int";
+ else if (pval->type == PGBT_DOUBLE)
+ return "double";
+ else if (pval->type == PGBT_BOOLEAN)
+ return "boolean";
+ else
+ return "<unknown>";
+}
+
/* get a value as an int, tell if there is a problem */
static bool
coerceToInt(PgBenchValue *pval, int64 *ival)
@@ -1234,11 +1259,10 @@ coerceToInt(PgBenchValue *pval, int64 *ival)
*ival = pval->u.ival;
return true;
}
- else
+ else if (pval->type == PGBT_DOUBLE)
{
double dval = pval->u.dval;
- Assert(pval->type == PGBT_DOUBLE);
if (dval < PG_INT64_MIN || PG_INT64_MAX < dval)
{
fprintf(stderr, "double to int overflow for %f\n", dval);
@@ -1247,6 +1271,11 @@ coerceToInt(PgBenchValue *pval, int64 *ival)
*ival = (int64) dval;
return true;
}
+ else /* BOOLEAN */
+ {
+ fprintf(stderr, "cannot coerce %s to int\n", valueTypeName(pval));
+ return false;
+ }
}
/* get a value as a double, or tell if there is a problem */
@@ -1258,12 +1287,32 @@ coerceToDouble(PgBenchValue *pval, double *dval)
*dval = pval->u.dval;
return true;
}
- else
+ else if (pval->type == PGBT_INT)
{
- Assert(pval->type == PGBT_INT);
*dval = (double) pval->u.ival;
return true;
}
+ else /* BOOLEAN */
+ {
+ fprintf(stderr, "cannot coerce %s to double\n", valueTypeName(pval));
+ return false;
+ }
+}
+
+/* get a value as a boolean, or tell if there is a problem */
+static bool
+coerceToBool(PgBenchValue *pval, bool *bval)
+{
+ if (pval->type == PGBT_BOOLEAN)
+ {
+ *bval = pval->u.bval;
+ return true;
+ }
+ else /* INT or DOUBLE */
+ {
+ fprintf(stderr, "cannot coerce %s to boolean\n", valueTypeName(pval));
+ return false;
+ }
}
/* assign an integer value */
@@ -1282,6 +1331,14 @@ setDoubleValue(PgBenchValue *pv, double dval)
pv->u.dval = dval;
}
+/* assign a boolean value */
+static void
+setBoolValue(PgBenchValue *pv, bool bval)
+{
+ pv->type = PGBT_BOOLEAN;
+ pv->u.bval = bval;
+}
+
/* maximum number of function arguments */
#define MAX_FARGS 16
@@ -1317,6 +1374,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];
@@ -1352,6 +1413,22 @@ evalFunc(TState *thread, CState *st,
setDoubleValue(retval, ld / rd);
return true;
+ case PGBENCH_EQ:
+ setBoolValue(retval, ld == rd);
+ return true;
+
+ case PGBENCH_NE:
+ setBoolValue(retval, ld != rd);
+ return true;
+
+ case PGBENCH_LE:
+ setBoolValue(retval, ld <= rd);
+ return true;
+
+ case PGBENCH_LT:
+ setBoolValue(retval, ld < rd);
+ return true;
+
default:
/* cannot get here */
Assert(0);
@@ -1380,6 +1457,22 @@ evalFunc(TState *thread, CState *st,
setIntValue(retval, li * ri);
return true;
+ case PGBENCH_EQ:
+ setBoolValue(retval, li == ri);
+ return true;
+
+ case PGBENCH_NE:
+ setBoolValue(retval, li != ri);
+ return true;
+
+ case PGBENCH_LE:
+ setBoolValue(retval, li <= ri);
+ return true;
+
+ case PGBENCH_LT:
+ setBoolValue(retval, li < ri);
+ return true;
+
case PGBENCH_DIV:
case PGBENCH_MOD:
if (ri == 0)
@@ -1420,6 +1513,63 @@ evalFunc(TState *thread, CState *st,
}
}
+ /* integer bitwise 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;
+
+ if (!coerceToBool(&vargs[0], &lb) ||
+ !coerceToBool(&vargs[1], &rb))
+ return false;
+
+ if (func == PGBENCH_AND)
+ setBoolValue(retval, lb && rb);
+ else if (func == PGBENCH_OR)
+ setBoolValue(retval, lb || rb);
+ else /* cannot get here */
+ Assert(0);
+
+ return true;
+ }
+ case PGBENCH_NOT:
+ {
+ bool b;
+ if (!coerceToBool(&vargs[0], &b))
+ return false;
+
+ setBoolValue(retval, !b);
+ return true;
+ }
+
/* no arguments */
case PGBENCH_PI:
setDoubleValue(retval, M_PI);
@@ -1460,11 +1610,12 @@ evalFunc(TState *thread, CState *st,
if (varg->type == PGBT_INT)
fprintf(stderr, "int " INT64_FORMAT "\n", varg->u.ival);
- else
- {
- Assert(varg->type == PGBT_DOUBLE);
+ else if (varg->type == PGBT_DOUBLE)
fprintf(stderr, "double %.*g\n", DBL_DIG, varg->u.dval);
- }
+ else if (varg->type == PGBT_BOOLEAN)
+ fprintf(stderr, "boolean %s\n", varg->u.bval ? "true" : "false");
+ else /* internal error, unexpected type */
+ Assert(0);
*retval = *varg;
@@ -1474,6 +1625,8 @@ evalFunc(TState *thread, CState *st,
/* 1 double argument */
case PGBENCH_DOUBLE:
case PGBENCH_SQRT:
+ case PGBENCH_LN:
+ case PGBENCH_EXP:
{
double dval;
@@ -1484,6 +1637,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;
@@ -1562,6 +1720,28 @@ 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++)
+ {
+ bool condition;
+
+ if (!coerceToBool(&vargs[2*i], &condition))
+ return false;
+
+ if (condition)
+ {
+ *retval = vargs[2*i+1];
+ return true;
+ }
+ }
+ /* else value is last */
+ *retval = vargs[nargs-1];
+ return true;
+ }
/* random functions */
case PGBENCH_RANDOM:
@@ -1671,10 +1851,10 @@ evaluateExpr(TState *thread, CState *st, PgBenchExpr *expr, PgBenchValue *retval
return false;
}
- if (!makeVariableNumeric(var))
+ if (!makeVariableValue(var))
return false;
- *retval = var->num_value;
+ *retval = var->value;
return true;
}
@@ -2219,7 +2399,7 @@ doCustom(TState *thread, CState *st, StatsData *agg)
break;
}
- if (!putVariableNumber(st, argv[0], argv[1], &result))
+ if (!putVariableValue(st, argv[0], argv[1], &result))
{
commandFailed(st, "assignment of meta-command 'set' failed");
st->state = CSTATE_ABORTED;
@@ -4127,16 +4307,16 @@ main(int argc, char **argv)
{
Variable *var = &state[0].variables[j];
- if (var->is_numeric)
+ if (var->has_value)
{
- if (!putVariableNumber(&state[i], "startup",
- var->name, &var->num_value))
+ if (!putVariableValue(&state[i], "startup",
+ var->name, &var->value))
exit(1);
}
else
{
if (!putVariable(&state[i], "startup",
- var->name, var->value))
+ var->name, var->svalue))
exit(1);
}
}
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index 38b3af5..37d92b5 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -34,7 +34,8 @@ union YYSTYPE;
typedef enum
{
PGBT_INT,
- PGBT_DOUBLE
+ PGBT_DOUBLE,
+ PGBT_BOOLEAN
/* add other types here */
} PgBenchValueType;
@@ -45,6 +46,7 @@ typedef struct
{
int64 ival;
double dval;
+ bool bval;
/* add other types here */
} u;
} PgBenchValue;
@@ -73,9 +75,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..5c4479c
--- /dev/null
+++ b/src/bin/pgbench/t/002_pgbench.pl
@@ -0,0 +1,57 @@
+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: constants, 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 TRUE AND TRUE OR FALSE THEN 18 END',
+ 'case when not false 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');
functions.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
