Re: BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

On Thu, Jun 10, 2010 at 10:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> From a code perspective, the difficulting in adding such a flag is that
>> much of the quoting happens inside the backend, not by pg_dump, and
>> therefore there is significant code change required to add this flag.
>
> Yeah, and not only that, but you'd need the *old* server to cooperate.
>
> Which means BTW that "use the newer pg_dump" is only an 80% solution.
> So maybe we do need to think about this.
>
> The least invasive answer that I can think of is to invent a "force
> quoting" GUC that's looked at by all the deparsing functions used by
> pg_dump.  We have pg_dump set that once, on backend versions that
> support it, and then we don't have to run around touching every single
> deparsing function's signature (and adding extra code paths in pg_dump
> to deal with older versions not having such functions).
>
> But the earliest this could be of use would be a 9.1->9.2 update ...

That's OK. I don't have an immediate problem I need to solve; I just
want to improve things for future users. The fact is, I've had this
problem in the past, and it wasn't fun, so, I understand the OP's
pain. But there's definitely a workaround until we get this done, it
just isn't a particularly enjoyable one.

The deparse_force_quoting GUC is a good idea - I bet the number of
places that would need to examine the value of that GUC is extremely
small. One possible objection is that such a GUC would also affect
the output of tools like EXPLAIN, but I'm not sure we really need to
worry about that. If someone really wants to force quoting in their
EXPLAIN output, I say let 'em.

If we really wanted to get sneaky we could even let the value of the
GUC be a list of words to force-quote, with * meaning all. That would
let a newer server talking to an older server hand over a specific
list of keywords that it knows are problem cases, even if the user
isn't using --quote-like-crazy. Not sure if we want to go there,
though.

The idea mentioned on another part of this thread of providing a way
to separate schema and data dumps without tanking performance is a
good one, too, but I still think this has merit even if we do that.
Just because we make it easier to manually edit dump files is not a
reason not to create options that render it unnecessary in the first
place.

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