Thread: Unable to copy large (>2GB) files using PostgreSQL 11 (Windows)
Hello,
I am unable to use psql's \COPY command to load in large text files on Windows. Here are the steps I used to reproduce:
1. Install PostgreSQL 11.0 from enterprisedb
2. Create two files: test1.txt (2.4 GB) and test2.txt (1.8 GB) - content doesn't matter
3. Run SQL Shell (psql)
postgres=# create table tbl (col int);
CREATE TABLE
postgres=# \COPY tbl FROM 'test1.txt'
could not stat file "test1.txt": Unknown error
postgres=# \COPY tbl from 'test2.txt'
(runs normally)
I believe the issue is related to `stat` not handling large files properly. I installed PostgreSQL 10.5 Windows x86-64 from the EDB website, attempted to reproduce, and *could not* reproduce the error, so I think this is a PostgreSQL 11 specific bug.
-Alistair
On Fri, Nov 02, 2018 at 02:46:44PM -0400, Alistair Johnson wrote: > I believe the issue is related to `stat` not handling large files properly. > I installed PostgreSQL 10.5 Windows x86-64 from the EDB website, attempted > to reproduce, and *could not* reproduce the error, so I think this is a > PostgreSQL 11 specific bug. You would be interested in this thread then: https://www.postgresql.org/message-id/1803D792815FC24D871C00D17AE95905CF5099@g01jpexmbkw24 Windows portability problems with stat() makes this problem way harder than it looks at first glance, because it also uses a "stat" structure which is not able to store the size of files larger than 2GB. I tried a couple of solutions for that, but gave up because of how things got easily dirty. I don't think that we want to replace the "stat" structure used all over the code for a "pg_stat" equivalent either, many folks rely on the system definitions for many patches, so that would be most likely forgotten and lead to extra bugs on Windows :( If somebody is smart enough to design a patch for that one, please feel free to send one.. -- Michael
Attachment
Michael Paquier wrote:
> Windows portability problems with stat() makes this problem way harder
> than it looks at first glance, because it also uses a "stat" structure
> which is not able to store the size of files larger than 2GB
FWIW I don't reproduce the problem with a self-compiled PostgreSQL 11.0
with the MSYS2/mingw64 toolchain on a Windows 10 64-bit desktop.
But I do get the error when using PG 11.0 packaged by
EDB and trying the same test on the same machine
(that is, \copy table from file with a file a bit over 2GB).
The error seems to be emited at the point of this fstat() call in
psql/copy.c:
/* make sure the specified file is not a directory */
if ((result = fstat(fileno(copystream), &st)) < 0)
psql_error("could not stat file \"%s\": %s\n",
options->file,
Note that it's fstat() rather than stat().
A quick git grep -w seems to show that fstat() doesn't go through
the same kind of aliasing that can make stat() a macro actually
calling pgwin32_safestat().
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
On Sat, Nov 03, 2018 at 12:11:53PM +0100, Daniel Verite wrote: > But I do get the error when using PG 11.0 packaged by > EDB and trying the same test on the same machine > (that is, \copy table from file with a file a bit over 2GB). This API layer comes from MSVC compilation, mingw has its own way of handling the stat() layer, which we could perhaps get inspiration from.. -- Michael