Cannot load oracle_fdw.dll under Windows Server 2012 R2

[CJohnsonM3]

I know this issue is going to sound very similar to other issues reported in the past, but I'm running out of ideas. Here are the particulars of my system:

• Windows Server 2012 R2 Standard (64-bit)

• PostgreSQL version() string (from the BigSQL installer PostgreSQL-9.6.1-1-win64-bigsql.exe) :
  version | PostgreSQL 9.6.1 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit

• PostgreSQL installed at F:\PostgreSQL\pg96

• Oracle Client 12.2.0.1 64bit (not the Instant Client, although I tried that first), installed at F:\oracle\product\12.2.0.2 (yes, the last digit of the directory does not match the patch level)

• oracle_fdw 1.5.0 from download file "oracle_fdw-1.5.0-pg96-win64.zip"

• WIndows System PATH: F:\oracle\product\12.2.0.2\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0;F:\PostgreSQL\pg96\bin

• Windows System ORACLE_HOME=F:\oracle\product\12.2.0.2

I copied the oracle_fdw.dll file to F:\PostgreSQL\pg96\lib\postgresql, where other extension DLLs appear to live, and copied the *.sql and oracle_fdw.control to F:\PostgreSQL\pg96\share\postgresql\extension, where other extension SQL and CONTROL files appear to live.

The extension is visible from the "pg_available_extensions" view.

After configuring the environment, I logged out and back in (but did not reboot the server). Then I restarted the Postgres service so it would pick up the PATH changes.

The Postgres service is running as the "Local System" account. The necessary directories and files are all readable/writable to SYSTEM.

When I try to create the extension from PSQL, I get the following error:

dbname=# CREATE EXTENSION oracle_fdw;
ERROR: could not load library "F:/PostgreSQL/pg96/../pg96/lib/postgresql/oracle_fdw.dll": The specified module could not be found.

I've read other postings about this error and ensured my PATH was correct. Could the fact that PostgreSQL is 9.6.1 instead of 9.6.0 be causing the problem? I did not compile my Postgres from source since I don't have a development environment.

Thank you for your help!

[chrullrich]

Do you have the Visual C++ 2013 redistributable package installed? Your PostgreSQL appears to be built with MinGW (based on looking at their 9.6.2 installer as well as your info above), so it (ab)uses a system library instead, but oracle_fdw needs the VC++ runtime.

The Windows error message is unfortunately silent on whether the failing DLL itself or one of its dependencies is missing.

@laurenz, the "Installation Requirements" don't mention this.

[CJohnsonM3]

@chrullrich,

Thank you for the info. I downloaded the Visual C++ 2013 Redistributable package from Microsoft, and it prompted me to "Repair" or "Remove", indicating that it was already installed. There must be something else missing...

[chrullrich]

First, check if the redistributable package is actually installed. Does the file C:\Windows\system32\msvcr120.dll exist? If it does, make sure you are looking at the 64-bit system32 directory by looking for C:\Windows\sysnative; this directory should not exist.

Then, use Dependency Walker (http://www.dependencywalker.com/) on oracle_fdw.dll to see whether it can find its dependencies. It will report plenty of errors, but only the direct dependencies in the upper left pane are relevant. Use the 64-bit version to avoid false positives.

Then, run regsvr32 oracle_fdw.dll. You will get one of two error messages. If it is "DllRegisterServer not found", your PATH is good and the PostgreSQL service probably has not picked it up, if it is similar to what PostgreSQL tells you, the PATH is still incomplete as configured.

Then, use Sysinternals Process Explorer (https://live.sysinternals.com/procexp.exe) to check the environment of (any of) the postgres processes to see whether they are actually using the changed PATH. I agree that restarting the service should be enough to refresh its environment, but if you find that the path has not updated, you may have to reboot the server after all.

Other than that, I'm out of ideas as well; perhaps your server has software restriction policies applied in group policy?

[CJohnsonM3]

Thank you, @chrullrich, for all of your help. I think I figured out the problem, using the Process Explorer app you linked to above. When I looked at the Environment of the Postgres process, the PATH variable did not contain the edits I made to the System PATH, particularly the path to the Oracle client libraries. It turns out the PATH for the Postgres Windows Service is set via a script that in my installation is located at F:\PostgreSQL\pg96\pg96-env.bat . This batch file looks like this:

@echo off
set PGHOME=F:\PostgreSQL\pg96
set PGDATA=G:\PostgreSQL\data\pg96
set PATH=F:\PostgreSQL\pg96\bin;%PATH%
set PGUSER=postgres
set PGDATABASE=postgres
set PGPORT=5432
set PGPASSFILE=C:\Users\cjohnson\AppData\Roaming\postgresql\pgpass.conf
set PYTHONPATH=F:\PostgreSQL\pg96\python\site-packages

I edited the PATH and added the ORACLE_HOME variable in this file and restarted the service, then verified via Process Explorer that the PATH contained the Oracle client location, and that the ORACLE_HOME variable showed up. I went back to PSQL and ran the "CREATE EXTENSION ora_fdw" command, and it completed successfully. Unfortunately I must wait for some networking issues to get cleared up before I can connect to the Oracle server, but my DLL loading issue appears to be solved.

Thanks again, @chrullrich, for your help.