COPY problem

From: Ron <rstpierre(at)syscor(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: COPY problem
Date: 2003-07-16 16:22:07
Message-ID: 3F157BAF.5010505@syscor.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2003-07-16 16:26:09 Re: Postgresql "FIFO" Tables, How-To ?
Previous Message Bruno Wolff III 2003-07-16 16:18:10 Re: IPv4 addresses, unsigned integers, space