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, "e_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