Re: pgbench more operators & functions

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Jeevan Ladhe <jeevan(dot)ladhe(at)enterprisedb(dot)com>, PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pgbench more operators & functions
Date: 2017-04-20 17:17:44
Message-ID: alpine.DEB.2.20.1704201915210.7266@lancre
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> 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.
> 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.
> 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, 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, a log function is used to determine "think
> time", which can be truncated (i.e. "least" function, already in pgbench).
> 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:

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


Attachment Content-Type Size
pgbench-more-ops-funcs-10.patch text/x-diff 37.0 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-04-20 17:31:55 Re: Fwd: WIP Patch: Precalculate stable functions
Previous Message Fabien COELHO 2017-04-20 17:14:34 Re: pgbench tap tests & minor fixes