[ patch ] pg_dump: new --custom-fetch-table and --custom-fetch-value parameters
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachments:
9_6.patchapplication/octet-streamDownload
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index f1e60bb..c7a065c 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -652,6 +652,55 @@ PostgreSQL documentation
</varlistentry>
<varlistentry>
+ <term><option>--custom-fetch-table=<replaceable class="parameter">table</replaceable></option></term>
+ <listitem>
+ <para>
+ For the given table(s) a custom number of rows is fetched.
+ <command>pg_dump</command> gets 100 rows at the time when it is
+ exporting the table data as inserts. When you have tables with big
+ rows (i.e. blob fields), this fetch request could fail due to the too
+ big amount of data. With this parameter you can decide for which
+ table(s) to fetch a different number of rows, in particular the
+ number of rows defined via the <option>--custom-fetch-value</option>
+ parameter.
+ </para>
+ <para>
+ This parameter does not change the output of <command>pg_dump</command>:
+ it just controls how to read the data.
+ </para>
+ <para>
+ This option is only meaningful when the <option>--inserts</option>
+ parameter is used and requires the definition of the <option>--custom-fetch-value</option>
+ parameter.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><option>--custom-fetch-value=<replaceable class="parameter">nRows</replaceable></option></term>
+ <listitem>
+ <para>
+ The number of rows to fetch for the table(s) identified by the
+ <option>--custom-fetch-table</option> parameter.
+ <command>pg_dump</command> gets 100 rows at the time when it is
+ exporting the table data as inserts. When you have tables with big
+ rows (i.e. blob fields), this fetch request could fail due to the too
+ big amount of data. With this parameter you can fetch the given
+ number of rows (usually a number less than 100) for the table(s)
+ defined by the <option>--custom-fetch-table</option> parameter
+ and this could avoid the failure.
+ </para>
+ <para>
+ This parameter does not change the output of <command>pg_dump</command>:
+ it just controls how to read the data.
+ </para>
+ <para>
+ This option is only meaningful when the <option>--inserts</option>
+ parameter is used and requires the definition of the <option>--custom-fetch-table</option>
+ parameter.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
<term><option>--disable-dollar-quoting</></term>
<listitem>
<para>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 4afa92f..ffef10d 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -150,6 +150,7 @@ typedef struct _dumpOptions
int outputNoTablespaces;
int use_setsessauth;
int enable_row_security;
+ int custom_fetch_value;
/* default, if no "inclusion" switches appear, is to dump everything */
bool include_everything;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index fde7f59..c57b1e6 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -119,6 +119,8 @@ static SimpleStringList table_exclude_patterns = {NULL, NULL};
static SimpleOidList table_exclude_oids = {NULL, NULL};
static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
+static SimpleStringList custom_fetch_table_patterns = {NULL, NULL};
+static SimpleOidList custom_fetch_table_oids = {NULL, NULL};
char g_opaque_type[10]; /* name for the opaque type */
@@ -349,6 +351,8 @@ main(int argc, char **argv)
{"no-security-labels", no_argument, &dopt.no_security_labels, 1},
{"no-synchronized-snapshots", no_argument, &dopt.no_synchronized_snapshots, 1},
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
+ {"custom-fetch-table", required_argument, NULL, 7},
+ {"custom-fetch-value", required_argument, NULL, 8},
{NULL, 0, NULL, 0}
};
@@ -529,6 +533,19 @@ main(int argc, char **argv)
dumpsnapshot = pg_strdup(optarg);
break;
+ case 7: /* custom fetch table(s) */
+ simple_string_list_append(&custom_fetch_table_patterns, optarg);
+ break;
+
+ case 8: /* custom fetch value */
+ dopt.custom_fetch_value = atoi(optarg);
+ if (dopt.custom_fetch_value <= 0 )
+ {
+ write_msg(NULL, "custom fetch value must be bigger than 0\n");
+ exit_nicely(1);
+ }
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit_nicely(1);
@@ -578,6 +595,12 @@ main(int argc, char **argv)
if (dopt.if_exists && !dopt.outputClean)
exit_horribly(NULL, "option --if-exists requires option -c/--clean\n");
+ if ((dopt.custom_fetch_value>0)&&(!dopt.dump_inserts))
+ {
+ write_msg(NULL, "option --custom-fetch-value requires option --inserts/--column-inserts\n");
+ exit_nicely(1);
+ }
+
/* Identify archive format to emit */
archiveFormat = parseArchiveFormat(format, &archiveMode);
@@ -728,6 +751,16 @@ main(int argc, char **argv)
/* non-matching exclusion patterns aren't an error */
+ /* regarding the "custom fetch" options for me everything is
+ still fine if some (or all of the) provided names don't exist
+ (think, i.e., to batches that provides a standard list of table's
+ names that could be present or not inside the database) */
+ expand_table_name_patterns(fout, &custom_fetch_table_patterns,
+ &custom_fetch_table_oids,
+ false);
+ if ((custom_fetch_table_oids.head!=NULL)&&(dopt.custom_fetch_value==0))
+ exit_horribly(NULL, "option --custom-fetch-table requires --custom-fetch-value\n");
+
/*
* Dumping blobs is now default unless we saw an inclusion switch or -s
* ... but even if we did see one of these, -b turns it back on.
@@ -908,6 +941,9 @@ help(const char *progname)
printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
printf(_(" --binary-upgrade for use by upgrade utilities only\n"));
printf(_(" --column-inserts dump data as INSERT commands with column names\n"));
+ printf(_(" --custom-fetch-table=TABLE defines the name(s) of the table(s) for which to\n"
+ " fetch a custom number of rows\n"));
+ printf(_(" --custom-fetch-value=VALUE defines how many rows to fetch\n"));
printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
printf(_(" --enable-row-security enable row security (dump only content user has\n"
@@ -1816,10 +1852,12 @@ dumpTableData_insert(Archive *fout, void *dcontext)
DumpOptions *dopt = fout->dopt;
PQExpBuffer q = createPQExpBuffer();
PQExpBuffer insertStmt = NULL;
+ PQExpBuffer fetchStmt = createPQExpBuffer();
PGresult *res;
int tuple;
int nfields;
int field;
+ int nRecordsToFetch;
/*
* Make sure we are in proper schema. We will qualify the table name
@@ -1850,10 +1888,19 @@ dumpTableData_insert(Archive *fout, void *dcontext)
ExecuteSqlStatement(fout, q->data);
+ /* Check if for this table we have to use custom or
+ standard fetch */
+ if (simple_oid_list_member(&custom_fetch_table_oids,
+ tbinfo->dobj.catId.oid))
+ nRecordsToFetch = dopt->custom_fetch_value;
+ else
+ nRecordsToFetch = 100;
+ appendPQExpBuffer(fetchStmt, "FETCH %d FROM _pg_dump_cursor",
+ nRecordsToFetch );
+
while (1)
{
- res = ExecuteSqlQuery(fout, "FETCH 100 FROM _pg_dump_cursor",
- PGRES_TUPLES_OK);
+ res = ExecuteSqlQuery(fout, fetchStmt->data, PGRES_TUPLES_OK);
nfields = PQnfields(res);
for (tuple = 0; tuple < PQntuples(res); tuple++)
{
@@ -1983,6 +2030,7 @@ dumpTableData_insert(Archive *fout, void *dcontext)
destroyPQExpBuffer(q);
if (insertStmt != NULL)
destroyPQExpBuffer(insertStmt);
+ destroyPQExpBuffer(fetchStmt);
return 1;
}
master.patchapplication/octet-streamDownload
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index f6225d2..8cf4d1d 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -668,6 +668,55 @@ PostgreSQL documentation
</varlistentry>
<varlistentry>
+ <term><option>--custom-fetch-table=<replaceable class="parameter">table</replaceable></option></term>
+ <listitem>
+ <para>
+ For the given table(s) a custom number of rows is fetched.
+ <command>pg_dump</command> gets 100 rows at the time when it is
+ exporting the table data as inserts. When you have tables with big
+ rows (i.e. blob fields), this fetch request could fail due to the too
+ big amount of data. With this parameter you can decide for which
+ table(s) to fetch a different number of rows, in particular the
+ number of rows defined via the <option>--custom-fetch-value</option>
+ parameter.
+ </para>
+ <para>
+ This parameter does not change the output of <command>pg_dump</command>:
+ it just controls how to read the data.
+ </para>
+ <para>
+ This option is only meaningful when the <option>--inserts</option>
+ parameter is used and requires the definition of the <option>--custom-fetch-value</option>
+ parameter.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><option>--custom-fetch-value=<replaceable class="parameter">nRows</replaceable></option></term>
+ <listitem>
+ <para>
+ The number of rows to fetch for the table(s) identified by the
+ <option>--custom-fetch-table</option> parameter.
+ <command>pg_dump</command> gets 100 rows at the time when it is
+ exporting the table data as inserts. When you have tables with big
+ rows (i.e. blob fields), this fetch request could fail due to the too
+ big amount of data. With this parameter you can fetch the given
+ number of rows (usually a number less than 100) for the table(s)
+ defined by the <option>--custom-fetch-table</option> parameter
+ and this could avoid the failure.
+ </para>
+ <para>
+ This parameter does not change the output of <command>pg_dump</command>:
+ it just controls how to read the data.
+ </para>
+ <para>
+ This option is only meaningful when the <option>--inserts</option>
+ parameter is used and requires the definition of the <option>--custom-fetch-table</option>
+ parameter.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
<term><option>--disable-dollar-quoting</></term>
<listitem>
<para>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 7241cdf..d585252 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -152,6 +152,7 @@ typedef struct _dumpOptions
int outputNoTablespaces;
int use_setsessauth;
int enable_row_security;
+ int custom_fetch_value;
/* default, if no "inclusion" switches appear, is to dump everything */
bool include_everything;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7949aad..dc93d02 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -116,6 +116,8 @@ static SimpleStringList table_exclude_patterns = {NULL, NULL};
static SimpleOidList table_exclude_oids = {NULL, NULL};
static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
+static SimpleStringList custom_fetch_table_patterns = {NULL, NULL};
+static SimpleOidList custom_fetch_table_oids = {NULL, NULL};
char g_opaque_type[10]; /* name for the opaque type */
@@ -344,6 +346,8 @@ main(int argc, char **argv)
{"no-security-labels", no_argument, &dopt.no_security_labels, 1},
{"no-synchronized-snapshots", no_argument, &dopt.no_synchronized_snapshots, 1},
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
+ {"custom-fetch-table", required_argument, NULL, 7},
+ {"custom-fetch-value", required_argument, NULL, 8},
{NULL, 0, NULL, 0}
};
@@ -524,6 +528,17 @@ main(int argc, char **argv)
dumpsnapshot = pg_strdup(optarg);
break;
+ case 7: /* custom fetch table(s) */
+ simple_string_list_append(&custom_fetch_table_patterns, optarg);
+ break;
+ case 8: /* custom fetch value */
+ dopt.custom_fetch_value = atoi(optarg);
+ if (dopt.custom_fetch_value <= 0 )
+ {
+ write_msg(NULL, "custom fetch value must be bigger than 0\n");
+ exit_nicely(1);
+ }
+ break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit_nicely(1);
@@ -581,6 +596,11 @@ main(int argc, char **argv)
if (dopt.if_exists && !dopt.outputClean)
exit_horribly(NULL, "option --if-exists requires option -c/--clean\n");
+ if ((dopt.custom_fetch_value>0)&&(!dopt.dump_inserts))
+ {
+ write_msg(NULL, "option --custom-fetch-value requires option --inserts/--column-inserts\n");
+ exit_nicely(1);
+ }
/* Identify archive format to emit */
archiveFormat = parseArchiveFormat(format, &archiveMode);
@@ -717,6 +737,15 @@ main(int argc, char **argv)
/* non-matching exclusion patterns aren't an error */
+ /* regarding the "custom fetch" options for me everything is
+ still fine if some (or all of the) provided names don't exist
+ (think, i.e., to batches that provides a standard list of table's
+ names that could be present or not inside the database) */
+ expand_table_name_patterns(fout, &custom_fetch_table_patterns,
+ &custom_fetch_table_oids,
+ false);
+ if ((custom_fetch_table_oids.head!=NULL)&&(dopt.custom_fetch_value==0))
+ exit_horribly(NULL, "option --custom-fetch-table requires --custom-fetch-value\n");
/*
* Dumping blobs is the default for dumps where an inclusion switch is not
* used (an "include everything" dump). -B can be used to exclude blobs
@@ -902,6 +931,9 @@ help(const char *progname)
printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
printf(_(" --binary-upgrade for use by upgrade utilities only\n"));
printf(_(" --column-inserts dump data as INSERT commands with column names\n"));
+ printf(_(" --custom-fetch-table=TABLE defines the name(s) of the table(s) for which to\n"
+ " fetch a custom number of rows\n"));
+ printf(_(" --custom-fetch-value=VALUE defines how many rows to fetch\n"));
printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
printf(_(" --enable-row-security enable row security (dump only content user has\n"
@@ -1800,10 +1832,12 @@ dumpTableData_insert(Archive *fout, void *dcontext)
DumpOptions *dopt = fout->dopt;
PQExpBuffer q = createPQExpBuffer();
PQExpBuffer insertStmt = NULL;
+ PQExpBuffer fetchStmt = createPQExpBuffer();
PGresult *res;
int tuple;
int nfields;
int field;
+ int nRecordsToFetch;
/*
* Make sure we are in proper schema. We will qualify the table name
@@ -1823,10 +1857,19 @@ dumpTableData_insert(Archive *fout, void *dcontext)
ExecuteSqlStatement(fout, q->data);
+ /* Check if for this table we have to use custom or
+ standard fetch */
+ if (simple_oid_list_member(&custom_fetch_table_oids,
+ tbinfo->dobj.catId.oid))
+ nRecordsToFetch = dopt->custom_fetch_value;
+ else
+ nRecordsToFetch = 100;
+ appendPQExpBuffer(fetchStmt, "FETCH %d FROM _pg_dump_cursor",
+ nRecordsToFetch );
+
while (1)
{
- res = ExecuteSqlQuery(fout, "FETCH 100 FROM _pg_dump_cursor",
- PGRES_TUPLES_OK);
+ res = ExecuteSqlQuery(fout, fetchStmt->data, PGRES_TUPLES_OK);
nfields = PQnfields(res);
for (tuple = 0; tuple < PQntuples(res); tuple++)
{
@@ -1956,6 +1999,7 @@ dumpTableData_insert(Archive *fout, void *dcontext)
destroyPQExpBuffer(q);
if (insertStmt != NULL)
destroyPQExpBuffer(insertStmt);
+ destroyPQExpBuffer(fetchStmt);
return 1;
}
Andrea,
* Andrea Urbani (matfanjol@mail.com) wrote:
I had a problem with a Postgresql 9.3.5 on 32 bit linux, old 2.6.26
kernel:
Ok, though, to be clear, this is a feature request, so we wouldn't
back-patch adding this to pg_dump.
I have solve it adding two new parameters, --custom-fetch-table and
--custom-fetch-value, to fetch less records for the specified table(s).
Giving the user the ability to change the fetch size sounds interesting,
though do we really need to specify it per table? What about just a
--fetch-size=X option?
This does not completely solve the problem, but it helps you to get more
chance to be able to dump your database.
That is certainly a worthwhile goal.
pg_dump --dbname=healthorganizer --username=hor --column-inserts
--custom-fetch-table='"tDocumentsFiles"' --custom-fetch-value=25
I don't particularly like the use of 'custom' in the name of the option,
seems like it's just a noise word and not really necessary.
I haven't tested the documentation: too many problems while building it
(also the original version, without my changes; probably I have bogus
tools... and too less time to check/try...).
Attached the patches for the master and REL9_6_STABLE.
I agree the documentation can be a bit of a pain, but there's a lot of
issues with the patch itself when it comes to the project style. The
indentation doesn't look like it's all correct, and multi-line comments
should be of the form:
/*
* text here
*/
Lastly, it'd be good to have this patch added to
https://commitfest.postgresql.org to have it formally reviewed in the
commitfest cycle coming up in January.
Thanks!
Stephen
I have solve it adding two new parameters, --custom-fetch-table and
--custom-fetch-value, to fetch less records for the specified table(s).
Giving the user the ability to change the fetch size sounds interesting,
though do we really need to specify it per table? What about just a
--fetch-size=X option?
...
I don't particularly like the use of 'custom' in the name of the option,
seems like it's just a noise word and not really necessary.
I have used "custom" parameters because I want to decrease the fetch size only on the tables with big bloab fields. If we remove the "custom-fetch-table" parameter and we provide only the "fetch-size" parameter all the tables will use the new fetch size and the execution time will be slower (according to my few tests). But just "fetch-size" will be faster to use and maybe more clear.
Well, how to go on? I add it to the commitfest and somebody will decide and fix it?
Please, let me know
Thank you
Andrea
Sent: Wednesday, December 21, 2016 at 6:44 PM
From: "Stephen Frost" <sfrost@snowman.net>
To: "Andrea Urbani" <matfanjol@mail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [ patch ] pg_dump: new --custom-fetch-table and --custom-fetch-value parameters
Andrea,
* Andrea Urbani (matfanjol@mail.com) wrote:
I had a problem with a Postgresql 9.3.5 on 32 bit linux, old 2.6.26
kernel:
Ok, though, to be clear, this is a feature request, so we wouldn't
back-patch adding this to pg_dump.
I have solve it adding two new parameters, --custom-fetch-table and
--custom-fetch-value, to fetch less records for the specified table(s).
Giving the user the ability to change the fetch size sounds interesting,
though do we really need to specify it per table? What about just a
--fetch-size=X option?
This does not completely solve the problem, but it helps you to get more
chance to be able to dump your database.
That is certainly a worthwhile goal.
pg_dump --dbname=healthorganizer --username=hor --column-inserts
--custom-fetch-table='"tDocumentsFiles"' --custom-fetch-value=25
I don't particularly like the use of 'custom' in the name of the option,
seems like it's just a noise word and not really necessary.
I haven't tested the documentation: too many problems while building it
(also the original version, without my changes; probably I have bogus
tools... and too less time to check/try...).
Attached the patches for the master and REL9_6_STABLE.
I agree the documentation can be a bit of a pain, but there's a lot of
issues with the patch itself when it comes to the project style. The
indentation doesn't look like it's all correct, and multi-line comments
should be of the form:
/*
* text here
*/
Lastly, it'd be good to have this patch added to
https://commitfest.postgresql.org to have it formally reviewed in the
commitfest cycle coming up in January.
Thanks!
Stephen
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jan 20, 2017 at 12:52 AM, Andrea Urbani <matfanjol@mail.com> wrote:
I have used "custom" parameters because I want to decrease the fetch size only on the tables with big bloab fields. If we remove the "custom-fetch-table" parameter and we provide only the "fetch-size" parameter all the tables will use the new fetch size and the execution time will be slower (according to my few tests). But just "fetch-size" will be faster to use and maybe more clear.
Well, how to go on? I add it to the commitfest and somebody will decide and fix it?
OK, so I think the idea is that --custom-fetch-size affects only the
tables mentioned in --custom-fetch-table. I understand why you want
to do it that way but it's kind of messy. Suppose somebody else comes
along and wants to customize some other thing for some other set of
tables. Then we'll have --custom2-otherthing and --custom2-tables?
Blech.
Interestingly, this isn't the first attempt to solve a problem of this
type. Kyotaro Horiguchi ran into a similar issue with postgres_fdw
trying to fetch too much data at once from a remote server:
/messages/by-id/20150122.192739.164180273.horiguchi.kyotaro@lab.ntt.co.jp
In the end, all that got done there was a table-level-configurable
fetch limit, and we could do the same thing here (e.g. by adding a
dummy storage parameter that only pg_dump uses). But I think what we
really ought to do is what Kyotaro Horiguchi proposed originally: have
a way to limit the FETCH command to a certain number of bytes. If
that number of bytes is exceeded, the FETCH stops after that row even
if the number of rows the user requested isn't fulfilled yet. The
user can FETCH again if they want more.
Tom wasn't a big fan of this idea, but I thought it was clever and
still do. And it's undeniable that it provides a much better solution
to this problem than forcing the user to manually tweak the fetch size
based on their installation-specific knowledge of which tables have
blobs large enough that returning 100 rows at a time will exhaust the
local server's memory.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I'm a beginner here... anyway I try to share my ideas.
My situation is changed in a worst state: I'm no more able to make a pg_dump neither with my custom fetch value (I have tried "1" as value = one row at the time) neither without the "--column-inserts":
pg_dump: Dumping the contents of table "tDocumentsFiles" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: out of memory
DETAIL: Failed on request of size 1073741823.
pg_dump: The command was: COPY public."tDocumentsFiles" ("ID_Document", "ID_File", "Name", "FileName", "Link", "Note", "Picture", "Content", "FileSize", "FileDateTime", "DrugBox", "DrugPicture", "DrugInstructions") TO stdout;
I don't know if the Kyotaro Horiguchi patch will solve this, because, again, I'm not able to get neither one single row.
Similar problem trying to read and to write the bloab fields with my program.
Actually I'm working via pieces:
Read
r1) I get the length of the bloab field
r2) I check the available free memory (on the client pc)
r3) I read pieces of the bloab field, according to the free memory, appending them to a physical file
Write
w1) I check the length of the file to save inside the bloab
w2) I check the available free memory (on the client pc)
w3) I create a temporary table on the server
w4) I add lines to this temporary table, writing pieces of the file according to the free memory
w5) I ask the server to write, inside the final bloab field, the concatenation of the rows of the temporary data
The read and write is working now.
Probably the free memory check should be done on both sides (client and server [does a function/view with the available free memory exist?]) taking the smallest one.
What do you think to use a similar approach in the pg_dump?
a) go through the table getting the size of each row / fields
b) when the size of the row or of the field is bigger than the value (provided or stored somewhere), read pieces of the field till the end
PS: I have see there are the "large object" that can work via streams. My files are actually not bigger than 1Gb, but, ok, maybe in the future I will use them instead of the bloabs.
Thank you
Andrea
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sat, Feb 11, 2017 at 9:56 AM, Andrea Urbani <matfanjol@mail.com> wrote:
I'm a beginner here... anyway I try to share my ideas.
My situation is changed in a worst state: I'm no more able to make a pg_dump neither with my custom fetch value (I have tried "1" as value = one row at the time) neither without the "--column-inserts":
pg_dump: Dumping the contents of table "tDocumentsFiles" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: out of memory
DETAIL: Failed on request of size 1073741823.
pg_dump: The command was: COPY public."tDocumentsFiles" ("ID_Document", "ID_File", "Name", "FileName", "Link", "Note", "Picture", "Content", "FileSize", "FileDateTime", "DrugBox", "DrugPicture", "DrugInstructions") TO stdout;I don't know if the Kyotaro Horiguchi patch will solve this, because, again, I'm not able to get neither one single row.
Yeah, if you can't fetch even one row, limiting the fetch size won't
help. But why is that failing? A single 1GB allocation should be
fine on most modern servers. I guess the fact that you're using a
32-bit build of PostgreSQL is probably a big part of the problem;
there is probably only 2GB of available address space and you're
trying to find a single, contiguous 1GB chunk. If you switch to using
a 64-bit PostgreSQL things will probably get a lot better for you,
unless the server's actual memory is also very small.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers