Hi Jan,
I fiddled around with the example you gave me and created the view with
the necessary triggers. Everything worked great up to this point.
I think in order to get this to work with jdbc though there may be need some
changes to the jdbc driver. When I tried to insert 16,000 characters worth of
text using
jdbc it failed with this error:
The SQL Statement is too long - INSERT INTO de_summary (dif_id, summary)
VALUES (3, 'XXX ... XXX'); // There are 32,000 X's.
This same test worked fine with 8000 characters. Any suggestions?
My code looks like this:
String sql = "INSERT INTO de_summary (dif_id, summary) "+
"VALUES (?, ?)";
PreparedStatement ps = new PreparedSQL(sql,2);
ps.setInt(1, summary.getDifId());
ps.setString(2, summary.getSummary());
ps.executeUpdate();
Thanks,
Chris
Jan Wieck wrote:
>
> > If you don't mind to have a somewhat crippled DB schema
> > during one release (7.0 until TOAST), let's see how we can
> > simulate LONG columns using a set of rules and triggers. Wait
> > some hours and I'll tell ya how it could work - if what I
> > have in mind works at all :-).
>
> Well,
>
> the attached example (longtest.sql) needs the improvements of
> current 7.0 BETA because 6.5 AFAIK doesn't support unlimited
> querystring size.
>
> It uses PL/pgSQL for functions, that could be replaced with
> some C equivalents for speed improvement and maybe checks to
> prevent unchanged LONG values from beeing updated to avoid
> unneccessary growth of the longtext_sink table if LONG values
> aren't modified.
>
> Also it has the limitation that you NEED to be able to
> identify the row's to be updated/deleted by some other
> field(s) in the rules (key in this case). But who really
> IDENTIFIES a row by a potentially multiple KB sized text
> field?
>
> Other than that, you'll have a "table" longtest with the
> fields
>
> key integer
> content text
>
> where you can INSERT/UPDATE/DELETE and SELECT on. In reality
> it is a view with some additional rewriting rules. The rules
> use the mentioned functions to unlimit the content attribute
> by turning the LONG value into an integer ID and reconstruct
> the value from it on SELECT. The view has a shadow table
> where only the small key and the LONG ID get stored.
>
> I haven't run any performance tests on it and expect it to be
> damned slow when accessing the LONG value. But you can search
> for phrases that are stored across chunk boundaries, because
> the LONG value is reconstructed on the fly and handed around
> in memory for all queries. And if a query doesn't SELECT the
> LONG value, it might be a win, because then the huge text
> isn't part of the tuples moved around in memory, sort sets
> and the like.
>
> Fiddle around with it and feel free to ask me if you need
> help to write the C equivalents of the functions to gain the
> required speed for production.
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #========================================= [EMAIL PROTECTED] (Jan Wieck) #
>
> ------------------------------------------------------------------------------
> Name: longtest.sql
> longtest.sql Type: Plain Text (text/plain)
> Encoding: 7bit
> Description: LONG datatype example for PG v7.0
--
Christopher D. Gokey, Raytheon ITSS, NASA/GCMD
18 Martin Road, Shelburne Falls, MA 01370
Phone: Voice (413) 625-8129 / FAX 208-248-9055
[EMAIL PROTECTED] / http://gcmd.nasa.gov
