COPY problem

--- 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