pg_dump.c

PostgreSQL7.4.6 for Linux

						  "proargtypes[0] as aggbasetype, "
						  "(select usename from pg_user where proowner = usesysid) as usename, "
						  "proacl as aggacl "
						  "FROM pg_proc "
						  "WHERE proisagg "
						  "AND pronamespace != "
		  "(select oid from pg_namespace where nspname = 'pg_catalog')");
	}
	else
	{
		appendPQExpBuffer(query, "SELECT pg_aggregate.oid, aggname, "
						  "0::oid as aggnamespace, "
						  "aggbasetype, "
						  "(select usename from pg_user where aggowner = usesysid) as usename, "
						  "'{=X}' as aggacl "
						  "from pg_aggregate "
						  "where oid > '%u'::oid",
						  g_last_builtin_oid);
	}

	res = PQexec(g_conn, query->data);
	if (!res ||
		PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		write_msg(NULL, "query to obtain list of aggregate functions failed: %s",
				  PQerrorMessage(g_conn));
		exit_nicely();
	}

	ntups = PQntuples(res);
	*numAggs = ntups;

	agginfo = (AggInfo *) malloc(ntups * sizeof(AggInfo));

	i_oid = PQfnumber(res, "oid");
	i_aggname = PQfnumber(res, "aggname");
	i_aggnamespace = PQfnumber(res, "aggnamespace");
	i_aggbasetype = PQfnumber(res, "aggbasetype");
	i_usename = PQfnumber(res, "usename");
	i_aggacl = PQfnumber(res, "aggacl");

	for (i = 0; i < ntups; i++)
	{
		agginfo[i].oid = strdup(PQgetvalue(res, i, i_oid));
		agginfo[i].aggname = strdup(PQgetvalue(res, i, i_aggname));
		agginfo[i].aggnamespace = findNamespace(PQgetvalue(res, i, i_aggnamespace),
												agginfo[i].oid);
		agginfo[i].aggbasetype = strdup(PQgetvalue(res, i, i_aggbasetype));
		agginfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
		if (strlen(agginfo[i].usename) == 0)
			write_msg(NULL, "WARNING: owner of aggregate function \"%s\" appears to be invalid\n",
					  agginfo[i].aggname);
		agginfo[i].aggacl = strdup(PQgetvalue(res, i, i_aggacl));
		agginfo[i].anybasetype = false; /* computed when it's dumped */
		agginfo[i].fmtbasetype = NULL;	/* computed when it's dumped */
	}

	PQclear(res);

	destroyPQExpBuffer(query);

	return agginfo;
}

/*
 * getFuncs:
 *	  read all the user-defined functions in the system catalogs and
 * return them in the FuncInfo* structure
 *
 * numFuncs is set to the number of functions read in
 */
