pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mary LaClair <mary@caliper.com>
To: pgsql-bugs@lists.postgresql.org
Subject: copy command bug
Date: Fri, 16 Oct 2020 15:16:52 -0400
Message-ID: <e831d360-c991-3078-01e6-47533df4c179@caliper.com> (raw)

The copy command fails to load a large csv file (>2GB) with a "Could not 
stat file" error.  This occurs on Windows with all versions since 10.7.
It happens because fstat() on Windows fails with large csv files.

Example:
COPY table_name FROM 'filename.csv' WITH CSV HEADER
succeeds with small files and fails with larger ones.

In my tests, using other methods to load the file, e.g.
COPY table_name FROM PROGRAM 'cmd /c \"type filename.csv"' WITH CSV HEADER
succeeds with all file sizes but runs /10-30% slower/, which is a 
significant difference.

Thanks, Mary

Mary LaClair

Vice President, Software Development | Caliper Corporation

|||||||||||||||||||||||||||||||||||||||||||||||||

1172 Beacon St, Ste 300 • Newton MA 02461 USA

Direct: 617-340-2003 • Main: 617-527-4700

mary@caliper.com <mailto:mary@caliper.com> • www.caliper.com 
<https://www.caliper.com;


P.S.
I downloaded the  master source and implemented a patch but was unable 
to test because I do not have a version of Visual Studio (11 or 12) old 
enough to try it:
Here's the change to BeginCopyFrom() in copy.c, starting at line 3529:
   {
unsigned short st_mode;

             cstate->copy_file = AllocateFile(cstate->filename, 
PG_BINARY_R);
             if (cstate->copy_file == NULL)
             {
                 /* copy errno because ereport subfunctions might change 
it */
                 int            save_errno = errno;

                 ereport(ERROR,
                         (errcode_for_file_access(),
                          errmsg("could not open file \"%s\" for 
reading: %m",
                                 cstate->filename),
                          (save_errno == ENOENT || save_errno == EACCES) ?
                          errhint("COPY FROM instructs the PostgreSQL 
server process to read a file. "
                                  "You may want a client-side facility 
such as psql's \\copy.") : 0));
             }

#ifndef WIN32
             {
             struct _stat32i64 st;
             // need the i64 version to handle files > 1GB
             if (_fstat32i64(fileno(cstate->copy_file), &st))
                 ereport(ERROR,
                         (errcode_for_file_access(),
                          errmsg("could not stat file \"%s\": %m",
                                 cstate->filename)));
             st_mode = st.st_mode;
             }
#else
             {
             struct stat st;
         if (fstat(fileno(cstate->copy_file), &st))
                 ereport(ERROR,
                         (errcode_for_file_access(),
                          errmsg("could not stat file \"%s\": %m",
                                 cstate->filename)));
             st_mode = st.st_mode;
             }
#endif

             if (S_ISDIR(st_mode))
                 ereport(ERROR,
                         (errcode(ERRCODE_WRONG_OBJECT_TYPE),
                          errmsg("\"%s\" is a directory", 
cstate->filename)));
         }

view thread (2+ messages)  latest in thread

Message-ID: <e831d360-c991-3078-01e6-47533df4c179@caliper.com>
Permalink:  ../e831d360-c991-3078-01e6-47533df4c179@caliper.com/
Also on:    postgresql.org/message-id/e831d360-c991-3078-01e6-47533df4c179@caliper.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: mary@caliper.com
  Subject: Re: copy command bug
  In-Reply-To: <e831d360-c991-3078-01e6-47533df4c179@caliper.com>
  References: <e831d360-c991-3078-01e6-47533df4c179@caliper.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox