Re: Using COPY FREEZE in pgbench - Mailing list pgsql-hackers

>> V3 works for me and looks ok. I changed it to ready in the CF app.
> 
> Thank you for your review!

Unfortunately it seems cfbot is not happy with the patch.

#   Failed test 'pgbench scale 1 initialization status (got 1 vs expected 0)'
#   at t/001_pgbench_with_server.pl line 116.
#   Failed test 'pgbench scale 1 initialization stderr /(?^:creating foreign keys)/'
#   at t/001_pgbench_with_server.pl line 116.
#                   'dropping old tables...
# creating tables...
# creating 2 partitions...
# creating primary keys...
# vacuuming...
# generating data (client-side)...
# 100000 of 100000 tuples (100%) done (elapsed 0.02 s, remaining 0.00 s)
# ERROR:  cannot perform COPY FREEZE on a partitioned table
# pgbench: fatal: PQendcopy failed

I think pgbench needs to check whether partitioning option is
specified or not. If specified, pgbench should not use COPY
FREEZE. Attached v4 patch does this.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 50cf22ba6b..0f6a6babc2 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -220,6 +220,10 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
             data is generated in <command>pgbench</command> client and then
             sent to the server. This uses the client/server bandwidth
             extensively through a <command>COPY</command>.
+            <command>pgbench</command> uses FREEZE option with 14 or later
+            version of <productname>PostgreSQL</productname> to speed up
+            subsequent <command>VACUUM</command> if portioning option is not
+            specified.
             Using <literal>g</literal> causes logging to print one message
             every 100,000 rows while generating data for the
             <structname>pgbench_accounts</structname> table.
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index e69d43b26b..44d39ad55d 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -3976,6 +3976,7 @@ initGenerateDataClientSide(PGconn *con)
     PGresult   *res;
     int            i;
     int64        k;
+    char        *copy_statement;
 
     /* used to track elapsed time and estimate of the remaining time */
     pg_time_usec_t start;
@@ -4022,7 +4023,18 @@ initGenerateDataClientSide(PGconn *con)
     /*
      * accounts is big enough to be worth using COPY and tracking runtime
      */
-    res = PQexec(con, "copy pgbench_accounts from stdin");
+
+    /*
+     * If partitioning is not enabled and server version is 14.0 or later, we
+     * can take account of freeze option of copy.
+     */
+    if (partitions == 0 && PQserverVersion(con) >= 140000)
+        copy_statement = "copy pgbench_accounts from stdin with (freeze on)";
+    else
+        copy_statement = "copy pgbench_accounts from stdin";
+
+    res = PQexec(con, copy_statement);
+
     if (PQresultStatus(res) != PGRES_COPY_IN)
     {
         pg_log_fatal("unexpected copy in result: %s", PQerrorMessage(con));