Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qPnGP-00C32L-Gz for pgsql-general@arkaria.postgresql.org; Sat, 29 Jul 2023 16:57:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qPnGM-0065LV-TG for pgsql-general@arkaria.postgresql.org; Sat, 29 Jul 2023 16:57:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qPnGM-0065LN-GV for pgsql-general@lists.postgresql.org; Sat, 29 Jul 2023 16:57:54 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qPnGJ-0016PT-Ld for pgsql-general@lists.postgresql.org; Sat, 29 Jul 2023 16:57:53 +0000 Received: from smtp102.mailbox.org (smtp102.mailbox.org [10.196.197.102]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4RCrKP2q21z9sS5; Sat, 29 Jul 2023 18:57:45 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1690649865; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=dazUpH/4n0brykjjLTa3/cXLX/Ewbn3QaQglaM+BbpQ=; b=e5ZngsnLW63OWxaz5jVneJCJZhsjNzg6k3JoEMkyhrMjYkCNlA9ZtRnBjOAqMFqmcjqwcH nrmZ9RBu/pIHsUCCybWapPtM5IUuSU9+w+qhwAbaUHkGxAFdFOGIs2VgCJ6T93kmaQuPdx meCztj2hluGqpui5CK+2CawZf8JgzEMYP/aGW+yf52ES/8A0jnMXrRgAHDXPRLL3Z7qGLs 70mbYgDYZyIubfZYpRhHniPE43hYAqyrfi2+uISpRRAAGWg/0/WeqZV14AJNTR6+vWtlED /DfoGFjQulY3SsZPnLjsTV1Oaa176TpjdiDkglLzeqwlZBBYLFhmaJv9a4F2yg== Date: Sat, 29 Jul 2023 18:57:44 +0200 (CEST) From: Erik Wienhold To: Alex Shan <3341018@gmail.com>, pgsql-general@lists.postgresql.org Message-ID: <2099704620.64179.1690649864815@office.mailbox.org> In-Reply-To: References: Subject: Re: How to get an md5/sha256 hash of a really large object in psql? MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 29/07/2023 08:42 CEST Alex Shan <3341018@gmail.com> wrote: > > In my DB I have a large object over 4GB in size. > I need to get its MD5 or SHA256 from within psql query, i.e. without > exporting it to FS first. > > =E2=80=9CSELECT md5(lo_get(loid));=E2=80=9D doesnt work =E2=80=94 =E2=80= =9Clarge object is too large=E2=80=9D. > > Is there any other way to do it? Is plpython3u [1] an option for you? In that case you can use Python's has= hlib in a custom function and feed every page from pg_largeobject to a selected = hash function. For example: =09BEGIN; =09CREATE EXTENSION plpython3u; =09CREATE OR REPLACE FUNCTION lo_hash(loid oid, name text) =09 RETURNS bytea =09 LANGUAGE plpython3u =09AS $$ =09 import hashlib =09 =09 hash =3D hashlib.new(name) =09 =09 # Check if large object exists. =09 plan =3D plpy.prepare(""" =09 SELECT =09 FROM pg_largeobject_metadata =09 WHERE oid =3D $1 =09 """, ['oid']) =09 rv =3D plpy.execute(plan, [loid]) =09 =09 if rv.nrows() =3D=3D 0: =09 raise ValueError(f"large object {loid} does not exist") =09 =09 # Get all pages (possibly zero). =09 plan =3D plpy.prepare(""" =09 SELECT data =09 FROM pg_largeobject =09 WHERE loid =3D $1 =09 ORDER BY pageno =09 """, ['oid']) =09 pages =3D plpy.cursor(plan, [loid]) =09 =09 for page in pages: =09 hash.update(page['data']) =09 =09 return hash.digest() =09$$; =09COMMIT; Testing with 65 KiB null bytes: =09BEGIN; =09SELECT =09 lo_from_bytea(0, decode(repeat('00', 1 << 16), 'hex')) AS test_loid =09\gset =09 =09SELECT =09 loid, =09 count(*) AS n_pages, =09 sum(length(data)) AS n_bytes =09FROM pg_largeobject =09WHERE loid =3D :test_loid =09GROUP BY loid; =09 loid | n_pages | n_bytes =09--------+---------+--------- =09 365958 | 32 | 65536 =09(1 row) =09SELECT =09 :test_loid AS loid, =09 lo_hash(:test_loid, 'md5') AS md5; =09 loid | md5 =09--------+------------------------------------ =09 365958 | \xfcd6bcb56c1689fcef28b57c22475bad =09(1 row) =09SELECT =09 :test_loid AS loid, =09 lo_hash(:test_loid, 'sha256') AS sha256; =09 loid | sha256 =09--------+---------------------------------------------------------------= ----- =09 365958 | \xde2f256064a0af797747c2b97505dc0b9f3df0de4f489eac731c23ae9ca9= cc31 =09(1 row) =09ROLLBACK; Verifying the hashes: =09$ head -c65536 /dev/zero | md5sum =09fcd6bcb56c1689fcef28b57c22475bad - =09$ head -c65536 /dev/zero | sha256sum =09de2f256064a0af797747c2b97505dc0b9f3df0de4f489eac731c23ae9ca9cc31 - [1] https://www.postgresql.org/docs/15/plpython.html -- Erik