Cannot load plr.dll for my pg9.6 on window 7 64bit about plr HOT 33 CLOSED

Please give this a try: https://github.com/postgres-plr/plr/releases/download/REL8_3_0_17/plr-8.3.0.17-pg9.6-R3.4.1-win64.zip

from plr.

@sgennaria ok, have a look at #21 plr.dll is the only file in there. As the note on the PR says. I needed to set the PATH to %R_HOME%/bin/x64 to get it to work. Note it also uses R 3.4.1.

Thanks

from plr.

This is a pretty common thread. There is another issue #19 and issue #17 with exactly the same complaint.
Installing the msvc dll seems to help some.

The dll is actually there, but you are missing some dll which it is dependant on.

from plr.

I am not sure I know anything, but here it goes.

In here
https://www.postgresql.org/docs/9.6/static/install-procedure.html
reads

For relocatable installs, you might want to use configure's --disable-rpath option. 

Line

Also, you will need to tell the operating system how to find the shared libraries.

does not apply to Windows.

But line

For relocatable installs, you might want to use configure's --disable-rpath option. 

does apply to windows.

From "pg96" I can tell tell its BigSQL ( that is compiled using GNU ).

In the past, when I, personally, compiled pl/r using GNU, I compiled all of postgreSQL ( -disable-rpath ) first, then used the postgreSQL configuration ( -disable-rpath ) to 'quickly' compiile pl/r.

I wonder if BigSQL was compiled using --disable-rpath

I looked at my BigSQL installation

C:\PostgreSQL-9.6._-win64-bigsql\pg96>pg96-env.bat

C:\PostgreSQL-9.6._-win64-bigsql\pg96>pg_config --configure
'--prefix=C:/msys64/opt/pgbin-build/pgbin/bin/../20170509_0538/build/pg96-9.6.3-1-win64' '--enable-integer-datetimes' '--enable-thread-safety' '--with-libxml' '--with-libxslt' '--with-ossp-uuid' '--enable-nls' '--with-openssl' '--with-python' '--with-perl' '--with-tcl' '--with-libraries=/opt/pgbin-build/pgbin/shared/win64/lib:/usr/local/lib:/usr/local/ssl/lib' '--with-includes=/usr/local/include:/usr/local/ssl/include' 'CFLAGS=-O2 -DMS_WIN64 -I/opt/pgbin-build/pgbin/shared/win64/include'

So --disable-rpath Is not there. So I am guessing. I do not know anything. But I said my 2 cents.

from plr.

@davecramer since my comments on issue #17 I have switched to a Win7 machine, and installed PG9.6. I'm using R version 3.4.1. Unfortunately I'm having the same issue as before:

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

Following the advice given to @sgennaria, I installed MSVC++ Redistributable for VS2017, but am receiving the same error message.

from plr.

@birdwrangler I know for sure it was not built with R 3.4. I believe it is 3.3.1

I apologize that we aren't more help but windows is particularly challenging to debug.

Can you try with 3.3.1 ?

from plr.

@davecramer I tried the earlier version of R, but this did not work - same error message.
To be more specific:
Installed R 3.3.1 on Windows 7, using PG 96
Downloaded plr-REL8_3_0_17 (have also tried _16 version)
Followed same steps as @sgennaria (where .dll, .sql, and .control files were placed - the paths are different for the newer version)
Updated environment variables
Restarted PG server & also tried restarting computer

I also tried just opening the plr.sql file (since this was how plr was loaded in older PG versions), and received this error:

ERROR:

syntax error at or near "none"
LINE 5: set ECHO none,
^

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

ERROR: syntax error at or near "none"
SQL state: 42601

from plr.

@jconway Your 8.3.0.17 release solved my #19 problem for EnterpriseDB's 9.6 distro.

Unfortunately, I still get errors about not being able to find "C:/POSTGR~1/pg96/../pg96/lib/postgresql/plr.dll" when trying to install it on my BigSQL 9.6 distro.

I guess I'll be sticking with EnterpriseDB for now.

from plr.

@sgennaria I have a dll I built on bigsql, Do you have time to try it ?

from plr.

Yup! Thanks!

from plr.

No go. Got an error trying to install plr:

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

PATH had the following:

C:\Program Files\R\R-3.4.1\bin\x64;
C:\PostgreSQL\pg96\bin;
C:\PostgreSQL\pg96\lib;
C:\PostgreSQL\pg96\lib\postgresql

from plr.

You may need
set R_HOME=C:\Program Files\R\R-3.4.1
set PATH=C:\PostgreSQL\pg96\bin;C:\PostgreSQL\pg96\lib;C:\PostgreSQL\pg96\lib\postgresql;%PATH%

from plr.

I did have R_HOME and PATH set exactly as you described. I also tried permutations of using only one directory with lib (either \lib\postgresql or just \lib), and I tried these same combinations with R_HOME as C:\Program Files\R\R-3.4.1\bin\x64 just in desperation. I restarted the server after each change, prior to each installation attempt. Same exact error message every time.

I also verified the following:

C:>pg_config --libdir
C:/POSTGR~1/pg96/lib
and
C:>pg_config --pkglibdir
C:/POSTGR~1/pg96/lib/POSTGR~1

from plr.

Where does this issue currently stand? Is there something needing to be done or can it be closed?

from plr.

To my knowledge, this is solved for the EnterpriseDB distro, but not BigSQL.

from plr.

Just recently I tried installing a MSYS2 MINGW64 compiled/built PostgreSQL 10 plr.dll into BigSQL pg10. I ended up getting the same error: "C:/POSTGR~1/pg10/../pg10/lib/postgresql/plr.dll". The situation seems the BigSQL may have "something else required." I can not find their methods of their 'build system.' sgennaria, there is a help forum here: https://www.openscg.com/bigsql/forum/. Also sgennaria, someone just posted "plpython2.dll not found on postgresql 9.6.5" No one has answered yet, but that seems like the same class of problem.

from plr.

Also, sgennaria, mentioned in here "I tried to create the oracle_fdw extension and gave error could not load library libclntsh.so.10.1" ( https://www.openscg.com/bigsql/forum/ ) near the bottom mentions ...

HH !  It's systemd playing games. 
You'll need to edit the systemd unit file in order to fix this.
1.sudo vi /usr/lib/systemd/system/postgresql96.service
2. Right above: OOMScoreAdjust=-1000  add:
     Environment=LD_LIBRARY_PATH=/your/ld/library/path

3. Save and close
4. reload the unit
    sudo systemctl daemon-reload]
5. Restart postgres
    ./pgc restart pg96

After doing the steps you passed.
I was able to verify that the LD_LIBRARY_PATH variable was recognized by postgresql server
The CREATE EXTENSION command oracle_fdw; worked perfectly.

sgennaria, so maybe the 'BigSQL postgres windows service' can not see extra new PATHs and extra new enviroment variables. To test, one may start (if possible), Windows BigSQL postgres 'outside of its windows service'.
To test exactly, start a single Command prompt. Next, in that Command prompt, setup the PATH and environment variables mentioned above in this issue. Next run (from the same command prompt ) postgres.exe.

from plr.

@AndreMikulec your suggestion worked! BTW, I had already asked about BigSQL PLR support back in May. I just bumped it up again, so hopefully it gets some attention.

So after your suggestion worked, I dropped PLR, killed the postgres.exe process and used pgc start pg96 to start the server back up. Surprisingly, a new PLR install worked at this point. So I ran pgc stop pg96 and then pgc config pg96 --autostart=on and rebooted my computer.

When it came back up, the service wasn't running yet, so I manually started the service, attempted to install PLR but got the same error about not finding the DLL. Then I stopped the Windows service, ran pgc start pg96 and it still failed to install with the same error. So then after pgc stop pg96, I ran postgres.exe with -D pointing to my bigsql data directory, and it worked again! But at this point, even if I re-try with pgc start pg96 or by using the new Windows service, it still fails with the same error. Then I ran pgc config pg96 --autostart=off and then immediately pgc start pg96 and now the PLR install works again.

So something inherent to BigSQL's Windows service installation is messing up these paths. I'm not sure where to troubleshoot further...

from plr.

OK I've spent a lot of time messing with this today, and it appears that my issue comes from using administrative command prompts while creating the pg96 service. Here is what works for me:

1. In an Administrative CMD Prompt, ensure there is no pg96 service installed: pgc config pg96 --autostart=off
2. In a separate NON-Administrative CMD Prompt, configure the pg96 service to autostart: pgc config pg96 --autostart=on
3. Back in the Administrative CMD Prompt, start the pg96 service: pgc start pg96
4. Login to your server and install PLR. It should work now.

The funny thing after step 3 is that you can even remove the four PATH variables for R, \bin, \lib and \lib\postgresql and you can still install/uninstall and run PLR functions successfully. The PATH env variable apparently gets saved into BigSQL's own configuration at the time the autostart setting is turned on, which creates the Windows service.

NB: The problem with this workaround is that you can only non-administratively autostart a single BigSQL Postgres install at a time. If you try to configure autostart on a second version, you get the following error:

