Patchers,
Here is a patch bringing oid2name into the 21st century. Supports
schemas (thought you can't select what schema to display), tablespaces,
and using -o for OIDs and -f for filenames (pg_class.relfilenode). Also
-t now accepts LIKE patterns. There can be multiple and mixed -o, -f
and -t arguments, and it will show all the objects in one go.
By default it only shows filenode and relation name, though a -x switch
makes it include tablespace name, schema name and OID.
Also, the headers are prettier.
Please comment, review and/or apply.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Cuando no hay humildad las personas se degradan" (A. Christie)
Index: README.oid2name
===================================================================
RCS file: /home/alvherre/cvs/pgsql-server/contrib/oid2name/README.oid2name,v
retrieving revision 1.9
diff -c -r1.9 README.oid2name
*** README.oid2name 12 Oct 2002 19:15:09 -0000 1.9
--- README.oid2name 14 Sep 2004 18:31:34 -0000
***************
*** 1,124 ****
This utility allows administrators to view the file structure used by
! PostgreSQL. Databases are placed in directories based on their OIDs in
! pg_database, and the tables in that directory are named by original
! OIDs, stored in pg_class.relfilenode. Oid2name connects to the database
! and extracts the OID and table name information.
! ---------------------------------------------------------------------------
!
! It can be used in four ways:
!
!
! oid2name
!
! This will connect to the template1 database and display all databases
! in the system:
!
! $ oid2name
! All databases:
! ---------------------------------
! 18720 = test1
! 1 = template1
! 18719 = template0
! 18721 = test
! 18735 = postgres
! 18736 = cssi
!
!
! oid2name -d test [-x]
!
! This connects to the database test and shows all tables and their OIDs:
!
! $ oid2name -d test
! All tables from database "test":
! ---------------------------------
! 18766 = dns
! 18737 = ips
! 18722 = testdate
!
!
! oid2name -d test -o 18737
! oid2name -d test -t testdate
!
! This will connect to the database test and display the table name for oid
! 18737 and the oid for table name testdate respectively:
! $ oid2name -d test -o 18737
! Tablename of oid 18737 from database "test":
! ---------------------------------
! 18737 = ips
!
!
! $ oid2name -d test -t testdate
! Oid of table testdate from database "test":
! ---------------------------------
! 18722 = testdate
!
! Keep in mind tables over one gigabyte will be split into separate files
! with numeric file extensions.
---------------------------------------------------------------------------
Sample session:
- $ cd /u/pg/data/base
$ oid2name
All databases:
! ---------------------------------
! 16817 = test2
! 16578 = x
! 16756 = test
! 1 = template1
! 16569 = template0
! 16818 = test3
! 16811 = floattest
!
! $ cd 16756
! $ ls 1873*
! 18730 18731 18732 18735 18736 18737 18738 18739
!
! $ oid2name -d test -o 18737
! Tablename of oid 18737 from database "test":
! ---------------------------------
! 18737 = ips
!
! $ oid2name -d test -t ips
! Oid of table ips from database "test":
! ---------------------------------
! 18737 = ips
$ # show disk space for every db object
! $ du * | while read SIZE OID
> do
! > echo "$SIZE `oid2name -q -d test -o $OID`"
> done
! 24 18737 = ips
! 36 18722 = cities
...
! $ # same as above, but sort by largest first
! $ du * | while read SIZE OID
> do
! > echo "$SIZE `oid2name -q -d test -o $OID`"
! > done |
! > sort -rn
! 2048 19324 = bigtable
! 1950 23903 = customers
...
! $ # show disk usage per database
! $ cd /u/pg/data/base
! $ du -s * |
! > while read SIZE OID
! > do
! > echo "$SIZE `aspg oid2name -q | grep ^$OID' '`"
! > done |
! > sort -rn
! 2256 18721 = test
! 2135 18735 = postgres
! ..
! This can be done in psql with:
test=> SELECT relpages, relfilenode, relname FROM pg_class ORDER BY relpages DESC;
--- 1,170 ----
This utility allows administrators to view the file structure used by
! PostgreSQL.
! Databases are placed in directories based on their OIDs in pg_database,
! and the tables in that directory are named by original OIDs, stored in
! pg_class.relfilenode.
!
! Tablespaces make the scenario more complicated. Each tablespace has a
! directory inside the pg_tblspc directory, with a symlink pointing to the
! actual tablespace directory. Inside that directory there is another
! directory for each database that has elements in the tablespace, named
! after the database's OID.
!
! Oid2name connects to the database and extracts the OID and table name
! information. You can also have it show the database OIDs and tablespace
! OIDs.
!
! When displaying tables (i.e. not all databases, not tablespaces, and not
! a whole database) you can select which tables to show by using -o, -f
! and -t. The first switch receives an OID, the second receives a
! filename, and the third receives a tablename (actually, it's a LIKE
! pattern, so you can use things like "foo%"). Note that you can use as
! many of these switches as you like, and the listing will include all
! relevant objects.
!
! Note that while OID and filename sometimes match, this is not
! necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER
! and some forms of ALTER TABLE can change the filename while preserving
! the OID.
!
! Additional switches:
! -i include indexes and sequences in the database listing.
! -x display more information about each object shown:
! tablespace name, schema name, OID.
! -S also show system objects
! (those in information_schema, pg_toast and pg_catalog schemas)
! -q don't display headers
! (useful for scripting)
! -s show a tablespace listing instead
! If you don't give any of -t, -f or -o it will dump the whole database
! given in -d. If you don't give -d, it will show a database listing.
---------------------------------------------------------------------------
Sample session:
$ oid2name
All databases:
! Oid Database Name Tablespace
! ----------------------------------
! 17228 alvherre pg_default
! 17255 regression pg_default
! 17227 template0 pg_default
! 1 template1 pg_default
!
! $ oid2name -s
! All tablespaces:
! Oid Tablespace Name
! -------------------------
! 1663 pg_default
! 1664 pg_global
! 155151 fastdisk
! 155152 bigdisk
!
! $ cd $PGDATA/17228
!
! $ # get top 10 db objects in the default tablespace, ordered by size
! $ ls -lS * | head -10
! -rw------- 1 alvherre alvherre 136536064 sep 14 09:51 155173
! -rw------- 1 alvherre alvherre 17965056 sep 14 09:51 1155291
! -rw------- 1 alvherre alvherre 1204224 sep 14 09:51 16717
! -rw------- 1 alvherre alvherre 581632 sep 6 17:51 1255
! -rw------- 1 alvherre alvherre 237568 sep 14 09:50 16674
! -rw------- 1 alvherre alvherre 212992 sep 14 09:51 1249
! -rw------- 1 alvherre alvherre 204800 sep 14 09:51 16684
! -rw------- 1 alvherre alvherre 196608 sep 14 09:50 16700
! -rw------- 1 alvherre alvherre 163840 sep 14 09:50 16699
! -rw------- 1 alvherre alvherre 122880 sep 6 17:51 16751
!
! $ oid2name -d alvherre -f 155173
! From database "alvherre":
! Filenode Table Name
! ----------------------
! 155173 accounts
!
! $ # you can ask for more than one object
! $ oid2name -d alvherre -f 155173 -f 1155291
! From database "alvherre":
! Filenode Table Name
! -------------------------
! 155173 accounts
! 1155291 accounts_pkey
!
! $ # you can also mix the options, and have more details
! $ oid2name -d alvherre -t accounts -f 1155291 -x
! From database "alvherre":
! Filenode Table Name Oid Schema Tablespace
! ------------------------------------------------------
! 155173 accounts 155173 public pg_default
! 1155291 accounts_pkey 1155291 public pg_default
$ # show disk space for every db object
! $ du [0-9]* |
! > while read SIZE OID
> do
! > echo "$SIZE `oid2name -q -d alvherre -i -f $OID`"
> done
! 16 1155287 branches_pkey
! 16 1155289 tellers_pkey
! 17561 1155291 accounts_pkey
...
! $ # same, but sort by size
! $ du [0-9]* | sort -rn | while read SIZE FN
> do
! > echo "$SIZE `oid2name -q -d alvherre -f $FN`"
! > done
! 133466 155173 accounts
! 17561 1155291 accounts_pkey
! 1177 16717 pg_proc_proname_args_nsp_index
...
! $ # If you want to see what's in tablespaces, use the pg_tblspc directory
! $ cd $PGDATA/pg_tblspc
! $ oid2name -s
! All tablespaces:
! Oid Tablespace Name
! -------------------------
! 1663 pg_default
! 1664 pg_global
! 155151 fastdisk
! 155152 bigdisk
!
! $ # what databases have objects in tablespace "fastdisk"?
! $ ls -d 155151/*
! 155151/17228/ 155151/PG_VERSION
!
! $ # Oh, what was database 17228 again?
! $ oid2name
! All databases:
! Oid Database Name Tablespace
! ----------------------------------
! 17228 alvherre pg_default
! 17255 regression pg_default
! 17227 template0 pg_default
! 1 template1 pg_default
!
! $ # Let's see what objects does this database has in the tablespace.
! $ cd 155151/17228
! $ ls -l
! total 0
! -rw------- 1 postgres postgres 0 sep 13 23:20 155156
!
! $ # OK, this is a pretty small table ... but which one is it?
! $ oid2name -d alvherre -f 155156
! From database "alvherre":
! Filenode Table Name
! ----------------------
! 155156 foo
!
! $ # end of sample session.
!
! ---------------------------------------------------------------------------
! You can also get approximate size data for each object using psql. For
! example,
test=> SELECT relpages, relfilenode, relname FROM pg_class ORDER BY relpages DESC;
Index: oid2name.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql-server/contrib/oid2name/oid2name.c,v
retrieving revision 1.23
diff -c -r1.23 oid2name.c
*** oid2name.c 3 Mar 2004 14:24:12 -0000 1.23
--- oid2name.c 14 Sep 2004 18:46:37 -0000
***************
*** 1,9 ****
/*
! oid2name; a postgresql 7.1 (+?) app to map OIDs on the filesystem
! to table and database names.
!
! b. palmer, [EMAIL PROTECTED] 1-17-2001
!
*/
#include "postgres_fe.h"
--- 1,9 ----
/*
! * oid2name, a PostgreSQL app to map OIDs on the filesystem
! * to table and database names.
! *
! * Originally by
! * B. Palmer, [EMAIL PROTECTED] 1-17-2001
*/
#include "postgres_fe.h"
***************
*** 14,54 ****
#include "libpq-fe.h"
/* these are the opts structures for command line params */
struct options
{
! int getdatabase;
! int gettable;
! int getoid;
!
! int quiet;
!
! int systables;
!
! int remotehost;
! int remoteport;
! int remoteuser;
! int remotepass;
!
! int _oid;
! char _dbname[128];
! char _tbname[128];
!
! char _hostname[128];
! char _port[6];
! char _username[128];
! char _password[128];
};
/* function prototypes */
void get_opts(int, char **, struct options *);
! PGconn *sql_conn(const char *, struct options *);
! void sql_exec_error(int);
! int sql_exec(PGconn *, const char *, int);
! void sql_exec_dumpdb(PGconn *);
! void sql_exec_dumptable(PGconn *, int);
! void sql_exec_searchtable(PGconn *, const char *);
! void sql_exec_searchoid(PGconn *, int);
/* function to parse command line options and check for some usage errors. */
void
--- 14,59 ----
#include "libpq-fe.h"
+ /* an extensible array to keep track of elements to show */
+ typedef struct
+ {
+ char **array;
+ int num;
+ int alloc;
+ } eary;
+
/* these are the opts structures for command line params */
struct options
{
! eary *tables;
! eary *oids;
! eary *filenodes;
!
! bool quiet;
! bool systables;
! bool indexes;
! bool nodb;
! bool extended;
! bool tablespaces;
!
! char *dbname;
! char *hostname;
! char *port;
! char *username;
! char *password;
};
/* function prototypes */
void get_opts(int, char **, struct options *);
! void *myalloc(size_t size);
! void add_one_elt(char *eltname, eary *eary);
! char *get_comma_elts(eary *eary);
! PGconn *sql_conn(struct options *);
! int sql_exec(PGconn *, const char *sql, bool quiet);
! void sql_exec_dumpalldbs(PGconn *, struct options *);
! void sql_exec_dumpalltables(PGconn *, struct options *);
! void sql_exec_searchtables(PGconn *, struct options *);
! void sql_exec_dumpalltbspc(PGconn *, struct options *);
/* function to parse command line options and check for some usage errors. */
void
***************
*** 57,295 ****
int c;
/* set the defaults */
! my_opts->getdatabase = 0;
! my_opts->gettable = 0;
! my_opts->getoid = 0;
!
! my_opts->quiet = 0;
!
! my_opts->systables = 0;
!
! my_opts->remotehost = 0;
! my_opts->remoteport = 0;
! my_opts->remoteuser = 0;
! my_opts->remotepass = 0;
/* get opts */
! while ((c = getopt(argc, argv, "H:p:U:P:d:t:o:qxh?")) != -1)
{
switch (c)
{
/* specify the database */
case 'd':
! my_opts->getdatabase = 1;
! sscanf(optarg, "%s", my_opts->_dbname);
break;
! /* specify the table name */
case 't':
! /* make sure we set the database first */
! if (!my_opts->getdatabase)
! {
! fprintf(stderr, "You must specify a database
to dump from.\n");
! exit(1);
! }
! /* make sure we don't try to do a -o also */
! if (my_opts->getoid)
! {
! fprintf(stderr, "You can only specify either
oid or table\n");
! exit(1);
! }
!
! my_opts->gettable = 1;
! sscanf(optarg, "%s", my_opts->_tbname);
!
break;
! /* specify the oid int */
case 'o':
! /* make sure we set the database first */
! if (!my_opts->getdatabase)
! {
! fprintf(stderr, "You must specify a database
to dump from.\n");
! exit(1);
! }
! /* make sure we don't try to do a -t also */
! if (my_opts->gettable)
! {
! fprintf(stderr, "You can only specify either
oid or table\n");
! exit(1);
! }
!
! my_opts->getoid = 1;
! sscanf(optarg, "%i", &my_opts->_oid);
break;
case 'q':
! my_opts->quiet = 1;
break;
/* host to connect to */
case 'H':
! my_opts->remotehost = 1;
! sscanf(optarg, "%s", my_opts->_hostname);
break;
/* port to connect to on remote host */
case 'p':
! my_opts->remoteport = 1;
! sscanf(optarg, "%s", my_opts->_port);
break;
/* username */
case 'U':
! my_opts->remoteuser = 1;
! sscanf(optarg, "%s", my_opts->_username);
break;
/* password */
case 'P':
! my_opts->remotepass = 1;
! sscanf(optarg, "%s", my_opts->_password);
break;
/* display system tables */
case 'x':
! my_opts->systables = 1;
break;
/* help! (ugly in code for easier editing) */
case '?':
case 'h':
! fprintf(stderr, "\
! Usage: oid2name [-d database [-x] ] [-t table | -o oid]\n\
! default action display all databases\n\
! -d database database to oid2name\n\
! -x display system tables\n\
! -t table | -o oid search for table name (-t) or\n\
! oid (-o) in -d database\n\
! -q quiet\n\
! -H host connect to remote host\n\
! -p port host port to connect to\n\
! -U username username to connect with\n\
! -P password password for username\n\
! ");
exit(1);
break;
}
}
}
! /* establish connection with database. */
! PGconn *
! sql_conn(const char *dbName, struct options * my_opts)
{
! char *pghost,
! *pgport;
! char *pgoptions,
! *pgtty;
! char *pguser,
! *pgpass;
!
! PGconn *conn;
!
! pghost = NULL;
! pgport = NULL;
! pgoptions = NULL; /* special options to start up the
backend
! * server */
! pgtty = NULL; /* debugging tty for the backend
server */
! pguser = NULL;
! pgpass = NULL;
!
! /* override the NULLs with the user params if passed */
! if (my_opts->remotehost)
{
! pghost = (char *) malloc(128);
! sscanf(my_opts->_hostname, "%s", pghost);
}
! if (my_opts->remoteport)
{
! pgport = (char *) malloc(6);
! sscanf(my_opts->_port, "%s", pgport);
}
!
! if (my_opts->remoteuser)
{
! pguser = (char *) malloc(128);
! sscanf(my_opts->_username, "%s", pguser);
}
! if (my_opts->remotepass)
{
! pgpass = (char *) malloc(128);
! sscanf(my_opts->_password, "%s", pgpass);
}
/* login */
! conn = PQsetdbLogin(pghost, pgport, pgoptions, pgtty, dbName, pguser, pgpass);
/* deal with errors */
! if (PQstatus(conn) == CONNECTION_BAD)
{
! fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
fprintf(stderr, "%s", PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
- /* free data structures: not strictly necessary */
- if (pghost != NULL)
- free(pghost);
- if (pgport != NULL)
- free(pgport);
- if (pguser != NULL)
- free(pguser);
- if (pgpass != NULL)
- free(pgpass);
-
- sql_exec(conn, "SET search_path = public;", 0);
-
/* return the conn if good */
return conn;
}
! /* If the sql_ command has an error, this function looks up the error number and
prints it out. */
! void
! sql_exec_error(int error_number)
! {
! fprintf(stderr, "Error number %i.\n", error_number);
! switch (error_number)
! {
! case 3:
! fprintf(stderr, "Error: PGRES_COPY_OUT\n");
! break;
!
! case 4:
! fprintf(stderr, "Error: PGRES_COPY_IN\n");
! break;
!
! case 5:
! fprintf(stderr, "Error: PGRES_BAD_RESPONCE\n");
! break;
!
! case 6:
! fprintf(stderr, "Error: PGRES_NONFATAL_ERROR\n");
! break;
!
! case 7:
! fprintf(stderr, "Error: PGRES_FATAL_ERROR\n");
! break;
! }
! }
!
! /* actual code to make call to the database and print the output data */
int
! sql_exec(PGconn *conn, const char *todo, int match)
{
PGresult *res;
! int numbfields;
! int error_number;
! int i,
! len;
/* make the call */
res = PQexec(conn, todo);
--- 62,298 ----
int c;
/* set the defaults */
! my_opts->quiet = false;
! my_opts->systables = false;
! my_opts->indexes = false;
! my_opts->nodb = false;
! my_opts->extended = false;
! my_opts->tablespaces = false;
/* get opts */
! while ((c = getopt(argc, argv, "H:p:U:P:d:t:o:f:qSxish?")) != -1)
{
switch (c)
{
/* specify the database */
case 'd':
! my_opts->dbname = (char *) myalloc(strlen(optarg));
! sscanf(optarg, "%s", my_opts->dbname);
break;
! /* specify one tablename to show */
case 't':
! add_one_elt(optarg, my_opts->tables);
break;
! /* specify one Oid to show */
case 'o':
! add_one_elt(optarg, my_opts->oids);
! break;
+ /* specify one filenode to show*/
+ case 'f':
+ add_one_elt(optarg, my_opts->filenodes);
break;
+ /* don't show headers */
case 'q':
! my_opts->quiet = true;
break;
/* host to connect to */
case 'H':
! my_opts->hostname = (char *) myalloc(strlen(optarg));
! sscanf(optarg, "%s", my_opts->hostname);
break;
/* port to connect to on remote host */
case 'p':
! my_opts->port = (char *) myalloc(strlen(optarg));
! sscanf(optarg, "%s", my_opts->port);
break;
/* username */
case 'U':
! my_opts->username = (char *) myalloc(strlen(optarg));
! sscanf(optarg, "%s", my_opts->username);
break;
/* password */
case 'P':
! my_opts->password = (char *) myalloc(strlen(optarg));
! sscanf(optarg, "%s", my_opts->password);
break;
/* display system tables */
+ case 'S':
+ my_opts->systables = true;
+ break;
+
+ /* also display indexes */
+ case 'i':
+ my_opts->indexes = true;
+ break;
+
+ /* display extra columns */
case 'x':
! my_opts->extended = true;
! break;
!
! /* dump tablespaces only */
! case 's':
! my_opts->tablespaces = true;
break;
/* help! (ugly in code for easier editing) */
case '?':
case 'h':
! fprintf(stderr,
! "Usage: oid2name [-s|-d database] [-S][-i][-q][-x] [-t table|-o oid|-f file] ...\n"
! " default action show all database Oids\n"
! " -d database database to connect to\n"
! " -s show all tablespaces\n"
! " -S show system objects too\n"
! " -i show indexes and sequences too\n"
! " -x extended (show additional columns)\n"
! " -q quiet (don't show headers)\n"
! " -t <table> show Oid of <table>\n"
! " -o <oid> show table name of <oid>\n"
! " -f <filename> show table name of a filename\n"
! " -H host connect to remote host\n"
! " -p port host port to connect to\n"
! " -U username username to connect with\n"
! " -P password password for username\n"
! " (also see $PGPASSWORD and ~/.pgpass)\n"
! );
exit(1);
break;
}
}
}
! void *
! myalloc(size_t size)
{
! void *ptr = malloc(size);
! if (!ptr)
{
! fprintf(stderr, "out of memory");
! exit(1);
}
+ return ptr;
+ }
! /*
! * add_one_elt
! *
! * Add one element to a (possibly empty) eary struct.
! */
! void
! add_one_elt(char *eltname, eary *eary)
! {
! if (eary->alloc == 0)
{
! eary->alloc = 8;
! eary->array = (char **) myalloc(8 * sizeof(char *));
}
! else if (eary->num >= eary->alloc)
{
! eary->alloc *= 2;
! eary->array = (char **)
! realloc(eary->array, eary->alloc * sizeof(char *));
! if (!eary->array)
! {
! fprintf(stderr, "out of memory");
! exit(1);
! }
}
! eary->array[eary->num] = strdup(eltname);
! eary->num++;
! }
!
! /*
! * get_comma_elts
! *
! * Return the elements of an eary as a (freshly allocated) single string, in
! * single quotes, separated by commas and properly escaped for insertion in an
! * SQL statement.
! */
! char *
! get_comma_elts(eary *eary)
! {
! char *ret,
! *ptr;
! int i, length = 0;
!
! if (eary->num == 0)
! return "";
!
! /*
! * PQescapeString wants 2 * length + 1 bytes of breath space. Add two
! * chars per element for the single quotes and one for the comma.
! */
! for (i = 0; i < eary->num; i++)
! length += strlen(eary->array[i]);
!
! ret = (char *) myalloc(length * 2 + 4 * eary->num);
! ptr = ret;
!
! for (i = 0; i < eary->num; i++)
{
! if (i != 0)
! sprintf(ptr++, ",");
! sprintf(ptr++, "'");
! ptr += PQescapeString(ptr, eary->array[i], strlen(eary->array[i]));
! sprintf(ptr++, "'");
}
+ return ret;
+ }
+
+ /* establish connection with database. */
+ PGconn *
+ sql_conn(struct options * my_opts)
+ {
+ PGconn *conn;
+
/* login */
! conn = PQsetdbLogin(my_opts->hostname,
! my_opts->port,
! NULL, /* options */
! NULL, /* tty */
! my_opts->dbname,
! my_opts->username,
! my_opts->password);
/* deal with errors */
! if (PQstatus(conn) != CONNECTION_OK)
{
! fprintf(stderr, "%s: connection to database '%s' failed.\n",
"oid2name", my_opts->dbname);
fprintf(stderr, "%s", PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
/* return the conn if good */
return conn;
}
! /*
! * Actual code to make call to the database and print the output data.
! */
int
! sql_exec(PGconn *conn, const char *todo, bool quiet)
{
PGresult *res;
! int nfields;
! int nrows;
! int i, j, l;
! int *length;
! char *pad;
/* make the call */
res = PQexec(conn, todo);
***************
*** 297,306 ****
/* check and deal with errors */
if (!res || PQresultStatus(res) > 2)
{
! error_number = PQresultStatus(res);
! fprintf(stderr, "There was an error in the SQL command:\n%s\n", todo);
! sql_exec_error(error_number);
! fprintf(stderr, "PQerrorMessage = %s\n", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
--- 300,307 ----
/* check and deal with errors */
if (!res || PQresultStatus(res) > 2)
{
! fprintf(stderr, "oid2name: query failed: %s\n", PQerrorMessage(conn));
! fprintf(stderr, "oid2name: query was: %s\n", todo);
PQclear(res);
PQfinish(conn);
***************
*** 308,404 ****
}
/* get the number of fields */
! numbfields = PQntuples(res);
! /* if we only expect 1 and there mode than, return -2 */
! if (match == 1 && numbfields > 1)
! return -2;
! /* return -1 if there aren't any returns */
! if (match == 1 && numbfields < 1)
! return -1;
! /* for each row, dump the information */
! for (i = 0; i < numbfields; i++)
{
! len = strlen(PQgetvalue(res, i, 0));
! fprintf(stdout, "%-6s = %s\n", PQgetvalue(res, i, 0), PQgetvalue(res,
i, 1));
}
! /* clean the PGconn once done */
PQclear(res);
return 0;
}
! /* dump all databases known by the system table */
void
! sql_exec_dumpdb(PGconn *conn)
{
char todo[1024];
/* get the oid and database name from the system pg_database table */
! snprintf(todo, 1024, "select oid,datname from pg_database");
! sql_exec(conn, todo, 0);
}
! /* display all tables in whatever db we are connected to. don't display the
! system tables by default */
void
! sql_exec_dumptable(PGconn *conn, int systables)
{
char todo[1024];
! /* don't exclude the systables if this is set */
! if (systables == 1)
! snprintf(todo, 1024, "select relfilenode,relname from pg_class order
by relname");
! else
! snprintf(todo, 1024, "select relfilenode,relname from pg_class "
! "where relkind not in ('v','s', 'c') and "
! "relname not like 'pg_%%' order by relname");
! sql_exec(conn, todo, 0);
}
! /* display the oid for a given tablename for whatever db we are connected
! to. do we want to allow %bar% in the search? Not now. */
void
! sql_exec_searchtable(PGconn *conn, const char *tablename)
{
! int returnvalue;
! char todo[1024];
!
! /* get the oid and tablename where the name matches tablename */
! snprintf(todo, 1024, "select relfilenode,relname from pg_class where relname =
'%s'", tablename);
! returnvalue = sql_exec(conn, todo, 1);
! /* deal with the return errors */
! if (returnvalue == -1)
! printf("No tables with that name found\n");
!
! if (returnvalue == -2)
! printf("VERY scary: more than one table with that name found!!\n");
}
- /* same as above */
void
! sql_exec_searchoid(PGconn *conn, int oid)
{
! int returnvalue;
! char todo[1024];
!
! snprintf(todo, 1024, "select relfilenode,relname from pg_class where oid =
%i", oid);
! returnvalue = sql_exec(conn, todo, 1);
! if (returnvalue == -1)
! printf("No tables with that oid found\n");
!
! if (returnvalue == -2)
! printf("VERY scary: more than one table with that oid found!!\n");
}
int
--- 309,487 ----
}
/* get the number of fields */
! nrows = PQntuples(res);
! nfields = PQnfields(res);
! /* for each field, get the needed width */
! length = (int *) myalloc(sizeof(int) * nfields);
! for (j = 0; j < nfields; j++)
! length[j] = strlen(PQfname(res, j));
! for (i = 0; i < nrows; i++)
! {
! for (j = 0; j < nfields; j++)
! {
! l = strlen(PQgetvalue(res, i, j));
! if (l > length[j])
! length[j] = strlen(PQgetvalue(res, i, j));
! }
! }
! /* print a header */
! if (!quiet)
{
! for (j = 0, l = 0; j < nfields; j++)
! {
! fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j));
! l += length[j] + 2;
! }
! fprintf(stdout, "\n");
! pad = (char *) myalloc(l + 1);
! MemSet(pad, '-', l);
! pad[l] = '\0';
! fprintf(stdout, "%s\n", pad);
! free(pad);
! }
! /* for each row, dump the information */
! for (i = 0; i < nrows; i++)
! {
! for (j = 0; j < nfields; j++)
! fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j));
! fprintf(stdout, "\n");
}
! /* cleanup */
PQclear(res);
+ free(length);
return 0;
}
! /*
! * Dump all databases. There are no system objects to worry about.
! */
void
! sql_exec_dumpalldbs(PGconn *conn, struct options *opts)
{
char todo[1024];
/* get the oid and database name from the system pg_database table */
! snprintf(todo, 1024, "SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
! "spcname AS \"Tablespace\" FROM pg_database d JOIN
pg_tablespace t ON "
! "(dattablespace = t.oid) ORDER BY 2");
! sql_exec(conn, todo, opts->quiet);
}
! /*
! * Dump all tables, indexes and sequences in the current database.
! */
void
! sql_exec_dumpalltables(PGconn *conn, struct options *opts)
{
char todo[1024];
+ char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as
\"Tablespace\" ";
! snprintf(todo, 1024,
! "SELECT relfilenode as \"Filenode\", relname as \"Table
Name\" %s "
! "FROM pg_class c "
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace "
! " LEFT JOIN pg_catalog.pg_database d ON d.datname =
current_database(),"
! " pg_catalog.pg_tablespace t "
! "WHERE relkind IN ('r'%s) AND "
! " %s"
! " t.oid = CASE"
! " WHEN reltablespace <> 0 THEN
reltablespace"
! " WHEN n.nsptablespace <> 0 THEN
nsptablespace"
! " WHEN d.dattablespace <> 0 THEN
dattablespace"
! " END "
! "ORDER BY relname",
! opts->extended ? addfields : "",
! opts->indexes ? ", 'i', 'S', 't'" : "",
! opts->systables ? "" : "n.nspname NOT IN ('pg_catalog',
'pg_toast', 'information_schema') AND");
! sql_exec(conn, todo, opts->quiet);
}
! /*
! * Show oid, relfilenode, name, schema and tablespace for each of the
! * given objects in the current database.
! */
void
! sql_exec_searchtables(PGconn *conn, struct options *opts)
{
! char *todo;
! char *qualifiers, *ptr;
! char *comma_oids, *comma_filenodes, *comma_tables;
! bool written = false;
! char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as
\"Tablespace\" ";
!
! /* get tables qualifiers, whether names, relfilenodes, or OIDs */
! comma_oids = get_comma_elts(opts->oids);
! comma_tables = get_comma_elts(opts->tables);
! comma_filenodes = get_comma_elts(opts->filenodes);
!
! /* 80 extra chars for SQL expression */
! qualifiers = (char *) myalloc(strlen(comma_oids) + strlen(comma_tables) +
!
strlen(comma_filenodes) + 80);
! ptr = qualifiers;
!
! if (opts->oids->num > 0)
! {
! ptr += sprintf(ptr, "c.oid IN (%s)", comma_oids);
! written = true;
! }
! if (opts->filenodes->num > 0)
! {
! if (written)
! ptr += sprintf(ptr, " OR ");
! ptr += sprintf(ptr, "c.relfilenode IN (%s)", comma_filenodes);
! written = true;
! }
! if (opts->tables->num > 0)
! {
! if (written)
! ptr += sprintf(ptr, " OR ");
! sprintf(ptr, "c.relname ~~ ANY (ARRAY[%s])", comma_tables);
! }
! free(comma_oids);
! free(comma_tables);
! free(comma_filenodes);
!
! /* now build the query */
! todo = (char *) myalloc(650 + strlen(qualifiers));
! snprintf(todo, 1024,
! "SELECT relfilenode as \"Filenode\", relname as \"Table
Name\" %s\n"
! "FROM pg_class c \n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace \n"
! " LEFT JOIN pg_catalog.pg_database d ON d.datname =
current_database(),\n"
! " pg_catalog.pg_tablespace t \n"
! "WHERE relkind IN ('r', 'i', 'S', 't') AND \n"
! " t.oid = CASE\n"
! " WHEN reltablespace <> 0 THEN
reltablespace\n"
! " WHEN n.nsptablespace <> 0 THEN
nsptablespace\n"
! " WHEN d.dattablespace <> 0 THEN
dattablespace\n"
! " END AND \n"
! " (%s) \n"
! "ORDER BY relname\n",
! opts->extended ? addfields : "",
! qualifiers);
! free(qualifiers);
! sql_exec(conn, todo, opts->quiet);
}
void
! sql_exec_dumpalltbspc(PGconn *conn, struct options *opts)
{
! char todo[1024];
! snprintf(todo, 1024, "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
! "FROM pg_tablespace");
! sql_exec(conn, todo, opts->quiet);
}
int
***************
*** 407,471 ****
struct options *my_opts;
PGconn *pgconn;
! my_opts = (struct options *) malloc(sizeof(struct options));
/* parse the opts */
get_opts(argc, argv, my_opts);
! /* display all the tables in the database */
! if (my_opts->getdatabase & my_opts->gettable)
{
if (!my_opts->quiet)
! {
! printf("Oid of table %s from database \"%s\":\n",
my_opts->_tbname, my_opts->_dbname);
! printf("---------------------------------\n");
! }
! pgconn = sql_conn(my_opts->_dbname, my_opts);
! sql_exec_searchtable(pgconn, my_opts->_tbname);
! PQfinish(pgconn);
! exit(1);
}
! /* search for the tablename of the given OID */
! if (my_opts->getdatabase & my_opts->getoid)
{
if (!my_opts->quiet)
! {
! printf("Tablename of oid %i from database \"%s\":\n",
my_opts->_oid, my_opts->_dbname);
! printf("---------------------------------\n");
! }
! pgconn = sql_conn(my_opts->_dbname, my_opts);
! sql_exec_searchoid(pgconn, my_opts->_oid);
! PQfinish(pgconn);
! exit(1);
}
! /* search for the oid for the given tablename */
! if (my_opts->getdatabase)
{
if (!my_opts->quiet)
! {
! printf("All tables from database \"%s\":\n", my_opts->_dbname);
! printf("---------------------------------\n");
! }
! pgconn = sql_conn(my_opts->_dbname, my_opts);
! sql_exec_dumptable(pgconn, my_opts->systables);
! PQfinish(pgconn);
! exit(1);
}
! /* display all the databases for the server we are connected to.. */
if (!my_opts->quiet)
- {
printf("All databases:\n");
! printf("---------------------------------\n");
! }
! pgconn = sql_conn("template1", my_opts);
! sql_exec_dumpdb(pgconn);
! PQfinish(pgconn);
exit(0);
}
--- 490,555 ----
struct options *my_opts;
PGconn *pgconn;
! my_opts = (struct options *) myalloc(sizeof(struct options));
!
! my_opts->oids = (eary *) myalloc(sizeof(eary));
! my_opts->tables = (eary *) myalloc(sizeof(eary));
! my_opts->filenodes = (eary *) myalloc(sizeof(eary));
!
! my_opts->oids->num = my_opts->oids->alloc = 0;
! my_opts->tables->num = my_opts->tables->alloc = 0;
! my_opts->filenodes->num = my_opts->filenodes->alloc = 0;
/* parse the opts */
get_opts(argc, argv, my_opts);
! if (my_opts->dbname == NULL)
! {
! my_opts->dbname = "template1";
! my_opts->nodb = true;
! }
! pgconn = sql_conn(my_opts);
!
! /* display only tablespaces */
! if (my_opts->tablespaces)
{
if (!my_opts->quiet)
! printf("All tablespaces:\n");
! sql_exec_dumpalltbspc(pgconn, my_opts);
! PQfinish(pgconn);
! exit(0);
}
! /* display the given elements in the database */
! if (my_opts->oids->num > 0 ||
! my_opts->tables->num > 0 ||
! my_opts->filenodes->num > 0)
{
if (!my_opts->quiet)
! printf("From database \"%s\":\n", my_opts->dbname);
! sql_exec_searchtables(pgconn, my_opts);
! PQfinish(pgconn);
! exit(0);
}
! /* no elements given; dump the given database */
! if (my_opts->dbname && !my_opts->nodb)
{
if (!my_opts->quiet)
! printf("From database \"%s\":\n", my_opts->dbname);
! sql_exec_dumpalltables(pgconn, my_opts);
! PQfinish(pgconn);
! exit(0);
}
! /* no database either; dump all databases */
if (!my_opts->quiet)
printf("All databases:\n");
! sql_exec_dumpalldbs(pgconn, my_opts);
+ PQfinish(pgconn);
exit(0);
}
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
