Postgres OnLine Journal: Jul 2017 - Dec 2017
An in-depth Exploration of the PostgreSQL Open Source Database
 

Table Of Contents

PL Programming
Using PostgreSQL Contribs
Application Development

PL Programming

 

PLV8 binaries for PostgreSQL 10 windows both 32-bit and 64-bit



Now that we are in midst of moving our databases and client databases to PostgreSQL 10, we started to build our favorite extensions. One popular one which several have asked when we'll have ready for PostgreSQL 10, is the plv8 extensions.

We now have version 1.4.10 for PostgreSQL 10 windows both the 32-bit and 64-bit. The 64-bit versions will work for PostgreSQL EDB Windows as well as the BigSQL distribution.

BigSQL windows 64 distributions doesn't currently come with PL/V8 (though the BigSQL Linux and OSX offerings do). These binaries work on BigSQL windows as well. Note: OpenSCG uses MingW64 chain to build BigSQL on windows as well and newer than what I use. So when copying, make sure not to overwrite files that came with BigSQL.

Listed below PL/V8 binaries built for PostgreSQL 10 for windows 64-bit and 32-bit. These I tested with both EDB PostgreSQL 10 windows (32/64-bit) installs and BigSQL 64-bit . These were built with PLV8 v1.4.10.

PL/V8 PostgreSQL 10 binaries

We hope windows users find these useful.

A number of folks have asked if I can compile them a newer version of PL/V8. Unfortunately PL/V8 from 1.5 onwards requires a newer version of V8 engine and I haven't had the time with other paying work to focus on working out the kinks of building the newer V8. Anyone who has tried to compile V8 probably has an idea what a mess that is. The Google V8 team seems to change their tooling chain which from the outside seems just arbitrary and basic fumbling I haven't had much success getting their newer chains to work against mingw64, my preferred windows development platform. I've been toying with the idea of trying to build at least the V8 part under Cygwin since V8 instructions seem to favor that.

Anyrate if people are willing to fund my numerous hours of development to crack this, I'd be willing to put aside other paying work to do this. If you are interested, just write to me PLV8 Fund. You should note how much you are willing to spend on the effort and if I have enough folks interested (I'm thinking about $2000 worth), I can start work and invoice everyone who pledged if I am successful. You should be willing to spend at least $100 to make the hassle of invoicing you worth my while.

Money will go toward building newer PL/V8 versions as well as more speedily getting out new binaries whenever new releases of PL/V8 come out.


Using PostgreSQL Contribs

 

http extension for windows 64 and 32-bit



For those folks on windows who want to do http gets and posts directly from your PostgreSQL server, we've made binaries for the http extension v1.2.2 release for PostgreSQL 10, 9.6, 9.5, and 9.4 Windows 64-bit.

These should work fine on both PostgreSQL EDB windows distributions as well as the BigSQL windows 64 distributions. Instructions in the ReadMe.txt for where to copy the files to for each distribution. Caution should be taken especially with BigSQL to not overwrite existing BigSQL files. Many of the files in this already exist in the BigSQL distribution since we both compile using the Mingw64 chain.

If you have PostGIS already installed, many of these files you will also already have since things like the libcurl and PCRE are also packaged with PostGIS.

Updated November 22nd, 2017 all packages updated to http extension v1.2.2 release

http extension binaries for PostgreSQL 10, 9.6, 9.5, and 9.4 windows 64-bit downloads

The curl library for http is built with SSL support and utilizes the ssleasy.dll packaged with the EDB and BigSQL installs.

http extension binaries for PostgreSQL 10, 9.6, 9.5, and 9.4 windows 32-bit downloads

http quick primer

To enable in a database after having installed the binaries.

CREATE EXTENSION http;

Do a basic get

SELECT h.content, h.content_type, hkv.value As dt
FROM http_get('http://postgis.net/tips/') AS h 
    LEFT JOIN LATERAL (SELECT *  
    FROM unnest(h.headers) 
        WHERE field =  'Date') AS hkv ON true;

Check out more examples at: https://github.com/pramsey/pgsql-http


Application Development

 

Installing pgTap in windows with msys2 and mingw64 Advanced



This weekend we spent sometime moving PostGIS/pgRouting windows buildbot Winnie to new hardware. Leo did the hardware and I handled installing and reconfiguring stuff. While I was at it, I upgraded to new Jenkins. Vicky Vergara has been bugging me to setup pgTap so she can run her pgRouting pgTap tests to make sure they work on windows. She's got 22488 tests. She just loves pgTap. Last time I tried installing pgTap I gave up, but I was in mood for experimentation so gave it another chance.

Main obstacle was installing pg_prove perl module which is commonly used with pgTap. I couldn't get this to install under msys cause it gave some sort of out of memory error and I hadn't migrated winnie to msys2 yet. For those unfamiliar msys is a 32-bit chain that gives you a Unix like toolbelt under windows. Msys2 is a much nicer new project that does what Msys did, but with a nice package manager called pacman and that has a 32-bit and 64-bit variant. To get the almost full unix experience under windows, you would couple this with mingw-w64 which is a much nicer set of gnu-compiles than the old mingw. I should add, getting a functioning unix shell and compilers, is much less painful than installing Visual Studio. Most everything is extract and go and copiable. so when I get a new workstation, I just copy over my folders, or copy stuff to Winnie.

For those interested in running with msys2. There is an msys2 for 64-bit and msys2 for 32-bit where as the regular old msys was just a 32-bit. I use 2 chains for building all my PostgreSQL extensions. msys2 (32-bit)/mingw-w64 32-bit chain and msys2 (64-bit)/mingw-w64 64-bit chain. Though it's not required, I like to have my 32-bits with my 32-bits and 64-bits with my 64-bit and keep them separate just in case I ever need to run on a 32-bit windows box.

Download a mingw-64 from https://mingw-w64.org. Extract somewhere and change msys2/ets/fstab to have lines something like:

# For a description of the file format, see the Users Guide
# http://cygwin.com/cygwin-ug-net/using.html#mount-table

# DO NOT REMOVE NEXT LINE. It remove cygdrive prefix from path
none / cygdrive binary,posix=0,noacl,user 0 0
c:/projects /projects
c:/projects/sources /sources
c:/ming64gcc48/mingw64 /mingw
c:/ming64gcc48/mingw64 /mingw64
#I use 32-bit cmake for building pgRouting. 
#Get from https://cmake.org/download/ since it has msys/mingw generator scripts
#one that you get from pacman does not.
C:/projects/cmake-3.7.2-win32-x86 /cmake

Launch msys batch script:

pacman -Syu
pacman --needed -S git
pacman --needed -S autoconf automake libtool make patch dos2unix
pacman --needed -S wget unzip bison

Now msys2 comes packaged with perl already, so to install pg_prove, you run this from the msys2 console.

cpan TAP::Parser::SourceHandler::pgTAP

To install pgTap, you just do the usual, like any other Unix/Linux user, after you have compiled PostgreSQL using msys2/mingw64.

export PGPATH=/projects/postgresql/rel/pg96w64gcc48
export PATH="${PGPATH}/bin:${PGPATH}/lib:${PATH}" 
export USE_PGXS=1

cd /projects/postgresql/extensions
rm -rf pgtap
git clone https://github.com/theory/pgtap.git pgtap
cd pgtap
git pull
make
make install

Then install in your db you will use for testing:

CREATE EXTENSION pgtap;

Note that pgTap extensions you build, can be copied over to a VC++ PostgreSQL compiled (no binaries for it, so just copy the pgtap* files from share/extension) and you can run against a PostgreSQL VC++ build even under msys2.

Vicky has tons of pgTap tests here launched with pg_prove_tests.sh.