PATCH: Add Table Access Method option to pgbench
Hello!
This patch adds a `--tableam=TABLEAM` option to the pgbench command line
which allows the user to specify which table am is used to create tables
initialized with `-i`.
This change was originally authored by Alexander Korotkov, I have updated
it and added a test to the pgbench runner. I'm hoping to make the deadline
for this currently open Commit Fest?
My goal is to add a couple more regression tests but the implementation is
complete.
Thanks in advance for any comments or questions!
-Michel
Attachments:
pgbench-tableam.patchtext/x-patch; charset=US-ASCII; name=pgbench-tableam.patchDownload
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index fbb74bdc4..80b57e8a8 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -223,6 +223,11 @@ double throttle_delay = 0;
*/
int64 latency_limit = 0;
+/*
+ * tableam selection
+ */
+char *tableam = NULL;
+
/*
* tablespace selection
*/
@@ -890,6 +895,7 @@ usage(void)
" --partition-method=(range|hash)\n"
" partition pgbench_accounts with this method (default: range)\n"
" --partitions=NUM partition pgbench_accounts into NUM parts (default: 0)\n"
+ " --tableam=TABLEAM create tables using the specified Table Access Method\n"
" --tablespace=TABLESPACE create tables in the specified tablespace\n"
" --unlogged-tables create tables as unlogged tables\n"
"\nOptions to select what to run:\n"
@@ -4705,14 +4711,34 @@ createPartitions(PGconn *con)
appendPQExpBufferStr(&query, "maxvalue");
appendPQExpBufferChar(&query, ')');
+
+ if (tableam != NULL)
+ {
+ char *escape_tableam;
+
+ escape_tableam = PQescapeIdentifier(con, tableam, strlen(tableam));
+ appendPQExpBuffer(&query, " using %s", escape_tableam);
+ PQfreemem(escape_tableam);
+ }
}
else if (partition_method == PART_HASH)
+ {
printfPQExpBuffer(&query,
"create%s table pgbench_accounts_%d\n"
" partition of pgbench_accounts\n"
" for values with (modulus %d, remainder %d)",
unlogged_tables ? " unlogged" : "", p,
partitions, p - 1);
+
+ if (tableam != NULL)
+ {
+ char *escape_tableam;
+
+ escape_tableam = PQescapeIdentifier(con, tableam, strlen(tableam));
+ appendPQExpBuffer(&query, " using %s", escape_tableam);
+ PQfreemem(escape_tableam);
+ }
+ }
else /* cannot get there */
Assert(0);
@@ -4799,10 +4825,20 @@ initCreateTables(PGconn *con)
if (partition_method != PART_NONE && strcmp(ddl->table, "pgbench_accounts") == 0)
appendPQExpBuffer(&query,
" partition by %s (aid)", PARTITION_METHOD[partition_method]);
- else if (ddl->declare_fillfactor)
+ else
{
+ if (tableam != NULL)
+ {
+ char *escape_tableam;
+
+ escape_tableam = PQescapeIdentifier(con, tableam, strlen(tableam));
+ appendPQExpBuffer(&query, " using %s", escape_tableam);
+ PQfreemem(escape_tableam);
+ }
+
/* fillfactor is only expected on actual tables */
- appendPQExpBuffer(&query, " with (fillfactor=%d)", fillfactor);
+ if (ddl->declare_fillfactor)
+ appendPQExpBuffer(&query, " with (fillfactor=%d)", fillfactor);
}
if (tablespace != NULL)
@@ -6556,6 +6592,7 @@ main(int argc, char **argv)
{"failures-detailed", no_argument, NULL, 13},
{"max-tries", required_argument, NULL, 14},
{"verbose-errors", no_argument, NULL, 15},
+ {"tableam", required_argument, NULL, 16},
{NULL, 0, NULL, 0}
};
@@ -6898,6 +6935,10 @@ main(int argc, char **argv)
benchmarking_option_set = true;
verbose_errors = true;
break;
+ case 16: /* tableam */
+ initialization_option_set = true;
+ tableam = pg_strdup(optarg);
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 2c0dc3696..eed976d7e 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -1418,6 +1418,23 @@ SELECT pg_advisory_unlock_all();
# Clean up
$node->safe_psql('postgres', 'DROP TABLE first_client_table, xy;');
+# Test table access method
+$node->safe_psql('postgres', 'CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler;');
+
+# Initialize pgbench table am
+$node->pgbench(
+ '-i --tableam=heap2', 0,
+ [qr{^$}],
+ [
+ qr{creating tables},
+ qr{vacuuming},
+ qr{creating primary keys},
+ qr{done in \d+\.\d\d s }
+ ],
+ 'pgbench test tableam options');
+
+# Clean up
+$node->safe_psql('postgres', 'DROP ACCESS METHOD heap2;');
# done
$node->safe_psql('postgres', 'DROP TABLESPACE regress_pgbench_tap_1_ts');
On Thu, Jun 30, 2022 at 09:09:17AM -0700, Michel Pelletier wrote:
This change was originally authored by Alexander Korotkov, I have updated
it and added a test to the pgbench runner. I'm hoping to make the deadline
for this currently open Commit Fest?
This is failing check-world
http://cfbot.cputube.org/michel-pelletier.html
BTW, you can test your patches the same as cfbot does (before mailing the list)
on 4 OSes by pushing a branch to a github account. See ./src/tools/ci/README
--
Justin
On Thu, 30 Jun 2022 at 09:51, Justin Pryzby <pryzby@telsasoft.com> wrote:
On Thu, Jun 30, 2022 at 09:09:17AM -0700, Michel Pelletier wrote:
This change was originally authored by Alexander Korotkov, I have updated
it and added a test to the pgbench runner. I'm hoping to make thedeadline
for this currently open Commit Fest?
This is failing check-world
http://cfbot.cputube.org/michel-pelletier.htmlBTW, you can test your patches the same as cfbot does (before mailing the
list)
on 4 OSes by pushing a branch to a github account. See
./src/tools/ci/READMEAh that's very helpful thank you! This is my first patch submission so
sorry for any mixups.
-Michel
I've got CI setup and building and the tests now pass, I was missing a
CASCADE in my test. New patch attached:
On Thu, 30 Jun 2022 at 10:50, Michel Pelletier <michel@supabase.io> wrote:
Show quoted text
On Thu, 30 Jun 2022 at 09:51, Justin Pryzby <pryzby@telsasoft.com> wrote:
On Thu, Jun 30, 2022 at 09:09:17AM -0700, Michel Pelletier wrote:
This change was originally authored by Alexander Korotkov, I have
updated
it and added a test to the pgbench runner. I'm hoping to make the
deadline
for this currently open Commit Fest?
This is failing check-world
http://cfbot.cputube.org/michel-pelletier.htmlBTW, you can test your patches the same as cfbot does (before mailing the
list)
on 4 OSes by pushing a branch to a github account. See
./src/tools/ci/READMEAh that's very helpful thank you! This is my first patch submission so
sorry for any mixups.
-Michel
Attachments:
pgbench-tableam.patchtext/x-patch; charset=US-ASCII; name=pgbench-tableam.patchDownload
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index fbb74bdc4c..80b57e8a87 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -223,6 +223,11 @@ double throttle_delay = 0;
*/
int64 latency_limit = 0;
+/*
+ * tableam selection
+ */
+char *tableam = NULL;
+
/*
* tablespace selection
*/
@@ -890,6 +895,7 @@ usage(void)
" --partition-method=(range|hash)\n"
" partition pgbench_accounts with this method (default: range)\n"
" --partitions=NUM partition pgbench_accounts into NUM parts (default: 0)\n"
+ " --tableam=TABLEAM create tables using the specified Table Access Method\n"
" --tablespace=TABLESPACE create tables in the specified tablespace\n"
" --unlogged-tables create tables as unlogged tables\n"
"\nOptions to select what to run:\n"
@@ -4705,14 +4711,34 @@ createPartitions(PGconn *con)
appendPQExpBufferStr(&query, "maxvalue");
appendPQExpBufferChar(&query, ')');
+
+ if (tableam != NULL)
+ {
+ char *escape_tableam;
+
+ escape_tableam = PQescapeIdentifier(con, tableam, strlen(tableam));
+ appendPQExpBuffer(&query, " using %s", escape_tableam);
+ PQfreemem(escape_tableam);
+ }
}
else if (partition_method == PART_HASH)
+ {
printfPQExpBuffer(&query,
"create%s table pgbench_accounts_%d\n"
" partition of pgbench_accounts\n"
" for values with (modulus %d, remainder %d)",
unlogged_tables ? " unlogged" : "", p,
partitions, p - 1);
+
+ if (tableam != NULL)
+ {
+ char *escape_tableam;
+
+ escape_tableam = PQescapeIdentifier(con, tableam, strlen(tableam));
+ appendPQExpBuffer(&query, " using %s", escape_tableam);
+ PQfreemem(escape_tableam);
+ }
+ }
else /* cannot get there */
Assert(0);
@@ -4799,10 +4825,20 @@ initCreateTables(PGconn *con)
if (partition_method != PART_NONE && strcmp(ddl->table, "pgbench_accounts") == 0)
appendPQExpBuffer(&query,
" partition by %s (aid)", PARTITION_METHOD[partition_method]);
- else if (ddl->declare_fillfactor)
+ else
{
+ if (tableam != NULL)
+ {
+ char *escape_tableam;
+
+ escape_tableam = PQescapeIdentifier(con, tableam, strlen(tableam));
+ appendPQExpBuffer(&query, " using %s", escape_tableam);
+ PQfreemem(escape_tableam);
+ }
+
/* fillfactor is only expected on actual tables */
- appendPQExpBuffer(&query, " with (fillfactor=%d)", fillfactor);
+ if (ddl->declare_fillfactor)
+ appendPQExpBuffer(&query, " with (fillfactor=%d)", fillfactor);
}
if (tablespace != NULL)
@@ -6556,6 +6592,7 @@ main(int argc, char **argv)
{"failures-detailed", no_argument, NULL, 13},
{"max-tries", required_argument, NULL, 14},
{"verbose-errors", no_argument, NULL, 15},
+ {"tableam", required_argument, NULL, 16},
{NULL, 0, NULL, 0}
};
@@ -6898,6 +6935,10 @@ main(int argc, char **argv)
benchmarking_option_set = true;
verbose_errors = true;
break;
+ case 16: /* tableam */
+ initialization_option_set = true;
+ tableam = pg_strdup(optarg);
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 2c0dc36965..c33df7b829 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -1418,6 +1418,23 @@ SELECT pg_advisory_unlock_all();
# Clean up
$node->safe_psql('postgres', 'DROP TABLE first_client_table, xy;');
+# Test table access method
+$node->safe_psql('postgres', 'CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler;');
+
+# Initialize pgbench table am
+$node->pgbench(
+ '-i --tableam=heap2', 0,
+ [qr{^$}],
+ [
+ qr{creating tables},
+ qr{vacuuming},
+ qr{creating primary keys},
+ qr{done in \d+\.\d\d s }
+ ],
+ 'pgbench test tableam options');
+
+# Clean up
+$node->safe_psql('postgres', 'DROP ACCESS METHOD heap2 CASCADE;');
# done
$node->safe_psql('postgres', 'DROP TABLESPACE regress_pgbench_tap_1_ts');
On Thu, Jun 30, 2022 at 01:07:53PM -0700, Michel Pelletier wrote:
I've got CI setup and building and the tests now pass, I was missing a
CASCADE in my test. New patch attached:
The exact same patch has been proposed back in November 2020:
/messages/by-id/0177f78c-4702-69c9-449d-93cc93c7f8c0@highgo.ca
And the conclusion back then is that one can already achieve this by
using PGOPTIONS:
PGOPTIONS='-c default_table_access_method=wuzza' pgbench [...]
So there is no need to complicate more pgbench, particularly when it
comes to partitioned tables where USING is not supported. Your patch
touches this area of the client code to bypass the backend error.
--
Michael
On Fri, Jul 01, 2022 at 10:06:49AM +0900, Michael Paquier wrote:
And the conclusion back then is that one can already achieve this by
using PGOPTIONS:
PGOPTIONS='-c default_table_access_method=wuzza' pgbench [...]So there is no need to complicate more pgbench, particularly when it
comes to partitioned tables where USING is not supported. Your patch
touches this area of the client code to bypass the backend error.
Actually, it could be a good thing to mention that directly in the
docs of pgbench.
--
Michael
On Thu, 30 Jun 2022 at 18:09, Michael Paquier <michael@paquier.xyz> wrote:
On Fri, Jul 01, 2022 at 10:06:49AM +0900, Michael Paquier wrote:
And the conclusion back then is that one can already achieve this by
using PGOPTIONS:
PGOPTIONS='-c default_table_access_method=wuzza' pgbench [...]So there is no need to complicate more pgbench, particularly when it
comes to partitioned tables where USING is not supported. Your patch
touches this area of the client code to bypass the backend error.Actually, it could be a good thing to mention that directly in the
docs of pgbench.
I've attached a documentation patch that mentions and links to the
PGOPTIONS documentation per your suggestion. I'll keep the other patch on
the back burner, perhaps in the future there will be demand for a command
line option as more TAMs are created.
Thanks,
-Michel
Show quoted text
--
Michael
Attachments:
pgbench-options.patchtext/x-patch; charset=US-ASCII; name=pgbench-options.patchDownload
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 37fd80388c..e2d728e0c4 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -317,7 +317,7 @@ UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter
</sect2>
- <sect2>
+ <sect2 id="config-setting-shell">
<title>Parameter Interaction via the Shell</title>
<para>
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 2acf55c2ac..f15825c293 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -1018,6 +1018,17 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<literal>always</literal>, <literal>auto</literal> and
<literal>never</literal>.
</para>
+
+ <para>
+ The environment variable <envar>PGOPTIONS</envar> specifies database
+ configuration options that are passed to PostgreSQL via the command line
+ (See <xref linkend="config-setting-shell"/>). For example, a hypothetical
+ default Table Access Method for the tables that pgbench creates
+ called <literal>wuzza</literal> can be specified with:
+<programlisting>
+PGOPTIONS='-c default_table_access_method=wuzza'
+</programlisting>
+ </para>
</refsect1>
<refsect1>
On Wed, Jul 13, 2022 at 12:33 AM Michel Pelletier <michel@supabase.io>
wrote:
On Thu, 30 Jun 2022 at 18:09, Michael Paquier <michael@paquier.xyz> wrote:
On Fri, Jul 01, 2022 at 10:06:49AM +0900, Michael Paquier wrote:
And the conclusion back then is that one can already achieve this by
using PGOPTIONS:
PGOPTIONS='-c default_table_access_method=wuzza' pgbench [...]So there is no need to complicate more pgbench, particularly when it
comes to partitioned tables where USING is not supported. Your patch
touches this area of the client code to bypass the backend error.Actually, it could be a good thing to mention that directly in the
docs of pgbench.I've attached a documentation patch that mentions and links to the
PGOPTIONS documentation per your suggestion. I'll keep the other patch on
the back burner, perhaps in the future there will be demand for a command
line option as more TAMs are created.
The documentation change looks good to me
On Mon, Jul 18, 2022 at 12:08 AM Mason Sharp <masonlists@gmail.com> wrote:
On Wed, Jul 13, 2022 at 12:33 AM Michel Pelletier <michel@supabase.io> wrote:
On Thu, 30 Jun 2022 at 18:09, Michael Paquier <michael@paquier.xyz> wrote:
On Fri, Jul 01, 2022 at 10:06:49AM +0900, Michael Paquier wrote:
And the conclusion back then is that one can already achieve this by
using PGOPTIONS:
PGOPTIONS='-c default_table_access_method=wuzza' pgbench [...]So there is no need to complicate more pgbench, particularly when it
comes to partitioned tables where USING is not supported. Your patch
touches this area of the client code to bypass the backend error.Actually, it could be a good thing to mention that directly in the
docs of pgbench.I've attached a documentation patch that mentions and links to the PGOPTIONS documentation per your suggestion. I'll keep the other patch on the back burner, perhaps in the future there will be demand for a command line option as more TAMs are created.
The documentation change looks good to me
Looks good to me as well. I'm going to push this if no objections.
------
Regards,
Alexander Korotkov
On Mon, Jul 18, 2022 at 01:53:21PM +0300, Alexander Korotkov wrote:
Looks good to me as well. I'm going to push this if no objections.
FWIW, I find the extra mention of PGOPTIONS with the specific point of
table AMs added within the part of the environment variables a bit
confusing, because we already mention PGOPTIONS for serializable
transactions a bit down. Hence, my choice would be the addition of an
extra paragraph in the "Notes", named "Table Access Methods", just
before or after "Good Practices". My 2c.
--
Michael
Hi!
On Tue, Jul 19, 2022 at 4:47 AM Michael Paquier <michael@paquier.xyz> wrote:
On Mon, Jul 18, 2022 at 01:53:21PM +0300, Alexander Korotkov wrote:
Looks good to me as well. I'm going to push this if no objections.
FWIW, I find the extra mention of PGOPTIONS with the specific point of
table AMs added within the part of the environment variables a bit
confusing, because we already mention PGOPTIONS for serializable
transactions a bit down. Hence, my choice would be the addition of an
extra paragraph in the "Notes", named "Table Access Methods", just
before or after "Good Practices". My 2c.
Thank you. Pushed applying the suggestion above.
------
Regards,
Alexander Korotkov