pgbench post-connection command

New -x option for pgbench executes the given command once after
connection of each session.

e.g.
pgbench -x "SET synchronous_commit = off"
pgbench -x "SET foo_extension.enabled = on"

Allows easier testing of user parameters. Command listed in final report

$ pgbench -c 2 -t 4 -x "set synchronous_commit = off"
starting vacuum...end.
transaction type: TPC-B (sort of)
post connect command: [set synchronous_commit = off]
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
number of transactions per client: 4
number of transactions actually processed: 8/8
tps = 122.897304 (including connections establishing)
tps = 179.953212 (excluding connections establishing)

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> New -x option for pgbench executes the given command once after
> connection of each session.

This seems rather poorly designed, mainly because there's no reason to
think that a single command would be sufficient.

What would make more sense to me is to add an option for a one-time
script, or possibly extend the script language to allow marking some
commands as "do once".

regards, tom lane

Excerpts from Tom Lane's message of jue ene 12 12:26:49 -0300 2012:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > New -x option for pgbench executes the given command once after
> > connection of each session.
>
> This seems rather poorly designed, mainly because there's no reason to
> think that a single command would be sufficient.
>
> What would make more sense to me is to add an option for a one-time
> script, or possibly extend the script language to allow marking some
> commands as "do once".

Maybe use isolation tester spec files as examples, which has blocks for
a "setup" as well as "teardown", in addition to whatever commands are to
run. It's possible that teardown is not ever necessary -- who knows?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 12.01.2012 17:26, Tom Lane wrote:
> Simon Riggs<simon(at)2ndQuadrant(dot)com> writes:
>> New -x option for pgbench executes the given command once after
>> connection of each session.

If it's just for SET, you could just put the GUCs in postgresql.conf.

> This seems rather poorly designed, mainly because there's no reason to
> think that a single command would be sufficient.
>
> What would make more sense to me is to add an option for a one-time
> script, or possibly extend the script language to allow marking some
> commands as "do once".

Hmm, that might be handy. I wanted to write a some tests a while ago
where each session operated on a separate table. With this, I could've
put the CREATE TABLE statement in the statup-script.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> On 12.01.2012 17:26, Tom Lane wrote:
>> Simon Riggs<simon(at)2ndQuadrant(dot)com> writes:
>>> New -x option for pgbench executes the given command once after
>>> connection of each session.

> If it's just for SET, you could just put the GUCs in postgresql.conf.

Or use PGOPTIONS. I think there might be a use-case for this sort of
thing, but it's going to be more complicated than a SET or two; that's
why I think the feature ought to provide for a script not just a
command.

One particular use-case that comes to mind is executing \set commands
that only need to be done once, not once per iteration.

regards, tom lane

On Thu, Jan 12, 2012 at 3:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> New -x option for pgbench executes the given command once after
>> connection of each session.
>
> This seems rather poorly designed, mainly because there's no reason to
> think that a single command would be sufficient.

It supports multiple commands via multi-statement requests
e.g.

-x "SET this = on; SET that = off"

> What would make more sense to me is to add an option for a one-time
> script, or possibly extend the script language to allow marking some
> commands as "do once".

That seems a little overcooked. Any long preparatory script can be
executed before pgbench. So this command is only for options that need
to be set on each database session. If you really do have a long
script that needs to be run for each session, you can set the command
to "SELECT execute_my_initial_function()" and then write your script
as a function.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Thu, Jan 12, 2012 at 3:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> This seems rather poorly designed, mainly because there's no reason to
>> think that a single command would be sufficient.

> It supports multiple commands via multi-statement requests
> e.g.
> -x "SET this = on; SET that = off"

I don't believe that works for multiple \set commands, which is the
more likely use-case for this; as noted upthread, executing SET here
is quite unnecessary since you can get that behavior with
"export PGOPTIONS".

regards, tom lane

On Thu, Jan 12, 2012 at 4:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Thu, Jan 12, 2012 at 3:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> This seems rather poorly designed, mainly because there's no reason to
>>> think that a single command would be sufficient.
>
>> It supports multiple commands via multi-statement requests
>> e.g.
>> -x "SET this = on; SET that = off"
>
> I don't believe that works for multiple \set commands, which is the
> more likely use-case for this; as noted upthread, executing SET here
> is quite unnecessary since you can get that behavior with
> "export PGOPTIONS".

OK, so you want...

\setonce <command>

or?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Thu, Jan 12, 2012 at 4:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I don't believe that works for multiple \set commands, which is the
>> more likely use-case for this; as noted upthread, executing SET here
>> is quite unnecessary since you can get that behavior with
>> "export PGOPTIONS".

