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

Generated by: LCOV version 1.13