Postgresql – Problems while building plpgsql_check on Windows

installationpostgresqlvisual studio 2010windows

I try to build plpgsql_check extension by @PavelStehule (Erwin Brandstetter 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 errors:

error LNK2001: unresolved external symbol _exec_get_datum_type
error LNK2001: unresolved external symbol _plpgsql_compile
error LNK2001: unresolved external symbol _plpgsql_build_datatype
error LNK2001: unresolved external symbol _plpgsql_stmt_typename
error LNK2001: unresolved external symbol _plpgsql_parser_setup

In addition, there are two IntelliSense errors: expected a ')' in plpgsql.h (lines 923 and 953 – both in datum-related functions) – I expect these to be false alerts (if they are important, how to solve them?).

After some googling I found that someone had a similar problem on OS X. The problem was that plpgsql.so either wasn't linked or didn't work. I have no plpgsql.so on my computer, but I have plpgsql.dll which should correspond to it. So I linked it and the error changed:

error LNK1107: invalid or corrupt file: cannot read at 0x2A8

This dynamic library is part of the PostgreSQL distribution, so it shouldn't be corrupt. However, I'm afraid that what Pavel wrote in the linked discussion applies here:

It needs link against plpgsql.so. I didn't test it against clang – I
remember a talk with Tom Lane, he was surprised so this code works and
probably on some platforms can be unavailable.

…

Nobody expected so plpgsql will be used from other extensions – and
environment is not prepared for it 🙁

The worst case would be that plpgsql checker extension is simply not available under Windows, because it is too low-mana area for such a mighty magic. 🙂 If I just need a different approach, please point me to it.

Best Answer

You can find Adam Bartoszewicz's instructions here on Google Groups (also published on Pavel Stehule's blog):

Download and install PostgreSQL 9.3.4 for Win32 from http://www.enterprisedb.com

Download and install Microsoft Visual C++ 2010 Express

Lern tutorial http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows

The plpgsql_check depends on plpgsql and we need to add plpgsql.lib to the library list. Unfortunately PostgreSQL 9.4.3 does not contain this library.

Create a plpgsql.lib from plpgsql.dll as described in http://adrianhenke.wordpress.com/2008/12/05/create-lib-file-from-dll

Change plpgsql_check.c file, add PGDLLEXPORT line before evry extension function, as described in http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows
...
PGDLLEXPORT Datum plpgsql_check_function_tb(PG_FUNCTION_ARGS); PGDLLEXPORT Datum plpgsql_check_function(PG_FUNCTION_ARGS);
...
PGDLLEXPORT Datum plpgsql_check_function(PG_FUNCTION_ARGS) { Oid funcoid = PG_GETARG_OID(0);
...
PGDLLEXPORT Datum plpgsql_check_function_tb(PG_FUNCTION_ARGS) { Oid funcoid = PG_GETARG_OID(0);
...

Build plpgsql_check.dll

Install plugin

copy plpgsql_check.dll to PostgreSQL\9.3\lib

copy plpgsql_check.control and plpgsql_check--0.8.sql to PostgreSQL\9.3\share\extension

Related Solutions

MySQL – Installing Default Tables in Windows

I knew I had faced this before and managed to figure it out, so I searched my drives and found a batch file I had written last time.

For anyone else who experiences this issue, what you need to do is to run the server daemon with the --bootstrap parameters.

Here is the script I used to dump all the databases out to an SQL file and re-import them (that is, manually—and inconveniently—implementing a MySQL counterpart to SQLite’s vacuum command).

::Start the server, change to the MySQL bin directory, and dump all databases
net start MySQL
cd       /d "%serverdir%\MySQL\bin"
mysqldump  -uroot -p --all-tablespaces --all-databases --add-drop-database --add-drop-table --add-locks --comments --complete-insert --compress --create-options --events --routines --quick --quote-names --set-charset --triggers > %temp%\all.sql

::Stop the server and rename the data directory (as backup)
net stop mysql
ren         "%datadir%\MySQL"        MySQL_

::Delete data folder in MySQL directory, optionally copy old mysql tables
rd    /s /q "%serverdir%\MySQL\data"
md          "%serverdir%\MySQL\data"
xcopy /s /e "%datadir%\MySQL_\mysql" "%serverdir%\MySQL\data"

::Bootstrap the server (creates grant tables if they don’t exist)
mysqld --bootstrap

::Run server, optionally upgrade tables and move upgrade log to data directory
start mysqld --skip-grant-tables
mysql_upgrade --force
move "%serverdir%\MySQL\bin\mysql_upgrade_info" "%serverdir%\MySQL\data"

::Import all databases, shutdown, delete logs, then move to old data dir
mysql      -uroot -p < %temp%\all.sql
mysqladmin -uroot -p shutdown
md  "%datadir%\MySQL"
del "%serverdir%\MySQL\data\ib_logfile?"
xcopy /s /e "%serverdir%\MySQL\data\*" "%datadir%\MySQL"
rd    /s /q "%serverdir%\MySQL\data"

::Start the server, if it works, then all should be well, so del
net start mysql
rd    /s /q "%datadir%\MySQL_"

Postgresql – Problems connecting to Windows-based Postgresql

Here are the steps of troubleshooting I would recommend:

From the local machine try the following. If they work you know the issue is not PostgreSQL. If they don't work then you know your issue is local to the machine.
- psql -h localhost -p 5433
- psql -h [external ip] -p 5433
- If the latter fails, try temporarily disabling all firewalls and trying again
Now if that works the next step is to troubleshoot the network. Traceroute may be helpful here. Also try connecting to any other open ports using other software and make sure that works. From there, you may be able to figure out exactly where the packets are being dropped.

Best Answer

Related Solutions

MySQL – Installing Default Tables in Windows

Postgresql – Problems connecting to Windows-based Postgresql

Related Question