[rt-devel] RT 1.3.48

[ivan]

On Mon, Mar 05, 2001 at 12:55:05AM -0500, Alex Pilosov wrote:
> On Sun, 4 Mar 2001, ivan wrote:
> 
> > The Pg backend is not prepared to handle nulls.  This is not news; this is
> > a known limitation.  Please read the pgsql-hackers mailing list archives
> > at postgresql.org if you are interested.
> Nono, actually, Pg backend IS prepared. bytea datatype does not do any
> interpretation of data passed to it, and does not use any C-string
> functions. 

The bytea datatype has no useful way to return data.

> What may be happening is perl Pg frontend not being prepared for it, and
> not properly quoting data before passing it to backend. 

Quite possible.  DBD::Pg seems to have several problems with this sort of
thing, as I said before - quote() is broken on some sorts of data, and
placeholders have a ~64k limit.

But bytea's problems aren't in the DBD::Pg layer.

> Escaping binary data for Pg is a tricky thing. Essentially, this works for
> psql: insert into foo values('\\000')
> Note the double backslash and the three-digit-octal representation of a
> character.  To verify that we actually inserted what you expected to, use
> octet_length(x) and get_byte(x,n) functions. 

Unfortunately, that's not a useful way to get data back out of the
database.  Much *worse* than base64-decoding.  And Pg-specific, whereas
the base-64 and length-limiting stuff would be useful for any database.

If we try to get bytea columns out of the database normally; the encoding
is incorrect and lossy;  nulls are *returned* as ASCII '\000' instead of a
zero byte. (s/\\000/\x00/g; ?  No.  Then you can't store the string
'\000'.) 

> *checking in perl Pg driver*
> 
> Yep, the same trick works. I do get proper, non-mangled data.

You can get it *in*, but you can't get it *out*.

> Ivan, can you see if this solves your problem?

Nope.

> You may need to write you
> own quote_bytea method (to properly quote binary data as above),

Nope, I'm not quoting data, I'm using placeholders.  As I said
before, there are several problems at the DBD::Pg layer.  We're also
working on fixing them, but accepting DBD::Pg 0.95 as a given, this
seems like the best fix.

> but it sure beats base64 encoding ;)

I disagree.  Base64-encoding has a space penalty, and a *small*
performance penalty to encode/decode the data.  Sucking data out of the
database with individual get_byte() SQL transactions would come at a heavy
performance cost. 

-- 
meow
_ivan