pg_dump(all) --quote-all-identifiers

In response to a complaint from Hartmut Goebel:

http://archives.postgresql.org/pgsql-bugs/2010-06/msg00018.php

And per a design proposed by Tom Lane:

http://archives.postgresql.org/pgsql-bugs/2010-06/msg00211.php

PFA a patch to implement $SUBJECT. One interesting aspect of this
patch is that types like "integer" and "double precision" don't get
quoted in the output, whereas types like "text" do. But it turns out
that types like "integer" and "double precision" don't *work* if
they're quoted, so this is not a bad thing. It might possibly be
judged to require documentation somewhere, however.

Suggestions welcome.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Robert Haas wrote:
> In response to a complaint from Hartmut Goebel:
>
> http://archives.postgresql.org/pgsql-bugs/2010-06/msg00018.php
>
> And per a design proposed by Tom Lane:
>
> http://archives.postgresql.org/pgsql-bugs/2010-06/msg00211.php
>
> PFA a patch to implement $SUBJECT. One interesting aspect of this
> patch is that types like "integer" and "double precision" don't get
> quoted in the output, whereas types like "text" do. But it turns out
> that types like "integer" and "double precision" don't *work* if
> they're quoted, so this is not a bad thing. It might possibly be
> judged to require documentation somewhere, however.

Uh, I thought this was about quoting the identifiers. I am confused
about why "integer" is an issue in this case. Can you show an example?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

On Mon, Jun 14, 2010 at 6:57 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> In response to a complaint from Hartmut Goebel:
>>
>> http://archives.postgresql.org/pgsql-bugs/2010-06/msg00018.php
>>
>> And per a design proposed by Tom Lane:
>>
>> http://archives.postgresql.org/pgsql-bugs/2010-06/msg00211.php
>>
>> PFA a patch to implement $SUBJECT.  One interesting aspect of this
>> patch is that types like "integer" and "double precision" don't get
>> quoted in the output, whereas types like "text" do.  But it turns out
>> that types like "integer" and "double precision" don't *work* if
>> they're quoted, so this is not a bad thing.  It might possibly be
>> judged to require documentation somewhere, however.
>
> Uh, I thought this was about quoting the identifiers.  I am confused
> about why "integer" is an issue in this case.  Can you show an example?

Sure.

rhaas=# create table bruce (demonstration "integer");
ERROR: type "integer" does not exist
LINE 1: create table bruce (demonstration "integer");
^
rhaas=# create table bruce (demonstration integer);
CREATE TABLE

See gram.y, around line 8341.

Note that if you try the same example with "text" instead of
"integer", both variants work.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jun 14, 2010 at 6:57 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> Uh, I thought this was about quoting the identifiers. I am confused
>> about why "integer" is an issue in this case. Can you show an example?

> Sure.

INTEGER is actually a keyword in this context, not an identifier.
(Remember the actual name of the type is int4, not integer.)

regards, tom lane

On Sun, Jun 13, 2010 at 18:35, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

*Waves* Hi!

Patch looks and tests good to me. Only thing that seemed to be
missing was documentation of the new pg_dump(all) and guc params.
Find attached a stab at this. Yeah the docs I added need work, but I
figure if you are anything like me its easier to work/tweak from a
(crappy) base... At least with docs :). I also bumped the version
check to 90100 from 90000.

For testing I dumped one of my production schemas that has over
something like 1000 relations and lots of indexs and junk. (~ 1.4MB
for a schema only dump). I also tried some custom types just because
I was curious.

Everything worked as expected.

> PFA a patch to implement $SUBJECT.  One interesting aspect of this
> patch is that types like "integer" and "double precision" don't get
> quoted in the output, whereas types like "text" do.  But it turns out
> that types like "integer" and "double precision" don't *work* if
> they're quoted, so this is not a bad thing.  It might possibly be
> judged to require documentation somewhere, however.

IMHO I don't think additional documentation for the above is needed.
*shrug* But it does make me wonder if there is some subtle way for it
to break if we somehow did call quote_ident with "integer". Not that
I saw anyway for this to happen...

On Sat, Jul 17, 2010 at 3:59 AM, Alex Hunsaker <badalex(at)gmail(dot)com> wrote:
> Patch looks and tests good to me.  Only thing that seemed to be
> missing was documentation of the new pg_dump(all) and guc params.
> Find attached a stab at this.  Yeah the docs I added need work, but I
> figure if you are anything like me its easier to work/tweak from a
> (crappy) base...  At least with docs :).  I also bumped the version
> check to 90100 from 90000.

Thanks for the review. Committed with some further cleanup.

Unfortunately, as I noticed just after committing, you only fixed ONE
of the two references to 90000, so I have corrected that with a second
commit.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company