
Maybe I am missing something obvious, but I am unable to load
larger tables (~300k rows) with COPY command that pg_dump by
default produces.  Yes, dump as INSERTs works but is slow.

"Cant" as in "it does not work with the default setup I have
running on devel machine" - 128M mem, 128M swap, basically
default postgresql.conf:

1) Too few WAL files.
   - well, increase the wal_files (eg to 32),

2) Machine runs out of swap, PostgreSQL seems to keep whole TX
   in memory.
   - So I must put 1G of swap?   But what if I have 1G of rows?

Or shortly: during pg_restore the resource requirements are
order of magnitude higher than during pg_dump, which is
non-obvious and may be a bad surprise when in real trouble.

This is annoying, especially as dump as COPY's should be
preferred as it is faster and smaller.  Ofcourse the
dump-as-INSERTs has also positive side - eg. ALTER TABLE DROP
COLUMN with sed...

Patch below implements '-m NUM' switch to pg_dump, which splits
each COPY command to chunks, each maximum NUM rows.

Comments?  What am I missing?

-- 
marko



Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /opt/cvs/pgsql/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.41
diff -u -c -r1.41 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml	8 Dec 2001 03:24:37 -0000	1.41
--- doc/src/sgml/ref/pg_dump.sgml	11 Dec 2001 03:58:30 -0000
***************
*** 35,40 ****
--- 35,41 ----
     <arg>-f <replaceable>file</replaceable></arg> 
     <arg>-F <replaceable>format</replaceable></arg>
     <arg>-i</arg>
+    <arg>-m <replaceable>num_rows</replaceable></arg>
     <group> <arg>-n</arg> <arg>-N</arg> </group>
     <arg>-o</arg>
     <arg>-O</arg>
