Re: pgbench more operators & functions - Mailing list pgsql-hackers

Hello Stephen,

> * Fabien COELHO (coelho@cri.ensmp.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_tellersWHERE tid = :tid ; -- if random < 0.85 account is in teller's branch, else in a *different* branch \set
bidCASE \            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 +
:deltaWHERE 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.