Strange behaviour with incompatible psql/server

From: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Strange behaviour with incompatible psql/server
Date: 2012-11-28 08:46:20
Message-ID: CABOikdMDUL1U7j4cWNtMUF9edUfQmefRtgS8jB+ZhfpAUq3gUw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
bad.sql application/octet-stream 292 bytes
good.sql application/octet-stream 289 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2012-11-28 08:58:22 Re: Strange behaviour with incompatible psql/server
Previous Message Hari Babu 2012-11-28 08:27:50 Re: pg_basebackup is taking backup of extra files inside a tablespace directory