I get the following error:

org.postgresql.util.PSQLException: ERROR: invalid byte sequence for
encoding "UTF8": 0x00
at
org.postgresql.core.v3.QueryExecutorImpl.receiveEr rorResponse(QueryExecutorImpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:191)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eWithFlags(AbstractJdbc2Statement.java:351)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:344)
[sni, goes into our code]

The statement is an insert into a table with various columns, one of
them being a bytea column.

The data base has locale C, encoding UTF-8, the "file.encoding" java
property is set to UTF-8 too, I think it should be OK if I'm not missing
something else...

The biggest problem is that I cant reproduce it in a self contained test
case, although in our system is consistently happening with the same
data.

I tried to set all the parameters with the same data as in our system,
including the byte array used to populate the bytea field, but in the
independent test the insert succeeds...

I tried to set the JDBC driver/server debug level to the highest
possible values, but I seem unable to get a more detailed error message
(in particular about which parameter is wrong, and what was it's value),
so I'm not completely sure I have the right values in the independent
test case (I got them from debugging our system and copy paste the
values).

Any ideas how could I further debug this ? It's show-stopper for us in
adopting 8.2.

Thanks,
Csaba.



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Wed, 17 Jan 2007, Csaba Nagy wrote:

> I get the following error:
>
> org.postgresql.util.PSQLException: ERROR: invalid byte sequence for
> encoding "UTF8": 0x00
> at
>
> Any ideas how could I further debug this ? It's show-stopper for us in
> adopting 8.2.
>

This will actually happen in 8.1 servers if you are running the latest
releases. Previously, inserting something like "a\0b" would silently
truncate at the null byte, but now it's an error. So, check your data for
\0.

Kris Jurka

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

http://www.postgresql.org/docs/faq

> This will actually happen in 8.1 servers if you are running the latest
> releases. Previously, inserting something like "a\0b" would silently
> truncate at the null byte, but now it's an error. So, check your data for
> \0.

Well, if the 0 bytes are in the byte array, I would expect that to be
OK, the driver should escape them, right ? (I actually tested this, it
works).

Other than that, the parameters are plain strings and primitives, so I
would also be surprised to be able to insert invalid 0 bytes in those...
Could it be that we have some strange characters not properly encoded by
the driver ?

Thanks,
Csaba.



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

On Wed, 17 Jan 2007, Csaba Nagy wrote:

>> This will actually happen in 8.1 servers if you are running the latest
>> releases. Previously, inserting something like "a\0b" would silently
>> truncate at the null byte, but now it's an error. So, check your data for
>> \0.
>
> Well, if the 0 bytes are in the byte array, I would expect that to be
> OK, the driver should escape them, right ? (I actually tested this, it
> works).

Right, bytea data can contain 0.

> Other than that, the parameters are plain strings and primitives, so I
> would also be surprised to be able to insert invalid 0 bytes in those...
> Could it be that we have some strange characters not properly encoded by
> the driver ?
>

Java is not C where 0 is a string terminator. Java String objects may
contain embedded 0 characters. The driver encodes these correctly and
sends them to the server which rejects them. The server being written in
C has code everywhere that treats 0 as a terminator and it cannot
correctly handle embedded 0s. You have 0s somewhere in your string data.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Wed, 2007-01-17 at 18:13, Csaba Nagy wrote:
> > This will actually happen in 8.1 servers if you are running the latest
> > releases. Previously, inserting something like "a\0b" would silently
> > truncate at the null byte, but now it's an error. So, check your data for
> > \0.

OK, I was able to reproduce it using:

Connection connection = getConnection();
Statement statement = connection.createStatement();
statement.execute("create table test_null_in_string (data text)");
PreparedStatement prepStatement =
connection.prepareStatement("insert into test_null_in_string values
(?)");
String test = new String(new byte[] {1, 2, 3, 0, 1, 2, 3}, "US-ASCII");
prepStatement.setString(1, test);
// will throw exception
prepStatement.executeUpdate();


So it is easily possible... now I have to check how it happens in our
system, but I'm afraid I won't like the implications :-/

Cheers,
Csaba.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match