pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMD5 sums of large objects
5+ messages / 2 participants
[nested] [flat]
* MD5 sums of large objects
@ 2007-04-08 17:03 Dirk Jagdmann <jagdmann@gmail.com>
2007-04-08 17:37 ` Re: MD5 sums of large objects Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 5+ messages in thread
From: Dirk Jagdmann @ 2007-04-08 17:03 UTC (permalink / raw)
To: pgsql-sql
Hello all together,
I have a database containing lots of large objects. Now I'd like to
compare large objects in my database and I thought of having a
function which creates a hashsum (MD5, SHA-1 or whatever) of my large
object, so I can use that in queries:
create function lo_md5(id oid) returns text...
Now I don't know if something like this is already included in the
PostgreSQL distribution, could be found somewhere on pgfoundry or
thirdly how to do it? If I have to program myself I would go for a
C-language function which would use the libpq large-object functions
to create the hashsums.
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: MD5 sums of large objects
2007-04-08 17:03 MD5 sums of large objects Dirk Jagdmann <jagdmann@gmail.com>
@ 2007-04-08 17:37 ` Michael Fuhr <mike@fuhr.org>
2007-04-09 00:07 ` Re: MD5 sums of large objects Dirk Jagdmann <jagdmann@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2007-04-08 17:37 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: pgsql-sql
On Sun, Apr 08, 2007 at 07:03:17PM +0200, Dirk Jagdmann wrote:
> I have a database containing lots of large objects. Now I'd like to
> compare large objects in my database and I thought of having a
> function which creates a hashsum (MD5, SHA-1 or whatever) of my large
> object, so I can use that in queries:
>
> create function lo_md5(id oid) returns text...
Something like this might work:
create function lo_md5(id oid) returns text as $$
declare
fd integer;
size integer;
hashval text;
INV_READ constant integer := 262144;
SEEK_SET constant integer := 0;
SEEK_END constant integer := 2;
begin
fd := lo_open(id, INV_READ);
size := lo_lseek(0, 0, SEEK_END);
perform lo_lseek(0, 0, SEEK_SET);
hashval:= md5(loread(fd, size));
perform lo_close(fd);
return hashval;
end;
$$ language plpgsql stable strict;
For hash functions other than MD5 see contrib/pgcrypto.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: MD5 sums of large objects
2007-04-08 17:03 MD5 sums of large objects Dirk Jagdmann <jagdmann@gmail.com>
2007-04-08 17:37 ` Re: MD5 sums of large objects Michael Fuhr <mike@fuhr.org>
@ 2007-04-09 00:07 ` Dirk Jagdmann <jagdmann@gmail.com>
2007-04-09 01:07 ` Re: MD5 sums of large objects Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 5+ messages in thread
From: Dirk Jagdmann @ 2007-04-09 00:07 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
Hello Michael,
this works like charm. Although I did fix the argument for lo_lseek:
CREATE OR REPLACE FUNCTION md5(id oid)
RETURNS text
as $$
DECLARE
fd integer;
size integer;
hashval text;
INV_READ constant integer := 262144; -- 0x40000 from libpq-fs.h
SEEK_SET constant integer := 0;
SEEK_END constant integer := 2;
BEGIN
IF id is null THEN
RETURN NULL;
END IF;
fd := lo_open(id, INV_READ);
size := lo_lseek(fd, 0, SEEK_END);
PERFORM lo_lseek(fd, 0, SEEK_SET);
hashval := md5(loread(fd, size));
PERFORM lo_close(fd);
RETURN hashval;
END;
$$
language plpgsql stable strict;
comment on FUNCTION md5(id oid) is 'Calculates the md5 sum of a large object.';
I vote for this function beeing included either somewhere in the
contrib directories, as you often don't need the full power of
pgcrypto is md5 suffices for your hashing needs.
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: MD5 sums of large objects
2007-04-08 17:03 MD5 sums of large objects Dirk Jagdmann <jagdmann@gmail.com>
2007-04-08 17:37 ` Re: MD5 sums of large objects Michael Fuhr <mike@fuhr.org>
2007-04-09 00:07 ` Re: MD5 sums of large objects Dirk Jagdmann <jagdmann@gmail.com>
@ 2007-04-09 01:07 ` Michael Fuhr <mike@fuhr.org>
2007-04-09 15:11 ` Re: MD5 sums of large objects Dirk Jagdmann <jagdmann@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2007-04-09 01:07 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: pgsql-sql
On Mon, Apr 09, 2007 at 02:07:16AM +0200, Dirk Jagdmann wrote:
> this works like charm. Although I did fix the argument for lo_lseek:
Oops; thanks for fixing that.
> INV_READ constant integer := 262144; -- 0x40000 from libpq-fs.h
You could also use a hex constant (the cast to integer is necessary):
INV_READ constant integer := x'40000'::integer;
> IF id is null THEN
> RETURN NULL;
> END IF;
The above check is unnecessary since the function is marked STRICT,
aka RETURNS NULL ON NULL INPUT.
> I vote for this function beeing included either somewhere in the
> contrib directories, as you often don't need the full power of
> pgcrypto is md5 suffices for your hashing needs.
You could make a proposal in pgsql-hackers but I think 8.3 is in
feature freeze so don't expect to see it until 8.4, if it's accepted
at all. There's always PgFoundry :-)
--
Michael Fuhr
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: MD5 sums of large objects
2007-04-08 17:03 MD5 sums of large objects Dirk Jagdmann <jagdmann@gmail.com>
2007-04-08 17:37 ` Re: MD5 sums of large objects Michael Fuhr <mike@fuhr.org>
2007-04-09 00:07 ` Re: MD5 sums of large objects Dirk Jagdmann <jagdmann@gmail.com>
2007-04-09 01:07 ` Re: MD5 sums of large objects Michael Fuhr <mike@fuhr.org>
@ 2007-04-09 15:11 ` Dirk Jagdmann <jagdmann@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dirk Jagdmann @ 2007-04-09 15:11 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
Hello Michael,
thanks for the comments on my corrected function.
> You could make a proposal in pgsql-hackers but I think 8.3 is in
> feature freeze so don't expect to see it until 8.4, if it's accepted
> at all. There's always PgFoundry :-)
I'll now see how this performs in my application and if I'm satisfied
with this solution I'll post something on pgsql-hackers of pgfoundry.
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-04-09 15:11 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-08 17:03 MD5 sums of large objects Dirk Jagdmann <jagdmann@gmail.com>
2007-04-08 17:37 ` Michael Fuhr <mike@fuhr.org>
2007-04-09 00:07 ` Dirk Jagdmann <jagdmann@gmail.com>
2007-04-09 01:07 ` Michael Fuhr <mike@fuhr.org>
2007-04-09 15:11 ` Dirk Jagdmann <jagdmann@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox