Thread: Retrieving bytea column problem - they're are sometimes truncated
G'day all,
I've been hacking at this all day and have now been through my source and
then the driver source - grokking the entire postgres jdbc driver in the
process ;)
What happens is when I store binary content into a bytea field it works fine
(using setBinaryStream() or setBytes()). I've tested this by looking into
the DB at the actual content itself.
However when I retrive the content, it's sometimes truncated. (It appears to
be for content greater than about 25k but that's a very rough
approximation).
here's a snippet of my debugging code:
org.postgresql.jdbc2.ResultSet.java
if (fields[columnIndex - 1].getPGType().equals("bytea"))
{
String s = getString(columnIndex);
System.out.println("s = " + s);
byte[] result = PGbytea.toBytes(s);
System.out.println("result.length = " + result.length);
return result;
}
the result.length returned (in one example) is 59402 bytes, when the length
of the bytea field is actually 190608 bytes.
printing s to the console indeed reveals that it has been truncated.
(As a test I inserted a list of a few thousand emails addresses in order,
starting from a-z - it gets truncated around f - although the DB has all the
way through to z ;))
I looked deeper into the source for about 30 minutes but got a little lost
in pg_stream in QueryExecutor and figured I'd leave the rest of the
investigation up to the experts!
I couldn't find any related queries in the archives.
Can anyone help? Or provide insight as to where I should start looking?
Cheers,
Mike
Mike Cannon-Brookes :: mike@atlassian.com
Atlassian :: http://www.atlassian.com
Supporting YOUR J2EE World
Mike,
Which version of the database and drivers are you running?
thanks,
--Barry
Mike Cannon-Brookes wrote:
> G'day all,
>
> I've been hacking at this all day and have now been through my source and
> then the driver source - grokking the entire postgres jdbc driver in the
> process ;)
>
> What happens is when I store binary content into a bytea field it works fine
> (using setBinaryStream() or setBytes()). I've tested this by looking into
> the DB at the actual content itself.
>
> However when I retrive the content, it's sometimes truncated. (It appears to
> be for content greater than about 25k but that's a very rough
> approximation).
>
> here's a snippet of my debugging code:
>
> org.postgresql.jdbc2.ResultSet.java
>
> if (fields[columnIndex - 1].getPGType().equals("bytea"))
> {
> String s = getString(columnIndex);
> System.out.println("s = " + s);
> byte[] result = PGbytea.toBytes(s);
> System.out.println("result.length = " + result.length);
> return result;
> }
>
> the result.length returned (in one example) is 59402 bytes, when the length
> of the bytea field is actually 190608 bytes.
>
> printing s to the console indeed reveals that it has been truncated.
>
> (As a test I inserted a list of a few thousand emails addresses in order,
> starting from a-z - it gets truncated around f - although the DB has all the
> way through to z ;))
>
> I looked deeper into the source for about 30 minutes but got a little lost
> in pg_stream in QueryExecutor and figured I'd leave the rest of the
> investigation up to the experts!
>
> I couldn't find any related queries in the archives.
>
> Can anyone help? Or provide insight as to where I should start looking?
>
> Cheers,
> Mike
>
> Mike Cannon-Brookes :: mike@atlassian.com
>
> Atlassian :: http://www.atlassian.com
> Supporting YOUR J2EE World
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
Re: Retrieving bytea column problem - they're are sometimes truncated
From
"Mike Cannon-Brookes"
Date:
Barry,
Running postgres 7.1.3 and a freshly built driver from CVS.
-mike
> -----Original Message-----
> From: Barry Lind [mailto:barry@xythos.com]
> Sent: Sunday, November 18, 2001 7:46 AM
> To: mike@atlassian.com
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: Retrieving bytea column problem - they're are sometimes
> truncated
>
>
> Mike,
>
> Which version of the database and drivers are you running?
>
> thanks,
> --Barry
>
>
> Mike Cannon-Brookes wrote:
>
> > G'day all,
> >
> > I've been hacking at this all day and have now been through my
> source and
> > then the driver source - grokking the entire postgres jdbc driver in the
> > process ;)
> >
> > What happens is when I store binary content into a bytea field
> it works fine
> > (using setBinaryStream() or setBytes()). I've tested this by
> looking into
> > the DB at the actual content itself.
> >
> > However when I retrive the content, it's sometimes truncated.
> (It appears to
> > be for content greater than about 25k but that's a very rough
> > approximation).
> >
> > here's a snippet of my debugging code:
> >
> > org.postgresql.jdbc2.ResultSet.java
> >
> > if (fields[columnIndex - 1].getPGType().equals("bytea"))
> > {
> > String s = getString(columnIndex);
> > System.out.println("s = " + s);
> > byte[] result = PGbytea.toBytes(s);
> > System.out.println("result.length = " + result.length);
> > return result;
> > }
> >
> > the result.length returned (in one example) is 59402 bytes,
> when the length
> > of the bytea field is actually 190608 bytes.
> >
> > printing s to the console indeed reveals that it has been truncated.
> >
> > (As a test I inserted a list of a few thousand emails addresses
> in order,
> > starting from a-z - it gets truncated around f - although the
> DB has all the
> > way through to z ;))
> >
> > I looked deeper into the source for about 30 minutes but got a
> little lost
> > in pg_stream in QueryExecutor and figured I'd leave the rest of the
> > investigation up to the experts!
> >
> > I couldn't find any related queries in the archives.
> >
> > Can anyone help? Or provide insight as to where I should start looking?
> >
> > Cheers,
> > Mike
> >
> > Mike Cannon-Brookes :: mike@atlassian.com
> >
> > Atlassian :: http://www.atlassian.com
> > Supporting YOUR J2EE World
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> >
>
>
>
Re: Retrieving bytea column problem - they're are sometimes truncated
From
"Mike Cannon-Brookes"
Date:
Any ideas on this? Do I need to install 7.2 beta to use bytea columns? (I'm doubtful that would make a difference in this case). Can someone more knowledgeable about the driver internals point me to where I can start to debug inside QueryExectutor / pg_stream? -mike Mike Cannon-Brookes :: mike@atlassian.com Atlassian :: http://www.atlassian.com Supporting YOUR J2EE World > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Mike Cannon-Brookes > Sent: Sunday, November 18, 2001 9:44 AM > To: Barry Lind > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] Retrieving bytea column problem - they're are > sometimes truncated > > > Barry, > > Running postgres 7.1.3 and a freshly built driver from CVS. > > -mike > > > > -----Original Message----- > > From: Barry Lind [mailto:barry@xythos.com] > > Sent: Sunday, November 18, 2001 7:46 AM > > To: mike@atlassian.com > > Cc: pgsql-jdbc@postgresql.org > > Subject: Re: Retrieving bytea column problem - they're are sometimes > > truncated > > > > > > Mike, > > > > Which version of the database and drivers are you running? > > > > thanks, > > --Barry > > > > > > Mike Cannon-Brookes wrote: > > > > > G'day all, > > > > > > I've been hacking at this all day and have now been through my > > source and > > > then the driver source - grokking the entire postgres jdbc > driver in the > > > process ;) > > > > > > What happens is when I store binary content into a bytea field > > it works fine > > > (using setBinaryStream() or setBytes()). I've tested this by > > looking into > > > the DB at the actual content itself. > > > > > > However when I retrive the content, it's sometimes truncated. > > (It appears to > > > be for content greater than about 25k but that's a very rough > > > approximation). > > > > > > here's a snippet of my debugging code: > > > > > > org.postgresql.jdbc2.ResultSet.java > > > > > > if (fields[columnIndex - 1].getPGType().equals("bytea")) > > > { > > > String s = getString(columnIndex); > > > System.out.println("s = " + s); > > > byte[] result = PGbytea.toBytes(s); > > > System.out.println("result.length = " + result.length); > > > return result; > > > } > > > > > > the result.length returned (in one example) is 59402 bytes, > > when the length > > > of the bytea field is actually 190608 bytes. > > > > > > printing s to the console indeed reveals that it has been truncated. > > > > > > (As a test I inserted a list of a few thousand emails addresses > > in order, > > > starting from a-z - it gets truncated around f - although the > > DB has all the > > > way through to z ;)) > > > > > > I looked deeper into the source for about 30 minutes but got a > > little lost > > > in pg_stream in QueryExecutor and figured I'd leave the rest of the > > > investigation up to the experts! > > > > > > I couldn't find any related queries in the archives. > > > > > > Can anyone help? Or provide insight as to where I should > start looking? > > > > > > Cheers, > > > Mike > > > > > > Mike Cannon-Brookes :: mike@atlassian.com > > > > > > Atlassian :: http://www.atlassian.com > > > Supporting YOUR J2EE World > > > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
"Mike Cannon-Brookes" <mike@atlassian.com> writes:
> Any ideas on this? Do I need to install 7.2 beta to use bytea columns?
No, bytea's been around for a long time. I'm betting that you are
looking at a bug in the JDBC driver ... but I don't know enough about
JDBC to help debug it.
A possible starting point: do you see similar truncation on long
values of non-bytea types, eg text?
regards, tom lane
Mike, I hope to have time tonight to look into this bug. What would make that easier would be if you could provide a test case that demonstrates the problem. A simple create table foo then run this java main to see the problem would be great. thanks, --Barry Mike Cannon-Brookes wrote: > Any ideas on this? Do I need to install 7.2 beta to use bytea columns? (I'm > doubtful that would make a difference in this case). > > Can someone more knowledgeable about the driver internals point me to where > I can start to debug inside QueryExectutor / pg_stream? > > -mike > > > Mike Cannon-Brookes :: mike@atlassian.com > > Atlassian :: http://www.atlassian.com > Supporting YOUR J2EE World > > > > >>-----Original Message----- >>From: pgsql-jdbc-owner@postgresql.org >>[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Mike Cannon-Brookes >>Sent: Sunday, November 18, 2001 9:44 AM >>To: Barry Lind >>Cc: pgsql-jdbc@postgresql.org >>Subject: Re: [JDBC] Retrieving bytea column problem - they're are >>sometimes truncated >> >> >>Barry, >> >>Running postgres 7.1.3 and a freshly built driver from CVS. >> >>-mike >> >> >> >>>-----Original Message----- >>>From: Barry Lind [mailto:barry@xythos.com] >>>Sent: Sunday, November 18, 2001 7:46 AM >>>To: mike@atlassian.com >>>Cc: pgsql-jdbc@postgresql.org >>>Subject: Re: Retrieving bytea column problem - they're are sometimes >>>truncated >>> >>> >>>Mike, >>> >>>Which version of the database and drivers are you running? >>> >>>thanks, >>>--Barry >>> >>> >>>Mike Cannon-Brookes wrote: >>> >>> >>>>G'day all, >>>> >>>>I've been hacking at this all day and have now been through my >>>> >>>source and >>> >>>>then the driver source - grokking the entire postgres jdbc >>>> >>driver in the >> >>>>process ;) >>>> >>>>What happens is when I store binary content into a bytea field >>>> >>>it works fine >>> >>>>(using setBinaryStream() or setBytes()). I've tested this by >>>> >>>looking into >>> >>>>the DB at the actual content itself. >>>> >>>>However when I retrive the content, it's sometimes truncated. >>>> >>>(It appears to >>> >>>>be for content greater than about 25k but that's a very rough >>>>approximation). >>>> >>>>here's a snippet of my debugging code: >>>> >>>>org.postgresql.jdbc2.ResultSet.java >>>> >>>>if (fields[columnIndex - 1].getPGType().equals("bytea")) >>>>{ >>>> String s = getString(columnIndex); >>>> System.out.println("s = " + s); >>>> byte[] result = PGbytea.toBytes(s); >>>> System.out.println("result.length = " + result.length); >>>> return result; >>>>} >>>> >>>>the result.length returned (in one example) is 59402 bytes, >>>> >>>when the length >>> >>>>of the bytea field is actually 190608 bytes. >>>> >>>>printing s to the console indeed reveals that it has been truncated. >>>> >>>>(As a test I inserted a list of a few thousand emails addresses >>>> >>>in order, >>> >>>>starting from a-z - it gets truncated around f - although the >>>> >>>DB has all the >>> >>>>way through to z ;)) >>>> >>>>I looked deeper into the source for about 30 minutes but got a >>>> >>>little lost >>> >>>>in pg_stream in QueryExecutor and figured I'd leave the rest of the >>>>investigation up to the experts! >>>> >>>>I couldn't find any related queries in the archives. >>>> >>>>Can anyone help? Or provide insight as to where I should >>>> >>start looking? >> >>>>Cheers, >>>>Mike >>>> >>>>Mike Cannon-Brookes :: mike@atlassian.com >>>> >>>>Atlassian :: http://www.atlassian.com >>>> Supporting YOUR J2EE World >>>> >>>> >>>> >>>>---------------------------(end of >>>> >>broadcast)--------------------------- >> >>>>TIP 4: Don't 'kill -9' the postmaster >>>> >>>> >>>> >>> >>> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster >> >
Mike,
I tried but couldn't reproduce this problem. Below is the code I ran to
try to reproduce. I tried for various different sizes of content (with
my last run being 658890 bytes without any problem).
In order to research this more I will need a test case that reproduces
the problem you are seeing.
My environment:
Linux 2.4.7, PG 7.2b2, JDBC current CVS
(test program below requires the following table):
create table test (cola bytea);
import java.io.*;
import java.sql.*;
public class test2 {
public static void main(String[] p_args) {
try {
Class.forName("org.postgresql.Driver");
Connection l_conn;
l_conn =
DriverManager.getConnection("jdbc:postgresql://localhost:5432/files",
"blind", "");
l_conn.setAutoCommit(false);
PreparedStatement l_stmt = l_conn.prepareStatement("insert into
test values (?)");
Statement l_stmt2 = l_conn.createStatement();
StringBuffer l_strBuf = new StringBuffer();
for (int i = 0; i< 10000; i++) {
l_strBuf.append("-" + i + "-");
l_strBuf.append("123456789012345678901234567890123456789012345678901234567890");
}
byte[] l_value = l_strBuf.toString().getBytes();
System.out.println("insert length = " + l_value.length);
l_stmt.setBytes(1,l_value);
l_stmt.executeUpdate();
l_conn.commit();
ResultSet l_rset = l_stmt2.executeQuery("select cola from test");
while (l_rset.next()) {
byte[] l_result = l_rset.getBytes(1);
System.out.println("select length = " + l_result.length);
// System.out.println(new String(l_result));
}
} catch (Exception l_se) {
System.out.println(l_se.toString());
}
}
The results from running the above was:
$ java test2
insert length = 658890
select length = 658890
As I mentioned I tried with various different values of i in the program
but I always retrieved the same amount of data as I inserted.
thanks,
--Barry
Mike Cannon-Brookes wrote:
> Barry,
>
> Running postgres 7.1.3 and a freshly built driver from CVS.
>
> -mike
>
>
>
>>-----Original Message-----
>>From: Barry Lind [mailto:barry@xythos.com]
>>Sent: Sunday, November 18, 2001 7:46 AM
>>To: mike@atlassian.com
>>Cc: pgsql-jdbc@postgresql.org
>>Subject: Re: Retrieving bytea column problem - they're are sometimes
>>truncated
>>
>>
>>Mike,
>>
>>Which version of the database and drivers are you running?
>>
>>thanks,
>>--Barry
>>
>>
>>Mike Cannon-Brookes wrote:
>>
>>
>>>G'day all,
>>>
>>>I've been hacking at this all day and have now been through my
>>>
>>source and
>>
>>>then the driver source - grokking the entire postgres jdbc driver in the
>>>process ;)
>>>
>>>What happens is when I store binary content into a bytea field
>>>
>>it works fine
>>
>>>(using setBinaryStream() or setBytes()). I've tested this by
>>>
>>looking into
>>
>>>the DB at the actual content itself.
>>>
>>>However when I retrive the content, it's sometimes truncated.
>>>
>>(It appears to
>>
>>>be for content greater than about 25k but that's a very rough
>>>approximation).
>>>
>>>here's a snippet of my debugging code:
>>>
>>>org.postgresql.jdbc2.ResultSet.java
>>>
>>>if (fields[columnIndex - 1].getPGType().equals("bytea"))
>>>{
>>> String s = getString(columnIndex);
>>> System.out.println("s = " + s);
>>> byte[] result = PGbytea.toBytes(s);
>>> System.out.println("result.length = " + result.length);
>>> return result;
>>>}
>>>
>>>the result.length returned (in one example) is 59402 bytes,
>>>
>>when the length
>>
>>>of the bytea field is actually 190608 bytes.
>>>
>>>printing s to the console indeed reveals that it has been truncated.
>>>
>>>(As a test I inserted a list of a few thousand emails addresses
>>>
>>in order,
>>
>>>starting from a-z - it gets truncated around f - although the
>>>
>>DB has all the
>>
>>>way through to z ;))
>>>
>>>I looked deeper into the source for about 30 minutes but got a
>>>
>>little lost
>>
>>>in pg_stream in QueryExecutor and figured I'd leave the rest of the
>>>investigation up to the experts!
>>>
>>>I couldn't find any related queries in the archives.
>>>
>>>Can anyone help? Or provide insight as to where I should start looking?
>>>
>>>Cheers,
>>>Mike
>>>
>>>Mike Cannon-Brookes :: mike@atlassian.com
>>>
>>>Atlassian :: http://www.atlassian.com
>>> Supporting YOUR J2EE World
>>>
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 4: Don't 'kill -9' the postmaster
>>>
>>>
>>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>