syntax error but command executes anyway?

[Kevin Murphy]

Using PG 7.4.3 on Mac OS X 10.2.8, the following "insert into ... select ..."
statement completed and then announced a syntax error, which seems bizarre.

(Don't be confused by the fact that the two tables referred to
(public.identif iers and original.identi fiers) have slightly different column
names.)

egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT INTO public.identifi ers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identi fiers;
egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT 0 1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql:15: ERROR:
syntax error at or near "sourcecode " at character 2

A fuller psql transcript showing table layouts is below.

What should I make of this?

Thanks,
Kevin Murphy

P.S. Full transcript:

egenome_dev=# \d original.identi fiers
Table "original.ident ifiers"
Column | Type | Modifiers
------------+-----------------------+-----------
elementid | integer |
nameid | character varying(80) |
name | character varying(80) |
source | character varying(39) |
title | text |
sourcecode | character varying(2) |
Indexes:
"identifiers_el ementid_idx" btree (elementid)
"identifiers_na me_idx" btree (name)
"identifiers_na meid_idx" btree (nameid)
"identifiers_so urce_idx" btree (source)

egenome_dev=# select count(*) from original.identi fiers;
count
---------
1685440
(1 row)

egenome_dev=# \d identifiers
Table "public.identif iers"
Column | Type | Modifiers
-------------+-----------------------+-----------
element_id | integer |
name | character varying(80) |
source | character varying(39) |
source_code | character varying(2) |
title | text |
Indexes:
"identifiers_mu lti1_idx" btree (name, source)
"identifiers_na me_idx" btree (name)

[NOTE: the above indexes on original.identi fiers are not the intended final
indexes; in fact, I had forgotten that they were there.]

egenome_dev=# truncate identifiers;
TRUNCATE TABLE

egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT INTO public.identifi ers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identi fiers;

egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT 0 1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql:15: ERROR:
syntax error at or near "sourcecode " at character 2

egenome_dev=# select count(*) from identifiers;
count
---------
1672036
(1 row)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Scott Marlowe]

On Sat, 2004-06-19 at 07:14, Kevin Murphy wrote:

Using PG 7.4.3 on Mac OS X 10.2.8, the following "insert into ... select ..."
statement completed and then announced a syntax error, which seems bizarre.
It's not actually inserting anything...
(Don't be confused by the fact that the two tables referred to
(public.identif iers and original.identi fiers) have slightly different column
names.)

egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT INTO public.identifi ers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identi fiers;
egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT 0 1672036

This INSERT 0 part tells you it didn't actually insert anything.
Since I don't see any obvious error, I'm wondering if maybe there are
characters in the sql file that don't print that are causing this
problem. Try recreating the SQL by hand and see if you get the same
problem. Use the simplest editor on your system, like vi or notepad or
whatever.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Scott Marlowe]

On Sat, 2004-06-19 at 07:14, Kevin Murphy wrote:

Using PG 7.4.3 on Mac OS X 10.2.8, the following "insert into ... select ..."
statement completed and then announced a syntax error, which seems bizarre.
It's not actually inserting anything...
(Don't be confused by the fact that the two tables referred to
(public.identif iers and original.identi fiers) have slightly different column
names.)

egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT INTO public.identifi ers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identi fiers;
egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT 0 1672036

This INSERT 0 part tells you it didn't actually insert anything.
Since I don't see any obvious error, I'm wondering if maybe there are
characters in the sql file that don't print that are causing this
problem. Try recreating the SQL by hand and see if you get the same
problem. Use the simplest editor on your system, like vi or notepad or
whatever.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Kevin Murphy <mu****@genome. chop.edu> writes:

What should I make of this? egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT INTO public.identifi ers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identi fiers; egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT 0 1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql:15: ERROR:
syntax error at or near "sourcecode " at character 2

It seems mighty suspicious that psql is reporting a syntax error at line
15 of the file when cat is only showing 9 lines. I suspect that the
insert you are showing us did execute, but then something further on
in the file is producing the syntax error.

I am wondering whether cat on OS X stops at embedded nulls, or something
stupid like that. It sure looks like there must be garbage in the
port_identifier s.sql file beyond what cat has printed here. What do you
see when you examine the file with other tools? (Try "od -c" if nothing
else springs to mind.)

It is possible that the problem is not entirely cat's fault but has
something to do with the way that psql's \! command invokes cat.
Does cat at the shell prompt produce the same output?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Kevin Murphy <mu****@genome. chop.edu> writes:

