2015-03-23 17:11 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:

> Hi
>
> 2015-03-15 16:09 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us>:
>
>> Pavel Stehule <pavel.steh...@gmail.com> writes:
>> > other variant, I hope better than previous. We can introduce new long
>> > option "--strict". With this active option, every pattern specified by
>> -t
>> > option have to have identifies exactly only one table. It can be used
>> for
>> > any other "should to exists" patterns - schemas. Initial implementation
>> in
>> > attachment.
>>
>> I think this design is seriously broken.  If I have '-t foo*' the code
>> should not prevent that from matching multiple tables.  What would the use
>> case for such a restriction be?
>>
>> What would make sense to me is one or both of these ideas:
>>
>> * require a match for a wildcard-free -t switch
>>
>> * require at least one (not "exactly one") match for a wildcarded -t
>>   switch.
>>
>
>
> attached initial implementation
>

updated version - same mechanism should be used for schema

Regards

Pavel


>
> Regards
>
> Pavel
>
>
>>
>> Neither of those is what you wrote, though.
>>
>> If we implemented the second one of these, it would have to be controlled
>> by a new switch, because there are plausible use cases for wildcards that
>> sometimes don't match anything (not to mention backwards compatibility).
>> There might be a reasonable argument for the first one being the
>> default behavior, though; I'm not sure if we could get away with that
>> from a compatibility perspective.
>>
>>                         regards, tom lane
>>
>
>
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
new file mode 100644
index d7506e1..47ae6b8
*** a/src/bin/pg_dump/dumputils.c
--- b/src/bin/pg_dump/dumputils.c
*************** bool
*** 983,989 ****
  processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
  					  bool have_where, bool force_escape,
  					  const char *schemavar, const char *namevar,
