Compiling PostgreSQL extensions with Visual Studio on Windows

I’ve seen a number of users struggling with building PostgreSQL extensions under Visual Studio, so I thought I’d see what’s involved in getting it working. The result is this tutorial, showing how to compile a simple extension with Visual Studio 2010 Express Edition.

Requirements

You will need a supported version of Visual Studio installed. These instructions refer to Visual Studio 2010 Express Edition. It is not necessary to use the same Visual Studio version as PostgreSQL was compiled with, or the same version I’m using here. You do need to make sure your Visual Studio version is supported by the release of PostgreSQL you’re targeting (or modify Configuration Properties -> General -> Platform Toolkit to use an older, supported toolkit). If you’re not using VS 2010, some details will of course differ.

To follow these instructions step by step you’ll also need a 32-bit PostgreSQL install. I’m using 64-bit Windows with a 32-bit Pg so all my paths have (x86) in them; if you’re on 32-bit you’ll need to change any references to Program Files (x86) to just Program Files, but nothing else will change.

If you’re trying to compile a 64-bit extension these instructions will work with minimal changes, but be aware that many free Visual Studio versions do not include a 64-bit toolchain. To test 64-bit builds I landed up installing the Windows SDK 7.1 plus the 64-bit toolchain patches for it, then selecting “WindowsSDK7.1″ under Configuration Properties -> General -> Platform Toolkit in project properties.

Bugs and caveats

I’ve only tested against PostgreSQL 9.3, both 32-bit and 64-bit versions, on Windows 7 with MS Visual Studio 2010. I used the v100 (default) toolchain for the 32-bit build, and the Windows SDK 7.1 toolchain for the 64-bit build due to lack of 64-bit compilers in 2010 Express Edition.

The 64-bit PostgreSQL installer is missing libintl.h. I’ve reported this and it’s being fixed. In the mean time you can download libintl.h and put it in PostgreSQL\9.3\include. Here’s the copy from the 32-bit installer in case you’re trying this before the 64-bit installer is fixed.

Step by step

Create a new Project (and new Solution if there isn’t one already) with File->New, Project. Use the Empty Project template. I’ll call my example DemoExtension.

Add the source file

Open the new project tree for DemoExtension. Right-click on “Source files” and choose “Add -> New Item”. Create a new C++ source file with your choice of name. Mine is DemoExtension.c. You must explicitly enter the file extension as .c; otherwise Visual Studio will compile the sources as C++, which won’t work with PostgreSQL.

02-sourcefile

Now paste the example extension code from the PostgreSQL documentation section “Version 1 Calling Conventions” – from the start includes and PG_MODULE_MAGIC to the end of the add_one function. Insert PGDLLEXPORT before Datum on the line before add_one.

#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

/* by value */
PG_FUNCTION_INFO_V1(add_one);

PGDLLEXPORT  /* Insert this line before every extension function */
Datum
add_one(PG_FUNCTION_ARGS)
{
    int32   arg = PG_GETARG_INT32(0);

    PG_RETURN_INT32(arg + 1);
}

Visual Studio doesn’t know where the headers are, so it’ll highlight most of the file with error markers. We need to tell it where PostgreSQL’s headers and libraries are before we can build the project – and make a few other changes while we’re at it.

Project configuration

Get properties on the DemoExtension project.

03-properties

In “Configuration” (top left), choose “All Configurations”.

04-allconfigurations

General settings

Under Configuration Properties -> General, set “Configuration Type” to “Dynamic Library (.dll)”.

Under C/C++ -> Preprocessor Directives, add the directive “WIN32″. PostgreSQL doesn’t recognise “WINDLL” as equivalent so we need to explicitly tell it when we’re on a win32 platform. This will be fixed in a future version.

Under C/C++ -> Code Generation, set “Enable C++ Exceptions” to “No”, and under “Advanced” set “Compile As” to “C Code”.

Under Linker -> Manifest File, set “Generate Manifest” to “No”.

Under Linker -> Input -> Additional Dependencies, add postgres.lib to the library list. Do not uncheck “Inherit from parent or project defaults”. Trivial extensions don’t require this, but if you link to any server functions you’ll require it.

Include and library directories

Now you need to add the required include directories.

In the properties dialog, go to Configuration Properties -> C/C++ -> General. In Additional Include Directories, pull down the arrow in the right of the textbox and choose “”. Now, by pasting paths or by folder selection, add the following folders inside your PostgreSQL install directory in this order:

include\server\port\win32_msvc
include\server\port\win32
include\server
include

On my system that looks like:

05additional-includes

You’ll also need to set the library path. Under “Linker”->”General”, in Additional Library Directories. In my case that’s C:\Program Files (x86)\PostgreSQL\9.3\lib. While you’re there, set Link Library Dependencies to No.

Click “OK”. The error highlights on your extension file should go away when you return to the source file.

Building the project

Choose “Release” from the pull-down menu.

06-releasebuild

Right-click on your project in the solution explorer and choose “Build”.

You should get no errors in the build output.

Installation and testing

At this point you must fish the DLL out of the project and copy it into PostgreSQL’s lib directory. In my case that means copying C:\Users\Craig\Documents\Visual Studio 2010\Projects\DemoExtension\Release\DemoExtension.dll to C:\Program Files (x86)\PostgreSQL\9.3\lib.

Since we have not defined the control file or setup file for an extension, we’ll just manually load the extension by connecting as user postgres or another superuser, and running:

CREATE FUNCTION add_one(integer) RETURNS integer
     AS 'DemoExtension', 'add_one'
     LANGUAGE C STRICT;

If all goes well, you can now use your brand new extension:

postgres=# CREATE FUNCTION add_one(integer) RETURNS integer
postgres-#      AS 'DemoExtension', 'add_one'
postgres-#      LANGUAGE C STRICT;
CREATE FUNCTION
postgres=# SELECT add_one(4);
 add_one
---------
       5
(1 row)

If you were to deploy your extension to others, you’d want to create a .control file and an extension SQL setup file, per the PostgreSQL documentation.

Related work

Shortly after finishing this, while searching for reports of users who’d been struggling with building extensions on Windows, I found Build PostgreSQL C Functions on Windows by Tim Child. I didn’t use his article as a resource when writing this, and thankfully a few things have improved since then. It looks like a good resource, so if you find yourself stuck take a look at it.

This Post Has 22 Comments

  1. KT says:

    Thank you very much for this useful post.
    And can you explain what is ‘PGDLLEXPORT’ and its aim? I don’t see it in the example of the doc. Second, is there any different between this compiling method and the method described at ‘http://wiki.postgresql.org/wiki/Building_and_Installing_PostgreSQL_Extension_Modules’? Why do we have to recompile the whole project source in order to compile an extention?
    And again, thank you! Happy New Year 2014.

    • craig.ringer says:

      See src/include/port/win32.h.

      #ifdef BUILDING_DLL
      #define PGDLLIMPORT __declspec (dllexport)
      #else /* not BUILDING_DLL */
      #define PGDLLIMPORT __declspec (dllimport)
      #endif

      BUILDING_DLL is defined only when compiling postgres.exe (by the build system). Yes, the name is confusing, it should really be called EXPORT_SYMBOLS or something.

      Unlike on UNIX platforms, Windows requires special magic annotations to correctly link symbols between shared libraries. If the compiler annotations are omitted, then inefficient methods of calling C functions are used, and exported variables will fail to link or link incorrectly.

      When compiling postgres.exe symbols marked PGDLLEXPORT get annotated with __declspec(dllexport). This tells the Microsoft Visual C++ compiler to export the symbol so it can be accessed from outside the DLL/EXE.

      When including PostgreSQL backend headers into an extension module, PGDLLEXPORT instead expands to __declspec(dllimport). This tells the Visual C++ compiler that this is a symbol from an external DLL, so it can link it correctly.

      To learn more about DLL linkage on Windows, see:

      Exporting from a DLL Using __declspec(dllexport)
      __declspec
      What does dllimport really mean?

      … and of course, Google.

      As for the second part of your question: The whole point of this blog post is to show you how to build an extension without recompiling all of PostgreSQL, targeting a binary PostgreSQL distribution you already have installed.

      The instructions you linked to only show you how to compile an extension as part of a whole PostgreSQL source code build on Windows. They’ll produce an extension compatible with the PostgreSQL you compiled – but not necessarily with the public distribution PostgreSQL binaries.

  2. Neil McGuigan says:

    NICE work Craig. Many thanks.

    Looks like you can make 64-bit DLLs with Visual Studio 2012 Express for Windows Desktop, without having to download the Windows SDK. Worked for me anyways.

    Line 2 of your c program has an error due to the < in <string.h>

    Also, order of includes is different in code than in image.

    Cheers.

    • craig.ringer says:

      Sure, the in-text code was updated a few times, and screenshots are a pain to re-do. The screenshot exists to illustrate the UI, not the code.

      Fixed the issue with <string.h>. Thanks.

  3. Apurva says:

    I am able to get as far as creating a dll (64 bit) file. I copy it to “C:\Program Files\PostgreSQL\9.1\lib” and run the following command:

    CREATE OR REPLACE FUNCTION add_one(INTEGER) RETURNS INTEGER
    AS ‘isInMesh’, ‘add_one’
    LANGUAGE C STRICT;

    I get the following error.

    ERROR: could not load library “C:/Program Files/PostgreSQL/9.1/lib/isInMesh.dll”: The specified module could not be found.

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

    ERROR: could not load library “C:/Program Files/PostgreSQL/9.1/lib/isInMesh.dll”: The specified module could not be found.
    SQL state: 58P01

    Not sure, I also tried giving absolute path creating an env variable in Windows 7 64 bit. I am building the dll on a different machine than the one that has postgres.

    Any suggestion?

    • craig.ringer says:

      Windows will report “somedll.dll: the specified module could not be found” when it really means “somedll.dll: A DDL required by this DLL was not found”. In other words, you may be missing a *dependency* DLL.

      Check using Dependency Walker – http://www.dependencywalker.com/ . Make sure you get the 32-bit version if you compiled 32-bit PostgreSQL (whether your host system is 32- or 64-bit). Get the 64-bit version if you’re on a 64-bit host and compiled a 64-bit postgres.

      • Apurva says:

        Thanks, my code is the same exact code for now that you provided in the example. Would that still have a dependency?

        • craig.ringer says:

          In that case, I suspect the dependency in question may well be the Microsoft Visual C++ runtime for the version of Visual C++ you used.

          Try installing the correct MSVC runtime redist on the target computer. E.g. if you compiled with MSVC 2010 SP1, you need to install the MSVC 2010 SP1 runtime redist on the target computer.

          Failing that, try what I said above re dependency walker, to see what is actually missing.

          • Apurva says:

            I build on the same machine, dependency walker looks good. But now I get a different error.

            ERROR: could not load library “C:/Program Files/PostgreSQL/9.1/lib/isInMesh.dll”: Access is denied.

          • craig.ringer says:

            That’s nothing to do with PostgreSQL. The security properties on the file don’t let PostgreSQL open it. You need to give the user PostgreSQL runs as the right to access the file.

            If you’re using the binary installer, PostgreSQL 9.2 and newer run as user NETWORKSERVICE, and 9.1 and older run as user “postgres”. But you can always give the “Everyone” user “Read” rights.

            It could also be a virus scanner getting in the way.

  4. Apurva says:

    I am using 9.1. I gave postgres and Everyone read rights. But I get same error. I also restarted and checked. Virus scanner is disabled.

  5. […] recommended it to me in this answer) on Windows XP 32bit with Visual Studio 2010 (I used this article as a manual). However, I got following […]

  6. Vincent says:

    Doing this with VS 2013 I get:
    Error 3 error C2375: ‘rint’ : redefinition; different linkage C:\Program Files\Microsoft Visual Studio 12.0\VC\include\math.h 515 1 Project1

    I did not find Preprocessor Directives in VS2008 or VS2013 I am guessing that it is the same as Preprocessor Definitions

  7. KP says:

    Great post Craig.
    I could get your example running. However, when I try to return a FLOAT using macro PG_RETURN_FLOAT4(). I am able to compile my code but NOT link. Linker gives the following error:
    error LNK2001: unresolved external symbol “unsigned int __cdecl Float4GetDatum(float)” (?Float4GetDatum@@YAIM@Z)

    Same code with PG_RETURN_INT32() works fine, I mean could compile and link and call from psql with no issue.

    • craig.ringer says:

      The key difference is that the PG_RETURN_FLOAT macro uses Float4GetDatum, which is C function.

      PG_RETURN_INT32 uses Int32GetDatum which is just a macro for SET_4_BYTES, which in turn is just a masked assignment. So it doesn’t call any C functions.

      At a guess, you’re not linking to postgres.lib . You’ll probably find that no other C function calls work either, and you’re using only macros elsewhere.

  8. Anna says:

    Hey Craig, thanks for this Blog.
    I have a question. i always get the error-message ”

    Mistake: could not find »C:/Program Files/PostgreSQL/9.1/lib/somedll.dll« could not load: %1 is not a valid Win32 application.
    SQL Status:58P01 ”

    What can i do here? Thanks for a replie & sorry for my english

    • craig.ringer says:

      At a guess you compiled a 32-bit binary and installed it in the 64-bit PostgreSQL. It’s hard to be sure without knowing exactly what steps you followed, how your project was configured, etc, but that’d be my first guess.

      You can check using dumpbin.exe from the SDK, and/or using Dependency Walker (depends.exe) from dependencywalker.com.

  9. Anna says:

    Hey Craig, it really works. Thank you so much. But i have a last question. Do you having the same warnings like me in the buildprocess?

    1>c:\program files\postgresql\9.1\include\server\pg_config_os.h(106): warning C4005: ‘EIDRM’ : macro redefinition

    1>C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\include\errno.h(103) : see previous definition of ‘EIDRM’

    Warnings like this. I have about 8.

    Greetings from Germany :)

  10. AdalWolf says:

    Your tutorial is a real blessing. I suffered with cygwin before discovering it. It is clear and appropriate. My dll works!!!
    Thank you!

Leave A Reply