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