Re: copy losing information

Thanks guys,

The output of select version() is
"PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3"

Tom, how and why would INSERTs be dropped on the client side?
I'll be away next week, but when I get back I'll conduct iterations to
find out how often this happens and if there is a pattern.

Reece, is there an ftp site where I can drop the file? It's way too
large for email.

The copy to the table from the file has been tried in two ways:

"C:\Program Files\PostgreSQL\8.1\bin\psql" -c "\copy mytable (series,
ticker, date, value) from C:\temp\myfile.out" -h <server> -U <user>
<database>

And also through a ruby script that uses the ruby-native library to
connect to Postgres, and sends INSERTs in batches of 2000.

Both ways work unreliably, sometimes getting every line, sometimes
dropping a few lines. I check that doing a "select count(*)", and
comparing against the number of lines in the file. The table gets
truncated before the import, and has no sort of indexing.


In case it can help, here is the definition.

CREATE TABLE mytable
(
  series varchar(15),
  ticker varchar(20),
  date date,
  value numeric,
  variable varchar(20),
  msa varchar(3),
  "year" int4,
  "month" int2,
  freq varchar(2),
  geog varchar(6)
)


Thanks
Jaime

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, July 26, 2006 5:05 PM
To: Alvaro Herrera
Cc: Silvela, Jaime (Exchange); pgsql-general@postgresql.org
Subject: Re: [GENERAL] copy losing information

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Silvela, Jaime (Exchange) wrote:
>> No lines contain quotes. And the same file will sometimes be fully
>> imported, and sometimes lose data. I'm thinking that under heavy
loads,
>> the database is discarding INSERTS.

> I don't think that's very likely.

Especially not since he says he's using COPY --- any sort of error would
be all-or-nothing.

Personally I'm wondering about individual rows getting dropped on the
client side.

            regards, tom lane



***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.

Bear Stearns does not provide tax, legal or accounting advice.  You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of:  (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************