What should I make of this? egenome_dev=# \!cat /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT INTO public.identifi ers (element_id, name, source, source_code, title)
SELECT DISTINCT
elementid,
name,
source,
sourcecode,
title
FROM original.identi fiers; egenome_dev=# \i /Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT 0 1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql:15: ERROR:
syntax error at or near "sourcecode " at character 2

It seems mighty suspicious that psql is reporting a syntax error at line
15 of the file when cat is only showing 9 lines. I suspect that the
insert you are showing us did execute, but then something further on
in the file is producing the syntax error.

I am wondering whether cat on OS X stops at embedded nulls, or something
stupid like that. It sure looks like there must be garbage in the
port_identifier s.sql file beyond what cat has printed here. What do you
see when you examine the file with other tools? (Try "od -c" if nothing
else springs to mind.)

It is possible that the problem is not entirely cat's fault but has
something to do with the way that psql's \! command invokes cat.
Does cat at the shell prompt produce the same output?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Kevin Murphy]

Scott,

egenome_dev=# \i
/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT 0 1672036

This INSERT 0 part tells you it didn't actually insert anything.

Actually, that's not true. I didn't know what this number was until just now,
but I looked it up: for a single-row insert, it's the OID of the new row;
otherwise, it's 0. The 1672036, on the other hand, means that 1,672,036 rows
were inserted.

-Kevin Murphy

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Kevin Murphy]

Scott,

egenome_dev=# \i
/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql
INSERT 0 1672036

This INSERT 0 part tells you it didn't actually insert anything.

Actually, that's not true. I didn't know what this number was until just now,
but I looked it up: for a single-row insert, it's the OID of the new row;
otherwise, it's 0. The 1672036, on the other hand, means that 1,672,036 rows
were inserted.

-Kevin Murphy

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Kevin Murphy]

On Saturday 19 June 2004 06:57 pm, Tom Lane wrote:

egenome_dev=# \i
/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql INSERT 0
1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql:15:
ERROR: syntax error at or near "sourcecode " at character 2

It seems mighty suspicious that psql is reporting a syntax error at line
15 of the file when cat is only showing 9 lines. I suspect that the
insert you are showing us did execute, but then something further on
in the file is producing the syntax error.

I am wondering whether cat on OS X stops at embedded nulls, or something
stupid like that. It sure looks like there must be garbage in the
port_identifier s.sql file beyond what cat has printed here. What do you
see when you examine the file with other tools? (Try "od -c" if nothing
else springs to mind.)

It is possible that the problem is not entirely cat's fault but has
something to do with the way that psql's \! command invokes cat.
Does cat at the shell prompt produce the same output?

regards, tom lane

It is indeed very weird, since the script is not that long. I piped it
through 'od -a' to confirm.

However, I retract my complaint, since I can't reproduce it!

I had to take a bad DIMM out of this machine a few days ago; maybe I need to
run that memory test again. Still, it would seem remarkable if a memory
error could produce my initial results rather than a crash or hang. I am
also feeling like compiling PG again, since it was initially compiled with
that bad DIMM (but again, what would the odds be?)

Tom, as always, thanks for your mellow and rapid response to questions.

I'll let you know if this crops up again in a reproducible way.

-Kevin Murphy

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Kevin Murphy]

On Saturday 19 June 2004 06:57 pm, Tom Lane wrote:

egenome_dev=# \i
/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql INSERT 0
1672036
psql:/Users/murphy/cvs/egora/sql/data_port/port_identifier s.sql:15:
ERROR: syntax error at or near "sourcecode " at character 2

It seems mighty suspicious that psql is reporting a syntax error at line
15 of the file when cat is only showing 9 lines. I suspect that the
insert you are showing us did execute, but then something further on
in the file is producing the syntax error.

I am wondering whether cat on OS X stops at embedded nulls, or something
stupid like that. It sure looks like there must be garbage in the
port_identifier s.sql file beyond what cat has printed here. What do you
see when you examine the file with other tools? (Try "od -c" if nothing
else springs to mind.)

It is possible that the problem is not entirely cat's fault but has
something to do with the way that psql's \! command invokes cat.
Does cat at the shell prompt produce the same output?

regards, tom lane

It is indeed very weird, since the script is not that long. I piped it
through 'od -a' to confirm.

However, I retract my complaint, since I can't reproduce it!

I had to take a bad DIMM out of this machine a few days ago; maybe I need to
run that memory test again. Still, it would seem remarkable if a memory
error could produce my initial results rather than a crash or hang. I am
also feeling like compiling PG again, since it was initially compiled with
that bad DIMM (but again, what would the odds be?)

Tom, as always, thanks for your mellow and rapid response to questions.

I'll let you know if this crops up again in a reproducible way.

-Kevin Murphy

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly