Re: [PATCH] pgbench: new feature allowing to launch shell commands - Mailing list pgsql-hackers

On Tue, 22 Sep 2009, Michael Paquier wrote:

Besides, you can also make tests without 2pc transactions, such as:
\shell ls -ll /home/ioltas/usr/pgsql/data
END;

I think that demonstrating the pgbench shell feature with this 2PC example is working against your patch being even considered, much less accepted. It's way too complicated, and the idea you're pouplarizing with it that the script should save whatever it does in the external filesystem is messy.  You'll need a simpler one that still accomplishes something useful that can go into the docs no matter what, the problems with prepared transactions you're reporting in your messages are just the beginning of issues that come from presenting this as the "hello, world" of how to use the shell feature.

Most of the cases I can think of for situations where I'd like to call the shell in a pgbench script require doing something useful with the result that is returned.  For example, when I'm picking a row at random, I often want to skew which row that is, to simulate the common real-world situation where a fraction of customers generate most of the transactions (the Pareto principle).

What I'd consider closer to a useful implementation of this feature is that you could call a shell script and use the first line of its output to set one of the pgbench internal variables.  The range of return codes is way too limited for those to help here.  Here's what that might look like, assuming "skewedrand" is our script that does that given the range it needs to operate over:

\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale

\setshell aid skewedrand 1 :naccounts

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

(I'm not sure if setshell would need to interpret the output as an integer for this to work right, there may be some int vs. string considerations here)

If you got something like that working first before moving onto these more complicated examples and I think you'll have an easier time of things.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD