Re: JDBC compressed stream

Javier wrote:
>
> Hi, I'm developing a java client application that uses JDBC to access
> a PostGIS/PostgreSQL database. I'm working with PostgreSQL JDBC driver
> Type 4 (http://jdbc.postgresql.org/) and I need to compress the output
> streamdata from database queries.
>
> The problem can't be resolved making a better WHERE clause, because I
> have to received a great amount of GIS information, and this info can be
> highly compressed. Moreover, this data can't be stored compressed in the
> database, so, an external compressor is needed.
>
> Anybody can explain me if it is possible to compress a JDBC datastream
> ??? Is this a PostgreSQL JDBC driver issue??? or a new function must be
> added to PostgreSQL???
>
> Last question --> Anyone knows any other way to achieve compression
> with other kind of solution???

you could add a C function to the Postgres server engine which provides the
compression on a field by field basis, then have a corresponding Java function
to decompress the results after you recieve them.

You would use this function on a select statement something like...

SELECT field1, fieldcompress(field2) AS field2,
fieldcompress(field3) AS field3 FROM tablename
WHERE ...;

These sorts of user C functions can be added to the database server at runtime,
you compile the C to a .so shared object file (.DLL on Windows), and load it
into the database server with a CREATE FUNCTION statement.
See http://www.postgresql.org/docs/8.0/static/xfunc-c.html for how you develop,
install, and use these sorts of functions.

otherwise, AFAIK, you'd have to design a new compressed data protocol for
pgsql, implement it in the postgres postmaster, and implement it in the jdbc
client library (as well as pgsql.so for the client applications that aren't
written in Java).