Re: [HACKERS] pgbench post-connection command
On Thu, Jan 12, 2012 at 8:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Thu, Jan 12, 2012 at 5:32 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
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 USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
Simon Riggs si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
Excerpts from Tom Lane's message of jue ene 12 12:26:49 -0300 2012: Simon Riggs si...@2ndquadrant.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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
On 12.01.2012 17:26, Tom Lane wrote: Simon Riggssi...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 12.01.2012 17:26, Tom Lane wrote: Simon Riggssi...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
On Thu, Jan 12, 2012 at 3:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
Simon Riggs si...@2ndquadrant.com writes: On Thu, Jan 12, 2012 at 3:26 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
On Thu, Jan 12, 2012 at 4:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Thu, Jan 12, 2012 at 3:26 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
Simon Riggs si...@2ndquadrant.com writes: On Thu, Jan 12, 2012 at 4:24 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
On Thu, Jan 12, 2012 at 5:32 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
Simon Riggs si...@2ndquadrant.com writes: On Thu, Jan 12, 2012 at 5:32 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench post-connection command
On 12.01.2012 22:04, Tom Lane wrote: Simon Riggssi...@2ndquadrant.com writes: On Thu, Jan 12, 2012 at 5:32 PM, Tom Lanet...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
