Forum OpenACS Q&A: lztext crashes over 8K

Posted by Bob OConnor on 08/10/00 03:35 PM

I am using PG7.0 and OpenACS 3.2.2 and the lztext for long text gathered from a textbox. When I paste in text that is about 12K, The database barfs.

Here is the error message:

Error: Ns_PgExec: result status: 7 message: ERROR:  Tuple is too 
big: size 8492, max size 8140

I am stuffing the database using ..., bigtext ='$QQbigtext',... into a bigtext field with type lztext.

I understand the limit of a textarea is 32K so how do I extend lztext to match/exceed the textarea limit. Also, does textarea gracefully fail over 32K and truncate the text over 32K without throwing an error? TIA

-Bob

2: Response to lztext crashes over 8K (response to 1)

Posted by Don Baccus on 08/10/00 04:27 PM

In the documentation I give instructions on how to build Postgres to use a blocksize of greater than 8KB, and my personal recommendation has always been to use a blocksize of at least 16KB to run OpenACS. This will give you lztext fields of 24KB or so, at least - if you want to absolutely guarantee you can insert a 32KB textarea choose an even larger blocksize.

Rows in Postgres are variable length, so you don't have to worry about each row occupying a full block.

I've avoided putting in limit checks for inserts because version 7.1 of Postgres will have segmented, compressed long types (including text), which will remove the size limit altogether.

3: Response to lztext crashes over 8K (response to 1)

Posted by Bob OConnor on 08/11/00 04:16 PM

Yes, I saw the docs about blocksize... It appears that I would need to do a new installation of pg and loose my current data ? Has anyone saved their data, recompiled pg from 8k to 16k and re-loaded their data successfully? How soon will 7.1 be available which will "solve the problem"?

As a stop gap measure, how would I put a limit check on data before insert/update? It appears that the limit has an effect on the total size of all fields being updated. I can do seperate sql updates for each big field within a begin/end transaction...or do I need to release and get a new handle. In other words, is the tuple the entire size of all the fields together?

I've tried to move text around between textareas but it seems that perhaps ?? some whitespace in the textarea gets counted although I can't see it.

TIA -Bob

4: Response to lztext crashes over 8K (response to 1)

Posted by Don Baccus on 08/11/00 08:17 PM

All you need to do is pg_dump your database, compile your new PG, and reload the dump. There might be problems in the dump file regarding ordering (sometimes things are referenced before they're defined in dump files, already fixed for known cases in the current PG 7.1 sources, so you don't have too suffer this much longer). If you run into this, it's not a big deal - the dump is just a text file with a bunch of CREATE commands to build your schema and COPY commands to add the data.

I did this with my own system, changing from type TEXT to type LZTEXT at the same time (by editing the dump) and had no problem.

7.1 is due out this fall.

The size limit is on the row itself, i.e. a row in the database is limited to the size of a database block. 7.1 will store large columns outside the block automatically (much as you do "by hand" in Oracle with CLOBs etc).