Pgbench shell command

These shell-linked features were submitted at PostgreSQL's Commit Fest for 8.5 version in September and November 2009.

\setshell option

Description

This function permits to set a parameter as an output of a shell or C script.

• Details of this feature
  • For a given transaction, it is possible to launch a customized script to set as the user wants a parameter for this transaction. pgbench takes the first output from the script and uses it as a parameter. If the output is not an integer, an error is returned and the client process stops. the same error is returned if commands are too long.
  • As a default option, the maximum size of those commands is limited to 256 characters
  • Please note that the number of arguments for the script is on the contrary not limited
  • The default prototype can be defined as "\setshell parameter_name scripts_name scripts_options"
• This feature can play an essential role in using statistical laws instead of the current \set and \setrandom options implemented so as to simulate the case of a group of accounts being used more than others for instance. The user is also completely free in setting parameters without having to modify pgbench's code.

Application example

Possibilities are multiple, applicable to all the existing statistic distributions: Preto, Gaussian or Poisson. It only depends on the user's needs.

\shell option

Description

This function permits to launch shell commands in a transaction file.

• Details of this feature
  • For a given transaction, it is possible to launch a shell command by preceding the command with the prefix "\shell". Please note that it is even possible to launch such commands between to common SQL sentences also.
  • By configuration default, shell commands are limited to 256 characters.

Example of 2PC transaction transaction

• This transaction is a 2PC generating a random prepare ID

\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
\setrandom txidrand 0 10000
START TRANSACTION;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
SELECT 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);
PREPARE TRANSACTION ':txidrand';
\shell ls -ll >> /tmp/log_data`date '+%Y%m%d'`_`date '+%k%M'`
COMMIT PREPARED ':txidrand';

Between prepared and commit statements, the user can scan pg_twophase so as to see the size of 2PC state files.

Example of common transaction

• As an possible application, it is possible to have a look at 2PC state files in pg_twophase with a simple ls -ll.

Begin;
[Transaction instructions]
Prepare transaction ‘TXID’;
\shell ls ~/pg_twophase;
Commit prepared ‘TXID’;