Skip to content

Commit ce68df4

Browse files
robertmhaasrobertmhaas
committed
Add options to force quoting of all identifiers.
I've added a quote_all_identifiers GUC which affects the behavior of the backend, and a --quote-all-identifiers argument to pg_dump and pg_dumpall which sets the GUC and also affects the quoting done internally by those applications. Design by Tom Lane; review by Alex Hunsaker; in response to bug #5488 filed by Hartmut Goebel.
1 parent b8c6c71 commit ce68df4

File tree

11 files changed

+85
-11
lines changed

11 files changed

+85
-11
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.298 2010/07/20 00:47:52 rhaas Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.299 2010/07/22 01:22:32 rhaas Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -5209,6 +5209,23 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
52095209
</listitem>
52105210
</varlistentry>
52115211

5212+
<varlistentry id="guc-quote-all-identifiers" xreflabel="quote-all-identifiers">
5213+
<term><varname>quote_all_identifiers</varname> (<type>boolean</type>)</term>
5214+
<indexterm>
5215+
<primary><varname>quote_all_identifiers</> configuration parameter</primary>
5216+
</indexterm>
5217+
<listitem>
5218+
<para>
5219+
When the database generates SQL, force all identifiers to be quoted,
5220+
even if they are not (currently) keywords. This will affect the
5221+
output of <command>EXPLAIN</> as well as the results of functions
5222+
like <function>pg_get_viewdef</>. See also the
5223+
<option>--quote-all-identifiers</option> to
5224+
<xref linkend="app-pgdump"> and <xref linkend="app-pg-dumpall">.
5225+
</para>
5226+
</listitem>
5227+
</varlistentry>
5228+
52125229
<varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
52135230
<term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
52145231
<indexterm>

‎doc/src/sgml/ref/pg_dump.sgml

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.117 2010/02/23 17:28:09 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.118 2010/07/22 01:22:33 rhaas Exp $
33
PostgreSQL documentation
44
-->
55

@@ -660,6 +660,17 @@ PostgreSQL documentation
660660
</listitem>
661661
</varlistentry>
662662

663+
<varlistentry>
664+
<term><option>--quote-all-identifiers</></term>
665+
<listitem>
666+
<para>
667+
Force quoting of all identifiers. This may be useful when dumping a
668+
database for migration to a future version that may have introduced
669+
additional keywords.
670+
</para>
671+
</listitem>
672+
</varlistentry>
673+
663674
<varlistentry>
664675
<term><option>-?</></term>
665676
<term><option>--help</></term>

‎doc/src/sgml/ref/pg_dumpall.sgml

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.82 2010/04/03 07:23:01 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.83 2010/07/22 01:22:33 rhaas Exp $
33
PostgreSQL documentation
44
-->
55

@@ -356,6 +356,17 @@ PostgreSQL documentation
356356
</listitem>
357357
</varlistentry>
358358

359+
<varlistentry>
360+
<term><option>--quote-all-identifiers</></term>
361+
<listitem>
362+
<para>
363+
Force quoting of all identifiers. This may be useful when dumping a
364+
database for migration to a future version that may have introduced
365+
additional keywords.
366+
</para>
367+
</listitem>
368+
</varlistentry>
369+
359370
<varlistentry>
360371
<term><option>-?</></term>
361372
<term><option>--help</></term>

‎src/backend/utils/adt/ruleutils.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.328 2010/07/13 20:57:19 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.329 2010/07/22 01:22:33 rhaas Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -132,6 +132,7 @@ static SPIPlanPtr plan_getrulebyoid = NULL;
132132
static const char *query_getrulebyoid = "SELECT * FROM pg_catalog.pg_rewrite WHERE oid = $1";
133133
static SPIPlanPtr plan_getviewrule = NULL;
134134
static const char *query_getviewrule = "SELECT * FROM pg_catalog.pg_rewrite WHERE ev_class = $1 AND rulename = $2";
135+
bool quote_all_identifiers;
135136

136137

137138
/* ----------
@@ -6727,6 +6728,9 @@ quote_identifier(const char *ident)
67276728
}
67286729
}
67296730

6731+
if (quote_all_identifiers)
6732+
safe = false;
6733+
67306734
if (safe)
67316735
{
67326736
/*

‎src/backend/utils/misc/guc.c

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Written by Peter Eisentraut <peter_e@gmx.net>.
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.564 2010/07/20 00:47:53 rhaas Exp $
13+
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.565 2010/07/22 01:22:33 rhaas Exp $
1414
*
1515
*--------------------------------------------------------------------
1616
*/
@@ -1282,6 +1282,15 @@ static struct config_bool ConfigureNamesBool[] =
12821282
false, NULL, NULL
12831283
},
12841284

