BUG #16127: PostgreSQL 12.1 on Windows 2008 R2 copy table from ‘large 2GB csv’report “Unknown error”

On Wed, Nov 20, 2019 at 12:42:38PM +0000, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 16127
Logged by: yanliang lei
Email address: leiyanliang@highgo.com
PostgreSQL version: 12.1
Operating system: Windows 2008 R2
Description:

D:\>dir
驱动器 D 中的卷是 新加卷

D:\Program Files\PostgreSQL\12>cd bin

D:\Program Files\PostgreSQL\12\bin>psql -d postgres -U posgres
用户 posgres 的口令：
psql: 错误: 无法连接到服务器：FATAL: password authentication failed for user "p
osgres"

D:\Program Files\PostgreSQL\12\bin>psql -d postgres -U postgres
用户 postgres 的口令：
psql (12.1)
输入 "help" 来获取帮助信息.

postgres=# select version();
version
------------------------------------------------------------
PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit
(1 行记录)

postgres=# CREATE TABLE github_events
postgres-# (
postgres(# event_id bigint,
postgres(# event_type text,
postgres(# event_public boolean,
postgres(# repo_id bigint,
postgres(# payload jsonb,
postgres(# repo jsonb,
postgres(# user_id bigint,
postgres(# org jsonb,
postgres(# created_at timestamp
postgres(# );
CREATE TABLE
postgres=# \timing
启用计时功能.
postgres=# set client_encoding='UTF8';
SET
时间：1.185 ms
postgres=# set lc_messages=en_us;
SET
时间：23.467 ms
postgres=# copy github_events from 'd:\large_events.csv' csv;
ERROR: could not stat file "d:\large_events.csv": Unknown error
----->>>Please note this error!!!

This probably means the PostgreSQL user/process does not have access to
the file, either because it does not exist, lack of privileges, an AV
system blocking the access, or something like that.

I suggest you seach in the Windows Event Log, and various other logs you
might have there.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Thank you for reply.

I do not think the root cause of this problem is about privilege,and this windows machine is my test machine, only I have access to this machine.

I think this problem is a bug,there are two evidences:

The first evidence :

postgres=# select version();
version
------------------------------------------------------------
PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit
(1 行记录)

时间：0.280 ms
postgres=#
postgres=#
postgres=# copy github_events from 'd:\events.csv' csv; ----->>> d:\events.csv( about 316,302,020 Bytes) can copy succussfully!!
COPY 126245
时间：19574.819 ms (00:19.575)
postgres=# \q

D:\Program Files\PostgreSQL\12\bin>cd \

D:\>dir
驱动器 D 中的卷是 新加卷
卷的序列号是 6CCB-1F3C

D:\ 的目录

2019-09-01 14:49 130,128,838 001190828090801.MSS
2019-09-01 15:09 <DIR> cwdata
2019-07-26 10:40 7,924 dd_vcredist_amd64_20190726103808.log
2019-09-29 12:31 316,302,020 events.csv ----------->>>d:\events.csv is 316,302,020 Bytes.
2019-07-16 19:03 <DIR> hgdb5.6.4-enterprise-windows2012-x86-64-2019
0614
2019-07-16 18:59 334,664,468 hgdb5.6.4-enterprise-windows2012-x86-64-2019
0614.zip
2019-07-16 19:04 <DIR> highgo
2019-09-01 15:05 <DIR> INSPUR
2019-09-29 17:56 3,347,357,856 large_events.csv----------->>>d:\large_events.csv is 3,347,357,856 Bytes.
2019-11-15 09:30 196,064,592 postgresql-12.1-1-windows-x64.exe
2019-11-20 20:19 <DIR> Program Files
2019-07-29 10:33 <DIR> ps标准版11.0
2019-09-01 14:58 1,351,706,026 ps标准版11.0.zip
2019-07-26 10:32 7,201,776 vcredist_x64.exe
2019-07-26 10:51 2,700,960 Windows6.1-KB2677070-x64.msu
2019-08-08 17:32 444 新加卷 (D) - 快捷方式.lnk
10 个文件 5,686,134,904 字节
6 个目录 72,976,457,728 可用字节

the second evidence:

https://github.com/MIT-LCP/mimic-code/issues/493
https://www.postgresql-archive.org/Unable-to-copy-large-gt-2GB-files-using-PostgreSQL-11-Windows-td6057082.html

祝工作顺利！
----------------------------------
类延良 研发一部
瀚高基础软件股份有限公司
网址：www.highgo.com
地址：济南市高新区新泺大街2117号铭盛大厦20层
手机：138-0531-7390 邮箱：leiyanliang@highgo.com

发件人： Tomas Vondra
发送时间： 2019-11-20 21:21
收件人： leiyanliang@highgo.com; pgsql-bugs@lists.postgresql.org
主题： Re: BUG #16127: PostgreSQL 12.1 on Windows 2008 R2copy table from ‘large 2GB csv’report “Unknown error”
On Wed, Nov 20, 2019 at 12:42:38PM +0000, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 16127
Logged by: yanliang lei
Email address: leiyanliang@highgo.com
PostgreSQL version: 12.1
Operating system: Windows 2008 R2
Description:

D:\>dir
驱动器 D 中的卷是 新加卷

D:\Program Files\PostgreSQL\12>cd bin

D:\Program Files\PostgreSQL\12\bin>psql -d postgres -U posgres
用户 posgres 的口令：
psql: 错误: 无法连接到服务器：FATAL: password authentication failed for user "p
osgres"

D:\Program Files\PostgreSQL\12\bin>psql -d postgres -U postgres
用户 postgres 的口令：
psql (12.1)
输入 "help" 来获取帮助信息.

postgres=# select version();
version
------------------------------------------------------------
PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit
(1 行记录)

postgres=# CREATE TABLE github_events
postgres-# (
postgres(# event_id bigint,
postgres(# event_type text,
postgres(# event_public boolean,
postgres(# repo_id bigint,
postgres(# payload jsonb,
postgres(# repo jsonb,
postgres(# user_id bigint,
postgres(# org jsonb,
postgres(# created_at timestamp
postgres(# );
CREATE TABLE
postgres=# \timing
启用计时功能.
postgres=# set client_encoding='UTF8';
SET
时间：1.185 ms
postgres=# set lc_messages=en_us;
SET
时间：23.467 ms
postgres=# copy github_events from 'd:\large_events.csv' csv;
ERROR: could not stat file "d:\large_events.csv": Unknown error
----->>>Please note this error!!!

This probably means the PostgreSQL user/process does not have access to
the file, either because it does not exist, lack of privileges, an AV
system blocking the access, or something like that.

I suggest you seach in the Windows Event Log, and various other logs you
might have there.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

PG Bug reporting form <noreply@postgresql.org> writes:

postgres=# copy github_events from 'd:\large_events.csv' csv;
ERROR: could not stat file "d:\large_events.csv": Unknown error
----->>>Please note this error!!!

1. d:\large_events.csv this file is large 2GB

Given that, I wonder if this isn't the same issue being worked
on over here:

/messages/by-id/15858-9572469fd3b73263@postgresql.org
https://commitfest.postgresql.org/25/2189/

If you could help review/test that patch, it would make it more
likely to get fixed soon.

regards, tom lane

On Wed, Nov 20, 2019 at 09:42:49PM +0800, leiyanliang@highgo.com wrote:

Thank you for reply.

I do not think the root cause of this problem is about privilege,and this windows machine is my test machine, only I have access to this machine.

I think this problem is a bug,there are two evidences:

The first evidence :

postgres=# select version();
version
------------------------------------------------------------
PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit
(1 行记录)

时间：0.280 ms
postgres=#
postgres=#
postgres=# copy github_events from 'd:\events.csv' csv; ----->>> d:\events.csv( about 316,302,020 Bytes) can copy succussfully!!
COPY 126245
时间：19574.819 ms (00:19.575)
postgres=# \q

Ah, I haven't noticed the information about file size in the first post,
as it was buried at the very end :-(

In that case, I think Tom is right it's likely the same issue as [1]/messages/by-id/15858-9572469fd3b73263@postgresql.org,
although that starts with a report about issues with 4GB. Maybe that's
due to using different windows version 2008 R2 vs. 2012 R2, though.

It'd be good if you could test the latest patch [2]/messages/by-id/attachment/103789/0001-WIP-support-for-large-files-on-Win32-v4.patch in that thread.
You'll need to build PostgreSQL on Windows, though - I don't have much
experience with that, but there are wiki pages about doing that either
using Visual Studio [3]https://wiki.postgresql.org/wiki/Working_With_VisualStudio or mingw [4]https://wiki.postgresql.org/wiki/Building_With_MinGW.

regards

[1]: /messages/by-id/15858-9572469fd3b73263@postgresql.org

[2]: /messages/by-id/attachment/103789/0001-WIP-support-for-large-files-on-Win32-v4.patch

[3]: https://wiki.postgresql.org/wiki/Working_With_VisualStudio

[4]: https://wiki.postgresql.org/wiki/Building_With_MinGW

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Nov 20, 2019 at 10:47:41AM -0500, Tom Lane wrote:

Given that, I wonder if this isn't the same issue being worked
on over here:

/messages/by-id/15858-9572469fd3b73263@postgresql.org
https://commitfest.postgresql.org/25/2189/

If you could help review/test that patch, it would make it more
likely to get fixed soon.

I think that's the same issue with stat() not working for files larger
than 2GB on Windows.
--
Michael

<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>
</head>
<body>
<style>
font{
line-height: 1.6;
}
ul,ol{
padding-left: 20px;
list-style-position: inside;
}
</style>
<div style = 'font-family:微软雅黑,Verdana,&quot;Microsoft Yahei&quot;,SimSun,sans-serif;font-size:14px; line-height:1.6;'>
<div ></div><div>
<div><span style="line-height: 22.4px;">On Wed, Nov 20, 2019 at 09:42:49PM +0800, leiyanliang@highgo.com wrote:</span></div><div><br style="line-height: 22.4px;"><span style="line-height: 22.4px;">&gt;In that case, I think Tom is right it's likely the same issue as [1],</span><br style="line-height: 22.4px;"><span style="line-height: 22.4px;">&gt;although that starts with a report about issues with 4GB. Maybe that's</span><br style="line-height: 22.4px;"><span style="line-height: 22.4px;">&gt;due to using different windows version 2008 R2 vs. 2012 R2, though.</span><br style="line-height: 22.4px;">&gt;<br style="line-height: 22.4px;"><span style="line-height: 22.4px;">&gt;It'd be good if you could test the latest patch [2] in that thread.</span><br style="line-height: 22.4px;"><span style="line-height: 22.4px;">&gt;You'll need to build PostgreSQL on Windows, though - I don't have much</span><br style="line-height: 22.4px;"><span style="line-height: 22.4px;">&gt;experience with that, but there are wiki pages about doing that either</span><br style="line-height: 22.4px;"><span style="line-height: 22.4px;">&gt;using Visual Studio [3] or mingw [4].</span><br style="line-height: 22.4px;"><br style="line-height: 22.4px;">I have had the same problem on both win2008r2(64bit) and win10(64bit) with postgreSQL12.1.</div><div><br style="line-height: 22.4px;">The problem has been solved when the above path been applied on&nbsp;<span style="line-height: 22.4px;">win2008r2(64bit) and win10(64bit).</span></div></div><div><span style="line-height: 22.4px;"><br></span></div><div>It works:</div><div><div>postgres=# copy github_events from '\\vmware-host\Shared Folders\share\large_eve</div><div>nts.csv' csv;</div><div>COPY 1146625</div></div><div><br></div><div>Best Wishes</div><div>lipeng</div><div>----------------------</div><div>HighGo Software</div><div><br></div><!--😀-->
</div>
</body>
</html>

On Thu, Nov 28, 2019 at 5:16 AM sirlipeng <sirlipeng@gmail.com> wrote:

On Wed, Nov 20, 2019 at 09:42:49PM +0800, leiyanliang@highgo.com wrote:

In that case, I think Tom is right it's likely the same issue as [1],
although that starts with a report about issues with 4GB. Maybe that's
due to using different windows version 2008 R2 vs. 2012 R2, though.

It'd be good if you could test the latest patch [2] in that thread.
You'll need to build PostgreSQL on Windows, though - I don't have much
experience with that, but there are wiki pages about doing that either
using Visual Studio [3] or mingw [4].

I have had the same problem on both win2008r2(64bit) and win10(64bit) with
postgreSQL12.1.

The problem has been solved when the above path been applied on win2008r2(64bit)
and win10(64bit).

It works:
postgres=# copy github_events from '\\vmware-host\Shared
Folders\share\large_eve
nts.csv' csv;
COPY 1146625

Great! Thanks for testing.

Regards,

Juan José Santamaría Flecha