***************
*** 301,306 ****
--- 302,321 ----
  	if you need to override the version check (and if
  	<command>pg_dump</command> then fails, don't
  	say you weren't warned).
+        </para>
+       </listitem>
+      </varlistentry>
+ 
+      <varlistentry>
+       <term>-m <replaceable class="parameter">num_rows</replaceable></term>
+       <term>--maxrows=<replaceable class="parameter">num_rows</replaceable></term>
+       <listitem>
+        <para>
+ 	Set maximum number of rows to put into one COPY statement.
+ 	This starts new COPY command after every
+ 	<replaceable class="parameter">num_rows</replaceable>.
+ 	This is useful on large tables to avoid restoring whole table in 
+ 	one transaction which may consume lot of resources.
         </para>
        </listitem>
       </varlistentry>
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /opt/cvs/pgsql/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.236
diff -u -c -r1.236 pg_dump.c
*** src/bin/pg_dump/pg_dump.c	28 Oct 2001 06:25:58 -0000	1.236
--- src/bin/pg_dump/pg_dump.c	11 Dec 2001 04:48:42 -0000
***************
*** 116,121 ****
--- 116,123 ----
  bool		dataOnly;
  bool		aclsSkip;
  
+ int			g_max_copy_rows = 0;
+ 
  char		g_opaque_type[10];	/* name for the opaque type */
  
  /* placeholders for the delimiters for comments */
***************
*** 151,156 ****
--- 153,159 ----
  				 "  -h, --host=HOSTNAME      database server host name\n"
  				 "  -i, --ignore-version     proceed even when server version mismatches\n"
  				 "                           pg_dump version\n"
+ 				 "  ï¿½m, --maxrows=NUM        max rows in one COPY command\n"
  	"  -n, --no-quotes          suppress most quotes around identifiers\n"
  	 "  -N, --quotes             enable most quotes around identifiers\n"
  				 "  -o, --oids               include oids in dump\n"
***************
*** 187,192 ****
--- 190,196 ----
  				 "                           pg_dump version\n"
  	"  -n                       suppress most quotes around identifiers\n"
  	 "  -N                       enable most quotes around identifiers\n"
+ 				 "  ï¿½m NUM                   max rows in one COPY command\n"
  				 "  -o                       include oids in dump\n"
  				 "  -O                       do not output \\connect commands in plain\n"
  				 "                           text format\n"
***************
*** 244,249 ****
--- 248,255 ----
  	int			ret;
  	bool		copydone;
  	char		copybuf[COPYBUFSIZ];
+ 	int			cur_row;
+ 	int			linestart;
  
  	if (g_verbose)
  		write_msg(NULL, "dumping out the contents of table %s\n", classname);
***************
*** 297,302 ****
--- 303,310 ----
  		else
  		{
  			copydone = false;
+ 			linestart = 1;
+ 			cur_row = 0;
  
  			while (!copydone)
  			{
***************
*** 310,316 ****
--- 318,338 ----
  				}
  				else
  				{
+ 					/*
+ 					 * Avoid too large transactions by breaking them up.
+ 					 */
+ 					if (g_max_copy_rows > 0 && linestart
+ 							&& cur_row >= g_max_copy_rows)
+ 					{
+ 						cur_row = 0;
+ 						archputs("\\.\n", fout);
+ 						archprintf(fout, "COPY %s %sFROM stdin;\n",
+ 								fmtId(classname, force_quotes),
+ 								(oids && hasoids) ? "WITH OIDS " : "");
+ 					}
+ 
  					archputs(copybuf, fout);
+ 					
  					switch (ret)
  					{
  						case EOF:
***************
*** 318,325 ****
--- 340,350 ----
  							/* FALLTHROUGH */
  						case 0:
  							archputc('\n', fout);
+ 							cur_row++;
+ 							linestart = 1;
  							break;
  						case 1:
+ 							linestart = 0;
  							break;
  					}
  				}
***************
*** 696,701 ****
--- 721,727 ----
  		{"compress", required_argument, NULL, 'Z'},
  		{"help", no_argument, NULL, '?'},
  		{"version", no_argument, NULL, 'V'},
+ 		{"maxrows", required_argument, NULL, 'm'},
  
  		/*
  		 * the following options don't have an equivalent short option
***************
*** 748,756 ****
  	}
  
  #ifdef HAVE_GETOPT_LONG
! 	while ((c = getopt_long(argc, argv, "abcCdDf:F:h:inNoOp:RsS:t:uU:vWxX:zZ:V?", long_options, &optindex)) != -1)
  #else
! 	while ((c = getopt(argc, argv, "abcCdDf:F:h:inNoOp:RsS:t:uU:vWxX:zZ:V?-")) != -1)
  #endif
  
  	{
--- 774,782 ----
  	}
  
  #ifdef HAVE_GETOPT_LONG
! 	while ((c = getopt_long(argc, argv, "abcCdDf:F:h:im:nNoOp:RsS:t:uU:vWxX:zZ:V?", long_options, &optindex)) != -1)
  #else
! 	while ((c = getopt(argc, argv, "abcCdDf:F:h:im:nNoOp:RsS:t:uU:vWxX:zZ:V?-")) != -1)
  #endif
  
  	{
***************
*** 798,803 ****
--- 824,833 ----
  
  			case 'i':			/* ignore database version mismatch */
  				ignore_version = true;
+ 				break;
+ 
+ 			case 'm':
+ 				g_max_copy_rows = atoi(optarg);
  				break;
  
  			case 'n':			/* Do not force double-quotes on

Tue, 11 Dec 2001 17:10:05 +0200
Marko Kreen <marko@l-t.ee>
Restoring large tables with COPY
Marko Kreen <marko@l-t.ee> writes:
> Maybe I am missing something obvious, but I am unable to load
> larger tables (~300k rows) with COPY command that pg_dump by
> default produces.

I'd like to find out what the problem is, rather than work around it
with such an ugly hack.

> 1) Too few WAL files.
>    - well, increase the wal_files (eg to 32),

What PG version are you running?  7.1.3 or later should not have a
problem with WAL file growth.

> 2) Machine runs out of swap, PostgreSQL seems to keep whole TX
>    in memory.

That should not happen either.  Could we see the full schema of the
table you are having trouble with?

> Or shortly: during pg_restore the resource requirements are
> order of magnitude higher than during pg_dump,

We found some client-side memory leaks in pg_restore recently; is that
what you're talking about?

			regards, tom lane

Tue, 11 Dec 2001 10:55:30 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Restoring large tables with COPY 
On Tue, Dec 11, 2001 at 10:55:30AM -0500, Tom Lane wrote:
> Marko Kreen <marko@l-t.ee> writes:
> > Maybe I am missing something obvious, but I am unable to load
> > larger tables (~300k rows) with COPY command that pg_dump by
> > default produces.
> 
> I'd like to find out what the problem is, rather than work around it
> with such an ugly hack.
> 
> > 1) Too few WAL files.
> >    - well, increase the wal_files (eg to 32),
> 
> What PG version are you running?  7.1.3 or later should not have a
> problem with WAL file growth.

7.1.3

> > 2) Machine runs out of swap, PostgreSQL seems to keep whole TX
> >    in memory.
> 
> That should not happen either.  Could we see the full schema of the
> table you are having trouble with?

Well, there are several such tables, I will reproduce it,
then send the schema.  I guess its the first one, but maybe
not.  postgres gets killed by Linux OOM handler, so I cant
tell by messages, which one it was.  (hmm, i should probably
run it as psql -q -a > log).

> > Or shortly: during pg_restore the resource requirements are
> > order of magnitude higher than during pg_dump,
> 
> We found some client-side memory leaks in pg_restore recently; is that
> what you're talking about?

No, its the postgres process thats memory-hungry, it happens
with "psql < db.dump" too.

If I run a dump thats produced with "pg_dump -m 5000" then
it loops between 20M and 10M is much better.  (the 10M
depends on shared_buffers I guess).

-- 
marko


Tue, 11 Dec 2001 18:19:36 +0200
Marko Kreen <marko@l-t.ee>
Re: Restoring large tables with COPY
On Tue, Dec 11, 2001 at 10:55:30AM -0500, Tom Lane wrote:
> Marko Kreen <marko@l-t.ee> writes:
> > Maybe I am missing something obvious, but I am unable to load
> > larger tables (~300k rows) with COPY command that pg_dump by
> > default produces.
> 
> I'd like to find out what the problem is, rather than work around it
> with such an ugly hack.

Hmm, the problem was more 'interesting' than I thought.
Basically:

1) pg_dump of 7.1.3 dumps constraints and primary keys
   with table defs in this case, so they are run during COPY.
2) I have some tricky CHECK contraints.

Look at the attached Python script, it reproduces the problem.
Sorry, cannot test on 7.2 at the moment.

-- 
marko
Tue, 11 Dec 2001 19:25:51 +0200
Marko Kreen <marko@l-t.ee>
Re: Restoring large tables with COPY
----- Original Message -----
From: Marko Kreen <marko@l-t.ee>
Sent: Tuesday, December 11, 2001 10:10 AM

If this thing ever gets through, shouldn't this

>   /* placeholders for the delimiters for comments */
> ***************
> *** 151,156 ****
> --- 153,159 ----
>   "  -h, --host=HOSTNAME      database server host name\n"
>   "  -i, --ignore-version     proceed even when server version mismatches\n"
>   "                           pg_dump version\n"
> + "  ï¿½m, --maxrows=NUM        max rows in one COPY command\n"

say '-m'

> + "  ï¿½m NUM                   max rows in one COPY command\n"

and this one too?



Tue, 11 Dec 2001 12:29:07 -0500
"Serguei Mokhov" <sa_mokho@alcor.concordia.ca>
Re: Restoring large tables with COPY
On Tue, Dec 11, 2001 at 12:29:07PM -0500, Serguei Mokhov wrote:
> If this thing ever gets through, shouldn't this
> 
> >   /* placeholders for the delimiters for comments */
> > ***************
> > *** 151,156 ****
> > --- 153,159 ----
> >   "  -h, --host=HOSTNAME      database server host name\n"
> >   "  -i, --ignore-version     proceed even when server version mismatches\n"
> >   "                           pg_dump version\n"
> > + "  ï¿½m, --maxrows=NUM        max rows in one COPY command\n"
> 
> say '-m'
> 
> > + "  ï¿½m NUM                   max rows in one COPY command\n"
> 
> and this one too?

One is for systems that have 'getopt_long', second for
short-getopt-only ones.  The '-h, --host=HOSTNAME' means
that '-h HOSTNAME' and '--host=HOSTNAME' are same.

-- 
marko


Tue, 11 Dec 2001 19:38:27 +0200
Marko Kreen <marko@l-t.ee>
Re: Restoring large tables with COPY
Marko Kreen <marko@l-t.ee> writes:
> Look at the attached Python script, it reproduces the problem.

Hmm.  You'd probably have much better luck if you rewrote the check_code
function in plpgsql: that should eliminate the memory-leak problem, and
also speed things up because plpgsql knows about caching query plans
across function calls.  IIRC, sql functions don't do that.

The memory leakage is definitely a bug, but not one that is going to get
fixed for 7.2.  It'll take some nontrivial work on the SQL function
executor...

			regards, tom lane

Tue, 11 Dec 2001 13:06:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Restoring large tables with COPY 
----- Original Message -----
From: Marko Kreen <marko@l-t.ee>
Sent: Tuesday, December 11, 2001 12:38 PM

> On Tue, Dec 11, 2001 at 12:29:07PM -0500, Serguei Mokhov wrote:
> > If this thing ever gets through, shouldn't this
> >
> > >   /* placeholders for the delimiters for comments */
> > > ***************
> > > *** 151,156 ****
> > > --- 153,159 ----
> > >   "  -h, --host=HOSTNAME      database server host name\n"
> > >   "  -i, --ignore-version     proceed even when server version mismatches\n"
> > >   "                           pg_dump version\n"
> > > + "  ï¿½m, --maxrows=NUM        max rows in one COPY command\n"
> >
> > say '-m'
> >
> > > + "  ï¿½m NUM                   max rows in one COPY command\n"
> >
> > and this one too?
>
> One is for systems that have 'getopt_long', second for
> short-getopt-only ones.  The '-h, --host=HOSTNAME' means
> that '-h HOSTNAME' and '--host=HOSTNAME' are same.

I know, I know. I just was trying to point out a typo :)
You forgot to add '-' in the messages before 'm'.




Tue, 11 Dec 2001 13:37:03 -0500
"Serguei Mokhov" <sa_mokho@alcor.concordia.ca>
Re: Restoring large tables with COPY
> > > > + "  ï¿½m, --maxrows=NUM        max rows in one COPY command\n"
> > >
> > > say '-m'

> You forgot to add '-' in the messages before 'm'.

Ah.  On my screen it looks lot like a '-', but od shows 0xAD...
Well, thats VIM's digraph feature in action ;)

-- 
marko


Tue, 11 Dec 2001 21:37:58 +0200
Marko Kreen <marko@l-t.ee>
Re: Restoring large tables with COPY
On Tue, Dec 11, 2001 at 01:06:13PM -0500, Tom Lane wrote:
> Marko Kreen <marko@l-t.ee> writes:
> > Look at the attached Python script, it reproduces the problem.
> 
> Hmm.  You'd probably have much better luck if you rewrote the check_code
> function in plpgsql: that should eliminate the memory-leak problem, and
> also speed things up because plpgsql knows about caching query plans
> across function calls.  IIRC, sql functions don't do that.

And I thought that the 'sql' is the more lightweight approach...

Thanks, now it seems to work.

-- 
marko


Tue, 11 Dec 2001 21:39:48 +0200
Marko Kreen <marko@l-t.ee>
Re: Restoring large tables with COPY