> OK, so you want...

> \setonce <command>

More like "\once ... any SQL command or meta command here ..."
if we want to extend the scripting language. But I'd be perfectly happy
with a command-line switch that specifies a script file to be run once.

A difficulty with \once is that it's not very clear what should happen
in the case of multiple scripts (multiple -f switches). Should we go
through all of them at startup looking for \once switches? Or should
it happen the first time a given script is selected for execution?
And do conflicting \once \sets in different scripts affect each other?
If we go with a command-line switch then the confusion goes away, as it
is then clear that the start script's settings should be inherited by
all the client tasks.

regards, tom lane

On Thu, Jan 12, 2012 at 5:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> More like "\once ... any SQL command or meta command here ..."
> if we want to extend the scripting language.  But I'd be perfectly happy
> with a command-line switch that specifies a script file to be run once.

Once per connection, yes?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Thu, Jan 12, 2012 at 5:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> More like "\once ... any SQL command or meta command here ..."
>> if we want to extend the scripting language. But I'd be perfectly happy
>> with a command-line switch that specifies a script file to be run once.

> Once per connection, yes?

Hmmm ... good question. Heikki was speculating about doing CREATE TABLE
or similar, which you'd want done only once period. But I see no very
strong reason why cases like that couldn't be handled outside of
pgbench. So yeah, once per connection.

regards, tom lane

On 12.01.2012 22:04, Tom Lane wrote:
> Simon Riggs<simon(at)2ndQuadrant(dot)com> writes:
>> On Thu, Jan 12, 2012 at 5:32 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> More like "\once ... any SQL command or meta command here ..."
>>> if we want to extend the scripting language. But I'd be perfectly happy
>>> with a command-line switch that specifies a script file to be run once.
>
>> Once per connection, yes?
>
> Hmmm ... good question. Heikki was speculating about doing CREATE TABLE
> or similar, which you'd want done only once period.

I was creating a separate table for each connection to work with...

> But I see no very
> strong reason why cases like that couldn't be handled outside of
> pgbench. So yeah, once per connection.

... so that is exactly what I was thinking too.

For things that only need to run once, period, you can just do:

psql -c "CREATE TABLE"; pgbench ...

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, Jan 12, 2012 at 8:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Thu, Jan 12, 2012 at 5:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> More like "\once ... any SQL command or meta command here ..."
>>> if we want to extend the scripting language.  But I'd be perfectly happy
>>> with a command-line switch that specifies a script file to be run once.
>
>> Once per connection, yes?
>
> Hmmm ... good question.  Heikki was speculating about doing CREATE TABLE
> or similar, which you'd want done only once period.  But I see no very
> strong reason why cases like that couldn't be handled outside of
> pgbench.  So yeah, once per connection.

OK, its a TODO item, but I don't think I'll have time for it for the next CF.

If we need it during testing of other patches then I'll write it.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

There's one part of this that's still fuzzy in the spec I'd like to
clarify, if nothing else than for my own memory's sake--as the person
most likely to review a random pgbench patch. Simon gave an example
like this:

pgbench -x "SET synchronous_commit = off"

All are agreed this should take the name of a script instead on the
command line:

pgbench -x "nosync"

And execute that script as part of the initial setup to every connection.

Now: "nosync" might be a shell script called similarly to \shell.
That's more flexible in terms of its ability to do complicated setup
things, say a more ambitious iteration along the 'create a table per
connection' trail. But it can't really prime connection parameters
anymore, so that's pretty worthless.

It seems it must then be a pgbench script like "-f" specifies instead.
It will execute SQL and pgbench's \ meta commands. And I think that's
OK so long as two things are nailed down (as in, tested to work and
hopefully alluded to in the documentation):

1) The pgbench connection setup script can call \shell or \setshell.
Then we've got a backdoor to also handle the complicated external script
situation.

2) The connection setup script can set variables and they will still be
active after passing control to the main test script. Returning to the
"table per connection" sort of idea, that might be:

setup:

\setrandom tablename 1 100
CREATE TABLE test_:tablename;

main:

SELECT count(*) FROM test_tablename;

I would use this feature all the time once it was added, so glad to see
the idea pop up. I also have a long standing personal TODO to write a
doc update in this area: suggest how to use environment variables to
sneak settings into things. There's been a couple of ideas for pgbench
proposed that were blocked with "you can already do that setting PGxxx
before calling pgbench". That is true, but not obvious, and
periodically it get reinvented again.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com