PostgreSQL Source Code git master
All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros Pages
oid2name.c
Go to the documentation of this file.
1/*
2 * oid2name, a PostgreSQL app to map OIDs on the filesystem
3 * to table and database names.
4 *
5 * Originally by
6 * B. Palmer, bpalmer@crimelabs.net 1-17-2001
7 *
8 * contrib/oid2name/oid2name.c
9 */
10#include "postgres_fe.h"
11
12#include "catalog/pg_class_d.h"
13#include "common/connect.h"
14#include "common/logging.h"
15#include "common/string.h"
16#include "getopt_long.h"
17#include "libpq-fe.h"
18#include "pg_getopt.h"
19
20/* an extensible array to keep track of elements to show */
21typedef struct
22{
23 char **array;
24 int num;
25 int alloc;
26} eary;
27
28/* these are the opts structures for command line params */
29struct options
30{
34
35 bool quiet;
37 bool indexes;
38 bool nodb;
41
42 char *dbname;
43 char *hostname;
44 char *port;
45 char *username;
46 const char *progname;
47};
48
49/* function prototypes */
50static void help(const char *progname);
51void get_opts(int argc, char **argv, struct options *my_opts);
52void add_one_elt(char *eltname, eary *eary);
54PGconn *sql_conn(struct options *my_opts);
55int sql_exec(PGconn *conn, const char *todo, bool quiet);
60
61/* function to parse command line options and check for some usage errors. */
62void
63get_opts(int argc, char **argv, struct options *my_opts)
64{
65 static const struct option long_options[] = {
66 {"dbname", required_argument, NULL, 'd'},
67 {"host", required_argument, NULL, 'h'},
68 {"host", required_argument, NULL, 'H'}, /* deprecated */
69 {"filenode", required_argument, NULL, 'f'},
70 {"indexes", no_argument, NULL, 'i'},
71 {"oid", required_argument, NULL, 'o'},
72 {"port", required_argument, NULL, 'p'},
73 {"quiet", no_argument, NULL, 'q'},
74 {"tablespaces", no_argument, NULL, 's'},
75 {"system-objects", no_argument, NULL, 'S'},
76 {"table", required_argument, NULL, 't'},
77 {"username", required_argument, NULL, 'U'},
78 {"version", no_argument, NULL, 'V'},
79 {"extended", no_argument, NULL, 'x'},
80 {"help", no_argument, NULL, '?'},
81 {NULL, 0, NULL, 0}
82 };
83
84 int c;
85 const char *progname;
86 int optindex;
87
88 pg_logging_init(argv[0]);
89 progname = get_progname(argv[0]);
90
91 /* set the defaults */
92 my_opts->quiet = false;
93 my_opts->systables = false;
94 my_opts->indexes = false;
95 my_opts->nodb = false;
96 my_opts->extended = false;
97 my_opts->tablespaces = false;
98 my_opts->dbname = NULL;
99 my_opts->hostname = NULL;
100 my_opts->port = NULL;
101 my_opts->username = NULL;
102 my_opts->progname = progname;
103
104 if (argc > 1)
105 {
106 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
107 {
108 help(progname);
109 exit(0);
110 }
111 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
112 {
113 puts("oid2name (PostgreSQL) " PG_VERSION);
114 exit(0);
115 }
116 }
117
118 /* get opts */
119 while ((c = getopt_long(argc, argv, "d:f:h:H:io:p:qsSt:U:x", long_options, &optindex)) != -1)
120 {
121 switch (c)
122 {
123 /* specify the database */
124 case 'd':
125 my_opts->dbname = pg_strdup(optarg);
126 break;
127
128 /* specify one filenumber to show */
129 case 'f':
130 add_one_elt(optarg, my_opts->filenumbers);
131 break;
132
133 /* host to connect to */
134 case 'H': /* deprecated */
135 case 'h':
136 my_opts->hostname = pg_strdup(optarg);
137 break;
138
139 /* also display indexes */
140 case 'i':
141 my_opts->indexes = true;
142 break;
143
144 /* specify one Oid to show */
145 case 'o':
146 add_one_elt(optarg, my_opts->oids);
147 break;
148
149 /* port to connect to on remote host */
150 case 'p':
151 my_opts->port = pg_strdup(optarg);
152 break;
153
154 /* don't show headers */
155 case 'q':
156 my_opts->quiet = true;
157 break;
158
159 /* dump tablespaces only */
160 case 's':
161 my_opts->tablespaces = true;
162 break;
163
164 /* display system tables */
165 case 'S':
166 my_opts->systables = true;
167 break;
168
169 /* specify one tablename to show */
170 case 't':
171 add_one_elt(optarg, my_opts->tables);
172 break;
173
174 /* username */
175 case 'U':
176 my_opts->username = pg_strdup(optarg);
177 break;
178
179 /* display extra columns */
180 case 'x':
181 my_opts->extended = true;
182 break;
183
184 default:
185 /* getopt_long already emitted a complaint */
186 pg_log_error_hint("Try \"%s --help\" for more information.", progname);
187 exit(1);
188 }
189 }
190
191 if (optind < argc)
192 {
193 pg_log_error("too many command-line arguments (first is \"%s\")",
194 argv[optind]);
195 pg_log_error_hint("Try \"%s --help\" for more information.", progname);
196 exit(1);
197 }
198}
199
200static void
201help(const char *progname)
202{
203 printf("%s helps examining the file structure used by PostgreSQL.\n\n"
204 "Usage:\n"
205 " %s [OPTION]...\n"
206 "\nOptions:\n"
207 " -f, --filenode=FILENODE show info for table with given file node\n"
208 " -i, --indexes show indexes and sequences too\n"
209 " -o, --oid=OID show info for table with given OID\n"
210 " -q, --quiet quiet (don't show headers)\n"
211 " -s, --tablespaces show all tablespaces\n"
212 " -S, --system-objects show system objects too\n"
213 " -t, --table=TABLE show info for named table\n"
214 " -V, --version output version information, then exit\n"
215 " -x, --extended extended (show additional columns)\n"
216 " -?, --help show this help, then exit\n"
217 "\nConnection options:\n"
218 " -d, --dbname=DBNAME database to connect to\n"
219 " -h, --host=HOSTNAME database server host or socket directory\n"
220 " -H (same as -h, deprecated)\n"
221 " -p, --port=PORT database server port number\n"
222 " -U, --username=USERNAME connect as specified database user\n"
223 "\nThe default action is to show all database OIDs.\n\n"
224 "Report bugs to <%s>.\n"
225 "%s home page: <%s>\n",
226 progname, progname, PACKAGE_BUGREPORT, PACKAGE_NAME, PACKAGE_URL);
227}
228
229/*
230 * add_one_elt
231 *
232 * Add one element to a (possibly empty) eary struct.
233 */
234void
235add_one_elt(char *eltname, eary *eary)
236{
237 if (eary->alloc == 0)
238 {
239 eary ->alloc = 8;
240 eary ->array = (char **) pg_malloc(8 * sizeof(char *));
241 }
242 else if (eary->num >= eary->alloc)
243 {
244 eary ->alloc *= 2;
245 eary ->array = (char **) pg_realloc(eary->array,
246 eary->alloc * sizeof(char *));
247 }
248
249 eary ->array[eary->num] = pg_strdup(eltname);
250 eary ->num++;
251}
252
253/*
254 * get_comma_elts
255 *
256 * Return the elements of an eary as a (freshly allocated) single string, in
257 * single quotes, separated by commas and properly escaped for insertion in an
258 * SQL statement.
259 */
260char *
262{
263 char *ret,
264 *ptr;
265 int i,
266 length = 0;
267
268 if (eary->num == 0)
269 return pg_strdup("");
270
271 /*
272 * PQescapeString wants 2 * length + 1 bytes of breath space. Add two
273 * chars per element for the single quotes and one for the comma.
274 */
275 for (i = 0; i < eary->num; i++)
276 length += strlen(eary->array[i]);
277
278 ret = (char *) pg_malloc(length * 2 + 4 * eary->num);
279 ptr = ret;
280
281 for (i = 0; i < eary->num; i++)
282 {
283 if (i != 0)
284 sprintf(ptr++, ",");
285 sprintf(ptr++, "'");
286 ptr += PQescapeString(ptr, eary->array[i], strlen(eary->array[i]));
287 sprintf(ptr++, "'");
288 }
289
290 return ret;
291}
292
293/* establish connection with database. */
294PGconn *
295sql_conn(struct options *my_opts)
296{
297 PGconn *conn;
298 char *password = NULL;
299 bool new_pass;
300 PGresult *res;
301
302 /*
303 * Start the connection. Loop until we have a password if requested by
304 * backend.
305 */
306 do
307 {
308#define PARAMS_ARRAY_SIZE 7
309
310 const char *keywords[PARAMS_ARRAY_SIZE];
311 const char *values[PARAMS_ARRAY_SIZE];
312
313 keywords[0] = "host";
314 values[0] = my_opts->hostname;
315 keywords[1] = "port";
316 values[1] = my_opts->port;
317 keywords[2] = "user";
318 values[2] = my_opts->username;
319 keywords[3] = "password";
320 values[3] = password;
321 keywords[4] = "dbname";
322 values[4] = my_opts->dbname;
323 keywords[5] = "fallback_application_name";
324 values[5] = my_opts->progname;
325 keywords[6] = NULL;
326 values[6] = NULL;
327
328 new_pass = false;
330
331 if (!conn)
332 pg_fatal("could not connect to database %s",
333 my_opts->dbname);
334
335 if (PQstatus(conn) == CONNECTION_BAD &&
337 !password)
338 {
339 PQfinish(conn);
340 password = simple_prompt("Password: ", false);
341 new_pass = true;
342 }
343 } while (new_pass);
344
345 /* check to see that the backend connection was successfully made */
347 {
349 PQfinish(conn);
350 exit(1);
351 }
352
355 {
356 pg_log_error("could not clear \"search_path\": %s",
358 PQclear(res);
359 PQfinish(conn);
360 exit(1);
361 }
362 PQclear(res);
363
364 /* return the conn if good */
365 return conn;
366}
367
368/*
369 * Actual code to make call to the database and print the output data.
370 */
371int
372sql_exec(PGconn *conn, const char *todo, bool quiet)
373{
374 PGresult *res;
375
376 int nfields;
377 int nrows;
378 int i,
379 j,
380 l;
381 int *length;
382 char *pad;
383
384 /* make the call */
385 res = PQexec(conn, todo);
386
387 /* check and deal with errors */
388 if (!res || PQresultStatus(res) > 2)
389 {
390 pg_log_error("query failed: %s", PQerrorMessage(conn));
391 pg_log_error_detail("Query was: %s", todo);
392
393 PQclear(res);
394 PQfinish(conn);
395 exit(1);
396 }
397
398 /* get the number of fields */
399 nrows = PQntuples(res);
400 nfields = PQnfields(res);
401
402 /* for each field, get the needed width */
403 length = (int *) pg_malloc(sizeof(int) * nfields);
404 for (j = 0; j < nfields; j++)
405 length[j] = strlen(PQfname(res, j));
406
407 for (i = 0; i < nrows; i++)
408 {
409 for (j = 0; j < nfields; j++)
410 {
411 l = strlen(PQgetvalue(res, i, j));
412 if (l > length[j])
413 length[j] = strlen(PQgetvalue(res, i, j));
414 }
415 }
416
417 /* print a header */
418 if (!quiet)
419 {
420 for (j = 0, l = 0; j < nfields; j++)
421 {
422 fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j));
423 l += length[j] + 2;
424 }
425 fprintf(stdout, "\n");
426 pad = (char *) pg_malloc(l + 1);
427 memset(pad, '-', l);
428 pad[l] = '\0';
429 fprintf(stdout, "%s\n", pad);
430 free(pad);
431 }
432
433 /* for each row, dump the information */
434 for (i = 0; i < nrows; i++)
435 {
436 for (j = 0; j < nfields; j++)
437 fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j));
438 fprintf(stdout, "\n");
439 }
440
441 /* cleanup */
442 PQclear(res);
443 free(length);
444
445 return 0;
446}
447
448/*
449 * Dump all databases. There are no system objects to worry about.
450 */
451void
453{
454 char todo[1024];
455
456 /* get the oid and database name from the system pg_database table */
457 snprintf(todo, sizeof(todo),
458 "SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
459 "spcname AS \"Tablespace\" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t ON "
460 "(dattablespace = t.oid) ORDER BY 2");
461
462 sql_exec(conn, todo, opts->quiet);
463}
464
465/*
466 * Dump all tables, indexes and sequences in the current database.
467 */
468void
470{
471 char todo[1024];
472 char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
473
474 snprintf(todo, sizeof(todo),
475 "SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s "
476 "FROM pg_catalog.pg_class c "
477 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
478 " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),"
479 " pg_catalog.pg_tablespace t "
480 "WHERE relkind IN (" CppAsString2(RELKIND_RELATION) ","
481 CppAsString2(RELKIND_MATVIEW) "%s%s) AND "
482 " %s"
483 " t.oid = CASE"
484 " WHEN reltablespace <> 0 THEN reltablespace"
485 " ELSE dattablespace"
486 " END "
487 "ORDER BY relname",
488 opts->extended ? addfields : "",
489 opts->indexes ? "," CppAsString2(RELKIND_INDEX) "," CppAsString2(RELKIND_SEQUENCE) : "",
490 opts->systables ? "," CppAsString2(RELKIND_TOASTVALUE) : "",
491 opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND");
492
493 sql_exec(conn, todo, opts->quiet);
494}
495
496/*
497 * Show oid, filenumber, name, schema and tablespace for each of the
498 * given objects in the current database.
499 */
500void
502{
503 char *todo;
504 char *qualifiers,
505 *ptr;
506 char *comma_oids,
507 *comma_filenumbers,
508 *comma_tables;
509 bool written = false;
510 char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
511
512 /* get tables qualifiers, whether names, filenumbers, or OIDs */
513 comma_oids = get_comma_elts(opts->oids);
514 comma_tables = get_comma_elts(opts->tables);
515 comma_filenumbers = get_comma_elts(opts->filenumbers);
516
517 /* 80 extra chars for SQL expression */
518 qualifiers = (char *) pg_malloc(strlen(comma_oids) + strlen(comma_tables) +
519 strlen(comma_filenumbers) + 80);
520 ptr = qualifiers;
521
522 if (opts->oids->num > 0)
523 {
524 ptr += sprintf(ptr, "c.oid IN (%s)", comma_oids);
525 written = true;
526 }
527 if (opts->filenumbers->num > 0)
528 {
529 if (written)
530 ptr += sprintf(ptr, " OR ");
531 ptr += sprintf(ptr, "pg_catalog.pg_relation_filenode(c.oid) IN (%s)",
532 comma_filenumbers);
533 written = true;
534 }
535 if (opts->tables->num > 0)
536 {
537 if (written)
538 ptr += sprintf(ptr, " OR ");
539 sprintf(ptr, "c.relname ~~ ANY (ARRAY[%s])", comma_tables);
540 }
541 free(comma_oids);
542 free(comma_tables);
543 free(comma_filenumbers);
544
545 /* now build the query */
546 todo = psprintf("SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s\n"
547 "FROM pg_catalog.pg_class c\n"
548 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
549 " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),\n"
550 " pg_catalog.pg_tablespace t\n"
551 "WHERE relkind IN (" CppAsString2(RELKIND_RELATION) ","
552 CppAsString2(RELKIND_MATVIEW) ","
553 CppAsString2(RELKIND_INDEX) ","
554 CppAsString2(RELKIND_SEQUENCE) ","
555 CppAsString2(RELKIND_TOASTVALUE) ") AND\n"
556 " t.oid = CASE\n"
557 " WHEN reltablespace <> 0 THEN reltablespace\n"
558 " ELSE dattablespace\n"
559 " END AND\n"
560 " (%s)\n"
561 "ORDER BY relname\n",
562 opts->extended ? addfields : "",
563 qualifiers);
564
565 free(qualifiers);
566
567 sql_exec(conn, todo, opts->quiet);
568}
569
570void
572{
573 char todo[1024];
574
575 snprintf(todo, sizeof(todo),
576 "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
577 "FROM pg_catalog.pg_tablespace");
578
579 sql_exec(conn, todo, opts->quiet);
580}
581
582int
583main(int argc, char **argv)
584{
585 struct options *my_opts;
586 PGconn *pgconn;
587
588 my_opts = (struct options *) pg_malloc(sizeof(struct options));
589
590 my_opts->oids = (eary *) pg_malloc(sizeof(eary));
591 my_opts->tables = (eary *) pg_malloc(sizeof(eary));
592 my_opts->filenumbers = (eary *) pg_malloc(sizeof(eary));
593
594 my_opts->oids->num = my_opts->oids->alloc = 0;
595 my_opts->tables->num = my_opts->tables->alloc = 0;
596 my_opts->filenumbers->num = my_opts->filenumbers->alloc = 0;
597
598 /* parse the opts */
599 get_opts(argc, argv, my_opts);
600
601 if (my_opts->dbname == NULL)
602 {
603 my_opts->dbname = "postgres";
604 my_opts->nodb = true;
605 }
606 pgconn = sql_conn(my_opts);
607
608 /* display only tablespaces */
609 if (my_opts->tablespaces)
610 {
611 if (!my_opts->quiet)
612 printf("All tablespaces:\n");
613 sql_exec_dumpalltbspc(pgconn, my_opts);
614
615 PQfinish(pgconn);
616 exit(0);
617 }
618
619 /* display the given elements in the database */
620 if (my_opts->oids->num > 0 ||
621 my_opts->tables->num > 0 ||
622 my_opts->filenumbers->num > 0)
623 {
624 if (!my_opts->quiet)
625 printf("From database \"%s\":\n", my_opts->dbname);
626 sql_exec_searchtables(pgconn, my_opts);
627
628 PQfinish(pgconn);
629 exit(0);
630 }
631
632 /* no elements given; dump the given database */
633 if (my_opts->dbname && !my_opts->nodb)
634 {
635 if (!my_opts->quiet)
636 printf("From database \"%s\":\n", my_opts->dbname);
637 sql_exec_dumpalltables(pgconn, my_opts);
638
639 PQfinish(pgconn);
640 exit(0);
641 }
642
643 /* no database either; dump all databases */
644 if (!my_opts->quiet)
645 printf("All databases:\n");
646 sql_exec_dumpalldbs(pgconn, my_opts);
647
648 PQfinish(pgconn);
649 return 0;
650}
static Datum values[MAXATTR]
Definition: bootstrap.c:151
#define CppAsString2(x)
Definition: c.h:346
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
#define fprintf(file, fmt, msg)
Definition: cubescan.l:21
int PQconnectionNeedsPassword(const PGconn *conn)
Definition: fe-connect.c:7260
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:7146
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4880
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:7209
PGconn * PQconnectdbParams(const char *const *keywords, const char *const *values, int expand_dbname)
Definition: fe-connect.c:691
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3876
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:3411
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3481
char * PQfname(const PGresult *res, int field_num)
Definition: fe-exec.c:3567
size_t PQescapeString(char *to, const char *from, size_t length)
Definition: fe-exec.c:4167
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:2262
int PQnfields(const PGresult *res)
Definition: fe-exec.c:3489
void * pg_malloc(size_t size)
Definition: fe_memutils.c:47
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
void * pg_realloc(void *ptr, size_t size)
Definition: fe_memutils.c:65
int getopt_long(int argc, char *const argv[], const char *optstring, const struct option *longopts, int *longindex)
Definition: getopt_long.c:60
#define no_argument
Definition: getopt_long.h:24
#define required_argument
Definition: getopt_long.h:25
#define free(a)
Definition: header.h:65
int j
Definition: isn.c:73
int i
Definition: isn.c:72
static const JsonPathKeyword keywords[]
@ CONNECTION_BAD
Definition: libpq-fe.h:82
@ PGRES_TUPLES_OK
Definition: libpq-fe.h:123
exit(1)
void pg_logging_init(const char *argv0)
Definition: logging.c:83
#define pg_log_error(...)
Definition: logging.h:106
#define pg_log_error_hint(...)
Definition: logging.h:112
#define pg_log_error_detail(...)
Definition: logging.h:109
const char * progname
Definition: main.c:44
PGconn * sql_conn(struct options *my_opts)
Definition: oid2name.c:295
void sql_exec_searchtables(PGconn *conn, struct options *opts)
Definition: oid2name.c:501
static void help(const char *progname)
Definition: oid2name.c:201
int main(int argc, char **argv)
Definition: oid2name.c:583
int sql_exec(PGconn *conn, const char *todo, bool quiet)
Definition: oid2name.c:372
void add_one_elt(char *eltname, eary *eary)
Definition: oid2name.c:235
void get_opts(int argc, char **argv, struct options *my_opts)
Definition: oid2name.c:63
#define PARAMS_ARRAY_SIZE
void sql_exec_dumpalltbspc(PGconn *conn, struct options *opts)
Definition: oid2name.c:571
char * get_comma_elts(eary *eary)
Definition: oid2name.c:261
void sql_exec_dumpalldbs(PGconn *conn, struct options *opts)
Definition: oid2name.c:452
void sql_exec_dumpalltables(PGconn *conn, struct options *opts)
Definition: oid2name.c:469
static AmcheckOptions opts
Definition: pg_amcheck.c:112
#define pg_fatal(...)
PGDLLIMPORT int optind
Definition: getopt.c:51
PGDLLIMPORT char * optarg
Definition: getopt.c:53
#define sprintf
Definition: port.h:240
#define snprintf
Definition: port.h:238
const char * get_progname(const char *argv0)
Definition: path.c:575
#define printf(...)
Definition: port.h:244
char * c
char * psprintf(const char *fmt,...)
Definition: psprintf.c:43
char * simple_prompt(const char *prompt, bool echo)
Definition: sprompt.c:38
static char * password
Definition: streamutil.c:52
PGconn * conn
Definition: streamutil.c:53
Definition: oid2name.c:22
char ** array
Definition: oid2name.c:23
int num
Definition: oid2name.c:24
int alloc
Definition: oid2name.c:25
eary * filenumbers
Definition: oid2name.c:33
char * username
Definition: oid2name.c:45
char * hostname
Definition: oid2name.c:43
char * dbname
Definition: oid2name.c:42
const char * progname
Definition: oid2name.c:46
bool systables
Definition: oid2name.c:36
bool indexes
Definition: oid2name.c:37
bool quiet
Definition: oid2name.c:35
bool tablespaces
Definition: oid2name.c:40
bool extended
Definition: oid2name.c:39
bool nodb
Definition: oid2name.c:38
eary * tables
Definition: oid2name.c:31
eary * oids
Definition: oid2name.c:32
char * port
Definition: oid2name.c:44