Re: pgbench more operators & functions

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

Given the time scale to get things through, or eventually not,
here is an update I was initially planning to submit later on.

On top of new functions, operators and the boolean type provided in v9:

- improve boolean conversion for "yes", "on" and other variants,
in line with pg general behavior.

- add support for NULL, including IS test variants.

- conditions are quite permissive i.e. non zero numericals are true
on a test.

- TAP tests are removed.
I think there must be a TAP test, but the pgbench testing
infrastructure is currently not very adequate.
I've submitted an independent patch to enhance it:

https://commitfest.postgresql.org/14/1118/

that I suggest should be considered first. Once there is such
convenient infra, I would update this patch to take advantage of it.

- it cleans up a few things in the implementation

--
Fabien.