summaryrefslogtreecommitdiffstats
path: root/contrib/vacuumlo/vacuumlo.c
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--contrib/vacuumlo/vacuumlo.c560
1 files changed, 560 insertions, 0 deletions
diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
new file mode 100644
index 0000000..0641885
--- /dev/null
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -0,0 +1,560 @@
+/*-------------------------------------------------------------------------
+ *
+ * vacuumlo.c
+ * This removes orphaned large objects from a database.
+ *
+ * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * contrib/vacuumlo/vacuumlo.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres_fe.h"
+
+#include <sys/stat.h>
+#include <fcntl.h>
+#include <unistd.h>
+#ifdef HAVE_TERMIOS_H
+#include <termios.h>
+#endif
+
+#include "catalog/pg_class_d.h"
+#include "common/connect.h"
+#include "common/logging.h"
+#include "getopt_long.h"
+#include "libpq-fe.h"
+#include "pg_getopt.h"
+
+#define BUFSIZE 1024
+
+enum trivalue
+{
+ TRI_DEFAULT,
+ TRI_NO,
+ TRI_YES
+};
+
+struct _param
+{
+ char *pg_user;
+ enum trivalue pg_prompt;
+ char *pg_port;
+ char *pg_host;
+ const char *progname;
+ int verbose;
+ int dry_run;
+ long transaction_limit;
+};
+
+static int vacuumlo(const char *database, const struct _param *param);
+static void usage(const char *progname);
+
+
+
+/*
+ * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
+ */
+static int
+vacuumlo(const char *database, const struct _param *param)
+{
+ PGconn *conn;
+ PGresult *res,
+ *res2;
+ char buf[BUFSIZE];
+ long matched;
+ long deleted;
+ int i;
+ bool new_pass;
+ bool success = true;
+ static bool have_password = false;
+ static char password[100];
+
+ /* Note: password can be carried over from a previous call */
+ if (param->pg_prompt == TRI_YES && !have_password)
+ {
+ simple_prompt("Password: ", password, sizeof(password), false);
+ have_password = true;
+ }
+
+ /*
+ * Start the connection. Loop until we have a password if requested by
+ * backend.
+ */
+ do
+ {
+#define PARAMS_ARRAY_SIZE 7
+
+ const char *keywords[PARAMS_ARRAY_SIZE];
+ const char *values[PARAMS_ARRAY_SIZE];
+
+ keywords[0] = "host";
+ values[0] = param->pg_host;
+ keywords[1] = "port";
+ values[1] = param->pg_port;
+ keywords[2] = "user";
+ values[2] = param->pg_user;
+ keywords[3] = "password";
+ values[3] = have_password ? password : NULL;
+ keywords[4] = "dbname";
+ values[4] = database;
+ keywords[5] = "fallback_application_name";
+ values[5] = param->progname;
+ keywords[6] = NULL;
+ values[6] = NULL;
+
+ new_pass = false;
+ conn = PQconnectdbParams(keywords, values, true);
+ if (!conn)
+ {
+ pg_log_error("connection to database \"%s\" failed", database);
+ return -1;
+ }
+
+ if (PQstatus(conn) == CONNECTION_BAD &&
+ PQconnectionNeedsPassword(conn) &&
+ !have_password &&
+ param->pg_prompt != TRI_NO)
+ {
+ PQfinish(conn);
+ simple_prompt("Password: ", password, sizeof(password), false);
+ have_password = true;
+ new_pass = true;
+ }
+ } while (new_pass);
+
+ /* check to see that the backend connection was successfully made */
+ if (PQstatus(conn) == CONNECTION_BAD)
+ {
+ pg_log_error("connection to database \"%s\" failed: %s",
+ PQdb(conn) ? PQdb(conn) : "", PQerrorMessage(conn));
+ PQfinish(conn);
+ return -1;
+ }
+
+ if (param->verbose)
+ {
+ fprintf(stdout, "Connected to database \"%s\"\n", database);
+ if (param->dry_run)
+ fprintf(stdout, "Test run: no large objects will be removed!\n");
+ }
+
+ res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ {
+ pg_log_error("failed to set search_path: %s", PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+ PQclear(res);
+
+ /*
+ * First we create and populate the LO temp table
+ */
+ buf[0] = '\0';
+ strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
+ if (PQserverVersion(conn) >= 90000)
+ strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
+ else
+ strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
+ res = PQexec(conn, buf);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("failed to create temp table: %s", PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+ PQclear(res);
+
+ /*
+ * Analyze the temp table so that planner will generate decent plans for
+ * the DELETEs below.
+ */
+ buf[0] = '\0';
+ strcat(buf, "ANALYZE vacuum_l");
+ res = PQexec(conn, buf);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("failed to vacuum temp table: %s", PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+ PQclear(res);
+
+ /*
+ * Now find any candidate tables that have columns of type oid.
+ *
+ * NOTE: we ignore system tables and temp tables by the expedient of
+ * rejecting tables in schemas named 'pg_*'. In particular, the temp
+ * table formed above is ignored, and pg_largeobject will be too. If
+ * either of these were scanned, obviously we'd end up with nothing to
+ * delete...
+ */
+ buf[0] = '\0';
+ strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
+ strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
+ strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
+ strcat(buf, " AND a.attrelid = c.oid ");
+ strcat(buf, " AND a.atttypid = t.oid ");
+ strcat(buf, " AND c.relnamespace = s.oid ");
+ strcat(buf, " AND t.typname in ('oid', 'lo') ");
+ strcat(buf, " AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
+ strcat(buf, " AND s.nspname !~ '^pg_'");
+ res = PQexec(conn, buf);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ {
+ pg_log_error("failed to find OID columns: %s", PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ char *schema,
+ *table,
+ *field;
+
+ schema = PQgetvalue(res, i, 0);
+ table = PQgetvalue(res, i, 1);
+ field = PQgetvalue(res, i, 2);
+
+ if (param->verbose)
+ fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
+
+ schema = PQescapeIdentifier(conn, schema, strlen(schema));
+ table = PQescapeIdentifier(conn, table, strlen(table));
+ field = PQescapeIdentifier(conn, field, strlen(field));
+
+ if (!schema || !table || !field)
+ {
+ pg_log_error("%s", PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ if (schema != NULL)
+ PQfreemem(schema);
+ if (table != NULL)
+ PQfreemem(table);
+ if (field != NULL)
+ PQfreemem(field);
+ return -1;
+ }
+
+ snprintf(buf, BUFSIZE,
+ "DELETE FROM vacuum_l "
+ "WHERE lo IN (SELECT %s FROM %s.%s)",
+ field, schema, table);
+ res2 = PQexec(conn, buf);
+ if (PQresultStatus(res2) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("failed to check %s in table %s.%s: %s",
+ field, schema, table, PQerrorMessage(conn));
+ PQclear(res2);
+ PQclear(res);
+ PQfinish(conn);
+ PQfreemem(schema);
+ PQfreemem(table);
+ PQfreemem(field);
+ return -1;
+ }
+ PQclear(res2);
+
+ PQfreemem(schema);
+ PQfreemem(table);
+ PQfreemem(field);
+ }
+ PQclear(res);
+
+ /*
+ * Now, those entries remaining in vacuum_l are orphans. Delete 'em.
+ *
+ * We don't want to run each delete as an individual transaction, because
+ * the commit overhead would be high. However, since 9.0 the backend will
+ * acquire a lock per deleted LO, so deleting too many LOs per transaction
+ * risks running out of room in the shared-memory lock table. Accordingly,
+ * we delete up to transaction_limit LOs per transaction.
+ */
+ res = PQexec(conn, "begin");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("failed to start transaction: %s", PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+ PQclear(res);
+
+ buf[0] = '\0';
+ strcat(buf,
+ "DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l");
+ res = PQexec(conn, buf);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("DECLARE CURSOR failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+ PQclear(res);
+
+ snprintf(buf, BUFSIZE, "FETCH FORWARD %ld IN myportal",
+ param->transaction_limit > 0 ? param->transaction_limit : 1000L);
+
+ deleted = 0;
+
+ do
+ {
+ res = PQexec(conn, buf);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ {
+ pg_log_error("FETCH FORWARD failed: %s", PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+
+ matched = PQntuples(res);
+ if (matched <= 0)
+ {
+ /* at end of resultset */
+ PQclear(res);
+ break;
+ }
+
+ for (i = 0; i < matched; i++)
+ {
+ Oid lo = atooid(PQgetvalue(res, i, 0));
+
+ if (param->verbose)
+ {
+ fprintf(stdout, "\rRemoving lo %6u ", lo);
+ fflush(stdout);
+ }
+
+ if (param->dry_run == 0)
+ {
+ if (lo_unlink(conn, lo) < 0)
+ {
+ pg_log_error("failed to remove lo %u: %s", lo,
+ PQerrorMessage(conn));
+ if (PQtransactionStatus(conn) == PQTRANS_INERROR)
+ {
+ success = false;
+ break; /* out of inner for-loop */
+ }
+ }
+ else
+ deleted++;
+ }
+ else
+ deleted++;
+
+ if (param->transaction_limit > 0 &&
+ (deleted % param->transaction_limit) == 0)
+ {
+ res2 = PQexec(conn, "commit");
+ if (PQresultStatus(res2) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("failed to commit transaction: %s",
+ PQerrorMessage(conn));
+ PQclear(res2);
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+ PQclear(res2);
+ res2 = PQexec(conn, "begin");
+ if (PQresultStatus(res2) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("failed to start transaction: %s",
+ PQerrorMessage(conn));
+ PQclear(res2);
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+ PQclear(res2);
+ }
+ }
+
+ PQclear(res);
+ } while (success);
+
+ /*
+ * That's all folks!
+ */
+ res = PQexec(conn, "commit");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("failed to commit transaction: %s",
+ PQerrorMessage(conn));
+ PQclear(res);
+ PQfinish(conn);
+ return -1;
+ }
+ PQclear(res);
+
+ PQfinish(conn);
+
+ if (param->verbose)
+ {
+ if (param->dry_run)
+ fprintf(stdout, "\rWould remove %ld large objects from database \"%s\".\n",
+ deleted, database);
+ else if (success)
+ fprintf(stdout,
+ "\rSuccessfully removed %ld large objects from database \"%s\".\n",
+ deleted, database);
+ else
+ fprintf(stdout, "\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
+ database, deleted, matched);
+ }
+
+ return ((param->dry_run || success) ? 0 : -1);
+}
+
+static void
+usage(const char *progname)
+{
+ printf("%s removes unreferenced large objects from databases.\n\n", progname);
+ printf("Usage:\n %s [OPTION]... DBNAME...\n\n", progname);
+ printf("Options:\n");
+ printf(" -l, --limit=LIMIT commit after removing each LIMIT large objects\n");
+ printf(" -n, --dry-run don't remove large objects, just show what would be done\n");
+ printf(" -v, --verbose write a lot of progress messages\n");
+ printf(" -V, --version output version information, then exit\n");
+ printf(" -?, --help show this help, then exit\n");
+ printf("\nConnection options:\n");
+ printf(" -h, --host=HOSTNAME database server host or socket directory\n");
+ printf(" -p, --port=PORT database server port\n");
+ printf(" -U, --username=USERNAME user name to connect as\n");
+ printf(" -w, --no-password never prompt for password\n");
+ printf(" -W, --password force password prompt\n");
+ printf("\n");
+ printf("Report bugs to <%s>.\n", PACKAGE_BUGREPORT);
+ printf("%s home page: <%s>\n", PACKAGE_NAME, PACKAGE_URL);
+}
+
+
+int
+main(int argc, char **argv)
+{
+ static struct option long_options[] = {
+ {"host", required_argument, NULL, 'h'},
+ {"limit", required_argument, NULL, 'l'},
+ {"dry-run", no_argument, NULL, 'n'},
+ {"port", required_argument, NULL, 'p'},
+ {"username", required_argument, NULL, 'U'},
+ {"verbose", no_argument, NULL, 'v'},
+ {"version", no_argument, NULL, 'V'},
+ {"no-password", no_argument, NULL, 'w'},
+ {"password", no_argument, NULL, 'W'},
+ {"help", no_argument, NULL, '?'},
+ {NULL, 0, NULL, 0}
+ };
+
+ int rc = 0;
+ struct _param param;
+ int c;
+ int port;
+ const char *progname;
+ int optindex;
+
+ pg_logging_init(argv[0]);
+ progname = get_progname(argv[0]);
+
+ /* Set default parameter values */
+ param.pg_user = NULL;
+ param.pg_prompt = TRI_DEFAULT;
+ param.pg_host = NULL;
+ param.pg_port = NULL;
+ param.progname = progname;
+ param.verbose = 0;
+ param.dry_run = 0;
+ param.transaction_limit = 1000;
+
+ /* Process command-line arguments */
+ if (argc > 1)
+ {
+ if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
+ {
+ usage(progname);
+ exit(0);
+ }
+ if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
+ {
+ puts("vacuumlo (PostgreSQL) " PG_VERSION);
+ exit(0);
+ }
+ }
+
+ while ((c = getopt_long(argc, argv, "h:l:np:U:vwW", long_options, &optindex)) != -1)
+ {
+ switch (c)
+ {
+ case '?':
+ fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
+ exit(1);
+ case 'h':
+ param.pg_host = pg_strdup(optarg);
+ break;
+ case 'l':
+ param.transaction_limit = strtol(optarg, NULL, 10);
+ if (param.transaction_limit < 0)
+ {
+ pg_log_error("transaction limit must not be negative (0 disables)");
+ exit(1);
+ }
+ break;
+ case 'n':
+ param.dry_run = 1;
+ param.verbose = 1;
+ break;
+ case 'p':
+ port = strtol(optarg, NULL, 10);
+ if ((port < 1) || (port > 65535))
+ {
+ pg_log_error("invalid port number: %s", optarg);
+ exit(1);
+ }
+ param.pg_port = pg_strdup(optarg);
+ break;
+ case 'U':
+ param.pg_user = pg_strdup(optarg);
+ break;
+ case 'v':
+ param.verbose = 1;
+ break;
+ case 'w':
+ param.pg_prompt = TRI_NO;
+ break;
+ case 'W':
+ param.pg_prompt = TRI_YES;
+ break;
+ default:
+ fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
+ exit(1);
+ }
+ }
+
+ /* No database given? Show usage */
+ if (optind >= argc)
+ {
+ pg_log_error("missing required argument: database name");
+ fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
+ exit(1);
+ }
+
+ for (c = optind; c < argc; c++)
+ {
+ /* Work on selected database */
+ rc += (vacuumlo(argv[c], &param) != 0);
+ }
+
+ return rc;
+}
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-26 04:40:51 +0000