Re: [HACKERS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"

Hello.

System: PostgreSQL v9.0 Windows XP SP3
SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary)
ERROR: syntax error at or near "binary"
LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary)
^

********** Error **********

ERROR: syntax error at or near "binary"
SQL state: 42601
Character: 55

But if I use 'FORMAT text' or 'FORMAT csv' all is OK.

Suppose this happens because BINARY is not listed in
"unreserved_keyword" neither in "col_name_keyword parser" parser rules, but
listed in "type_func_name_keyword" instead.

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011:
> Hello.
>
> System: PostgreSQL v9.0 Windows XP SP3
> SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary)
> ERROR: syntax error at or near "binary"
> LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary)
> ^
>
> ********** Error **********
>
> ERROR: syntax error at or near "binary"
> SQL state: 42601
> Character: 55
>
> But if I use 'FORMAT text' or 'FORMAT csv' all is OK.
>
> Suppose this happens because BINARY is not listed in
> "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but
> listed in "type_func_name_keyword" instead.

That seems pretty unfortunate. Of course, it works if you quote it:

COPY "tablename" TO STDOUT WITH (FORMAT "binary")

I assume it's not in unreserved_keyword because it would cause a
shift/reduce conflict elsewhere.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hello, Alvaro.

You wrote:

AH> Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011:
>> Hello.
>>
>> System: PostgreSQL v9.0 Windows XP SP3
>> SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary)
>> ERROR: syntax error at or near "binary"
>> LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary)
>> ^
>>
>> ********** Error **********
>>
>> ERROR: syntax error at or near "binary"
>> SQL state: 42601
>> Character: 55
>>
>> But if I use 'FORMAT text' or 'FORMAT csv' all is OK.
>>
>> Suppose this happens because BINARY is not listed in
>> "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but
>> listed in "type_func_name_keyword" instead.

AH> That seems pretty unfortunate. Of course, it works if you quote it:

AH> COPY "tablename" TO STDOUT WITH (FORMAT "binary")

AH> I assume it's not in unreserved_keyword because it would cause a
AH> shift/reduce conflict elsewhere.

Well, there are two ways:
1. Change documentation, so quoted or double quoted values are
accepted

2. Fix parser

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

On Tue, Jul 5, 2011 at 11:06 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011:
>> Hello.
>>
>> System: PostgreSQL v9.0 Windows XP SP3
>> SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary)
>> ERROR:  syntax error at or near "binary"
>> LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary)
>>                                                   ^
>>
>> ********** Error **********
>>
>> ERROR: syntax error at or near "binary"
>> SQL state: 42601
>> Character: 55
>>
>> But if I use 'FORMAT text' or 'FORMAT csv' all is OK.
>>
>> Suppose this happens because BINARY is not listed in
>> "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but
>> listed in "type_func_name_keyword" instead.
>
> That seems pretty unfortunate.  Of course, it works if you quote it:
>
> COPY "tablename" TO STDOUT WITH (FORMAT "binary")
>
> I assume it's not in unreserved_keyword because it would cause a
> shift/reduce conflict elsewhere.

Yeah. In particular, it conflicts with the ancient copy syntax which
we still support for backwards compatibility with versions < 7.3. We
can fix the immediate problem with something like the attached.

(a) Should we do that?

(b) Should we back-patch it to 9.1 and 9.0?

(c) Should we consider removing compatibility with the ancient copy
syntax in 9.2, and de-reserving that keyword? (Given that the
workaround is this simple, I'm inclined to say "no", but could be
persuaded otherwise.)

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Jul 5, 2011 at 11:06 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>> I assume it's not in unreserved_keyword because it would cause a
>> shift/reduce conflict elsewhere.

> Yeah. In particular, it conflicts with the ancient copy syntax which
> we still support for backwards compatibility with versions < 7.3. We
> can fix the immediate problem with something like the attached.

> (a) Should we do that?

That seems like a horrid crock ...

regards, tom lane

On 07/05/2011 11:23 AM, Robert Haas wrote:
>
> Yeah. In particular, it conflicts with the ancient copy syntax which
> we still support for backwards compatibility with versions< 7.3. We
> can fix the immediate problem with something like the attached.
>
> (a) Should we do that?

yes.

> (b) Should we back-patch it to 9.1 and 9.0?

yes.

> (c) Should we consider removing compatibility with the ancient copy
> syntax in 9.2, and de-reserving that keyword? (Given that the
> workaround is this simple, I'm inclined to say "no", but could be
> persuaded otherwise.)
>

I'm inclined to say yes, but mainly because it's just old cruft. I don't
expect to be able,say, to load a pre-7.3 dump into a modern Postgres.

cheers

andrew

On Tue, Jul 5, 2011 at 11:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Jul 5, 2011 at 11:06 AM, Alvaro Herrera
>> <alvherre(at)commandprompt(dot)com> wrote:
>>> I assume it's not in unreserved_keyword because it would cause a
>>> shift/reduce conflict elsewhere.
>
>> Yeah.  In particular, it conflicts with the ancient copy syntax which
>> we still support for backwards compatibility with versions < 7.3.  We
>> can fix the immediate problem with something like the attached.
>
>> (a) Should we do that?
>
> That seems like a horrid crock ...