FuncInfo *
getFuncs(int *numFuncs)
{
	PGresult   *res;
	int			ntups;
	int			i;
	PQExpBuffer query = createPQExpBuffer();
	FuncInfo   *finfo;

	int			i_oid;
	int			i_proname;
	int			i_pronamespace;
	int			i_usename;
	int			i_prolang;
	int			i_pronargs;
	int			i_proargtypes;
	int			i_prorettype;
	int			i_proacl;

	/* Make sure we are in proper schema */
	selectSourceSchema("pg_catalog");

	/* find all user-defined funcs */

	if (g_fout->remoteVersion >= 70300)
	{
		appendPQExpBuffer(query,
						  "SELECT pg_proc.oid, proname, prolang, "
						  "pronargs, proargtypes, prorettype, proacl, "
						  "pronamespace, "
						  "(select usename from pg_user where proowner = usesysid) as usename "
						  "FROM pg_proc "
						  "WHERE NOT proisagg "
						  "AND pronamespace != "
		  "(select oid from pg_namespace where nspname = 'pg_catalog')");
	}
	else
	{
		appendPQExpBuffer(query,
						  "SELECT pg_proc.oid, proname, prolang, "
						  "pronargs, proargtypes, prorettype, "
						  "'{=X}' as proacl, "
						  "0::oid as pronamespace, "
						  "(select usename from pg_user where proowner = usesysid) as usename "
						  "FROM pg_proc "
						  "where pg_proc.oid > '%u'::oid",
						  g_last_builtin_oid);
	}

	res = PQexec(g_conn, query->data);
	if (!res ||
		PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		write_msg(NULL, "query to obtain list of functions failed: %s",
				  PQerrorMessage(g_conn));
		exit_nicely();
	}

	ntups = PQntuples(res);

	*numFuncs = ntups;

	finfo = (FuncInfo *) calloc(ntups, sizeof(FuncInfo));

	i_oid = PQfnumber(res, "oid");
	i_proname = PQfnumber(res, "proname");
	i_pronamespace = PQfnumber(res, "pronamespace");
	i_usename = PQfnumber(res, "usename");
	i_prolang = PQfnumber(res, "prolang");
	i_pronargs = PQfnumber(res, "pronargs");
	i_proargtypes = PQfnumber(res, "proargtypes");
	i_prorettype = PQfnumber(res, "prorettype");
	i_proacl = PQfnumber(res, "proacl");

	for (i = 0; i < ntups; i++)
	{
		finfo[i].oid = strdup(PQgetvalue(res, i, i_oid));
		finfo[i].proname = strdup(PQgetvalue(res, i, i_proname));
		finfo[i].pronamespace = findNamespace(PQgetvalue(res, i, i_pronamespace),
											  finfo[i].oid);
		finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
		finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
		finfo[i].prorettype = strdup(PQgetvalue(res, i, i_prorettype));
		finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
		finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
		if (finfo[i].nargs == 0)
			finfo[i].argtypes = NULL;
		else
		{
			finfo[i].argtypes = malloc(finfo[i].nargs * sizeof(finfo[i].argtypes[0]));
			parseNumericArray(PQgetvalue(res, i, i_proargtypes),
							  finfo[i].argtypes,
							  finfo[i].nargs);
		}

		finfo[i].dumped = false;

		if (strlen(finfo[i].usename) == 0)
			write_msg(NULL, "WARNING: owner of function \"%s\" appears to be invalid\n",
					  finfo[i].proname);
	}

	PQclear(res);

	destroyPQExpBuffer(query);

	return finfo;
}

/*
 * getTables
 *	  read all the user-defined tables (no indexes, no catalogs)
 * in the system catalogs return them in the TableInfo* structure
 *
 * numTables is set to the number of tables read in
 */
TableInfo *
getTables(int *numTables)
{
	PGresult   *res;
	int			ntups;
	int			i;
	PQExpBuffer query = createPQExpBuffer();
	PQExpBuffer delqry = createPQExpBuffer();
	PQExpBuffer lockquery = createPQExpBuffer();
	TableInfo  *tblinfo;

	int			i_reloid;
	int			i_relname;
	int			i_relnamespace;
	int			i_relkind;
	int			i_relacl;
	int			i_usename;
	int			i_relchecks;
	int			i_reltriggers;
	int			i_relhasindex;
	int			i_relhasrules;
	int			i_relhasoids;
	int			i_owning_tab;
	int			i_owning_col;

	/* Make sure we are in proper schema */
	selectSourceSchema("pg_catalog");

	/*
	 * Find all the tables (including views and sequences).
	 *
	 * We include system catalogs, so that we can work if a user table is
	 * defined to inherit from a system catalog (pretty weird, but...)
	 *
	 * We ignore tables that are not type 'r' (ordinary relation) or 'S'
	 * (sequence) or 'v' (view).
	 *
	 * Note: in this phase we should collect only a minimal amount of
	 * information about each table, basically just enough to decide if it
	 * is interesting.	We must fetch all tables in this phase because
	 * otherwise we cannot correctly identify inherited columns, serial
	 * columns, etc.
	 */

	if (g_fout->remoteVersion >= 70300)
	{
		/*
		 * Left join to pick up dependency info linking sequences to their
		 * serial column, if any
		 */
		appendPQExpBuffer(query,
						  "SELECT c.oid, relname, relacl, relkind, "
						  "relnamespace, "
						  "(select usename from pg_user where relowner = usesysid) as usename, "
						  "relchecks, reltriggers, "
						  "relhasindex, relhasrules, relhasoids, "
						  "d.refobjid as owning_tab, "
						  "d.refobjsubid as owning_col "
						  "from pg_class c "
						  "left join pg_depend d on "
						  "(c.relkind = '%c' and "
						"d.classid = c.tableoid and d.objid = c.oid and "
						  "d.objsubid = 0 and "
						"d.refclassid = c.tableoid and d.deptype = 'i') "
						  "where relkind in ('%c', '%c', '%c') "
						  "order by c.oid",
						  RELKIND_SEQUENCE,
					   RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
	}
	else if (g_fout->remoteVersion >= 70200)
	{
		appendPQExpBuffer(query,
						"SELECT pg_class.oid, relname, relacl, relkind, "
						  "0::oid as relnamespace, "
						  "(select usename from pg_user where relowner = usesysid) as usename, "
						  "relchecks, reltriggers, "
						  "relhasindex, relhasrules, relhasoids, "
						  "NULL::oid as owning_tab, "
						  "NULL::int4 as owning_col "
						  "from pg_class "
						  "where relkind in ('%c', '%c', '%c') "
						  "order by oid",
					   RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
	}
	else if (g_fout->remoteVersion >= 70100)
	{
		/* all tables have oids in 7.1 */
		appendPQExpBuffer(query,
						"SELECT pg_class.oid, relname, relacl, relkind, "
						  "0::oid as relnamespace, "
						  "(select usename from pg_user where relowner = usesysid) as usename, "
						  "relchecks, reltriggers, "
						  "relhasindex, relhasrules, "
						  "'t'::bool as relhasoids, "
						  "NULL::oid as owning_tab, "
						  "NULL::int4 as owning_col "
						  "from pg_class "
						  "where relkind in ('%c', '%c', '%c') "
						  "order by oid",
					   RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
	}
	else
	{
		/*
		 * Before 7.1, view relkind was not set to 'v', so we must check
		 * if we have a view by looking for a rule in pg_rewrite.
		 */
		appendPQExpBuffer(query,
						  "SELECT c.oid, relname, relacl, "
						  "CASE WHEN relhasrules and relkind = 'r' "
				  "  and EXISTS(SELECT rulename FROM pg_rewrite r WHERE "
				  "             r.ev_class = c.oid AND r.ev_type = '1') "
						  "THEN '%c'::\"char\" "
						  "ELSE relkind END AS relkind,"
						  "0::oid as relnamespace, "
						  "(select usename from pg_user where relowner = usesysid) as usename, "
						  "relchecks, reltriggers, "
						  "relhasindex, relhasrules, "
						  "'t'::bool as relhasoids, "
						  "NULL::oid as owning_tab, "
						  "NULL::int4 as owning_col "
						  "from pg_class c "
						  "where relkind in ('%c', '%c') "
						  "order by oid",
						  RELKIND_VIEW,
						  RELKIND_RELATION, RELKIND_SEQUENCE);
	}

	res = PQexec(g_conn, query->data);
	if (!res ||
		PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		write_msg(NULL, "query to obtain list of tables failed: %s",
				  PQerrorMessage(g_conn));
		exit_nicely();
	}

	ntups = PQntuples(res);

	*numTables = ntups;

	/*
	 * Extract data from result and lock dumpable tables.  We do the
	 * locking before anything else, to minimize the window wherein a
	 * table could disappear under us.
	 *
	 * Note that we have to save info about all tables here, even when
	 * dumping only one, because we don't yet know which tables might be
	 * inheritance ancestors of the target table.
	 */
	tblinfo = (TableInfo *) calloc(ntups, sizeof(TableInfo));

	i_reloid = PQfnumber(res, "oid");
	i_relname = PQfnumber(res, "relname");
	i_relnamespace = PQfnumber(res, "relnamespace");
	i_relacl = PQfnumber(res, "relacl");
	i_relkind = PQfnumber(res, "relkind");
	i_usename = PQfnumber(res, "usename");
	i_relchecks = PQfnumber(res, "relchecks");
	i_reltriggers = PQfnumber(res, "reltriggers");
	i_relhasindex = PQfnumber(res, "relhasindex");
	i_relhasrules = PQfnumber(res, "relhasrules");
	i_relhasoids = PQfnumber(res, "relhasoids");
	i_owning_tab = PQfnumber(res, "owning_tab");
	i_owning_col = PQfnumber(res, "owning_col");

	for (i = 0; i < ntups; i++)
	{
		tblinfo[i].oid = strdup(PQgetvalue(res, i, i_reloid));
		tblinfo[i].relname = strdup(PQgetvalue(res, i, i_relname));
		tblinfo[i].relnamespace = findNamespace(PQgetvalue(res, i, i_relnamespace),
												tblinfo[i].oid);
		tblinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
		tblinfo[i].relacl = strdup(PQgetvalue(res, i, i_relacl));
		tblinfo[i].relkind = *(PQgetvalue(res, i, i_relkind));
		tblinfo[i].hasindex = (strcmp(PQgetvalue(res, i, i_relhasindex), "t") == 0);
		tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
		tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0);
		tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
		tblinfo[i].ntrig = atoi(PQgetvalue(res, i, i_reltriggers));
		if (PQgetisnull(res, i, i_owning_tab))
		{
			tblinfo[i].owning_tab = NULL;
			tblinfo[i].owning_col = 0;
		}
		else
		{
			tblinfo[i].owning_tab = strdup(PQgetvalue(res, i, i_owning_tab));
			tblinfo[i].owning_col = atoi(PQgetvalue(res, i, i_owning_col));
		}

		/* other fields were zeroed above */

		/*
		 * Decide whether we want to dump this table.  Sequences owned by
		 * serial columns are never dumpable on their own; we will
		 * transpose their owning table's dump flag to them below.
		 */
		if (tblinfo[i].owning_tab == NULL)
			selectDumpableTable(&tblinfo[i]);
		else
			tblinfo[i].dump = false;
		tblinfo[i].interesting = tblinfo[i].dump;

		/*
		 * Read-lock target tables to make sure they aren't DROPPED or
		 * altered in schema before we get around to dumping them.
		 *
		 * Note that we don't explicitly lock parents of the target tables;
		 * we assume our lock on the child is enough to prevent schema
		 * alterations to parent tables.
		 *
		 * NOTE: it'd be kinda nice to lock views and sequences too, not only
		 * plain tables, but the backend doesn't presently allow that.
		 */
		if (tblinfo[i].dump && tblinfo[i].relkind == RELKIND_RELATION)
		{
			PGresult   *lres;

			resetPQExpBuffer(lockquery);
			appendPQExpBuffer(lockquery,
							  "LOCK TABLE %s IN ACCESS SHARE MODE",
						 fmtQualifiedId(tblinfo[i].relnamespace->nspname,
										tblinfo[i].relname));
			lres = PQexec(g_conn, lockquery->data);
			if (!lres || PQresultStatus(lres) != PGRES_COMMAND_OK)
			{
				write_msg(NULL, "attempt to lock table \"%s\" failed: %s",
						  tblinfo[i].relname, PQerrorMessage(g_conn));
				exit_nicely();
			}
			PQclear(lres);
		}

		/* Emit notice if join for owner failed */
		if (strlen(tblinfo[i].usename) == 0)
			write_msg(NULL, "WARNING: owner of table \"%s\" appears to be invalid\n",
					  tblinfo[i].relname);
	}

	/*
	 * If the user is attempting to dump a specific table, check to ensure
	 * that the specified table actually exists.  (This is a bit simplistic
	 * since we don't fully check the combination of -n and -t switches.)
	 */
	if (selectTableName)
	{
		for (i = 0; i < ntups; i++)
			if (strcmp(tblinfo[i].relname, selectTableName) == 0)
				break;

		/* Didn't find a match */
		if (i == ntups)
		{
			write_msg(NULL, "specified table \"%s\" does not exist\n",
					  selectTableName);
			exit_nicely();
		}
	}

	PQclear(res);
	destroyPQExpBuffer(query);
	destroyPQExpBuffer(delqry);
	destroyPQExpBuffer(lockquery);

	return tblinfo;
}

/*
 * getInherits
 *	  read all the inheritance information
 * from the system catalogs return them in the InhInfo* structure
 *
 * numInherits is set to the number of pairs read in
 */
InhInfo *
getInherits(int *numInherits)
{
	PGresult   *res;
	int			ntups;
	int			i;
	PQExpBuffer query = createPQExpBuffe