Thread: Retrieving bytea column problem - they're are sometimes truncated

Retrieving bytea column problem - they're are sometimes truncated

From
"Mike Cannon-Brookes"
Date:
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



Re: Retrieving bytea column problem - they're are sometimes truncated

From
Barry Lind
Date:
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


Re: Retrieving bytea column problem - they're are sometimes truncated

From
Tom Lane
Date:
"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

Re: Retrieving bytea column problem - they're are sometimes truncated

From
Barry Lind
Date:
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
>>
>



Re: Retrieving bytea column problem - they're are sometimes truncated

From
Barry Lind
Date:
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
>
>