public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin Pryzby <[email protected]>
Subject: [PATCH] pg_dump: use a cursor in getBlobs..
Date: Tue, 9 Mar 2021 14:06:17 -0600
..to mitigate huge memory use in the case of millions of large objects
---
src/bin/pg_dump/pg_dump.c | 96 +++++++++++++++++++++------------------
1 file changed, 52 insertions(+), 44 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index aa02ada079..3fd7f48605 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3333,7 +3333,7 @@ getBlobs(Archive *fout)
BlobInfo *binfo;
DumpableObject *bdata;
PGresult *res;
- int ntups;
+ int ntups, total = 0;
int i;
int i_oid;
int i_lomowner;
@@ -3341,9 +3341,12 @@ getBlobs(Archive *fout)
int i_rlomacl;
int i_initlomacl;
int i_initrlomacl;
+ const char *blobFetchQry = "FETCH 1000 IN blob";
pg_log_info("reading large objects");
+ appendPQExpBuffer(blobQry, "DECLARE blob CURSOR FOR ");
+
/* Fetch BLOB OIDs, and owner/ACL data if >= 9.0 */
if (fout->remoteVersion >= 90600)
{
@@ -3393,58 +3396,66 @@ getBlobs(Archive *fout)
"NULL::oid AS initrlomacl "
" FROM pg_largeobject");
- res = ExecuteSqlQuery(fout, blobQry->data, PGRES_TUPLES_OK);
+ ExecuteSqlStatement(fout, blobQry->data);
+ destroyPQExpBuffer(blobQry);
- i_oid = PQfnumber(res, "oid");
- i_lomowner = PQfnumber(res, "rolname");
- i_lomacl = PQfnumber(res, "lomacl");
- i_rlomacl = PQfnumber(res, "rlomacl");
- i_initlomacl = PQfnumber(res, "initlomacl");
- i_initrlomacl = PQfnumber(res, "initrlomacl");
+ do {
+ res = ExecuteSqlQuery(fout, blobFetchQry, PGRES_TUPLES_OK);
- ntups = PQntuples(res);
+ i_oid = PQfnumber(res, "oid");
+ i_lomowner = PQfnumber(res, "rolname");
+ i_lomacl = PQfnumber(res, "lomacl");
+ i_rlomacl = PQfnumber(res, "rlomacl");
+ i_initlomacl = PQfnumber(res, "initlomacl");
+ i_initrlomacl = PQfnumber(res, "initrlomacl");
- /*
- * Each large object has its own BLOB archive entry.
- */
- binfo = (BlobInfo *) pg_malloc(ntups * sizeof(BlobInfo));
+ ntups = PQntuples(res);
+ total += ntups;
- for (i = 0; i < ntups; i++)
- {
- binfo[i].dobj.objType = DO_BLOB;
- binfo[i].dobj.catId.tableoid = LargeObjectRelationId;
- binfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
- AssignDumpId(&binfo[i].dobj);
+ /*
+ * Each large object has its own BLOB archive entry.
+ */
+ binfo = (BlobInfo *) pg_malloc(ntups * sizeof(BlobInfo));
- binfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_oid));
- binfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_lomowner));
- binfo[i].blobacl = pg_strdup(PQgetvalue(res, i, i_lomacl));
- binfo[i].rblobacl = pg_strdup(PQgetvalue(res, i, i_rlomacl));
- binfo[i].initblobacl = pg_strdup(PQgetvalue(res, i, i_initlomacl));
- binfo[i].initrblobacl = pg_strdup(PQgetvalue(res, i, i_initrlomacl));
+ for (i = 0; i < ntups; i++)
+ {
+ binfo[i].dobj.objType = DO_BLOB;
+ binfo[i].dobj.catId.tableoid = LargeObjectRelationId;
+ binfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
+ AssignDumpId(&binfo[i].dobj);
+
+ binfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_oid));
+ binfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_lomowner));
+ binfo[i].blobacl = pg_strdup(PQgetvalue(res, i, i_lomacl));
+ binfo[i].rblobacl = pg_strdup(PQgetvalue(res, i, i_rlomacl));
+ binfo[i].initblobacl = pg_strdup(PQgetvalue(res, i, i_initlomacl));
+ binfo[i].initrblobacl = pg_strdup(PQgetvalue(res, i, i_initrlomacl));
+
+ if (PQgetisnull(res, i, i_lomacl) &&
+ PQgetisnull(res, i, i_rlomacl) &&
+ PQgetisnull(res, i, i_initlomacl) &&
+ PQgetisnull(res, i, i_initrlomacl))
+ binfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
- if (PQgetisnull(res, i, i_lomacl) &&
- PQgetisnull(res, i, i_rlomacl) &&
- PQgetisnull(res, i, i_initlomacl) &&
- PQgetisnull(res, i, i_initrlomacl))
- binfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
+ /*
+ * In binary-upgrade mode for blobs, we do *not* dump out the blob
+ * data, as it will be copied by pg_upgrade, which simply copies the
+ * pg_largeobject table. We *do* however dump out anything but the
+ * data, as pg_upgrade copies just pg_largeobject, but not
+ * pg_largeobject_metadata, after the dump is restored.
+ */
+ if (dopt->binary_upgrade)
+ binfo[i].dobj.dump &= ~DUMP_COMPONENT_DATA;
+ }
- /*
- * In binary-upgrade mode for blobs, we do *not* dump out the blob
- * data, as it will be copied by pg_upgrade, which simply copies the
- * pg_largeobject table. We *do* however dump out anything but the
- * data, as pg_upgrade copies just pg_largeobject, but not
- * pg_largeobject_metadata, after the dump is restored.
- */
- if (dopt->binary_upgrade)
- binfo[i].dobj.dump &= ~DUMP_COMPONENT_DATA;
- }
+ PQclear(res);
+ } while (ntups != 0);
/*
* If we have any large objects, a "BLOBS" archive entry is needed. This
* is just a placeholder for sorting; it carries no data now.
*/
- if (ntups > 0)
+ if (total > 0)
{
bdata = (DumpableObject *) pg_malloc(sizeof(DumpableObject));
bdata->objType = DO_BLOB_DATA;
@@ -3452,9 +3463,6 @@ getBlobs(Archive *fout)
AssignDumpId(bdata);
bdata->name = pg_strdup("BLOBS");
}
-
- PQclear(res);
- destroyPQExpBuffer(blobQry);
}
/*
--
2.17.0
--gatW/ieO32f1wygP--
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH] pg_dump: use a cursor in getBlobs..
In-Reply-To: <no-message-id-1880195@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox