BUG #14152: pg_dump does not take into account previous versions reserved keywords as column names

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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 prior
versions, thats the documented procedure for updating... I contend this
*is* a bug. quote-all-identifiers is a workaround, for sure.

​This was my first reaction, and I suspect that we attempt to do this

already and that this one in just an oversight​.

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.
"""

​I'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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

​I'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.​ 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.

​David J.​

"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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs