Unable to execute PLR functions on new 9.6 install

[Goldfish42]

I recently overcame an issue getting PLR installed. Now that everything is running, I went to run a PLR function and received an error:
SELECT load_r_typenames();

ERROR: could not access "C:/Program Files/PostgreSQL/9.6/lib/plr": No such file or directory

This particular error came from an EnterpriseDB 9.6 install, but I also received the same error (different path) for a BigSQL 9.6 install (I changed my env variables accordingly).

The error isn't very useful. The plr.dll file is certainly in the location specified. What could be making it inaccessible?

[davecramer]

Spaces in the file name. It is better to use c:/Progra~1/....

[Goldfish42]

I just changed my environment variables per your suggestion, and restarted the server. Unfortunately, the error message is exactly the same as before.

I checked pg_config --pkglibdir and the output is:

C:/PROGRA~1/POSTGR~1/9.6/lib

Any other suggestions?

[davecramer]

I have my own issues on windows... :(
so when you change the env variables you get the same error message or a different error message?

[Goldfish42]

It gives the same error message as my first post.

[davecramer]

That suggests this is what PostgreSQL thinks is $libdir so installing PostgreSQL in something like C:\PostgreSQL or the like without spaces would be my first attempt to fix this

[Goldfish42]

It must be something else. I did try with my BigSQL install which has its lib dir with no spaces at:

C:\PostgreSQL\pg96\lib\postgresql

Not to mention I have a perfectly functioning 9.4 installation in my Program Files directory on a different computer (Win 7 instead of 10, however).

[Goldfish42]

The fact that it didn't specifically mention plr.dll troubled me (the error just ended with plr). So I took all of the source files and put them in a folder called plr and placed it inside of the lib directory. Everything works perfectly after that. This wasn't required for my previous 9.4 install on Win7. Any idea why the dll itself isn't enough to get everything working as before?

[Goldfish42]

It seems my previous effort was only a temporary "fix". I ran many operations, including calls to my PLR functions, and then I rebooted my computer. After rebooting and attempting any PLR-related activities, I get a slightly new error:
SELECT * FROM plr_environ()

ERROR: could not load library "C:/POSTGR~1/pg96/../pg96/lib/postgresql/plr.dll": The specified module could not be found.

I'm very confused at this point. Of course plr.dll is in that location, but I'm also wondering why the path suddenly involves the superfluous "../pg96/".

[davecramer]

So this path is from the bigsql implementation.

As for could not be found this is of course related to some other module that can't be found. Windows is not very helpful here in narrowing down what exactly is missing but something is...

[Goldfish42]

BigSQL or EnterpriseDB... it's really just a 9.6/8.3.0.17 issue giving me the same behavior on both distros. The DLL released on GitHub allows PLR installation to a database, but then it fails to properly run PLR functions until I drop the source into .../lib/plr which works great until I reboot (or maybe just restart the process?) after which point it complains about not finding the plr.dll.

9.5/8.3.0.16 works perfectly on the same machine using the last plr.dll left on Joe Conway's website. I read some posts about needing to ensure the same Windows SDK being used to compile plr.dll as EnterpriseDB used during their Postgres compile. Maybe that's a diff between 8.3.0.16 and 8.3.0.17? I'm not sure.

After some researching, I'm led to believe I should be using some kind of Windows debugging tool to figure out what's wrong with the 9.6 DLL on my machine, but I'm not yet sure how to attach it to my Postgres process. I found an older set of instructions for MSVS Express, but I'm not sure how closely the instructions relate to the Community edition.

[Goldfish42]

I just tried to build a 9.6 environment on a fresh Win7 machine. I've been able to replicate this issue using EnterpriseDB's 9.6 distro along with R 3.3.1 and plr 8.3.0.17. Here are the steps:

1. Install EnterpriseDB 9.6 (postgresql-9.6.3-2-windows-x64.exe)
   at Stackbuilder, install PostGIS and LanguagePack (probably not necessary to replicate, but I did these nonetheless)

2. Install R 3.3.1 (R-3.3.1-win.exe)
   Run R-Gui (64-bit) as Administrator
   > update.packages()
> q()

3. Add to existing PATH environment variable:
   C:\Program Files\R\R-3.3.1\bin\x64;
C:\Program Files\PostgreSQL\9.6\bin;
C:\Program Files\PostgreSQL\9.6\lib

4. Create R_HOME environment variable:
   C:\Program Files\R\R-3.3.1

5. Download plr64.zip and Source code from https://github.com/postgres-plr/plr/releases/tag/REL8_3_0_17
   Extract plr.dll from plr64.zip and place the DLL in C:\Program Files\PostgreSQL\9.6\lib
   Extract everything from plr-REL8_3_0_17.zip and place the following files in C:\Program Files\PostgreSQL\9.6\share\extension:
   plr.control
plr--8.3.0.17.sql
plr--unpackaged--8.3.0.17.sql

6. Install MS Visual C++ Redistributable 2015 (vc_redistx64.exe)

7. Open Administrative CMD prompt to C:\ (Press WIN key, then type cmd in the search box and press ctrl-shift-enter)
   C:\windows\system32> cd ../..
C:\> @powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://s3.amazonaws.com/pgcentral/install.ps1'))"

   Navigate to C:\bigsql (which the prior command should have created)
   C:\> cd bigsql
C:\bigsql> pgc install pgadmin3
C:\bigsql> net stop postgresql-x64-9.6
C:\bigsql> net start postgresql-x64-9.6
   (this restarts the postgres process to allow the environment variables to kick in)

8. In pgAdmin3 (C:\BigSQL\pgadmin3\bin\pgAdmin3.exe), connect to the database and run the following queries:
   CREATE SCHEMA plr;
CREATE EXTENSION plr SCHEMA plr VERSION "8.3.0.17"

9. Then run:
   SELECT plr.load_r_typenames();

ERROR: could not access file "C:/Program Files/PostgreSQL/9.6/lib/plr": No such file or directory

********** Error **********

ERROR: could not access file "C:/Program Files/PostgreSQL/9.6/lib/plr": No such file or directory
SQL state: 58P01

10. Go back to where you extracted the plr source from plr-REL8_3_0_17.zip and rename the extracted folder to plr. Drop this folder into C:/Program Files/PostgreSQL/9.6/lib/plr

11. Back in pgAdmin3, re-run the query:
    SELECT plr.load_r_typenames();

Messages:

Total query runtime: 11 msec
1 row retrieved.

Data Output:

load_r_typenames
text
OK

This is the part I reached previously where dropping the whole contents of 8.3.0.17's source folder into C:/Program Files/PostgreSQL/9.6/lib/plr would get things working until the next reboot.

I hope this helps others to replicate the issue and assist with troubleshooting.
Thanks!

EDIT
I just tested a reboot and everything works normally, unlike on my previous Win10 attempt. This is new, and usable enough for me to move forward. But I still feel like something went awry, forcing me to put the source into ...lib/plr

[Goldfish42]

Sorry for the delay-- I've been pretty swamped with work.

@jconway provided an 8.3.0.17 release which worked great for my EnterpriseDB 9.6 installation.

Still no luck getting BigSQL's 9.6 to work without hacking the plr source into the lib folder, but I feel good enough to close this issue.

[jconway]

Yeah, no surprise that it does not work with BigSQL as I believe their version of Postgres is compiles with MinGW not MSVC. I have found in the past that PL/R need to be compiled using whatever Postgres itself is compiled with on Windows in order for everything to work as expected.