Do you have something else to propose?

It's a crock we have used elsewhere, so there is some precedent.

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

Hello, Robert.

You wrote:

RH> On Tue, Jul 5, 2011 at 11:06 AM, Alvaro Herrera
RH> <alvherre(at)commandprompt(dot)com> wrote:
>> Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011:
>>> Hello.
>>>
>>> System: PostgreSQL v9.0 Windows XP SP3
>>> SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary)
>>> ERROR:  syntax error at or near "binary"
>>> LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary)
>>>                                                   ^
>>>
>>> ********** Error **********
>>>
>>> ERROR: syntax error at or near "binary"
>>> SQL state: 42601
>>> Character: 55
>>>
>>> But if I use 'FORMAT text' or 'FORMAT csv' all is OK.
>>>
>>> Suppose this happens because BINARY is not listed in
>>> "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but
>>> listed in "type_func_name_keyword" instead.
>>
>> That seems pretty unfortunate.  Of course, it works if you quote it:
>>
>> COPY "tablename" TO STDOUT WITH (FORMAT "binary")
>>
>> I assume it's not in unreserved_keyword because it would cause a
>> shift/reduce conflict elsewhere.

RH> Yeah. In particular, it conflicts with the ancient copy syntax which
RH> we still support for backwards compatibility with versions < 7.3. We
RH> can fix the immediate problem with something like the attached.

This patch is ugly. Sorry, Robert, but it's true.

RH> (a) Should we do that?

RH> (b) Should we back-patch it to 9.1 and 9.0?

RH> (c) Should we consider removing compatibility with the ancient copy
RH> syntax in 9.2, and de-reserving that keyword? (Given that the
RH> workaround is this simple, I'm inclined to say "no", but could be
RH> persuaded otherwise.)

+1 for this. Pre-7.3 syntax is dead in fact for many years.

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

On Tue, Jul 5, 2011 at 11:37 AM, Pavel Golub <pavel(at)microolap(dot)com> wrote:
> RH> Yeah.  In particular, it conflicts with the ancient copy syntax which
> RH> we still support for backwards compatibility with versions < 7.3.  We
> RH> can fix the immediate problem with something like the attached.
>
> This patch is ugly. Sorry, Robert, but it's true.

No hard feelings here. If you, as the reporter of the problem, don't
feel that it's serious enough to warrant back-patching a fix, then I'm
not going to insist. However, if we don't do what I've proposed here,
then I think 8.4 and 9.0 and probably 9.1 are going to need to stay as
they are, because...

> RH> (c) Should we consider removing compatibility with the ancient copy
> RH> syntax in 9.2, and de-reserving that keyword?  (Given that the
> RH> workaround is this simple, I'm inclined to say "no", but could be
> RH> persuaded otherwise.)
>
> +1 for this. Pre-7.3 syntax is dead in fact for many years.

...this is not something we're going to back-patch.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> ... However, if we don't do what I've proposed here,
> then I think 8.4 and 9.0 and probably 9.1 are going to need to stay as
> they are, because...

>> RH> (c) Should we consider removing compatibility with the ancient copy
>> RH> syntax in 9.2, and de-reserving that keyword? (Given that the
>> RH> workaround is this simple, I'm inclined to say "no", but could be
>> RH> persuaded otherwise.)
>>
>> +1 for this. Pre-7.3 syntax is dead in fact for many years.

> ...this is not something we're going to back-patch.

Given the lack of prior complaints, and the simplicity of the
double-quote workaround, I feel little need to have a back-patchable
fix.

regards, tom lane

Hello, Robert.

You wrote:

RH> On Tue, Jul 5, 2011 at 11:37 AM, Pavel Golub <pavel(at)microolap(dot)com> wrote:
>> RH> Yeah.  In particular, it conflicts with the ancient copy syntax which
>> RH> we still support for backwards compatibility with versions < 7.3.  We
>> RH> can fix the immediate problem with something like the attached.
>>
>> This patch is ugly. Sorry, Robert, but it's true.

RH> No hard feelings here. If you, as the reporter of the problem, don't
RH> feel that it's serious enough to warrant back-patching a fix, then I'm
RH> not going to insist. However, if we don't do what I've proposed here,
RH> then I think 8.4 and 9.0 and probably 9.1 are going to need to stay as
RH> they are, because...

>> RH> (c) Should we consider removing compatibility with the ancient copy
>> RH> syntax in 9.2, and de-reserving that keyword?  (Given that the
>> RH> workaround is this simple, I'm inclined to say "no", but could be
>> RH> persuaded otherwise.)
>>
>> +1 for this. Pre-7.3 syntax is dead in fact for many years.

RH> ...this is not something we're going to back-patch.

Patches needed for 9.0 and 9.1 only, because this is new format
comparing with 8.x

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com