You must run as administrator/root when there are any AUTOSTART components.

I submitted this info to BigSQL's discussion forum in hopes that they come up with a fix.

from plr.

@sgennaria, I hope the BigSQL people answer back. I would really like to know how to install custom extensions (especially 'external dependency reliant extensions' such as plr) directly into BigSQL.

@sgennaria, or anyone, if motivated, the 'service creation'/'service run' logic seems to be in here.

C:\PostgreSQL-10._-win64-bigsq\pg10

10/07/2017  02:31 PM    <DIR>          .
10/07/2017  02:31 PM    <DIR>          ..
10/04/2017  07:32 AM               776 activity-pg10.py
10/07/2017  02:29 PM    <DIR>          bin
10/04/2017  07:32 AM             8,300 config-pg10.py
10/04/2017  07:32 AM             1,054 create_service.bat
10/07/2017  02:29 PM    <DIR>          include
10/07/2017  02:28 PM    <DIR>          init
10/04/2017  07:32 AM             7,263 init-pg10.py
10/07/2017  02:29 PM    <DIR>          lib
10/07/2017  03:22 PM               403 pg10-env.bat
10/04/2017  07:32 AM           103,936 pgservice.exe
10/04/2017  07:32 AM           104,448 pgservicew.exe
10/04/2017  07:32 AM               799 reload-pg10.py
10/04/2017  07:32 AM               878 remove-pg10.py
10/04/2017  07:32 AM             1,279 run-pgctl.py
10/07/2017  02:28 PM    <DIR>          share
10/04/2017  07:32 AM             2,358 start-pg10.py
10/04/2017  07:32 AM             1,938 stop-pg10.py
              12 File(s)        233,432 bytes
               7 Dir(s)  1,817,657,446,400 bytes free

This looks like a 'lot of python.'
pg10-env.bat, config-pg10.py, and create_service.bat may be good places to start looking.

For my own entertainment, I created a portable BigSQL portable. I used my own customized .bat file. This file, early in my usage, was similar what is found/used here: https://sourceforge.net/projects/postgresqlportable/files/. (NOTE: These seem to be EnterpriseDB 32bit PostgreSQL files)

I gathered the relevant BigSQL PostgreSQL directories and files from my directory, C:\PostgreSQL-10._-win64-bigsq\pg10.

Using my current .bat file, the ONLY thing I did is copy (and rename) my PG-10..bat to PG-10big..bat (and removed extra added BigSQL directories and files) and I did nothing else.

From my vanilla MSYS2 MINGW64 PostgreSQL 10 / plr build, I collected the relevant plr files and placed them in the gathered BigSQL PostgreSQL directories and files . Next, I (started my new BigSQL portable and ) installed plr.

plr on seems to work just fine in BigSQL PostgreSQL Windows 64bit.

psql (10.0)
Type "help" for help.

postgres=# select version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
(1 row)

postgres=# create extension plr;
CREATE EXTENSION
postgres=# select r_version();
                    r_version
-------------------------------------------------
 (platform,x86_64-w64-mingw32)
 (arch,x86_64)
 (os,mingw32)
 (system,"x86_64, mingw32")
 (status,"")
 (major,3)
 (minor,4.2)
 (year,2017)
 (month,09)
 (day,28)
 ("svn rev",73368)
 (language,R)
 (version.string,"R version 3.4.2 (2017-09-28)")
 (nickname,"Short Summer")
(14 rows)

from plr.

@AndreMikulec so this is your plr that you built running in bigsql ?

from plr.

@davecramer BTW I haven't been clear on the DLL that worked for me. I got this to work using your DLL from here. The one from the 8.3.0.17 release does not work with BigSQL for me.

from plr.

@davecramer
Yes, I built that dll plr.dll using MSYS2 MINGW64 (gcc 7.2) using a slightly modified Windows (and R) friendly Makefile (that is still based on the original/current plr Makefile).

from plr.

@AndreMikulec at this point I don't even know what to try to fix this. Seems windows is very particular as to which compiler, and which dll's are loaded into the system. On top of that it's not very helpful at saying why it doesn't work. I'm open to suggestions but like Joe I'm not a windows guy so my assistance is limited.

from plr.

So referencing our progress with getting PLR working on BigSQL's pg10 release, let's take a look at where things currently stand.

1. plr-8.3.0.17-pg9.6-R3.4.1-win64.zip on GitHub works great for EnterpriseDB's 9.6 release (extension can be created and PLR functions can execute).

