Fw: storing binary data

----- Original Message -----
From: Horst Herb <hherb(at)malleenet(dot)net(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Tuesday, October 17, 2000 11:58 PM
Subject: Re: [GENERAL] storing binary data

> > It is, but (IMHO) it's a big waste of space. The actual MD5 digest is
> > 128 bits. If stored in binary form, it's 16 bytes. If stored in hex
> > form (as ASCII), it's 32 characters @ 1 byte per character =3D 32 bytes.
>
> You're worried about 16 bytes per pg_shadow entry? Get real. I'd
> have recommended bytea if the amount of storage involved were actually
> significant, but for this application readability seems more important.

Oh well, there are other cases. We are using digests for every single row in virtually any table in our 300+ table gnumed project. Some of the tables have several hundreds of thousands entries (like pathology results). For obvius medico-legal reasons we can't simply trust a database system, we have to have some means of double checking whether the data has been corrupted some way or not (the life span of our data will go over several decades, and the data certainly will go through power outages, glitches, and countless backup/restore cycles).

So, the solution is digests (strong hashes) for every row in the table. Adds up and slows down. I am sure there are other projects around with a similar level of need for data integrity.

AND THAT IS WHY I AM SO FRUSTRATED that there does not seem to be any means of automatizing this. Unless I miss something, I have to write a trigger for each and any single table to achieve this, instead of just one generic database wide trigger (like "create trigger calc_crc before insert or update on * ...." or inheritable triggers);

Horst