public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] pg_dump: use a cursor in getBlobs..
26+ messages / 3 participants
[nested] [flat]
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* [PATCH] pg_dump: use a cursor in getBlobs..
@ 2021-03-09 20:06 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Justin Pryzby @ 2021-03-09 20:06 UTC (permalink / raw)
..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--
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Adding a pg_get_owned_sequence function?
@ 2023-09-12 07:33 Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Peter Eisentraut @ 2023-09-12 07:33 UTC (permalink / raw)
To: Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers
On 09.06.23 21:19, Dagfinn Ilmari Mannsåker wrote:
> I've always been annoyed by the fact that pg_get_serial_sequence takes
> the table and returns the sequence as strings rather than regclass. And
> since identity columns were added, the name is misleading as well (which
> is even acknowledged in the docs, together with a suggestion for a
> better name).
If you are striving for less misleading terminology, note that the
concept of an "owned sequence" does not exist for users of identity
sequences, and ALTER SEQUENCE / OWNED BY cannot be used for such sequences.
Would it work to just overload pg_get_serial_sequence with regclass
argument types?
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Adding a pg_get_owned_sequence function?
@ 2023-09-12 14:40 Tom Lane <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Tom Lane @ 2023-09-12 14:40 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Dagfinn Ilmari Mannsåker <[email protected]>; pgsql-hackers
Peter Eisentraut <[email protected]> writes:
> Would it work to just overload pg_get_serial_sequence with regclass
> argument types?
Probably not; the parser would have no principled way to resolve
pg_get_serial_sequence('foo', 'bar') as one or the other. I'm
not sure offhand if it would throw error or just choose one, but
if it just chooses one it'd likely be the text variant.
It's possible that we could get away with just summarily changing
the argument type from text to regclass. ISTR that we did exactly
that with nextval() years ago, and didn't get too much push-back.
But we couldn't do the same for the return type. Also, this
approach does nothing for the concern about the name being
misleading.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2023-09-12 14:40 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2021-03-09 20:06 [PATCH] pg_dump: use a cursor in getBlobs.. Justin Pryzby <[email protected]>
2023-09-12 07:33 Re: Adding a pg_get_owned_sequence function? Peter Eisentraut <[email protected]>
2023-09-12 14:40 ` Re: Adding a pg_get_owned_sequence function? Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox