Hello Justin,

src/test/regress/sql/create_am.sql:CREATE ACCESS METHOD heap2 TYPE TABLE 
HANDLER heap_tableam_handler;
...
src/test/regress/sql/create_am.sql:DROP ACCESS METHOD heap2;

Or maybe using SET default_tablespace instead of modifying the CREATE sql.
That'd need to be done separately for indexes, and RESET after creating the
tables, to avoid accidentally affecting indexes, too.

Why should it not affect indexes?

@Fabien: I think the table should be created and populated within the same
transaction, to allow this optimization in v13 to apply during the
"initialization" phase.
c6b92041d Skip WAL for new relfilenodes, under wal_level=minimal.

Possibly.

This would be a change of behavior: currently only filling tables is under an explicit transaction.

That would be a matter for another patch, probably with an added option.

As VACUUM is not transaction compatible, it might be a little bit tricky to add such a feature. Also I'm not sure about some constraint declarations.

ISTM that I submitted a patch a long time ago to allow "()" to control transaction from the command line, but somehow it got lost or rejected. I redeveloped it, see attached. I cannot see reliable performance improvement by playing with (), though.

--
Fabien.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index b03d0cc50f..8d9b642fdd 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -170,7 +170,7 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
         <replaceable>init_steps</replaceable> specifies the
         initialization steps to be performed, using one character per step.
         Each step is invoked in the specified order.
-        The default is <literal>dtgvp</literal>.
+        The default is <literal>dt(g)vp</literal>.
         The available steps are:
 
         <variablelist>
@@ -226,6 +226,22 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
            </para>
           </listitem>
          </varlistentry>
+         <varlistentry>
+          <term><literal>(</literal> (Begin)</term>
+          <listitem>
+           <para>
+            Begin a transaction.
+           </para>
+          </listitem>
+         </varlistentry>
+         <varlistentry>
+          <term><literal>)</literal> (Commit)</term>
+          <listitem>
+           <para>
+            Commit a transaction.
+           </para>
+          </listitem>
+         </varlistentry>
          <varlistentry>
           <term><literal>v</literal> (Vacuum)</term>
           <listitem>
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 3057665bbe..f77e33056c 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -132,8 +132,8 @@ static int	pthread_join(pthread_t th, void **thread_return);
 /********************************************************************
  * some configurable parameters */
 
-#define DEFAULT_INIT_STEPS "dtgvp"	/* default -I setting */
-#define ALL_INIT_STEPS "dtgGvpf"	/* all possible steps */
+#define DEFAULT_INIT_STEPS "dt(g)vp"	/* default -I setting */
+#define ALL_INIT_STEPS "dtgGvpf()"	/* all possible steps */
 
 #define LOG_STEP_SECONDS	5	/* seconds between log messages */
 #define DEFAULT_NXACTS	10		/* default nxacts */
@@ -3823,12 +3823,6 @@ initGenerateDataClientSide(PGconn *con)
 
 	fprintf(stderr, "generating data (client-side)...\n");
 
-	/*
-	 * we do all of this in one transaction to enable the backend's
-	 * data-loading optimizations
-	 */
-	executeStatement(con, "begin");
-
 	/* truncate away any old data */
 	initTruncateTables(con);
 
@@ -3940,8 +3934,6 @@ initGenerateDataClientSide(PGconn *con)
 	}
 
 	termPQExpBuffer(&sql);
-
-	executeStatement(con, "commit");
 }
 
 /*
@@ -3958,12 +3950,6 @@ initGenerateDataServerSide(PGconn *con)
 
 	fprintf(stderr, "generating data (server-side)...\n");
 
-	/*
-	 * we do all of this in one transaction to enable the backend's
-	 * data-loading optimizations
-	 */
-	executeStatement(con, "begin");
-
 	/* truncate away any old data */
 	initTruncateTables(con);
 
@@ -3989,8 +3975,6 @@ initGenerateDataServerSide(PGconn *con)
 	executeStatement(con, sql.data);
 
 	termPQExpBuffer(&sql);
-
-	executeStatement(con, "commit");
 }
 
 /*
@@ -4076,6 +4060,8 @@ initCreateFKeys(PGconn *con)
 static void
 checkInitSteps(const char *initialize_steps)
 {
+	bool in_tx = false;
+
 	if (initialize_steps[0] == '\0')
 	{
 		pg_log_fatal("no initialization steps specified");
@@ -4090,6 +4076,36 @@ checkInitSteps(const char *initialize_steps)
 			pg_log_info("Allowed step characters are: \"" ALL_INIT_STEPS "\".");
 			exit(1);
 		}
+
+		if (*step == '(')
+		{
+			if (in_tx)
+			{
+				pg_log_fatal("opening a transaction inside a transaction");
+				exit(1);
+			}
+			in_tx = true;
+		}
+		else if (*step == ')')
+		{
+			if (!in_tx)
+			{
+				pg_log_fatal("closing a transaction without opening it");
+				exit(1);
+			}
+			in_tx = false;
+		}
+		else if (*step == 'v' && in_tx)
+		{
+			pg_log_fatal("cannot run vacuum within a transaction");
+			exit(1);
+		}
+	}
+
+	if (in_tx)
+	{
+		pg_log_fatal("uncommitted transaction");
+		exit(1);
 	}
 }
 
@@ -4150,6 +4166,14 @@ runInitSteps(const char *initialize_steps)
 				op = "foreign keys";
 				initCreateFKeys(con);
 				break;
+			case '(':
+				op = "begin";
+				executeStatement(con, "begin");
+				break;
+			case ')':
+				op = "commit";
+				executeStatement(con, "commit");
+				break;
 			case ' ':
 				break;			/* ignore */
 			default:
diff --git a/src/bin/pgbench/t/002_pgbench_no_server.pl b/src/bin/pgbench/t/002_pgbench_no_server.pl
index e38c7d77d1..11c677ae54 100644
--- a/src/bin/pgbench/t/002_pgbench_no_server.pl
+++ b/src/bin/pgbench/t/002_pgbench_no_server.pl
@@ -139,6 +139,8 @@ my @options = (
 		'--progress-timestamp => --progress', '--progress-timestamp',
 		[qr{allowed only under}]
 	],
+
+	# initialization
 	[
 		'-I without init option',
 		'-I dtg',
@@ -152,6 +154,26 @@ my @options = (
 			qr{Allowed step characters are}
 		]
 	],
+	[
+		'bad init tx 1',
+		'-i -I )',
+		[	qr{open} ]
+	],
+	[
+		'bad init tx 2',
+		'-i -I (',
+		[	qr{uncommit} ]
+	],
+	[
+		'bad init tx 3',
+		'-i -I (v)',
+		[	qr{vacuum} ]
+	],
+	[
+		'bad init tx 4',
+		'-i -I ((',
+		[	qr{inside} ]
+	],
 	[
 		'bad random seed',
 		'--random-seed=one',

Reply via email to