diff options
Diffstat (limited to '')
| -rw-r--r-- | contrib/vacuumlo/.gitignore | 3 | ||||
| -rw-r--r-- | contrib/vacuumlo/Makefile | 25 | ||||
| -rw-r--r-- | contrib/vacuumlo/t/001_basic.pl | 14 | ||||
| -rw-r--r-- | contrib/vacuumlo/vacuumlo.c | 547 |
4 files changed, 589 insertions, 0 deletions
diff --git a/contrib/vacuumlo/.gitignore b/contrib/vacuumlo/.gitignore new file mode 100644 index 0000000..f3f0ce3 --- /dev/null +++ b/contrib/vacuumlo/.gitignore @@ -0,0 +1,3 @@ +/vacuumlo + +/tmp_check/ diff --git a/contrib/vacuumlo/Makefile b/contrib/vacuumlo/Makefile new file mode 100644 index 0000000..6bc7b34 --- /dev/null +++ b/contrib/vacuumlo/Makefile @@ -0,0 +1,25 @@ +# contrib/vacuumlo/Makefile + +PGFILEDESC = "vacuumlo - removes orphaned large objects" +PGAPPICON = win32 + +PROGRAM = vacuumlo +OBJS = \ + $(WIN32RES) \ + vacuumlo.o + +TAP_TESTS = 1 + +PG_CPPFLAGS = -I$(libpq_srcdir) +PG_LIBS_INTERNAL = $(libpq_pgport) + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/vacuumlo +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/vacuumlo/t/001_basic.pl b/contrib/vacuumlo/t/001_basic.pl new file mode 100644 index 0000000..f4bfb2a --- /dev/null +++ b/contrib/vacuumlo/t/001_basic.pl @@ -0,0 +1,14 @@ + +# Copyright (c) 2021-2022, PostgreSQL Global Development Group + +use strict; +use warnings; + +use PostgreSQL::Test::Utils; +use Test::More; + +program_help_ok('vacuumlo'); +program_version_ok('vacuumlo'); +program_options_handling_ok('vacuumlo'); + +done_testing(); diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c new file mode 100644 index 0000000..b7c8f2c --- /dev/null +++ b/contrib/vacuumlo/vacuumlo.c @@ -0,0 +1,547 @@ +/*------------------------------------------------------------------------- + * + * vacuumlo.c + * This removes orphaned large objects from a database. + * + * Portions Copyright (c) 1996-2022, 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 "common/string.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 char *password = NULL; + + /* Note: password can be carried over from a previous call */ + if (param->pg_prompt == TRI_YES && !password) + password = simple_prompt("Password: ", false); + + /* + * 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] = password; + 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) && + !password && + param->pg_prompt != TRI_NO) + { + PQfinish(conn); + password = simple_prompt("Password: ", false); + new_pass = true; + } + } while (new_pass); + + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) + { + pg_log_error("%s", 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 'h': + param.pg_host = pg_strdup(optarg); + break; + case 'l': + param.transaction_limit = strtol(optarg, NULL, 10); + if (param.transaction_limit < 0) + pg_fatal("transaction limit must not be negative (0 disables)"); + break; + case 'n': + param.dry_run = 1; + param.verbose = 1; + break; + case 'p': + port = strtol(optarg, NULL, 10); + if ((port < 1) || (port > 65535)) + pg_fatal("invalid port number: %s", optarg); + 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: + /* getopt_long already emitted a complaint */ + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit(1); + } + } + + /* No database given? Show usage */ + if (optind >= argc) + { + pg_log_error("missing required argument: database name"); + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit(1); + } + + for (c = optind; c < argc; c++) + { + /* Work on selected database */ + rc += (vacuumlo(argv[c], ¶m) != 0); + } + + return rc; +} |