Thread: createuser/dropuser username
Hi, I think that createuser/drouser reference documentation could be enhanced for username param. There is: "createuser is a wrapper around the SQL command CREATE ROLE. There is no effective difference between creating users via this utility and via other methods for accessing the server." http://www.postgresql.org/docs/9.0/static/app-createuser.html http://www.postgresql.org/docs/9.0/static/app-dropuser.html But createuser and dropuser wrappers in some cases are doing implicit quoting (when CREATE ROLE is not), which I think is not fully known and is undocumented here. For example: createuser -SDRe user -- reserved in PostgreSQL CREATE ROLE "user" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN; createuser -SDRe myuser CREATE ROLE myuser NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN; createuser -SDRe MYuser CREATE ROLE "MYuser" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN; psql postgres ALTER ROLE MYuser CREATEDB; -- fatal mistake, it works for myuser delimited identifier ALTER ROLE ALTER ROLE "MYuser" CREATEDB; -- ok, quoted identifier ALTER ROLE ALTER ROLE user CREATEDB; ERROR: syntax error at or near "user" LINE 1: ALTER ROLE user CREATEDB; ^ postgres=# ALTER ROLE "user" CREATEDB; ALTER ROLE Regards, Grzegorz Szpetkowski
On Wed, May 4, 2011 at 12:05 PM, Grzegorz Szpetkowski <gszpetkowski@gmail.com> wrote: > Hi, > > I think that createuser/drouser reference documentation could be > enhanced for username param. There is: > > "createuser is a wrapper around the SQL command CREATE ROLE. There is > no effective difference between creating users via this utility and > via other methods for accessing the server." > > http://www.postgresql.org/docs/9.0/static/app-createuser.html > http://www.postgresql.org/docs/9.0/static/app-dropuser.html > > But createuser and dropuser wrappers in some cases are doing implicit > quoting (when CREATE ROLE is not), which I think is not fully known > and is undocumented here. For example: I'm not sure this really needs to be documented, but what exactly do you have in mind? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 19.05.2011 22:15, Robert Haas wrote: > On Wed, May 4, 2011 at 12:05 PM, Grzegorz Szpetkowski > <gszpetkowski@gmail.com> wrote: >> Hi, >> >> I think that createuser/drouser reference documentation could be >> enhanced for username param. There is: >> >> "createuser is a wrapper around the SQL command CREATE ROLE. There is >> no effective difference between creating users via this utility and >> via other methods for accessing the server." >> >> http://www.postgresql.org/docs/9.0/static/app-createuser.html >> http://www.postgresql.org/docs/9.0/static/app-dropuser.html >> >> But createuser and dropuser wrappers in some cases are doing implicit >> quoting (when CREATE ROLE is not), which I think is not fully known >> and is undocumented here. For example: > I'm not sure this really needs to be documented, but what exactly do > you have in mind? Honestly, I think we should document that the scripts use quotes. I just made an ad-hoc look into the docs and couldn't find something about it. Lots of years ago - you could see me sitting in front of my computer and wondering - because I used: $ createdb test $ createdb tEst I wondered that I didn't got an error message. Thinking deeper about it just let it get logical to me that createdb quotes by automatism. But this isn't logical on the first view - because we point out that all will get to lower case without quotes. PostgreSQL even is so intelligent - that when you say: $ createdb "tEst" - it will ignore the quotes - you will get database named tEst and not named "tEst". This all isn't really obvious to a typical user. Best Regards, Susanne -- Susanne Ebrecht - 2ndQuadrant PostgreSQL Development, 24x7 Support, Training and Services www.2ndQuadrant.com
Susanne Ebrecht <susanne@2ndQuadrant.com> writes:
> PostgreSQL even is so intelligent - that when you say:
> $ createdb "tEst" - it will ignore the quotes - you will get database
> named tEst and not
> named "tEst".
Not true. The reason you don't get quotes there is that the shell
stripped them off. Try '"tEst"' ...
The current behavior is something that we settled on years ago, after
thinking about the interaction between shell quoting rules and SQL
quoting rules. If we don't treat SQL names appearing on command lines
as spelled literally, then you have to quote them if you don't want them
downcased --- and doing that in a way that the shell won't mangle is
unpleasant, as in my example above. So we ruled that all utilities that
take a SQL name from the command line should auto-quote the name.
I'm pretty sure this is documented somewhere, but perhaps not in the
place where Grzegorz looked for it ...
regards, tom lane
On 20.05.2011 14:08, Tom Lane wrote: > I'm pretty sure this is documented somewhere, but perhaps not in the > place where Grzegorz looked for it ... Hehe - that was my intention too. I looked through the Tutorial and through some other chapters - but I couldn't find a hint. Susanne -- Susanne Ebrecht - 2ndQuadrant PostgreSQL Development, 24x7 Support, Training and Services www.2ndQuadrant.com
Tom Lane wrote: > Susanne Ebrecht <susanne@2ndQuadrant.com> writes: > > PostgreSQL even is so intelligent - that when you say: > > $ createdb "tEst" - it will ignore the quotes - you will get database > > named tEst and not > > named "tEst". > > Not true. The reason you don't get quotes there is that the shell > stripped them off. Try '"tEst"' ... > > The current behavior is something that we settled on years ago, after > thinking about the interaction between shell quoting rules and SQL > quoting rules. If we don't treat SQL names appearing on command lines > as spelled literally, then you have to quote them if you don't want them > downcased --- and doing that in a way that the shell won't mangle is > unpleasant, as in my example above. So we ruled that all utilities that > take a SQL name from the command line should auto-quote the name. > > I'm pretty sure this is documented somewhere, but perhaps not in the > place where Grzegorz looked for it ... Uh, I did some digging after this email report and found it does need some cleanup, which is done in the attached patch. It removes quoting for table references in clusterdb and index/table references in reindexdb, and adds a general documentation overview about when case is preserved and suggests quoting, and adds documentation where special quoting happens. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml new file mode 100644 index 3e50173..60a54cd *** a/doc/src/sgml/ref/createdb.sgml --- b/doc/src/sgml/ref/createdb.sgml *************** PostgreSQL documentation *** 90,96 **** <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term> <listitem> <para> ! Specifies the default tablespace for the database. </para> </listitem> </varlistentry> --- 90,96 ---- <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term> <listitem> <para> ! Specifies the default tablespace for the database (double-quoted internally). </para> </listitem> </varlistentry> *************** PostgreSQL documentation *** 153,159 **** <term><option>--owner=<replaceable class="parameter">owner</replaceable></></term> <listitem> <para> ! Specifies the database user who will own the new database. </para> </listitem> </varlistentry> --- 153,160 ---- <term><option>--owner=<replaceable class="parameter">owner</replaceable></></term> <listitem> <para> ! Specifies the database user who will own the new database ! (double-quoted internally). </para> </listitem> </varlistentry> *************** PostgreSQL documentation *** 163,169 **** <term><option>--template=<replaceable class="parameter">template</replaceable></></term> <listitem> <para> ! Specifies the template database from which to build this database. </para> </listitem> </varlistentry> --- 164,171 ---- <term><option>--template=<replaceable class="parameter">template</replaceable></></term> <listitem> <para> ! Specifies the template database from which to build this ! database (double-quoted internally). </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/createlang.sgml b/doc/src/sgml/ref/createlang.sgml new file mode 100644 index f01f298..7f9459a *** a/doc/src/sgml/ref/createlang.sgml --- b/doc/src/sgml/ref/createlang.sgml *************** PostgreSQL documentation *** 70,76 **** <term><replaceable class="parameter">langname</replaceable></term> <listitem> <para> ! Specifies the name of the procedural language to be installed. </para> </listitem> </varlistentry> --- 70,77 ---- <term><replaceable class="parameter">langname</replaceable></term> <listitem> <para> ! Specifies the name of the procedural language to be ! installed. (automatically lower-cased) </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/droplang.sgml b/doc/src/sgml/ref/droplang.sgml new file mode 100644 index 04c3a60..fa7e913 *** a/doc/src/sgml/ref/droplang.sgml --- b/doc/src/sgml/ref/droplang.sgml *************** PostgreSQL documentation *** 73,78 **** --- 73,79 ---- <listitem> <para> Specifies the name of the procedural language to be removed. + (automatically lower-cased) </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml new file mode 100644 index 9ae8000..5fd6410 *** a/doc/src/sgml/reference.sgml --- b/doc/src/sgml/reference.sgml *************** *** 198,203 **** --- 198,211 ---- applications is that they can be run on any host, independent of where the database server resides. </para> + + <para> + When specified on the command line, user and databases names have + their case preserved — the presence of spaces or special + characters might require quoting. Table names and other identifiers + do not have their case preserved, except where documented, and + might require quoting. + </para> </partintro> &clusterdb; diff --git a/src/bin/scripts/clusterdb.c b/src/bin/scripts/clusterdb.c new file mode 100644 index f4c317a..3742091 *** a/src/bin/scripts/clusterdb.c --- b/src/bin/scripts/clusterdb.c *************** cluster_one_database(const char *dbname, *** 177,183 **** if (verbose) appendPQExpBuffer(&sql, " VERBOSE"); if (table) ! appendPQExpBuffer(&sql, " %s", fmtId(table)); appendPQExpBuffer(&sql, ";\n"); conn = connectDatabase(dbname, host, port, username, prompt_password, progname); --- 177,183 ---- if (verbose) appendPQExpBuffer(&sql, " VERBOSE"); if (table) ! appendPQExpBuffer(&sql, " %s", table); appendPQExpBuffer(&sql, ";\n"); conn = connectDatabase(dbname, host, port, username, prompt_password, progname); diff --git a/src/bin/scripts/createlang.c b/src/bin/scripts/createlang.c new file mode 100644 index c2153db..2f667e8 *** a/src/bin/scripts/createlang.c --- b/src/bin/scripts/createlang.c *************** main(int argc, char *argv[]) *** 164,169 **** --- 164,170 ---- exit(1); } + /* lower case language name */ for (p = langname; *p; p++) if (*p >= 'A' && *p <= 'Z') *p += ('a' - 'A'); diff --git a/src/bin/scripts/droplang.c b/src/bin/scripts/droplang.c new file mode 100644 index 7fadee0..f136a76 *** a/src/bin/scripts/droplang.c --- b/src/bin/scripts/droplang.c *************** main(int argc, char *argv[]) *** 165,170 **** --- 165,171 ---- exit(1); } + /* lower case language name */ for (p = langname; *p; p++) if (*p >= 'A' && *p <= 'Z') *p += ('a' - 'A'); diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c new file mode 100644 index 53fff01..caeed75 *** a/src/bin/scripts/reindexdb.c --- b/src/bin/scripts/reindexdb.c *************** reindex_one_database(const char *name, c *** 223,231 **** appendPQExpBuffer(&sql, "REINDEX"); if (strcmp(type, "TABLE") == 0) ! appendPQExpBuffer(&sql, " TABLE %s", fmtId(name)); else if (strcmp(type, "INDEX") == 0) ! appendPQExpBuffer(&sql, " INDEX %s", fmtId(name)); else if (strcmp(type, "DATABASE") == 0) appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name)); appendPQExpBuffer(&sql, ";\n"); --- 223,231 ---- appendPQExpBuffer(&sql, "REINDEX"); if (strcmp(type, "TABLE") == 0) ! appendPQExpBuffer(&sql, " TABLE %s", name); else if (strcmp(type, "INDEX") == 0) ! appendPQExpBuffer(&sql, " INDEX %s", name); else if (strcmp(type, "DATABASE") == 0) appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name)); appendPQExpBuffer(&sql, ";\n");
Bruce Momjian <bruce@momjian.us> writes:
> Uh, I did some digging after this email report and found it does need
> some cleanup, which is done in the attached patch.
> It removes quoting for table references in clusterdb and index/table
> references in reindexdb,
Uh, surely that breaks things. Or did you miss my statement that the
current behavior is what is intended?
> and adds a general documentation overview about
> when case is preserved and suggests quoting, and adds documentation
> where special quoting happens.
I don't find the documentation changes to be improvements either.
Possibly instead of
> ! Specifies the default tablespace for the database (double-quoted internally).
you could do something like
Specifies the default tablespace for the database. (This name
is not subject to case-folding.)
> + <para>
> + When specified on the command line, user and databases names have
> + their case preserved — the presence of spaces or special
> + characters might require quoting. Table names and other identifiers
> + do not have their case preserved, except where documented, and
> + might require quoting.
> + </para>
This latter sentence is just plain wrong.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Uh, I did some digging after this email report and found it does need
> > some cleanup, which is done in the attached patch.
>
> > It removes quoting for table references in clusterdb and index/table
> > references in reindexdb,
>
> Uh, surely that breaks things. Or did you miss my statement that the
> current behavior is what is intended?
I saw that, but how is that consistent with other command-line tools?
What is the logic that has some tools preserve case and others not?
> > and adds a general documentation overview about
> > when case is preserved and suggests quoting, and adds documentation
> > where special quoting happens.
>
> I don't find the documentation changes to be improvements either.
> Possibly instead of
>
> > ! Specifies the default tablespace for the database (double-quoted internally).
>
> you could do something like
>
> Specifies the default tablespace for the database. (This name
> is not subject to case-folding.)
OK.
> > + <para>
> > + When specified on the command line, user and databases names have
> > + their case preserved — the presence of spaces or special
> > + characters might require quoting. Table names and other identifiers
> > + do not have their case preserved, except where documented, and
> > + might require quoting.
> > + </para>
>
> This latter sentence is just plain wrong.
Really? Pg_dump doesn't preserve case for table names:
pg_dump -t Test test
$ pg_dump -t Test test
pg_dump: No matching tables were found
$ pg_dump -t '"Test"' test
--
-- PostgreSQL database dump
...
and vacuumdb certainly does not:
vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy
$ vacuumdb --analyze --verbose --table 'Test (x)' test
vacuumdb: vacuuming of table "Test (x)" in database "test" failed: ERROR: relation "test" does not exist
$ vacuumdb --analyze --verbose --table '"Test" (x)' test
INFO: vacuuming "public.Test"
INFO: "Test": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
...
Who does? reindexdb and clusterdb did before my patch, but that hardly
seems consistent.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Uh, I did some digging after this email report and found it does need
> > some cleanup, which is done in the attached patch.
>
> > It removes quoting for table references in clusterdb and index/table
> > references in reindexdb,
>
> Uh, surely that breaks things. Or did you miss my statement that the
> current behavior is what is intended?
>
> > and adds a general documentation overview about
> > when case is preserved and suggests quoting, and adds documentation
> > where special quoting happens.
>
> I don't find the documentation changes to be improvements either.
> Possibly instead of
>
> > ! Specifies the default tablespace for the database (double-quoted internally).
>
> you could do something like
>
> Specifies the default tablespace for the database. (This name
> is not subject to case-folding.)
Actually, that is inaccurate. When we call PQconnectdbParams() with
username/database names, or call fmtId(), we are doing more than
preserving case. We are also preserving whitespace and special
characters. I can make the new text:
(This name is processed as a double-quoted identifier.)
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> Bruce Momjian <bruce@momjian.us> writes:
>>> It removes quoting for table references in clusterdb and index/table
>>> references in reindexdb,
>> Uh, surely that breaks things. Or did you miss my statement that the
>> current behavior is what is intended?
> I saw that, but how is that consistent with other command-line tools?
> What is the logic that has some tools preserve case and others not?
If there are any that fail to preserve case, those are the ones to be
fixed. Making the working ones match the broken ones is not the correct
direction.
> Really? Pg_dump doesn't preserve case for table names:
> pg_dump -t Test test
pg_dump is a special case because the arguments are patterns to match,
not fixed names.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Tom Lane wrote:
> >> Bruce Momjian <bruce@momjian.us> writes:
> >>> It removes quoting for table references in clusterdb and index/table
> >>> references in reindexdb,
>
> >> Uh, surely that breaks things. Or did you miss my statement that the
> >> current behavior is what is intended?
>
> > I saw that, but how is that consistent with other command-line tools?
> > What is the logic that has some tools preserve case and others not?
>
> If there are any that fail to preserve case, those are the ones to be
> fixed. Making the working ones match the broken ones is not the correct
> direction.
I started going in that direction and stopped because of this example
in our docs:
vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy
Ideas?
>
> > Really? Pg_dump doesn't preserve case for table names:
>
> > pg_dump -t Test test
>
> pg_dump is a special case because the arguments are patterns to match,
> not fixed names.
OK.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Excerpts from Bruce Momjian's message of jue sep 08 11:52:04 -0300 2011: > Tom Lane wrote: > > If there are any that fail to preserve case, those are the ones to be > > fixed. Making the working ones match the broken ones is not the correct > > direction. > > I started going in that direction and stopped because of this example > in our docs: > > vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy > > Ideas? Maybe do it unless there are parens. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Excerpts from Bruce Momjian's message of jue sep 08 11:52:04 -0300 2011: > > Tom Lane wrote: > > > > If there are any that fail to preserve case, those are the ones to be > > > fixed. Making the working ones match the broken ones is not the correct > > > direction. > > > > I started going in that direction and stopped because of this example > > in our docs: > > > > vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy > > > > Ideas? > > Maybe do it unless there are parens. OK, let me give that a try. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> Alvaro Herrera wrote:
>> Excerpts from Bruce Momjian's message of jue sep 08 11:52:04 -0300 2011:
>>> I started going in that direction and stopped because of this example
>>> in our docs:
>>> vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy
>> Maybe do it unless there are parens.
> OK, let me give that a try.
I've been thinking more about this, and realized that there is one
killer argument in favor of not auto-quoting, at least for arguments
that represent table names: if we quote, there is no way to specify
a schema-qualified name.
I'm too lazy to troll the archives to verify this, but I am moderately
sure that this point never came up in the original discussions where
we concluded that auto-quoting command-line arguments was a good policy,
because that was before we'd implemented schemas.
So what I'm thinking right now is that we indeed should not auto-quote
table- or index-name arguments. However, usernames and database names
taken from the command line are still going to be auto-quoted, because
that's how things work in connection requests. Is it going to be
confusing that some things are quoted and some not?
(Of course, any such change would be non-backwards-compatible and would
have to be suitably documented and release-noted.)
The backwards-compatible alternative would be to invent additional
schema option arguments so that you could write something like
"reindexdb --schema foo --table bar", but that seems pretty darn ugly.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Alvaro Herrera wrote: > >> Excerpts from Bruce Momjian's message of jue sep 08 11:52:04 -0300 2011: > >>> I started going in that direction and stopped because of this example > >>> in our docs: > >>> vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy > > >> Maybe do it unless there are parens. > > > OK, let me give that a try. > > I've been thinking more about this, and realized that there is one > killer argument in favor of not auto-quoting, at least for arguments > that represent table names: if we quote, there is no way to specify > a schema-qualified name. > > I'm too lazy to troll the archives to verify this, but I am moderately > sure that this point never came up in the original discussions where > we concluded that auto-quoting command-line arguments was a good policy, > because that was before we'd implemented schemas. > > So what I'm thinking right now is that we indeed should not auto-quote > table- or index-name arguments. However, usernames and database names > taken from the command line are still going to be auto-quoted, because > that's how things work in connection requests. Is it going to be > confusing that some things are quoted and some not? > > (Of course, any such change would be non-backwards-compatible and would > have to be suitably documented and release-noted.) > > The backwards-compatible alternative would be to invent additional > schema option arguments so that you could write something like > "reindexdb --schema foo --table bar", but that seems pretty darn ugly. I have implemented what you suggested above, with improved wording. Patch attached. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml new file mode 100644 index 3e50173..1516f33 *** a/doc/src/sgml/ref/createdb.sgml --- b/doc/src/sgml/ref/createdb.sgml *************** PostgreSQL documentation *** 90,96 **** <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term> <listitem> <para> ! Specifies the default tablespace for the database. </para> </listitem> </varlistentry> --- 90,97 ---- <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term> <listitem> <para> ! Specifies the default tablespace for the database. (This name ! is processed as a double-quoted identifier.) </para> </listitem> </varlistentry> *************** PostgreSQL documentation *** 154,159 **** --- 155,161 ---- <listitem> <para> Specifies the database user who will own the new database. + (This name is processed as a double-quoted identifier.) </para> </listitem> </varlistentry> *************** PostgreSQL documentation *** 163,169 **** <term><option>--template=<replaceable class="parameter">template</replaceable></></term> <listitem> <para> ! Specifies the template database from which to build this database. </para> </listitem> </varlistentry> --- 165,172 ---- <term><option>--template=<replaceable class="parameter">template</replaceable></></term> <listitem> <para> ! Specifies the template database from which to build this ! database. (This name is processed as a double-quoted identifier.) </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/createlang.sgml b/doc/src/sgml/ref/createlang.sgml new file mode 100644 index f01f298..d28cfb7 *** a/doc/src/sgml/ref/createlang.sgml --- b/doc/src/sgml/ref/createlang.sgml *************** PostgreSQL documentation *** 70,76 **** <term><replaceable class="parameter">langname</replaceable></term> <listitem> <para> ! Specifies the name of the procedural language to be installed. </para> </listitem> </varlistentry> --- 70,77 ---- <term><replaceable class="parameter">langname</replaceable></term> <listitem> <para> ! Specifies the name of the procedural language to be ! installed. (This name is lower-cased.) </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/droplang.sgml b/doc/src/sgml/ref/droplang.sgml new file mode 100644 index 04c3a60..e5d02aa *** a/doc/src/sgml/ref/droplang.sgml --- b/doc/src/sgml/ref/droplang.sgml *************** PostgreSQL documentation *** 73,78 **** --- 73,79 ---- <listitem> <para> Specifies the name of the procedural language to be removed. + (This name is lower-cased.) </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml new file mode 100644 index 9ae8000..5fd6410 *** a/doc/src/sgml/reference.sgml --- b/doc/src/sgml/reference.sgml *************** *** 198,203 **** --- 198,211 ---- applications is that they can be run on any host, independent of where the database server resides. </para> + + <para> + When specified on the command line, user and databases names have + their case preserved — the presence of spaces or special + characters might require quoting. Table names and other identifiers + do not have their case preserved, except where documented, and + might require quoting. + </para> </partintro> &clusterdb; diff --git a/src/bin/scripts/clusterdb.c b/src/bin/scripts/clusterdb.c new file mode 100644 index f4c317a..3742091 *** a/src/bin/scripts/clusterdb.c --- b/src/bin/scripts/clusterdb.c *************** cluster_one_database(const char *dbname, *** 177,183 **** if (verbose) appendPQExpBuffer(&sql, " VERBOSE"); if (table) ! appendPQExpBuffer(&sql, " %s", fmtId(table)); appendPQExpBuffer(&sql, ";\n"); conn = connectDatabase(dbname, host, port, username, prompt_password, progname); --- 177,183 ---- if (verbose) appendPQExpBuffer(&sql, " VERBOSE"); if (table) ! appendPQExpBuffer(&sql, " %s", table); appendPQExpBuffer(&sql, ";\n"); conn = connectDatabase(dbname, host, port, username, prompt_password, progname); diff --git a/src/bin/scripts/createlang.c b/src/bin/scripts/createlang.c new file mode 100644 index c2153db..2f667e8 *** a/src/bin/scripts/createlang.c --- b/src/bin/scripts/createlang.c *************** main(int argc, char *argv[]) *** 164,169 **** --- 164,170 ---- exit(1); } + /* lower case language name */ for (p = langname; *p; p++) if (*p >= 'A' && *p <= 'Z') *p += ('a' - 'A'); diff --git a/src/bin/scripts/droplang.c b/src/bin/scripts/droplang.c new file mode 100644 index 7fadee0..f136a76 *** a/src/bin/scripts/droplang.c --- b/src/bin/scripts/droplang.c *************** main(int argc, char *argv[]) *** 165,170 **** --- 165,171 ---- exit(1); } + /* lower case language name */ for (p = langname; *p; p++) if (*p >= 'A' && *p <= 'Z') *p += ('a' - 'A'); diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c new file mode 100644 index 53fff01..caeed75 *** a/src/bin/scripts/reindexdb.c --- b/src/bin/scripts/reindexdb.c *************** reindex_one_database(const char *name, c *** 223,231 **** appendPQExpBuffer(&sql, "REINDEX"); if (strcmp(type, "TABLE") == 0) ! appendPQExpBuffer(&sql, " TABLE %s", fmtId(name)); else if (strcmp(type, "INDEX") == 0) ! appendPQExpBuffer(&sql, " INDEX %s", fmtId(name)); else if (strcmp(type, "DATABASE") == 0) appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name)); appendPQExpBuffer(&sql, ";\n"); --- 223,231 ---- appendPQExpBuffer(&sql, "REINDEX"); if (strcmp(type, "TABLE") == 0) ! appendPQExpBuffer(&sql, " TABLE %s", name); else if (strcmp(type, "INDEX") == 0) ! appendPQExpBuffer(&sql, " INDEX %s", name); else if (strcmp(type, "DATABASE") == 0) appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name)); appendPQExpBuffer(&sql, ";\n");
Patch applied. --------------------------------------------------------------------------- Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian <bruce@momjian.us> writes: > > > Alvaro Herrera wrote: > > >> Excerpts from Bruce Momjian's message of jue sep 08 11:52:04 -0300 2011: > > >>> I started going in that direction and stopped because of this example > > >>> in our docs: > > >>> vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy > > > > >> Maybe do it unless there are parens. > > > > > OK, let me give that a try. > > > > I've been thinking more about this, and realized that there is one > > killer argument in favor of not auto-quoting, at least for arguments > > that represent table names: if we quote, there is no way to specify > > a schema-qualified name. > > > > I'm too lazy to troll the archives to verify this, but I am moderately > > sure that this point never came up in the original discussions where > > we concluded that auto-quoting command-line arguments was a good policy, > > because that was before we'd implemented schemas. > > > > So what I'm thinking right now is that we indeed should not auto-quote > > table- or index-name arguments. However, usernames and database names > > taken from the command line are still going to be auto-quoted, because > > that's how things work in connection requests. Is it going to be > > confusing that some things are quoted and some not? > > > > (Of course, any such change would be non-backwards-compatible and would > > have to be suitably documented and release-noted.) > > > > The backwards-compatible alternative would be to invent additional > > schema option arguments so that you could write something like > > "reindexdb --schema foo --table bar", but that seems pretty darn ugly. > > I have implemented what you suggested above, with improved wording. > Patch attached. > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + [ text/x-diff is unsupported, treating like TEXT/PLAIN ] > diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml > new file mode 100644 > index 3e50173..1516f33 > *** a/doc/src/sgml/ref/createdb.sgml > --- b/doc/src/sgml/ref/createdb.sgml > *************** PostgreSQL documentation > *** 90,96 **** > <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term> > <listitem> > <para> > ! Specifies the default tablespace for the database. > </para> > </listitem> > </varlistentry> > --- 90,97 ---- > <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term> > <listitem> > <para> > ! Specifies the default tablespace for the database. (This name > ! is processed as a double-quoted identifier.) > </para> > </listitem> > </varlistentry> > *************** PostgreSQL documentation > *** 154,159 **** > --- 155,161 ---- > <listitem> > <para> > Specifies the database user who will own the new database. > + (This name is processed as a double-quoted identifier.) > </para> > </listitem> > </varlistentry> > *************** PostgreSQL documentation > *** 163,169 **** > <term><option>--template=<replaceable class="parameter">template</replaceable></></term> > <listitem> > <para> > ! Specifies the template database from which to build this database. > </para> > </listitem> > </varlistentry> > --- 165,172 ---- > <term><option>--template=<replaceable class="parameter">template</replaceable></></term> > <listitem> > <para> > ! Specifies the template database from which to build this > ! database. (This name is processed as a double-quoted identifier.) > </para> > </listitem> > </varlistentry> > diff --git a/doc/src/sgml/ref/createlang.sgml b/doc/src/sgml/ref/createlang.sgml > new file mode 100644 > index f01f298..d28cfb7 > *** a/doc/src/sgml/ref/createlang.sgml > --- b/doc/src/sgml/ref/createlang.sgml > *************** PostgreSQL documentation > *** 70,76 **** > <term><replaceable class="parameter">langname</replaceable></term> > <listitem> > <para> > ! Specifies the name of the procedural language to be installed. > </para> > </listitem> > </varlistentry> > --- 70,77 ---- > <term><replaceable class="parameter">langname</replaceable></term> > <listitem> > <para> > ! Specifies the name of the procedural language to be > ! installed. (This name is lower-cased.) > </para> > </listitem> > </varlistentry> > diff --git a/doc/src/sgml/ref/droplang.sgml b/doc/src/sgml/ref/droplang.sgml > new file mode 100644 > index 04c3a60..e5d02aa > *** a/doc/src/sgml/ref/droplang.sgml > --- b/doc/src/sgml/ref/droplang.sgml > *************** PostgreSQL documentation > *** 73,78 **** > --- 73,79 ---- > <listitem> > <para> > Specifies the name of the procedural language to be removed. > + (This name is lower-cased.) > </para> > </listitem> > </varlistentry> > diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml > new file mode 100644 > index 9ae8000..5fd6410 > *** a/doc/src/sgml/reference.sgml > --- b/doc/src/sgml/reference.sgml > *************** > *** 198,203 **** > --- 198,211 ---- > applications is that they can be run on any host, independent of > where the database server resides. > </para> > + > + <para> > + When specified on the command line, user and databases names have > + their case preserved — the presence of spaces or special > + characters might require quoting. Table names and other identifiers > + do not have their case preserved, except where documented, and > + might require quoting. > + </para> > </partintro> > > &clusterdb; > diff --git a/src/bin/scripts/clusterdb.c b/src/bin/scripts/clusterdb.c > new file mode 100644 > index f4c317a..3742091 > *** a/src/bin/scripts/clusterdb.c > --- b/src/bin/scripts/clusterdb.c > *************** cluster_one_database(const char *dbname, > *** 177,183 **** > if (verbose) > appendPQExpBuffer(&sql, " VERBOSE"); > if (table) > ! appendPQExpBuffer(&sql, " %s", fmtId(table)); > appendPQExpBuffer(&sql, ";\n"); > > conn = connectDatabase(dbname, host, port, username, prompt_password, progname); > --- 177,183 ---- > if (verbose) > appendPQExpBuffer(&sql, " VERBOSE"); > if (table) > ! appendPQExpBuffer(&sql, " %s", table); > appendPQExpBuffer(&sql, ";\n"); > > conn = connectDatabase(dbname, host, port, username, prompt_password, progname); > diff --git a/src/bin/scripts/createlang.c b/src/bin/scripts/createlang.c > new file mode 100644 > index c2153db..2f667e8 > *** a/src/bin/scripts/createlang.c > --- b/src/bin/scripts/createlang.c > *************** main(int argc, char *argv[]) > *** 164,169 **** > --- 164,170 ---- > exit(1); > } > > + /* lower case language name */ > for (p = langname; *p; p++) > if (*p >= 'A' && *p <= 'Z') > *p += ('a' - 'A'); > diff --git a/src/bin/scripts/droplang.c b/src/bin/scripts/droplang.c > new file mode 100644 > index 7fadee0..f136a76 > *** a/src/bin/scripts/droplang.c > --- b/src/bin/scripts/droplang.c > *************** main(int argc, char *argv[]) > *** 165,170 **** > --- 165,171 ---- > exit(1); > } > > + /* lower case language name */ > for (p = langname; *p; p++) > if (*p >= 'A' && *p <= 'Z') > *p += ('a' - 'A'); > diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c > new file mode 100644 > index 53fff01..caeed75 > *** a/src/bin/scripts/reindexdb.c > --- b/src/bin/scripts/reindexdb.c > *************** reindex_one_database(const char *name, c > *** 223,231 **** > > appendPQExpBuffer(&sql, "REINDEX"); > if (strcmp(type, "TABLE") == 0) > ! appendPQExpBuffer(&sql, " TABLE %s", fmtId(name)); > else if (strcmp(type, "INDEX") == 0) > ! appendPQExpBuffer(&sql, " INDEX %s", fmtId(name)); > else if (strcmp(type, "DATABASE") == 0) > appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name)); > appendPQExpBuffer(&sql, ";\n"); > --- 223,231 ---- > > appendPQExpBuffer(&sql, "REINDEX"); > if (strcmp(type, "TABLE") == 0) > ! appendPQExpBuffer(&sql, " TABLE %s", name); > else if (strcmp(type, "INDEX") == 0) > ! appendPQExpBuffer(&sql, " INDEX %s", name); > else if (strcmp(type, "DATABASE") == 0) > appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name)); > appendPQExpBuffer(&sql, ";\n"); > > -- > Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-docs -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On fre, 2011-09-09 at 19:48 -0400, Bruce Momjian wrote: > ! Specifies the default tablespace for the database. (This name > ! is processed as a double-quoted identifier.) I don't find this wording particularly useful. What does it mean? The imaginary double quotes are stripped off? The advantage of using the shell tools is that you are not affected by the oddities of SQL syntax, so don't introduce references to SQL syntax where it doesn't need to be. Just say, the names are used as is. Or just say nothing, because nothing happens. > ! Specifies the name of the procedural language to be > ! installed. (This name is lower-cased.) Maybe an explanation why would be in order here. > + <para> > + When specified on the command line, user and databases names have > + their case preserved — the presence of spaces or special > + characters might require quoting. Table names and other identifiers > + do not have their case preserved, except where documented, and > + might require quoting. > + </para> What kind of quoting might be required?
Peter Eisentraut wrote: > On fre, 2011-09-09 at 19:48 -0400, Bruce Momjian wrote: > > ! Specifies the default tablespace for the database. (This name > > ! is processed as a double-quoted identifier.) > > I don't find this wording particularly useful. What does it mean? The > imaginary double quotes are stripped off? My original wording said that the identifier is processed with double-quotes internally. Is that better? > The advantage of using the shell tools is that you are not affected by > the oddities of SQL syntax, so don't introduce references to SQL syntax > where it doesn't need to be. Just say, the names are used as is. Or > just say nothing, because nothing happens. We have to say something because the section description says only user/db names are double-quoted, execept where documented. > > ! Specifies the name of the procedural language to be > > ! installed. (This name is lower-cased.) > > Maybe an explanation why would be in order here. I frankly have no idea why. > > + <para> > > + When specified on the command line, user and databases names have > > + their case preserved — the presence of spaces or special > > + characters might require quoting. Table names and other identifiers > > + do not have their case preserved, except where documented, and > > + might require quoting. > > + </para> > > What kind of quoting might be required? Double or single-quoting, and I don't know about Windows, except I know is supports double-quoting. Do we really want to get into that? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +