Porting a PostgreSQL Extension from Unix to Windows 10
2ndQuadrant has written before about how to build your own extension for Windows. This time we take a look at what it takes to port one written by someone else. I am building this for PostgreSQL 11 on Windows using Visual Studio 2019 and clang.
Here we are working with the hashtypes extension. Unfortunately, at the time of writing, version 0.1.5 has not been released with the support for PostgreSQL 11. So we build one from git.
git clone https://github.com/adjust/hashtypes.git
The first thing we do is to take a look at the C source files. There are only four of them, common.c
, crc32.c
, md5.c
, and sha.c
so we can be reasonably sure the porting effort will be quick.
Then we use the directions from 2ndQuadrant to build our own makefile, called windows.mak
. We start by setting the compiler and PostgreSQL directory in variables. Here we are using clang
to compile so the flags will be familiar to Unix programmers but alien to those used to cl
.
## You will need to change these two settings for your own system. CC="C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\Llvm\8.0.0\bin\clang.exe" POSTGRES="C:\Program Files\PostgreSQL\11"
Then we set the compilation flags in CFLAGS
.
CFLAGS=-m64 -Wall -Wextra -O3 \ -I$(POSTGRES)\include\server\port\win32_msvc \ -I$(POSTGRES)\include\server\port\win32 \ -I$(POSTGRES)\include\server \ -I$(POSTGRES)\include
The CFLAGS
are set to build 64bit binaries with -m64
then we add the usual warnings and some extra warnings for extra credit with -Wall
and -Wextra
. Then since we’re compiling production code we add optimization with -O3
. The various -I
flags are for include directories and we take their names directly from the tutorial by 2ndQuadrant.
The link flags are easy, we just add the PostgreSQL lib
directory with -L
and link with postgres
with -l
.
LDFLAGS=-L$(POSTGRES)\lib -lpostgres
Now we can start our make rules. We make a generic rule for the object files.
.c.obj: $(CC) $(CFLAGS) -c $< -o$@
There is nothing strange here, -c
tells the compiler to compile only, not link and -o
names the output file. The $<
and $@
are the usual make macros.
Now we’re at a point where we can attempt to make a link rule for the DLL file. Here -shared
tells the compiler to make a DLL file. We try,
src\hashtypes.dll: src\common.obj src\crc32.obj src\md5.obj src\sha.obj $(CC) $(CFLAGS) -shared $** $(LDFLAGS) -o$@
and when we run nmake -f windows.mak
we get an error.
src\crc32.c:38:10: error: use of undeclared identifier 'uint32_t'
Which is easy to fix. We just add #include <stdint.h>
at the top of the file.
Now crc32.c
compiles, but we get an error when we try to compile sha.c
.
src\sha.c:22:2: error: No digest length defined #error No digest length defined ^ src\sha.c:25:2: error: No algorithm name defined #error No algorithm name defined ^
Something strange is going on here. With a closer look at the source, we find this.
#ifndef SHA_LENGTH #error No digest length defined #endif #ifndef SHA_NAME #error No algorithm name defined #endif
So we need to figure out how to properly define these values. We find them in the PGXS makefile.
src/sha1.o: CFLAGS+=-DSHA_NAME=1 -DSHA_LENGTH=20 src/sha224.o: CFLAGS+=-DSHA_NAME=224 -DSHA_LENGTH=28 src/sha256.o: CFLAGS+=-DSHA_NAME=256 -DSHA_LENGTH=32 src/sha384.o: CFLAGS+=-DSHA_NAME=384 -DSHA_LENGTH=48 src/sha512.o: CFLAGS+=-DSHA_NAME=512 -DSHA_LENGTH=64
But we can’t just copy this method, because as far as I know, Microsoft’s nmake
doesn’t support addition to CFLAGS
like this.
We need to make separate rules for each file, which we can do with the following.
src\sha1.obj: src\sha.c $(CC) $(CFLAGS) -c src\sha.c -o$@ -DSHA_NAME=1 -DSHA_LENGTH=20 src\sha224.obj: src\sha.c $(CC) $(CFLAGS) -c src\sha.c -o$@ -DSHA_NAME=224 -DSHA_LENGTH=28 src\sha256.obj: src\sha.c $(CC) $(CFLAGS) -c src\sha.c -o$@ -DSHA_NAME=256 -DSHA_LENGTH=32 src\sha384.obj: src\sha.c $(CC) $(CFLAGS) -c src\sha.c -o$@ -DSHA_NAME=384 -DSHA_LENGTH=48 src\sha512.obj: src\sha.c $(CC) $(CFLAGS) -c src\sha.c -o$@ -DSHA_NAME=512 -DSHA_LENGTH=64
And change our link rule to this.
src\hashtypes.dll: src\common.obj src\crc32.obj src\md5.obj src\sha1.obj \ src\sha224.obj src\sha256.obj src\sha384.obj src\sha512.obj $(CC) $(CFLAGS) -shared $** $(LDFLAGS) -o$@
Now we have a successfully built a DLL file, so it’s time to try and install it. To do that we use the following installation rule. Because we’re only building for PostgreSQL 11, for the moment, and the earlier versions of hashtypes doesn’t work with it, we only install one of the SQL files. This rule typically must be run as an Administrator, so open an x64 Native Tools Command Prompt for VS 2019 as an Administrator and run nmake -f windows.mak install
.
install: copy src\hashtypes.dll $(POSTGRES)\lib copy hashtypes.control $(POSTGRES)\share\extension copy sql\hashtypes--0.1.5.sql $(POSTGRES)\share\extension
Now we can create the extension in Postgres. So turn to your psql
prompt or whatever you use to talk to the database and run
create extension hashtypes;
as the database superuser, typically postgres
.
This returns an error,
ERROR: could not find function "sha_in1" in file "C:/Program Files/PostgreSQL/11/lib/hashtypes.dll"
and that means the PGDLLEXPORT
is missing from the source files. Instead of retracing all my steps here, I’ll just list the amended prototypes in order.
src\crc32.c
PGDLLEXPORT Datum crc32_in(PG_FUNCTION_ARGS); PGDLLEXPORT Datum crc32_out(PG_FUNCTION_ARGS);
src\md5.c
PGDLLEXPORT Datum md5_in(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_out(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_recv(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_send(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_to_text(PG_FUNCTION_ARGS); PGDLLEXPORT Datum text_to_md5(PG_FUNCTION_ARGS); PGDLLEXPORT Datum bytea_to_md5(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_to_bytea(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_cmp(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_eq(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_ne(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_ge(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_gt(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_le(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_lt(PG_FUNCTION_ARGS); PGDLLEXPORT Datum md5_hash(PG_FUNCTION_ARGS);
src\sha.c
PGDLLEXPORT Datum input_function(PG_FUNCTION_ARGS); PGDLLEXPORT Datum output_function(PG_FUNCTION_ARGS); PGDLLEXPORT Datum sha_to_text_fn(PG_FUNCTION_ARGS); PGDLLEXPORT Datum text_to_sha_fn(PG_FUNCTION_ARGS); PGDLLEXPORT Datum bytea_to_sha_fn(PG_FUNCTION_ARGS); PGDLLEXPORT Datum sha_to_bytea_fn(PG_FUNCTION_ARGS); PGDLLEXPORT Datum cmp_function(PG_FUNCTION_ARGS); PGDLLEXPORT Datum equal_function(PG_FUNCTION_ARGS); PGDLLEXPORT Datum notequal_function(PG_FUNCTION_ARGS); PGDLLEXPORT Datum ge_function(PG_FUNCTION_ARGS); PGDLLEXPORT Datum gt_function(PG_FUNCTION_ARGS); PGDLLEXPORT Datum le_function(PG_FUNCTION_ARGS); PGDLLEXPORT Datum lt_function(PG_FUNCTION_ARGS); PGDLLEXPORT Datum hash_function(PG_FUNCTION_ARGS);
Now we can build and install again with nmake -f windows.mak
and nmake -f windows.mak install
.
This time create extension hashtypes;
succeeds, and the porting effort is done.
Pull Request
Update. There is now a pull request for the Windows port.
Troubleshooting
If you find yourself repeatedly installing the same extension and get the following error, it means it’s currently loaded by some PostgreSQL process.
copy src\hashtypes.dll "C:\Program Files\PostgreSQL\11"\lib The process cannot access the file because it is being used by another process. 0 file(s) copied. NMAKE : fatal error U1077: 'copy' : return code '0x1' Stop.
If you’re using psql
you can do \c
to reconnect to the database, and the new process typically hasn’t loaded the extension just yet. Now you can run the copy command again.
Support
If you find yourself in need of a support for an extension you’re porting, you should first turn to your support company. If you don’t have a support company yet, turn to the PostgreSQL list of support companies.
You can also send mail to the PostgreSQL General mailing list.
As a last resort, write to johann@myrkraverk.com.