1 /*-------------------------------------------------------------------------
4 * pg_dump is a utility for dumping out a postgres database
7 * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
8 * Portions Copyright (c) 1994, Regents of the University of California
10 * pg_dump will read the system catalogs in a database and dump out a
11 * script that reproduces the schema in terms of SQL that is understood
15 * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.447 2006/08/21 00:57:25 tgl Exp $
17 *-------------------------------------------------------------------------
21 * Although this is not a backend module, we must include postgres.h anyway
22 * so that we can include a bunch of backend include files. pg_dump has
23 * never pretended to be very independent of the backend anyhow ...
41 #include "getopt_long.h"
43 #ifndef HAVE_INT_OPTRESET
49 #include "access/htup.h"
50 #include "catalog/pg_class.h"
51 #include "catalog/pg_proc.h"
52 #include "catalog/pg_trigger.h"
53 #include "catalog/pg_type.h"
54 #include "commands/sequence.h"
55 #include "libpq/libpq-fs.h"
56 #include "mb/pg_wchar.h"
58 #include "pg_backup_archiver.h"
59 #include "dumputils.h"
68 const char *descr; /* comment for an object */
69 Oid classoid; /* object class (catalog OID) */
70 Oid objoid; /* object OID */
71 int objsubid; /* subobject (table column #) */
76 bool g_verbose; /* User wants verbose narration of our
78 Archive *g_fout; /* the script file */
79 PGconn *g_conn; /* the database connection */
81 /* various user-settable parameters */
82 bool dumpInserts; /* dump data using proper insert strings */
83 bool attrNames; /* put attr names into insert strings */
88 /* subquery used to convert user ID (eg, datdba) to user name */
89 static const char *username_subquery;
91 /* obsolete as of 7.3: */
92 static Oid g_last_builtin_oid; /* value of the last builtin oid */
94 /* select and exclude tables and schemas */
95 typedef struct objnameArg
97 struct objnameArg *next;
98 char *name; /* name of the relation */
99 bool is_include; /* include/exclude? */
102 objnameArg *schemaList = NULL; /* List of schemas to include/exclude */
103 objnameArg *tableList = NULL; /* List of tables to include/exclude */
105 char *matchingSchemas = NULL; /* Final list of schemas to dump by oid */
106 char *matchingTables = NULL; /* Final list of tables to dump by oid */
108 char g_opaque_type[10]; /* name for the opaque type */
110 /* placeholders for the delimiters for comments */
111 char g_comment_start[10];
112 char g_comment_end[10];
114 static const CatalogId nilCatalogId = {0, 0};
116 /* these are to avoid passing around info for findNamespace() */
117 static NamespaceInfo *g_namespaces;
118 static int g_numNamespaces;
120 /* flag to turn on/off dollar quoting */
121 static int disable_dollar_quoting = 0;
124 static void help(const char *progname);
125 static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
126 static void dumpTableData(Archive *fout, TableDataInfo *tdinfo);
127 static void dumpComment(Archive *fout, const char *target,
128 const char *namespace, const char *owner,
129 CatalogId catalogId, int subid, DumpId dumpId);
130 static int findComments(Archive *fout, Oid classoid, Oid objoid,
131 CommentItem **items);
132 static int collectComments(Archive *fout, CommentItem **items);
133 static void dumpDumpableObject(Archive *fout, DumpableObject *dobj);
134 static void dumpNamespace(Archive *fout, NamespaceInfo *nspinfo);
135 static void dumpType(Archive *fout, TypeInfo *tinfo);
136 static void dumpBaseType(Archive *fout, TypeInfo *tinfo);
137 static void dumpDomain(Archive *fout, TypeInfo *tinfo);
138 static void dumpCompositeType(Archive *fout, TypeInfo *tinfo);
139 static void dumpShellType(Archive *fout, ShellTypeInfo *stinfo);
140 static void dumpProcLang(Archive *fout, ProcLangInfo *plang);
141 static void dumpFunc(Archive *fout, FuncInfo *finfo);
142 static void dumpCast(Archive *fout, CastInfo *cast);
143 static void dumpOpr(Archive *fout, OprInfo *oprinfo);
144 static void dumpOpclass(Archive *fout, OpclassInfo *opcinfo);
145 static void dumpConversion(Archive *fout, ConvInfo *convinfo);
146 static void dumpRule(Archive *fout, RuleInfo *rinfo);
147 static void dumpAgg(Archive *fout, AggInfo *agginfo);
148 static void dumpTrigger(Archive *fout, TriggerInfo *tginfo);
149 static void dumpTable(Archive *fout, TableInfo *tbinfo);
150 static void dumpTableSchema(Archive *fout, TableInfo *tbinfo);
151 static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo);
152 static void dumpSequence(Archive *fout, TableInfo *tbinfo);
153 static void dumpIndex(Archive *fout, IndxInfo *indxinfo);
154 static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo);
155 static void dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo);
157 static void dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
158 const char *type, const char *name,
159 const char *tag, const char *nspname, const char *owner,
162 static void getDependencies(void);
163 static void getDomainConstraints(TypeInfo *tinfo);
164 static void getTableData(TableInfo *tblinfo, int numTables, bool oids);
165 static char *format_function_arguments(FuncInfo *finfo, int nallargs,
169 static char *format_function_signature(FuncInfo *finfo, bool honor_quotes);
170 static const char *convertRegProcReference(const char *proc);
171 static const char *convertOperatorReference(const char *opr);
172 static Oid findLastBuiltinOid_V71(const char *);
173 static Oid findLastBuiltinOid_V70(void);
174 static void selectSourceSchema(const char *schemaName);
175 static char *getFormattedTypeName(Oid oid, OidOptions opts);
176 static char *myFormatType(const char *typname, int32 typmod);
177 static const char *fmtQualifiedId(const char *schema, const char *id);
178 static bool hasBlobs(Archive *AH);
179 static int dumpBlobs(Archive *AH, void *arg);
180 static int dumpBlobComments(Archive *AH, void *arg);
181 static void dumpDatabase(Archive *AH);
182 static void dumpEncoding(Archive *AH);
183 static void dumpStdStrings(Archive *AH);
184 static const char *getAttrName(int attrnum, TableInfo *tblInfo);
185 static const char *fmtCopyColumnList(const TableInfo *ti);
186 static void do_sql_command(PGconn *conn, const char *query);
187 static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
188 ExecStatusType expected);
192 main(int argc, char **argv)
194 PQExpBuffer query = createPQExpBuffer();
196 objnameArg *this_obj_name, *schemaList_tail = NULL, *tableList_tail = NULL;
198 const char *filename = NULL;
199 const char *format = "p";
200 const char *dbname = NULL;
201 const char *pghost = NULL;
202 const char *pgport = NULL;
203 const char *username = NULL;
204 const char *dumpencoding = NULL;
205 const char *std_strings;
209 DumpableObject **dobjs;
212 bool switch_include_exclude;
213 bool force_password = false;
214 int compressLevel = -1;
215 bool ignore_version = false;
218 int outputCreate = 0;
219 bool outputBlobs = true;
220 int outputNoOwner = 0;
221 static int use_setsessauth = 0;
222 static int disable_triggers = 0;
223 char *outputSuperuser = NULL;
225 RestoreOptions *ropt;
227 static struct option long_options[] = {
228 {"data-only", no_argument, NULL, 'a'},
229 {"blobs", no_argument, NULL, 'b'},
230 {"clean", no_argument, NULL, 'c'},
231 {"create", no_argument, NULL, 'C'},
232 {"file", required_argument, NULL, 'f'},
233 {"format", required_argument, NULL, 'F'},
234 {"inserts", no_argument, NULL, 'd'},
235 {"attribute-inserts", no_argument, NULL, 'D'},
236 {"column-inserts", no_argument, NULL, 'D'},
237 {"host", required_argument, NULL, 'h'},
238 {"ignore-version", no_argument, NULL, 'i'},
239 {"no-reconnect", no_argument, NULL, 'R'},
240 {"oids", no_argument, NULL, 'o'},
241 {"no-owner", no_argument, NULL, 'O'},
242 {"port", required_argument, NULL, 'p'},
243 {"schema", required_argument, NULL, 'n'},
244 {"exclude-schema", required_argument, NULL, 'N'},
245 {"schema-only", no_argument, NULL, 's'},
246 {"superuser", required_argument, NULL, 'S'},
247 {"table", required_argument, NULL, 't'},
248 {"exclude-table", required_argument, NULL, 'T'},
249 {"password", no_argument, NULL, 'W'},
250 {"username", required_argument, NULL, 'U'},
251 {"verbose", no_argument, NULL, 'v'},
252 {"no-privileges", no_argument, NULL, 'x'},
253 {"no-acl", no_argument, NULL, 'x'},
254 {"compress", required_argument, NULL, 'Z'},
255 {"encoding", required_argument, NULL, 'E'},
256 {"help", no_argument, NULL, '?'},
257 {"version", no_argument, NULL, 'V'},
260 * the following options don't have an equivalent short option letter,
261 * but are available as '-X long-name'
263 {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
264 {"disable-triggers", no_argument, &disable_triggers, 1},
265 {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
271 set_pglocale_pgservice(argv[0], "pg_dump");
275 strcpy(g_comment_start, "-- ");
276 g_comment_end[0] = '\0';
277 strcpy(g_opaque_type, "opaque");
279 dataOnly = schemaOnly = dumpInserts = attrNames = false;
281 progname = get_progname(argv[0]);
283 /* Set default options based on progname */
284 if (strcmp(progname, "pg_backup") == 0)
289 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
294 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
296 puts("pg_dump (PostgreSQL) " PG_VERSION);
301 while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:uU:vWxX:Z:",
302 long_options, &optindex)) != -1)
306 case 'a': /* Dump data only */
310 case 'b': /* Dump blobs */
311 /* this is now default, so just ignore the switch */
314 case 'c': /* clean (i.e., drop) schema prior to create */
318 case 'C': /* Create DB */
322 case 'd': /* dump data as proper insert strings */
326 case 'D': /* dump data as proper insert strings with
332 case 'E': /* Dump encoding */
333 dumpencoding = optarg;
344 case 'h': /* server host */
348 case 'i': /* ignore database version mismatch */
349 ignore_version = true;
352 case 'n': /* Include schemas */
353 case 'N': /* Exclude schemas */
354 case 't': /* Include tables */
355 case 'T': /* Exclude tables */
357 if (strlen(optarg) < 1)
359 fprintf(stderr, _("%s: invalid -%c option\n"), progname, c);
364 /* Create a struct for this name */
365 objnameArg *new_obj_name = (objnameArg *)
366 malloc(sizeof(objnameArg));
368 new_obj_name->next = NULL;
369 new_obj_name->name = strdup(optarg);
370 new_obj_name->is_include = islower(c) ? true : false;
372 /* add new entry to the proper list */
373 if (tolower(c) == 'n')
375 if (!schemaList_tail)
376 schemaList_tail = schemaList = new_obj_name;
378 schemaList_tail = schemaList_tail->next = new_obj_name;
383 tableList_tail = tableList = new_obj_name;
385 tableList_tail = tableList_tail->next = new_obj_name;
390 case 'o': /* Dump oids */
394 case 'O': /* Don't reconnect to match owner */
398 case 'p': /* server port */
403 /* no-op, still accepted for backwards compatibility */
406 case 's': /* dump schema only */
411 case 'S': /* Username for superuser in plain text output */
412 outputSuperuser = strdup(optarg);
416 force_password = true;
417 username = simple_prompt("User name: ", 100, true);
424 case 'v': /* verbose */
429 force_password = true;
432 case 'x': /* skip ACL dump */
437 * Option letters were getting scarce, so I invented this new
438 * scheme: '-X feature' turns on some feature. Compare to the
439 * -f option in GCC. You should also add an equivalent
440 * GNU-style option --feature. Features that require
441 * arguments should use '-X feature=foo'.
444 if (strcmp(optarg, "disable-dollar-quoting") == 0)
445 disable_dollar_quoting = 1;
446 else if (strcmp(optarg, "disable-triggers") == 0)
447 disable_triggers = 1;
448 else if (strcmp(optarg, "use-set-session-authorization") == 0)
453 _("%s: invalid -X option -- %s\n"),
455 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
460 case 'Z': /* Compression Level */
461 compressLevel = atoi(optarg);
463 /* This covers the long options equivalent to -X xxx. */
469 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
474 if (optind < (argc - 1))
476 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
477 progname, argv[optind + 1]);
478 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
483 /* Get database name from command line */
485 dbname = argv[optind];
487 if (dataOnly && schemaOnly)
489 write_msg(NULL, "options \"schema only\" (-s) and \"data only\" (-a) cannot be used together\n");
493 if (dataOnly && outputClean)
495 write_msg(NULL, "options \"clean\" (-c) and \"data only\" (-a) cannot be used together\n");
499 if (matchingTables != NULL || matchingSchemas != NULL)
502 if (dumpInserts == true && oids == true)
504 write_msg(NULL, "INSERT (-d, -D) and OID (-o) options cannot be used together\n");
505 write_msg(NULL, "(The INSERT command cannot set OIDs.)\n");
509 /* open the output file */
514 g_fout = CreateArchive(filename, archCustom, compressLevel);
519 g_fout = CreateArchive(filename, archFiles, compressLevel);
525 g_fout = CreateArchive(filename, archNull, 0);
530 g_fout = CreateArchive(filename, archTar, compressLevel);
534 write_msg(NULL, "invalid output format \"%s\" specified\n", format);
540 write_msg(NULL, "could not open output file \"%s\" for writing\n", filename);
544 /* Let the archiver know how noisy to be */
545 g_fout->verbose = g_verbose;
547 g_fout->minRemoteVersion = 70000; /* we can handle back to 7.0 */
548 g_fout->maxRemoteVersion = parse_version(PG_VERSION);
549 if (g_fout->maxRemoteVersion < 0)
551 write_msg(NULL, "could not parse version string \"%s\"\n", PG_VERSION);
556 * Open the database using the Archiver, so it knows about it. Errors mean
559 g_conn = ConnectDatabase(g_fout, dbname, pghost, pgport,
560 username, force_password, ignore_version);
562 /* Set the client encoding if requested */
565 if (PQsetClientEncoding(g_conn, dumpencoding) < 0)
567 write_msg(NULL, "invalid client encoding \"%s\" specified\n",
574 * Get the active encoding and the standard_conforming_strings setting,
575 * so we know how to escape strings.
577 g_fout->encoding = PQclientEncoding(g_conn);
579 std_strings = PQparameterStatus(g_conn, "standard_conforming_strings");
580 g_fout->std_strings = (std_strings && strcmp(std_strings, "on") == 0);
582 /* Set the datestyle to ISO to ensure the dump's portability */
583 do_sql_command(g_conn, "SET DATESTYLE = ISO");
586 * Start serializable transaction to dump consistent data.
588 do_sql_command(g_conn, "BEGIN");
590 do_sql_command(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
592 /* Select the appropriate subquery to convert user IDs to names */
593 if (g_fout->remoteVersion >= 80100)
594 username_subquery = "SELECT rolname FROM pg_catalog.pg_roles WHERE oid =";
595 else if (g_fout->remoteVersion >= 70300)
596 username_subquery = "SELECT usename FROM pg_catalog.pg_user WHERE usesysid =";
598 username_subquery = "SELECT usename FROM pg_user WHERE usesysid =";
601 * If supported, set extra_float_digits so that we can dump float data
602 * exactly (given correctly implemented float I/O code, anyway)
604 if (g_fout->remoteVersion >= 70400)
605 do_sql_command(g_conn, "SET extra_float_digits TO 2");
607 /* Find the last built-in OID, if needed */
608 if (g_fout->remoteVersion < 70300)
610 if (g_fout->remoteVersion >= 70100)
611 g_last_builtin_oid = findLastBuiltinOid_V71(PQdb(g_conn));
613 g_last_builtin_oid = findLastBuiltinOid_V70();
615 write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid);
619 if (schemaList != NULL && g_fout->remoteVersion < 70300)
621 write_msg(NULL, "Postgres must be at least version 7.3 to use schema switches\n");
625 /* Check schema selection flags */
626 resetPQExpBuffer(query);
627 switch_include_exclude = true;
629 for (this_obj_name = schemaList; this_obj_name; this_obj_name = this_obj_name->next)
631 if (switch_include_exclude)
633 /* Special case for when -N is the first argument */
634 if (this_obj_name == schemaList && !this_obj_name->is_include)
635 appendPQExpBuffer(query,
636 "SELECT oid FROM pg_catalog.pg_namespace "
637 "WHERE nspname NOT LIKE 'pg_%%' AND "
638 " nspname != 'information_schema' EXCEPT\n");
640 appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_namespace WHERE");
643 appendPQExpBuffer(query, "%s nspname %c ", switch_include_exclude ? "" : " OR",
644 /* any meta-characters? */
645 strpbrk(this_obj_name->name,"([{\\.?+") == NULL ? '=' : '~');
646 appendStringLiteralAH(query, this_obj_name->name, g_fout);
648 if (this_obj_name->next && this_obj_name->next->is_include == this_obj_name->is_include)
649 switch_include_exclude = false;
652 switch_include_exclude = true;
654 /* Add the joiner if needed */
655 if (this_obj_name->next)
656 appendPQExpBuffer(query, "\n%s\n",
657 this_obj_name->next->is_include ? "UNION" : "EXCEPT");
661 /* Construct OID list of matching schemas */
666 res = PQexec(g_conn, query->data);
667 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
668 if (PQntuples(res) == 0)
670 write_msg(NULL, "No matching schemas were found\n");
674 for (i = 0, len = strlen(" "); i < PQntuples(res); i++)
675 len += strlen(PQgetvalue(res, i, 0)) + 1;
678 * Need to use comma separators so it can be used by IN. zero
679 * is a dummy placeholder. Format is " oid oid oid ".
681 matchingSchemas = malloc(len + 1);
682 strcpy(matchingSchemas, " ");
683 for (i = 0; i < PQntuples(res); i++)
685 strcat(matchingSchemas, PQgetvalue(res, i, 0));
686 strcat(matchingSchemas, " ");
690 /* Check table selection flags */
691 resetPQExpBuffer(query);
692 switch_include_exclude = true;
694 for (this_obj_name = tableList; this_obj_name; this_obj_name = this_obj_name->next)
696 if (switch_include_exclude)
698 /* Special case for when -T is the first argument */
699 if (this_obj_name == tableList && !this_obj_name->is_include && !strlen(query->data))
700 appendPQExpBuffer(query,
701 "SELECT pg_class.oid FROM pg_catalog.pg_class, pg_catalog.pg_namespace "
702 "WHERE relkind='r' AND "
703 " relnamespace = pg_namespace.oid AND "
704 " nspname NOT LIKE 'pg_%%' AND "
705 " nspname != 'information_schema' EXCEPT\n");
707 appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_class WHERE relkind='r' AND (");
710 appendPQExpBuffer(query, "%srelname %c ", switch_include_exclude ? "" : " OR ",
711 /* any meta-characters? */
712 strpbrk(this_obj_name->name,"([{\\.?+") == NULL ? '=' : '~');
713 appendStringLiteralAH(query, this_obj_name->name, g_fout);
715 if (this_obj_name->next && this_obj_name->next->is_include == this_obj_name->is_include)
716 switch_include_exclude = false;
719 switch_include_exclude = true;
720 appendPQExpBuffer(query, ")");
722 /* Add the joiner if needed */
723 if (this_obj_name->next)
724 appendPQExpBuffer(query, "\n%s\n", this_obj_name->next->is_include ?
729 /* Construct OID list of matching tables */
734 /* Restrict by schema? */
735 if (matchingSchemas != NULL)
737 char *matchingSchemas_commas = strdup(matchingSchemas), *p;
739 /* Construct "IN" SQL string by adding commas, " oid, oid, oid " */
740 for (p = matchingSchemas_commas; *p; p++)
742 /* No commas for first/last characters */
743 if (*p == ' ' && p != matchingSchemas_commas && *(p+1))
747 appendPQExpBuffer(query,
748 "\nINTERSECT\nSELECT oid FROM pg_catalog.pg_class WHERE relkind='r' AND relnamespace IN (%s)\n",
749 matchingSchemas_commas);
752 res = PQexec(g_conn, query->data);
753 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
754 if (PQntuples(res) == 0)
756 write_msg(NULL, "No matching tables were found\n");
760 for (i = 0, len = strlen(" "); i < PQntuples(res); i++)
761 len += strlen(PQgetvalue(res, i, 0)) + 1;
763 matchingTables = malloc(len + 1);
764 strcpy(matchingTables, " ");
765 for (i = 0; i < PQntuples(res); i++)
767 strcat(matchingTables, PQgetvalue(res, i, 0));
768 strcat(matchingTables, " ");
772 destroyPQExpBuffer(query);
775 * Now scan the database and create DumpableObject structs for all the
776 * objects we intend to dump.
778 tblinfo = getSchemaData(&numTables);
781 getTableData(tblinfo, numTables, oids);
783 if (outputBlobs && hasBlobs(g_fout))
785 /* Add placeholders to allow correct sorting of blobs */
786 DumpableObject *blobobj;
788 blobobj = (DumpableObject *) malloc(sizeof(DumpableObject));
789 blobobj->objType = DO_BLOBS;
790 blobobj->catId = nilCatalogId;
791 AssignDumpId(blobobj);
792 blobobj->name = strdup("BLOBS");
794 blobobj = (DumpableObject *) malloc(sizeof(DumpableObject));
795 blobobj->objType = DO_BLOB_COMMENTS;
796 blobobj->catId = nilCatalogId;
797 AssignDumpId(blobobj);
798 blobobj->name = strdup("BLOB COMMENTS");
802 * Collect dependency data to assist in ordering the objects.
807 * Sort the objects into a safe dump order (no forward references).
809 * In 7.3 or later, we can rely on dependency information to help us
810 * determine a safe order, so the initial sort is mostly for cosmetic
811 * purposes: we sort by name to ensure that logically identical schemas
812 * will dump identically. Before 7.3 we don't have dependencies and we
813 * use OID ordering as an (unreliable) guide to creation order.
815 getDumpableObjects(&dobjs, &numObjs);
817 if (g_fout->remoteVersion >= 70300)
818 sortDumpableObjectsByTypeName(dobjs, numObjs);
820 sortDumpableObjectsByTypeOid(dobjs, numObjs);
822 sortDumpableObjects(dobjs, numObjs);
825 * Create archive TOC entries for all the objects to be dumped, in a safe
829 /* First the special ENCODING and STDSTRINGS entries. */
830 dumpEncoding(g_fout);
831 dumpStdStrings(g_fout);
833 /* The database item is always next, unless we don't want it at all */
834 if (!dataOnly && matchingTables == NULL && matchingSchemas == NULL)
835 dumpDatabase(g_fout);
837 /* Now the rearrangeable objects. */
838 for (i = 0; i < numObjs; i++)
839 dumpDumpableObject(g_fout, dobjs[i]);
842 * And finally we can do the actual output.
846 ropt = NewRestoreOptions();
847 ropt->filename = (char *) filename;
848 ropt->dropSchema = outputClean;
849 ropt->aclsSkip = aclsSkip;
850 ropt->superuser = outputSuperuser;
851 ropt->create = outputCreate;
852 ropt->noOwner = outputNoOwner;
853 ropt->disable_triggers = disable_triggers;
854 ropt->use_setsessauth = use_setsessauth;
855 ropt->dataOnly = dataOnly;
857 if (compressLevel == -1)
858 ropt->compression = 0;
860 ropt->compression = compressLevel;
862 ropt->suppressDumpWarnings = true; /* We've already shown them */
864 RestoreArchive(g_fout, ropt);
867 CloseArchive(g_fout);
876 help(const char *progname)
878 printf(_("%s dumps a database as a text file or to other formats.\n\n"), progname);
879 printf(_("Usage:\n"));
880 printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
882 printf(_("\nGeneral options:\n"));
883 printf(_(" -f, --file=FILENAME output file name\n"));
884 printf(_(" -F, --format=c|t|p output file format (custom, tar, plain text)\n"));
885 printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
886 " pg_dump version\n"));
887 printf(_(" -v, --verbose verbose mode\n"));
888 printf(_(" -Z, --compress=0-9 compression level for compressed formats\n"));
889 printf(_(" --help show this help, then exit\n"));
890 printf(_(" --version output version information, then exit\n"));
892 printf(_("\nOptions controlling the output content:\n"));
893 printf(_(" -a, --data-only dump only the data, not the schema\n"));
894 printf(_(" -c, --clean clean (drop) schema prior to create\n"));
895 printf(_(" -C, --create include commands to create database in dump\n"));
896 printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
897 printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
898 printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n"));
899 printf(_(" -n, --schema=SCHEMA dump the named schema only\n"));
900 printf(_(" -N, --exclude-schema=SCHEMA do NOT dump the named schema\n"));
901 printf(_(" -o, --oids include OIDs in dump\n"));
902 printf(_(" -O, --no-owner skip restoration of object ownership\n"
903 " in plain text format\n"));
904 printf(_(" -s, --schema-only dump only the schema, no data\n"));
905 printf(_(" -S, --superuser=NAME specify the superuser user name to use in\n"
906 " plain text format\n"));
907 printf(_(" -t, --table=TABLE dump the named table only\n"));
908 printf(_(" -T, --exclude-table=TABLE do NOT dump the named table\n"));
909 printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
910 printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n"
911 " disable dollar quoting, use SQL standard quoting\n"));
912 printf(_(" -X disable-triggers, --disable-triggers\n"
913 " disable triggers during data-only restore\n"));
914 printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n"
915 " use SESSION AUTHORIZATION commands instead of\n"
916 " OWNER TO commands\n"));
918 printf(_("\nConnection options:\n"));
919 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
920 printf(_(" -p, --port=PORT database server port number\n"));
921 printf(_(" -U, --username=NAME connect as specified database user\n"));
922 printf(_(" -W, --password force password prompt (should happen automatically)\n"));
924 printf(_("\nIf no database name is supplied, then the PGDATABASE environment\n"
925 "variable value is used.\n\n"));
926 printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
934 write_msg(NULL, "*** aborted because of error\n");
939 * selectDumpableNamespace: policy-setting subroutine
940 * Mark a namespace as to be dumped or not
943 selectDumpableNamespace(NamespaceInfo *nsinfo)
946 * If specific tables are being dumped, do not dump any complete
947 * namespaces. If specific namespaces are being dumped, dump just
948 * those namespaces. Otherwise, dump all non-system namespaces.
950 nsinfo->dobj.dump = false;
952 if (matchingTables != NULL)
954 else if (matchingSchemas != NULL)
956 char *search_oid = malloc(20);
958 sprintf(search_oid, " %d ", nsinfo->dobj.catId.oid);
959 if (strstr(matchingSchemas, search_oid) != NULL)
960 nsinfo->dobj.dump = true;
964 /* The server prevents users from creating pg_ schemas */
965 else if (strncmp(nsinfo->dobj.name, "pg_", 3) != 0 &&
966 strcmp(nsinfo->dobj.name, "information_schema") != 0)
967 nsinfo->dobj.dump = true;
971 * selectDumpableTable: policy-setting subroutine
972 * Mark a table as to be dumped or not
975 selectDumpableTable(TableInfo *tbinfo)
978 * Always dump if dumping parent namespace; else, if a particular
979 * tablename has been specified, dump matching table name; else, do not
982 tbinfo->dobj.dump = false;
984 if (matchingTables == NULL)
986 if (tbinfo->dobj.namespace->dobj.dump)
987 tbinfo->dobj.dump = true;
991 char *search_oid = malloc(20);
993 sprintf(search_oid, " %d ", tbinfo->dobj.catId.oid);
994 if (strstr(matchingTables, search_oid) != NULL)
995 tbinfo->dobj.dump = true;
1002 * selectDumpableType: policy-setting subroutine
1003 * Mark a type as to be dumped or not
1006 selectDumpableType(TypeInfo *tinfo)
1008 /* Dump only types in dumpable namespaces */
1009 if (!tinfo->dobj.namespace->dobj.dump)
1010 tinfo->dobj.dump = false;
1012 /* skip complex types, except for standalone composite types */
1013 /* (note: this test should now be unnecessary) */
1014 else if (OidIsValid(tinfo->typrelid) &&
1015 tinfo->typrelkind != RELKIND_COMPOSITE_TYPE)
1016 tinfo->dobj.dump = false;
1018 /* skip undefined placeholder types */
1019 else if (!tinfo->isDefined)
1020 tinfo->dobj.dump = false;
1022 /* skip all array types that start w/ underscore */
1023 else if ((tinfo->dobj.name[0] == '_') &&
1024 OidIsValid(tinfo->typelem))
1025 tinfo->dobj.dump = false;
1028 tinfo->dobj.dump = true;
1032 * selectDumpableObject: policy-setting subroutine
1033 * Mark a generic dumpable object as to be dumped or not
1035 * Use this only for object types without a special-case routine above.
1038 selectDumpableObject(DumpableObject *dobj)
1041 * Default policy is to dump if parent namespace is dumpable,
1042 * or always for non-namespace-associated items.
1044 if (dobj->namespace)
1045 dobj->dump = dobj->namespace->dobj.dump;
1051 * Dump a table's contents for loading using the COPY command
1052 * - this routine is called by the Archiver when it wants the table
1057 dumpTableData_copy(Archive *fout, void *dcontext)
1059 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
1060 TableInfo *tbinfo = tdinfo->tdtable;
1061 const char *classname = tbinfo->dobj.name;
1062 const bool hasoids = tbinfo->hasoids;
1063 const bool oids = tdinfo->oids;
1064 PQExpBuffer q = createPQExpBuffer();
1068 const char *column_list;
1071 write_msg(NULL, "dumping contents of table %s\n", classname);
1074 * Make sure we are in proper schema. We will qualify the table name
1075 * below anyway (in case its name conflicts with a pg_catalog table); but
1076 * this ensures reproducible results in case the table contains regproc,
1077 * regclass, etc columns.
1079 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
1082 * If possible, specify the column list explicitly so that we have no
1083 * possibility of retrieving data in the wrong column order. (The default
1084 * column ordering of COPY will not be what we want in certain corner
1085 * cases involving ADD COLUMN and inheritance.)
1087 if (g_fout->remoteVersion >= 70300)
1088 column_list = fmtCopyColumnList(tbinfo);
1090 column_list = ""; /* can't select columns in COPY */
1092 if (oids && hasoids)
1094 appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout;",
1095 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
1101 appendPQExpBuffer(q, "COPY %s %s TO stdout;",
1102 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
1106 res = PQexec(g_conn, q->data);
1107 check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
1112 ret = PQgetCopyData(g_conn, ©buf, 0);
1115 break; /* done or error */
1119 WriteData(fout, copybuf, ret);
1126 * There was considerable discussion in late July, 2000 regarding
1127 * slowing down pg_dump when backing up large tables. Users with both
1128 * slow & fast (multi-processor) machines experienced performance
1129 * degradation when doing a backup.
1131 * Initial attempts based on sleeping for a number of ms for each ms
1132 * of work were deemed too complex, then a simple 'sleep in each loop'
1133 * implementation was suggested. The latter failed because the loop
1134 * was too tight. Finally, the following was implemented:
1136 * If throttle is non-zero, then See how long since the last sleep.
1137 * Work out how long to sleep (based on ratio). If sleep is more than
1138 * 100ms, then sleep reset timer EndIf EndIf
1140 * where the throttle value was the number of ms to sleep per ms of
1141 * work. The calculation was done in each loop.
1143 * Most of the hard work is done in the backend, and this solution
1144 * still did not work particularly well: on slow machines, the ratio
1145 * was 50:1, and on medium paced machines, 1:1, and on fast
1146 * multi-processor machines, it had little or no effect, for reasons
1147 * that were unclear.
1149 * Further discussion ensued, and the proposal was dropped.
1151 * For those people who want this feature, it can be implemented using
1152 * gettimeofday in each loop, calculating the time since last sleep,
1153 * multiplying that by the sleep ratio, then if the result is more
1154 * than a preset 'minimum sleep time' (say 100ms), call the 'select'
1155 * function to sleep for a subsecond period ie.
1157 * select(0, NULL, NULL, NULL, &tvi);
1159 * This will return after the interval specified in the structure tvi.
1160 * Finally, call gettimeofday again to save the 'last sleep time'.
1163 archprintf(fout, "\\.\n\n\n");
1167 /* copy data transfer failed */
1168 write_msg(NULL, "Dumping the contents of table \"%s\" failed: PQgetCopyData() failed.\n", classname);
1169 write_msg(NULL, "Error message from server: %s", PQerrorMessage(g_conn));
1170 write_msg(NULL, "The command was: %s\n", q->data);
1174 /* Check command status and return to normal libpq state */
1175 res = PQgetResult(g_conn);
1176 check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
1179 destroyPQExpBuffer(q);
1184 dumpTableData_insert(Archive *fout, void *dcontext)
1186 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
1187 TableInfo *tbinfo = tdinfo->tdtable;
1188 const char *classname = tbinfo->dobj.name;
1189 PQExpBuffer q = createPQExpBuffer();
1196 * Make sure we are in proper schema. We will qualify the table name
1197 * below anyway (in case its name conflicts with a pg_catalog table); but
1198 * this ensures reproducible results in case the table contains regproc,
1199 * regclass, etc columns.
1201 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
1203 if (fout->remoteVersion >= 70100)
1205 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
1206 "SELECT * FROM ONLY %s",
1207 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
1212 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
1214 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
1218 res = PQexec(g_conn, q->data);
1219 check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
1225 res = PQexec(g_conn, "FETCH 100 FROM _pg_dump_cursor");
1226 check_sql_result(res, g_conn, "FETCH 100 FROM _pg_dump_cursor",
1228 nfields = PQnfields(res);
1229 for (tuple = 0; tuple < PQntuples(res); tuple++)
1231 archprintf(fout, "INSERT INTO %s ", fmtId(classname));
1234 /* corner case for zero-column table */
1235 archprintf(fout, "DEFAULT VALUES;\n");
1238 if (attrNames == true)
1240 resetPQExpBuffer(q);
1241 appendPQExpBuffer(q, "(");
1242 for (field = 0; field < nfields; field++)
1245 appendPQExpBuffer(q, ", ");
1246 appendPQExpBufferStr(q, fmtId(PQfname(res, field)));
1248 appendPQExpBuffer(q, ") ");
1249 archputs(q->data, fout);
1251 archprintf(fout, "VALUES (");
1252 for (field = 0; field < nfields; field++)
1255 archprintf(fout, ", ");
1256 if (PQgetisnull(res, tuple, field))
1258 archprintf(fout, "NULL");
1262 /* XXX This code is partially duplicated in ruleutils.c */
1263 switch (PQftype(res, field))
1274 * These types are printed without quotes unless
1275 * they contain values that aren't accepted by the
1276 * scanner unquoted (e.g., 'NaN'). Note that
1277 * strtod() and friends might accept NaN, so we
1278 * can't use that to test.
1280 * In reality we only need to defend against
1281 * infinity and NaN, so we need not get too crazy
1282 * about pattern matching here.
1284 const char *s = PQgetvalue(res, tuple, field);
1286 if (strspn(s, "0123456789 +-eE.") == strlen(s))
1287 archprintf(fout, "%s", s);
1289 archprintf(fout, "'%s'", s);
1295 archprintf(fout, "B'%s'",
1296 PQgetvalue(res, tuple, field));
1300 if (strcmp(PQgetvalue(res, tuple, field), "t") == 0)
1301 archprintf(fout, "true");
1303 archprintf(fout, "false");
1307 /* All other types are printed as string literals. */
1308 resetPQExpBuffer(q);
1309 appendStringLiteralAH(q,
1310 PQgetvalue(res, tuple, field),
1312 archputs(q->data, fout);
1316 archprintf(fout, ");\n");
1318 } while (PQntuples(res) > 0);
1322 archprintf(fout, "\n\n");
1324 do_sql_command(g_conn, "CLOSE _pg_dump_cursor");
1326 destroyPQExpBuffer(q);
1333 * dump the contents of a single table
1335 * Actually, this just makes an ArchiveEntry for the table contents.
1338 dumpTableData(Archive *fout, TableDataInfo *tdinfo)
1340 TableInfo *tbinfo = tdinfo->tdtable;
1341 PQExpBuffer copyBuf = createPQExpBuffer();
1342 DataDumperPtr dumpFn;
1347 /* Dump/restore using COPY */
1348 dumpFn = dumpTableData_copy;
1349 /* must use 2 steps here 'cause fmtId is nonreentrant */
1350 appendPQExpBuffer(copyBuf, "COPY %s ",
1351 fmtId(tbinfo->dobj.name));
1352 appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
1353 fmtCopyColumnList(tbinfo),
1354 (tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
1355 copyStmt = copyBuf->data;
1359 /* Restore using INSERT */
1360 dumpFn = dumpTableData_insert;
1364 ArchiveEntry(fout, tdinfo->dobj.catId, tdinfo->dobj.dumpId,
1366 tbinfo->dobj.namespace->dobj.name,
1368 tbinfo->rolname, false,
1369 "TABLE DATA", "", "", copyStmt,
1370 tdinfo->dobj.dependencies, tdinfo->dobj.nDeps,
1373 destroyPQExpBuffer(copyBuf);
1378 * set up dumpable objects representing the contents of tables
1381 getTableData(TableInfo *tblinfo, int numTables, bool oids)
1385 for (i = 0; i < numTables; i++)
1387 /* Skip VIEWs (no data to dump) */
1388 if (tblinfo[i].relkind == RELKIND_VIEW)
1390 /* Skip SEQUENCEs (handled elsewhere) */
1391 if (tblinfo[i].relkind == RELKIND_SEQUENCE)
1394 if (tblinfo[i].dobj.dump)
1396 TableDataInfo *tdinfo;
1398 tdinfo = (TableDataInfo *) malloc(sizeof(TableDataInfo));
1400 tdinfo->dobj.objType = DO_TABLE_DATA;
1403 * Note: use tableoid 0 so that this object won't be mistaken for
1404 * something that pg_depend entries apply to.
1406 tdinfo->dobj.catId.tableoid = 0;
1407 tdinfo->dobj.catId.oid = tblinfo[i].dobj.catId.oid;
1408 AssignDumpId(&tdinfo->dobj);
1409 tdinfo->dobj.name = tblinfo[i].dobj.name;
1410 tdinfo->dobj.namespace = tblinfo[i].dobj.namespace;
1411 tdinfo->tdtable = &(tblinfo[i]);
1412 tdinfo->oids = oids;
1413 addObjectDependency(&tdinfo->dobj, tblinfo[i].dobj.dumpId);
1421 * dump the database definition
1424 dumpDatabase(Archive *AH)
1426 PQExpBuffer dbQry = createPQExpBuffer();
1427 PQExpBuffer delQry = createPQExpBuffer();
1428 PQExpBuffer creaQry = createPQExpBuffer();
1438 const char *datname,
1443 datname = PQdb(g_conn);
1446 write_msg(NULL, "saving database definition\n");
1448 /* Make sure we are in proper schema */
1449 selectSourceSchema("pg_catalog");
1451 /* Get the database owner and parameters from pg_database */
1452 if (g_fout->remoteVersion >= 80200)
1454 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1455 "(%s datdba) as dba, "
1456 "pg_encoding_to_char(encoding) as encoding, "
1457 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) as tablespace, "
1458 "shobj_description(oid, 'pg_database') as description "
1463 appendStringLiteralAH(dbQry, datname, AH);
1465 else if (g_fout->remoteVersion >= 80000)
1467 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1468 "(%s datdba) as dba, "
1469 "pg_encoding_to_char(encoding) as encoding, "
1470 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) as tablespace "
1474 appendStringLiteralAH(dbQry, datname, AH);
1476 else if (g_fout->remoteVersion >= 70100)
1478 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1479 "(%s datdba) as dba, "
1480 "pg_encoding_to_char(encoding) as encoding, "
1481 "NULL as tablespace "
1485 appendStringLiteralAH(dbQry, datname, AH);
1489 appendPQExpBuffer(dbQry, "SELECT "
1490 "(SELECT oid FROM pg_class WHERE relname = 'pg_database') AS tableoid, "
1492 "(%s datdba) as dba, "
1493 "pg_encoding_to_char(encoding) as encoding, "
1494 "NULL as tablespace "
1498 appendStringLiteralAH(dbQry, datname, AH);
1501 res = PQexec(g_conn, dbQry->data);
1502 check_sql_result(res, g_conn, dbQry->data, PGRES_TUPLES_OK);
1504 ntups = PQntuples(res);
1508 write_msg(NULL, "missing pg_database entry for database \"%s\"\n",
1515 write_msg(NULL, "query returned more than one (%d) pg_database entry for database \"%s\"\n",
1520 i_tableoid = PQfnumber(res, "tableoid");
1521 i_oid = PQfnumber(res, "oid");
1522 i_dba = PQfnumber(res, "dba");
1523 i_encoding = PQfnumber(res, "encoding");
1524 i_tablespace = PQfnumber(res, "tablespace");
1526 dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
1527 dbCatId.oid = atooid(PQgetvalue(res, 0, i_oid));
1528 dba = PQgetvalue(res, 0, i_dba);
1529 encoding = PQgetvalue(res, 0, i_encoding);
1530 tablespace = PQgetvalue(res, 0, i_tablespace);
1532 appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
1534 if (strlen(encoding) > 0)
1536 appendPQExpBuffer(creaQry, " ENCODING = ");
1537 appendStringLiteralAH(creaQry, encoding, AH);
1539 if (strlen(tablespace) > 0 && strcmp(tablespace, "pg_default") != 0)
1540 appendPQExpBuffer(creaQry, " TABLESPACE = %s",
1542 appendPQExpBuffer(creaQry, ";\n");
1544 appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
1547 dbDumpId = createDumpId();
1550 dbCatId, /* catalog ID */
1551 dbDumpId, /* dump ID */
1553 NULL, /* Namespace */
1554 NULL, /* Tablespace */
1556 false, /* with oids */
1557 "DATABASE", /* Desc */
1558 creaQry->data, /* Create */
1559 delQry->data, /* Del */
1564 NULL); /* Dumper Arg */
1566 /* Dump DB comment if any */
1567 if (g_fout->remoteVersion >= 80200)
1569 /* 8.2 keeps comments on shared objects in a shared table, so
1570 * we cannot use the dumpComment used for other database objects.
1572 char *comment = PQgetvalue(res, 0, PQfnumber(res, "description"));
1573 if (comment && strlen(comment)) {
1574 resetPQExpBuffer(dbQry);
1575 appendPQExpBuffer(dbQry, "COMMENT ON DATABASE %s IS ", fmtId(datname));
1576 appendStringLiteralAH(dbQry, comment, AH);
1577 appendPQExpBuffer(dbQry, ";\n");
1579 ArchiveEntry(AH, dbCatId, createDumpId(), datname, NULL, NULL,
1580 dba, false, "COMMENT", dbQry->data, "", NULL,
1581 &dbDumpId, 1, NULL, NULL);
1584 resetPQExpBuffer(dbQry);
1585 appendPQExpBuffer(dbQry, "DATABASE %s", fmtId(datname));
1586 dumpComment(AH, dbQry->data, NULL, "",
1587 dbCatId, 0, dbDumpId);
1592 destroyPQExpBuffer(dbQry);
1593 destroyPQExpBuffer(delQry);
1594 destroyPQExpBuffer(creaQry);
1599 * dumpEncoding: put the correct encoding into the archive
1602 dumpEncoding(Archive *AH)
1604 const char *encname = pg_encoding_to_char(AH->encoding);
1605 PQExpBuffer qry = createPQExpBuffer();
1608 write_msg(NULL, "saving encoding = %s\n", encname);
1610 appendPQExpBuffer(qry, "SET client_encoding = ");
1611 appendStringLiteralAH(qry, encname, AH);
1612 appendPQExpBuffer(qry, ";\n");
1614 ArchiveEntry(AH, nilCatalogId, createDumpId(),
1615 "ENCODING", NULL, NULL, "",
1616 false, "ENCODING", qry->data, "", NULL,
1620 destroyPQExpBuffer(qry);
1625 * dumpStdStrings: put the correct escape string behavior into the archive
1628 dumpStdStrings(Archive *AH)
1630 const char *stdstrings = AH->std_strings ? "on" : "off";
1631 PQExpBuffer qry = createPQExpBuffer();
1634 write_msg(NULL, "saving standard_conforming_strings = %s\n",
1637 appendPQExpBuffer(qry, "SET standard_conforming_strings = '%s';\n",
1640 ArchiveEntry(AH, nilCatalogId, createDumpId(),
1641 "STDSTRINGS", NULL, NULL, "",
1642 false, "STDSTRINGS", qry->data, "", NULL,
1646 destroyPQExpBuffer(qry);
1652 * Test whether database contains any large objects
1655 hasBlobs(Archive *AH)
1658 const char *blobQry;
1661 /* Make sure we are in proper schema */
1662 selectSourceSchema("pg_catalog");
1664 /* Check for BLOB OIDs */
1665 if (AH->remoteVersion >= 70100)
1666 blobQry = "SELECT loid FROM pg_largeobject LIMIT 1";
1668 blobQry = "SELECT oid FROM pg_class WHERE relkind = 'l' LIMIT 1";
1670 res = PQexec(g_conn, blobQry);
1671 check_sql_result(res, g_conn, blobQry, PGRES_TUPLES_OK);
1673 result = PQntuples(res) > 0;
1685 dumpBlobs(Archive *AH, void *arg)
1687 const char *blobQry;
1688 const char *blobFetchQry;
1690 char buf[LOBBUFSIZE];
1695 write_msg(NULL, "saving large objects\n");
1697 /* Make sure we are in proper schema */
1698 selectSourceSchema("pg_catalog");
1700 /* Cursor to get all BLOB OIDs */
1701 if (AH->remoteVersion >= 70100)
1702 blobQry = "DECLARE bloboid CURSOR FOR SELECT DISTINCT loid FROM pg_largeobject";
1704 blobQry = "DECLARE bloboid CURSOR FOR SELECT oid FROM pg_class WHERE relkind = 'l'";
1706 res = PQexec(g_conn, blobQry);
1707 check_sql_result(res, g_conn, blobQry, PGRES_COMMAND_OK);
1709 /* Command to fetch from cursor */
1710 blobFetchQry = "FETCH 1000 IN bloboid";
1717 res = PQexec(g_conn, blobFetchQry);
1718 check_sql_result(res, g_conn, blobFetchQry, PGRES_TUPLES_OK);
1720 /* Process the tuples, if any */
1721 for (i = 0; i < PQntuples(res); i++)
1726 blobOid = atooid(PQgetvalue(res, i, 0));
1728 loFd = lo_open(g_conn, blobOid, INV_READ);
1731 write_msg(NULL, "dumpBlobs(): could not open large object: %s",
1732 PQerrorMessage(g_conn));
1736 StartBlob(AH, blobOid);
1738 /* Now read it in chunks, sending data to archive */
1741 cnt = lo_read(g_conn, loFd, buf, LOBBUFSIZE);
1744 write_msg(NULL, "dumpBlobs(): error reading large object: %s",
1745 PQerrorMessage(g_conn));
1749 WriteData(AH, buf, cnt);
1752 lo_close(g_conn, loFd);
1754 EndBlob(AH, blobOid);
1756 } while (PQntuples(res) > 0);
1765 * dump all blob comments
1767 * Since we don't provide any way to be selective about dumping blobs,
1768 * there's no need to be selective about their comments either. We put
1769 * all the comments into one big TOC entry.
1772 dumpBlobComments(Archive *AH, void *arg)
1774 const char *blobQry;
1775 const char *blobFetchQry;
1776 PQExpBuffer commentcmd = createPQExpBuffer();
1781 write_msg(NULL, "saving large object comments\n");
1783 /* Make sure we are in proper schema */
1784 selectSourceSchema("pg_catalog");
1786 /* Cursor to get all BLOB comments */
1787 if (AH->remoteVersion >= 70200)
1788 blobQry = "DECLARE blobcmt CURSOR FOR SELECT loid, obj_description(loid, 'pg_largeobject') FROM (SELECT DISTINCT loid FROM pg_largeobject) ss";
1789 else if (AH->remoteVersion >= 70100)
1790 blobQry = "DECLARE blobcmt CURSOR FOR SELECT loid, obj_description(loid) FROM (SELECT DISTINCT loid FROM pg_largeobject) ss";
1792 blobQry = "DECLARE blobcmt CURSOR FOR SELECT oid, (SELECT description FROM pg_description pd WHERE pd.objoid=pc.oid) FROM pg_class pc WHERE relkind = 'l'";
1794 res = PQexec(g_conn, blobQry);
1795 check_sql_result(res, g_conn, blobQry, PGRES_COMMAND_OK);
1797 /* Command to fetch from cursor */
1798 blobFetchQry = "FETCH 100 IN blobcmt";
1805 res = PQexec(g_conn, blobFetchQry);
1806 check_sql_result(res, g_conn, blobFetchQry, PGRES_TUPLES_OK);
1808 /* Process the tuples, if any */
1809 for (i = 0; i < PQntuples(res); i++)
1814 /* ignore blobs without comments */
1815 if (PQgetisnull(res, i, 1))
1818 blobOid = atooid(PQgetvalue(res, i, 0));
1819 comment = PQgetvalue(res, i, 1);
1821 printfPQExpBuffer(commentcmd, "COMMENT ON LARGE OBJECT %u IS ",
1823 appendStringLiteralAH(commentcmd, comment, AH);
1824 appendPQExpBuffer(commentcmd, ";\n");
1826 archputs(commentcmd->data, AH);
1828 } while (PQntuples(res) > 0);
1834 destroyPQExpBuffer(commentcmd);
1841 * read all namespaces in the system catalogs and return them in the
1842 * NamespaceInfo* structure
1844 * numNamespaces is set to the number of namespaces read in
1847 getNamespaces(int *numNamespaces)
1853 NamespaceInfo *nsinfo;
1861 * Before 7.3, there are no real namespaces; create two dummy entries, one
1862 * for user stuff and one for system stuff.
1864 if (g_fout->remoteVersion < 70300)
1866 nsinfo = (NamespaceInfo *) malloc(2 * sizeof(NamespaceInfo));
1868 nsinfo[0].dobj.objType = DO_NAMESPACE;
1869 nsinfo[0].dobj.catId.tableoid = 0;
1870 nsinfo[0].dobj.catId.oid = 0;
1871 AssignDumpId(&nsinfo[0].dobj);
1872 nsinfo[0].dobj.name = strdup("public");
1873 nsinfo[0].rolname = strdup("");
1874 nsinfo[0].nspacl = strdup("");
1876 selectDumpableNamespace(&nsinfo[0]);
1878 nsinfo[1].dobj.objType = DO_NAMESPACE;
1879 nsinfo[1].dobj.catId.tableoid = 0;
1880 nsinfo[1].dobj.catId.oid = 1;
1881 AssignDumpId(&nsinfo[1].dobj);
1882 nsinfo[1].dobj.name = strdup("pg_catalog");
1883 nsinfo[1].rolname = strdup("");
1884 nsinfo[1].nspacl = strdup("");
1886 selectDumpableNamespace(&nsinfo[1]);
1888 g_namespaces = nsinfo;
1889 g_numNamespaces = *numNamespaces = 2;
1894 query = createPQExpBuffer();
1896 /* Make sure we are in proper schema */
1897 selectSourceSchema("pg_catalog");
1900 * we fetch all namespaces including system ones, so that every object we
1901 * read in can be linked to a containing namespace.
1903 appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
1904 "(%s nspowner) as rolname, "
1905 "nspacl FROM pg_namespace",
1908 res = PQexec(g_conn, query->data);
1909 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1911 ntups = PQntuples(res);
1913 nsinfo = (NamespaceInfo *) malloc(ntups * sizeof(NamespaceInfo));
1915 i_tableoid = PQfnumber(res, "tableoid");
1916 i_oid = PQfnumber(res, "oid");
1917 i_nspname = PQfnumber(res, "nspname");
1918 i_rolname = PQfnumber(res, "rolname");
1919 i_nspacl = PQfnumber(res, "nspacl");
1921 for (i = 0; i < ntups; i++)
1923 nsinfo[i].dobj.objType = DO_NAMESPACE;
1924 nsinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1925 nsinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1926 AssignDumpId(&nsinfo[i].dobj);
1927 nsinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_nspname));
1928 nsinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
1929 nsinfo[i].nspacl = strdup(PQgetvalue(res, i, i_nspacl));
1931 /* Decide whether to dump this namespace */
1932 selectDumpableNamespace(&nsinfo[i]);
1934 if (strlen(nsinfo[i].rolname) == 0)
1935 write_msg(NULL, "WARNING: owner of schema \"%s\" appears to be invalid\n",
1936 nsinfo[i].dobj.name);
1940 destroyPQExpBuffer(query);
1942 g_namespaces = nsinfo;
1943 g_numNamespaces = *numNamespaces = ntups;
1950 * given a namespace OID and an object OID, look up the info read by
1953 * NB: for pre-7.3 source database, we use object OID to guess whether it's
1954 * a system object or not. In 7.3 and later there is no guessing.
1956 static NamespaceInfo *
1957 findNamespace(Oid nsoid, Oid objoid)
1961 if (g_fout->remoteVersion >= 70300)
1963 for (i = 0; i < g_numNamespaces; i++)
1965 NamespaceInfo *nsinfo = &g_namespaces[i];
1967 if (nsoid == nsinfo->dobj.catId.oid)
1970 write_msg(NULL, "schema with OID %u does not exist\n", nsoid);
1975 /* This code depends on the layout set up by getNamespaces. */
1976 if (objoid > g_last_builtin_oid)
1977 i = 0; /* user object */
1979 i = 1; /* system object */
1980 return &g_namespaces[i];
1983 return NULL; /* keep compiler quiet */
1988 * read all types in the system catalogs and return them in the
1989 * TypeInfo* structure
1991 * numTypes is set to the number of types read in
1993 * NB: this must run after getFuncs() because we assume we can do
1997 getTypes(int *numTypes)
2002 PQExpBuffer query = createPQExpBuffer();
2004 ShellTypeInfo *stinfo;
2019 * we include even the built-in types because those may be used as array
2020 * elements by user-defined types
2022 * we filter out the built-in types when we dump out the types
2024 * same approach for undefined (shell) types
2027 /* Make sure we are in proper schema */
2028 selectSourceSchema("pg_catalog");
2030 if (g_fout->remoteVersion >= 70300)
2032 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
2034 "(%s typowner) as rolname, "
2035 "typinput::oid as typinput, "
2036 "typoutput::oid as typoutput, typelem, typrelid, "
2037 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
2038 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
2039 "typtype, typisdefined "
2043 else if (g_fout->remoteVersion >= 70100)
2045 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
2046 "0::oid as typnamespace, "
2047 "(%s typowner) as rolname, "
2048 "typinput::oid as typinput, "
2049 "typoutput::oid as typoutput, typelem, typrelid, "
2050 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
2051 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
2052 "typtype, typisdefined "
2058 appendPQExpBuffer(query, "SELECT "
2059 "(SELECT oid FROM pg_class WHERE relname = 'pg_type') AS tableoid, "
2061 "0::oid as typnamespace, "
2062 "(%s typowner) as rolname, "
2063 "typinput::oid as typinput, "
2064 "typoutput::oid as typoutput, typelem, typrelid, "
2065 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
2066 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
2067 "typtype, typisdefined "
2072 res = PQexec(g_conn, query->data);
2073 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2075 ntups = PQntuples(res);
2077 tinfo = (TypeInfo *) malloc(ntups * sizeof(TypeInfo));
2079 i_tableoid = PQfnumber(res, "tableoid");
2080 i_oid = PQfnumber(res, "oid");
2081 i_typname = PQfnumber(res, "typname");
2082 i_typnamespace = PQfnumber(res, "typnamespace");
2083 i_rolname = PQfnumber(res, "rolname");
2084 i_typinput = PQfnumber(res, "typinput");
2085 i_typoutput = PQfnumber(res, "typoutput");
2086 i_typelem = PQfnumber(res, "typelem");
2087 i_typrelid = PQfnumber(res, "typrelid");
2088 i_typrelkind = PQfnumber(res, "typrelkind");
2089 i_typtype = PQfnumber(res, "typtype");
2090 i_typisdefined = PQfnumber(res, "typisdefined");
2092 for (i = 0; i < ntups; i++)
2094 tinfo[i].dobj.objType = DO_TYPE;
2095 tinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2096 tinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2097 AssignDumpId(&tinfo[i].dobj);
2098 tinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_typname));
2099 tinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_typnamespace)),
2100 tinfo[i].dobj.catId.oid);
2101 tinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2102 tinfo[i].typelem = atooid(PQgetvalue(res, i, i_typelem));
2103 tinfo[i].typrelid = atooid(PQgetvalue(res, i, i_typrelid));
2104 tinfo[i].typrelkind = *PQgetvalue(res, i, i_typrelkind);
2105 tinfo[i].typtype = *PQgetvalue(res, i, i_typtype);
2106 tinfo[i].shellType = NULL;
2109 * If it's a table's rowtype, use special type code to facilitate
2110 * sorting into the desired order. (We don't want to consider it an
2111 * ordinary type because that would bring the table up into the
2112 * datatype part of the dump order.)
2114 if (OidIsValid(tinfo[i].typrelid) &&
2115 tinfo[i].typrelkind != RELKIND_COMPOSITE_TYPE)
2116 tinfo[i].dobj.objType = DO_TABLE_TYPE;
2119 * check for user-defined array types, omit system generated ones
2121 if (OidIsValid(tinfo[i].typelem) &&
2122 tinfo[i].dobj.name[0] != '_')
2123 tinfo[i].isArray = true;
2125 tinfo[i].isArray = false;
2127 if (strcmp(PQgetvalue(res, i, i_typisdefined), "t") == 0)
2128 tinfo[i].isDefined = true;
2130 tinfo[i].isDefined = false;
2132 /* Decide whether we want to dump it */
2133 selectDumpableType(&tinfo[i]);
2136 * If it's a domain, fetch info about its constraints, if any
2138 tinfo[i].nDomChecks = 0;
2139 tinfo[i].domChecks = NULL;
2140 if (tinfo[i].dobj.dump && tinfo[i].typtype == 'd')
2141 getDomainConstraints(&(tinfo[i]));
2144 * If it's a base type, make a DumpableObject representing a shell
2145 * definition of the type. We will need to dump that ahead of the
2146 * I/O functions for the type.
2148 * Note: the shell type doesn't have a catId. You might think it
2149 * should copy the base type's catId, but then it might capture
2150 * the pg_depend entries for the type, which we don't want.
2152 if (tinfo[i].dobj.dump && tinfo[i].typtype == 'b')
2154 stinfo = (ShellTypeInfo *) malloc(sizeof(ShellTypeInfo));
2155 stinfo->dobj.objType = DO_SHELL_TYPE;
2156 stinfo->dobj.catId = nilCatalogId;
2157 AssignDumpId(&stinfo->dobj);
2158 stinfo->dobj.name = strdup(tinfo[i].dobj.name);
2159 stinfo->dobj.namespace = tinfo[i].dobj.namespace;
2160 stinfo->baseType = &(tinfo[i]);
2161 tinfo[i].shellType = stinfo;
2164 * Initially mark the shell type as not to be dumped. We'll
2165 * only dump it if the I/O functions need to be dumped; this
2166 * is taken care of while sorting dependencies.
2168 stinfo->dobj.dump = false;
2171 * However, if dumping from pre-7.3, there will be no dependency
2172 * info so we have to fake it here. We only need to worry about
2173 * typinput and typoutput since the other functions only exist
2176 if (g_fout->remoteVersion < 70300)
2182 typinput = atooid(PQgetvalue(res, i, i_typinput));
2183 typoutput = atooid(PQgetvalue(res, i, i_typoutput));
2185 funcInfo = findFuncByOid(typinput);
2186 if (funcInfo && funcInfo->dobj.dump)
2188 /* base type depends on function */
2189 addObjectDependency(&tinfo[i].dobj,
2190 funcInfo->dobj.dumpId);
2191 /* function depends on shell type */
2192 addObjectDependency(&funcInfo->dobj,
2193 stinfo->dobj.dumpId);
2194 /* mark shell type as to be dumped */
2195 stinfo->dobj.dump = true;
2198 funcInfo = findFuncByOid(typoutput);
2199 if (funcInfo && funcInfo->dobj.dump)
2201 /* base type depends on function */
2202 addObjectDependency(&tinfo[i].dobj,
2203 funcInfo->dobj.dumpId);
2204 /* function depends on shell type */
2205 addObjectDependency(&funcInfo->dobj,
2206 stinfo->dobj.dumpId);
2207 /* mark shell type as to be dumped */
2208 stinfo->dobj.dump = true;
2213 if (strlen(tinfo[i].rolname) == 0 && tinfo[i].isDefined)
2214 write_msg(NULL, "WARNING: owner of data type \"%s\" appears to be invalid\n",
2215 tinfo[i].dobj.name);
2222 destroyPQExpBuffer(query);
2229 * read all operators in the system catalogs and return them in the
2230 * OprInfo* structure
2232 * numOprs is set to the number of operators read in
2235 getOperators(int *numOprs)
2240 PQExpBuffer query = createPQExpBuffer();
2250 * find all operators, including builtin operators; we filter out
2251 * system-defined operators at dump-out time.
2254 /* Make sure we are in proper schema */
2255 selectSourceSchema("pg_catalog");
2257 if (g_fout->remoteVersion >= 70300)
2259 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
2261 "(%s oprowner) as rolname, "
2262 "oprcode::oid as oprcode "
2266 else if (g_fout->remoteVersion >= 70100)
2268 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
2269 "0::oid as oprnamespace, "
2270 "(%s oprowner) as rolname, "
2271 "oprcode::oid as oprcode "
2277 appendPQExpBuffer(query, "SELECT "
2278 "(SELECT oid FROM pg_class WHERE relname = 'pg_operator') AS tableoid, "
2280 "0::oid as oprnamespace, "
2281 "(%s oprowner) as rolname, "
2282 "oprcode::oid as oprcode "
2287 res = PQexec(g_conn, query->data);
2288 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2290 ntups = PQntuples(res);
2293 oprinfo = (OprInfo *) malloc(ntups * sizeof(OprInfo));
2295 i_tableoid = PQfnumber(res, "tableoid");
2296 i_oid = PQfnumber(res, "oid");
2297 i_oprname = PQfnumber(res, "oprname");
2298 i_oprnamespace = PQfnumber(res, "oprnamespace");
2299 i_rolname = PQfnumber(res, "rolname");
2300 i_oprcode = PQfnumber(res, "oprcode");
2302 for (i = 0; i < ntups; i++)
2304 oprinfo[i].dobj.objType = DO_OPERATOR;
2305 oprinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2306 oprinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2307 AssignDumpId(&oprinfo[i].dobj);
2308 oprinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_oprname));
2309 oprinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_oprnamespace)),
2310 oprinfo[i].dobj.catId.oid);
2311 oprinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2312 oprinfo[i].oprcode = atooid(PQgetvalue(res, i, i_oprcode));
2314 /* Decide whether we want to dump it */
2315 selectDumpableObject(&(oprinfo[i].dobj));
2317 if (strlen(oprinfo[i].rolname) == 0)
2318 write_msg(NULL, "WARNING: owner of operator \"%s\" appears to be invalid\n",
2319 oprinfo[i].dobj.name);
2324 destroyPQExpBuffer(query);
2331 * read all conversions in the system catalogs and return them in the
2332 * ConvInfo* structure
2334 * numConversions is set to the number of conversions read in
2337 getConversions(int *numConversions)
2342 PQExpBuffer query = createPQExpBuffer();
2350 /* Conversions didn't exist pre-7.3 */
2351 if (g_fout->remoteVersion < 70300)
2353 *numConversions = 0;
2358 * find all conversions, including builtin conversions; we filter out
2359 * system-defined conversions at dump-out time.
2362 /* Make sure we are in proper schema */
2363 selectSourceSchema("pg_catalog");
2365 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
2367 "(%s conowner) as rolname "
2368 "FROM pg_conversion",
2371 res = PQexec(g_conn, query->data);
2372 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2374 ntups = PQntuples(res);
2375 *numConversions = ntups;
2377 convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
2379 i_tableoid = PQfnumber(res, "tableoid");
2380 i_oid = PQfnumber(res, "oid");
2381 i_conname = PQfnumber(res, "conname");
2382 i_connamespace = PQfnumber(res, "connamespace");
2383 i_rolname = PQfnumber(res, "rolname");
2385 for (i = 0; i < ntups; i++)
2387 convinfo[i].dobj.objType = DO_CONVERSION;
2388 convinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2389 convinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2390 AssignDumpId(&convinfo[i].dobj);
2391 convinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_conname));
2392 convinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_connamespace)),
2393 convinfo[i].dobj.catId.oid);
2394 convinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2396 /* Decide whether we want to dump it */
2397 selectDumpableObject(&(convinfo[i].dobj));
2402 destroyPQExpBuffer(query);
2409 * read all opclasses in the system catalogs and return them in the
2410 * OpclassInfo* structure
2412 * numOpclasses is set to the number of opclasses read in
2415 getOpclasses(int *numOpclasses)
2420 PQExpBuffer query = createPQExpBuffer();
2421 OpclassInfo *opcinfo;
2429 * find all opclasses, including builtin opclasses; we filter out
2430 * system-defined opclasses at dump-out time.
2433 /* Make sure we are in proper schema */
2434 selectSourceSchema("pg_catalog");
2436 if (g_fout->remoteVersion >= 70300)
2438 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
2440 "(%s opcowner) as rolname "
2444 else if (g_fout->remoteVersion >= 70100)
2446 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
2447 "0::oid as opcnamespace, "
2448 "''::name as rolname "
2453 appendPQExpBuffer(query, "SELECT "
2454 "(SELECT oid FROM pg_class WHERE relname = 'pg_opclass') AS tableoid, "
2456 "0::oid as opcnamespace, "
2457 "''::name as rolname "
2461 res = PQexec(g_conn, query->data);
2462 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2464 ntups = PQntuples(res);
2465 *numOpclasses = ntups;
2467 opcinfo = (OpclassInfo *) malloc(ntups * sizeof(OpclassInfo));
2469 i_tableoid = PQfnumber(res, "tableoid");
2470 i_oid = PQfnumber(res, "oid");
2471 i_opcname = PQfnumber(res, "opcname");
2472 i_opcnamespace = PQfnumber(res, "opcnamespace");
2473 i_rolname = PQfnumber(res, "rolname");
2475 for (i = 0; i < ntups; i++)
2477 opcinfo[i].dobj.objType = DO_OPCLASS;
2478 opcinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2479 opcinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2480 AssignDumpId(&opcinfo[i].dobj);
2481 opcinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_opcname));
2482 opcinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_opcnamespace)),
2483 opcinfo[i].dobj.catId.oid);
2484 opcinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2486 /* Decide whether we want to dump it */
2487 selectDumpableObject(&(opcinfo[i].dobj));
2489 if (g_fout->remoteVersion >= 70300)
2491 if (strlen(opcinfo[i].rolname) == 0)
2492 write_msg(NULL, "WARNING: owner of operator class \"%s\" appears to be invalid\n",
2493 opcinfo[i].dobj.name);
2499 destroyPQExpBuffer(query);
2506 * read all the user-defined aggregates in the system catalogs and
2507 * return them in the AggInfo* structure
2509 * numAggs is set to the number of aggregates read in
2512 getAggregates(int *numAggs)
2517 PQExpBuffer query = createPQExpBuffer();
2528 /* Make sure we are in proper schema */
2529 selectSourceSchema("pg_catalog");
2531 /* find all user-defined aggregates */
2533 if (g_fout->remoteVersion >= 80200)
2535 appendPQExpBuffer(query, "SELECT tableoid, oid, proname as aggname, "
2536 "pronamespace as aggnamespace, "
2537 "pronargs, proargtypes, "
2538 "(%s proowner) as rolname, "
2542 "AND pronamespace != "
2543 "(select oid from pg_namespace where nspname = 'pg_catalog')",
2546 else if (g_fout->remoteVersion >= 70300)
2548 appendPQExpBuffer(query, "SELECT tableoid, oid, proname as aggname, "
2549 "pronamespace as aggnamespace, "
2550 "CASE WHEN proargtypes[0] = 'pg_catalog.\"any\"'::pg_catalog.regtype THEN 0 ELSE 1 END as pronargs, "
2552 "(%s proowner) as rolname, "
2556 "AND pronamespace != "
2557 "(select oid from pg_namespace where nspname = 'pg_catalog')",
2560 else if (g_fout->remoteVersion >= 70100)
2562 appendPQExpBuffer(query, "SELECT tableoid, oid, aggname, "
2563 "0::oid as aggnamespace, "
2564 "CASE WHEN aggbasetype = 0 THEN 0 ELSE 1 END as pronargs, "
2565 "aggbasetype as proargtypes, "
2566 "(%s aggowner) as rolname, "
2568 "FROM pg_aggregate "
2569 "where oid > '%u'::oid",
2571 g_last_builtin_oid);
2575 appendPQExpBuffer(query, "SELECT "
2576 "(SELECT oid FROM pg_class WHERE relname = 'pg_aggregate') AS tableoid, "
2578 "0::oid as aggnamespace, "
2579 "CASE WHEN aggbasetype = 0 THEN 0 ELSE 1 END as pronargs, "
2580 "aggbasetype as proargtypes, "
2581 "(%s aggowner) as rolname, "
2583 "FROM pg_aggregate "
2584 "where oid > '%u'::oid",
2586 g_last_builtin_oid);
2589 res = PQexec(g_conn, query->data);
2590 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2592 ntups = PQntuples(res);
2595 agginfo = (AggInfo *) malloc(ntups * sizeof(AggInfo));
2597 i_tableoid = PQfnumber(res, "tableoid");
2598 i_oid = PQfnumber(res, "oid");
2599 i_aggname = PQfnumber(res, "aggname");
2600 i_aggnamespace = PQfnumber(res, "aggnamespace");
2601 i_pronargs = PQfnumber(res, "pronargs");
2602 i_proargtypes = PQfnumber(res, "proargtypes");
2603 i_rolname = PQfnumber(res, "rolname");
2604 i_aggacl = PQfnumber(res, "aggacl");
2606 for (i = 0; i < ntups; i++)
2608 agginfo[i].aggfn.dobj.objType = DO_AGG;
2609 agginfo[i].aggfn.dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2610 agginfo[i].aggfn.dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2611 AssignDumpId(&agginfo[i].aggfn.dobj);
2612 agginfo[i].aggfn.dobj.name = strdup(PQgetvalue(res, i, i_aggname));
2613 agginfo[i].aggfn.dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_aggnamespace)),
2614 agginfo[i].aggfn.dobj.catId.oid);
2615 agginfo[i].aggfn.rolname = strdup(PQgetvalue(res, i, i_rolname));
2616 if (strlen(agginfo[i].aggfn.rolname) == 0)
2617 write_msg(NULL, "WARNING: owner of aggregate function \"%s\" appears to be invalid\n",
2618 agginfo[i].aggfn.dobj.name);
2619 agginfo[i].aggfn.lang = InvalidOid; /* not currently interesting */
2620 agginfo[i].aggfn.prorettype = InvalidOid; /* not saved */
2621 agginfo[i].aggfn.proacl = strdup(PQgetvalue(res, i, i_aggacl));
2622 agginfo[i].aggfn.nargs = atoi(PQgetvalue(res, i, i_pronargs));
2623 if (agginfo[i].aggfn.nargs == 0)
2624 agginfo[i].aggfn.argtypes = NULL;
2627 agginfo[i].aggfn.argtypes = (Oid *) malloc(agginfo[i].aggfn.nargs * sizeof(Oid));
2628 if (g_fout->remoteVersion >= 70300)
2629 parseOidArray(PQgetvalue(res, i, i_proargtypes),
2630 agginfo[i].aggfn.argtypes,
2631 agginfo[i].aggfn.nargs);
2632 else /* it's just aggbasetype */
2633 agginfo[i].aggfn.argtypes[0] = atooid(PQgetvalue(res, i, i_proargtypes));
2636 /* Decide whether we want to dump it */
2637 selectDumpableObject(&(agginfo[i].aggfn.dobj));
2642 destroyPQExpBuffer(query);
2649 * read all the user-defined functions in the system catalogs and
2650 * return them in the FuncInfo* structure
2652 * numFuncs is set to the number of functions read in
2655 getFuncs(int *numFuncs)
2660 PQExpBuffer query = createPQExpBuffer();
2673 /* Make sure we are in proper schema */
2674 selectSourceSchema("pg_catalog");
2676 /* find all user-defined funcs */
2678 if (g_fout->remoteVersion >= 70300)
2680 appendPQExpBuffer(query,
2681 "SELECT tableoid, oid, proname, prolang, "
2682 "pronargs, proargtypes, prorettype, proacl, "
2684 "(%s proowner) as rolname "
2686 "WHERE NOT proisagg "
2687 "AND pronamespace != "
2688 "(select oid from pg_namespace"
2689 " where nspname = 'pg_catalog')",
2692 else if (g_fout->remoteVersion >= 70100)
2694 appendPQExpBuffer(query,
2695 "SELECT tableoid, oid, proname, prolang, "
2696 "pronargs, proargtypes, prorettype, "
2697 "'{=X}' as proacl, "
2698 "0::oid as pronamespace, "
2699 "(%s proowner) as rolname "
2701 "where pg_proc.oid > '%u'::oid",
2703 g_last_builtin_oid);
2707 appendPQExpBuffer(query,
2709 "(SELECT oid FROM pg_class "
2710 " WHERE relname = 'pg_proc') AS tableoid, "
2711 "oid, proname, prolang, "
2712 "pronargs, proargtypes, prorettype, "
2713 "'{=X}' as proacl, "
2714 "0::oid as pronamespace, "
2715 "(%s proowner) as rolname "
2717 "where pg_proc.oid > '%u'::oid",
2719 g_last_builtin_oid);
2722 res = PQexec(g_conn, query->data);
2723 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2725 ntups = PQntuples(res);
2729 finfo = (FuncInfo *) calloc(ntups, sizeof(FuncInfo));
2731 i_tableoid = PQfnumber(res, "tableoid");
2732 i_oid = PQfnumber(res, "oid");
2733 i_proname = PQfnumber(res, "proname");
2734 i_pronamespace = PQfnumber(res, "pronamespace");
2735 i_rolname = PQfnumber(res, "rolname");
2736 i_prolang = PQfnumber(res, "prolang");
2737 i_pronargs = PQfnumber(res, "pronargs");
2738 i_proargtypes = PQfnumber(res, "proargtypes");
2739 i_prorettype = PQfnumber(res, "prorettype");
2740 i_proacl = PQfnumber(res, "proacl");
2742 for (i = 0; i < ntups; i++)
2744 finfo[i].dobj.objType = DO_FUNC;
2745 finfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2746 finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2747 AssignDumpId(&finfo[i].dobj);
2748 finfo[i].dobj.name = strdup(PQgetvalue(res, i, i_proname));
2749 finfo[i].dobj.namespace =
2750 findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
2751 finfo[i].dobj.catId.oid);
2752 finfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2753 finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
2754 finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
2755 finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
2756 finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
2757 if (finfo[i].nargs == 0)
2758 finfo[i].argtypes = NULL;
2761 finfo[i].argtypes = (Oid *) malloc(finfo[i].nargs * sizeof(Oid));
2762 parseOidArray(PQgetvalue(res, i, i_proargtypes),
2763 finfo[i].argtypes, finfo[i].nargs);
2766 /* Decide whether we want to dump it */
2767 selectDumpableObject(&(finfo[i].dobj));
2769 if (strlen(finfo[i].rolname) == 0)
2771 "WARNING: owner of function \"%s\" appears to be invalid\n",
2772 finfo[i].dobj.name);
2777 destroyPQExpBuffer(query);
2784 * read all the user-defined tables (no indexes, no catalogs)
2785 * in the system catalogs return them in the TableInfo* structure
2787 * numTables is set to the number of tables read in
2790 getTables(int *numTables)
2795 PQExpBuffer query = createPQExpBuffer();
2796 PQExpBuffer delqry = createPQExpBuffer();
2797 PQExpBuffer lockquery = createPQExpBuffer();
2813 int i_reltablespace;
2816 /* Make sure we are in proper schema */
2817 selectSourceSchema("pg_catalog");
2820 * Find all the tables (including views and sequences).
2822 * We include system catalogs, so that we can work if a user table is
2823 * defined to inherit from a system catalog (pretty weird, but...)
2825 * We ignore tables that are not type 'r' (ordinary relation), 'S'
2826 * (sequence), 'v' (view), or 'c' (composite type).
2828 * Composite-type table entries won't be dumped as such, but we have to
2829 * make a DumpableObject for them so that we can track dependencies of the
2830 * composite type (pg_depend entries for columns of the composite type
2831 * link to the pg_class entry not the pg_type entry).
2833 * Note: in this phase we should collect only a minimal amount of
2834 * information about each table, basically just enough to decide if it is
2835 * interesting. We must fetch all tables in this phase because otherwise
2836 * we cannot correctly identify inherited columns, owned sequences, etc.
2839 if (g_fout->remoteVersion >= 80200)
2842 * Left join to pick up dependency info linking sequences to their
2843 * owning column, if any (note this dependency is AUTO as of 8.2)
2845 appendPQExpBuffer(query,
2846 "SELECT c.tableoid, c.oid, relname, "
2847 "relacl, relkind, relnamespace, "
2848 "(%s relowner) as rolname, "
2849 "relchecks, reltriggers, "
2850 "relhasindex, relhasrules, relhasoids, "
2851 "d.refobjid as owning_tab, "
2852 "d.refobjsubid as owning_col, "
2853 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
2854 "array_to_string(c.reloptions, ', ') as reloptions "
2856 "left join pg_depend d on "
2857 "(c.relkind = '%c' and "
2858 "d.classid = c.tableoid and d.objid = c.oid and "
2859 "d.objsubid = 0 and "
2860 "d.refclassid = c.tableoid and d.deptype = 'a') "
2861 "where relkind in ('%c', '%c', '%c', '%c') "
2865 RELKIND_RELATION, RELKIND_SEQUENCE,
2866 RELKIND_VIEW, RELKIND_COMPOSITE_TYPE);
2868 else if (g_fout->remoteVersion >= 80000)
2871 * Left join to pick up dependency info linking sequences to their
2872 * owning column, if any
2874 appendPQExpBuffer(query,
2875 "SELECT c.tableoid, c.oid, relname, "
2876 "relacl, relkind, relnamespace, "
2877 "(%s relowner) as rolname, "
2878 "relchecks, reltriggers, "
2879 "relhasindex, relhasrules, relhasoids, "
2880 "d.refobjid as owning_tab, "
2881 "d.refobjsubid as owning_col, "
2882 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
2883 "NULL as reloptions "
2885 "left join pg_depend d on "
2886 "(c.relkind = '%c' and "
2887 "d.classid = c.tableoid and d.objid = c.oid and "
2888 "d.objsubid = 0 and "
2889 "d.refclassid = c.tableoid and d.deptype = 'i') "
2890 "where relkind in ('%c', '%c', '%c', '%c') "
2894 RELKIND_RELATION, RELKIND_SEQUENCE,
2895 RELKIND_VIEW, RELKIND_COMPOSITE_TYPE);
2897 else if (g_fout->remoteVersion >= 70300)
2900 * Left join to pick up dependency info linking sequences to their
2901 * owning column, if any
2903 appendPQExpBuffer(query,
2904 "SELECT c.tableoid, c.oid, relname, "
2905 "relacl, relkind, relnamespace, "
2906 "(%s relowner) as rolname, "
2907 "relchecks, reltriggers, "
2908 "relhasindex, relhasrules, relhasoids, "
2909 "d.refobjid as owning_tab, "
2910 "d.refobjsubid as owning_col, "
2911 "NULL as reltablespace, "
2912 "NULL as reloptions "
2914 "left join pg_depend d on "
2915 "(c.relkind = '%c' and "
2916 "d.classid = c.tableoid and d.objid = c.oid and "
2917 "d.objsubid = 0 and "
2918 "d.refclassid = c.tableoid and d.deptype = 'i') "
2919 "where relkind in ('%c', '%c', '%c', '%c') "
2923 RELKIND_RELATION, RELKIND_SEQUENCE,
2924 RELKIND_VIEW, RELKIND_COMPOSITE_TYPE);
2926 else if (g_fout->remoteVersion >= 70200)
2928 appendPQExpBuffer(query,
2929 "SELECT tableoid, oid, relname, relacl, relkind, "
2930 "0::oid as relnamespace, "
2931 "(%s relowner) as rolname, "
2932 "relchecks, reltriggers, "
2933 "relhasindex, relhasrules, relhasoids, "
2934 "NULL::oid as owning_tab, "
2935 "NULL::int4 as owning_col, "
2936 "NULL as reltablespace, "
2937 "NULL as reloptions "
2939 "where relkind in ('%c', '%c', '%c') "
2942 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2944 else if (g_fout->remoteVersion >= 70100)
2946 /* all tables have oids in 7.1 */
2947 appendPQExpBuffer(query,
2948 "SELECT tableoid, oid, relname, relacl, relkind, "
2949 "0::oid as relnamespace, "
2950 "(%s relowner) as rolname, "
2951 "relchecks, reltriggers, "
2952 "relhasindex, relhasrules, "
2953 "'t'::bool as relhasoids, "
2954 "NULL::oid as owning_tab, "
2955 "NULL::int4 as owning_col, "
2956 "NULL as reltablespace, "
2957 "NULL as reloptions "
2959 "where relkind in ('%c', '%c', '%c') "
2962 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2967 * Before 7.1, view relkind was not set to 'v', so we must check if we
2968 * have a view by looking for a rule in pg_rewrite.
2970 appendPQExpBuffer(query,
2972 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2973 "oid, relname, relacl, "
2974 "CASE WHEN relhasrules and relkind = 'r' "
2975 " and EXISTS(SELECT rulename FROM pg_rewrite r WHERE "
2976 " r.ev_class = c.oid AND r.ev_type = '1') "
2977 "THEN '%c'::\"char\" "
2978 "ELSE relkind END AS relkind,"
2979 "0::oid as relnamespace, "
2980 "(%s relowner) as rolname, "
2981 "relchecks, reltriggers, "
2982 "relhasindex, relhasrules, "
2983 "'t'::bool as relhasoids, "
2984 "NULL::oid as owning_tab, "
2985 "NULL::int4 as owning_col, "
2986 "NULL as reltablespace, "
2987 "NULL as reloptions "
2989 "where relkind in ('%c', '%c') "
2993 RELKIND_RELATION, RELKIND_SEQUENCE);
2996 res = PQexec(g_conn, query->data);
2997 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2999 ntups = PQntuples(res);
3004 * Extract data from result and lock dumpable tables. We do the locking
3005 * before anything else, to minimize the window wherein a table could
3006 * disappear under us.
3008 * Note that we have to save info about all tables here, even when dumping
3009 * only one, because we don't yet know which tables might be inheritance
3010 * ancestors of the target table.
3012 tblinfo = (TableInfo *) calloc(ntups, sizeof(TableInfo));
3014 i_reltableoid = PQfnumber(res, "tableoid");
3015 i_reloid = PQfnumber(res, "oid");
3016 i_relname = PQfnumber(res, "relname");
3017 i_relnamespace = PQfnumber(res, "relnamespace");
3018 i_relacl = PQfnumber(res, "relacl");
3019 i_relkind = PQfnumber(res, "relkind");
3020 i_rolname = PQfnumber(res, "rolname");
3021 i_relchecks = PQfnumber(res, "relchecks");
3022 i_reltriggers = PQfnumber(res, "reltriggers");
3023 i_relhasindex = PQfnumber(res, "relhasindex");
3024 i_relhasrules = PQfnumber(res, "relhasrules");
3025 i_relhasoids = PQfnumber(res, "relhasoids");
3026 i_owning_tab = PQfnumber(res, "owning_tab");
3027 i_owning_col = PQfnumber(res, "owning_col");
3028 i_reltablespace = PQfnumber(res, "reltablespace");
3029 i_reloptions = PQfnumber(res, "reloptions");
3031 for (i = 0; i < ntups; i++)
3033 tblinfo[i].dobj.objType = DO_TABLE;
3034 tblinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_reltableoid));
3035 tblinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_reloid));
3036 AssignDumpId(&tblinfo[i].dobj);
3037 tblinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_relname));
3038 tblinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_relnamespace)),
3039 tblinfo[i].dobj.catId.oid);
3040 tblinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
3041 tblinfo[i].relacl = strdup(PQgetvalue(res, i, i_relacl));
3042 tblinfo[i].relkind = *(PQgetvalue(res, i, i_relkind));
3043 tblinfo[i].hasindex = (strcmp(PQgetvalue(res, i, i_relhasindex), "t") == 0);
3044 tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
3045 tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0);
3046 tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
3047 tblinfo[i].ntrig = atoi(PQgetvalue(res, i, i_reltriggers));
3048 if (PQgetisnull(res, i, i_owning_tab))
3050 tblinfo[i].owning_tab = InvalidOid;
3051 tblinfo[i].owning_col = 0;
3055 tblinfo[i].owning_tab = atooid(PQgetvalue(res, i, i_owning_tab));
3056 tblinfo[i].owning_col = atoi(PQgetvalue(res, i, i_owning_col));
3058 tblinfo[i].reltablespace = strdup(PQgetvalue(res, i, i_reltablespace));
3059 tblinfo[i].reloptions = strdup(PQgetvalue(res, i, i_reloptions));
3061 /* other fields were zeroed above */
3064 * Decide whether we want to dump this table.
3066 if (tblinfo[i].relkind == RELKIND_COMPOSITE_TYPE)
3067 tblinfo[i].dobj.dump = false;
3069 selectDumpableTable(&tblinfo[i]);
3070 tblinfo[i].interesting = tblinfo[i].dobj.dump;
3073 * Read-lock target tables to make sure they aren't DROPPED or altered
3074 * in schema before we get around to dumping them.
3076 * Note that we don't explicitly lock parents of the target tables; we
3077 * assume our lock on the child is enough to prevent schema
3078 * alterations to parent tables.
3080 * NOTE: it'd be kinda nice to lock views and sequences too, not only
3081 * plain tables, but the backend doesn't presently allow that.
3083 if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION)
3085 resetPQExpBuffer(lockquery);
3086 appendPQExpBuffer(lockquery,
3087 "LOCK TABLE %s IN ACCESS SHARE MODE",
3088 fmtQualifiedId(tblinfo[i].dobj.namespace->dobj.name,
3089 tblinfo[i].dobj.name));
3090 do_sql_command(g_conn, lockquery->data);
3093 /* Emit notice if join for owner failed */
3094 if (strlen(tblinfo[i].rolname) == 0)
3095 write_msg(NULL, "WARNING: owner of table \"%s\" appears to be invalid\n",
3096 tblinfo[i].dobj.name);
3102 * Force sequences that are "owned" by table columns to be dumped
3103 * whenever their owning table is being dumped.
3105 for (i = 0; i < ntups; i++)
3107 TableInfo *seqinfo = &tblinfo[i];
3110 if (!OidIsValid(seqinfo->owning_tab))
3111 continue; /* not an owned sequence */
3112 if (seqinfo->dobj.dump)
3113 continue; /* no need to search */
3115 /* can't use findTableByOid yet, unfortunately */
3116 for (j = 0; j < ntups; j++)
3118 if (tblinfo[j].dobj.catId.oid == seqinfo->owning_tab)
3120 if (tblinfo[j].dobj.dump)
3122 seqinfo->interesting = true;
3123 seqinfo->dobj.dump = true;
3130 destroyPQExpBuffer(query);
3131 destroyPQExpBuffer(delqry);
3132 destroyPQExpBuffer(lockquery);
3139 * read all the inheritance information
3140 * from the system catalogs return them in the InhInfo* structure
3142 * numInherits is set to the number of pairs read in
3145 getInherits(int *numInherits)
3150 PQExpBuffer query = createPQExpBuffer();
3156 /* Make sure we are in proper schema */
3157 selectSourceSchema("pg_catalog");
3159 /* find all the inheritance information */
3161 appendPQExpBuffer(query, "SELECT inhrelid, inhparent from pg_inherits");
3163 res = PQexec(g_conn, query->data);
3164 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3166 ntups = PQntuples(res);
3168 *numInherits = ntups;
3170 inhinfo = (InhInfo *) malloc(ntups * sizeof(InhInfo));
3172 i_inhrelid = PQfnumber(res, "inhrelid");
3173 i_inhparent = PQfnumber(res, "inhparent");
3175 for (i = 0; i < ntups; i++)
3177 inhinfo[i].inhrelid = atooid(PQgetvalue(res, i, i_inhrelid));
3178 inhinfo[i].inhparent = atooid(PQgetvalue(res, i, i_inhparent));
3183 destroyPQExpBuffer(query);
3190 * get information about every index on a dumpable table
3192 * Note: index data is not returned directly to the caller, but it
3193 * does get entered into the DumpableObject tables.
3196 getIndexes(TableInfo tblinfo[], int numTables)
3200 PQExpBuffer query = createPQExpBuffer();
3203 ConstraintInfo *constrinfo;
3219 for (i = 0; i < numTables; i++)
3221 TableInfo *tbinfo = &tblinfo[i];
3223 /* Only plain tables have indexes */
3224 if (tbinfo->relkind != RELKIND_RELATION || !tbinfo->hasindex)
3227 /* Ignore indexes of tables not to be dumped */
3228 if (!tbinfo->dobj.dump)
3232 write_msg(NULL, "reading indexes for table \"%s\"\n",
3235 /* Make sure we are in proper schema so indexdef is right */
3236 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
3239 * The point of the messy-looking outer join is to find a constraint
3240 * that is related by an internal dependency link to the index. If we
3241 * find one, create a CONSTRAINT entry linked to the INDEX entry. We
3242 * assume an index won't have more than one internal dependency.
3244 resetPQExpBuffer(query);
3245 if (g_fout->remoteVersion >= 80200)
3247 appendPQExpBuffer(query,
3248 "SELECT t.tableoid, t.oid, "
3249 "t.relname as indexname, "
3250 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
3251 "t.relnatts as indnkeys, "
3252 "i.indkey, i.indisclustered, "
3253 "c.contype, c.conname, "
3254 "c.tableoid as contableoid, "
3256 "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) as tablespace, "
3257 "array_to_string(t.reloptions, ', ') as options "
3258 "FROM pg_catalog.pg_index i "
3259 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
3260 "LEFT JOIN pg_catalog.pg_depend d "
3261 "ON (d.classid = t.tableoid "
3262 "AND d.objid = t.oid "
3263 "AND d.deptype = 'i') "
3264 "LEFT JOIN pg_catalog.pg_constraint c "
3265 "ON (d.refclassid = c.tableoid "
3266 "AND d.refobjid = c.oid) "
3267 "WHERE i.indrelid = '%u'::pg_catalog.oid "
3268 "ORDER BY indexname",
3269 tbinfo->dobj.catId.oid);
3271 else if (g_fout->remoteVersion >= 80000)
3273 appendPQExpBuffer(query,
3274 "SELECT t.tableoid, t.oid, "
3275 "t.relname as indexname, "
3276 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
3277 "t.relnatts as indnkeys, "
3278 "i.indkey, i.indisclustered, "
3279 "c.contype, c.conname, "
3280 "c.tableoid as contableoid, "
3282 "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) as tablespace, "
3284 "FROM pg_catalog.pg_index i "
3285 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
3286 "LEFT JOIN pg_catalog.pg_depend d "
3287 "ON (d.classid = t.tableoid "
3288 "AND d.objid = t.oid "
3289 "AND d.deptype = 'i') "
3290 "LEFT JOIN pg_catalog.pg_constraint c "
3291 "ON (d.refclassid = c.tableoid "
3292 "AND d.refobjid = c.oid) "
3293 "WHERE i.indrelid = '%u'::pg_catalog.oid "
3294 "ORDER BY indexname",
3295 tbinfo->dobj.catId.oid);
3297 else if (g_fout->remoteVersion >= 70300)
3299 appendPQExpBuffer(query,
3300 "SELECT t.tableoid, t.oid, "
3301 "t.relname as indexname, "
3302 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
3303 "t.relnatts as indnkeys, "
3304 "i.indkey, i.indisclustered, "
3305 "c.contype, c.conname, "
3306 "c.tableoid as contableoid, "
3308 "NULL as tablespace, "
3310 "FROM pg_catalog.pg_index i "
3311 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
3312 "LEFT JOIN pg_catalog.pg_depend d "
3313 "ON (d.classid = t.tableoid "
3314 "AND d.objid = t.oid "
3315 "AND d.deptype = 'i') "
3316 "LEFT JOIN pg_catalog.pg_constraint c "
3317 "ON (d.refclassid = c.tableoid "
3318 "AND d.refobjid = c.oid) "
3319 "WHERE i.indrelid = '%u'::pg_catalog.oid "
3320 "ORDER BY indexname",
3321 tbinfo->dobj.catId.oid);
3323 else if (g_fout->remoteVersion >= 70100)
3325 appendPQExpBuffer(query,
3326 "SELECT t.tableoid, t.oid, "
3327 "t.relname as indexname, "
3328 "pg_get_indexdef(i.indexrelid) as indexdef, "
3329 "t.relnatts as indnkeys, "
3330 "i.indkey, false as indisclustered, "
3331 "CASE WHEN i.indisprimary THEN 'p'::char "
3332 "ELSE '0'::char END as contype, "
3333 "t.relname as conname, "
3334 "0::oid as contableoid, "
3336 "NULL as tablespace, "
3338 "FROM pg_index i, pg_class t "
3339 "WHERE t.oid = i.indexrelid "
3340 "AND i.indrelid = '%u'::oid "
3341 "ORDER BY indexname",
3342 tbinfo->dobj.catId.oid);
3346 appendPQExpBuffer(query,
3348 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
3350 "t.relname as indexname, "
3351 "pg_get_indexdef(i.indexrelid) as indexdef, "
3352 "t.relnatts as indnkeys, "
3353 "i.indkey, false as indisclustered, "
3354 "CASE WHEN i.indisprimary THEN 'p'::char "
3355 "ELSE '0'::char END as contype, "
3356 "t.relname as conname, "
3357 "0::oid as contableoid, "
3359 "NULL as tablespace, "
3361 "FROM pg_index i, pg_class t "
3362 "WHERE t.oid = i.indexrelid "
3363 "AND i.indrelid = '%u'::oid "
3364 "ORDER BY indexname",
3365 tbinfo->dobj.catId.oid);
3368 res = PQexec(g_conn, query->data);
3369 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3371 ntups = PQntuples(res);
3373 i_tableoid = PQfnumber(res, "tableoid");
3374 i_oid = PQfnumber(res, "oid");
3375 i_indexname = PQfnumber(res, "indexname");
3376 i_indexdef = PQfnumber(res, "indexdef");
3377 i_indnkeys = PQfnumber(res, "indnkeys");
3378 i_indkey = PQfnumber(res, "indkey");
3379 i_indisclustered = PQfnumber(res, "indisclustered");
3380 i_contype = PQfnumber(res, "contype");
3381 i_conname = PQfnumber(res, "conname");
3382 i_contableoid = PQfnumber(res, "contableoid");
3383 i_conoid = PQfnumber(res, "conoid");
3384 i_tablespace = PQfnumber(res, "tablespace");
3385 i_options = PQfnumber(res, "options");
3387 indxinfo = (IndxInfo *) malloc(ntups * sizeof(IndxInfo));
3388 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
3390 for (j = 0; j < ntups; j++)
3394 indxinfo[j].dobj.objType = DO_INDEX;
3395 indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
3396 indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
3397 AssignDumpId(&indxinfo[j].dobj);
3398 indxinfo[j].dobj.name = strdup(PQgetvalue(res, j, i_indexname));
3399 indxinfo[j].dobj.namespace = tbinfo->dobj.namespace;
3400 indxinfo[j].indextable = tbinfo;
3401 indxinfo[j].indexdef = strdup(PQgetvalue(res, j, i_indexdef));
3402 indxinfo[j].indnkeys = atoi(PQgetvalue(res, j, i_indnkeys));
3403 indxinfo[j].tablespace = strdup(PQgetvalue(res, j, i_tablespace));
3404 indxinfo[j].options = strdup(PQgetvalue(res, j, i_options));
3407 * In pre-7.4 releases, indkeys may contain more entries than
3408 * indnkeys says (since indnkeys will be 1 for a functional
3409 * index). We don't actually care about this case since we don't
3410 * examine indkeys except for indexes associated with PRIMARY and
3411 * UNIQUE constraints, which are never functional indexes. But we
3412 * have to allocate enough space to keep parseOidArray from
3415 indxinfo[j].indkeys = (Oid *) malloc(INDEX_MAX_KEYS * sizeof(Oid));
3416 parseOidArray(PQgetvalue(res, j, i_indkey),
3417 indxinfo[j].indkeys, INDEX_MAX_KEYS);
3418 indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
3419 contype = *(PQgetvalue(res, j, i_contype));
3421 if (contype == 'p' || contype == 'u')
3424 * If we found a constraint matching the index, create an
3427 * In a pre-7.3 database, we take this path iff the index was
3428 * marked indisprimary.
3430 constrinfo[j].dobj.objType = DO_CONSTRAINT;
3431 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
3432 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
3433 AssignDumpId(&constrinfo[j].dobj);
3434 constrinfo[j].dobj.name = strdup(PQgetvalue(res, j, i_conname));
3435 constrinfo[j].dobj.namespace = tbinfo->dobj.namespace;
3436 constrinfo[j].contable = tbinfo;
3437 constrinfo[j].condomain = NULL;
3438 constrinfo[j].contype = contype;
3439 constrinfo[j].condef = NULL;
3440 constrinfo[j].conindex = indxinfo[j].dobj.dumpId;
3441 constrinfo[j].coninherited = false;
3442 constrinfo[j].separate = true;
3444 indxinfo[j].indexconstraint = constrinfo[j].dobj.dumpId;
3446 /* If pre-7.3 DB, better make sure table comes first */
3447 addObjectDependency(&constrinfo[j].dobj,
3448 tbinfo->dobj.dumpId);
3452 /* Plain secondary index */
3453 indxinfo[j].indexconstraint = 0;
3460 destroyPQExpBuffer(query);
3466 * Get info about constraints on dumpable tables.
3468 * Currently handles foreign keys only.
3469 * Unique and primary key constraints are handled with indexes,
3470 * while check constraints are processed in getTableAttrs().
3473 getConstraints(TableInfo tblinfo[], int numTables)
3477 ConstraintInfo *constrinfo;
3486 /* pg_constraint was created in 7.3, so nothing to do if older */
3487 if (g_fout->remoteVersion < 70300)
3490 query = createPQExpBuffer();
3492 for (i = 0; i < numTables; i++)
3494 TableInfo *tbinfo = &tblinfo[i];
3496 if (tbinfo->ntrig == 0 || !tbinfo->dobj.dump)
3500 write_msg(NULL, "reading foreign key constraints for table \"%s\"\n",
3504 * select table schema to ensure constraint expr is qualified if
3507 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
3509 resetPQExpBuffer(query);
3510 appendPQExpBuffer(query,
3511 "SELECT tableoid, oid, conname, "
3512 "pg_catalog.pg_get_constraintdef(oid) as condef "
3513 "FROM pg_catalog.pg_constraint "
3514 "WHERE conrelid = '%u'::pg_catalog.oid "
3515 "AND contype = 'f'",
3516 tbinfo->dobj.catId.oid);
3517 res = PQexec(g_conn, query->data);
3518 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3520 ntups = PQntuples(res);
3522 i_contableoid = PQfnumber(res, "tableoid");
3523 i_conoid = PQfnumber(res, "oid");
3524 i_conname = PQfnumber(res, "conname");
3525 i_condef = PQfnumber(res, "condef");
3527 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
3529 for (j = 0; j < ntups; j++)
3531 constrinfo[j].dobj.objType = DO_FK_CONSTRAINT;
3532 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
3533 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
3534 AssignDumpId(&constrinfo[j].dobj);
3535 constrinfo[j].dobj.name = strdup(PQgetvalue(res, j, i_conname));
3536 constrinfo[j].dobj.namespace = tbinfo->dobj.namespace;
3537 constrinfo[j].contable = tbinfo;
3538 constrinfo[j].condomain = NULL;
3539 constrinfo[j].contype = 'f';
3540 constrinfo[j].condef = strdup(PQgetvalue(res, j, i_condef));
3541 constrinfo[j].conindex = 0;
3542 constrinfo[j].coninherited = false;
3543 constrinfo[j].separate = true;
3549 destroyPQExpBuffer(query);
3553 * getDomainConstraints
3555 * Get info about constraints on a domain.
3558 getDomainConstraints(TypeInfo *tinfo)
3561 ConstraintInfo *constrinfo;
3570 /* pg_constraint was created in 7.3, so nothing to do if older */
3571 if (g_fout->remoteVersion < 70300)
3575 * select appropriate schema to ensure names in constraint are properly
3578 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
3580 query = createPQExpBuffer();
3582 if (g_fout->remoteVersion >= 70400)
3583 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
3584 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
3585 "FROM pg_catalog.pg_constraint "
3586 "WHERE contypid = '%u'::pg_catalog.oid "
3588 tinfo->dobj.catId.oid);
3590 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
3591 "'CHECK (' || consrc || ')' AS consrc "
3592 "FROM pg_catalog.pg_constraint "
3593 "WHERE contypid = '%u'::pg_catalog.oid "
3595 tinfo->dobj.catId.oid);
3597 res = PQexec(g_conn, query->data);
3598 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3600 ntups = PQntuples(res);
3602 i_tableoid = PQfnumber(res, "tableoid");
3603 i_oid = PQfnumber(res, "oid");
3604 i_conname = PQfnumber(res, "conname");
3605 i_consrc = PQfnumber(res, "consrc");
3607 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
3609 tinfo->nDomChecks = ntups;
3610 tinfo->domChecks = constrinfo;
3612 for (i = 0; i < ntups; i++)
3614 constrinfo[i].dobj.objType = DO_CONSTRAINT;
3615 constrinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3616 constrinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3617 AssignDumpId(&constrinfo[i].dobj);
3618 constrinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_conname));
3619 constrinfo[i].dobj.namespace = tinfo->dobj.namespace;
3620 constrinfo[i].contable = NULL;
3621 constrinfo[i].condomain = tinfo;
3622 constrinfo[i].contype = 'c';
3623 constrinfo[i].condef = strdup(PQgetvalue(res, i, i_consrc));
3624 constrinfo[i].conindex = 0;
3625 constrinfo[i].coninherited = false;
3626 constrinfo[i].separate = false;
3629 * Make the domain depend on the constraint, ensuring it won't be
3630 * output till any constraint dependencies are OK.
3632 addObjectDependency(&tinfo->dobj,
3633 constrinfo[i].dobj.dumpId);
3638 destroyPQExpBuffer(query);
3643 * get basic information about every rule in the system
3645 * numRules is set to the number of rules read in
3648 getRules(int *numRules)
3653 PQExpBuffer query = createPQExpBuffer();
3662 /* Make sure we are in proper schema */
3663 selectSourceSchema("pg_catalog");
3665 if (g_fout->remoteVersion >= 70100)
3667 appendPQExpBuffer(query, "SELECT "
3668 "tableoid, oid, rulename, "
3669 "ev_class as ruletable, ev_type, is_instead "
3675 appendPQExpBuffer(query, "SELECT "
3676 "(SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') AS tableoid, "
3678 "ev_class as ruletable, ev_type, is_instead "
3683 res = PQexec(g_conn, query->data);
3684 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3686 ntups = PQntuples(res);
3690 ruleinfo = (RuleInfo *) malloc(ntups * sizeof(RuleInfo));
3692 i_tableoid = PQfnumber(res, "tableoid");
3693 i_oid = PQfnumber(res, "oid");
3694 i_rulename = PQfnumber(res, "rulename");
3695 i_ruletable = PQfnumber(res, "ruletable");
3696 i_ev_type = PQfnumber(res, "ev_type");
3697 i_is_instead = PQfnumber(res, "is_instead");
3699 for (i = 0; i < ntups; i++)
3703 ruleinfo[i].dobj.objType = DO_RULE;
3704 ruleinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3705 ruleinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3706 AssignDumpId(&ruleinfo[i].dobj);
3707 ruleinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_rulename));
3708 ruletableoid = atooid(PQgetvalue(res, i, i_ruletable));
3709 ruleinfo[i].ruletable = findTableByOid(ruletableoid);
3710 if (ruleinfo[i].ruletable == NULL)
3712 write_msg(NULL, "failed sanity check, parent table OID %u of pg_rewrite entry OID %u not found\n",
3714 ruleinfo[i].dobj.catId.oid);
3717 ruleinfo[i].dobj.namespace = ruleinfo[i].ruletable->dobj.namespace;
3718 ruleinfo[i].dobj.dump = ruleinfo[i].ruletable->dobj.dump;
3719 ruleinfo[i].ev_type = *(PQgetvalue(res, i, i_ev_type));
3720 ruleinfo[i].is_instead = *(PQgetvalue(res, i, i_is_instead)) == 't';
3721 if (ruleinfo[i].ruletable)
3724 * If the table is a view, force its ON SELECT rule to be sorted
3725 * before the view itself --- this ensures that any dependencies
3726 * for the rule affect the table's positioning. Other rules are
3727 * forced to appear after their table.
3729 if (ruleinfo[i].ruletable->relkind == RELKIND_VIEW &&
3730 ruleinfo[i].ev_type == '1' && ruleinfo[i].is_instead)
3732 addObjectDependency(&ruleinfo[i].ruletable->dobj,
3733 ruleinfo[i].dobj.dumpId);
3734 /* We'll merge the rule into CREATE VIEW, if possible */
3735 ruleinfo[i].separate = false;
3739 addObjectDependency(&ruleinfo[i].dobj,
3740 ruleinfo[i].ruletable->dobj.dumpId);
3741 ruleinfo[i].separate = true;
3745 ruleinfo[i].separate = true;
3750 destroyPQExpBuffer(query);
3757 * get information about every trigger on a dumpable table
3759 * Note: trigger data is not returned directly to the caller, but it
3760 * does get entered into the DumpableObject tables.
3763 getTriggers(TableInfo tblinfo[], int numTables)
3767 PQExpBuffer query = createPQExpBuffer();
3769 TriggerInfo *tginfo;
3786 for (i = 0; i < numTables; i++)
3788 TableInfo *tbinfo = &tblinfo[i];
3790 if (tbinfo->ntrig == 0 || !tbinfo->dobj.dump)
3794 write_msg(NULL, "reading triggers for table \"%s\"\n",
3798 * select table schema to ensure regproc name is qualified if needed
3800 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
3802 resetPQExpBuffer(query);
3803 if (g_fout->remoteVersion >= 70300)
3806 * We ignore triggers that are tied to a foreign-key constraint
3808 appendPQExpBuffer(query,
3810 "tgfoid::pg_catalog.regproc as tgfname, "
3811 "tgtype, tgnargs, tgargs, tgenabled, "
3812 "tgisconstraint, tgconstrname, tgdeferrable, "
3813 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3814 "tgconstrrelid::pg_catalog.regclass as tgconstrrelname "
3815 "from pg_catalog.pg_trigger t "
3816 "where tgrelid = '%u'::pg_catalog.oid "
3817 "and (not tgisconstraint "
3819 " (SELECT 1 FROM pg_catalog.pg_depend d "
3820 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
3821 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))",
3822 tbinfo->dobj.catId.oid);
3824 else if (g_fout->remoteVersion >= 70100)
3826 appendPQExpBuffer(query,
3827 "SELECT tgname, tgfoid::regproc as tgfname, "
3828 "tgtype, tgnargs, tgargs, tgenabled, "
3829 "tgisconstraint, tgconstrname, tgdeferrable, "
3830 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3831 "(select relname from pg_class where oid = tgconstrrelid) "
3832 " as tgconstrrelname "
3834 "where tgrelid = '%u'::oid",
3835 tbinfo->dobj.catId.oid);
3839 appendPQExpBuffer(query,
3840 "SELECT tgname, tgfoid::regproc as tgfname, "
3841 "tgtype, tgnargs, tgargs, tgenabled, "
3842 "tgisconstraint, tgconstrname, tgdeferrable, "
3843 "tgconstrrelid, tginitdeferred, "
3844 "(SELECT oid FROM pg_class WHERE relname = 'pg_trigger') AS tableoid, "
3847 "(select relname from pg_class where oid = tgconstrrelid) "
3848 " as tgconstrrelname "
3850 "where tgrelid = '%u'::oid",
3851 tbinfo->dobj.catId.oid);
3853 res = PQexec(g_conn, query->data);
3854 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3856 ntups = PQntuples(res);
3859 * We may have less triggers than recorded due to having ignored
3860 * foreign-key triggers
3862 if (ntups > tbinfo->ntrig)
3864 write_msg(NULL, "expected %d triggers on table \"%s\" but found %d\n",
3865 tbinfo->ntrig, tbinfo->dobj.name, ntups);
3868 i_tableoid = PQfnumber(res, "tableoid");
3869 i_oid = PQfnumber(res, "oid");
3870 i_tgname = PQfnumber(res, "tgname");
3871 i_tgfname = PQfnumber(res, "tgfname");
3872 i_tgtype = PQfnumber(res, "tgtype");
3873 i_tgnargs = PQfnumber(res, "tgnargs");
3874 i_tgargs = PQfnumber(res, "tgargs");
3875 i_tgisconstraint = PQfnumber(res, "tgisconstraint");
3876 i_tgconstrname = PQfnumber(res, "tgconstrname");
3877 i_tgconstrrelid = PQfnumber(res, "tgconstrrelid");
3878 i_tgconstrrelname = PQfnumber(res, "tgconstrrelname");
3879 i_tgenabled = PQfnumber(res, "tgenabled");
3880 i_tgdeferrable = PQfnumber(res, "tgdeferrable");
3881 i_tginitdeferred = PQfnumber(res, "tginitdeferred");
3883 tginfo = (TriggerInfo *) malloc(ntups * sizeof(TriggerInfo));
3885 for (j = 0; j < ntups; j++)
3887 tginfo[j].dobj.objType = DO_TRIGGER;
3888 tginfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
3889 tginfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
3890 AssignDumpId(&tginfo[j].dobj);
3891 tginfo[j].dobj.name = strdup(PQgetvalue(res, j, i_tgname));
3892 tginfo[j].dobj.namespace = tbinfo->dobj.namespace;
3893 tginfo[j].tgtable = tbinfo;
3894 tginfo[j].tgfname = strdup(PQgetvalue(res, j, i_tgfname));
3895 tginfo[j].tgtype = atoi(PQgetvalue(res, j, i_tgtype));
3896 tginfo[j].tgnargs = atoi(PQgetvalue(res, j, i_tgnargs));
3897 tginfo[j].tgargs = strdup(PQgetvalue(res, j, i_tgargs));
3898 tginfo[j].tgisconstraint = *(PQgetvalue(res, j, i_tgisconstraint)) == 't';
3899 tginfo[j].tgenabled = *(PQgetvalue(res, j, i_tgenabled)) == 't';
3900 tginfo[j].tgdeferrable = *(PQgetvalue(res, j, i_tgdeferrable)) == 't';
3901 tginfo[j].tginitdeferred = *(PQgetvalue(res, j, i_tginitdeferred)) == 't';
3903 if (tginfo[j].tgisconstraint)
3905 tginfo[j].tgconstrname = strdup(PQgetvalue(res, j, i_tgconstrname));
3906 tginfo[j].tgconstrrelid = atooid(PQgetvalue(res, j, i_tgconstrrelid));
3907 if (OidIsValid(tginfo[j].tgconstrrelid))
3909 if (PQgetisnull(res, j, i_tgconstrrelname))
3911 write_msg(NULL, "query produced null referenced table name for foreign key trigger \"%s\" on table \"%s\" (OID of table: %u)\n",
3912 tginfo[j].dobj.name, tbinfo->dobj.name,
3913 tginfo[j].tgconstrrelid);
3916 tginfo[j].tgconstrrelname = strdup(PQgetvalue(res, j, i_tgconstrrelname));
3919 tginfo[j].tgconstrrelname = NULL;
3923 tginfo[j].tgconstrname = NULL;
3924 tginfo[j].tgconstrrelid = InvalidOid;
3925 tginfo[j].tgconstrrelname = NULL;
3932 destroyPQExpBuffer(query);
3937 * get basic information about every procedural language in the system
3939 * numProcLangs is set to the number of langs read in
3941 * NB: this must run after getFuncs() because we assume we can do
3945 getProcLangs(int *numProcLangs)
3950 PQExpBuffer query = createPQExpBuffer();
3951 ProcLangInfo *planginfo;
3956 int i_lanplcallfoid;
3961 /* Make sure we are in proper schema */
3962 selectSourceSchema("pg_catalog");
3964 if (g_fout->remoteVersion >= 80100)
3966 /* Languages are owned by the bootstrap superuser, OID 10 */
3967 appendPQExpBuffer(query, "SELECT tableoid, oid, *, "
3968 "(%s '10') as lanowner "
3974 else if (g_fout->remoteVersion >= 70400)
3976 /* Languages are owned by the bootstrap superuser, sysid 1 */
3977 appendPQExpBuffer(query, "SELECT tableoid, oid, *, "
3978 "(%s '1') as lanowner "
3984 else if (g_fout->remoteVersion >= 70100)
3986 /* No clear notion of an owner at all before 7.4 ... */
3987 appendPQExpBuffer(query, "SELECT tableoid, oid, * FROM pg_language "
3993 appendPQExpBuffer(query, "SELECT "
3994 "(SELECT oid FROM pg_class WHERE relname = 'pg_language') AS tableoid, "
3995 "oid, * FROM pg_language "
4000 res = PQexec(g_conn, query->data);
4001 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4003 ntups = PQntuples(res);
4005 *numProcLangs = ntups;
4007 planginfo = (ProcLangInfo *) malloc(ntups * sizeof(ProcLangInfo));
4009 i_tableoid = PQfnumber(res, "tableoid");
4010 i_oid = PQfnumber(res, "oid");
4011 i_lanname = PQfnumber(res, "lanname");
4012 i_lanpltrusted = PQfnumber(res, "lanpltrusted");
4013 i_lanplcallfoid = PQfnumber(res, "lanplcallfoid");
4014 /* these may fail and return -1: */
4015 i_lanvalidator = PQfnumber(res, "lanvalidator");
4016 i_lanacl = PQfnumber(res, "lanacl");
4017 i_lanowner = PQfnumber(res, "lanowner");
4019 for (i = 0; i < ntups; i++)
4021 planginfo[i].dobj.objType = DO_PROCLANG;
4022 planginfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
4023 planginfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
4024 AssignDumpId(&planginfo[i].dobj);
4026 planginfo[i].dobj.name = strdup(PQgetvalue(res, i, i_lanname));
4027 planginfo[i].lanpltrusted = *(PQgetvalue(res, i, i_lanpltrusted)) == 't';
4028 planginfo[i].lanplcallfoid = atooid(PQgetvalue(res, i, i_lanplcallfoid));
4029 if (i_lanvalidator >= 0)
4030 planginfo[i].lanvalidator = atooid(PQgetvalue(res, i, i_lanvalidator));
4032 planginfo[i].lanvalidator = InvalidOid;
4034 planginfo[i].lanacl = strdup(PQgetvalue(res, i, i_lanacl));
4036 planginfo[i].lanacl = strdup("{=U}");
4037 if (i_lanowner >= 0)
4038 planginfo[i].lanowner = strdup(PQgetvalue(res, i, i_lanowner));
4040 planginfo[i].lanowner = strdup("");
4042 if (g_fout->remoteVersion < 70300)
4045 * We need to make a dependency to ensure the function will be
4046 * dumped first. (In 7.3 and later the regular dependency
4047 * mechanism will handle this for us.)
4049 FuncInfo *funcInfo = findFuncByOid(planginfo[i].lanplcallfoid);
4052 addObjectDependency(&planginfo[i].dobj,
4053 funcInfo->dobj.dumpId);
4059 destroyPQExpBuffer(query);
4066 * get basic information about every cast in the system
4068 * numCasts is set to the number of casts read in
4071 getCasts(int *numCasts)
4076 PQExpBuffer query = createPQExpBuffer();
4085 /* Make sure we are in proper schema */
4086 selectSourceSchema("pg_catalog");
4088 if (g_fout->remoteVersion >= 70300)
4090 appendPQExpBuffer(query, "SELECT tableoid, oid, "
4091 "castsource, casttarget, castfunc, castcontext "
4092 "FROM pg_cast ORDER BY 3,4");
4096 appendPQExpBuffer(query, "SELECT 0 as tableoid, p.oid, "
4097 "t1.oid as castsource, t2.oid as casttarget, "
4098 "p.oid as castfunc, 'e' as castcontext "
4099 "FROM pg_type t1, pg_type t2, pg_proc p "
4100 "WHERE p.pronargs = 1 AND "
4101 "p.proargtypes[0] = t1.oid AND "
4102 "p.prorettype = t2.oid AND p.proname = t2.typname "
4106 res = PQexec(g_conn, query->data);
4107 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4109 ntups = PQntuples(res);
4113 castinfo = (CastInfo *) malloc(ntups * sizeof(CastInfo));
4115 i_tableoid = PQfnumber(res, "tableoid");
4116 i_oid = PQfnumber(res, "oid");
4117 i_castsource = PQfnumber(res, "castsource");
4118 i_casttarget = PQfnumber(res, "casttarget");
4119 i_castfunc = PQfnumber(res, "castfunc");
4120 i_castcontext = PQfnumber(res, "castcontext");
4122 for (i = 0; i < ntups; i++)
4124 PQExpBufferData namebuf;
4125 TypeInfo *sTypeInfo;
4126 TypeInfo *tTypeInfo;
4128 castinfo[i].dobj.objType = DO_CAST;
4129 castinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
4130 castinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
4131 AssignDumpId(&castinfo[i].dobj);
4132 castinfo[i].castsource = atooid(PQgetvalue(res, i, i_castsource));
4133 castinfo[i].casttarget = atooid(PQgetvalue(res, i, i_casttarget));
4134 castinfo[i].castfunc = atooid(PQgetvalue(res, i, i_castfunc));
4135 castinfo[i].castcontext = *(PQgetvalue(res, i, i_castcontext));
4138 * Try to name cast as concatenation of typnames. This is only used
4139 * for purposes of sorting. If we fail to find either type, the name
4140 * will be an empty string.
4142 initPQExpBuffer(&namebuf);
4143 sTypeInfo = findTypeByOid(castinfo[i].castsource);
4144 tTypeInfo = findTypeByOid(castinfo[i].casttarget);
4145 if (sTypeInfo && tTypeInfo)
4146 appendPQExpBuffer(&namebuf, "%s %s",
4147 sTypeInfo->dobj.name, tTypeInfo->dobj.name);
4148 castinfo[i].dobj.name = namebuf.data;
4150 if (OidIsValid(castinfo[i].castfunc))
4153 * We need to make a dependency to ensure the function will be
4154 * dumped first. (In 7.3 and later the regular dependency
4155 * mechanism will handle this for us.)
4159 funcInfo = findFuncByOid(castinfo[i].castfunc);
4161 addObjectDependency(&castinfo[i].dobj,
4162 funcInfo->dobj.dumpId);
4168 destroyPQExpBuffer(query);
4175 * for each interesting table, read info about its attributes
4176 * (names, types, default values, CHECK constraints, etc)
4178 * This is implemented in a very inefficient way right now, looping
4179 * through the tblinfo and doing a join per table to find the attrs and their
4180 * types. However, because we want type names and so forth to be named
4181 * relative to the schema of each table, we couldn't do it in just one
4182 * query. (Maybe one query per schema?)
4187 getTableAttrs(TableInfo *tblinfo, int numTables)
4191 PQExpBuffer q = createPQExpBuffer();
4196 int i_attstattarget;
4207 for (i = 0; i < numTables; i++)
4209 TableInfo *tbinfo = &tblinfo[i];
4211 /* Don't bother to collect info for sequences */
4212 if (tbinfo->relkind == RELKIND_SEQUENCE)
4215 /* Don't bother with uninteresting tables, either */
4216 if (!tbinfo->interesting)
4220 * Make sure we are in proper schema for this table; this allows
4221 * correct retrieval of formatted type names and default exprs
4223 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
4225 /* find all the user attributes and their types */
4228 * we must read the attribute names in attribute number order! because
4229 * we will use the attnum to index into the attnames array later. We
4230 * actually ask to order by "attrelid, attnum" because (at least up to
4231 * 7.3) the planner is not smart enough to realize it needn't re-sort
4232 * the output of an indexscan on pg_attribute_relid_attnum_index.
4235 write_msg(NULL, "finding the columns and types of table \"%s\"\n",
4238 resetPQExpBuffer(q);
4240 if (g_fout->remoteVersion >= 70300)
4242 /* need left join here to not fail on dropped columns ... */
4243 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, "
4244 "a.attnotnull, a.atthasdef, a.attisdropped, a.attislocal, "
4245 "pg_catalog.format_type(t.oid,a.atttypmod) as atttypname "
4246 "from pg_catalog.pg_attribute a left join pg_catalog.pg_type t "
4247 "on a.atttypid = t.oid "
4248 "where a.attrelid = '%u'::pg_catalog.oid "
4249 "and a.attnum > 0::pg_catalog.int2 "
4250 "order by a.attrelid, a.attnum",
4251 tbinfo->dobj.catId.oid);
4253 else if (g_fout->remoteVersion >= 70100)
4256 * attstattarget doesn't exist in 7.1. It does exist in 7.2, but
4257 * we don't dump it because we can't tell whether it's been
4258 * explicitly set or was just a default.
4260 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, -1 as attstattarget, a.attstorage, t.typstorage, "
4261 "a.attnotnull, a.atthasdef, false as attisdropped, false as attislocal, "
4262 "format_type(t.oid,a.atttypmod) as atttypname "
4263 "from pg_attribute a left join pg_type t "
4264 "on a.atttypid = t.oid "
4265 "where a.attrelid = '%u'::oid "
4266 "and a.attnum > 0::int2 "
4267 "order by a.attrelid, a.attnum",
4268 tbinfo->dobj.catId.oid);
4272 /* format_type not available before 7.1 */
4273 appendPQExpBuffer(q, "SELECT attnum, attname, atttypmod, -1 as attstattarget, attstorage, attstorage as typstorage, "
4274 "attnotnull, atthasdef, false as attisdropped, false as attislocal, "
4275 "(select typname from pg_type where oid = atttypid) as atttypname "
4276 "from pg_attribute a "
4277 "where attrelid = '%u'::oid "
4278 "and attnum > 0::int2 "
4279 "order by attrelid, attnum",
4280 tbinfo->dobj.catId.oid);
4283 res = PQexec(g_conn, q->data);
4284 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
4286 ntups = PQntuples(res);
4288 i_attnum = PQfnumber(res, "attnum");
4289 i_attname = PQfnumber(res, "attname");
4290 i_atttypname = PQfnumber(res, "atttypname");
4291 i_atttypmod = PQfnumber(res, "atttypmod");
4292 i_attstattarget = PQfnumber(res, "attstattarget");
4293 i_attstorage = PQfnumber(res, "attstorage");
4294 i_typstorage = PQfnumber(res, "typstorage");
4295 i_attnotnull = PQfnumber(res, "attnotnull");
4296 i_atthasdef = PQfnumber(res, "atthasdef");
4297 i_attisdropped = PQfnumber(res, "attisdropped");
4298 i_attislocal = PQfnumber(res, "attislocal");
4300 tbinfo->numatts = ntups;
4301 tbinfo->attnames = (char **) malloc(ntups * sizeof(char *));
4302 tbinfo->atttypnames = (char **) malloc(ntups * sizeof(char *));
4303 tbinfo->atttypmod = (int *) malloc(ntups * sizeof(int));
4304 tbinfo->attstattarget = (int *) malloc(ntups * sizeof(int));
4305 tbinfo->attstorage = (char *) malloc(ntups * sizeof(char));
4306 tbinfo->typstorage = (char *) malloc(ntups * sizeof(char));
4307 tbinfo->attisdropped = (bool *) malloc(ntups * sizeof(bool));
4308 tbinfo->attislocal = (bool *) malloc(ntups * sizeof(bool));
4309 tbinfo->notnull = (bool *) malloc(ntups * sizeof(bool));
4310 tbinfo->attrdefs = (AttrDefInfo **) malloc(ntups * sizeof(AttrDefInfo *));
4311 tbinfo->inhAttrs = (bool *) malloc(ntups * sizeof(bool));
4312 tbinfo->inhAttrDef = (bool *) malloc(ntups * sizeof(bool));
4313 tbinfo->inhNotNull = (bool *) malloc(ntups * sizeof(bool));
4314 hasdefaults = false;
4316 for (j = 0; j < ntups; j++)
4318 if (j + 1 != atoi(PQgetvalue(res, j, i_attnum)))
4320 write_msg(NULL, "invalid column numbering in table \"%s\"\n",
4324 tbinfo->attnames[j] = strdup(PQgetvalue(res, j, i_attname));
4325 tbinfo->atttypnames[j] = strdup(PQgetvalue(res, j, i_atttypname));
4326 tbinfo->atttypmod[j] = atoi(PQgetvalue(res, j, i_atttypmod));
4327 tbinfo->attstattarget[j] = atoi(PQgetvalue(res, j, i_attstattarget));
4328 tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
4329 tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
4330 tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
4331 tbinfo->attislocal[j] = (PQgetvalue(res, j, i_attislocal)[0] == 't');
4332 tbinfo->notnull[j] = (PQgetvalue(res, j, i_attnotnull)[0] == 't');
4333 tbinfo->attrdefs[j] = NULL; /* fix below */
4334 if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
4336 /* these flags will be set in flagInhAttrs() */
4337 tbinfo->inhAttrs[j] = false;
4338 tbinfo->inhAttrDef[j] = false;
4339 tbinfo->inhNotNull[j] = false;
4345 * Get info about column defaults
4349 AttrDefInfo *attrdefs;
4353 write_msg(NULL, "finding default expressions of table \"%s\"\n",
4356 resetPQExpBuffer(q);
4357 if (g_fout->remoteVersion >= 70300)
4359 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
4360 "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
4361 "FROM pg_catalog.pg_attrdef "
4362 "WHERE adrelid = '%u'::pg_catalog.oid",
4363 tbinfo->dobj.catId.oid);
4365 else if (g_fout->remoteVersion >= 70200)
4367 /* 7.2 did not have OIDs in pg_attrdef */
4368 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, "
4369 "pg_get_expr(adbin, adrelid) AS adsrc "
4371 "WHERE adrelid = '%u'::oid",
4372 tbinfo->dobj.catId.oid);
4374 else if (g_fout->remoteVersion >= 70100)
4376 /* no pg_get_expr, so must rely on adsrc */
4377 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc "
4379 "WHERE adrelid = '%u'::oid",
4380 tbinfo->dobj.catId.oid);
4384 /* no pg_get_expr, no tableoid either */
4385 appendPQExpBuffer(q, "SELECT "
4386 "(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, "
4387 "oid, adnum, adsrc "
4389 "WHERE adrelid = '%u'::oid",
4390 tbinfo->dobj.catId.oid);
4392 res = PQexec(g_conn, q->data);
4393 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
4395 numDefaults = PQntuples(res);
4396 attrdefs = (AttrDefInfo *) malloc(numDefaults * sizeof(AttrDefInfo));
4398 for (j = 0; j < numDefaults; j++)
4402 attrdefs[j].dobj.objType = DO_ATTRDEF;
4403 attrdefs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
4404 attrdefs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
4405 AssignDumpId(&attrdefs[j].dobj);
4406 attrdefs[j].adtable = tbinfo;
4407 attrdefs[j].adnum = adnum = atoi(PQgetvalue(res, j, 2));
4408 attrdefs[j].adef_expr = strdup(PQgetvalue(res, j, 3));
4410 attrdefs[j].dobj.name = strdup(tbinfo->dobj.name);
4411 attrdefs[j].dobj.namespace = tbinfo->dobj.namespace;
4413 attrdefs[j].dobj.dump = tbinfo->dobj.dump;
4416 * Defaults on a VIEW must always be dumped as separate ALTER
4417 * TABLE commands. Defaults on regular tables are dumped as
4418 * part of the CREATE TABLE if possible. To check if it's
4419 * safe, we mark the default as needing to appear before the
4422 if (tbinfo->relkind == RELKIND_VIEW)
4424 attrdefs[j].separate = true;
4425 /* needed in case pre-7.3 DB: */
4426 addObjectDependency(&attrdefs[j].dobj,
4427 tbinfo->dobj.dumpId);
4431 attrdefs[j].separate = false;
4432 addObjectDependency(&tbinfo->dobj,
4433 attrdefs[j].dobj.dumpId);
4436 if (adnum <= 0 || adnum > ntups)
4438 write_msg(NULL, "invalid adnum value %d for table \"%s\"\n",
4439 adnum, tbinfo->dobj.name);
4442 tbinfo->attrdefs[adnum - 1] = &attrdefs[j];
4448 * Get info about table CHECK constraints
4450 if (tbinfo->ncheck > 0)
4452 ConstraintInfo *constrs;
4456 write_msg(NULL, "finding check constraints for table \"%s\"\n",
4459 resetPQExpBuffer(q);
4460 if (g_fout->remoteVersion >= 70400)
4462 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
4463 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
4464 "FROM pg_catalog.pg_constraint "
4465 "WHERE conrelid = '%u'::pg_catalog.oid "
4466 " AND contype = 'c' "
4468 tbinfo->dobj.catId.oid);
4470 else if (g_fout->remoteVersion >= 70300)
4472 /* no pg_get_constraintdef, must use consrc */
4473 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
4474 "'CHECK (' || consrc || ')' AS consrc "
4475 "FROM pg_catalog.pg_constraint "
4476 "WHERE conrelid = '%u'::pg_catalog.oid "
4477 " AND contype = 'c' "
4479 tbinfo->dobj.catId.oid);
4481 else if (g_fout->remoteVersion >= 70200)
4483 /* 7.2 did not have OIDs in pg_relcheck */
4484 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, "
4485 "rcname AS conname, "
4486 "'CHECK (' || rcsrc || ')' AS consrc "
4488 "WHERE rcrelid = '%u'::oid "
4490 tbinfo->dobj.catId.oid);
4492 else if (g_fout->remoteVersion >= 70100)
4494 appendPQExpBuffer(q, "SELECT tableoid, oid, "
4495 "rcname AS conname, "
4496 "'CHECK (' || rcsrc || ')' AS consrc "
4498 "WHERE rcrelid = '%u'::oid "
4500 tbinfo->dobj.catId.oid);
4504 /* no tableoid in 7.0 */
4505 appendPQExpBuffer(q, "SELECT "
4506 "(SELECT oid FROM pg_class WHERE relname = 'pg_relcheck') AS tableoid, "
4507 "oid, rcname AS conname, "
4508 "'CHECK (' || rcsrc || ')' AS consrc "
4510 "WHERE rcrelid = '%u'::oid "
4512 tbinfo->dobj.catId.oid);
4514 res = PQexec(g_conn, q->data);
4515 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
4517 numConstrs = PQntuples(res);
4518 if (numConstrs != tbinfo->ncheck)
4520 write_msg(NULL, "expected %d check constraints on table \"%s\" but found %d\n",
4521 tbinfo->ncheck, tbinfo->dobj.name, numConstrs);
4522 write_msg(NULL, "(The system catalogs might be corrupted.)\n");
4526 constrs = (ConstraintInfo *) malloc(numConstrs * sizeof(ConstraintInfo));
4527 tbinfo->checkexprs = constrs;
4529 for (j = 0; j < numConstrs; j++)
4531 constrs[j].dobj.objType = DO_CONSTRAINT;
4532 constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
4533 constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
4534 AssignDumpId(&constrs[j].dobj);
4535 constrs[j].dobj.name = strdup(PQgetvalue(res, j, 2));
4536 constrs[j].dobj.namespace = tbinfo->dobj.namespace;
4537 constrs[j].contable = tbinfo;
4538 constrs[j].condomain = NULL;
4539 constrs[j].contype = 'c';
4540 constrs[j].condef = strdup(PQgetvalue(res, j, 3));
4541 constrs[j].conindex = 0;
4542 constrs[j].coninherited = false;
4543 constrs[j].separate = false;
4545 constrs[j].dobj.dump = tbinfo->dobj.dump;
4548 * Mark the constraint as needing to appear before the table
4549 * --- this is so that any other dependencies of the
4550 * constraint will be emitted before we try to create the
4553 addObjectDependency(&tbinfo->dobj,
4554 constrs[j].dobj.dumpId);
4557 * If the constraint is inherited, this will be detected
4558 * later. We also detect later if the constraint must be
4559 * split out from the table definition.
4566 destroyPQExpBuffer(q);
4573 * This routine is used to dump any comments associated with the
4574 * object handed to this routine. The routine takes a constant character
4575 * string for the target part of the comment-creation command, plus
4576 * the namespace and owner of the object (for labeling the ArchiveEntry),
4577 * plus catalog ID and subid which are the lookup key for pg_description,
4578 * plus the dump ID for the object (for setting a dependency).
4579 * If a matching pg_description entry is found, it is dumped.
4581 * Note: although this routine takes a dumpId for dependency purposes,
4582 * that purpose is just to mark the dependency in the emitted dump file
4583 * for possible future use by pg_restore. We do NOT use it for determining
4584 * ordering of the comment in the dump file, because this routine is called
4585 * after dependency sorting occurs. This routine should be called just after
4586 * calling ArchiveEntry() for the specified object.
4589 dumpComment(Archive *fout, const char *target,
4590 const char *namespace, const char *owner,
4591 CatalogId catalogId, int subid, DumpId dumpId)
4593 CommentItem *comments;
4596 /* Comments are SCHEMA not data */
4600 /* Search for comments associated with catalogId, using table */
4601 ncomments = findComments(fout, catalogId.tableoid, catalogId.oid,
4604 /* Is there one matching the subid? */
4605 while (ncomments > 0)
4607 if (comments->objsubid == subid)
4613 /* If a comment exists, build COMMENT ON statement */
4616 PQExpBuffer query = createPQExpBuffer();
4618 appendPQExpBuffer(query, "COMMENT ON %s IS ", target);
4619 appendStringLiteralAH(query, comments->descr, fout);
4620 appendPQExpBuffer(query, ";\n");
4622 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4623 target, namespace, NULL, owner, false,
4624 "COMMENT", query->data, "", NULL,
4628 destroyPQExpBuffer(query);
4633 * dumpTableComment --
4635 * As above, but dump comments for both the specified table (or view)
4639 dumpTableComment(Archive *fout, TableInfo *tbinfo,
4640 const char *reltypename)
4642 CommentItem *comments;
4647 /* Comments are SCHEMA not data */
4651 /* Search for comments associated with relation, using table */
4652 ncomments = findComments(fout,
4653 tbinfo->dobj.catId.tableoid,
4654 tbinfo->dobj.catId.oid,
4657 /* If comments exist, build COMMENT ON statements */
4661 query = createPQExpBuffer();
4662 target = createPQExpBuffer();
4664 while (ncomments > 0)
4666 const char *descr = comments->descr;
4667 int objsubid = comments->objsubid;
4671 resetPQExpBuffer(target);
4672 appendPQExpBuffer(target, "%s %s", reltypename,
4673 fmtId(tbinfo->dobj.name));
4675 resetPQExpBuffer(query);
4676 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
4677 appendStringLiteralAH(query, descr, fout);
4678 appendPQExpBuffer(query, ";\n");
4680 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4682 tbinfo->dobj.namespace->dobj.name,
4685 false, "COMMENT", query->data, "", NULL,
4686 &(tbinfo->dobj.dumpId), 1,
4689 else if (objsubid > 0 && objsubid <= tbinfo->numatts)
4691 resetPQExpBuffer(target);
4692 appendPQExpBuffer(target, "COLUMN %s.",
4693 fmtId(tbinfo->dobj.name));
4694 appendPQExpBuffer(target, "%s",
4695 fmtId(tbinfo->attnames[objsubid - 1]));
4697 resetPQExpBuffer(query);
4698 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
4699 appendStringLiteralAH(query, descr, fout);
4700 appendPQExpBuffer(query, ";\n");
4702 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4704 tbinfo->dobj.namespace->dobj.name,
4707 false, "COMMENT", query->data, "", NULL,
4708 &(tbinfo->dobj.dumpId), 1,
4716 destroyPQExpBuffer(query);
4717 destroyPQExpBuffer(target);
4723 * Find the comment(s), if any, associated with the given object. All the
4724 * objsubid values associated with the given classoid/objoid are found with
4728 findComments(Archive *fout, Oid classoid, Oid objoid,
4729 CommentItem **items)
4731 /* static storage for table of comments */
4732 static CommentItem *comments = NULL;
4733 static int ncomments = -1;
4735 CommentItem *middle = NULL;
4740 /* Get comments if we didn't already */
4742 ncomments = collectComments(fout, &comments);
4745 * Pre-7.2, pg_description does not contain classoid, so collectComments
4746 * just stores a zero. If there's a collision on object OID, well, you
4747 * get duplicate comments.
4749 if (fout->remoteVersion < 70200)
4753 * Do binary search to find some item matching the object.
4756 high = &comments[ncomments - 1];
4759 middle = low + (high - low) / 2;
4761 if (classoid < middle->classoid)
4763 else if (classoid > middle->classoid)
4765 else if (objoid < middle->objoid)
4767 else if (objoid > middle->objoid)
4770 break; /* found a match */
4773 if (low > high) /* no matches */
4780 * Now determine how many items match the object. The search loop
4781 * invariant still holds: only items between low and high inclusive could
4785 while (middle > low)
4787 if (classoid != middle[-1].classoid ||
4788 objoid != middle[-1].objoid)
4797 while (middle <= high)
4799 if (classoid != middle->classoid ||
4800 objoid != middle->objoid)
4810 * collectComments --
4812 * Construct a table of all comments available for database objects.
4813 * We used to do per-object queries for the comments, but it's much faster
4814 * to pull them all over at once, and on most databases the memory cost
4817 * The table is sorted by classoid/objid/objsubid for speed in lookup.
4820 collectComments(Archive *fout, CommentItem **items)
4830 CommentItem *comments;
4833 * Note we do NOT change source schema here; preserve the caller's
4837 query = createPQExpBuffer();
4839 if (fout->remoteVersion >= 70300)
4841 appendPQExpBuffer(query, "SELECT description, classoid, objoid, objsubid "
4842 "FROM pg_catalog.pg_description "
4843 "ORDER BY classoid, objoid, objsubid");
4845 else if (fout->remoteVersion >= 70200)
4847 appendPQExpBuffer(query, "SELECT description, classoid, objoid, objsubid "
4848 "FROM pg_description "
4849 "ORDER BY classoid, objoid, objsubid");
4853 /* Note: this will fail to find attribute comments in pre-7.2... */
4854 appendPQExpBuffer(query, "SELECT description, 0 as classoid, objoid, 0 as objsubid "
4855 "FROM pg_description "
4859 res = PQexec(g_conn, query->data);
4860 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4862 /* Construct lookup table containing OIDs in numeric form */
4864 i_description = PQfnumber(res, "description");
4865 i_classoid = PQfnumber(res, "classoid");
4866 i_objoid = PQfnumber(res, "objoid");
4867 i_objsubid = PQfnumber(res, "objsubid");
4869 ntups = PQntuples(res);
4871 comments = (CommentItem *) malloc(ntups * sizeof(CommentItem));
4873 for (i = 0; i < ntups; i++)
4875 comments[i].descr = PQgetvalue(res, i, i_description);
4876 comments[i].classoid = atooid(PQgetvalue(res, i, i_classoid));
4877 comments[i].objoid = atooid(PQgetvalue(res, i, i_objoid));
4878 comments[i].objsubid = atoi(PQgetvalue(res, i, i_objsubid));
4881 /* Do NOT free the PGresult since we are keeping pointers into it */
4882 destroyPQExpBuffer(query);
4889 * dumpDumpableObject
4891 * This routine and its subsidiaries are responsible for creating
4892 * ArchiveEntries (TOC objects) for each object to be dumped.
4895 dumpDumpableObject(Archive *fout, DumpableObject *dobj)
4897 switch (dobj->objType)
4900 dumpNamespace(fout, (NamespaceInfo *) dobj);
4903 dumpType(fout, (TypeInfo *) dobj);
4906 dumpShellType(fout, (ShellTypeInfo *) dobj);
4909 dumpFunc(fout, (FuncInfo *) dobj);
4912 dumpAgg(fout, (AggInfo *) dobj);
4915 dumpOpr(fout, (OprInfo *) dobj);
4918 dumpOpclass(fout, (OpclassInfo *) dobj);
4921 dumpConversion(fout, (ConvInfo *) dobj);
4924 dumpTable(fout, (TableInfo *) dobj);
4927 dumpAttrDef(fout, (AttrDefInfo *) dobj);
4930 dumpIndex(fout, (IndxInfo *) dobj);
4933 dumpRule(fout, (RuleInfo *) dobj);
4936 dumpTrigger(fout, (TriggerInfo *) dobj);
4939 dumpConstraint(fout, (ConstraintInfo *) dobj);
4941 case DO_FK_CONSTRAINT:
4942 dumpConstraint(fout, (ConstraintInfo *) dobj);
4945 dumpProcLang(fout, (ProcLangInfo *) dobj);
4948 dumpCast(fout, (CastInfo *) dobj);
4951 dumpTableData(fout, (TableDataInfo *) dobj);
4954 /* table rowtypes are never dumped separately */
4957 ArchiveEntry(fout, dobj->catId, dobj->dumpId,
4958 dobj->name, NULL, NULL, "",
4959 false, "BLOBS", "", "", NULL,
4963 case DO_BLOB_COMMENTS:
4964 ArchiveEntry(fout, dobj->catId, dobj->dumpId,
4965 dobj->name, NULL, NULL, "",
4966 false, "BLOB COMMENTS", "", "", NULL,
4968 dumpBlobComments, NULL);
4975 * writes out to fout the queries to recreate a user-defined namespace
4978 dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
4984 /* Skip if not to be dumped */
4985 if (!nspinfo->dobj.dump || dataOnly)
4988 /* don't dump dummy namespace from pre-7.3 source */
4989 if (strlen(nspinfo->dobj.name) == 0)
4992 q = createPQExpBuffer();
4993 delq = createPQExpBuffer();
4995 qnspname = strdup(fmtId(nspinfo->dobj.name));
4997 appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
4999 appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname);
5001 ArchiveEntry(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId,
5005 false, "SCHEMA", q->data, delq->data, NULL,
5006 nspinfo->dobj.dependencies, nspinfo->dobj.nDeps,
5009 /* Dump Schema Comments */
5010 resetPQExpBuffer(q);
5011 appendPQExpBuffer(q, "SCHEMA %s", qnspname);
5012 dumpComment(fout, q->data,
5013 NULL, nspinfo->rolname,
5014 nspinfo->dobj.catId, 0, nspinfo->dobj.dumpId);
5016 dumpACL(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId, "SCHEMA",
5017 qnspname, nspinfo->dobj.name, NULL,
5018 nspinfo->rolname, nspinfo->nspacl);
5022 destroyPQExpBuffer(q);
5023 destroyPQExpBuffer(delq);
5028 * writes out to fout the queries to recreate a user-defined type
5031 dumpType(Archive *fout, TypeInfo *tinfo)
5033 /* Skip if not to be dumped */
5034 if (!tinfo->dobj.dump || dataOnly)
5037 /* Dump out in proper style */
5038 if (tinfo->typtype == 'b')
5039 dumpBaseType(fout, tinfo);
5040 else if (tinfo->typtype == 'd')
5041 dumpDomain(fout, tinfo);
5042 else if (tinfo->typtype == 'c')
5043 dumpCompositeType(fout, tinfo);
5048 * writes out to fout the queries to recreate a user-defined base type
5051 dumpBaseType(Archive *fout, TypeInfo *tinfo)
5053 PQExpBuffer q = createPQExpBuffer();
5054 PQExpBuffer delq = createPQExpBuffer();
5055 PQExpBuffer query = createPQExpBuffer();
5074 bool typdefault_is_literal = false;
5076 /* Set proper schema search path so regproc references list correctly */
5077 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
5079 /* Fetch type-specific details */
5080 if (fout->remoteVersion >= 80000)
5082 appendPQExpBuffer(query, "SELECT typlen, "
5083 "typinput, typoutput, typreceive, typsend, "
5085 "typinput::pg_catalog.oid as typinputoid, "
5086 "typoutput::pg_catalog.oid as typoutputoid, "
5087 "typreceive::pg_catalog.oid as typreceiveoid, "
5088 "typsend::pg_catalog.oid as typsendoid, "
5089 "typanalyze::pg_catalog.oid as typanalyzeoid, "
5090 "typdelim, typbyval, typalign, typstorage, "
5091 "pg_catalog.pg_get_expr(typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) as typdefaultbin, typdefault "
5092 "FROM pg_catalog.pg_type "
5093 "WHERE oid = '%u'::pg_catalog.oid",
5094 tinfo->dobj.catId.oid);
5096 else if (fout->remoteVersion >= 70400)
5098 appendPQExpBuffer(query, "SELECT typlen, "
5099 "typinput, typoutput, typreceive, typsend, "
5100 "'-' as typanalyze, "
5101 "typinput::pg_catalog.oid as typinputoid, "
5102 "typoutput::pg_catalog.oid as typoutputoid, "
5103 "typreceive::pg_catalog.oid as typreceiveoid, "
5104 "typsend::pg_catalog.oid as typsendoid, "
5105 "0 as typanalyzeoid, "
5106 "typdelim, typbyval, typalign, typstorage, "
5107 "pg_catalog.pg_get_expr(typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) as typdefaultbin, typdefault "
5108 "FROM pg_catalog.pg_type "
5109 "WHERE oid = '%u'::pg_catalog.oid",
5110 tinfo->dobj.catId.oid);
5112 else if (fout->remoteVersion >= 70300)
5114 appendPQExpBuffer(query, "SELECT typlen, "
5115 "typinput, typoutput, "
5116 "'-' as typreceive, '-' as typsend, "
5117 "'-' as typanalyze, "
5118 "typinput::pg_catalog.oid as typinputoid, "
5119 "typoutput::pg_catalog.oid as typoutputoid, "
5120 "0 as typreceiveoid, 0 as typsendoid, "
5121 "0 as typanalyzeoid, "
5122 "typdelim, typbyval, typalign, typstorage, "
5123 "pg_catalog.pg_get_expr(typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) as typdefaultbin, typdefault "
5124 "FROM pg_catalog.pg_type "
5125 "WHERE oid = '%u'::pg_catalog.oid",
5126 tinfo->dobj.catId.oid);
5128 else if (fout->remoteVersion >= 70200)
5131 * Note: although pre-7.3 catalogs contain typreceive and typsend,
5132 * ignore them because they are not right.
5134 appendPQExpBuffer(query, "SELECT typlen, "
5135 "typinput, typoutput, "
5136 "'-' as typreceive, '-' as typsend, "
5137 "'-' as typanalyze, "
5138 "typinput::oid as typinputoid, "
5139 "typoutput::oid as typoutputoid, "
5140 "0 as typreceiveoid, 0 as typsendoid, "
5141 "0 as typanalyzeoid, "
5142 "typdelim, typbyval, typalign, typstorage, "
5143 "NULL as typdefaultbin, typdefault "
5145 "WHERE oid = '%u'::oid",
5146 tinfo->dobj.catId.oid);
5148 else if (fout->remoteVersion >= 70100)
5151 * Ignore pre-7.2 typdefault; the field exists but has an unusable
5154 appendPQExpBuffer(query, "SELECT typlen, "
5155 "typinput, typoutput, "
5156 "'-' as typreceive, '-' as typsend, "
5157 "'-' as typanalyze, "
5158 "typinput::oid as typinputoid, "
5159 "typoutput::oid as typoutputoid, "
5160 "0 as typreceiveoid, 0 as typsendoid, "
5161 "0 as typanalyzeoid, "
5162 "typdelim, typbyval, typalign, typstorage, "
5163 "NULL as typdefaultbin, NULL as typdefault "
5165 "WHERE oid = '%u'::oid",
5166 tinfo->dobj.catId.oid);
5170 appendPQExpBuffer(query, "SELECT typlen, "
5171 "typinput, typoutput, "
5172 "'-' as typreceive, '-' as typsend, "
5173 "'-' as typanalyze, "
5174 "typinput::oid as typinputoid, "
5175 "typoutput::oid as typoutputoid, "
5176 "0 as typreceiveoid, 0 as typsendoid, "
5177 "0 as typanalyzeoid, "
5178 "typdelim, typbyval, typalign, "
5179 "'p'::char as typstorage, "
5180 "NULL as typdefaultbin, NULL as typdefault "
5182 "WHERE oid = '%u'::oid",
5183 tinfo->dobj.catId.oid);
5186 res = PQexec(g_conn, query->data);
5187 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5189 /* Expecting a single result only */
5190 ntups = PQntuples(res);
5193 write_msg(NULL, "Got %d rows instead of one from: %s",
5194 ntups, query->data);
5198 typlen = PQgetvalue(res, 0, PQfnumber(res, "typlen"));
5199 typinput = PQgetvalue(res, 0, PQfnumber(res, "typinput"));
5200 typoutput = PQgetvalue(res, 0, PQfnumber(res, "typoutput"));
5201 typreceive = PQgetvalue(res, 0, PQfnumber(res, "typreceive"));
5202 typsend = PQgetvalue(res, 0, PQfnumber(res, "typsend"));
5203 typanalyze = PQgetvalue(res, 0, PQfnumber(res, "typanalyze"));
5204 typinputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typinputoid")));
5205 typoutputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typoutputoid")));
5206 typreceiveoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typreceiveoid")));
5207 typsendoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typsendoid")));
5208 typanalyzeoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typanalyzeoid")));
5209 typdelim = PQgetvalue(res, 0, PQfnumber(res, "typdelim"));
5210 typbyval = PQgetvalue(res, 0, PQfnumber(res, "typbyval"));
5211 typalign = PQgetvalue(res, 0, PQfnumber(res, "typalign"));
5212 typstorage = PQgetvalue(res, 0, PQfnumber(res, "typstorage"));
5213 if (!PQgetisnull(res, 0, PQfnumber(res, "typdefaultbin")))
5214 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefaultbin"));
5215 else if (!PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
5217 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
5218 typdefault_is_literal = true; /* it needs quotes */
5224 * DROP must be fully qualified in case same name appears in pg_catalog.
5225 * The reason we include CASCADE is that the circular dependency between
5226 * the type and its I/O functions makes it impossible to drop the type
5229 appendPQExpBuffer(delq, "DROP TYPE %s.",
5230 fmtId(tinfo->dobj.namespace->dobj.name));
5231 appendPQExpBuffer(delq, "%s CASCADE;\n",
5232 fmtId(tinfo->dobj.name));
5234 appendPQExpBuffer(q,
5235 "CREATE TYPE %s (\n"
5236 " INTERNALLENGTH = %s",
5237 fmtId(tinfo->dobj.name),
5238 (strcmp(typlen, "-1") == 0) ? "variable" : typlen);
5240 if (fout->remoteVersion >= 70300)
5242 /* regproc result is correctly quoted as of 7.3 */
5243 appendPQExpBuffer(q, ",\n INPUT = %s", typinput);
5244 appendPQExpBuffer(q, ",\n OUTPUT = %s", typoutput);
5245 if (OidIsValid(typreceiveoid))
5246 appendPQExpBuffer(q, ",\n RECEIVE = %s", typreceive);
5247 if (OidIsValid(typsendoid))
5248 appendPQExpBuffer(q, ",\n SEND = %s", typsend);
5249 if (OidIsValid(typanalyzeoid))
5250 appendPQExpBuffer(q, ",\n ANALYZE = %s", typanalyze);
5254 /* regproc delivers an unquoted name before 7.3 */
5255 /* cannot combine these because fmtId uses static result area */
5256 appendPQExpBuffer(q, ",\n INPUT = %s", fmtId(typinput));
5257 appendPQExpBuffer(q, ",\n OUTPUT = %s", fmtId(typoutput));
5258 /* no chance that receive/send/analyze need be printed */
5261 if (typdefault != NULL)
5263 appendPQExpBuffer(q, ",\n DEFAULT = ");
5264 if (typdefault_is_literal)
5265 appendStringLiteralAH(q, typdefault, fout);
5267 appendPQExpBufferStr(q, typdefault);
5274 /* reselect schema in case changed by function dump */
5275 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
5276 elemType = getFormattedTypeName(tinfo->typelem, zeroAsOpaque);
5277 appendPQExpBuffer(q, ",\n ELEMENT = %s", elemType);
5281 if (typdelim && strcmp(typdelim, ",") != 0)
5283 appendPQExpBuffer(q, ",\n DELIMITER = ");
5284 appendStringLiteralAH(q, typdelim, fout);
5287 if (strcmp(typalign, "c") == 0)
5288 appendPQExpBuffer(q, ",\n ALIGNMENT = char");
5289 else if (strcmp(typalign, "s") == 0)
5290 appendPQExpBuffer(q, ",\n ALIGNMENT = int2");
5291 else if (strcmp(typalign, "i") == 0)
5292 appendPQExpBuffer(q, ",\n ALIGNMENT = int4");
5293 else if (strcmp(typalign, "d") == 0)
5294 appendPQExpBuffer(q, ",\n ALIGNMENT = double");
5296 if (strcmp(typstorage, "p") == 0)
5297 appendPQExpBuffer(q, ",\n STORAGE = plain");
5298 else if (strcmp(typstorage, "e") == 0)
5299 appendPQExpBuffer(q, ",\n STORAGE = external");
5300 else if (strcmp(typstorage, "x") == 0)
5301 appendPQExpBuffer(q, ",\n STORAGE = extended");
5302 else if (strcmp(typstorage, "m") == 0)
5303 appendPQExpBuffer(q, ",\n STORAGE = main");
5305 if (strcmp(typbyval, "t") == 0)
5306 appendPQExpBuffer(q, ",\n PASSEDBYVALUE");
5308 appendPQExpBuffer(q, "\n);\n");
5310 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
5312 tinfo->dobj.namespace->dobj.name,
5314 tinfo->rolname, false,
5315 "TYPE", q->data, delq->data, NULL,
5316 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
5319 /* Dump Type Comments */
5320 resetPQExpBuffer(q);
5322 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->dobj.name));
5323 dumpComment(fout, q->data,
5324 tinfo->dobj.namespace->dobj.name, tinfo->rolname,
5325 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
5328 destroyPQExpBuffer(q);
5329 destroyPQExpBuffer(delq);
5330 destroyPQExpBuffer(query);
5335 * writes out to fout the queries to recreate a user-defined domain
5338 dumpDomain(Archive *fout, TypeInfo *tinfo)
5340 PQExpBuffer q = createPQExpBuffer();
5341 PQExpBuffer delq = createPQExpBuffer();
5342 PQExpBuffer query = createPQExpBuffer();
5349 bool typdefault_is_literal = false;
5351 /* Set proper schema search path so type references list correctly */
5352 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
5354 /* Fetch domain specific details */
5355 /* We assume here that remoteVersion must be at least 70300 */
5356 appendPQExpBuffer(query, "SELECT typnotnull, "
5357 "pg_catalog.format_type(typbasetype, typtypmod) as typdefn, "
5358 "pg_catalog.pg_get_expr(typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) as typdefaultbin, typdefault "
5359 "FROM pg_catalog.pg_type "
5360 "WHERE oid = '%u'::pg_catalog.oid",
5361 tinfo->dobj.catId.oid);
5363 res = PQexec(g_conn, query->data);
5364 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5366 /* Expecting a single result only */
5367 ntups = PQntuples(res);
5370 write_msg(NULL, "Got %d rows instead of one from: %s",
5371 ntups, query->data);
5375 typnotnull = PQgetvalue(res, 0, PQfnumber(res, "typnotnull"));
5376 typdefn = PQgetvalue(res, 0, PQfnumber(res, "typdefn"));
5377 if (!PQgetisnull(res, 0, PQfnumber(res, "typdefaultbin")))
5378 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefaultbin"));
5379 else if (!PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
5381 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
5382 typdefault_is_literal = true; /* it needs quotes */
5387 appendPQExpBuffer(q,
5388 "CREATE DOMAIN %s AS %s",
5389 fmtId(tinfo->dobj.name),
5392 if (typnotnull[0] == 't')
5393 appendPQExpBuffer(q, " NOT NULL");
5395 if (typdefault != NULL)
5397 appendPQExpBuffer(q, " DEFAULT ");
5398 if (typdefault_is_literal)
5399 appendStringLiteralAH(q, typdefault, fout);
5401 appendPQExpBufferStr(q, typdefault);
5407 * Add any CHECK constraints for the domain
5409 for (i = 0; i < tinfo->nDomChecks; i++)
5411 ConstraintInfo *domcheck = &(tinfo->domChecks[i]);
5413 if (!domcheck->separate)
5414 appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s",
5415 fmtId(domcheck->dobj.name), domcheck->condef);
5418 appendPQExpBuffer(q, ";\n");
5421 * DROP must be fully qualified in case same name appears in pg_catalog
5423 appendPQExpBuffer(delq, "DROP DOMAIN %s.",
5424 fmtId(tinfo->dobj.namespace->dobj.name));
5425 appendPQExpBuffer(delq, "%s;\n",
5426 fmtId(tinfo->dobj.name));
5428 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
5430 tinfo->dobj.namespace->dobj.name,
5432 tinfo->rolname, false,
5433 "DOMAIN", q->data, delq->data, NULL,
5434 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
5437 /* Dump Domain Comments */
5438 resetPQExpBuffer(q);
5440 appendPQExpBuffer(q, "DOMAIN %s", fmtId(tinfo->dobj.name));
5441 dumpComment(fout, q->data,
5442 tinfo->dobj.namespace->dobj.name, tinfo->rolname,
5443 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
5445 destroyPQExpBuffer(q);
5446 destroyPQExpBuffer(delq);
5447 destroyPQExpBuffer(query);
5452 * writes out to fout the queries to recreate a user-defined stand-alone
5456 dumpCompositeType(Archive *fout, TypeInfo *tinfo)
5458 PQExpBuffer q = createPQExpBuffer();
5459 PQExpBuffer delq = createPQExpBuffer();
5460 PQExpBuffer query = createPQExpBuffer();
5467 /* Set proper schema search path so type references list correctly */
5468 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
5470 /* Fetch type specific details */
5471 /* We assume here that remoteVersion must be at least 70300 */
5473 appendPQExpBuffer(query, "SELECT a.attname, "
5474 "pg_catalog.format_type(a.atttypid, a.atttypmod) as atttypdefn "
5475 "FROM pg_catalog.pg_type t, pg_catalog.pg_attribute a "
5476 "WHERE t.oid = '%u'::pg_catalog.oid "
5477 "AND a.attrelid = t.typrelid "
5478 "AND NOT a.attisdropped "
5479 "ORDER BY a.attnum ",
5480 tinfo->dobj.catId.oid);
5482 res = PQexec(g_conn, query->data);
5483 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5485 /* Expecting at least a single result */
5486 ntups = PQntuples(res);
5489 write_msg(NULL, "query yielded no rows: %s\n", query->data);
5493 i_attname = PQfnumber(res, "attname");
5494 i_atttypdefn = PQfnumber(res, "atttypdefn");
5496 appendPQExpBuffer(q, "CREATE TYPE %s AS (",
5497 fmtId(tinfo->dobj.name));
5499 for (i = 0; i < ntups; i++)
5504 attname = PQgetvalue(res, i, i_attname);
5505 atttypdefn = PQgetvalue(res, i, i_atttypdefn);
5507 appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);
5509 appendPQExpBuffer(q, ",");
5511 appendPQExpBuffer(q, "\n);\n");
5514 * DROP must be fully qualified in case same name appears in pg_catalog
5516 appendPQExpBuffer(delq, "DROP TYPE %s.",
5517 fmtId(tinfo->dobj.namespace->dobj.name));
5518 appendPQExpBuffer(delq, "%s;\n",
5519 fmtId(tinfo->dobj.name));
5521 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
5523 tinfo->dobj.namespace->dobj.name,
5525 tinfo->rolname, false,
5526 "TYPE", q->data, delq->data, NULL,
5527 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
5531 /* Dump Type Comments */
5532 resetPQExpBuffer(q);
5534 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->dobj.name));
5535 dumpComment(fout, q->data,
5536 tinfo->dobj.namespace->dobj.name, tinfo->rolname,
5537 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
5540 destroyPQExpBuffer(q);
5541 destroyPQExpBuffer(delq);
5542 destroyPQExpBuffer(query);
5547 * writes out to fout the queries to create a shell type
5549 * We dump a shell definition in advance of the I/O functions for the type.
5552 dumpShellType(Archive *fout, ShellTypeInfo *stinfo)
5556 /* Skip if not to be dumped */
5557 if (!stinfo->dobj.dump || dataOnly)
5560 q = createPQExpBuffer();
5563 * Note the lack of a DROP command for the shell type; any required DROP
5564 * is driven off the base type entry, instead. This interacts with
5565 * _printTocEntry()'s use of the presence of a DROP command to decide
5566 * whether an entry needs an ALTER OWNER command. We don't want to
5567 * alter the shell type's owner immediately on creation; that should
5568 * happen only after it's filled in, otherwise the backend complains.
5571 appendPQExpBuffer(q, "CREATE TYPE %s;\n",
5572 fmtId(stinfo->dobj.name));
5574 ArchiveEntry(fout, stinfo->dobj.catId, stinfo->dobj.dumpId,
5576 stinfo->dobj.namespace->dobj.name,
5578 stinfo->baseType->rolname, false,
5579 "SHELL TYPE", q->data, "", NULL,
5580 stinfo->dobj.dependencies, stinfo->dobj.nDeps,
5583 destroyPQExpBuffer(q);
5587 * Determine whether we want to dump definitions for procedural languages.
5588 * Since the languages themselves don't have schemas, we can't rely on
5589 * the normal schema-based selection mechanism. We choose to dump them
5590 * whenever neither --schema nor --table was given. (Before 8.1, we used
5591 * the dump flag of the PL's call handler function, but in 8.1 this will
5592 * probably always be false since call handlers are created in pg_catalog.)
5594 * For some backwards compatibility with the older behavior, we forcibly
5595 * dump a PL if its handler function (and validator if any) are in a
5596 * dumpable namespace. That case is not checked here.
5599 shouldDumpProcLangs(void)
5601 if (matchingTables != NULL || matchingSchemas != NULL)
5603 /* And they're schema not data */
5611 * writes out to fout the queries to recreate a user-defined
5612 * procedural language
5615 dumpProcLang(Archive *fout, ProcLangInfo *plang)
5623 FuncInfo *validatorInfo = NULL;
5629 * Try to find the support function(s). It is not an error if we don't
5630 * find them --- if the functions are in the pg_catalog schema, as is
5631 * standard in 8.1 and up, then we won't have loaded them. (In this case
5632 * we will emit a parameterless CREATE LANGUAGE command, which will
5633 * require PL template knowledge in the backend to reload.)
5636 funcInfo = findFuncByOid(plang->lanplcallfoid);
5637 if (funcInfo != NULL && !funcInfo->dobj.dump)
5638 funcInfo = NULL; /* treat not-dumped same as not-found */
5640 if (OidIsValid(plang->lanvalidator))
5642 validatorInfo = findFuncByOid(plang->lanvalidator);
5643 if (validatorInfo != NULL && !validatorInfo->dobj.dump)
5644 validatorInfo = NULL;
5648 * If the functions are dumpable then emit a traditional CREATE LANGUAGE
5649 * with parameters. Otherwise, dump only if shouldDumpProcLangs() says to
5652 useParams = (funcInfo != NULL &&
5653 (validatorInfo != NULL || !OidIsValid(plang->lanvalidator)));
5655 if (!useParams && !shouldDumpProcLangs())
5658 defqry = createPQExpBuffer();
5659 delqry = createPQExpBuffer();
5661 qlanname = strdup(fmtId(plang->dobj.name));
5664 * If dumping a HANDLER clause, treat the language as being in the handler
5665 * function's schema; this avoids cluttering the HANDLER clause. Otherwise
5666 * it doesn't really have a schema.
5669 lanschema = funcInfo->dobj.namespace->dobj.name;
5673 appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE %s;\n",
5676 appendPQExpBuffer(defqry, "CREATE %sPROCEDURAL LANGUAGE %s",
5677 (useParams && plang->lanpltrusted) ? "TRUSTED " : "",
5681 appendPQExpBuffer(defqry, " HANDLER %s",
5682 fmtId(funcInfo->dobj.name));
5683 if (OidIsValid(plang->lanvalidator))
5685 appendPQExpBuffer(defqry, " VALIDATOR ");
5686 /* Cope with possibility that validator is in different schema */
5687 if (validatorInfo->dobj.namespace != funcInfo->dobj.namespace)
5688 appendPQExpBuffer(defqry, "%s.",
5689 fmtId(validatorInfo->dobj.namespace->dobj.name));
5690 appendPQExpBuffer(defqry, "%s",
5691 fmtId(validatorInfo->dobj.name));
5694 appendPQExpBuffer(defqry, ";\n");
5696 ArchiveEntry(fout, plang->dobj.catId, plang->dobj.dumpId,
5698 lanschema, NULL, plang->lanowner,
5699 false, "PROCEDURAL LANGUAGE",
5700 defqry->data, delqry->data, NULL,
5701 plang->dobj.dependencies, plang->dobj.nDeps,
5704 /* Dump Proc Lang Comments */
5705 resetPQExpBuffer(defqry);
5706 appendPQExpBuffer(defqry, "LANGUAGE %s", qlanname);
5707 dumpComment(fout, defqry->data,
5709 plang->dobj.catId, 0, plang->dobj.dumpId);
5711 if (plang->lanpltrusted)
5712 dumpACL(fout, plang->dobj.catId, plang->dobj.dumpId, "LANGUAGE",
5713 qlanname, plang->dobj.name,
5715 plang->lanowner, plang->lanacl);
5719 destroyPQExpBuffer(defqry);
5720 destroyPQExpBuffer(delqry);
5724 * format_function_arguments: generate function name and argument list
5726 * The argument type names are qualified if needed. The function name
5727 * is never qualified.
5729 * Any or all of allargtypes, argmodes, argnames may be NULL.
5732 format_function_arguments(FuncInfo *finfo, int nallargs,
5740 initPQExpBuffer(&fn);
5741 appendPQExpBuffer(&fn, "%s(", fmtId(finfo->dobj.name));
5742 for (j = 0; j < nallargs; j++)
5746 const char *argmode;
5747 const char *argname;
5749 typid = allargtypes ? atooid(allargtypes[j]) : finfo->argtypes[j];
5750 typname = getFormattedTypeName(typid, zeroAsOpaque);
5754 switch (argmodes[j][0])
5766 write_msg(NULL, "WARNING: bogus value in proargmodes array\n");
5774 argname = argnames ? argnames[j] : (char *) NULL;
5775 if (argname && argname[0] == '\0')
5778 appendPQExpBuffer(&fn, "%s%s%s%s%s",
5779 (j > 0) ? ", " : "",
5781 argname ? fmtId(argname) : "",
5786 appendPQExpBuffer(&fn, ")");
5791 * format_function_signature: generate function name and argument list
5793 * This is like format_function_arguments except that only a minimal
5794 * list of input argument types is generated; this is sufficient to
5795 * reference the function, but not to define it.
5797 * If honor_quotes is false then the function name is never quoted.
5798 * This is appropriate for use in TOC tags, but not in SQL commands.
5801 format_function_signature(FuncInfo *finfo, bool honor_quotes)
5806 initPQExpBuffer(&fn);
5808 appendPQExpBuffer(&fn, "%s(", fmtId(finfo->dobj.name));
5810 appendPQExpBuffer(&fn, "%s(", finfo->dobj.name);
5811 for (j = 0; j < finfo->nargs; j++)
5815 typname = getFormattedTypeName(finfo->argtypes[j], zeroAsOpaque);
5817 appendPQExpBuffer(&fn, "%s%s",
5818 (j > 0) ? ", " : "",
5822 appendPQExpBuffer(&fn, ")");
5829 * dump out one function
5832 dumpFunc(Archive *fout, FuncInfo *finfo)
5845 char *proallargtypes;
5854 char **allargtypes = NULL;
5855 char **argmodes = NULL;
5856 char **argnames = NULL;
5858 /* Skip if not to be dumped */
5859 if (!finfo->dobj.dump || dataOnly)
5862 query = createPQExpBuffer();
5863 q = createPQExpBuffer();
5864 delqry = createPQExpBuffer();
5865 asPart = createPQExpBuffer();
5867 /* Set proper schema search path so type references list correctly */
5868 selectSourceSchema(finfo->dobj.namespace->dobj.name);
5870 /* Fetch function-specific details */
5871 if (g_fout->remoteVersion >= 80100)
5873 appendPQExpBuffer(query,
5874 "SELECT proretset, prosrc, probin, "
5875 "proallargtypes, proargmodes, proargnames, "
5876 "provolatile, proisstrict, prosecdef, "
5877 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
5878 "FROM pg_catalog.pg_proc "
5879 "WHERE oid = '%u'::pg_catalog.oid",
5880 finfo->dobj.catId.oid);
5882 else if (g_fout->remoteVersion >= 80000)
5884 appendPQExpBuffer(query,
5885 "SELECT proretset, prosrc, probin, "
5886 "null as proallargtypes, "
5887 "null as proargmodes, "
5889 "provolatile, proisstrict, prosecdef, "
5890 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
5891 "FROM pg_catalog.pg_proc "
5892 "WHERE oid = '%u'::pg_catalog.oid",
5893 finfo->dobj.catId.oid);
5895 else if (g_fout->remoteVersion >= 70300)
5897 appendPQExpBuffer(query,
5898 "SELECT proretset, prosrc, probin, "
5899 "null as proallargtypes, "
5900 "null as proargmodes, "
5901 "null as proargnames, "
5902 "provolatile, proisstrict, prosecdef, "
5903 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
5904 "FROM pg_catalog.pg_proc "
5905 "WHERE oid = '%u'::pg_catalog.oid",
5906 finfo->dobj.catId.oid);
5908 else if (g_fout->remoteVersion >= 70100)
5910 appendPQExpBuffer(query,
5911 "SELECT proretset, prosrc, probin, "
5912 "null as proallargtypes, "
5913 "null as proargmodes, "
5914 "null as proargnames, "
5915 "case when proiscachable then 'i' else 'v' end as provolatile, "
5917 "'f'::boolean as prosecdef, "
5918 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
5920 "WHERE oid = '%u'::oid",
5921 finfo->dobj.catId.oid);
5925 appendPQExpBuffer(query,
5926 "SELECT proretset, prosrc, probin, "
5927 "null as proallargtypes, "
5928 "null as proargmodes, "
5929 "null as proargnames, "
5930 "case when proiscachable then 'i' else 'v' end as provolatile, "
5931 "'f'::boolean as proisstrict, "
5932 "'f'::boolean as prosecdef, "
5933 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
5935 "WHERE oid = '%u'::oid",
5936 finfo->dobj.catId.oid);
5939 res = PQexec(g_conn, query->data);
5940 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5942 /* Expecting a single result only */
5943 ntups = PQntuples(res);
5946 write_msg(NULL, "Got %d rows instead of one from: %s",
5947 ntups, query->data);
5951 proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset"));
5952 prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc"));
5953 probin = PQgetvalue(res, 0, PQfnumber(res, "probin"));
5954 proallargtypes = PQgetvalue(res, 0, PQfnumber(res, "proallargtypes"));
5955 proargmodes = PQgetvalue(res, 0, PQfnumber(res, "proargmodes"));
5956 proargnames = PQgetvalue(res, 0, PQfnumber(res, "proargnames"));
5957 provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
5958 proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
5959 prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
5960 lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
5963 * See backend/commands/define.c for details of how the 'AS' clause is
5966 if (strcmp(probin, "-") != 0)
5968 appendPQExpBuffer(asPart, "AS ");
5969 appendStringLiteralAH(asPart, probin, fout);
5970 if (strcmp(prosrc, "-") != 0)
5972 appendPQExpBuffer(asPart, ", ");
5975 * where we have bin, use dollar quoting if allowed and src
5976 * contains quote or backslash; else use regular quoting.
5978 if (disable_dollar_quoting ||
5979 (strchr(prosrc, '\'') == NULL && strchr(prosrc, '\\') == NULL))
5980 appendStringLiteralAH(asPart, prosrc, fout);
5982 appendStringLiteralDQ(asPart, prosrc, NULL);
5987 if (strcmp(prosrc, "-") != 0)
5989 appendPQExpBuffer(asPart, "AS ");
5990 /* with no bin, dollar quote src unconditionally if allowed */
5991 if (disable_dollar_quoting)
5992 appendStringLiteralAH(asPart, prosrc, fout);
5994 appendStringLiteralDQ(asPart, prosrc, NULL);
5998 nallargs = finfo->nargs; /* unless we learn different from allargs */
6000 if (proallargtypes && *proallargtypes)
6004 if (!parsePGArray(proallargtypes, &allargtypes, &nitems) ||
6005 nitems < finfo->nargs)
6007 write_msg(NULL, "WARNING: could not parse proallargtypes array\n");
6016 if (proargmodes && *proargmodes)
6020 if (!parsePGArray(proargmodes, &argmodes, &nitems) ||
6023 write_msg(NULL, "WARNING: could not parse proargmodes array\n");
6030 if (proargnames && *proargnames)
6034 if (!parsePGArray(proargnames, &argnames, &nitems) ||
6037 write_msg(NULL, "WARNING: could not parse proargnames array\n");
6044 funcsig = format_function_arguments(finfo, nallargs, allargtypes,
6045 argmodes, argnames);
6046 funcsig_tag = format_function_signature(finfo, false);
6049 * DROP must be fully qualified in case same name appears in pg_catalog
6051 appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n",
6052 fmtId(finfo->dobj.namespace->dobj.name),
6055 rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque);
6057 appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
6058 appendPQExpBuffer(q, "RETURNS %s%s\n %s\n LANGUAGE %s",
6059 (proretset[0] == 't') ? "SETOF " : "",
6066 if (provolatile[0] != PROVOLATILE_VOLATILE)
6068 if (provolatile[0] == PROVOLATILE_IMMUTABLE)
6069 appendPQExpBuffer(q, " IMMUTABLE");
6070 else if (provolatile[0] == PROVOLATILE_STABLE)
6071 appendPQExpBuffer(q, " STABLE");
6072 else if (provolatile[0] != PROVOLATILE_VOLATILE)
6074 write_msg(NULL, "unrecognized provolatile value for function \"%s\"\n",
6080 if (proisstrict[0] == 't')
6081 appendPQExpBuffer(q, " STRICT");
6083 if (prosecdef[0] == 't')
6084 appendPQExpBuffer(q, " SECURITY DEFINER");
6086 appendPQExpBuffer(q, ";\n");
6088 ArchiveEntry(fout, finfo->dobj.catId, finfo->dobj.dumpId,
6090 finfo->dobj.namespace->dobj.name,
6092 finfo->rolname, false,
6093 "FUNCTION", q->data, delqry->data, NULL,
6094 finfo->dobj.dependencies, finfo->dobj.nDeps,
6097 /* Dump Function Comments */
6098 resetPQExpBuffer(q);
6099 appendPQExpBuffer(q, "FUNCTION %s", funcsig);
6100 dumpComment(fout, q->data,
6101 finfo->dobj.namespace->dobj.name, finfo->rolname,
6102 finfo->dobj.catId, 0, finfo->dobj.dumpId);
6104 dumpACL(fout, finfo->dobj.catId, finfo->dobj.dumpId, "FUNCTION",
6105 funcsig, funcsig_tag,
6106 finfo->dobj.namespace->dobj.name,
6107 finfo->rolname, finfo->proacl);
6111 destroyPQExpBuffer(query);
6112 destroyPQExpBuffer(q);
6113 destroyPQExpBuffer(delqry);
6114 destroyPQExpBuffer(asPart);
6127 * Dump a user-defined cast
6130 dumpCast(Archive *fout, CastInfo *cast)
6134 PQExpBuffer castsig;
6135 FuncInfo *funcInfo = NULL;
6136 TypeInfo *sourceInfo;
6137 TypeInfo *targetInfo;
6142 if (OidIsValid(cast->castfunc))
6144 funcInfo = findFuncByOid(cast->castfunc);
6145 if (funcInfo == NULL)
6150 * As per discussion we dump casts if one or more of the underlying
6151 * objects (the conversion function and the two data types) are not
6152 * builtin AND if all of the non-builtin objects are included in the dump.
6153 * Builtin meaning, the namespace name does not start with "pg_".
6155 sourceInfo = findTypeByOid(cast->castsource);
6156 targetInfo = findTypeByOid(cast->casttarget);
6158 if (sourceInfo == NULL || targetInfo == NULL)
6162 * Skip this cast if all objects are from pg_
6164 if ((funcInfo == NULL ||
6165 strncmp(funcInfo->dobj.namespace->dobj.name, "pg_", 3) == 0) &&
6166 strncmp(sourceInfo->dobj.namespace->dobj.name, "pg_", 3) == 0 &&
6167 strncmp(targetInfo->dobj.namespace->dobj.name, "pg_", 3) == 0)
6171 * Skip cast if function isn't from pg_ and is not to be dumped.
6174 strncmp(funcInfo->dobj.namespace->dobj.name, "pg_", 3) != 0 &&
6175 !funcInfo->dobj.dump)
6179 * Same for the source type
6181 if (strncmp(sourceInfo->dobj.namespace->dobj.name, "pg_", 3) != 0 &&
6182 !sourceInfo->dobj.dump)
6186 * and the target type.
6188 if (strncmp(targetInfo->dobj.namespace->dobj.name, "pg_", 3) != 0 &&
6189 !targetInfo->dobj.dump)
6192 /* Make sure we are in proper schema (needed for getFormattedTypeName) */
6193 selectSourceSchema("pg_catalog");
6195 defqry = createPQExpBuffer();
6196 delqry = createPQExpBuffer();
6197 castsig = createPQExpBuffer();
6199 appendPQExpBuffer(delqry, "DROP CAST (%s AS %s);\n",
6200 getFormattedTypeName(cast->castsource, zeroAsNone),
6201 getFormattedTypeName(cast->casttarget, zeroAsNone));
6203 appendPQExpBuffer(defqry, "CREATE CAST (%s AS %s) ",
6204 getFormattedTypeName(cast->castsource, zeroAsNone),
6205 getFormattedTypeName(cast->casttarget, zeroAsNone));
6207 if (!OidIsValid(cast->castfunc))
6208 appendPQExpBuffer(defqry, "WITHOUT FUNCTION");
6212 * Always qualify the function name, in case it is not in pg_catalog
6213 * schema (format_function_signature won't qualify it).
6215 appendPQExpBuffer(defqry, "WITH FUNCTION %s.",
6216 fmtId(funcInfo->dobj.namespace->dobj.name));
6217 appendPQExpBuffer(defqry, "%s",
6218 format_function_signature(funcInfo, true));
6221 if (cast->castcontext == 'a')
6222 appendPQExpBuffer(defqry, " AS ASSIGNMENT");
6223 else if (cast->castcontext == 'i')
6224 appendPQExpBuffer(defqry, " AS IMPLICIT");
6225 appendPQExpBuffer(defqry, ";\n");
6227 appendPQExpBuffer(castsig, "CAST (%s AS %s)",
6228 getFormattedTypeName(cast->castsource, zeroAsNone),
6229 getFormattedTypeName(cast->casttarget, zeroAsNone));
6231 ArchiveEntry(fout, cast->dobj.catId, cast->dobj.dumpId,
6233 "pg_catalog", NULL, "",
6234 false, "CAST", defqry->data, delqry->data, NULL,
6235 cast->dobj.dependencies, cast->dobj.nDeps,
6238 /* Dump Cast Comments */
6239 resetPQExpBuffer(defqry);
6240 appendPQExpBuffer(defqry, "CAST (%s AS %s)",
6241 getFormattedTypeName(cast->castsource, zeroAsNone),
6242 getFormattedTypeName(cast->casttarget, zeroAsNone));
6243 dumpComment(fout, defqry->data,
6245 cast->dobj.catId, 0, cast->dobj.dumpId);
6247 destroyPQExpBuffer(defqry);
6248 destroyPQExpBuffer(delqry);
6249 destroyPQExpBuffer(castsig);
6254 * write out a single operator definition
6257 dumpOpr(Archive *fout, OprInfo *oprinfo)
6263 PQExpBuffer details;
6294 /* Skip if not to be dumped */
6295 if (!oprinfo->dobj.dump || dataOnly)
6299 * some operators are invalid because they were the result of user
6300 * defining operators before commutators exist
6302 if (!OidIsValid(oprinfo->oprcode))
6305 query = createPQExpBuffer();
6306 q = createPQExpBuffer();
6307 delq = createPQExpBuffer();
6308 oprid = createPQExpBuffer();
6309 details = createPQExpBuffer();
6311 /* Make sure we are in proper schema so regoperator works correctly */
6312 selectSourceSchema(oprinfo->dobj.namespace->dobj.name);
6314 if (g_fout->remoteVersion >= 70300)
6316 appendPQExpBuffer(query, "SELECT oprkind, "
6317 "oprcode::pg_catalog.regprocedure, "
6318 "oprleft::pg_catalog.regtype, "
6319 "oprright::pg_catalog.regtype, "
6320 "oprcom::pg_catalog.regoperator, "
6321 "oprnegate::pg_catalog.regoperator, "
6322 "oprrest::pg_catalog.regprocedure, "
6323 "oprjoin::pg_catalog.regprocedure, "
6325 "oprlsortop::pg_catalog.regoperator, "
6326 "oprrsortop::pg_catalog.regoperator, "
6327 "oprltcmpop::pg_catalog.regoperator, "
6328 "oprgtcmpop::pg_catalog.regoperator "
6329 "from pg_catalog.pg_operator "
6330 "where oid = '%u'::pg_catalog.oid",
6331 oprinfo->dobj.catId.oid);
6333 else if (g_fout->remoteVersion >= 70100)
6335 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
6336 "CASE WHEN oprleft = 0 THEN '-' "
6337 "ELSE format_type(oprleft, NULL) END as oprleft, "
6338 "CASE WHEN oprright = 0 THEN '-' "
6339 "ELSE format_type(oprright, NULL) END as oprright, "
6340 "oprcom, oprnegate, oprrest, oprjoin, "
6341 "oprcanhash, oprlsortop, oprrsortop, "
6342 "0 as oprltcmpop, 0 as oprgtcmpop "
6344 "where oid = '%u'::oid",
6345 oprinfo->dobj.catId.oid);
6349 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
6350 "CASE WHEN oprleft = 0 THEN '-'::name "
6351 "ELSE (select typname from pg_type where oid = oprleft) END as oprleft, "
6352 "CASE WHEN oprright = 0 THEN '-'::name "
6353 "ELSE (select typname from pg_type where oid = oprright) END as oprright, "
6354 "oprcom, oprnegate, oprrest, oprjoin, "
6355 "oprcanhash, oprlsortop, oprrsortop, "
6356 "0 as oprltcmpop, 0 as oprgtcmpop "
6358 "where oid = '%u'::oid",
6359 oprinfo->dobj.catId.oid);
6362 res = PQexec(g_conn, query->data);
6363 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6365 /* Expecting a single result only */
6366 ntups = PQntuples(res);
6369 write_msg(NULL, "Got %d rows instead of one from: %s",
6370 ntups, query->data);
6374 i_oprkind = PQfnumber(res, "oprkind");
6375 i_oprcode = PQfnumber(res, "oprcode");
6376 i_oprleft = PQfnumber(res, "oprleft");
6377 i_oprright = PQfnumber(res, "oprright");
6378 i_oprcom = PQfnumber(res, "oprcom");
6379 i_oprnegate = PQfnumber(res, "oprnegate");
6380 i_oprrest = PQfnumber(res, "oprrest");
6381 i_oprjoin = PQfnumber(res, "oprjoin");
6382 i_oprcanhash = PQfnumber(res, "oprcanhash");
6383 i_oprlsortop = PQfnumber(res, "oprlsortop");
6384 i_oprrsortop = PQfnumber(res, "oprrsortop");
6385 i_oprltcmpop = PQfnumber(res, "oprltcmpop");
6386 i_oprgtcmpop = PQfnumber(res, "oprgtcmpop");
6388 oprkind = PQgetvalue(res, 0, i_oprkind);
6389 oprcode = PQgetvalue(res, 0, i_oprcode);
6390 oprleft = PQgetvalue(res, 0, i_oprleft);
6391 oprright = PQgetvalue(res, 0, i_oprright);
6392 oprcom = PQgetvalue(res, 0, i_oprcom);
6393 oprnegate = PQgetvalue(res, 0, i_oprnegate);
6394 oprrest = PQgetvalue(res, 0, i_oprrest);
6395 oprjoin = PQgetvalue(res, 0, i_oprjoin);
6396 oprcanhash = PQgetvalue(res, 0, i_oprcanhash);
6397 oprlsortop = PQgetvalue(res, 0, i_oprlsortop);
6398 oprrsortop = PQgetvalue(res, 0, i_oprrsortop);
6399 oprltcmpop = PQgetvalue(res, 0, i_oprltcmpop);
6400 oprgtcmpop = PQgetvalue(res, 0, i_oprgtcmpop);
6402 appendPQExpBuffer(details, " PROCEDURE = %s",
6403 convertRegProcReference(oprcode));
6405 appendPQExpBuffer(oprid, "%s (",
6406 oprinfo->dobj.name);
6409 * right unary means there's a left arg and left unary means there's a
6412 if (strcmp(oprkind, "r") == 0 ||
6413 strcmp(oprkind, "b") == 0)
6415 if (g_fout->remoteVersion >= 70100)
6418 name = fmtId(oprleft);
6419 appendPQExpBuffer(details, ",\n LEFTARG = %s", name);
6420 appendPQExpBuffer(oprid, "%s", name);
6423 appendPQExpBuffer(oprid, "NONE");
6425 if (strcmp(oprkind, "l") == 0 ||
6426 strcmp(oprkind, "b") == 0)
6428 if (g_fout->remoteVersion >= 70100)
6431 name = fmtId(oprright);
6432 appendPQExpBuffer(details, ",\n RIGHTARG = %s", name);
6433 appendPQExpBuffer(oprid, ", %s)", name);
6436 appendPQExpBuffer(oprid, ", NONE)");
6438 name = convertOperatorReference(oprcom);
6440 appendPQExpBuffer(details, ",\n COMMUTATOR = %s", name);
6442 name = convertOperatorReference(oprnegate);
6444 appendPQExpBuffer(details, ",\n NEGATOR = %s", name);
6446 if (strcmp(oprcanhash, "t") == 0)
6447 appendPQExpBuffer(details, ",\n HASHES");
6449 name = convertRegProcReference(oprrest);
6451 appendPQExpBuffer(details, ",\n RESTRICT = %s", name);
6453 name = convertRegProcReference(oprjoin);
6455 appendPQExpBuffer(details, ",\n JOIN = %s", name);
6457 name = convertOperatorReference(oprlsortop);
6459 appendPQExpBuffer(details, ",\n SORT1 = %s", name);
6461 name = convertOperatorReference(oprrsortop);
6463 appendPQExpBuffer(details, ",\n SORT2 = %s", name);
6465 name = convertOperatorReference(oprltcmpop);
6467 appendPQExpBuffer(details, ",\n LTCMP = %s", name);
6469 name = convertOperatorReference(oprgtcmpop);
6471 appendPQExpBuffer(details, ",\n GTCMP = %s", name);
6474 * DROP must be fully qualified in case same name appears in pg_catalog
6476 appendPQExpBuffer(delq, "DROP OPERATOR %s.%s;\n",
6477 fmtId(oprinfo->dobj.namespace->dobj.name),
6480 appendPQExpBuffer(q, "CREATE OPERATOR %s (\n%s\n);\n",
6481 oprinfo->dobj.name, details->data);
6483 ArchiveEntry(fout, oprinfo->dobj.catId, oprinfo->dobj.dumpId,
6485 oprinfo->dobj.namespace->dobj.name,
6488 false, "OPERATOR", q->data, delq->data, NULL,
6489 oprinfo->dobj.dependencies, oprinfo->dobj.nDeps,
6492 /* Dump Operator Comments */
6493 resetPQExpBuffer(q);
6494 appendPQExpBuffer(q, "OPERATOR %s", oprid->data);
6495 dumpComment(fout, q->data,
6496 oprinfo->dobj.namespace->dobj.name, oprinfo->rolname,
6497 oprinfo->dobj.catId, 0, oprinfo->dobj.dumpId);
6501 destroyPQExpBuffer(query);
6502 destroyPQExpBuffer(q);
6503 destroyPQExpBuffer(delq);
6504 destroyPQExpBuffer(oprid);
6505 destroyPQExpBuffer(details);
6509 * Convert a function reference obtained from pg_operator
6511 * Returns what to print, or NULL if function references is InvalidOid
6513 * In 7.3 the input is a REGPROCEDURE display; we have to strip the
6514 * argument-types part. In prior versions, the input is a REGPROC display.
6517 convertRegProcReference(const char *proc)
6519 /* In all cases "-" means a null reference */
6520 if (strcmp(proc, "-") == 0)
6523 if (g_fout->remoteVersion >= 70300)
6529 name = strdup(proc);
6530 /* find non-double-quoted left paren */
6532 for (paren = name; *paren; paren++)
6534 if (*paren == '(' && !inquote)
6545 /* REGPROC before 7.3 does not quote its result */
6550 * Convert an operator cross-reference obtained from pg_operator
6552 * Returns what to print, or NULL to print nothing
6554 * In 7.3 and up the input is a REGOPERATOR display; we have to strip the
6555 * argument-types part, and add OPERATOR() decoration if the name is
6556 * schema-qualified. In older versions, the input is just a numeric OID,
6557 * which we search our operator list for.
6560 convertOperatorReference(const char *opr)
6564 /* In all cases "0" means a null reference */
6565 if (strcmp(opr, "0") == 0)
6568 if (g_fout->remoteVersion >= 70300)
6577 /* find non-double-quoted left paren, and check for non-quoted dot */
6580 for (ptr = name; *ptr; ptr++)
6584 else if (*ptr == '.' && !inquote)
6586 else if (*ptr == '(' && !inquote)
6592 /* If not schema-qualified, don't need to add OPERATOR() */
6595 oname = malloc(strlen(name) + 11);
6596 sprintf(oname, "OPERATOR(%s)", name);
6601 oprInfo = findOprByOid(atooid(opr));
6602 if (oprInfo == NULL)
6604 write_msg(NULL, "WARNING: could not find operator with OID %s\n",
6608 return oprInfo->dobj.name;
6613 * write out a single operator class definition
6616 dumpOpclass(Archive *fout, OpclassInfo *opcinfo)
6644 /* Skip if not to be dumped */
6645 if (!opcinfo->dobj.dump || dataOnly)
6649 * XXX currently we do not implement dumping of operator classes from
6650 * pre-7.3 databases. This could be done but it seems not worth the
6653 if (g_fout->remoteVersion < 70300)
6656 query = createPQExpBuffer();
6657 q = createPQExpBuffer();
6658 delq = createPQExpBuffer();
6660 /* Make sure we are in proper schema so regoperator works correctly */
6661 selectSourceSchema(opcinfo->dobj.namespace->dobj.name);
6663 /* Get additional fields from the pg_opclass row */
6664 appendPQExpBuffer(query, "SELECT opcintype::pg_catalog.regtype, "
6665 "opckeytype::pg_catalog.regtype, "
6667 "(SELECT amname FROM pg_catalog.pg_am WHERE oid = opcamid) AS amname "
6668 "FROM pg_catalog.pg_opclass "
6669 "WHERE oid = '%u'::pg_catalog.oid",
6670 opcinfo->dobj.catId.oid);
6672 res = PQexec(g_conn, query->data);
6673 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6675 /* Expecting a single result only */
6676 ntups = PQntuples(res);
6679 write_msg(NULL, "Got %d rows instead of one from: %s",
6680 ntups, query->data);
6684 i_opcintype = PQfnumber(res, "opcintype");
6685 i_opckeytype = PQfnumber(res, "opckeytype");
6686 i_opcdefault = PQfnumber(res, "opcdefault");
6687 i_amname = PQfnumber(res, "amname");
6689 opcintype = PQgetvalue(res, 0, i_opcintype);
6690 opckeytype = PQgetvalue(res, 0, i_opckeytype);
6691 opcdefault = PQgetvalue(res, 0, i_opcdefault);
6692 /* amname will still be needed after we PQclear res */
6693 amname = strdup(PQgetvalue(res, 0, i_amname));
6696 * DROP must be fully qualified in case same name appears in pg_catalog
6698 appendPQExpBuffer(delq, "DROP OPERATOR CLASS %s",
6699 fmtId(opcinfo->dobj.namespace->dobj.name));
6700 appendPQExpBuffer(delq, ".%s",
6701 fmtId(opcinfo->dobj.name));
6702 appendPQExpBuffer(delq, " USING %s;\n",
6705 /* Build the fixed portion of the CREATE command */
6706 appendPQExpBuffer(q, "CREATE OPERATOR CLASS %s\n ",
6707 fmtId(opcinfo->dobj.name));
6708 if (strcmp(opcdefault, "t") == 0)
6709 appendPQExpBuffer(q, "DEFAULT ");
6710 appendPQExpBuffer(q, "FOR TYPE %s USING %s AS\n ",
6716 if (strcmp(opckeytype, "-") != 0)
6718 appendPQExpBuffer(q, "STORAGE %s",
6726 * Now fetch and print the OPERATOR entries (pg_amop rows).
6728 resetPQExpBuffer(query);
6730 appendPQExpBuffer(query, "SELECT amopstrategy, amopreqcheck, "
6731 "amopopr::pg_catalog.regoperator "
6732 "FROM pg_catalog.pg_amop "
6733 "WHERE amopclaid = '%u'::pg_catalog.oid "
6734 "ORDER BY amopstrategy",
6735 opcinfo->dobj.catId.oid);
6737 res = PQexec(g_conn, query->data);
6738 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6740 ntups = PQntuples(res);
6742 i_amopstrategy = PQfnumber(res, "amopstrategy");
6743 i_amopreqcheck = PQfnumber(res, "amopreqcheck");
6744 i_amopopr = PQfnumber(res, "amopopr");
6746 for (i = 0; i < ntups; i++)
6748 amopstrategy = PQgetvalue(res, i, i_amopstrategy);
6749 amopreqcheck = PQgetvalue(res, i, i_amopreqcheck);
6750 amopopr = PQgetvalue(res, i, i_amopopr);
6753 appendPQExpBuffer(q, " ,\n ");
6755 appendPQExpBuffer(q, "OPERATOR %s %s",
6756 amopstrategy, amopopr);
6757 if (strcmp(amopreqcheck, "t") == 0)
6758 appendPQExpBuffer(q, " RECHECK");
6766 * Now fetch and print the FUNCTION entries (pg_amproc rows).
6768 resetPQExpBuffer(query);
6770 appendPQExpBuffer(query, "SELECT amprocnum, "
6771 "amproc::pg_catalog.regprocedure "
6772 "FROM pg_catalog.pg_amproc "
6773 "WHERE amopclaid = '%u'::pg_catalog.oid "
6774 "ORDER BY amprocnum",
6775 opcinfo->dobj.catId.oid);
6777 res = PQexec(g_conn, query->data);
6778 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6780 ntups = PQntuples(res);
6782 i_amprocnum = PQfnumber(res, "amprocnum");
6783 i_amproc = PQfnumber(res, "amproc");
6785 for (i = 0; i < ntups; i++)
6787 amprocnum = PQgetvalue(res, i, i_amprocnum);
6788 amproc = PQgetvalue(res, i, i_amproc);
6791 appendPQExpBuffer(q, " ,\n ");
6793 appendPQExpBuffer(q, "FUNCTION %s %s",
6801 appendPQExpBuffer(q, ";\n");
6803 ArchiveEntry(fout, opcinfo->dobj.catId, opcinfo->dobj.dumpId,
6805 opcinfo->dobj.namespace->dobj.name,
6808 false, "OPERATOR CLASS", q->data, delq->data, NULL,
6809 opcinfo->dobj.dependencies, opcinfo->dobj.nDeps,
6812 /* Dump Operator Class Comments */
6813 resetPQExpBuffer(q);
6814 appendPQExpBuffer(q, "OPERATOR CLASS %s",
6815 fmtId(opcinfo->dobj.name));
6816 appendPQExpBuffer(q, " USING %s",
6818 dumpComment(fout, q->data,
6819 NULL, opcinfo->rolname,
6820 opcinfo->dobj.catId, 0, opcinfo->dobj.dumpId);
6823 destroyPQExpBuffer(query);
6824 destroyPQExpBuffer(q);
6825 destroyPQExpBuffer(delq);
6830 * write out a single conversion definition
6833 dumpConversion(Archive *fout, ConvInfo *convinfo)
6838 PQExpBuffer details;
6842 int i_conforencoding;
6843 int i_contoencoding;
6846 const char *conname;
6847 const char *conforencoding;
6848 const char *contoencoding;
6849 const char *conproc;
6852 /* Skip if not to be dumped */
6853 if (!convinfo->dobj.dump || dataOnly)
6856 query = createPQExpBuffer();
6857 q = createPQExpBuffer();
6858 delq = createPQExpBuffer();
6859 details = createPQExpBuffer();
6861 /* Make sure we are in proper schema */
6862 selectSourceSchema(convinfo->dobj.namespace->dobj.name);
6864 /* Get conversion-specific details */
6865 appendPQExpBuffer(query, "SELECT conname, "
6866 "pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding, "
6867 "pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding, "
6868 "conproc, condefault "
6869 "FROM pg_catalog.pg_conversion c "
6870 "WHERE c.oid = '%u'::pg_catalog.oid",
6871 convinfo->dobj.catId.oid);
6873 res = PQexec(g_conn, query->data);
6874 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6876 /* Expecting a single result only */
6877 ntups = PQntuples(res);
6880 write_msg(NULL, "Got %d rows instead of one from: %s",
6881 ntups, query->data);
6885 i_conname = PQfnumber(res, "conname");
6886 i_conforencoding = PQfnumber(res, "conforencoding");
6887 i_contoencoding = PQfnumber(res, "contoencoding");
6888 i_conproc = PQfnumber(res, "conproc");
6889 i_condefault = PQfnumber(res, "condefault");
6891 conname = PQgetvalue(res, 0, i_conname);
6892 conforencoding = PQgetvalue(res, 0, i_conforencoding);
6893 contoencoding = PQgetvalue(res, 0, i_contoencoding);
6894 conproc = PQgetvalue(res, 0, i_conproc);
6895 condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
6898 * DROP must be fully qualified in case same name appears in pg_catalog
6900 appendPQExpBuffer(delq, "DROP CONVERSION %s",
6901 fmtId(convinfo->dobj.namespace->dobj.name));
6902 appendPQExpBuffer(delq, ".%s;\n",
6903 fmtId(convinfo->dobj.name));
6905 appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
6906 (condefault) ? "DEFAULT " : "",
6907 fmtId(convinfo->dobj.name));
6908 appendStringLiteralAH(q, conforencoding, fout);
6909 appendPQExpBuffer(q, " TO ");
6910 appendStringLiteralAH(q, contoencoding, fout);
6911 /* regproc is automatically quoted in 7.3 and above */
6912 appendPQExpBuffer(q, " FROM %s;\n", conproc);
6914 ArchiveEntry(fout, convinfo->dobj.catId, convinfo->dobj.dumpId,
6915 convinfo->dobj.name,
6916 convinfo->dobj.namespace->dobj.name,
6919 false, "CONVERSION", q->data, delq->data, NULL,
6920 convinfo->dobj.dependencies, convinfo->dobj.nDeps,
6923 /* Dump Conversion Comments */
6924 resetPQExpBuffer(q);
6925 appendPQExpBuffer(q, "CONVERSION %s", fmtId(convinfo->dobj.name));
6926 dumpComment(fout, q->data,
6927 convinfo->dobj.namespace->dobj.name, convinfo->rolname,
6928 convinfo->dobj.catId, 0, convinfo->dobj.dumpId);
6932 destroyPQExpBuffer(query);
6933 destroyPQExpBuffer(q);
6934 destroyPQExpBuffer(delq);
6935 destroyPQExpBuffer(details);
6939 * format_aggregate_signature: generate aggregate name and argument list
6941 * The argument type names are qualified if needed. The aggregate name
6942 * is never qualified.
6945 format_aggregate_signature(AggInfo *agginfo, Archive *fout, bool honor_quotes)
6947 PQExpBufferData buf;
6950 initPQExpBuffer(&buf);
6952 appendPQExpBuffer(&buf, "%s",
6953 fmtId(agginfo->aggfn.dobj.name));
6955 appendPQExpBuffer(&buf, "%s", agginfo->aggfn.dobj.name);
6957 if (agginfo->aggfn.nargs == 0)
6958 appendPQExpBuffer(&buf, "(*)");
6961 appendPQExpBuffer(&buf, "(");
6962 for (j = 0; j < agginfo->aggfn.nargs; j++)
6966 typname = getFormattedTypeName(agginfo->aggfn.argtypes[j], zeroAsOpaque);
6968 appendPQExpBuffer(&buf, "%s%s",
6969 (j > 0) ? ", " : "",
6973 appendPQExpBuffer(&buf, ")");
6980 * write out a single aggregate definition
6983 dumpAgg(Archive *fout, AggInfo *agginfo)
6988 PQExpBuffer details;
6999 const char *aggtransfn;
7000 const char *aggfinalfn;
7001 const char *aggsortop;
7002 const char *aggtranstype;
7003 const char *agginitval;
7006 /* Skip if not to be dumped */
7007 if (!agginfo->aggfn.dobj.dump || dataOnly)
7010 query = createPQExpBuffer();
7011 q = createPQExpBuffer();
7012 delq = createPQExpBuffer();
7013 details = createPQExpBuffer();
7015 /* Make sure we are in proper schema */
7016 selectSourceSchema(agginfo->aggfn.dobj.namespace->dobj.name);
7018 /* Get aggregate-specific details */
7019 if (g_fout->remoteVersion >= 80100)
7021 appendPQExpBuffer(query, "SELECT aggtransfn, "
7022 "aggfinalfn, aggtranstype::pg_catalog.regtype, "
7023 "aggsortop::pg_catalog.regoperator, "
7025 "'t'::boolean as convertok "
7026 "from pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
7027 "where a.aggfnoid = p.oid "
7028 "and p.oid = '%u'::pg_catalog.oid",
7029 agginfo->aggfn.dobj.catId.oid);
7031 else if (g_fout->remoteVersion >= 70300)
7033 appendPQExpBuffer(query, "SELECT aggtransfn, "
7034 "aggfinalfn, aggtranstype::pg_catalog.regtype, "
7037 "'t'::boolean as convertok "
7038 "from pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
7039 "where a.aggfnoid = p.oid "
7040 "and p.oid = '%u'::pg_catalog.oid",
7041 agginfo->aggfn.dobj.catId.oid);
7043 else if (g_fout->remoteVersion >= 70100)
7045 appendPQExpBuffer(query, "SELECT aggtransfn, aggfinalfn, "
7046 "format_type(aggtranstype, NULL) as aggtranstype, "
7049 "'t'::boolean as convertok "
7050 "from pg_aggregate "
7051 "where oid = '%u'::oid",
7052 agginfo->aggfn.dobj.catId.oid);
7056 appendPQExpBuffer(query, "SELECT aggtransfn1 as aggtransfn, "
7058 "(select typname from pg_type where oid = aggtranstype1) as aggtranstype, "
7060 "agginitval1 as agginitval, "
7061 "(aggtransfn2 = 0 and aggtranstype2 = 0 and agginitval2 is null) as convertok "
7062 "from pg_aggregate "
7063 "where oid = '%u'::oid",
7064 agginfo->aggfn.dobj.catId.oid);
7067 res = PQexec(g_conn, query->data);
7068 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7070 /* Expecting a single result only */
7071 ntups = PQntuples(res);
7074 write_msg(NULL, "Got %d rows instead of one from: %s",
7075 ntups, query->data);
7079 i_aggtransfn = PQfnumber(res, "aggtransfn");
7080 i_aggfinalfn = PQfnumber(res, "aggfinalfn");
7081 i_aggsortop = PQfnumber(res, "aggsortop");
7082 i_aggtranstype = PQfnumber(res, "aggtranstype");
7083 i_agginitval = PQfnumber(res, "agginitval");
7084 i_convertok = PQfnumber(res, "convertok");
7086 aggtransfn = PQgetvalue(res, 0, i_aggtransfn);
7087 aggfinalfn = PQgetvalue(res, 0, i_aggfinalfn);
7088 aggsortop = PQgetvalue(res, 0, i_aggsortop);
7089 aggtranstype = PQgetvalue(res, 0, i_aggtranstype);
7090 agginitval = PQgetvalue(res, 0, i_agginitval);
7091 convertok = (PQgetvalue(res, 0, i_convertok)[0] == 't');
7093 aggsig = format_aggregate_signature(agginfo, fout, true);
7094 aggsig_tag = format_aggregate_signature(agginfo, fout, false);
7098 write_msg(NULL, "WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
7103 if (g_fout->remoteVersion >= 70300)
7105 /* If using 7.3's regproc or regtype, data is already quoted */
7106 appendPQExpBuffer(details, " SFUNC = %s,\n STYPE = %s",
7110 else if (g_fout->remoteVersion >= 70100)
7112 /* format_type quotes, regproc does not */
7113 appendPQExpBuffer(details, " SFUNC = %s,\n STYPE = %s",
7119 /* need quotes all around */
7120 appendPQExpBuffer(details, " SFUNC = %s,\n",
7122 appendPQExpBuffer(details, " STYPE = %s",
7123 fmtId(aggtranstype));
7126 if (!PQgetisnull(res, 0, i_agginitval))
7128 appendPQExpBuffer(details, ",\n INITCOND = ");
7129 appendStringLiteralAH(details, agginitval, fout);
7132 if (strcmp(aggfinalfn, "-") != 0)
7134 appendPQExpBuffer(details, ",\n FINALFUNC = %s",
7138 aggsortop = convertOperatorReference(aggsortop);
7141 appendPQExpBuffer(details, ",\n SORTOP = %s",
7146 * DROP must be fully qualified in case same name appears in pg_catalog
7148 appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n",
7149 fmtId(agginfo->aggfn.dobj.namespace->dobj.name),
7152 appendPQExpBuffer(q, "CREATE AGGREGATE %s (\n%s\n);\n",
7153 aggsig, details->data);
7155 ArchiveEntry(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
7157 agginfo->aggfn.dobj.namespace->dobj.name,
7159 agginfo->aggfn.rolname,
7160 false, "AGGREGATE", q->data, delq->data, NULL,
7161 agginfo->aggfn.dobj.dependencies, agginfo->aggfn.dobj.nDeps,
7164 /* Dump Aggregate Comments */
7165 resetPQExpBuffer(q);
7166 appendPQExpBuffer(q, "AGGREGATE %s", aggsig);
7167 dumpComment(fout, q->data,
7168 agginfo->aggfn.dobj.namespace->dobj.name, agginfo->aggfn.rolname,
7169 agginfo->aggfn.dobj.catId, 0, agginfo->aggfn.dobj.dumpId);
7172 * Since there is no GRANT ON AGGREGATE syntax, we have to make the ACL
7173 * command look like a function's GRANT; in particular this affects the
7174 * syntax for zero-argument aggregates.
7179 aggsig = format_function_signature(&agginfo->aggfn, true);
7180 aggsig_tag = format_function_signature(&agginfo->aggfn, false);
7182 dumpACL(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
7185 agginfo->aggfn.dobj.namespace->dobj.name,
7186 agginfo->aggfn.rolname, agginfo->aggfn.proacl);
7193 destroyPQExpBuffer(query);
7194 destroyPQExpBuffer(q);
7195 destroyPQExpBuffer(delq);
7196 destroyPQExpBuffer(details);
7201 * Write out grant/revoke information
7203 * 'objCatId' is the catalog ID of the underlying object.
7204 * 'objDumpId' is the dump ID of the underlying object.
7205 * 'type' must be TABLE, FUNCTION, LANGUAGE, SCHEMA, DATABASE, or TABLESPACE.
7206 * 'name' is the formatted name of the object. Must be quoted etc. already.
7207 * 'tag' is the tag for the archive entry (typ. unquoted name of object).
7208 * 'nspname' is the namespace the object is in (NULL if none).
7209 * 'owner' is the owner, NULL if there is no owner (for languages).
7210 * 'acls' is the string read out of the fooacl system catalog field;
7211 * it will be parsed here.
7215 dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
7216 const char *type, const char *name,
7217 const char *tag, const char *nspname, const char *owner,
7222 /* Do nothing if ACL dump is not enabled */
7223 if (dataOnly || aclsSkip)
7226 sql = createPQExpBuffer();
7228 if (!buildACLCommands(name, type, acls, owner, fout->remoteVersion, sql))
7230 write_msg(NULL, "could not parse ACL list (%s) for object \"%s\" (%s)\n",
7236 ArchiveEntry(fout, nilCatalogId, createDumpId(),
7240 false, "ACL", sql->data, "", NULL,
7244 destroyPQExpBuffer(sql);
7249 * write out to fout the declarations (not data) of a user-defined table
7252 dumpTable(Archive *fout, TableInfo *tbinfo)
7256 if (tbinfo->dobj.dump)
7258 if (tbinfo->relkind == RELKIND_SEQUENCE)
7259 dumpSequence(fout, tbinfo);
7261 dumpTableSchema(fout, tbinfo);
7263 /* Handle the ACL here */
7264 namecopy = strdup(fmtId(tbinfo->dobj.name));
7265 dumpACL(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
7266 (tbinfo->relkind == RELKIND_SEQUENCE) ? "SEQUENCE" : "TABLE",
7267 namecopy, tbinfo->dobj.name,
7268 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
7276 * write the declaration (not data) of one user-defined table or view
7279 dumpTableSchema(Archive *fout, TableInfo *tbinfo)
7281 PQExpBuffer query = createPQExpBuffer();
7282 PQExpBuffer q = createPQExpBuffer();
7283 PQExpBuffer delq = createPQExpBuffer();
7286 TableInfo **parents;
7287 int actual_atts; /* number of attrs in this CREATE statment */
7293 /* Make sure we are in proper schema */
7294 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
7296 /* Is it a table or a view? */
7297 if (tbinfo->relkind == RELKIND_VIEW)
7301 reltypename = "VIEW";
7303 /* Fetch the view definition */
7304 if (g_fout->remoteVersion >= 70300)
7306 /* Beginning in 7.3, viewname is not unique; rely on OID */
7307 appendPQExpBuffer(query,
7308 "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) as viewdef",
7309 tbinfo->dobj.catId.oid);
7313 appendPQExpBuffer(query, "SELECT definition as viewdef "
7314 " from pg_views where viewname = ");
7315 appendStringLiteralAH(query, tbinfo->dobj.name, fout);
7316 appendPQExpBuffer(query, ";");
7319 res = PQexec(g_conn, query->data);
7320 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7322 if (PQntuples(res) != 1)
7324 if (PQntuples(res) < 1)
7325 write_msg(NULL, "query to obtain definition of view \"%s\" returned no data\n",
7328 write_msg(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n",
7333 viewdef = PQgetvalue(res, 0, 0);
7335 if (strlen(viewdef) == 0)
7337 write_msg(NULL, "definition of view \"%s\" appears to be empty (length zero)\n",
7343 * DROP must be fully qualified in case same name appears in
7346 appendPQExpBuffer(delq, "DROP VIEW %s.",
7347 fmtId(tbinfo->dobj.namespace->dobj.name));
7348 appendPQExpBuffer(delq, "%s;\n",
7349 fmtId(tbinfo->dobj.name));
7351 appendPQExpBuffer(q, "CREATE VIEW %s AS\n %s\n",
7352 fmtId(tbinfo->dobj.name), viewdef);
7358 reltypename = "TABLE";
7359 numParents = tbinfo->numParents;
7360 parents = tbinfo->parents;
7363 * DROP must be fully qualified in case same name appears in
7366 appendPQExpBuffer(delq, "DROP TABLE %s.",
7367 fmtId(tbinfo->dobj.namespace->dobj.name));
7368 appendPQExpBuffer(delq, "%s;\n",
7369 fmtId(tbinfo->dobj.name));
7371 appendPQExpBuffer(q, "CREATE TABLE %s (",
7372 fmtId(tbinfo->dobj.name));
7374 for (j = 0; j < tbinfo->numatts; j++)
7376 /* Is this one of the table's own attrs, and not dropped ? */
7377 if (!tbinfo->inhAttrs[j] && !tbinfo->attisdropped[j])
7379 /* Format properly if not first attr */
7380 if (actual_atts > 0)
7381 appendPQExpBuffer(q, ",");
7382 appendPQExpBuffer(q, "\n ");
7384 /* Attribute name */
7385 appendPQExpBuffer(q, "%s ",
7386 fmtId(tbinfo->attnames[j]));
7388 /* Attribute type */
7389 if (g_fout->remoteVersion >= 70100)
7391 appendPQExpBuffer(q, "%s",
7392 tbinfo->atttypnames[j]);
7396 /* If no format_type, fake it */
7397 appendPQExpBuffer(q, "%s",
7398 myFormatType(tbinfo->atttypnames[j],
7399 tbinfo->atttypmod[j]));
7403 * Default value --- suppress if inherited or to be
7404 * printed separately.
7406 if (tbinfo->attrdefs[j] != NULL &&
7407 !tbinfo->inhAttrDef[j] &&
7408 !tbinfo->attrdefs[j]->separate)
7409 appendPQExpBuffer(q, " DEFAULT %s",
7410 tbinfo->attrdefs[j]->adef_expr);
7413 * Not Null constraint --- suppress if inherited
7415 if (tbinfo->notnull[j] && !tbinfo->inhNotNull[j])
7416 appendPQExpBuffer(q, " NOT NULL");
7423 * Add non-inherited CHECK constraints, if any.
7425 for (j = 0; j < tbinfo->ncheck; j++)
7427 ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
7429 if (constr->coninherited || constr->separate)
7432 if (actual_atts > 0)
7433 appendPQExpBuffer(q, ",\n ");
7435 appendPQExpBuffer(q, "CONSTRAINT %s ",
7436 fmtId(constr->dobj.name));
7437 appendPQExpBuffer(q, "%s", constr->condef);
7442 appendPQExpBuffer(q, "\n)");
7446 appendPQExpBuffer(q, "\nINHERITS (");
7447 for (k = 0; k < numParents; k++)
7449 TableInfo *parentRel = parents[k];
7452 appendPQExpBuffer(q, ", ");
7453 if (parentRel->dobj.namespace != tbinfo->dobj.namespace)
7454 appendPQExpBuffer(q, "%s.",
7455 fmtId(parentRel->dobj.namespace->dobj.name));
7456 appendPQExpBuffer(q, "%s",
7457 fmtId(parentRel->dobj.name));
7459 appendPQExpBuffer(q, ")");
7462 if (tbinfo->reloptions && strlen(tbinfo->reloptions) > 0)
7463 appendPQExpBuffer(q, "\nWITH (%s)", tbinfo->reloptions);
7465 appendPQExpBuffer(q, ";\n");
7467 /* Loop dumping statistics and storage statements */
7468 for (j = 0; j < tbinfo->numatts; j++)
7471 * Dump per-column statistics information. We only issue an ALTER
7472 * TABLE statement if the attstattarget entry for this column is
7473 * non-negative (i.e. it's not the default value)
7475 if (tbinfo->attstattarget[j] >= 0 &&
7476 !tbinfo->attisdropped[j])
7478 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
7479 fmtId(tbinfo->dobj.name));
7480 appendPQExpBuffer(q, "ALTER COLUMN %s ",
7481 fmtId(tbinfo->attnames[j]));
7482 appendPQExpBuffer(q, "SET STATISTICS %d;\n",
7483 tbinfo->attstattarget[j]);
7487 * Dump per-column storage information. The statement is only
7488 * dumped if the storage has been changed from the type's default.
7490 if (!tbinfo->attisdropped[j] && tbinfo->attstorage[j] != tbinfo->typstorage[j])
7492 switch (tbinfo->attstorage[j])
7498 storage = "EXTERNAL";
7504 storage = "EXTENDED";
7511 * Only dump the statement if it's a storage type we recognize
7513 if (storage != NULL)
7515 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
7516 fmtId(tbinfo->dobj.name));
7517 appendPQExpBuffer(q, "ALTER COLUMN %s ",
7518 fmtId(tbinfo->attnames[j]));
7519 appendPQExpBuffer(q, "SET STORAGE %s;\n",
7526 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
7528 tbinfo->dobj.namespace->dobj.name,
7529 (tbinfo->relkind == RELKIND_VIEW) ? NULL : tbinfo->reltablespace,
7531 (strcmp(reltypename, "TABLE") == 0) ? tbinfo->hasoids : false,
7532 reltypename, q->data, delq->data, NULL,
7533 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
7536 /* Dump Table Comments */
7537 dumpTableComment(fout, tbinfo, reltypename);
7539 /* Dump comments on inlined table constraints */
7540 for (j = 0; j < tbinfo->ncheck; j++)
7542 ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
7544 if (constr->coninherited || constr->separate)
7547 dumpTableConstraintComment(fout, constr);
7550 destroyPQExpBuffer(query);
7551 destroyPQExpBuffer(q);
7552 destroyPQExpBuffer(delq);
7556 * dumpAttrDef --- dump an attribute's default-value declaration
7559 dumpAttrDef(Archive *fout, AttrDefInfo *adinfo)
7561 TableInfo *tbinfo = adinfo->adtable;
7562 int adnum = adinfo->adnum;
7566 /* Only print it if "separate" mode is selected */
7567 if (!tbinfo->dobj.dump || !adinfo->separate || dataOnly)
7570 /* Don't print inherited defaults, either */
7571 if (tbinfo->inhAttrDef[adnum - 1])
7574 q = createPQExpBuffer();
7575 delq = createPQExpBuffer();
7577 appendPQExpBuffer(q, "ALTER TABLE %s ",
7578 fmtId(tbinfo->dobj.name));
7579 appendPQExpBuffer(q, "ALTER COLUMN %s SET DEFAULT %s;\n",
7580 fmtId(tbinfo->attnames[adnum - 1]),
7584 * DROP must be fully qualified in case same name appears in pg_catalog
7586 appendPQExpBuffer(delq, "ALTER TABLE %s.",
7587 fmtId(tbinfo->dobj.namespace->dobj.name));
7588 appendPQExpBuffer(delq, "%s ",
7589 fmtId(tbinfo->dobj.name));
7590 appendPQExpBuffer(delq, "ALTER COLUMN %s DROP DEFAULT;\n",
7591 fmtId(tbinfo->attnames[adnum - 1]));
7593 ArchiveEntry(fout, adinfo->dobj.catId, adinfo->dobj.dumpId,
7594 tbinfo->attnames[adnum - 1],
7595 tbinfo->dobj.namespace->dobj.name,
7598 false, "DEFAULT", q->data, delq->data, NULL,
7599 adinfo->dobj.dependencies, adinfo->dobj.nDeps,
7602 destroyPQExpBuffer(q);
7603 destroyPQExpBuffer(delq);
7607 * getAttrName: extract the correct name for an attribute
7609 * The array tblInfo->attnames[] only provides names of user attributes;
7610 * if a system attribute number is supplied, we have to fake it.
7611 * We also do a little bit of bounds checking for safety's sake.
7614 getAttrName(int attrnum, TableInfo *tblInfo)
7616 if (attrnum > 0 && attrnum <= tblInfo->numatts)
7617 return tblInfo->attnames[attrnum - 1];
7620 case SelfItemPointerAttributeNumber:
7622 case ObjectIdAttributeNumber:
7624 case MinTransactionIdAttributeNumber:
7626 case MinCommandIdAttributeNumber:
7628 case MaxTransactionIdAttributeNumber:
7630 case MaxCommandIdAttributeNumber:
7632 case TableOidAttributeNumber:
7635 write_msg(NULL, "invalid column number %d for table \"%s\"\n",
7636 attrnum, tblInfo->dobj.name);
7638 return NULL; /* keep compiler quiet */
7643 * write out to fout a user-defined index
7646 dumpIndex(Archive *fout, IndxInfo *indxinfo)
7648 TableInfo *tbinfo = indxinfo->indextable;
7655 q = createPQExpBuffer();
7656 delq = createPQExpBuffer();
7659 * If there's an associated constraint, don't dump the index per se, but
7660 * do dump any comment for it. (This is safe because dependency ordering
7661 * will have ensured the constraint is emitted first.)
7663 if (indxinfo->indexconstraint == 0)
7665 /* Plain secondary index */
7666 appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
7668 /* If the index is clustered, we need to record that. */
7669 if (indxinfo->indisclustered)
7671 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
7672 fmtId(tbinfo->dobj.name));
7673 appendPQExpBuffer(q, " ON %s;\n",
7674 fmtId(indxinfo->dobj.name));
7678 * DROP must be fully qualified in case same name appears in
7681 appendPQExpBuffer(delq, "DROP INDEX %s.",
7682 fmtId(tbinfo->dobj.namespace->dobj.name));
7683 appendPQExpBuffer(delq, "%s;\n",
7684 fmtId(indxinfo->dobj.name));
7686 ArchiveEntry(fout, indxinfo->dobj.catId, indxinfo->dobj.dumpId,
7687 indxinfo->dobj.name,
7688 tbinfo->dobj.namespace->dobj.name,
7689 indxinfo->tablespace,
7690 tbinfo->rolname, false,
7691 "INDEX", q->data, delq->data, NULL,
7692 indxinfo->dobj.dependencies, indxinfo->dobj.nDeps,
7696 /* Dump Index Comments */
7697 resetPQExpBuffer(q);
7698 appendPQExpBuffer(q, "INDEX %s",
7699 fmtId(indxinfo->dobj.name));
7700 dumpComment(fout, q->data,
7701 tbinfo->dobj.namespace->dobj.name,
7703 indxinfo->dobj.catId, 0, indxinfo->dobj.dumpId);
7705 destroyPQExpBuffer(q);
7706 destroyPQExpBuffer(delq);
7711 * write out to fout a user-defined constraint
7714 dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
7716 TableInfo *tbinfo = coninfo->contable;
7720 /* Skip if not to be dumped */
7721 if (!coninfo->dobj.dump || dataOnly)
7724 q = createPQExpBuffer();
7725 delq = createPQExpBuffer();
7727 if (coninfo->contype == 'p' || coninfo->contype == 'u')
7729 /* Index-related constraint */
7733 indxinfo = (IndxInfo *) findObjectByDumpId(coninfo->conindex);
7735 if (indxinfo == NULL)
7737 write_msg(NULL, "missing index for constraint \"%s\"\n",
7738 coninfo->dobj.name);
7742 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
7743 fmtId(tbinfo->dobj.name));
7744 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s (",
7745 fmtId(coninfo->dobj.name),
7746 coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
7748 for (k = 0; k < indxinfo->indnkeys; k++)
7750 int indkey = (int) indxinfo->indkeys[k];
7751 const char *attname;
7753 if (indkey == InvalidAttrNumber)
7755 attname = getAttrName(indkey, tbinfo);
7757 appendPQExpBuffer(q, "%s%s",
7758 (k == 0) ? "" : ", ",
7762 appendPQExpBuffer(q, ")");
7764 if (indxinfo->options && strlen(indxinfo->options) > 0)
7765 appendPQExpBuffer(q, " WITH (%s)", indxinfo->options);
7767 appendPQExpBuffer(q, ";\n");
7769 /* If the index is clustered, we need to record that. */
7770 if (indxinfo->indisclustered)
7772 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
7773 fmtId(tbinfo->dobj.name));
7774 appendPQExpBuffer(q, " ON %s;\n",
7775 fmtId(indxinfo->dobj.name));
7779 * DROP must be fully qualified in case same name appears in
7782 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
7783 fmtId(tbinfo->dobj.namespace->dobj.name));
7784 appendPQExpBuffer(delq, "%s ",
7785 fmtId(tbinfo->dobj.name));
7786 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7787 fmtId(coninfo->dobj.name));
7789 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7791 tbinfo->dobj.namespace->dobj.name,
7792 indxinfo->tablespace,
7793 tbinfo->rolname, false,
7794 "CONSTRAINT", q->data, delq->data, NULL,
7795 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7798 else if (coninfo->contype == 'f')
7801 * XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so that the
7802 * current table data is not processed
7804 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
7805 fmtId(tbinfo->dobj.name));
7806 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
7807 fmtId(coninfo->dobj.name),
7811 * DROP must be fully qualified in case same name appears in
7814 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
7815 fmtId(tbinfo->dobj.namespace->dobj.name));
7816 appendPQExpBuffer(delq, "%s ",
7817 fmtId(tbinfo->dobj.name));
7818 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7819 fmtId(coninfo->dobj.name));
7821 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7823 tbinfo->dobj.namespace->dobj.name,
7825 tbinfo->rolname, false,
7826 "FK CONSTRAINT", q->data, delq->data, NULL,
7827 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7830 else if (coninfo->contype == 'c' && tbinfo)
7832 /* CHECK constraint on a table */
7834 /* Ignore if not to be dumped separately */
7835 if (coninfo->separate)
7837 /* not ONLY since we want it to propagate to children */
7838 appendPQExpBuffer(q, "ALTER TABLE %s\n",
7839 fmtId(tbinfo->dobj.name));
7840 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
7841 fmtId(coninfo->dobj.name),
7845 * DROP must be fully qualified in case same name appears in
7848 appendPQExpBuffer(delq, "ALTER TABLE %s.",
7849 fmtId(tbinfo->dobj.namespace->dobj.name));
7850 appendPQExpBuffer(delq, "%s ",
7851 fmtId(tbinfo->dobj.name));
7852 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7853 fmtId(coninfo->dobj.name));
7855 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7857 tbinfo->dobj.namespace->dobj.name,
7859 tbinfo->rolname, false,
7860 "CHECK CONSTRAINT", q->data, delq->data, NULL,
7861 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7865 else if (coninfo->contype == 'c' && tbinfo == NULL)
7867 /* CHECK constraint on a domain */
7868 TypeInfo *tinfo = coninfo->condomain;
7870 /* Ignore if not to be dumped separately */
7871 if (coninfo->separate)
7873 appendPQExpBuffer(q, "ALTER DOMAIN %s\n",
7874 fmtId(tinfo->dobj.name));
7875 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
7876 fmtId(coninfo->dobj.name),
7880 * DROP must be fully qualified in case same name appears in
7883 appendPQExpBuffer(delq, "ALTER DOMAIN %s.",
7884 fmtId(tinfo->dobj.namespace->dobj.name));
7885 appendPQExpBuffer(delq, "%s ",
7886 fmtId(tinfo->dobj.name));
7887 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7888 fmtId(coninfo->dobj.name));
7890 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7892 tinfo->dobj.namespace->dobj.name,
7894 tinfo->rolname, false,
7895 "CHECK CONSTRAINT", q->data, delq->data, NULL,
7896 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7902 write_msg(NULL, "unrecognized constraint type: %c\n", coninfo->contype);
7906 /* Dump Constraint Comments --- only works for table constraints */
7907 if (tbinfo && coninfo->separate)
7908 dumpTableConstraintComment(fout, coninfo);
7910 destroyPQExpBuffer(q);
7911 destroyPQExpBuffer(delq);
7915 * dumpTableConstraintComment --- dump a constraint's comment if any
7917 * This is split out because we need the function in two different places
7918 * depending on whether the constraint is dumped as part of CREATE TABLE
7919 * or as a separate ALTER command.
7922 dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo)
7924 TableInfo *tbinfo = coninfo->contable;
7925 PQExpBuffer q = createPQExpBuffer();
7927 appendPQExpBuffer(q, "CONSTRAINT %s ",
7928 fmtId(coninfo->dobj.name));
7929 appendPQExpBuffer(q, "ON %s",
7930 fmtId(tbinfo->dobj.name));
7931 dumpComment(fout, q->data,
7932 tbinfo->dobj.namespace->dobj.name,
7934 coninfo->dobj.catId, 0,
7935 coninfo->separate ? coninfo->dobj.dumpId : tbinfo->dobj.dumpId);
7937 destroyPQExpBuffer(q);
7941 * findLastBuiltInOid -
7942 * find the last built in oid
7944 * For 7.1 and 7.2, we do this by retrieving datlastsysoid from the
7945 * pg_database entry for the current database
7948 findLastBuiltinOid_V71(const char *dbname)
7953 PQExpBuffer query = createPQExpBuffer();
7955 resetPQExpBuffer(query);
7956 appendPQExpBuffer(query, "SELECT datlastsysoid from pg_database where datname = ");
7957 appendStringLiteralAH(query, dbname, g_fout);
7959 res = PQexec(g_conn, query->data);
7960 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7962 ntups = PQntuples(res);
7965 write_msg(NULL, "missing pg_database entry for this database\n");
7970 write_msg(NULL, "found more than one pg_database entry for this database\n");
7973 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "datlastsysoid")));
7975 destroyPQExpBuffer(query);
7980 * findLastBuiltInOid -
7981 * find the last built in oid
7983 * For 7.0, we do this by assuming that the last thing that initdb does is to
7984 * create the pg_indexes view. This sucks in general, but seeing that 7.0.x
7985 * initdb won't be changing anymore, it'll do.
7988 findLastBuiltinOid_V70(void)
7994 res = PQexec(g_conn,
7995 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'");
7996 check_sql_result(res, g_conn,
7997 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'",
7999 ntups = PQntuples(res);
8002 write_msg(NULL, "could not find entry for pg_indexes in pg_class\n");
8007 write_msg(NULL, "found more than one entry for pg_indexes in pg_class\n");
8010 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "oid")));
8016 dumpSequence(Archive *fout, TableInfo *tbinfo)
8028 PQExpBuffer query = createPQExpBuffer();
8029 PQExpBuffer delqry = createPQExpBuffer();
8031 /* Make sure we are in proper schema */
8032 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
8034 snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE);
8035 snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE);
8037 appendPQExpBuffer(query,
8038 "SELECT sequence_name, last_value, increment_by, "
8039 "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
8040 " WHEN increment_by < 0 AND max_value = -1 THEN NULL "
8042 "END AS max_value, "
8043 "CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
8044 " WHEN increment_by < 0 AND min_value = %s THEN NULL "
8046 "END AS min_value, "
8047 "cache_value, is_cycled, is_called from %s",
8049 fmtId(tbinfo->dobj.name));
8051 res = PQexec(g_conn, query->data);
8052 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
8054 if (PQntuples(res) != 1)
8056 write_msg(NULL, "query to get data of sequence \"%s\" returned %d rows (expected 1)\n",
8057 tbinfo->dobj.name, PQntuples(res));
8061 /* Disable this check: it fails if sequence has been renamed */
8063 if (strcmp(PQgetvalue(res, 0, 0), tbinfo->dobj.name) != 0)
8065 write_msg(NULL, "query to get data of sequence \"%s\" returned name \"%s\"\n",
8066 tbinfo->dobj.name, PQgetvalue(res, 0, 0));
8071 last = PQgetvalue(res, 0, 1);
8072 incby = PQgetvalue(res, 0, 2);
8073 if (!PQgetisnull(res, 0, 3))
8074 maxv = PQgetvalue(res, 0, 3);
8075 if (!PQgetisnull(res, 0, 4))
8076 minv = PQgetvalue(res, 0, 4);
8077 cache = PQgetvalue(res, 0, 5);
8078 cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
8079 called = (strcmp(PQgetvalue(res, 0, 7), "t") == 0);
8082 * The logic we use for restoring sequences is as follows:
8084 * Add a CREATE SEQUENCE statement as part of a "schema" dump
8085 * (use last_val for start if called is false, else use min_val for
8086 * start_val). Also, if the sequence is owned by a column, add an
8087 * ALTER SEQUENCE SET OWNED command for it.
8089 * Add a 'SETVAL(seq, last_val, iscalled)' as part of a "data" dump.
8093 resetPQExpBuffer(delqry);
8096 * DROP must be fully qualified in case same name appears in
8099 appendPQExpBuffer(delqry, "DROP SEQUENCE %s.",
8100 fmtId(tbinfo->dobj.namespace->dobj.name));
8101 appendPQExpBuffer(delqry, "%s;\n",
8102 fmtId(tbinfo->dobj.name));
8104 resetPQExpBuffer(query);
8105 appendPQExpBuffer(query,
8106 "CREATE SEQUENCE %s\n",
8107 fmtId(tbinfo->dobj.name));
8110 appendPQExpBuffer(query, " START WITH %s\n", last);
8112 appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
8115 appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
8117 appendPQExpBuffer(query, " NO MAXVALUE\n");
8120 appendPQExpBuffer(query, " MINVALUE %s\n", minv);
8122 appendPQExpBuffer(query, " NO MINVALUE\n");
8124 appendPQExpBuffer(query,
8126 cache, (cycled ? "\n CYCLE" : ""));
8128 appendPQExpBuffer(query, ";\n");
8130 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
8132 tbinfo->dobj.namespace->dobj.name,
8135 false, "SEQUENCE", query->data, delqry->data, NULL,
8136 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
8140 * If the sequence is owned by a table column, emit the ALTER for it
8141 * as a separate TOC entry immediately following the sequence's own
8142 * entry. It's OK to do this rather than using full sorting logic,
8143 * because the dependency that tells us it's owned will have forced
8144 * the table to be created first. We can't just include the ALTER
8145 * in the TOC entry because it will fail if we haven't reassigned
8146 * the sequence owner to match the table's owner.
8148 * We need not schema-qualify the table reference because both
8149 * sequence and table must be in the same schema.
8151 if (OidIsValid(tbinfo->owning_tab))
8153 TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab);
8157 resetPQExpBuffer(query);
8158 appendPQExpBuffer(query, "ALTER SEQUENCE %s",
8159 fmtId(tbinfo->dobj.name));
8160 appendPQExpBuffer(query, " OWNED BY %s",
8161 fmtId(owning_tab->dobj.name));
8162 appendPQExpBuffer(query, ".%s;\n",
8163 fmtId(owning_tab->attnames[tbinfo->owning_col - 1]));
8165 ArchiveEntry(fout, nilCatalogId, createDumpId(),
8167 tbinfo->dobj.namespace->dobj.name,
8170 false, "SEQUENCE OWNED BY", query->data, "", NULL,
8171 &(tbinfo->dobj.dumpId), 1,
8176 /* Dump Sequence Comments */
8177 resetPQExpBuffer(query);
8178 appendPQExpBuffer(query, "SEQUENCE %s", fmtId(tbinfo->dobj.name));
8179 dumpComment(fout, query->data,
8180 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
8181 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
8186 resetPQExpBuffer(query);
8187 appendPQExpBuffer(query, "SELECT pg_catalog.setval(");
8188 appendStringLiteralAH(query, fmtId(tbinfo->dobj.name), fout);
8189 appendPQExpBuffer(query, ", %s, %s);\n",
8190 last, (called ? "true" : "false"));
8192 ArchiveEntry(fout, nilCatalogId, createDumpId(),
8194 tbinfo->dobj.namespace->dobj.name,
8197 false, "SEQUENCE SET", query->data, "", NULL,
8198 &(tbinfo->dobj.dumpId), 1,
8204 destroyPQExpBuffer(query);
8205 destroyPQExpBuffer(delqry);
8209 dumpTrigger(Archive *fout, TriggerInfo *tginfo)
8211 TableInfo *tbinfo = tginfo->tgtable;
8220 query = createPQExpBuffer();
8221 delqry = createPQExpBuffer();
8224 * DROP must be fully qualified in case same name appears in pg_catalog
8226 appendPQExpBuffer(delqry, "DROP TRIGGER %s ",
8227 fmtId(tginfo->dobj.name));
8228 appendPQExpBuffer(delqry, "ON %s.",
8229 fmtId(tbinfo->dobj.namespace->dobj.name));
8230 appendPQExpBuffer(delqry, "%s;\n",
8231 fmtId(tbinfo->dobj.name));
8233 if (tginfo->tgisconstraint)
8235 appendPQExpBuffer(query, "CREATE CONSTRAINT TRIGGER ");
8236 appendPQExpBufferStr(query, fmtId(tginfo->tgconstrname));
8240 appendPQExpBuffer(query, "CREATE TRIGGER ");
8241 appendPQExpBufferStr(query, fmtId(tginfo->dobj.name));
8243 appendPQExpBuffer(query, "\n ");
8247 if (TRIGGER_FOR_BEFORE(tginfo->tgtype))
8248 appendPQExpBuffer(query, "BEFORE");
8250 appendPQExpBuffer(query, "AFTER");
8251 if (TRIGGER_FOR_INSERT(tginfo->tgtype))
8253 appendPQExpBuffer(query, " INSERT");
8256 if (TRIGGER_FOR_DELETE(tginfo->tgtype))
8259 appendPQExpBuffer(query, " OR DELETE");
8261 appendPQExpBuffer(query, " DELETE");
8264 if (TRIGGER_FOR_UPDATE(tginfo->tgtype))
8267 appendPQExpBuffer(query, " OR UPDATE");
8269 appendPQExpBuffer(query, " UPDATE");
8271 appendPQExpBuffer(query, " ON %s\n",
8272 fmtId(tbinfo->dobj.name));
8274 if (tginfo->tgisconstraint)
8276 if (OidIsValid(tginfo->tgconstrrelid))
8278 /* If we are using regclass, name is already quoted */
8279 if (g_fout->remoteVersion >= 70300)
8280 appendPQExpBuffer(query, " FROM %s\n ",
8281 tginfo->tgconstrrelname);
8283 appendPQExpBuffer(query, " FROM %s\n ",
8284 fmtId(tginfo->tgconstrrelname));
8286 if (!tginfo->tgdeferrable)
8287 appendPQExpBuffer(query, "NOT ");
8288 appendPQExpBuffer(query, "DEFERRABLE INITIALLY ");
8289 if (tginfo->tginitdeferred)
8290 appendPQExpBuffer(query, "DEFERRED\n");
8292 appendPQExpBuffer(query, "IMMEDIATE\n");
8295 if (TRIGGER_FOR_ROW(tginfo->tgtype))
8296 appendPQExpBuffer(query, " FOR EACH ROW\n ");
8298 appendPQExpBuffer(query, " FOR EACH STATEMENT\n ");
8300 /* In 7.3, result of regproc is already quoted */
8301 if (g_fout->remoteVersion >= 70300)
8302 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
8305 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
8306 fmtId(tginfo->tgfname));
8309 for (findx = 0; findx < tginfo->tgnargs; findx++)
8313 /* Set 'p' to end of arg string. marked by '\000' */
8316 p = strchr(p, '\\');
8319 write_msg(NULL, "invalid argument string (%s) for trigger \"%s\" on table \"%s\"\n",
8326 if (*p == '\\') /* is it '\\'? */
8331 if (p[0] == '0' && p[1] == '0' && p[2] == '0') /* is it '\000'? */
8336 appendPQExpBufferChar(query, '\'');
8340 appendPQExpBufferChar(query, '\'');
8342 * bytea unconditionally doubles backslashes, so we suppress
8343 * the doubling for standard_conforming_strings.
8345 if (fout->std_strings && *s == '\\' && s[1] == '\\')
8347 appendPQExpBufferChar(query, *s++);
8349 appendPQExpBufferChar(query, '\'');
8350 appendPQExpBuffer(query,
8351 (findx < tginfo->tgnargs - 1) ? ", " : "");
8354 appendPQExpBuffer(query, ");\n");
8356 if (!tginfo->tgenabled)
8358 appendPQExpBuffer(query, "\nALTER TABLE %s ",
8359 fmtId(tbinfo->dobj.name));
8360 appendPQExpBuffer(query, "DISABLE TRIGGER %s;\n",
8361 fmtId(tginfo->dobj.name));
8364 ArchiveEntry(fout, tginfo->dobj.catId, tginfo->dobj.dumpId,
8366 tbinfo->dobj.namespace->dobj.name,
8368 tbinfo->rolname, false,
8369 "TRIGGER", query->data, delqry->data, NULL,
8370 tginfo->dobj.dependencies, tginfo->dobj.nDeps,
8373 resetPQExpBuffer(query);
8374 appendPQExpBuffer(query, "TRIGGER %s ",
8375 fmtId(tginfo->dobj.name));
8376 appendPQExpBuffer(query, "ON %s",
8377 fmtId(tbinfo->dobj.name));
8379 dumpComment(fout, query->data,
8380 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
8381 tginfo->dobj.catId, 0, tginfo->dobj.dumpId);
8383 destroyPQExpBuffer(query);
8384 destroyPQExpBuffer(delqry);
8392 dumpRule(Archive *fout, RuleInfo *rinfo)
8394 TableInfo *tbinfo = rinfo->ruletable;
8400 /* Skip if not to be dumped */
8401 if (!rinfo->dobj.dump || dataOnly)
8405 * If it is an ON SELECT rule that is created implicitly by CREATE VIEW,
8406 * we do not want to dump it as a separate object.
8408 if (!rinfo->separate)
8412 * Make sure we are in proper schema.
8414 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
8416 query = createPQExpBuffer();
8417 cmd = createPQExpBuffer();
8418 delcmd = createPQExpBuffer();
8420 if (g_fout->remoteVersion >= 70300)
8422 appendPQExpBuffer(query,
8423 "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid) AS definition",
8424 rinfo->dobj.catId.oid);
8428 /* Rule name was unique before 7.3 ... */
8429 appendPQExpBuffer(query,
8430 "SELECT pg_get_ruledef('%s') AS definition",
8434 res = PQexec(g_conn, query->data);
8435 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
8437 if (PQntuples(res) != 1)
8439 write_msg(NULL, "query to get rule \"%s\" for table \"%s\" failed: wrong number of rows returned",
8440 rinfo->dobj.name, tbinfo->dobj.name);
8444 printfPQExpBuffer(cmd, "%s\n", PQgetvalue(res, 0, 0));
8447 * DROP must be fully qualified in case same name appears in pg_catalog
8449 appendPQExpBuffer(delcmd, "DROP RULE %s ",
8450 fmtId(rinfo->dobj.name));
8451 appendPQExpBuffer(delcmd, "ON %s.",
8452 fmtId(tbinfo->dobj.namespace->dobj.name));
8453 appendPQExpBuffer(delcmd, "%s;\n",
8454 fmtId(tbinfo->dobj.name));
8456 ArchiveEntry(fout, rinfo->dobj.catId, rinfo->dobj.dumpId,
8458 tbinfo->dobj.namespace->dobj.name,
8460 tbinfo->rolname, false,
8461 "RULE", cmd->data, delcmd->data, NULL,
8462 rinfo->dobj.dependencies, rinfo->dobj.nDeps,
8465 /* Dump rule comments */
8466 resetPQExpBuffer(query);
8467 appendPQExpBuffer(query, "RULE %s",
8468 fmtId(rinfo->dobj.name));
8469 appendPQExpBuffer(query, " ON %s",
8470 fmtId(tbinfo->dobj.name));
8471 dumpComment(fout, query->data,
8472 tbinfo->dobj.namespace->dobj.name,
8474 rinfo->dobj.catId, 0, rinfo->dobj.dumpId);
8478 destroyPQExpBuffer(query);
8479 destroyPQExpBuffer(cmd);
8480 destroyPQExpBuffer(delcmd);
8484 * getDependencies --- obtain available dependency data
8487 getDependencies(void)
8498 DumpableObject *dobj,
8501 /* No dependency info available before 7.3 */
8502 if (g_fout->remoteVersion < 70300)
8506 write_msg(NULL, "reading dependency data\n");
8508 /* Make sure we are in proper schema */
8509 selectSourceSchema("pg_catalog");
8511 query = createPQExpBuffer();
8513 appendPQExpBuffer(query, "SELECT "
8514 "classid, objid, refclassid, refobjid, deptype "
8516 "WHERE deptype != 'p' "
8519 res = PQexec(g_conn, query->data);
8520 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
8522 ntups = PQntuples(res);
8524 i_classid = PQfnumber(res, "classid");
8525 i_objid = PQfnumber(res, "objid");
8526 i_refclassid = PQfnumber(res, "refclassid");
8527 i_refobjid = PQfnumber(res, "refobjid");
8528 i_deptype = PQfnumber(res, "deptype");
8531 * Since we ordered the SELECT by referencing ID, we can expect that
8532 * multiple entries for the same object will appear together; this saves
8537 for (i = 0; i < ntups; i++)
8543 objId.tableoid = atooid(PQgetvalue(res, i, i_classid));
8544 objId.oid = atooid(PQgetvalue(res, i, i_objid));
8545 refobjId.tableoid = atooid(PQgetvalue(res, i, i_refclassid));
8546 refobjId.oid = atooid(PQgetvalue(res, i, i_refobjid));
8547 deptype = *(PQgetvalue(res, i, i_deptype));
8550 dobj->catId.tableoid != objId.tableoid ||
8551 dobj->catId.oid != objId.oid)
8552 dobj = findObjectByCatalogId(objId);
8555 * Failure to find objects mentioned in pg_depend is not unexpected,
8556 * since for example we don't collect info about TOAST tables.
8561 fprintf(stderr, "no referencing object %u %u\n",
8562 objId.tableoid, objId.oid);
8567 refdobj = findObjectByCatalogId(refobjId);
8569 if (refdobj == NULL)
8572 fprintf(stderr, "no referenced object %u %u\n",
8573 refobjId.tableoid, refobjId.oid);
8579 * Ordinarily, table rowtypes have implicit dependencies on their
8580 * tables. However, for a composite type the implicit dependency goes
8581 * the other way in pg_depend; which is the right thing for DROP but
8582 * it doesn't produce the dependency ordering we need. So in that one
8583 * case, we reverse the direction of the dependency.
8585 if (deptype == 'i' &&
8586 dobj->objType == DO_TABLE &&
8587 refdobj->objType == DO_TYPE)
8588 addObjectDependency(refdobj, dobj->dumpId);
8591 addObjectDependency(dobj, refdobj->dumpId);
8596 destroyPQExpBuffer(query);
8601 * selectSourceSchema - make the specified schema the active search path
8602 * in the source database.
8604 * NB: pg_catalog is explicitly searched after the specified schema;
8605 * so user names are only qualified if they are cross-schema references,
8606 * and system names are only qualified if they conflict with a user name
8607 * in the current schema.
8609 * Whenever the selected schema is not pg_catalog, be careful to qualify
8610 * references to system catalogs and types in our emitted commands!
8613 selectSourceSchema(const char *schemaName)
8615 static char *curSchemaName = NULL;
8618 /* Not relevant if fetching from pre-7.3 DB */
8619 if (g_fout->remoteVersion < 70300)
8621 /* Ignore null schema names */
8622 if (schemaName == NULL || *schemaName == '\0')
8624 /* Optimize away repeated selection of same schema */
8625 if (curSchemaName && strcmp(curSchemaName, schemaName) == 0)
8628 query = createPQExpBuffer();
8629 appendPQExpBuffer(query, "SET search_path = %s",
8631 if (strcmp(schemaName, "pg_catalog") != 0)
8632 appendPQExpBuffer(query, ", pg_catalog");
8634 do_sql_command(g_conn, query->data);
8636 destroyPQExpBuffer(query);
8638 free(curSchemaName);
8639 curSchemaName = strdup(schemaName);
8643 * getFormattedTypeName - retrieve a nicely-formatted type name for the
8646 * NB: in 7.3 and up the result may depend on the currently-selected
8647 * schema; this is why we don't try to cache the names.
8650 getFormattedTypeName(Oid oid, OidOptions opts)
8659 if ((opts & zeroAsOpaque) != 0)
8660 return strdup(g_opaque_type);
8661 else if ((opts & zeroAsAny) != 0)
8662 return strdup("'any'");
8663 else if ((opts & zeroAsStar) != 0)
8665 else if ((opts & zeroAsNone) != 0)
8666 return strdup("NONE");
8669 query = createPQExpBuffer();
8670 if (g_fout->remoteVersion >= 70300)
8672 appendPQExpBuffer(query, "SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
8675 else if (g_fout->remoteVersion >= 70100)
8677 appendPQExpBuffer(query, "SELECT format_type('%u'::oid, NULL)",
8682 appendPQExpBuffer(query, "SELECT typname "
8684 "WHERE oid = '%u'::oid",
8688 res = PQexec(g_conn, query->data);
8689 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
8691 /* Expecting a single result only */
8692 ntups = PQntuples(res);
8695 write_msg(NULL, "query yielded %d rows instead of one: %s\n",
8696 ntups, query->data);
8700 if (g_fout->remoteVersion >= 70100)
8702 /* already quoted */
8703 result = strdup(PQgetvalue(res, 0, 0));
8707 /* may need to quote it */
8708 result = strdup(fmtId(PQgetvalue(res, 0, 0)));
8712 destroyPQExpBuffer(query);
8718 * myFormatType --- local implementation of format_type for use with 7.0.
8721 myFormatType(const char *typname, int32 typmod)
8724 bool isarray = false;
8725 PQExpBuffer buf = createPQExpBuffer();
8727 /* Handle array types */
8728 if (typname[0] == '_')
8734 /* Show lengths on bpchar and varchar */
8735 if (!strcmp(typname, "bpchar"))
8737 int len = (typmod - VARHDRSZ);
8739 appendPQExpBuffer(buf, "character");
8741 appendPQExpBuffer(buf, "(%d)",
8744 else if (!strcmp(typname, "varchar"))
8746 appendPQExpBuffer(buf, "character varying");
8748 appendPQExpBuffer(buf, "(%d)",
8751 else if (!strcmp(typname, "numeric"))
8753 appendPQExpBuffer(buf, "numeric");
8760 tmp_typmod = typmod - VARHDRSZ;
8761 precision = (tmp_typmod >> 16) & 0xffff;
8762 scale = tmp_typmod & 0xffff;
8763 appendPQExpBuffer(buf, "(%d,%d)",
8769 * char is an internal single-byte data type; Let's make sure we force it
8770 * through with quotes. - thomas 1998-12-13
8772 else if (strcmp(typname, "char") == 0)
8773 appendPQExpBuffer(buf, "\"char\"");
8775 appendPQExpBuffer(buf, "%s", fmtId(typname));
8777 /* Append array qualifier for array types */
8779 appendPQExpBuffer(buf, "[]");
8781 result = strdup(buf->data);
8782 destroyPQExpBuffer(buf);
8788 * fmtQualifiedId - convert a qualified name to the proper format for
8789 * the source database.
8791 * Like fmtId, use the result before calling again.
8794 fmtQualifiedId(const char *schema, const char *id)
8796 static PQExpBuffer id_return = NULL;
8798 if (id_return) /* first time through? */
8799 resetPQExpBuffer(id_return);
8801 id_return = createPQExpBuffer();
8803 /* Suppress schema name if fetching from pre-7.3 DB */
8804 if (g_fout->remoteVersion >= 70300 && schema && *schema)
8806 appendPQExpBuffer(id_return, "%s.",
8809 appendPQExpBuffer(id_return, "%s",
8812 return id_return->data;
8816 * Return a column list clause for the given relation.
8818 * Special case: if there are no undropped columns in the relation, return
8819 * "", not an invalid "()" column list.
8822 fmtCopyColumnList(const TableInfo *ti)
8824 static PQExpBuffer q = NULL;
8825 int numatts = ti->numatts;
8826 char **attnames = ti->attnames;
8827 bool *attisdropped = ti->attisdropped;
8831 if (q) /* first time through? */
8832 resetPQExpBuffer(q);
8834 q = createPQExpBuffer();
8836 appendPQExpBuffer(q, "(");
8838 for (i = 0; i < numatts; i++)
8840 if (attisdropped[i])
8843 appendPQExpBuffer(q, ", ");
8844 appendPQExpBuffer(q, "%s", fmtId(attnames[i]));
8849 return ""; /* no undropped columns */
8851 appendPQExpBuffer(q, ")");
8856 * Convenience subroutine to execute a SQL command and check for
8857 * COMMAND_OK status.
8860 do_sql_command(PGconn *conn, const char *query)
8864 res = PQexec(conn, query);
8865 check_sql_result(res, conn, query, PGRES_COMMAND_OK);
8870 * Convenience subroutine to verify a SQL command succeeded,
8871 * and exit with a useful error message if not.
8874 check_sql_result(PGresult *res, PGconn *conn, const char *query,
8875 ExecStatusType expected)
8879 if (res && PQresultStatus(res) == expected)
8882 write_msg(NULL, "SQL command failed\n");
8884 err = PQresultErrorMessage(res);
8886 err = PQerrorMessage(conn);
8887 write_msg(NULL, "Error message from server: %s", err);
8888 write_msg(NULL, "The command was: %s\n", query);