LCOV - code coverage report
Current view: top level - contrib/vacuumlo - vacuumlo.c (source / functions) Hit Total Coverage
Test: PostgreSQL 15devel Lines: 43 281 15.3 %
Date: 2021-11-29 04:09:17 Functions: 2 3 66.7 %
Legend: Lines: hit not hit

          Line data    Source code
       1             : /*-------------------------------------------------------------------------
       2             :  *
       3             :  * vacuumlo.c
       4             :  *    This removes orphaned large objects from a database.
       5             :  *
       6             :  * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
       7             :  * Portions Copyright (c) 1994, Regents of the University of California
       8             :  *
       9             :  *
      10             :  * IDENTIFICATION
      11             :  *    contrib/vacuumlo/vacuumlo.c
      12             :  *
      13             :  *-------------------------------------------------------------------------
      14             :  */
      15             : #include "postgres_fe.h"
      16             : 
      17             : #include <sys/stat.h>
      18             : #include <fcntl.h>
      19             : #include <unistd.h>
      20             : #ifdef HAVE_TERMIOS_H
      21             : #include <termios.h>
      22             : #endif
      23             : 
      24             : #include "catalog/pg_class_d.h"
      25             : #include "common/connect.h"
      26             : #include "common/logging.h"
      27             : #include "common/string.h"
      28             : #include "getopt_long.h"
      29             : #include "libpq-fe.h"
      30             : #include "pg_getopt.h"
      31             : 
      32             : #define BUFSIZE         1024
      33             : 
      34             : enum trivalue
      35             : {
      36             :     TRI_DEFAULT,
      37             :     TRI_NO,
      38             :     TRI_YES
      39             : };
      40             : 
      41             : struct _param
      42             : {
      43             :     char       *pg_user;
      44             :     enum trivalue pg_prompt;
      45             :     char       *pg_port;
      46             :     char       *pg_host;
      47             :     const char *progname;
      48             :     int         verbose;
      49             :     int         dry_run;
      50             :     long        transaction_limit;
      51             : };
      52             : 
      53             : static int  vacuumlo(const char *database, const struct _param *param);
      54             : static void usage(const char *progname);
      55             : 
      56             : 
      57             : 
      58             : /*
      59             :  * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
      60             :  */
      61             : static int
      62           0 : vacuumlo(const char *database, const struct _param *param)
      63             : {
      64             :     PGconn     *conn;
      65             :     PGresult   *res,
      66             :                *res2;
      67             :     char        buf[BUFSIZE];
      68             :     long        matched;
      69             :     long        deleted;
      70             :     int         i;
      71             :     bool        new_pass;
      72           0 :     bool        success = true;
      73             :     static char *password = NULL;
      74             : 
      75             :     /* Note: password can be carried over from a previous call */
      76           0 :     if (param->pg_prompt == TRI_YES && !password)
      77           0 :         password = simple_prompt("Password: ", false);
      78             : 
      79             :     /*
      80             :      * Start the connection.  Loop until we have a password if requested by
      81             :      * backend.
      82             :      */
      83             :     do
      84             :     {
      85             : #define PARAMS_ARRAY_SIZE      7
      86             : 
      87             :         const char *keywords[PARAMS_ARRAY_SIZE];
      88             :         const char *values[PARAMS_ARRAY_SIZE];
      89             : 
      90           0 :         keywords[0] = "host";
      91           0 :         values[0] = param->pg_host;
      92           0 :         keywords[1] = "port";
      93           0 :         values[1] = param->pg_port;
      94           0 :         keywords[2] = "user";
      95           0 :         values[2] = param->pg_user;
      96           0 :         keywords[3] = "password";
      97           0 :         values[3] = password;
      98           0 :         keywords[4] = "dbname";
      99           0 :         values[4] = database;
     100           0 :         keywords[5] = "fallback_application_name";
     101           0 :         values[5] = param->progname;
     102           0 :         keywords[6] = NULL;
     103           0 :         values[6] = NULL;
     104             : 
     105           0 :         new_pass = false;
     106           0 :         conn = PQconnectdbParams(keywords, values, true);
     107           0 :         if (!conn)
     108             :         {
     109           0 :             pg_log_error("connection to database \"%s\" failed", database);
     110           0 :             return -1;
     111             :         }
     112             : 
     113           0 :         if (PQstatus(conn) == CONNECTION_BAD &&
     114           0 :             PQconnectionNeedsPassword(conn) &&
     115           0 :             !password &&
     116           0 :             param->pg_prompt != TRI_NO)
     117             :         {
     118           0 :             PQfinish(conn);
     119           0 :             password = simple_prompt("Password: ", false);
     120           0 :             new_pass = true;
     121             :         }
     122           0 :     } while (new_pass);
     123             : 
     124             :     /* check to see that the backend connection was successfully made */
     125           0 :     if (PQstatus(conn) == CONNECTION_BAD)
     126             :     {
     127           0 :         pg_log_error("%s", PQerrorMessage(conn));
     128           0 :         PQfinish(conn);
     129           0 :         return -1;
     130             :     }
     131             : 
     132           0 :     if (param->verbose)
     133             :     {
     134           0 :         fprintf(stdout, "Connected to database \"%s\"\n", database);
     135           0 :         if (param->dry_run)
     136           0 :             fprintf(stdout, "Test run: no large objects will be removed!\n");
     137             :     }
     138             : 
     139           0 :     res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL);
     140           0 :     if (PQresultStatus(res) != PGRES_TUPLES_OK)
     141             :     {
     142           0 :         pg_log_error("failed to set search_path: %s", PQerrorMessage(conn));
     143           0 :         PQclear(res);
     144           0 :         PQfinish(conn);
     145           0 :         return -1;
     146             :     }
     147           0 :     PQclear(res);
     148             : 
     149             :     /*
     150             :      * First we create and populate the LO temp table
     151             :      */
     152           0 :     buf[0] = '\0';
     153           0 :     strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
     154           0 :     if (PQserverVersion(conn) >= 90000)
     155           0 :         strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
     156             :     else
     157           0 :         strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
     158           0 :     res = PQexec(conn, buf);
     159           0 :     if (PQresultStatus(res) != PGRES_COMMAND_OK)
     160             :     {
     161           0 :         pg_log_error("failed to create temp table: %s", PQerrorMessage(conn));
     162           0 :         PQclear(res);
     163           0 :         PQfinish(conn);
     164           0 :         return -1;
     165             :     }
     166           0 :     PQclear(res);
     167             : 
     168             :     /*
     169             :      * Analyze the temp table so that planner will generate decent plans for
     170             :      * the DELETEs below.
     171             :      */
     172           0 :     buf[0] = '\0';
     173           0 :     strcat(buf, "ANALYZE vacuum_l");
     174           0 :     res = PQexec(conn, buf);
     175           0 :     if (PQresultStatus(res) != PGRES_COMMAND_OK)
     176             :     {
     177           0 :         pg_log_error("failed to vacuum temp table: %s", PQerrorMessage(conn));
     178           0 :         PQclear(res);
     179           0 :         PQfinish(conn);
     180           0 :         return -1;
     181             :     }
     182           0 :     PQclear(res);
     183             : 
     184             :     /*
     185             :      * Now find any candidate tables that have columns of type oid.
     186             :      *
     187             :      * NOTE: we ignore system tables and temp tables by the expedient of
     188             :      * rejecting tables in schemas named 'pg_*'.  In particular, the temp
     189             :      * table formed above is ignored, and pg_largeobject will be too. If
     190             :      * either of these were scanned, obviously we'd end up with nothing to
     191             :      * delete...
     192             :      */
     193           0 :     buf[0] = '\0';
     194           0 :     strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
     195           0 :     strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
     196           0 :     strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
     197           0 :     strcat(buf, "      AND a.attrelid = c.oid ");
     198           0 :     strcat(buf, "      AND a.atttypid = t.oid ");
     199           0 :     strcat(buf, "      AND c.relnamespace = s.oid ");
     200           0 :     strcat(buf, "      AND t.typname in ('oid', 'lo') ");
     201           0 :     strcat(buf, "      AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
     202           0 :     strcat(buf, "      AND s.nspname !~ '^pg_'");
     203           0 :     res = PQexec(conn, buf);
     204           0 :     if (PQresultStatus(res) != PGRES_TUPLES_OK)
     205             :     {
     206           0 :         pg_log_error("failed to find OID columns: %s", PQerrorMessage(conn));
     207           0 :         PQclear(res);
     208           0 :         PQfinish(conn);
     209           0 :         return -1;
     210             :     }
     211             : 
     212           0 :     for (i = 0; i < PQntuples(res); i++)
     213             :     {
     214             :         char       *schema,
     215             :                    *table,
     216             :                    *field;
     217             : 
     218           0 :         schema = PQgetvalue(res, i, 0);
     219           0 :         table = PQgetvalue(res, i, 1);
     220           0 :         field = PQgetvalue(res, i, 2);
     221             : 
     222           0 :         if (param->verbose)
     223           0 :             fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
     224             : 
     225           0 :         schema = PQescapeIdentifier(conn, schema, strlen(schema));
     226           0 :         table = PQescapeIdentifier(conn, table, strlen(table));
     227           0 :         field = PQescapeIdentifier(conn, field, strlen(field));
     228             : 
     229           0 :         if (!schema || !table || !field)
     230             :         {
     231           0 :             pg_log_error("%s", PQerrorMessage(conn));
     232           0 :             PQclear(res);
     233           0 :             PQfinish(conn);
     234           0 :             if (schema != NULL)
     235           0 :                 PQfreemem(schema);
     236           0 :             if (table != NULL)
     237           0 :                 PQfreemem(table);
     238           0 :             if (field != NULL)
     239           0 :                 PQfreemem(field);
     240           0 :             return -1;
     241             :         }
     242             : 
     243           0 :         snprintf(buf, BUFSIZE,
     244             :                  "DELETE FROM vacuum_l "
     245             :                  "WHERE lo IN (SELECT %s FROM %s.%s)",
     246             :                  field, schema, table);
     247           0 :         res2 = PQexec(conn, buf);
     248           0 :         if (PQresultStatus(res2) != PGRES_COMMAND_OK)
     249             :         {
     250           0 :             pg_log_error("failed to check %s in table %s.%s: %s",
     251             :                          field, schema, table, PQerrorMessage(conn));
     252           0 :             PQclear(res2);
     253           0 :             PQclear(res);
     254           0 :             PQfinish(conn);
     255           0 :             PQfreemem(schema);
     256           0 :             PQfreemem(table);
     257           0 :             PQfreemem(field);
     258           0 :             return -1;
     259             :         }
     260           0 :         PQclear(res2);
     261             : 
     262           0 :         PQfreemem(schema);
     263           0 :         PQfreemem(table);
     264           0 :         PQfreemem(field);
     265             :     }
     266           0 :     PQclear(res);
     267             : 
     268             :     /*
     269             :      * Now, those entries remaining in vacuum_l are orphans.  Delete 'em.
     270             :      *
     271             :      * We don't want to run each delete as an individual transaction, because
     272             :      * the commit overhead would be high.  However, since 9.0 the backend will
     273             :      * acquire a lock per deleted LO, so deleting too many LOs per transaction
     274             :      * risks running out of room in the shared-memory lock table. Accordingly,
     275             :      * we delete up to transaction_limit LOs per transaction.
     276             :      */
     277           0 :     res = PQexec(conn, "begin");
     278           0 :     if (PQresultStatus(res) != PGRES_COMMAND_OK)
     279             :     {
     280           0 :         pg_log_error("failed to start transaction: %s", PQerrorMessage(conn));
     281           0 :         PQclear(res);
     282           0 :         PQfinish(conn);
     283           0 :         return -1;
     284             :     }
     285           0 :     PQclear(res);
     286             : 
     287           0 :     buf[0] = '\0';
     288           0 :     strcat(buf,
     289             :            "DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l");
     290           0 :     res = PQexec(conn, buf);
     291           0 :     if (PQresultStatus(res) != PGRES_COMMAND_OK)
     292             :     {
     293           0 :         pg_log_error("DECLARE CURSOR failed: %s", PQerrorMessage(conn));
     294           0 :         PQclear(res);
     295           0 :         PQfinish(conn);
     296           0 :         return -1;
     297             :     }
     298           0 :     PQclear(res);
     299             : 
     300           0 :     snprintf(buf, BUFSIZE, "FETCH FORWARD %ld IN myportal",
     301           0 :              param->transaction_limit > 0 ? param->transaction_limit : 1000L);
     302             : 
     303           0 :     deleted = 0;
     304             : 
     305             :     do
     306             :     {
     307           0 :         res = PQexec(conn, buf);
     308           0 :         if (PQresultStatus(res) != PGRES_TUPLES_OK)
     309             :         {
     310           0 :             pg_log_error("FETCH FORWARD failed: %s", PQerrorMessage(conn));
     311           0 :             PQclear(res);
     312           0 :             PQfinish(conn);
     313           0 :             return -1;
     314             :         }
     315             : 
     316           0 :         matched = PQntuples(res);
     317           0 :         if (matched <= 0)
     318             :         {
     319             :             /* at end of resultset */
     320           0 :             PQclear(res);
     321           0 :             break;
     322             :         }
     323             : 
     324           0 :         for (i = 0; i < matched; i++)
     325             :         {
     326           0 :             Oid         lo = atooid(PQgetvalue(res, i, 0));
     327             : 
     328           0 :             if (param->verbose)
     329             :             {
     330           0 :                 fprintf(stdout, "\rRemoving lo %6u   ", lo);
     331           0 :                 fflush(stdout);
     332             :             }
     333             : 
     334           0 :             if (param->dry_run == 0)
     335             :             {
     336           0 :                 if (lo_unlink(conn, lo) < 0)
     337             :                 {
     338           0 :                     pg_log_error("failed to remove lo %u: %s", lo,
     339             :                                  PQerrorMessage(conn));
     340           0 :                     if (PQtransactionStatus(conn) == PQTRANS_INERROR)
     341             :                     {
     342           0 :                         success = false;
     343           0 :                         break;  /* out of inner for-loop */
     344             :                     }
     345             :                 }
     346             :                 else
     347           0 :                     deleted++;
     348             :             }
     349             :             else
     350           0 :                 deleted++;
     351             : 
     352           0 :             if (param->transaction_limit > 0 &&
     353           0 :                 (deleted % param->transaction_limit) == 0)
     354             :             {
     355           0 :                 res2 = PQexec(conn, "commit");
     356           0 :                 if (PQresultStatus(res2) != PGRES_COMMAND_OK)
     357             :                 {
     358           0 :                     pg_log_error("failed to commit transaction: %s",
     359             :                                  PQerrorMessage(conn));
     360           0 :                     PQclear(res2);
     361           0 :                     PQclear(res);
     362           0 :                     PQfinish(conn);
     363           0 :                     return -1;
     364             :                 }
     365           0 :                 PQclear(res2);
     366           0 :                 res2 = PQexec(conn, "begin");
     367           0 :                 if (PQresultStatus(res2) != PGRES_COMMAND_OK)
     368             :                 {
     369           0 :                     pg_log_error("failed to start transaction: %s",
     370             :                                  PQerrorMessage(conn));
     371           0 :                     PQclear(res2);
     372           0 :                     PQclear(res);
     373           0 :                     PQfinish(conn);
     374           0 :                     return -1;
     375             :                 }
     376           0 :                 PQclear(res2);
     377             :             }
     378             :         }
     379             : 
     380           0 :         PQclear(res);
     381           0 :     } while (success);
     382             : 
     383             :     /*
     384             :      * That's all folks!
     385             :      */
     386           0 :     res = PQexec(conn, "commit");
     387           0 :     if (PQresultStatus(res) != PGRES_COMMAND_OK)
     388             :     {
     389           0 :         pg_log_error("failed to commit transaction: %s",
     390             :                      PQerrorMessage(conn));
     391           0 :         PQclear(res);
     392           0 :         PQfinish(conn);
     393           0 :         return -1;
     394             :     }
     395           0 :     PQclear(res);
     396             : 
     397           0 :     PQfinish(conn);
     398             : 
     399           0 :     if (param->verbose)
     400             :     {
     401           0 :         if (param->dry_run)
     402           0 :             fprintf(stdout, "\rWould remove %ld large objects from database \"%s\".\n",
     403             :                     deleted, database);
     404           0 :         else if (success)
     405           0 :             fprintf(stdout,
     406             :                     "\rSuccessfully removed %ld large objects from database \"%s\".\n",
     407             :                     deleted, database);
     408             :         else
     409           0 :             fprintf(stdout, "\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
     410             :                     database, deleted, matched);
     411             :     }
     412             : 
     413           0 :     return ((param->dry_run || success) ? 0 : -1);
     414             : }
     415             : 
     416             : static void
     417           2 : usage(const char *progname)
     418             : {
     419           2 :     printf("%s removes unreferenced large objects from databases.\n\n", progname);
     420           2 :     printf("Usage:\n  %s [OPTION]... DBNAME...\n\n", progname);
     421           2 :     printf("Options:\n");
     422           2 :     printf("  -l, --limit=LIMIT         commit after removing each LIMIT large objects\n");
     423           2 :     printf("  -n, --dry-run             don't remove large objects, just show what would be done\n");
     424           2 :     printf("  -v, --verbose             write a lot of progress messages\n");
     425           2 :     printf("  -V, --version             output version information, then exit\n");
     426           2 :     printf("  -?, --help                show this help, then exit\n");
     427           2 :     printf("\nConnection options:\n");
     428           2 :     printf("  -h, --host=HOSTNAME       database server host or socket directory\n");
     429           2 :     printf("  -p, --port=PORT           database server port\n");
     430           2 :     printf("  -U, --username=USERNAME   user name to connect as\n");
     431           2 :     printf("  -w, --no-password         never prompt for password\n");
     432           2 :     printf("  -W, --password            force password prompt\n");
     433           2 :     printf("\n");
     434           2 :     printf("Report bugs to <%s>.\n", PACKAGE_BUGREPORT);
     435           2 :     printf("%s home page: <%s>\n", PACKAGE_NAME, PACKAGE_URL);
     436           2 : }
     437             : 
     438             : 
     439             : int
     440           6 : main(int argc, char **argv)
     441             : {
     442             :     static struct option long_options[] = {
     443             :         {"host", required_argument, NULL, 'h'},
     444             :         {"limit", required_argument, NULL, 'l'},
     445             :         {"dry-run", no_argument, NULL, 'n'},
     446             :         {"port", required_argument, NULL, 'p'},
     447             :         {"username", required_argument, NULL, 'U'},
     448             :         {"verbose", no_argument, NULL, 'v'},
     449             :         {"version", no_argument, NULL, 'V'},
     450             :         {"no-password", no_argument, NULL, 'w'},
     451             :         {"password", no_argument, NULL, 'W'},
     452             :         {"help", no_argument, NULL, '?'},
     453             :         {NULL, 0, NULL, 0}
     454             :     };
     455             : 
     456           6 :     int         rc = 0;
     457             :     struct _param param;
     458             :     int         c;
     459             :     int         port;
     460             :     const char *progname;
     461             :     int         optindex;
     462             : 
     463           6 :     pg_logging_init(argv[0]);
     464           6 :     progname = get_progname(argv[0]);
     465             : 
     466             :     /* Set default parameter values */
     467           6 :     param.pg_user = NULL;
     468           6 :     param.pg_prompt = TRI_DEFAULT;
     469           6 :     param.pg_host = NULL;
     470           6 :     param.pg_port = NULL;
     471           6 :     param.progname = progname;
     472           6 :     param.verbose = 0;
     473           6 :     param.dry_run = 0;
     474           6 :     param.transaction_limit = 1000;
     475             : 
     476             :     /* Process command-line arguments */
     477           6 :     if (argc > 1)
     478             :     {
     479           6 :         if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
     480             :         {
     481           2 :             usage(progname);
     482           2 :             exit(0);
     483             :         }
     484           4 :         if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
     485             :         {
     486           2 :             puts("vacuumlo (PostgreSQL) " PG_VERSION);
     487           2 :             exit(0);
     488             :         }
     489             :     }
     490             : 
     491           2 :     while ((c = getopt_long(argc, argv, "h:l:np:U:vwW", long_options, &optindex)) != -1)
     492             :     {
     493           2 :         switch (c)
     494             :         {
     495           2 :             case '?':
     496           2 :                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
     497           2 :                 exit(1);
     498           0 :             case 'h':
     499           0 :                 param.pg_host = pg_strdup(optarg);
     500           0 :                 break;
     501           0 :             case 'l':
     502           0 :                 param.transaction_limit = strtol(optarg, NULL, 10);
     503           0 :                 if (param.transaction_limit < 0)
     504             :                 {
     505           0 :                     pg_log_error("transaction limit must not be negative (0 disables)");
     506           0 :                     exit(1);
     507             :                 }
     508           0 :                 break;
     509           0 :             case 'n':
     510           0 :                 param.dry_run = 1;
     511           0 :                 param.verbose = 1;
     512           0 :                 break;
     513           0 :             case 'p':
     514           0 :                 port = strtol(optarg, NULL, 10);
     515           0 :                 if ((port < 1) || (port > 65535))
     516             :                 {
     517           0 :                     pg_log_error("invalid port number: %s", optarg);
     518           0 :                     exit(1);
     519             :                 }
     520           0 :                 param.pg_port = pg_strdup(optarg);
     521           0 :                 break;
     522           0 :             case 'U':
     523           0 :                 param.pg_user = pg_strdup(optarg);
     524           0 :                 break;
     525           0 :             case 'v':
     526           0 :                 param.verbose = 1;
     527           0 :                 break;
     528           0 :             case 'w':
     529           0 :                 param.pg_prompt = TRI_NO;
     530           0 :                 break;
     531           0 :             case 'W':
     532           0 :                 param.pg_prompt = TRI_YES;
     533           0 :                 break;
     534           0 :             default:
     535           0 :                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
     536           0 :                 exit(1);
     537             :         }
     538             :     }
     539             : 
     540             :     /* No database given? Show usage */
     541           0 :     if (optind >= argc)
     542             :     {
     543           0 :         pg_log_error("missing required argument: database name");
     544           0 :         fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
     545           0 :         exit(1);
     546             :     }
     547             : 
     548           0 :     for (c = optind; c < argc; c++)
     549             :     {
     550             :         /* Work on selected database */
     551           0 :         rc += (vacuumlo(argv[c], &param) != 0);
     552             :     }
     553             : 
     554           0 :     return rc;
     555             : }

Generated by: LCOV version 1.14