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