Tom Lane wrote:
> "Vincze, Tamas" <vincze@neb.com> writes:
>> + * Note that it may still select BLOBs that have no comment if a pg_description row's objoid
>> + * matches a BLOB's loid, but references an object contained in a different system catalog,
>
> ... seems like that would be easy to fix ...
Yes, it wasn't that hard. The revised patch is attached.
Originally I didn't want to add more dependencies on the system
catalogs. Also, I've left the DECLARE statements untouched for
pre-v7.2 backends, so the NULL check on the comment is still
needed for those cases and if the description itself is NULL.
Regards,
Tamas
--- postgresql-8.3.7/src/bin/pg_dump/pg_dump.c.orig 2009-03-31 15:47:28.000000000 -0400
+++ postgresql-8.3.7/src/bin/pg_dump/pg_dump.c 2009-04-01 14:07:55.000000000 -0400
@@ -1759,7 +1759,18 @@
/* Cursor to get all BLOB comments */
if (AH->remoteVersion >= 70200)
- blobQry = "DECLARE blobcmt CURSOR FOR SELECT loid, obj_description(loid, 'pg_largeobject') FROM (SELECT
DISTINCTloid FROM pg_largeobject) ss";
+ /* Get comments for BLOBs that have a matching pg_description row. When there are many
+ * (millions) of BLOBs without comments this avoids fetching and then ignoring them,
+ * potentionally saving hours of backup time. */
+ blobQry = "DECLARE blobcmt CURSOR FOR SELECT loid, obj_description(loid, 'pg_largeobject') FROM ("
+ "SELECT DISTINCT loid FROM pg_description JOIN pg_largeobject "
+ "ON (pg_description.objoid = pg_largeobject.loid) "
+ "WHERE classoid = ("
+ "SELECT oid FROM pg_class WHERE relname = 'pg_largeobject' AND relnamespace = ("
+ "SELECT oid FROM pg_namespace WHERE nspname='pg_catalog'"
+ ")"
+ ") AND objsubid = 0"
+ ") ss";
else if (AH->remoteVersion >= 70100)
blobQry = "DECLARE blobcmt CURSOR FOR SELECT loid, obj_description(loid) FROM (SELECT DISTINCT loid FROM
pg_largeobject)ss";
else
