Re: pgbench -f and vacuum - Mailing list pgsql-hackers

> On Sun, Dec 14, 2014 at 11:43 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>>> If we care enough about that case to attempt the vacuum anyway then we
>>> need to do something about the error message; either squelch it or
>>> check for the existence of the tables before attempting to
>>> vacuum. Since there's no way to squelch in the server logfile, I think
>>> checking for the table is the right answer.
>>
>> Fair enough. I will come up with "checking for table before vacuum"
>> approach.
> 
> +1 for this approach.

Here is the patch I promised.

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/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index d69036a..6b07932 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -88,6 +88,8 @@ static int    pthread_create(pthread_t *thread, pthread_attr_t *attr, void *(*startstatic int
pthread_join(pthread_tth, void **thread_return);#endif
 
+static void executeStatement2(PGconn *con, const char *sql, const char *table);
+static bool is_table_exists(PGconn *con, const char
*table);/********************************************************************* some configurable parameters */
 
@@ -605,6 +607,54 @@ executeStatement(PGconn *con, const char *sql)    PQclear(res);}
+/* call executeStatement() if table exists */
+static void
+executeStatement2(PGconn *con, const char *sql, const char *table)
+{
+    char        buf[1024];
+
+    snprintf(buf, sizeof(buf)-1, "%s %s", sql, table);
+
+    if (is_table_exists(con, table))
+            executeStatement(con, buf);
+}
+
+/*
+ * Return true if the table exists
+ */
+static bool
+is_table_exists(PGconn *con, const char *table)
+{
+    PGresult    *res;
+    char        buf[1024];
+    char        *result;
+
+    snprintf(buf, sizeof(buf)-1, "SELECT to_regclass('%s') IS NULL", table);
+
+    res = PQexec(con, buf);
+    if (PQresultStatus(res) != PGRES_TUPLES_OK)
+    {
+        return false;
+    }
+
+    result = PQgetvalue(res, 0, 0);
+
+    if (result == NULL)
+    {
+        PQclear(res);
+        return false;
+    }
+
+    if (*result == 't')
+    {
+        PQclear(res);
+        return false;    /* table does not exist */
+    }
+
+    PQclear(res);
+    return true;
+}
+/* set up a connection to the backend */static PGconn *doConnect(void)
@@ -3197,17 +3247,34 @@ main(int argc, char **argv)    if (!is_no_vacuum)    {
-        fprintf(stderr, "starting vacuum...");
-        executeStatement(con, "vacuum pgbench_branches");
-        executeStatement(con, "vacuum pgbench_tellers");
-        executeStatement(con, "truncate pgbench_history");
-        fprintf(stderr, "end.\n");
+        bool msg1 = false;
+        bool msg2 = false;
+
+        if (is_table_exists(con, "pgbench_branches"))
+            msg1 = true;
+
+        if (is_table_exists(con, "pgbench_accounts"))
+            msg2 = true;
+
+        if (msg1)
+            fprintf(stderr, "starting vacuum...");
+
+        executeStatement2(con, "vacuum", "pgbench_branches");
+        executeStatement2(con, "vacuum", "pgbench_tellers");
+        executeStatement2(con, "truncate", "pgbench_history");
+
+        if (msg1)
+            fprintf(stderr, "end.\n");        if (do_vacuum_accounts)        {
-            fprintf(stderr, "starting vacuum pgbench_accounts...");
-            executeStatement(con, "vacuum analyze pgbench_accounts");
-            fprintf(stderr, "end.\n");
+            if (msg2)
+                fprintf(stderr, "starting vacuum pgbench_accounts...");
+
+            executeStatement2(con, "vacuum analyze", "pgbench_accounts");
+
+            if (msg2)
+                fprintf(stderr, "end.\n");        }    }    PQfinish(con);