I've run your test and I can confirm the error. I've looked at the
protocol traffic with Wireshark, and the back-end is clearly lying
about the format of the results in this particular case: as you
stated, the row description says text, but the data is in binary.
I also wrote a simple Java program (on github at
https://github.com/deafbybeheading/postgresql-result-format-testcase ;
I didn't know if uninvited attachments were kosher) to minimize the
test case. Note that the program relies on trust authentication to be
on.
Since triggering the set of FEBE messages that leads to this was tied
deep into the guts of JDBC, I opted for raw wire protocol. It looks
like the following sequence of messages from the client leads to this
result format mixup:
1. Parse, with statement name "S_1", sql "SELECT $1::int", and
parameter type oid 0 (ask server to guess)
2. Describe "S_1"
3. Bind "S_1" to anonymous portal, with param formats [ 0 (text) ],
param values [ '2' ], result formats [ 1 (binary) ]
4. Execute anonymous portal (returning all rows)
5. Sync
I have not tried to narrow this further yet. This essentially
reproduces what Radosław was seeing but outside of the JDBC driver.
That is, the server responds with:
1. Parse completion
2. Parameter description, with type oids [ 23 ]
3. Row description, with empty table data, type oids [ 23 ], type
length 4, type mod -1, and format 0 (text)
4. Bind completion
5. DataRow, with the result in binary, as requested, but not as
described in message (3) above
6. Command completion & RFQ
For what it's worth, I'm running this against an 8.4 server:
PostgreSQL 8.4.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
I'll try to look into this some more, but first of all, is the client
sequence above valid? As far as I can tell, it is. I'm guessing that
the root of the problem is providing the parameter as text and
requesting it back in binary, but I'll freely admit my knowledge of
the internals here (well, beyond the protocol itself) is very limited.
Thanks,
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com