A bugfix release.
A schema change for boolean types that makes…
Postgres support works better. (Still have to deal with the lack of
BLOB support for B1. Got patches from
Ivan already)
bin/rt --id <int> should now work
jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90
Gur SOV jnagf gb znxr guvf fvt vyyrtny.
A bugfix release.
A schema change for boolean types that makes…
Postgres support works better. (Still have to deal with the lack of
BLOB support for B1. Got patches from
I thought you’ll rely on TOAST features of 7.1 and not use BLOBs for
postgres?
-alex
Postgres support works better. (Still have to deal with the lack of BLOB support for B1. Got patches fromI thought you’ll rely on TOAST features of 7.1 and not use BLOBs for
postgres?
As I understand it, toast-based columns aren’t embedded-null-safe.
The plan is currently to BASE64 encode attachments that contain embedded nulls
if the database engine (like PG7.1) doesn’t have support for a true blob
type.
-j
-alex
jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90
“It’s buried in the desert, got sand in it, melts Nazis. You know,
the Ark of the Covenant” – siva
As I understand it, toast-based columns aren’t embedded-null-safe.
The plan is currently to BASE64 encode attachments that contain embedded nulls
if the database engine (like PG7.1) doesn’t have support for a true blob
type.
a) I’m not sure about embedded nulls, AFAIK, if the field is declared of
type ‘bytea’ it must be embedded-null-safe.
b) If you chose to base64 encode, then you can use TOASTs and forget about
blobs…Or that’s what you do?
-alex
As I understand it, toast-based columns aren’t embedded-null-safe.
The plan is currently to BASE64 encode attachments that contain embedded nulls
if the database engine (like PG7.1) doesn’t have support for a true blob
type.a) I’m not sure about embedded nulls, AFAIK, if the field is declared of
type ‘bytea’ it must be embedded-null-safe.
Nope. Try it. 7.2 should have a proper binary type, but 7.1 doesn’t.
b) If you chose to base64 encode, then you can use TOASTs and forget about
blobs…Or that’s what you do?
That’s correct.
The thing that’s actually causing me the most aggrivation is
DBD::Pg. It’s quote() method is broken and placeholders segfault if you
try to insert more than ~64k of data. Ick.
meow
_ivan
a) I’m not sure about embedded nulls, AFAIK, if the field is declared of
type ‘bytea’ it must be embedded-null-safe.Nope. Try it. 7.2 should have a proper binary type, but 7.1 doesn’t.
Hmm. How did you test it? What problems you got?
-alex
a) I’m not sure about embedded nulls, AFAIK, if the field is declared of
type ‘bytea’ it must be embedded-null-safe.Nope. Try it. 7.2 should have a proper binary type, but 7.1 doesn’t.
Hmm. How did you test it?
I tried to insert and retrieve data.
What problems you got?
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.
meow
_ivan
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.
What may be happening is perl Pg frontend not being prepared for it, and
not properly quoting data before passing it to backend.
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.
checking in perl Pg driver
Yep, the same trick works. I do get proper, non-mangled data.
Ivan, can you see if this solves your problem? You may need to write you
own quote_bytea method (to properly quote binary data as above), but it
sure beats base64 encoding 
-alex
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
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’.)
No. String \000 will be stored in database as \000, and it does differ
from \000 which is a representation for a null character.
Here’s how you must correctly decode data after getting them out of Pg:
sub unquote($) {
$=shift;
s/(^|[^\])\(\d\d\d)/chr(oct($2))/ge;
s/\\/\/;
return $;
}
“A fair jaw-cracker dwarf-language must be”.
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.
Make it generic. Write a db_quote/db_unquote which would transform binary
into db-specific form. For Pg, it should use the functions above to do it.
For other dbs, do base64.
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’.)
No. String \000 will be stored in database as \000, and it does differ
from \000 which is a representation for a null character.
Erm, correction: I meant ‘retrieved’ not stored.
-alex
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’.)
No. String \000 will be stored in database as \000, and it does differ
from \000 which is a representation for a null character.
Interesting, but completely unacceptable. As I said, this is why the
bytea type is useless.
Here’s how you must correctly decode data after getting them out of Pg:
You shouldn’t have to decode data after getting it out of the database.
Similarly, you shouldn’t have to encode or quote data when using
placeholders.
If you’d like to fix this problem, provide a fix to DBD::Pg that speaks to
the backend correctly and post it to pgsql-interfaces@postgresql.org and
dbi-dev@perl.org. RT is not the place to fix these problems. Have a
nice day.
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.
Make it generic. Write a db_quote/db_unquote which would transform binary
into db-specific form. For Pg, it should use the functions above to do it.
For other dbs, do base64.
You may wish to do so, but I don’t. For RT’s purposes, I think it’s the
wrong decision to include Pg-specific code that will break when the bugs
in DBD::Pg are fixed.
meow
_ivan
Hiya,
Sorry for replying to this old email, but I just noticed that DBD::Pg
0.98 has my patch to fix bytea support, so I hope it’ll be at least an
option to stick mime files directly as bytea fields.
-alex
You shouldn’t have to decode data after getting it out of the database.
Similarly, you shouldn’t have to encode or quote data when using
placeholders.
If you’d like to fix this problem, provide a fix to DBD::Pg that speaks to
the backend correctly and post it to pgsql-interfaces@postgresql.org and
dbi-dev@perl.org. RT is not the place to fix these problems. Have a
nice day.
Done.
Hiya,
Sorry for replying to this old email, but I just noticed that DBD::Pg
0.98 has my patch to fix bytea support, so I hope it’ll be at least an
option to stick mime files directly as bytea fields.
I agree, but we’ll have to wait for Jesse to get back to actually
make it happen in the official source. 
Thanks for bearing with me being pigheaded about getting DBD::Pg to behave
like a proper DBD. The author was unresponsive to patches for a while
there, I was getting worried, but this and the 64k placeholder bug seem to
be resolved.
You’ll need to update the BinarySafeBLOBs subroutine in
DBIx::SearchBuilder Handle/Pg.pm, and you’ll need to update your schema.
If you’re doing a fresh RT installation you’ll need DBIx::DBSchema from
CVS (Jesse, poke me whenever you like and I’ll do a CPAN release) and
you’ll need to run `make genschema’ in the RT tree.
I’d be curious to know how it works out for you.
-alex
You shouldn’t have to decode data after getting it out of the database.
Similarly, you shouldn’t have to encode or quote data when using
placeholders.If you’d like to fix this problem, provide a fix to DBD::Pg that speaks to
the backend correctly and post it to pgsql-interfaces@postgresql.org and
dbi-dev@perl.org. RT is not the place to fix these problems. Have a
nice day.
Done.
Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel
meow
_ivan
Hiya,
Sorry for replying to this old email, but I just noticed that DBD::Pg
0.98 has my patch to fix bytea support, so I hope it’ll be at least an
option to stick mime files directly as bytea fields.I agree, but we’ll have to wait for Jesse to get back to actually
make it happen in the official source.
grin I just got home. I expect the first half of the week is going
to be spent dealing with getting my life back in order.
But this news makes me very happy.
-j
jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90
“If IBM wanted to make clones, we could make them cheaper and faster than
anyone else!” - An IBM Rep. visiting Vassar College’s Comp Sci Department.