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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

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

PG Doc comments form
The following bug has been logged on the website:

Bug reference:      16127
Logged by:          yanliang lei
Email address:      [hidden email]
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!!!
时间:4.558 ms
postgres=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit  
----->>>>PostgreSQL version is 12.1
(1 行记录)


时间:0.280 ms
postgres=#

Please note:
1.  d:\large_events.csv this file is large 2GB。
2.  the software “postgresql-12.1-1-windows-x64.exe” is downloaded from
enterprisedb website.

Reply | Threaded
Open this post in threaded view
|

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

Tomas Vondra-4
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:      [hidden email]
>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


Reply | Threaded
Open this post in threaded view
|

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

leiyanliang@highgo.com
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[hidden email]

 
发件人: [hidden email]
发送时间: 2019-11-20 21:21
主题: 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:      [hidden email]
>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
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by PG Doc comments form
PG Bug reporting form <[hidden email]> 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:

https://www.postgresql.org/message-id/flat/15858-9572469fd3b73263@...
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


Reply | Threaded
Open this post in threaded view
|

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

Tomas Vondra-4
In reply to this post by leiyanliang@highgo.com
On Wed, Nov 20, 2019 at 09:42:49PM +0800, [hidden email] 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],
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].


regards


[1] https://www.postgresql.org/message-id/15858-9572469fd3b73263%40postgresql.org

[2] https://www.postgresql.org/message-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


Reply | Threaded
Open this post in threaded view
|

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

Michael Paquier-2
In reply to this post by Tom Lane-2
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:
>
> https://www.postgresql.org/message-id/flat/15858-9572469fd3b73263@...
> 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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

sirlipeng
In reply to this post by Tomas Vondra-4
On Wed, Nov 20, 2019 at 09:42:49PM +0800, [hidden email] 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

Best Wishes
lipeng
----------------------
HighGo Software

Reply | Threaded
Open this post in threaded view
|

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

Juan José Santamaría Flecha


On Thu, Nov 28, 2019 at 5:16 AM sirlipeng <[hidden email]> wrote:
On Wed, Nov 20, 2019 at 09:42:49PM +0800, [hidden email] 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