Questions for pgsql users (was Re: [Phpwiki-talk] pgsql bug)

[Jeff D. <da...@da...>]

After spending some time reading postgres docs, here's what
I think I've figured out.

It appears that a postgres TEXT is not binary safe.
TEXTs have associated character encodings.
It seems null bytes are explicitly not allowed within TEXTs.

Recent postgreses have a BYTEA type which meant for
storing binary data.

I see two solutions to the current problem:

1) Change the pgsql schema to use BYTEAs for the page and version
   metadata fields.

   This brings some compatibility concerns, since it appears that
   only recent versions of postgres support BYTEAs.
   (It first shows up in the 7.2 docs, though my 7.1 installation
   seems to have basic support for it.)

   BYTEAs are also a bit of a pain to deal with since data
   has to be double-escaped:
   
     select octet_length('a\0b'::bytea);  => 1

     select octet_length('a\\000b'::bytea);  => 3


2) Encode binary data (in this case the compressed cached page
   markup data) in some ASCII encoding. (probably base64).

   This has the advantages of maximum backwards compatibility,
   and of not requiring schema changes.

   (Disadvantage is 33% larger storage requirement for the
   binary data.)

For now, I favor approach #2.


QUESTIONS FOR THOSE WHO USE OR KNOW ABOUT POSTGRES:
==================================================

1. Am I missing something?

2. All you PhpWiki postgres users: what version of postgres do
   you run?  Does it support BYTEA?

3. Do you have opinions regarding solution #1 vs #2 (or #3)?


Jeff