Jose' & Tom,
I had a similar problem running on Mac OS X Server (Panther). For example:
DROP TABLE test1;
CREATE TABLE test1 (
id1 INTEGER PRIMARY KEY,
id2 INT NOT NULL,
data VARCHAR(255) NOT NULL
);
DROP TABLE test2;
CREATE TABLE test2 (
id2 INTEGER PRIMARY KEY,
data VARCHAR(255) NOT NULL
);
INSERT INTO test1 (id1, id2, data) VALUES (1, 1, 'A');
INSERT INTO test1 (id1, id2, data) VALUES (2, 1, 'B');
INSERT INTO test1 (id1, id2, data) VALUES (3, 1, 'C');
INSERT INTO test1 (id1, id2, data) VALUES (4, 2, 'D');
INSERT INTO test1 (id1, id2, data) VALUES (5, 2, 'E');
INSERT INTO test1 (id1, id2, data) VALUES (6, 2, 'F');
INSERT INTO test1 (id1, id2, data) VALUES (7, 2, 'G');
INSERT INTO test1 (id1, id2, data) VALUES (8, 3, 'H');
INSERT INTO test2 (id2, data) VALUES (1, 'z');
INSERT INTO test2 (id2, data) VALUES (2, 'y');
INSERT INTO test2 (id2, data) VALUES (3, 'x');
SELECT data FROM test1 WHERE id2 = 2;
[fails with ctid error]
SELECT test1.data FROM test1, test2 WHERE (test1.id2 = test2.id2) AND
(test1.id2 = 2)
[works as expected]
It does appear to be a problem with the ODBC 7.03.02.00 driver -- queries
passing through JDBC or psql do not exhibit the behavior. Queries were being
made against PostgreSQL 7.4.
The workaround in my case was to turn off "Updatable Cursors" in the DSN
datasource options (page 2). By default, it is enabled.
A message from Hiroshi Inoue in the archives indicates that updatable
cursors are not supported by the drive -- maybe a short term solution is to
make the default setting be disabled (or be removed completely.)
-ch
> at a point the postgresql ODBC driver issues a command like this (not
> questioning here how useful or correct it could be)
>
> select * from table1 where ctid = '(,)';
>
> this command works (?) returning zero rows without error, even from
> psql,
> when the db is hosted in the following systems:
>
> - debian i386 using the stable postgresql7.2.1-2 package
> - debian ppc using the stable postgresql7.2.1-2 package
> - debian i386 using the testing postgresql7.4.2-2 package
> - MacOSX 10.2 using the 7.3.4 compiled from source
>
>
> but when hosted in a MacOSX 10.3 (Panther) using postgres 7.4.x, or
> 7.3.x
> compiled from source we get:
>
> (in 7.4) ERROR: invalid input syntax for type tid: "(,)"
> or
> (in 7.3) ERROR: tidin: invalid value
>
> I've tried on three different machines and a miriad of different
> configure parameters and always get the same error...
>
> probably is an incompatible version of some external lib?, which one?
> who is responsible of the parsing of the command?
>> select * from table1 where ctid = '(,)';
>> [ works on some machines and not others ]
>
> I would argue that the ODBC driver is broken and needs to be fixed,
> as does the TID input parser which should never allow this ...
>
> However, the reason for the platform dependency is probably that
> strtoul() is setting errno on some machines and not others.
>
> regards, tom lane