Re: pgbench \for or similar loop

On Tue, Apr 19, 2011 at 1:57 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
>
>> And for the "first-hack-that-comes-to-mind", I find my self
>> pulling out the named fifo trick all the time, and just leaving my
>> for/loop/if logic  in bash writing SQL commands to the fifo,
>> occasionally getting psql to write an answer to a file that I then
>> read back in bash....
>
> I'm not clear on exactly what you're proposing there, but the thing
> I've considered doing is having threads to try to keep a FIFO queue
> populated with a configurable transaction mix, while a configurable
> number of worker threads pull those transactions off the queue and
> submit them to the server.  The transactions would need to be
> scripted in some way such that they could query a value and then use
> it in another statement, or use flow control for conditional
> execution or looping.  And, of course, there would need to be a way
> do define conditions under which a transaction would roll back and
> retry from the beginning -- with the retry being a separate count
> and the failed attempt not counted in the TPS numbers.
>
> It would take that much infrastructure to have a performance test
> which would give numbers which would correspond well to an actual
> production load in our environment.  It still wouldn't be quite as
> good as actually logging production activity and playing it back,
> but it would come pretty close with a lot less work per test.

Well, I don't think I'm doing anything nearly as complicated as what
your'e thinking...

I'm talking about simple stuff like:

mkfifo psql.fifo
exec 4> psql.fifo
psql < psql.fifo&
...
for i in $(seq 1 1000)
do
echo "SELECT 1;" >&4
done

Couple that with:
echo "\o /path/to/some/file" >&4
and other \settitngs, and I can use bash for all my logic, and just
feed lines/statements to psql to have them executed as I wish, with
output directed/formated as I wish...

--
Aidan Van Dyk                                             Create like a god,
aidan(at)highrise(dot)ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.