Обсуждение: BUG #14152: pg_dump does not take into account previous versions reserved keywords as column names

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDE1MgpMb2dnZWQgYnk6ICAg
ICAgICAgIEFsZWphbmRybyBNYXJ0w61uZXoKRW1haWwgYWRkcmVzczogICAg
ICBhbGVqYW5kcm9AY2FydG9kYi5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5
LjUuMwpPcGVyYXRpbmcgc3lzdGVtOiAgIFdpbmRvd3MKRGVzY3JpcHRpb246
ICAgICAgICAKClN0ZXBzIHRvIHJlcHJvZHVjZToNCg0KLSBIYXZlIGEgOS4z
LjEzIGNsdXN0ZXINCi0gT24gdGhhdCBkYXRhYmFzZSBydW46IENSRUFURSBU
QUJMRSB0ZXN0KGZvbyBpbnQsICJvdmVyIiB0ZXN0LCBiYXIgaW50KTsuCk5v
dGUgdGhhdCAib3ZlciIgaXMgYSBrZXl3b3JkIHdoaWNoIGFwcGFyZW50bHkg
d2VudCBmcm9tIHJlc2VydmVkIHRvCm5vbi1yZXNlcnZlZCBvbiA5LjQgcmVs
ZWFzZS4NCi0gVXNlIGEgOS41LjMgcGdfZHVtcCB0byBkdW1wIHRoZSBkYXRh
YmFzZS4NCg0KSXQgZmFpbHMgd2l0aDoNCg0KcGdfZHVtcDogW2FyY2hpdmVy
IChkYildIHF1ZXJ5IGZhaWxlZDogRVJST1I6ICBzeW50YXggZXJyb3IgYXQg
b3IgbmVhcgoib3ZlciINCkxJTkUgMTogQ09QWSBwdWJsaWMudGVzdCAoZm9v
LCBvdmVyLCBiYXIpIFRPIHN0ZG91dDsNCiAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICBeDQpwZ19kdW1wOiBbYXJjaGl2ZXIgKGRiKV0gcXVlcnkg
d2FzOiBDT1BZIHB1YmxpYy50ZXN0IChmb28sIG92ZXIsIGJhcikgVE8Kc3Rk
b3V0Ow0KDQpVc2luZyBhIDkuMyBwZ19kdW1wIHdpbGwgcXVvdGUgdGhlICJv
dmVyIiBjb2x1bW4gbmFtZSBhbmQgcHJvcGVybHkgZmluaXNoCnRoZSBkdW1w
Lg0KCgo=

On Fri, May 20, 2016 at 12:58 PM,  <alejandro@cartodb.com> wrote:

> - On that database run: CREATE TABLE test(foo int, "over" test, bar int);.
> Note that "over" is a keyword which apparently went from reserved to
> non-reserved on 9.4 release.
> - Use a 9.5.3 pg_dump to dump the database.
>
> It fails with:
>
> pg_dump: [archiver (db)] query failed: ERROR:  syntax error at or near
> "over"
> LINE 1: COPY public.test (foo, over, bar) TO stdout;
>                                ^
> pg_dump: [archiver (db)] query was: COPY public.test (foo, over, bar) TO
> stdout;
>
> Using a 9.3 pg_dump will quote the "over" column name and properly finish
> the dump.

... or use the --quote-all-identifiers option of 9.5 pg_dump.

http://www.postgresql.org/docs/9.5/static/app-pgdump.html

Not a bug.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 5/20/2016 10:56 AM, Kevin Grittner wrote:
> ... or use the --quote-all-identifiers option of 9.5 pg_dump.
>
> http://www.postgresql.org/docs/9.5/static/app-pgdump.html
>
> Not a bug.

pg_dump and pg_dumpall are supposed to be able to dump all supported
prior versions, thats the documented procedure for updating...    I
contend this *is* a bug.   quote-all-identifiers is a workaround, for sure.


--
john r pierce, recycling bits in santa cruz

On Fri, May 20, 2016 at 2:02 PM, John R Pierce <pierce@hogranch.com> wrote:

> On 5/20/2016 10:56 AM, Kevin Grittner wrote:
>
> ... or use the --quote-all-identifiers option of 9.5 pg_dump.
> http://www.postgresql.org/docs/9.5/static/app-pgdump.html
>
> Not a bug.
>
>
> pg_dump and pg_dumpall are supposed to be able to dump all supported prio=
r
> versions, thats the documented procedure for updating...    I contend thi=
s
> *is* a bug.   quote-all-identifiers is a workaround, for sure.
>
>
> =E2=80=8BThis was my first reaction, and I suspect that we attempt to do =
this
already and that this one in just an oversight=E2=80=8B.

If indeed the prescribed procedure is quote-all-identifiers I'd say we
should at least update the docs.

"""
Force quoting of all identifiers. This may be useful when dumping a
database for migration to a future version that may have introduced
additional keywords.
"""

=E2=80=8BI'd probably say something like:

"It is recommended to use this option when performing dumps intended for
migration or when dealing with a version of PostgreSQL different than
pg_dump.  This is because the recognized keywords sometimes change between
major versions but only the quoting rules of the pg_dump version are
applied."

This doesn't seem to make its way that often to these lists otherwise I'd
recommend we add a "no-force-quote-identifiers" option and default to this
behavior.

David J.

On Fri, May 20, 2016 at 2:02 PM, John R Pierce <pierce@hogranch.com> wrote:
> pg_dump and pg_dumpall are supposed to be able to dump all supported prior
> versions, thats the documented procedure for updating...    I contend this
> *is* a bug.   quote-all-identifiers is a workaround, for sure.

Well, --quote-all-identifiers is here to handle the case of new
keywords introduced. Let's use that and call it a day.
--
Michael

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, May 20, 2016 at 2:02 PM, John R Pierce <pierce@hogranch.com> wrote:
>> pg_dump and pg_dumpall are supposed to be able to dump all supported prior
>> versions, thats the documented procedure for updating...    I contend this
>> *is* a bug.   quote-all-identifiers is a workaround, for sure.

> If indeed the prescribed procedure is quote-all-identifiers I'd say we
> should at least update the docs.

Yeah, agreed.  I'll put something in.

Alternatively, we could automatically turn on quote-all-identifiers when
dumping from a server of a different major version; but I imagine we'd
get complaints about that, too, since it's so seldom an issue.

            regards, tom lane

On Fri, May 20, 2016 at 2:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Fri, May 20, 2016 at 2:02 PM, John R Pierce <pierce@hogranch.com>
> wrote:
> >> pg_dump and pg_dumpall are supposed to be able to dump all supported
> prior
> >> versions, thats the documented procedure for updating...    I contend
> this
> >> *is* a bug.   quote-all-identifiers is a workaround, for sure.
>
> > If indeed the prescribed procedure is quote-all-identifiers I'd say we
> > should at least update the docs.
>
> Yeah, agreed.  I'll put something in.
>
>
=E2=80=8BI'd hit up the "Notes" section's last paragraph in addition to the
command-line option.  The former is going to be much more visible.  The
cross-version dynamic possibly warrants it own section header, IMO, given
that it is a primary use case for the tool.=E2=80=8B  Consider placing it
subsequent to the Description section - maybe calling it "Upgrades".

Alternatively, we could automatically turn on quote-all-identifiers when
> dumping from a server of a different major version; but I imagine we'd
> get complaints about that, too, since it's so seldom an issue.
>
>
True, the volume of people using even potentially reserved words as
identifiers is not all that high.  Given fail-fast behavior, and a
reasonable pointer to overcoming it, catering to this minority doesn't seem
worth disrupting the status quo; even in this limited fashion.

=E2=80=8BDavid J.=E2=80=8B

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, May 20, 2016 at 2:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, agreed.  I'll put something in.

> ​I'd hit up the "Notes" section's last paragraph in addition to the
> command-line option.

Good point --- I missed that, but will have another go.

            regards, tom lane