2. This same release allows PLR to be installed on BigSQL's pg96 release, but it does not allow PLR functions to run (with the message: ERROR: could not access file "C:/POSTGR~1/bigsql/pg96/lib/postgresql/plr": No such file or directory) until plr.dll has been copied within the /lib/postgresql path as both plr.dll and also just plr.

3. @davecramer's plr.dll referenced above allows the PLR extension to be created and allows PLR functions to execute in BigSQL's pg96 release. I presume this also works for @AndreMikulec's plr.dll for pg10, though I haven't tested it.

4. @davecramer's plr.dll allows PLR to be installed on EnterpriseDB's 9.6 release, but it does not allow PLR functions to run (with the message: ERROR: could not access file "C:/Program Files/PostgreSQL/9.6/lib/plr": No such file or directory) until plr.dll has been copied within the /lib path as both plr.dll and also just plr.

So do we maintain that PLR's github repo only supports EnterpriseDB distributions (allowing the workaround copied plr.dll to solve problems for BigSQL users), or do we provide separate downloads for each?

from plr.

@sgennaria the github repo was intended only for EDB distros' but I'd like to make bigsql work with plr

from plr.

@AndreMikulec @birdwrangler @sgennaria I'm working on an appveyor setup which will allow us to build windows binaries automatically. I need help with the R.dump.csv file. You guys know windows better. I need an automated way to take that file and get the outputs we need to build. The appveyor environment has perl, power shell, unix tools, and Visual C 2013. Any help would be appreciated.

from plr.

I would certainly like to help. Are people trying copy/read a file called R.dump.csv back and forth? What do you have in mind?

from plr.

@AndreMikulec in compiling.md one of the steps is to use dumpbin /EXPORT R.dll > R.dump.csv

then this file has to provide just the symbol names IIRC so we need to cut lines out of the top and bottom and then remove some columns

from plr.

@davecramer
I think that I remember you showing me that in in a Github issue. Myself, I remember repeating the command in MSYS bash. But the command did not work, right away. I had to make a slight adjustment ( because not all shell (bash) programs are the same: versions and flavors ). If they let you put software on the machine? MSYS(MINGW) or MSYS2(MINGW64) would probably make you feel comfortable. Other possibilities may include Cygwin (Tried: works well ) and Windows Subsystem for Linux ( Have not tried ). Also Unix Tools exist: http://unxutils.sourceforge.net/. Note: I just tried Git Portable: ( https://sourceforge.net/projects/gitportable/?source=typ_redirect ). It has "cut" and "sed." That should be enough to horizontally an vertically partition a file. I just tried. Those commands 'can' be ran from a Windows Command shell. If software is not allowed to be put on the machine, then 'needed' software would have to be carried along an with the source code. Off the top of my head, I can not think of an simple direct way on Windows to 'easily' vertically and horizontally slice a file in batch/cmd. I do not know Powershell. Cscript ( old programming on environment over OLE ) may be available. This site tends to have may windows scripts: ( http://scripts.dragon-it.co.uk/scripts.nsf/ )

from plr.

@davecramer
"cut" and "sed" from Git Portable seems to work. The support "msys-1.0.dll" file is required.
The total size of the three files is 2MB.

C:\Users\CurrentUser\Downloads\MyDir>dir
 Volume in drive C is OS
 Volume Serial Number is AEF7-BCB0

 Directory of C:\Users\CurrentUser\Downloads\MyDir

10/31/2017  05:53 PM    <DIR>          .
10/31/2017  05:53 PM    <DIR>          ..
12/18/2014  11:57 PM            24,064 cut.exe
12/18/2014  11:03 PM           777,544 msys-1.0.dll
12/18/2014  11:57 PM         1,146,880 sed.exe
               3 File(s)      1,948,488 bytes
               2 Dir(s)  1,769,072,934,912 bytes free

No dependents are in the PATH.

C:\Users\CurrentUser\Downloads\MyDir>echo %PATH%
C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\

The programs run.
"cut"

C:\Users\CurrentUser\Downloads\MyDir>cut --version
cut (GNU textutils) 2.0
Written by David Ihnat, David MacKenzie, and Jim Meyering.

Copyright (C) 1999 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

"sed"

C:\Users\CurrentUser\Downloads\MyDir>sed --version
GNU sed version 4.2.1
Copyright (C) 2009 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE,
to the extent permitted by law.

GNU sed home page: <http://www.gnu.org/software/sed/>.
General help using GNU software: <http://www.gnu.org/gethelp/>.
E-mail bug reports to: <[email protected]>.
Be sure to include the word ``sed'' somewhere in the ``Subject:'' field.

from plr.

OK, cut, sed, awk, as well as python, perl so I think we are good to go

from plr.

Lack of response

from plr.