Re: pgbench more operators & functions

Hello Stephen,

> * Fabien COELHO (coelho(at)cri(dot)ensmp(dot)fr) wrote:
>>> I've got no objection to a more-nearly-TPC-B script as an option.
>>
>> Good, because adding a "per-spec" tpc-b as an additional builtin
>> option is one of my intentions, once pgbench is capable of it.
>
> I believe it would be really helpful to have the more-nearly-TPC-B
> script written using these new capabilities of pgbench to see that
> a) the new capabilities actually allow for this, b) there aren't other
> things which are needed, c) to provide an actual use-case for these new
> capabilities.

Here are the details:

(1) The required schema is slightly different : currently the type used
for holding balances is not wide enough per the TCP-B standard, this mean
maybe having an option to do "pgbench -i --standard-tpcb" which would
generate the right schema, probably it should just change a few INTEGER to
INT8, or maybe use NUMERIC(10). I have not done such a patch yet.

(2) The benchmark specification requires the client application to get
hold of query results, which are currently discarded by pgbench, so
pgbench does not really comply. I have submitted a patch to do that, see:

https://commitfest.postgresql.org/11/669/

(3) The expression lines, especially with a CASE syntax, are quite long,
allowing continuations would be nice, I have submitted a patch to do so:

https://commitfest.postgresql.org/11/807/

(4) As stated above, conditions are needed. Given the above extensions,
the following script would work and comply in 2 round trips and uses two
tests and two integer comparisons, added by the patch under discussion.
It also needs to get hold of two results (the branch teller and the final
balance).

-- choose teller id
\set tid random(1, 10 * :scale)
-- get an account branch, used if not the same as teller
\set abid random(1; :scale - 1)
-- get an account in-branch number
\set laid random(1, 100000)
-- select amount
\set delta random(-999999, +999999)
-- let us now start the stuff
BEGIN \;
-- get the teller's branch
SELECT bid \into tbid
FROM pgbench_tellers WHERE tid = :tid ;
-- if random < 0.85 account is in teller's branch, else in a *different* branch
\set bid CASE \
WHEN random(0, 99) < 85 THEN :tbid \
ELSE :abid + (:abid < :tbid) \
END
\set aid :laid + 100000 * :bid
-- now move the money and return the final balance
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid \;
-- Maybe it is better to use "RETURNING aid" in the previous UPDATE?
SELECT abalance \into abalance
FROM pgbench_accounts WHERE aid = :aid \;
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;

(5) The above "composite" queries (\;) do not work with -M prepared, which
means (a) either doing independent queries and getting a lot of added
latency, or better (b) make -M prepared work with composite queries, which
I have not done yet.

Basically the 3 patches under submission allow to write the above working
TPC-B script, but more patches are needed for the schema to comply and for
-M prepared to work as well. I would prefer to wait for all pieces to be
there before adding an example script. I do not think that one large patch
mixing everything makes much sense from an engineering point of view, even
if it makes sense from a feature point of view.

--
Fabien.