Thread: COPY problem
I am creating a new database on a brand new server (P4, 1GB RAM, postgres 7.3.3, debian 3.0) and trying to populate one of the tables with the COPY command. I split a large file with 20 million records into 20 files, but when I run COPY I usually get the following message: analytics=# COPY tbl555 FROM '/usr/local/pgsql/xaa' WITH NULL AS ''; ERROR: copy: line 167641, Query was cancelled. The line number varies each time I run it, and occasionally succeeds. I split up the data into even smaller files (100,000 rows) with the same results. Then I tried it on one of our older debian boxes (version ?) with postgres 7.3 and the COPY's succeed. I turned up logging on the new server but the messages returned are: Jul 16 09:04:43 imp postgres[31180]: [72-2] Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. Jul 16 09:04:43 imp postgres[31180]: [72-3] ^ICPU 0.00s/0.00u sec elapsed 0.00 sec. Jul 16 09:04:43 imp postgres[31180]: [73-1] DEBUG: Index pg_toast_4070343_index: Pages 1; Tuples 0. Jul 16 09:04:43 imp postgres[31180]: [73-2] ^ICPU 0.00s/0.00u sec elapsed 0.00 sec. Jul 16 09:04:43 imp postgres[31180]: [74] DEBUG: CommitTransactionCommand Jul 16 09:04:57 imp postgres[31180]: [75] DEBUG: StartTransactionCommand Jul 16 09:04:57 imp postgres[31180]: [76] DEBUG: ProcessUtility analytics=# COPY tc555 FROM '/usr/local/pgsql/xaa' WITH NULL AS ''; DEBUG: StartTransactionCommand DEBUG: ProcessUtility ERROR: copy: line 218765, Query was cancelled. DEBUG: AbortCurrentTransaction ERROR: copy: line 218765, Query was cancelled. I set the following values in postgresql.conf: tcpip_socket = true shared_buffers = 1000 # min max_connections*2 or 16, 8KB each max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes sort_mem = 32168 # min 64, size in KB vacuum_mem = 64336 # min 1024, size in KB fsync = false effective_cache_size = 32768 # typically 8KB each geqo_threshold = 25 server_min_messages = debug5 # Values, in order of decreasing detail: syslog = 1 # range 0-2 LC_MESSAGES = 'C' LC_MONETARY = 'C' LC_NUMERIC = 'C' LC_TIME = 'C' If someone has any ideas what the problem may be, please let me know. I am thinking that it may involve one of the following, but am not sure which: - my configuration file changes - ram - driver issues - mother board = asus p4 p800 - driver issues - hard drive (IDE) - ? Thanks in advance Ron
--- Ron <rstpierre@syscor.com> wrote: > I am creating a new database on a brand new server > (P4, 1GB RAM, > postgres 7.3.3, debian 3.0) and trying to populate > one of the tables > with the COPY command. I split a large file with 20 > million records into > 20 files, but when I run COPY I usually get the > following message: > > analytics=# COPY tbl555 FROM '/usr/local/pgsql/xaa' > WITH NULL AS ''; > ERROR: copy: line 167641, Query was cancelled. > > The line number varies each time I run it, and > occasionally succeeds. I > split up the data into even smaller files (100,000 > rows) with the same > results. Then I tried it on one of our older debian > boxes (version ?) > with postgres 7.3 and the COPY's succeed. I turned > up logging on the new > server but the messages returned are: > Jul 16 09:04:43 imp postgres[31180]: [72-2] > Free/Avail. Space 0/0; > EndEmpty/Avail. Pages 0/0. > Jul 16 09:04:43 imp postgres[31180]: [72-3] ^ICPU > 0.00s/0.00u sec > elapsed 0.00 sec. > Jul 16 09:04:43 imp postgres[31180]: [73-1] DEBUG: > Index > pg_toast_4070343_index: Pages 1; Tuples 0. > Jul 16 09:04:43 imp postgres[31180]: [73-2] ^ICPU > 0.00s/0.00u sec > elapsed 0.00 sec. > Jul 16 09:04:43 imp postgres[31180]: [74] DEBUG: > CommitTransactionCommand > Jul 16 09:04:57 imp postgres[31180]: [75] DEBUG: > StartTransactionCommand > Jul 16 09:04:57 imp postgres[31180]: [76] DEBUG: > ProcessUtility > > analytics=# COPY tc555 FROM '/usr/local/pgsql/xaa' > WITH NULL AS ''; > DEBUG: StartTransactionCommand > DEBUG: ProcessUtility > ERROR: copy: line 218765, Query was cancelled. > DEBUG: AbortCurrentTransaction > ERROR: copy: line 218765, Query was cancelled. > > > I set the following values in postgresql.conf: > tcpip_socket = true > shared_buffers = 1000 # min max_connections*2 > or 16, 8KB each > max_fsm_relations = 1000 # min 10, fsm is free > space map, ~40 bytes > sort_mem = 32168 # min 64, size in KB > vacuum_mem = 64336 # min 1024, size in KB > fsync = false > effective_cache_size = 32768 # typically 8KB each > geqo_threshold = 25 > server_min_messages = debug5 # Values, in order > of decreasing detail: > syslog = 1 # range 0-2 > LC_MESSAGES = 'C' > LC_MONETARY = 'C' > LC_NUMERIC = 'C' > LC_TIME = 'C' > > If someone has any ideas what the problem may be, > please let me know. I > am thinking that it may involve one of the > following, but am not sure which: > - my configuration file changes > - ram > - driver issues - mother board = asus p4 p800 > - driver issues - hard drive (IDE) > - ? > > Thanks in advance > Ron Does your postgresql partition have enough space? I was testing a Linux distro recently and gave /usr lots of space, forgetting that Linux distros often install postgresql and mysql to /var/db (or /var/lib/db -- I forget). If this is your problem (and you don't want to reinstall Debian), stop the database server, move the postgresql directory to a spacious partition, and create a link to it in the original location. Best of luck, Andrew Gould
Ron <rstpierre@syscor.com> writes:
> I am creating a new database on a brand new server (P4, 1GB RAM,
> postgres 7.3.3, debian 3.0) and trying to populate one of the tables
> with the COPY command. I split a large file with 20 million records into
> 20 files, but when I run COPY I usually get the following message:
> analytics=# COPY tbl555 FROM '/usr/local/pgsql/xaa' WITH NULL AS '';
> ERROR: copy: line 167641, Query was cancelled.
"Query was cancelled" is not a failure that the database would ever
produce on its own. Something external to the Postgres code decided
to send SIGINT to either psql or the connected backend.
I suspect that you are running into some kind of resource-usage-limiting
functionality that you were not aware was active. Perhaps you have
"ulimit" settings that are restricting how long a process can run or how
much I/O it can do. It's a really bad idea to start the postmaster with
any non-infinite ulimit settings :-(. ulimit on the client side is not
so dangerous, but could still prevent you from getting your work done,
as in this case.
regards, tom lane