#include "postgres_fe.h"
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>
#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"

Include dependency graph for vacuumlo.c:

Data Structures
struct	_param

Macros
#define	BUFSIZE 1024

#define	PARAMS_ARRAY_SIZE 7

Enumerations
enum	trivalue { TRI_DEFAULT , TRI_NO , TRI_YES , TRI_DEFAULT , TRI_NO , TRI_YES , TRI_DEFAULT , TRI_NO , TRI_YES , TRI_DEFAULT , TRI_NO , TRI_YES }

Functions
static int	vacuumlo (const char database, const struct _param param)

static void	usage (const char *progname)

int	main (int argc, char **argv)

Macro Definition Documentation

◆ BUFSIZE

#define BUFSIZE 1024

Definition at line 32 of file vacuumlo.c.

◆ PARAMS_ARRAY_SIZE

#define PARAMS_ARRAY_SIZE 7

Enumeration Type Documentation

◆ trivalue

enum trivalue

Enumerator
TRI_DEFAULT
TRI_NO
TRI_YES
TRI_DEFAULT
TRI_NO
TRI_YES
TRI_DEFAULT
TRI_NO
TRI_YES
TRI_DEFAULT
TRI_NO
TRI_YES

Definition at line 34 of file vacuumlo.c.

 {
     TRI_DEFAULT,
     TRI_NO,
     TRI_YES,
 };

Function Documentation

◆ main()

int main	(	int	argc,
		char **	argv
	)

Definition at line 437 of file vacuumlo.c.

 {
     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], &param) != 0);
     }
  
     return rc;
 }

References _param::dry_run, exit(), get_progname(), getopt_long(), no_argument, optarg, optind, pg_fatal, _param::pg_host, pg_log_error, pg_log_error_hint, pg_logging_init(), _param::pg_port, _param::pg_prompt, pg_strdup(), _param::pg_user, port, _param::progname, progname, required_argument, _param::transaction_limit, TRI_DEFAULT, TRI_NO, TRI_YES, usage(), vacuumlo(), and _param::verbose.

◆ usage()

static void usage ( const char * progname )

static

Definition at line 414 of file vacuumlo.c.

 {
     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);
 }

References printf, and progname.

Referenced by exec_command_slash_command_help(), main(), parse_psql_options(), peek_buffer_usage(), pg_notification_queue_usage(), pg_store_delegated_credential(), show_buffer_usage(), and show_wal_usage().

◆ vacuumlo()

static int vacuumlo	(	const char *	database,
		const struct _param *	param
	)

static

Definition at line 62 of file vacuumlo.c.

 {
     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);
             PQfreemem(schema);
             PQfreemem(table);
             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);
 }

References ALWAYS_SECURE_SEARCH_PATH_SQL, atooid, buf, BUFSIZE, conn, CONNECTION_BAD, CppAsString2, _param::dry_run, fflush(), fprintf, i, lo_unlink(), PARAMS_ARRAY_SIZE, password, _param::pg_host, pg_log_error, _param::pg_port, _param::pg_prompt, _param::pg_user, PGRES_COMMAND_OK, PGRES_TUPLES_OK, PQclear(), PQconnectdbParams(), PQconnectionNeedsPassword(), PQerrorMessage(), PQescapeIdentifier(), PQexec(), PQfinish(), PQfreemem(), PQgetvalue(), PQntuples(), PQresultStatus(), PQserverVersion(), PQstatus(), PQTRANS_INERROR, PQtransactionStatus(), _param::progname, res, simple_prompt(), snprintf, generate_unaccent_rules::stdout, success, _param::transaction_limit, TRI_NO, TRI_YES, values, and _param::verbose.

Referenced by main().

Data Structures

Macros

Enumerations

Functions