! 					  const char *altnamevar, const char *visibilityrule)
  {
  	PQExpBufferData schemabuf;
  	PQExpBufferData namebuf;
--- 983,990 ----
  processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
  					  bool have_where, bool force_escape,
  					  const char *schemavar, const char *namevar,
! 					  const char *altnamevar, const char *visibilityrule,
! 					  bool *with_wildcards)
  {
  	PQExpBufferData schemabuf;
  	PQExpBufferData namebuf;
*************** processSQLNamePattern(PGconn *conn, PQEx
*** 997,1002 ****
--- 998,1007 ----
  	(appendPQExpBufferStr(buf, have_where ? "  AND " : "WHERE "), \
  	 have_where = true, added_clause = true)
  
+ #define SET_WITH_WILDCARDS(b)	if (with_wildcards) *with_wildcards = b;
+ 
+ 	SET_WITH_WILDCARDS(false);
+ 
  	if (pattern == NULL)
  	{
  		/* Default: select all visible objects */
*************** processSQLNamePattern(PGconn *conn, PQEx
*** 1055,1065 ****
--- 1060,1074 ----
  		{
  			appendPQExpBufferStr(&namebuf, ".*");
  			cp++;
+ 
+ 			SET_WITH_WILDCARDS(true);
  		}
  		else if (!inquotes && ch == '?')
  		{
  			appendPQExpBufferChar(&namebuf, '.');
  			cp++;
+ 
+ 			SET_WITH_WILDCARDS(true);
  		}
  		else if (!inquotes && ch == '.')
  		{
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
new file mode 100644
index b176746..7fb7b62
*** a/src/bin/pg_dump/dumputils.h
--- b/src/bin/pg_dump/dumputils.h
*************** extern bool processSQLNamePattern(PGconn
*** 94,100 ****
  					  const char *pattern,
  					  bool have_where, bool force_escape,
  					  const char *schemavar, const char *namevar,
! 					  const char *altnamevar, const char *visibilityrule);
  extern void buildShSecLabelQuery(PGconn *conn, const char *catalog_name,
  					 uint32 objectId, PQExpBuffer sql);
  extern void emitShSecLabels(PGconn *conn, PGresult *res,
--- 94,101 ----
  					  const char *pattern,
  					  bool have_where, bool force_escape,
  					  const char *schemavar, const char *namevar,
! 					  const char *altnamevar, const char *visibilityrule,
! 					  bool *with_wildcards);
  extern void buildShSecLabelQuery(PGconn *conn, const char *catalog_name,
  					 uint32 objectId, PQExpBuffer sql);
  extern void emitShSecLabels(PGconn *conn, PGresult *res,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
new file mode 100644
index f24fefa..ff1e6a0
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** static SimpleStringList schema_include_p
*** 107,112 ****
--- 107,113 ----
  static SimpleOidList schema_include_oids = {NULL, NULL};
  static SimpleStringList schema_exclude_patterns = {NULL, NULL};
  static SimpleOidList schema_exclude_oids = {NULL, NULL};
+ static SimpleStringList schema_optional_patterns = {NULL, NULL};
  
  static SimpleStringList table_include_patterns = {NULL, NULL};
  static SimpleOidList table_include_oids = {NULL, NULL};
*************** static SimpleStringList table_exclude_pa
*** 114,119 ****
--- 115,121 ----
  static SimpleOidList table_exclude_oids = {NULL, NULL};
  static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
  static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
+ static SimpleStringList table_optional_patterns = {NULL, NULL};
  
  
  char		g_opaque_type[10];	/* name for the opaque type */
*************** static void setup_connection(Archive *AH
*** 129,138 ****
  				const char *dumpencoding, const char *dumpsnapshot,
  				char *use_role);
  static ArchiveFormat parseArchiveFormat(const char *format, ArchiveMode *mode);
! static void expand_schema_name_patterns(Archive *fout,
  							SimpleStringList *patterns,
  							SimpleOidList *oids);
! static void expand_table_name_patterns(Archive *fout,
  						   SimpleStringList *patterns,
  						   SimpleOidList *oids);
  static NamespaceInfo *findNamespace(Archive *fout, Oid nsoid, Oid objoid);
--- 131,140 ----
  				const char *dumpencoding, const char *dumpsnapshot,
  				char *use_role);
  static ArchiveFormat parseArchiveFormat(const char *format, ArchiveMode *mode);
! static void expand_schema_name_patterns(Archive *fout, bool check_patterns,
  							SimpleStringList *patterns,
  							SimpleOidList *oids);
! static void expand_table_name_patterns(Archive *fout, bool check_patterns,
  						   SimpleStringList *patterns,
  						   SimpleOidList *oids);
  static NamespaceInfo *findNamespace(Archive *fout, Oid nsoid, Oid objoid);
*************** main(int argc, char **argv)
*** 329,337 ****
--- 331,341 ----
  		{"no-tablespaces", no_argument, &dopt.outputNoTablespaces, 1},
  		{"quote-all-identifiers", no_argument, &quote_all_identifiers, 1},
  		{"role", required_argument, NULL, 3},
+ 		{"schema-if-exists", required_argument, NULL, 7},
  		{"section", required_argument, NULL, 5},
  		{"serializable-deferrable", no_argument, &dopt.serializable_deferrable, 1},
  		{"snapshot", required_argument, NULL, 6},
+ 		{"table-if-exists", required_argument, NULL, 8},
  		{"use-set-session-authorization", no_argument, &dopt.use_setsessauth, 1},
  		{"no-security-labels", no_argument, &dopt.no_security_labels, 1},
  		{"no-synchronized-snapshots", no_argument, &dopt.no_synchronized_snapshots, 1},
*************** main(int argc, char **argv)
*** 515,520 ****
--- 519,532 ----
  				dumpsnapshot = pg_strdup(optarg);
  				break;
  
+ 			case 7:				/* optional schema dump */
+ 				simple_string_list_append(&schema_optional_patterns, optarg);
+ 				break;
+ 
+ 			case 8:				/* optional table dump */
+ 				simple_string_list_append(&table_optional_patterns, optarg);
+ 				break;
+ 
  			default:
  				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
  				exit_nicely(1);
*************** main(int argc, char **argv)
*** 685,713 ****
  	}
  
  	/* Expand schema selection patterns into OID lists */
! 	if (schema_include_patterns.head != NULL)
  	{
! 		expand_schema_name_patterns(fout, &schema_include_patterns,
  									&schema_include_oids);
  		if (schema_include_oids.head == NULL)
  			exit_horribly(NULL, "No matching schemas were found\n");
  	}
! 	expand_schema_name_patterns(fout, &schema_exclude_patterns,
  								&schema_exclude_oids);
  	/* non-matching exclusion patterns aren't an error */
  
  	/* Expand table selection patterns into OID lists */
! 	if (table_include_patterns.head != NULL)
  	{
! 		expand_table_name_patterns(fout, &table_include_patterns,
  								   &table_include_oids);
  		if (table_include_oids.head == NULL)
  			exit_horribly(NULL, "No matching tables were found\n");
  	}
! 	expand_table_name_patterns(fout, &table_exclude_patterns,
  							   &table_exclude_oids);
  
! 	expand_table_name_patterns(fout, &tabledata_exclude_patterns,
  							   &tabledata_exclude_oids);
  
  	/* non-matching exclusion patterns aren't an error */
--- 697,733 ----
  	}
  
  	/* Expand schema selection patterns into OID lists */
! 	if (schema_include_patterns.head != NULL || schema_optional_patterns.head != NULL)
  	{
! 		expand_schema_name_patterns(fout, true, &schema_include_patterns,
! 									&schema_include_oids);
! 
! 		expand_schema_name_patterns(fout, false, &schema_optional_patterns,
  									&schema_include_oids);
+ 
  		if (schema_include_oids.head == NULL)
  			exit_horribly(NULL, "No matching schemas were found\n");
  	}
! 	expand_schema_name_patterns(fout, false, &schema_exclude_patterns,
  								&schema_exclude_oids);
  	/* non-matching exclusion patterns aren't an error */
  
  	/* Expand table selection patterns into OID lists */
! 	if (table_include_patterns.head != NULL || table_optional_patterns.head != NULL)
  	{
! 		expand_table_name_patterns(fout, true, &table_include_patterns,
! 								   &table_include_oids);
! 
! 		expand_table_name_patterns(fout, false, &table_optional_patterns,
  								   &table_include_oids);
+ 
  		if (table_include_oids.head == NULL)
  			exit_horribly(NULL, "No matching tables were found\n");
  	}
! 	expand_table_name_patterns(fout, false, &table_exclude_patterns,
  							   &table_exclude_oids);
  
! 	expand_table_name_patterns(fout, false, &tabledata_exclude_patterns,
  							   &tabledata_exclude_oids);
  
  	/* non-matching exclusion patterns aren't an error */
*************** help(const char *progname)
*** 900,908 ****
--- 920,930 ----
  	printf(_("  --no-tablespaces             do not dump tablespace assignments\n"));
  	printf(_("  --no-unlogged-table-data     do not dump unlogged table data\n"));
  	printf(_("  --quote-all-identifiers      quote all identifiers, even if not key words\n"));
+ 	printf(_("  --schema-if-exists=SCHEMA    optional dump of schema\n"));
  	printf(_("  --section=SECTION            dump named section (pre-data, data, or post-data)\n"));
  	printf(_("  --serializable-deferrable    wait until the dump can run without anomalies\n"));
  	printf(_("  --snapshot=SNAPSHOT          use given synchronous snapshot for the dump\n"));
+ 	printf(_("  --table-if-exists=TABLE      optional dump of table\n"));
  	printf(_("  --use-set-session-authorization\n"
  			 "                               use SET SESSION AUTHORIZATION commands instead of\n"
  			 "                               ALTER OWNER commands to set ownership\n"));
*************** parseArchiveFormat(const char *format, A
*** 1130,1144 ****
   * and append them to the given OID list.
   */
  static void
! expand_schema_name_patterns(Archive *fout,
  							SimpleStringList *patterns,
  							SimpleOidList *oids)
  {
  	PQExpBuffer query;
  	PGresult   *res;
  	SimpleStringListCell *cell;
  	int			i;
  
  	if (patterns->head == NULL)
  		return;					/* nothing to do */
  
--- 1152,1171 ----
   * and append them to the given OID list.
   */
  static void
! expand_schema_name_patterns(Archive *fout, bool check_patterns,
  							SimpleStringList *patterns,
  							SimpleOidList *oids)
  {
  	PQExpBuffer query;
+ 	PQExpBuffer query_nonempty_check;
+ 	PQExpBuffer filter;
  	PGresult   *res;
  	SimpleStringListCell *cell;
  	int			i;
  
+ #define SEARCH_SCHEMA_OID_QUERY_WITH_FILTER	\
+     "SELECT oid FROM pg_catalog.pg_namespace n\n%s"
+ 
  	if (patterns->head == NULL)
  		return;					/* nothing to do */
  
*************** expand_schema_name_patterns(Archive *fou
*** 1146,1151 ****
--- 1173,1180 ----
  		exit_horribly(NULL, "server version must be at least 7.3 to use schema selection switches\n");
  
  	query = createPQExpBuffer();
+ 	query_nonempty_check = createPQExpBuffer();
+ 	filter = createPQExpBuffer();
  
  	/*
  	 * We use UNION ALL rather than UNION; this might sometimes result in
*************** expand_schema_name_patterns(Archive *fou
*** 1154,1195 ****
  
  	for (cell = patterns->head; cell; cell = cell->next)
  	{
! 		if (cell != patterns->head)
! 			appendPQExpBufferStr(query, "UNION ALL\n");
! 		appendPQExpBuffer(query,
! 						  "SELECT oid FROM pg_catalog.pg_namespace n\n");
! 		processSQLNamePattern(GetConnection(fout), query, cell->val, false,
! 							  false, NULL, "n.nspname", NULL, NULL);
! 	}
  
! 	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
  
! 	for (i = 0; i < PQntuples(res); i++)
  	{
! 		simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
  	}
  
! 	PQclear(res);
  	destroyPQExpBuffer(query);
  }
  
  /*
   * Find the OIDs of all tables matching the given list of patterns,
   * and append them to the given OID list.
   */
  static void
! expand_table_name_patterns(Archive *fout,
  						   SimpleStringList *patterns, SimpleOidList *oids)
  {
  	PQExpBuffer query;
  	PGresult   *res;
  	SimpleStringListCell *cell;
  	int			i;
  
  	if (patterns->head == NULL)
  		return;					/* nothing to do */
  
  	query = createPQExpBuffer();
  
  	/*
  	 * We use UNION ALL rather than UNION; this might sometimes result in
--- 1183,1274 ----
  
  	for (cell = patterns->head; cell; cell = cell->next)
  	{
! 		bool	with_wildcards;
  
! 		processSQLNamePattern(GetConnection(fout), filter, cell->val, false,
! 							  false, NULL, "n.nspname", NULL, NULL, 
! 							  &with_wildcards);
  
! 		if (check_patterns && !with_wildcards)
! 		{
! 			appendPQExpBuffer(query_nonempty_check,
! 						  SEARCH_SCHEMA_OID_QUERY_WITH_FILTER,
! 						  filter->data);
! 
! 			res = ExecuteSqlQuery(fout, query_nonempty_check->data, PGRES_TUPLES_OK);
! 			if (PQntuples(res) == 0)
! 				exit_horribly(NULL, "No matching schema were found for \"%s\"\n", cell->val);
! 
! 			for (i = 0; i < PQntuples(res); i++)
! 			{
! 				simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
! 			}
! 
! 			PQclear(res);
! 			resetPQExpBuffer(query_nonempty_check);
! 		}
! 		else
! 		{
! 			if (cell != patterns->head)
! 				appendPQExpBufferStr(query, "UNION ALL\n");
! 			appendPQExpBuffer(query,
! 							  SEARCH_SCHEMA_OID_QUERY_WITH_FILTER,
! 							  filter->data);
! 		}
! 
! 		resetPQExpBuffer(filter);
! 	}
! 
! 	if (*query->data != '\0')
  	{
! 		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
! 
! 		for (i = 0; i < PQntuples(res); i++)
! 		{
! 			simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
! 		}
! 
! 		PQclear(res);
  	}
  
! 	destroyPQExpBuffer(filter);
! 	destroyPQExpBuffer(query_nonempty_check);
  	destroyPQExpBuffer(query);
  }
  
  /*
   * Find the OIDs of all tables matching the given list of patterns,
   * and append them to the given OID list.
+  *
+  * When check_patterns is true, then ensure any pattern without wildcards has to specify one or
+  * more tables.
   */
  static void
! expand_table_name_patterns(Archive *fout, bool	check_patterns,
  						   SimpleStringList *patterns, SimpleOidList *oids)
  {
  	PQExpBuffer query;
+ 	PQExpBuffer query_nonempty_check;
+ 	PQExpBuffer filter;
  	PGresult   *res;
  	SimpleStringListCell *cell;
  	int			i;
  
+ #define SEARCH_TABLE_OID_QUERY_WITH_FILTER	\
+     "SELECT c.oid" \
+ 	"\nFROM pg_catalog.pg_class c" \
+ 	"\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace" \
+ 	"\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n" \
+ 	"%s", \
+ 	RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW, \
+ 	RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE
+ 
  	if (patterns->head == NULL)
  		return;					/* nothing to do */
  
  	query = createPQExpBuffer();
+ 	query_nonempty_check = createPQExpBuffer();
+ 	filter = createPQExpBuffer();
  
  	/*
  	 * We use UNION ALL rather than UNION; this might sometimes result in
*************** expand_table_name_patterns(Archive *fout
*** 1198,1225 ****
  
  	for (cell = patterns->head; cell; cell = cell->next)
  	{
! 		if (cell != patterns->head)
! 			appendPQExpBufferStr(query, "UNION ALL\n");
! 		appendPQExpBuffer(query,
! 						  "SELECT c.oid"
! 						  "\nFROM pg_catalog.pg_class c"
! 		"\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
! 					 "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c')\n",
! 						  RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
! 						  RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
! 		processSQLNamePattern(GetConnection(fout), query, cell->val, true,
  							  false, "n.nspname", "c.relname", NULL,
! 							  "pg_catalog.pg_table_is_visible(c.oid)");
! 	}
  
! 	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
  
! 	for (i = 0; i < PQntuples(res); i++)
  	{
! 		simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
  	}
  
! 	PQclear(res);
  	destroyPQExpBuffer(query);
  }
  
--- 1277,1333 ----
  
  	for (cell = patterns->head; cell; cell = cell->next)
  	{
! 		bool	with_wildcards;
! 
! 		processSQLNamePattern(GetConnection(fout), filter, cell->val, true,
  							  false, "n.nspname", "c.relname", NULL,
! 							  "pg_catalog.pg_table_is_visible(c.oid)",
! 							  &with_wildcards);
  
! 		if (check_patterns && !with_wildcards)
! 		{
! 			appendPQExpBuffer(query_nonempty_check,
! 						  SEARCH_TABLE_OID_QUERY_WITH_FILTER,
! 						  filter->data);
  
! 			res = ExecuteSqlQuery(fout, query_nonempty_check->data, PGRES_TUPLES_OK);
! 			if (PQntuples(res) == 0)
! 				exit_horribly(NULL, "No matching table were found for \"%s\"\n", cell->val);
! 
! 			for (i = 0; i < PQntuples(res); i++)
! 			{
! 				simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
! 			}
! 
! 			PQclear(res);
! 			resetPQExpBuffer(query_nonempty_check);
! 		}
! 		else
! 		{
! 			if (cell != patterns->head)
! 				appendPQExpBufferStr(query, "UNION ALL\n");
! 			appendPQExpBuffer(query,
! 							  SEARCH_TABLE_OID_QUERY_WITH_FILTER,
! 							  filter->data);
! 		}
! 
! 		resetPQExpBuffer(filter);
! 	}
! 
! 	if (*query->data != '\0')
  	{
! 		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
! 
! 		for (i = 0; i < PQntuples(res); i++)
! 		{
! 			simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
! 		}
! 
! 		PQclear(res);
  	}
  
! 	destroyPQExpBuffer(filter);
! 	destroyPQExpBuffer(query_nonempty_check);
  	destroyPQExpBuffer(query);
  }
  
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to