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