Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Ignore UTF-8-encoded Unicode byte-order mark at the beginning of a file if

  1. Strange behaviour with incompatible psql/server

    Pavan Deolasee <pavan.deolasee@gmail.com> — 2012-11-28T08:46:20Z

    A friend reported this issue to me and I find it a bit strange and even
after spending some time on this, I couldn't really figure out what's going
wrong. See attached two SQL files, bad.sql and good.sql. They look the
exact same in the editor. In fact, the good.sql is created by copying lines
from bad.sql in the editor. There is probably some control character that
differentiate the two files, but ":set list" in vim does not show anything.

Now, if I use 8.4.9 psql to connect to the server running 9.0.10, I get the
following results with bad.sql

$ psql postgres
psql (8.4.9, server 9.0.10)
WARNING: psql version 8.4, server version 9.0.
         Some psql features might not work.
Type "help" for help.

postgres=# \i bad.sql
psql:bad.sql:4: ERROR:  syntax error at or near ""
LINE 1:
        ^
psql:bad.sql:12: NOTICE:  CREATE TABLE will create implicit sequence
"history_id_seq" for serial column "history.id"
psql:bad.sql:12: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
index "history_pkey" for table "history"
CREATE TABLE
postgres=#

Notice the syntax error above.

Now, if I run the good.sql, I don't see any errors.
$ psql postgres
psql (8.4.9, server 9.0.10)
WARNING: psql version 8.4, server version 9.0.
         Some psql features might not work.
Type "help" for help.

postgres=# \i good.sql
DROP TABLE
psql:good.sql:12: NOTICE:  CREATE TABLE will create implicit sequence
"history_id_seq" for serial column "history.id"
psql:good.sql:12: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
index "history_pkey" for table "history"
CREATE TABLE

Finally, if I use psql from 9.0.10 release, both the files run without any
errors. See output from the offending SQL file below.
$ ./install/bin/psql postgres
psql (9.0.10)
Type "help" for help.

postgres=# \i bad.sql
DROP TABLE
psql:bad.sql:12: NOTICE:  CREATE TABLE will create implicit sequence
"history_id_seq" for serial column "history.id"
psql:bad.sql:12: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
index "history_pkey" for table "history"
CREATE TABLE

While I'm almost certain that this has something to do with special
characters that my naked eyes can not see, all my attempts to spot the
difference has failed. So I really have two questions:

1. What's the difference between these files ?
2. Why 9.0 psql works fine with that difference, but 8.4 psql does not ?

Any suggestions ?

Thanks,
Pavan

  2. Re: Strange behaviour with incompatible psql/server

    Heikki Linnakangas <hlinnakangas@vmware.com> — 2012-11-28T08:58:22Z

    On 28.11.2012 10:46, Pavan Deolasee wrote:
> While I'm almost certain that this has something to do with special
> characters that my naked eyes can not see, all my attempts to spot the
> difference has failed. So I really have two questions:
>
> 1. What's the difference between these files ?

Compare "hexdump -C bad.sql" and "hexdump -C good.sql". There's a UTF-8 
Byte-Order-Mark at the beginning of bad.sql, see 
https://en.wikipedia.org/wiki/Byte_Order_Mark#UTF-8

> 2. Why 9.0 psql works fine with that difference, but 8.4 psql does not ?

Dunno, I'll let you investigate that ;-)

- Heikki

  3. Re: Strange behaviour with incompatible psql/server

    Pavan Deolasee <pavan.deolasee@gmail.com> — 2012-11-28T09:22:34Z

    On Wed, Nov 28, 2012 at 2:28 PM, Heikki Linnakangas <hlinnakangas@vmware.com
> wrote:

> On 28.11.2012 10:46, Pavan Deolasee wrote:
>
>> While I'm almost certain that this has something to do with special
>> characters that my naked eyes can not see, all my attempts to spot the
>> difference has failed. So I really have two questions:
>>
>> 1. What's the difference between these files ?
>>
>
> Compare "hexdump -C bad.sql" and "hexdump -C good.sql". There's a UTF-8
> Byte-Order-Mark at the beginning of bad.sql, see
> https://en.wikipedia.org/wiki/**Byte_Order_Mark#UTF-8<https://en.wikipedia.org/wiki/Byte_Order_Mark#UTF-8>
>
>
Thanks.

>
>  2. Why 9.0 psql works fine with that difference, but 8.4 psql does not ?
>>
>
> Dunno, I'll let you investigate that ;-)
>

So seems like this commit did the trick for later releases.

commit 93d3bac5648bddfe195a9cecc45ef0a2da5e85a8
Author: Peter Eisentraut <peter_e@gmx.net>
Date:   Sat Nov 21 23:59:12 2009 +0000

    Ignore UTF-8-encoded Unicode byte-order mark at the beginning of a file
if
    the client encoding is UTF-8.

    a limited version of a patch proposed by Itagaki Takahiro


Should this not be back patched ? The error that's coming because not
having this fix is rather very strange and hard to debug for any average
individual. I'd almost concluded that one should NEVER use an old psql with
a new server even though the warning that comes is not too glaring.

Thanks,
Pavan

  4. Re: Strange behaviour with incompatible psql/server

    Pavan Deolasee <pavan.deolasee@gmail.com> — 2012-11-28T09:46:23Z

    On Wed, Nov 28, 2012 at 2:52 PM, Pavan Deolasee <pavan.deolasee@gmail.com>wrote:

>
>
> Should this not be back patched ? The error that's coming because not
> having this fix is rather very strange and hard to debug for any average
> individual. I'd almost concluded that one should NEVER use an old psql with
> a new server even though the warning that comes is not too glaring.
>
>
Never mind. I just read the email thread leading to this commit. This
wasn't a bug fix per se and that's probably the reason why this was never
back patched. The discussion itself looked quite inconclusive as well.

Thanks,
Pavan