Обсуждение: checksumming data

Hi,
        I am a newbie pgsql user. How can I get the sha1 sum of large
        objects? or md5. but sha1 is better.
thanks...
--
Can Burak Cilingir
http://canb.net/
http://tdk.org.tr/dogruyazalim.html
icq#10720999

On Tue, Jan 04, 2005 at 01:04:48AM +0200, Can Burak Cilingir wrote:

> How can I get the sha1 sum of large objects? or md5. but sha1 is better.

PostgreSQL 7.4 and later has a built-in MD5() function, but it takes
a TEXT argument and might return unexpected results for binary data.
For a digest function that handles binary data and can do SHA1, see
the contrib/pgcrypto module.

I haven't used large objects much, but running \df in psql shows
several functions like lo_open() and loread() that appear to work
like the C functions described in the "Client Interfaces" section
of the "Large Objects" chapter in the documentation.  I don't know
why they're not documented as server-side functions callable from
SQL or PL/pgSQL -- I just did some tests and was able to read large
object data from a PL/pgSQL function.  I did have to consult the
documentation for the C functions and search through C headers to
find values for constants like INV_READ, so maybe there's an easier
way that I don't know about.

How committed are you to using large objects?  You might be able
to use BYTEA instead, but search the list archives for the debate
about which is more appropriate for a given situation.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, 2005-01-03 at 18:14 -0700, Michael Fuhr wrote:
> On Tue, Jan 04, 2005 at 01:04:48AM +0200, Can Burak Cilingir wrote:
>
> > How can I get the sha1 sum of large objects? or md5. but sha1 is better.
> For a digest function that handles binary data and can do SHA1, see
> the contrib/pgcrypto module.
I'll look at that thanks
> find values for constants like INV_READ, so maybe there's an easier
these are very convenient functions for inserting data. i am in love
with these.
> How committed are you to using large objects?  You might be able
> to use BYTEA instead, but search the list archives for the debate
> about which is more appropriate for a given situation.

http://archives.postgresql.org/pgsql-general/2002-03/msg01313.php
Each type (bytea and LO) has its own advantages. For instance, bytea
manipulates the entire string in memory (up to 4 copies from what I
understand), which may be a problem if your data is very large (I've
only tried to store 10s of MB in bytea fields myself).

I am storing 60-100MB png's. though i didn't tested if statement above
is true. but i am convenient.

the need for md5summing by the help of database is just curiosity. I am
summing before insert which was enough already.
--
Can Burak Cilingir
http://canb.net/
http://tdk.org.tr/dogruyazalim.html
icq#10720999

On Tue, Jan 04, 2005 at 10:26:26AM +0200, Can Burak Cilingir wrote:

> the need for md5summing by the help of database is just curiosity. I am
> summing before insert which was enough already.

The digest() function in contrib/pgcrypto takes a BYTEA or TEXT
argument, which presumably means you'd have to read the entire file
into memory (or can it be mmap()'ed?  I'm not sure).  For large
data this isn't practical, so you might want to look for something
that can build the hash in blocks.  See the pgcrypto source code
for examples.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/