PostgreSQL 14.5 pg_read_binary_file could not open file for reading: Invalid argument-CodePudding

Yesterday I installed PostgreSQL 14.5 on a Windows 10 laptop. I then ran an old script to load images into a table. The script uses the pg_read_binary_file function. Some of the images are .jpg files and some are .png files. Of the 34 files, only 5 were successfully processed (1 .jpg and 4 .png). The other 29 failed with the following error:

[Exception, Error code 0, SQLState XX000] ERROR: could not open file "file absolute path" for reading: Invalid argument

For instance, the following statement executes without errors

select pg_read_binary_file('C:\Users\Jorge\OneDrive\Documents\000\020-logos\adalid.png') as adalid_png;

... and the following statement fails

select pg_read_binary_file('C:\Users\Jorge\OneDrive\Documents\000\020-logos\oper.png') as oper_png;

... with the following error message

[Exception, Error code 0, SQLState XX000] ERROR: could not open file "C:/Users/Jorge/OneDrive/Documents/000/020-logos/oper.png" for reading: Invalid argument

So far, I have not been able to identify any difference in the files that could be the cause of the error. Also, I'm pretty sure the script works on earlier releases of version 14. Unfortunately I have not been able to find a website to download any of those earlier releases to test it again.

Has anyone else found this problem, and its solution?

CodePudding user response：

I found the cause; the command fails when the filename is not lowercase. So I solved the problem by renaming all the image files.

For example, it fails with a filename JRCG1.JPG but succeeds if the file is renamed jrcg1.jpg.

Isn't technology wonderful?

Unfortunately the solution is not complete; the following commands keep failing:

update producto
set imagen=pg_read_binary_file('C:/Users/Jorge/OneDrive/Documents/000/030-productos/item-'||lpad(''||id-1000000,2,'0')||'.jpg')
where id between 1000001 and 1000020;

update pais set bandera=pg_read_binary_file('C:/Users/Jorge/OneDrive/Documents/000/025-banderas/'||lower(codigo)||'.png');

The first of these commands (update producto) should process files whose names are in lowercase, such as: item-01.jpg, item-02.jpg, item-03.jpg, etc.

The second command (update pais) should process files whose names are also in lowercase, such as: ad.png, ae.png, af.png, etc.

But they both fail, with these error messagges:

[Exception, Error code 0, SQLState XX000] ERROR: could not open file "C:/Users/Jorge/OneDrive/Documents/000/030-productos/item-01.jpg" for reading: Invalid argument

[Exception, Error code 0, SQLState XX000] ERROR: could not open file "C:/Users/Jorge/OneDrive/Documents/000/025-banderas/ad.png" for reading: Invalid argument

CodePudding user response：

Please ignore my previous answer. As you can see in it, there was no explanation for the failure of the last command (update pais).

Now I think the issue is somehow caused by OneDrive. This laptop is new. When I logged in with my Microsoft account, the OneDrive directory was automatically created and updated. Apparently this operation only updates the directory entries, leaving the contents of the files in the cloud until they are opened (or renamed). When I zipped the directory that contains all my images, a message from OneDrive appeared saying that in that moment it will restore some files. After that, all the commands in my scripts work. I even renamed (back to their original names) those files with hyphenated or uppercase names, and everything worked just fine.

My theory is that pg_read_binary_file gets the file entry from the directory, and so it doesn't give the "No such file or directory" message, but then it fails reading the contents; giving the "Invalid argument" message instead.

The unanswered question would be: why does 7-Zip make OneDrive restore the contents but pg_read_binary_file does not?