Re: pgbench - add \if support

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: pgbench - add \if support
Date: 2017-12-14 12:15:16
Message-ID: alpine.DEB.2.20.1712141314150.13653@lancre
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers


Mostly a rebase after zipfian function commit.

> This patch adds \if support to pgbench, similar to psql's version added in
> March.
>
> This patch brings a consistent set of features especially when combined with
> two other patches already in the (slow) CF process:
>
> - https://commitfest.postgresql.org/10/596/ .. /15/985/
> adds support for booleans expressions (comparisons, logical
> operators, ...). This enhanced expression engine would be useful
> to allow client-side expression in psql.
>
> - https://commitfest.postgresql.org/10/669/ .. /15/669/
> adds support for \gset, so that pgbench can interact with a database
> and extract something into a variable, instead of discarding it.
>
> This patch adds a \if construct so that an expression on variables, possibly
> with data coming from the database, can change the behavior of a script.
>
> For instance, the following script, which uses features from the three
> patches, would implement TPC-B per spec (not "tpcb-like", but really as
> specified).
>
> \set tbid random(1, :scale)
> \set tid 10 * (:tbid - 1) + random(1, 10)
> -- client in same branch as teller at 85%
> \if :scale = 1 OR random(0, 99) < 85
> -- same branch
> \set bid :tbid
> \else
> -- different branch
> \set bid 1 + (:tbid + random(1, :scale - 1)) % :scale
> \endif
> \set aid :bid * 100000 + random(1, 100000)
> \set delta random(-999999, 999999)
> BEGIN;
> UPDATE pgbench_accounts
> SET abalance = abalance + :delta WHERE aid = :aid
> RETURNING abalance AS balance \gset
> UPDATE pgbench_tellers
> SET tbalance = tbalance + :delta WHERE tid = :tid;
> UPDATE pgbench_branches
> SET bbalance = bbalance + :delta WHERE bid = :bid;
> INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
> VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
> END;
>
> The patch moves the conditional stack infrastructure from psql to fe_utils,
> so that it is available to both psql & pgbench.
>
> A partial evaluation is performed to detect structural errors (eg missing
> endif, else after else...) when the script is parsed, so that such errors
> cannot occur when a script is running.
>
> A new automaton state is added to quickly step over false branches.
>
> TAP tests ensure reasonable coverage of the feature.
>
>

--
Fabien.

Attachment Content-Type Size
pgbench-if-2.patch text/x-diff 39.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ali Akbar 2017-12-14 12:18:59 Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" in child table must be marked NOT NULL
Previous Message Fabien COELHO 2017-12-14 12:04:26 Re: [HACKERS] pgbench more operators & functions