Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kTVL0-00049w-Mv for pgsql-bugs@arkaria.postgresql.org; Fri, 16 Oct 2020 19:24:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kTVKz-0003Oc-L2 for pgsql-bugs@arkaria.postgresql.org; Fri, 16 Oct 2020 19:24:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kTVDr-0004zi-Rg for pgsql-bugs@lists.postgresql.org; Fri, 16 Oct 2020 19:17:04 +0000 Received: from mx.caliper.com ([198.22.17.180]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kTVDk-0001l1-KH for pgsql-bugs@lists.postgresql.org; Fri, 16 Oct 2020 19:17:03 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=caliper.com; i=@caliper.com; q=dns/txt; s=maps; t=1602875816; x=1634411816; h=to:from:subject:message-id:date:mime-version; bh=awpovKbIb+caz6QvtlfSVfIoRmsPqMtv42NOOU4MS0A=; b=Vx2NUDwKEqvWnj5djOpY5FwvVkbOsNI/prlyQWuc729D2qqIzAGXAJin ls9MN1VCA4/DOdm4h0sIWQ2rrdZnmrGImd43C4JFjFSX5GQ6WRQ2RS8sf XcGEfCcntBcX4LuQsixDXlSUAfWEQNlUvvvy+BmSAumEiCf+nFjubTbxl w=; IronPort-SDR: SWWSCqq5xxxuvg1mr8JI3QNsaThu3lRSfR/fcHk8H48EK1VNZthmVDjo72IcM1rqt7HIjdF9RC 5JaOE6dQ32/Wwqrpi3HEPaR0569l+GyxODMeJ/xCAOmlL1iAPUZRqGocvwTs6OsExcT/N318Ef 8ZkaDs1RMgZb1aVKERkX7assh0E0dSXWOCXeVHiwf3T6Tu8jzQMsRTOGOExlXCEw/3byJdczHV SGNbPw2Cv06y3bmbRL1Z6zoTqgU5JvTNHiQjF1cfv12gTRf5XBj2v4XZ8xcWwf1pZJqld6dPLt fmk= X-IronPort-AV: E=Sophos;i="5.77,383,1596513600"; d="scan'208,217";a="4516101" X-Footer: Y2FsaXBlci5jb20= To: pgsql-bugs@lists.postgresql.org From: Mary LaClair Subject: copy command bug Message-ID: Date: Fri, 16 Oct 2020 15:16:52 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:68.0) Gecko/20100101 Thunderbird/68.12.1 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------0118512E9C0FA1C597D6AE83" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------0118512E9C0FA1C597D6AE83 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 • 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)));         } --------------0118512E9C0FA1C597D6AE83 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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 • 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)));
        }

--------------0118512E9C0FA1C597D6AE83--