Thread: Strange behaviour with incompatible psql/server

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

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