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.
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. You should also add a prototype marked PGDLLEXPORT for the function so that MSVC knows to export its symbol.
#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
/* Add a prototype marked PGDLLEXPORT */
PGDLLEXPORT Datum add_one(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(add_one);
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.
In “Configuration” (top left), choose “All Configurations”.
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:
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.
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.
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.
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.
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.
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.
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?
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.
Thanks, my code is the same exact code for now that you provided in the example. Would that still have a dependency?
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.
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.
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.
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.
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
I haven’t tested with 2013 yet, and don’t expect to have time for a while. Odd issue, though.
Yes, Preprocessor Definitions.
Discussion with a colleague recently made it clear what this issue is. You have a prototype declared for your “rint” function, and that prototype doesn’t have PGDLLEXPORT. Then the definition does.
The prototype MUST be marked PGDLLEXPORT if it is present. The prototype is optional and may be omitted, as it was in the original version of the code above.
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)” ([email protected]@[email protected])
Same code with PG_RETURN_INT32() works fine, I mean could compile and link and call from psql with no issue.
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.
I’ve published an example extension too; see https://blog.2ndquadrant.com/pg_sysdatetime_windows_xplatform_postgresql_extension/
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
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.
I followed your instruction from step to step. Ok i will check it out 🙂
Hi. I got the same error, it is because you are using PostgeSQL x64 but you compiled your dll as Win32. So I figured out that, you just need to compile your project with x64 mode. Right-click on your solution from Solution Explorer -> Properties -> Configurations Properties. On “Platform” dropdown list, select “x64”. If it is not existing, try to create one. Click “Configuration Manager…” -> Active solution platform -> New …
In “Type or select the new platform”, Select “x64” -> OK.
Rebuild your solution and the new dll should work on PostgreSQL x64.
Thank @craig.ringer your your topic.
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 🙂
Your tutorial is a real blessing. I suffered with cygwin before discovering it. It is clear and appropriate. My dll works!!!
Thank you!
This was a great help, thanks!
Thanks for tutorial.
I try this with vs2012. It’s OK in Postgre 9.3
But when I use PostgreSQL 9.4 64bit , it said:
redefinition; different linkage when trying to compile a trigger function
I try to remove some line in the function declaration & its result as below:
— the original is:
PG_FUNCTION_INFO_V1(triggerName); PGDLLEXPORT
Datum triggerName(PG_FUNCTION_ARGS)
—
I try to remove PGDLLEXPORT, build successfully but then it could not find the function in dll file.
I try to remove PG_FUNCTION_INFO_V1(triggerName);, build successfully but then it throw error and stop the postgresql service each time trigger is called
Could you help me to solve this problem? Any reason or solution please. Thank you very much !
If you declare a prototype, the prototype MUST have PGDLLEXPORT.
This tutorial helped me to make an extension on Windows. Thanks for that. But can you help me to make an extension on linux?
I try to link but I get some error like this:
undefined reference to ‘pg_detoast_datum’
I link against pq.so. Do I need something else?
On Linux, you should use a pgxs Makefile, per the documentation: http://www.postgresql.org/docs/current/static/extend-pgxs.html
This article was necessary for Windows only because PGXS doesn’t support Visual Studio.
Thank you very much for this useful post. I need to compile k means PostgreSQL extention for windows. I tried the steps you mentioned but I am facing one error LNK1561: entry point must be defined. Could you please help me on this?
A quick search suggests that you’re trying to compile the project as a Win32 Application or Win32 Console Application, rather than as a DLL project.
This is fantastic. Worked right away and solved all my issues that I’d been laboring over for many hours. The description is so precise and skips over all unnecessary stuff. Thank you!
This is a great tutorial. I made it to 64bit without any issues with VS2013 premium.
I’m trying to adapt extension pg_qualstats for windows but I’m stuck.
In this extension, an error is raised if (!process_shared_preload_libraries_in_progress) and, even though I added pg_qualstats to shared_preload_libraries in the conf, it doesn’t work.
Is there something special I have to configure in the project file and/or the C file for this to work ?
Extensions that use shared_preload_libraries may require extra changes on Windows. Unlike other platforms, Windows doesn’t have fork() with copy-on-write shared process memory. Instead it exec’s backends and does some hoop jumping to make it look like there was a real fork. This is done with the EXEC_BACKEND compile mode.
In general I would expect an extension that uses shared_preload_libraries but doesn’t have special case code for EXEC_BACKEND not to work on Windows. I’m not certain, though, and can’t say for sure without a detailed reading of the pg_qualstats code.
Take a look at the examples in contrib/ for some of the existing extensions that use shared_preload_libraries to see how they handle EXEC_BACKEND and Windows. You may have to modify the extension to add Windows support, then submit a pull request or patch to the github project to get them to incorporate the changes.
I wrote my code with .cpg extension and generated a C code with ECPG compiler. Now i want to build a DLL (extension) with Visual Studio.
But i have link errors for some function:
1>sql_manager.obj : error LNK2001: unresolved external symbol _ECPGdeallocate
1>sql_manager.obj : error LNK2001: unresolved external symbol _ECPGprepare
1>sql_manager.obj : error LNK2001: unresolved external symbol _ECPGsetcommit
1>sql_manager.obj : error LNK2001: unresolved external symbol _ECPGdo
Can an ECPG program be compiled by Visual Studio?
I have fixed the problem: I just had to add the ECPG lib “libecpg.lib” here:
“Linker -> Input -> Additional Dependencies”
My DLL is generated.
I published the template I’m using personally.
It’s available on github : http://github.com/jmguazzo/pg_empty.
It’s ready for 32b and 64b build.
Hi Craig,
I am try to increase column name length which is more than 100 characters and i change it from C:\Program Files\PostgreSQL\9.4\include\server\pg_config_manual.h and restart the postgres server. but when i try to create a table with that length of column name then it truncate the length i.e. changes not affected. Please do the needful help ASAP.
Thanks in advvance.
Vikas Sangal
This has very little to do with the topic of this blog post. If you want general help with PostgreSQL please post on the pgsql-general mailing list, on Stack Overflow, or on dba.stackexchange.com.
If you need help ASAP, the best place to go is http://www.postgresql.org/support/professional_support/ .
BTW, you can’t change NAMEDATALEN without a re-initdb. If you don’t really know what you’re doing, so not touch those parameters.
If by any chance your module needs to refer to other DLL’s, make sure you add their corresponding directories to the system PATH under the System Properties window.
I am trying to do this for PostgreSQL 9.6.1 using Visual Studio 2015. I am still getting compilation errors after adding those source file paths. Please check my question in stackoverflow for more details and answer if you can. Thank you.
http://stackoverflow.com/questions/40344558/error-compiling-postgresql-9-6-1-extensions-with-visual-studio-2015
I followed the steps and all went weel for 32 bit DLL but when I switched to 64 bit I got a 10 errors:
Error 1 error LNK2019: unresolved external symbol errstart referenced in function get_hex C:\Users\victor\Documents\Visual Studio 2013\Projects\url_encode\Project1\url_encode.obj url_encode
Error 2 error LNK2019: unresolved external symbol errfinish referenced in function get_hex ….
So I tried to switch to another computer where I have a 64 bit postrges and I rebuild everything and got 1280 errors:
Error (active) identifier “int16” is undefined url_encode c:\Program Files\PostgreSQL\9.4\include\server\access\attnum.h 21
Error (active) identifier “Datum” is undefined url_encode c:\Program Files\PostgreSQL\9.4\include\server\fmgr.h 26
Error (active) expected a type specifier url_encode c:\Program Files\PostgreSQL\9.4\include\server\fmgr.h 40
….
What do I do wrong?
You must compile a 64-bit extension against a 64-bit PostgreSQL. Similarly 32-bit extensions must be built against 32-bit PostgreSQL.
I don’t know what’s going on with the errors on the other machine. Wrong include path or missing defines maybe.
hi craig.ringer this was a very useful post but iam having hard time using shared memory in postgres in windows .could you be of any help here .
thanks in advance
If you use PostgreSQL’s abstractions it works the same as on UNIX/Linux. See the examples in contrib/ . It should not be Windows specific.
Thank you very much, Craig!
It is a VERY useful post. I tried this on Visual Studio 2019 Community and Postgre SQL 10 (x64) Everything works fine.
In that case, you don’t need to include WIN32 to the Preprocessor Definitions section, and you ABSOLUTELY need this:
PGDLLEXPORT Datum your_function_name(PG_FUNCTION_ARGS);
foe EVERY function you want to make accessible from Postgre SQL.
Thankyou very much again!
This aricle saved me a lot of time.
hey how did you do that i would also like to make an extension to return the hostname of the server. But somehow it doesn’t work
Hi Craig,
Thank you for this wonderful tutorial. This sample worked pretty well. When I tried to compile another function “Make_Array”( from this link: https://www.postgresql.org/docs/12/xfunc-c.html#EXTEND-CPP) I am getting the following errors.
1>—— Rebuild All started: Project: DemoExtension1, Configuration: Release Win32 ——
1>C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\MSBuild\Microsoft\VC\v160\Microsoft.CppBuild.targets(467,5): warning MSB8028: The intermediate directory (Release\) contains files shared from another project (make_array1.vcxproj). This can lead to incorrect clean and rebuild behavior.
1>DemoExtension.c
1> Creating library C:\Users\deepan.kaviarasu\source\repos\DemoExtension1\Release\DemoExtension1.lib and object C:\Users\deepan.kaviarasu\source\repos\DemoExtension1\Release\DemoExtension1.exp
1>DemoExtension.obj : error LNK2001: unresolved external symbol _elog_finish
1>DemoExtension.obj : error LNK2001: unresolved external symbol _get_fn_expr_argtype
1>DemoExtension.obj : error LNK2001: unresolved external symbol _get_typlenbyvalalign
1>DemoExtension.obj : error LNK2001: unresolved external symbol _elog_start
1>DemoExtension.obj : error LNK2001: unresolved external symbol _construct_md_array
1>C:\Users\deepan.kaviarasu\source\repos\DemoExtension1\Release\DemoExtension1.dll : fatal error LNK1120: 5 unresolved externals
I am a newbie. Any Help is Appreciated.
Postgres Version: 12 64 Bit.
OS: Windows 10 64 Bit
Visual Studio: Community Version 2019
Thanks in Advance.
Looks like it’s not linking postgres.lib