Re: [HACKERS] pgbench more operators & functions - Mailing list pgsql-hackers

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.


CONTEXT

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


MOTIVATION

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


CONTENTS

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