pgbench: introduce a new automatic variable 'client_number'
Please find attached a patch for pgbench that introduces a new
auto-variable 'client_number'. Following in the footsteps of 'scale'
auto-variable, this is not declared if the user has specified this variable
using -D switch.
Since 'clientid' is a very common name a user can use for their own
script's variable, I chose to call this auto-variable client_number; just
to avoid conflicts.
This variable can come in handy when you want to use a different expression
in a query depending on which client is executing it. An example custom
transaction is attached, where the UPDATE statement from any given client
always updates the same logical row.
Best regards,
--
Gurjeet Singh
EnterpriseDB Inc.
Attachments:
pgbench_add_cleint_number_variable.patchapplication/octet-stream; name=pgbench_add_cleint_number_variable.patchDownload
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index 24dab1f..66d4023 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -2108,6 +2108,7 @@ main(int argc, char **argv)
instr_time total_time;
instr_time conn_total_time;
int total_xacts;
+ char *clientid;
int i;
@@ -2423,6 +2424,14 @@ main(int argc, char **argv)
exit(1);
}
#endif
+ /*
+ * Define a client_number variable that is unique per connection. But don't
+ * override an explicit -D switch.
+ */
+ if (getVariable(&state[0], "client_number") == NULL)
+ clientid = xmalloc(11); /* INT_MAX can be 10 digits long at most .*/
+ else
+ clientid = NULL
/*
* save main process id in the global variable because process id will be
@@ -2446,9 +2455,25 @@ main(int argc, char **argv)
if (!putVariable(&state[i], "startup", state[0].variables[j].name, state[0].variables[j].value))
exit(1);
}
+
+ /* Define the client-id for this client */
+ if (clientid != NULL)
+ {
+ sprintf(clientid, "%d", i);
+ if (!putVariable(&state[i], "startup", "client_number", clientid))
+ exit(1);
+ }
}
}
+ /* Define the client-id for the first client */
+ if (clientid != NULL)
+ {
+ sprintf(clientid, "%d", 0);
+ if (!putVariable(&state[0], "startup", "client_number", clientid))
+ exit(1);
+ }
+
if (debug)
{
if (duration <= 0)
diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml
index e9900d3..c495600 100644
--- a/doc/src/sgml/pgbench.sgml
+++ b/doc/src/sgml/pgbench.sgml
@@ -600,7 +600,10 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
Variables can be set by the command-line <option>-D</> option,
explained above, or by the meta commands explained below.
In addition to any variables preset by <option>-D</> command-line options,
- the variable <literal>scale</> is preset to the current scale factor.
+ the variable <literal>scale</> is automatically preset to the current scale factor,
+ and <literal>client_number</> is preset to a unique number per client; a value
+ specified for these variables using <option>-D</> takes precedence over the
+ automatic presets.
Once set, a variable's
value can be inserted into a SQL command by writing
<literal>:</><replaceable>variablename</>. When running more than
On 06.06.2013 06:53, Gurjeet Singh wrote:
Please find attached a patch for pgbench that introduces a new
auto-variable 'client_number'. Following in the footsteps of 'scale'
auto-variable, this is not declared if the user has specified this variable
using -D switch.Since 'clientid' is a very common name a user can use for their own
script's variable, I chose to call this auto-variable client_number; just
to avoid conflicts.
Hmm, I'm not sure I care too much about that, to be honest. We have
'scale' as an auto-variable as well, which is also a common word. Also,
if there's an existing script out there that does "\set client_id ...",
it will override the automatic value, and work as it used to.
Another reason to name it "client_id" is that in the pgbench -l log
format, the documentation calls the first column "client_id". Makes
sense to call the auto-variable the same.
I think you forgot to compile with the patch, because there's a
semicolon missing ;-). I moved the code around a bit, setting the
variable next to where :scale is set; that's more readable. In the docs,
I split the descriptions of :scale and :client_id into a table.
I'll commit the attached as soon as the tree opens for 9.4 development.
- Heikki
Attachments:
pgbench-client_id-2.patchtext/x-diff; name=pgbench-client_id-2.patchDownload
commit 85ebed80396313f0b7f943a228421f75c68db2ab
Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date: Sun Jun 9 11:33:16 2013 +0300
Add :client_id automatic variable for custom pgbench scripts.
This makes it easier to write custom scripts that have different logic for
each client.
Gurjeet Singh, with some changes by me.
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index 8c202bf..1303217 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -2544,6 +2544,20 @@ main(int argc, char **argv)
}
}
+ /*
+ * Define a :client_id variable that is unique per connection. But don't
+ * override an explicit -D switch.
+ */
+ if (getVariable(&state[0], "client_id") == NULL)
+ {
+ for (i = 0; i < nclients; i++)
+ {
+ snprintf(val, sizeof(val), "%d", i);
+ if (!putVariable(&state[i], "startup", "client_id", val))
+ exit(1);
+ }
+ }
+
if (!is_no_vacuum)
{
fprintf(stderr, "starting vacuum...");
diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml
index e9900d3..8775606 100644
--- a/doc/src/sgml/pgbench.sgml
+++ b/doc/src/sgml/pgbench.sgml
@@ -600,13 +600,39 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
Variables can be set by the command-line <option>-D</> option,
explained above, or by the meta commands explained below.
In addition to any variables preset by <option>-D</> command-line options,
- the variable <literal>scale</> is preset to the current scale factor.
+ there are a few variables that are preset automatically, listed in
+ <xref linkend="pgbench-automatic-variables">. A value specified for these
+ variables using <option>-D</> takes precedence over the automatic presets.
Once set, a variable's
value can be inserted into a SQL command by writing
<literal>:</><replaceable>variablename</>. When running more than
one client session, each session has its own set of variables.
</para>
+ <table id="pgbench-automatic-variables">
+ <title>Automatic variables</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Variable</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry> <literal>scale</literal> </entry>
+ <entry>current scale factor</entry>
+ </row>
+
+ <row>
+ <entry> <literal>client_id</literal> </entry>
+ <entry>unique number identifying the client session (starts from zero)</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
<para>
Script file meta commands begin with a backslash (<literal>\</>).
Arguments to a meta command are separated by white space.