1285+
{
1286+
{"quote_all_identifiers", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS,
1287+
gettext_noop("When generating SQL fragments, quote all identifiers."),
1288+
NULL,
1289+
},
1290+
&quote_all_identifiers,
1291+
false, NULL, NULL
1292+
},
1293+
12851294
/* End-of-list marker */
12861295
{
12871296
{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -510,6 +510,7 @@
510510
#default_with_oids = off
511511
#escape_string_warning = on
512512
#lo_compat_privileges = off
513+
#quote_all_identifiers = off
513514
#sql_inheritance = on
514515
#standard_conforming_strings = on
515516
#synchronize_seqscans = on

‎src/bin/pg_dump/dumputils.c

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.c,v 1.56 2010/03/03 20:10:48 heikki Exp $
11+
* $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.c,v 1.57 2010/07/22 01:22:34 rhaas Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -36,6 +36,8 @@ static bool parallel_init_done = false;
3636
static DWORD tls_index;
3737
#endif
3838

39+
int quote_all_identifiers;
40+
3941
void
4042
init_parallel_dump_utils(void)
4143
{
@@ -102,8 +104,10 @@ fmtId(const char *rawid)
102104
* These checks need to match the identifier production in scan.l. Don't
103105
* use islower() etc.
104106
*/
107+
if (quote_all_identifiers)
108+
need_quotes = true;
105109
/* slightly different rules for first character */
106-
if (!((rawid[0] >= 'a' && rawid[0] <= 'z') || rawid[0] == '_'))
110+
else if (!((rawid[0] >= 'a' && rawid[0] <= 'z') || rawid[0] == '_'))
107111
need_quotes = true;
108112
else
109113
{

‎src/bin/pg_dump/dumputils.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.h,v 1.29 2010/02/26 02:01:16 momjian Exp $
11+
* $PostgreSQL: pgsql/src/bin/pg_dump/dumputils.h,v 1.30 2010/07/22 01:22:34 rhaas Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -46,4 +46,6 @@ extern bool processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
4646
const char *schemavar, const char *namevar,
4747
const char *altnamevar, const char *visibilityrule);
4848

49+
extern int quote_all_identifiers;
50+
4951
#endif /* DUMPUTILS_H */

‎src/bin/pg_dump/pg_dump.c

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,7 @@
2525
* http://archives.postgresql.org/pgsql-bugs/2010-02/msg00187.php
2626
*
2727
* IDENTIFICATION
28-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.582 2010/07/14 21:21:08 tgl Exp $
28+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.583 2010/07/22 01:22:34 rhaas Exp $
2929
*
3030
*-------------------------------------------------------------------------
3131
*/
@@ -297,6 +297,7 @@ main(int argc, char **argv)
297297
{"inserts", no_argument, &dump_inserts, 1},
298298
{"lock-wait-timeout", required_argument, NULL, 2},
299299
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
300+
{"quote-all-identifiers", no_argument, &quote_all_identifiers, 1},
300301
{"role", required_argument, NULL, 3},
301302
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
302303

@@ -634,6 +635,12 @@ main(int argc, char **argv)
634635
if (g_fout->remoteVersion >= 70300)
635636
do_sql_command(g_conn, "SET statement_timeout = 0");
636637

638+
/*
639+
* Quote all identifiers, if requested.
640+
*/
641+
if (quote_all_identifiers && g_fout->remoteVersion >= 90100)
642+
do_sql_command(g_conn, "SET quote_all_identifiers = true");
643+
637644
/*
638645
* Start serializable transaction to dump consistent data.
639646
*/

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1994, Regents of the University of California
77
*
88
*
9-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.134 2010/02/26 02:01:17 momjian Exp $
9+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.135 2010/07/22 01:22:34 rhaas Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -130,6 +130,7 @@ main(int argc, char *argv[])
130130
{"inserts", no_argument, &inserts, 1},
131131
{"lock-wait-timeout", required_argument, NULL, 2},
132132
{"no-tablespaces", no_argument, &no_tablespaces, 1},
133+
{"quote-all-identifiers", no_argument, &quote_all_identifiers, 1},
133134
{"role", required_argument, NULL, 3},
134135
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
135136

@@ -328,6 +329,8 @@ main(int argc, char *argv[])
328329
appendPQExpBuffer(pgdumpopts, " --inserts");
329330
if (no_tablespaces)
330331
appendPQExpBuffer(pgdumpopts, " --no-tablespaces");
332+
if (quote_all_identifiers)
333+
appendPQExpBuffer(pgdumpopts, " --quote-all-identifiers");
331334
if (use_setsessauth)
332335
appendPQExpBuffer(pgdumpopts, " --use-set-session-authorization");
333336

@@ -440,6 +443,10 @@ main(int argc, char *argv[])
440443
destroyPQExpBuffer(query);
441444
}
442445

446+
/* Force quoting of all identifiers if requested. */
447+
if (quote_all_identifiers && server_version >= 90000)
448+
executeCommand(conn, "SET quote_all_identifiers = true");
449+
443450
fprintf(OPF, "--\n-- PostgreSQL database cluster dump\n--\n\n");
444451
if (verbose)
445452
dumpTimestamp("Started on");

0 commit comments

Comments
 (0)