PostgreSQL integration with pg

pg_matlab是一个pg插件, 并非一般的过程语言. 使用pg_matlab创建的函数必须转换成c代码后, 编译并在数据库中使用共享库的方式调用.

pg_matlab下载和安装 :

wget http://www.cybertec.at/download/pg_matlab/pg_matlab-v2.tar.gz
tar -zxvf pg_matlab-v2.tar.gz
mv pg_matlab /opt/soft_bak/postgresql-9.3beta2/contrib/
[root@db-172-16-3-39 contrib]# cd /opt/soft_bak/postgresql-9.3beta2/contrib/pg_matlab/
[root@db-172-16-3-39 pg_matlab]# . /home/pg93/.bash_profile
root@db-172-16-3-39-> which pg_config
/opt/pgsql9.3beta2/bin/pg_config
root@db-172-16-3-39-> gmake clean
gmake clean -e -C matcored
gmake[1]: Entering directory `/opt/soft_bak/postgresql-9.3beta2/contrib/pg_matlab/matcored'
rm -f matcored.o mat_wrapper.o matcored
gmake[1]: Leaving directory `/opt/soft_bak/postgresql-9.3beta2/contrib/pg_matlab/matcored'
USE_PGXS=1 gmake clean -e -C libmatpg
gmake[1]: Entering directory `/opt/soft_bak/postgresql-9.3beta2/contrib/pg_matlab/libmatpg'
rm -f matpg.so   libmatpg.a  libmatpg.pc
rm -f create_mat_pg_operators.sql
rm -f mat_pg_funcs.o
gmake[1]: Leaving directory `/opt/soft_bak/postgresql-9.3beta2/contrib/pg_matlab/libmatpg'
root@db-172-16-3-39-> gmake
gmake -e -C matcored
gmake[1]: Entering directory `/opt/soft_bak/postgresql-9.3beta2/contrib/pg_matlab/matcored'
cc -Wall -c -o matcored.o matcored.c -I/extern/include -I../libmatcore/distrib
In file included from matcored.c:15:
../libmatcore/distrib/libmatcore.h:17:22: error: mclmcrrt.h: No such file or directory
In file included from matcored.c:15:
../libmatcore/distrib/libmatcore.h:63: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘MW_CALL_CONV’
../libmatcore/distrib/libmatcore.h:68: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘MW_CALL_CONV’
../libmatcore/distrib/libmatcore.h:71: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘libmatcoreTerminate’
../libmatcore/distrib/libmatcore.h:76: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘libmatcorePrintStackTrace’
../libmatcore/distrib/libmatcore.h:79: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘MW_CALL_CONV’
../libmatcore/distrib/libmatcore.h:82: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘libmatcoreGetMcrID’
../libmatcore/distrib/libmatcore.h:86: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘MW_CALL_CONV’
matcored.c: In function ‘send_matcored_response’:
matcored.c:60: warning: implicit declaration of function ‘strncpy’
matcored.c:60: warning: incompatible implicit declaration of built-in function ‘strncpy’
matcored.c:60: warning: implicit declaration of function ‘strlen’
matcored.c:60: warning: incompatible implicit declaration of built-in function ‘strlen’
matcored.c: In function ‘send_matcored_error’:
matcored.c:74: warning: incompatible implicit declaration of built-in function ‘strncpy’
matcored.c:74: warning: incompatible implicit declaration of built-in function ‘strlen’
matcored.c:81: warning: implicit declaration of function ‘memset’
matcored.c:81: warning: incompatible implicit declaration of built-in function ‘memset’
matcored.c: At top level:
matcored.c:99: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘read_int_param’
matcored.c: In function ‘send_int_param’:
matcored.c:141: warning: incompatible implicit declaration of built-in function ‘strncpy’
matcored.c:141: warning: incompatible implicit declaration of built-in function ‘strlen’
matcored.c: At top level:
matcored.c:150: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘read_binary_param’
matcored.c: In function ‘send_binary_param’:
matcored.c:186: warning: incompatible implicit declaration of built-in function ‘strncpy’
matcored.c:186: warning: incompatible implicit declaration of built-in function ‘strlen’
matcored.c: In function ‘init_matcore’:
matcored.c:197: warning: implicit declaration of function ‘libmatcoreInitialize’
matcored.c: In function ‘terminate_matcore’:
matcored.c:207: warning: implicit declaration of function ‘libmatcoreTerminate’
matcored.c: In function ‘add_vector’:
matcored.c:226: warning: implicit declaration of function ‘read_int_param’
matcored.c:231: warning: implicit declaration of function ‘read_binary_param’
matcored.c: In function ‘handle_client’:
matcored.c:276: warning: implicit declaration of function ‘strncmp’
matcored.c:276: warning: incompatible implicit declaration of built-in function ‘strlen’
matcored.c: In function ‘set_sighandler’:
matcored.c:346: warning: incompatible implicit declaration of built-in function ‘memset’
matcored.c: In function ‘listen_on_port’:
matcored.c:371: warning: incompatible implicit declaration of built-in function ‘memset’
matcored.c: In function ‘connect_on_port’:
matcored.c:435: warning: incompatible implicit declaration of built-in function ‘memset’
matcored.c:447: warning: incompatible implicit declaration of built-in function ‘strncpy’
matcored.c: In function ‘main’:
matcored.c:471: warning: implicit declaration of function ‘strcmp’
gmake[1]: *** [matcored.o] Error 1
gmake[1]: Leaving directory `/opt/soft_bak/postgresql-9.3beta2/contrib/pg_matlab/matcored'
gmake: *** [all] Error 2

错误来自libmatcore/distrib/libmatcore.h

/*
 * MATLAB Compiler: 4.13 (R2010a)
 * Date: Thu Feb 10 10:54:51 2011
 * Arguments: "-B" "macro_default" "-W" "lib:libmatcore" "-T" "link:lib" "-d"
 * "/home/babak/Downloads/pg_matlab/libmatcore/src" "-w"
 * "enable:specified_file_mismatch" "-w" "enable:repeated_file" "-w"
 * "enable:switch_ignored" "-w" "enable:missing_lib_sentinel" "-w"
 * "enable:demo_license" "-g" "-G" "-v" "/home/babak/Downloads/pg_matlab/add.m"
 */
#include "mclmcrrt.h"

头文件不存在, 因此在安装pg_matlab前需要先安装MCR.

unzip MCR_R2012a_glnxa64_installer.zip
修改输入文件文件
vi installer_input.txt
agreeToLicense=yes
product.MATLAB_Compiler

安装

./install -mode silent -inputFile ./installer_input.txt

安装正常的输出 :

添加到LD_LIBRARY_PATH
/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/runtime/glnxa64:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/bin/glnxa64:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/sys/os/glnxa64:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/sys/java/jre/glnxa64/jre/lib/amd64/native_threads:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/sys/java/jre/glnxa64/jre/lib/amd64/server:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/sys/java/jre/glnxa64/jre/lib/amd64
Next, set the XAPPLRESDIR environment variable to the following value:

/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/X11/app-defaults
(Sep 25, 2013 16:02:28) Exiting with status 0
(Sep 25, 2013 16:02:28) End - Successful.
Finished

mcr默认安装到这里来了

root@digoal-PowerEdge-R610-> ll /usr/local/MATLAB/MATLAB_Compiler_Runtime/v717
total 136K
drwxr-xr-x  5 root root 4.0K Sep 25 16:02 appdata
drwxr-xr-x  5 root root 4.0K Sep 25 16:02 bin
drwxr-xr-x  3 root root 4.0K Sep 25 16:02 etc
drwxr-xr-x  4 root root 4.0K Sep 25 16:02 extern
drwxr-xr-x  3 root root 4.0K Sep 25 16:02 help
drwxr-xr-x  4 root root 4.0K Sep 25 16:02 java
-r--r--r--  1 root root  72K Jan 15  2012 license.txt
-r--r--r--  1 root root 5.1K Dec 21  2011 MCR_license.txt
-r--r--r--  1 root root 3.9K Jan  5  2012 patents.txt
drwxr-xr-x 64 root root 4.0K Sep 25 16:02 resources
drwxr-xr-x  3 root root 4.0K Sep 25 16:02 runtime
drwxr-xr-x  3 root root 4.0K Sep 25 16:02 simulink
drwxr-xr-x  8 root root 4.0K Sep 25 16:02 sys
drwxr-xr-x 10 root root 4.0K Sep 25 16:02 toolbox
-r--r--r--  1 root root  248 Dec 26  2007 trademarks.txt
drwxr-xr-x  4 root root 4.0K Sep 25 16:02 X11

在安装pg_matlab前, 接下来要修改几个地方,

1. pg_matlab

root@digoal-PowerEdge-R610-> pwd
/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/matcored
root@digoal-PowerEdge-R610-> vi Makefile
修改
PGM_DIR = /opt/pgsql/bin
LIB_DIR = /opt/pgsql/lib
加一行
MATLABROOT=/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717

root@digoal-PowerEdge-R610-> pwd
/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab
root@digoal-PowerEdge-R610-> vi Makefile
修改
MATLABROOT=/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717

2. 修改环境变量

su - pg92
把安装mcr时建议的配置加上去
vi .bash_profile
export LD_LIBRARY_PATH=/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/runtime/glnxa64:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/bin/glnxa64:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/sys/os/glnxa64:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/sys/java/jre/glnxa64/jre/lib/amd64/native_threads:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/sys/java/jre/glnxa64/jre/lib/amd64/server:/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/sys/java/jre/glnxa64/jre/lib/amd64:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export XAPPLRESDIR=/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/X11/app-defaults

3. 安装pg_matlab

root@digoal-PowerEdge-R610-> make clean
make clean -e -C matcored
make[1]: Entering directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/matcored'
rm -f matcored.o mat_wrapper.o matcored
make[1]: Leaving directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/matcored'
USE_PGXS=1 make clean -e -C libmatpg
make[1]: Entering directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/libmatpg'
rm -f matpg.so   libmatpg.a
rm -f create_mat_pg_operators.sql
rm -f mat_pg_funcs.o
make[1]: Leaving directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/libmatpg'
root@digoal-PowerEdge-R610-> make
make -e -C matcored
make[1]: Entering directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/matcored'
cc -Wall -c -o matcored.o matcored.c -I/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/extern/include -I../libmatcore/distrib
cc -Wall -c -o mat_wrapper.o mat_wrapper.c -I/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/extern/include -I../libmatcore/distrib
gcc -o matcored matcored.o mat_wrapper.o -L/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/runtime/glnxa64 -L../libmatcore/distrib -lmwmclmcrrt -lmatcore
make[1]: Leaving directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/matcored'
USE_PGXS=1 make -e -C libmatpg
make[1]: Entering directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/libmatpg'
sed 's,MODULE_PATHNAME,$libdir/create_mat_pg_operators,g' create_mat_pg_operators.sql.in >create_mat_pg_operators.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -I../matcored -I. -I. -I/opt/pgsql9.2.4/include/server -I/opt/pgsql9.2.4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o mat_pg_funcs.o mat_pg_funcs.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -shared -o matpg.so mat_pg_funcs.o -L/opt/pgsql9.2.4/lib -L/usr/lib/x86_64-linux-gnu  -Wl,--as-needed -Wl,-rpath,'/opt/pgsql9.2.4/lib',--enable-new-dtags
make[1]: Leaving directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/libmatpg'
root@digoal-PowerEdge-R610-> make install
cp libmatcore/distrib/libmatcore.so /opt/pgsql/lib
cp matcored/matcored /opt/pgsql/bin
USE_PGXS=1 make install -e -C libmatpg
make[1]: Entering directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/libmatpg'
/bin/mkdir -p '/opt/pgsql9.2.4/lib'
/bin/mkdir -p '/opt/pgsql9.2.4/share/contrib'
/bin/sh /opt/pgsql9.2.4/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  matpg.so '/opt/pgsql9.2.4/lib/matpg.so'
/bin/sh /opt/pgsql9.2.4/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644  create_mat_pg_operators.sql '/opt/pgsql9.2.4/share/contrib/'
make[1]: Leaving directory `/data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/libmatpg'

4. 修改ld.so.conf

vi /etc/ld.so.conf
/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/bin/glnxa64
/usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/runtime/glnxa64
执行ldconfig, 同时检查是否生效
root@digoal-PowerEdge-R610-> ldconfig
root@digoal-PowerEdge-R610-> ldconfig -p|grep libmwmclmcrrt
        libmwmclmcrrt.so.7.17 (libc6,x86-64) => /usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/runtime/glnxa64/libmwmclmcrrt.so.7.17
        libmwmclmcrrt.so (libc6,x86-64) => /usr/local/MATLAB/MATLAB_Compiler_Runtime/v717/runtime/glnxa64/libmwmclmcrrt.so

启动matcored

root@digoal-PowerEdge-R610-> /opt/pgsql/bin/matcored 54321
pg92@digoal-PowerEdge-R610-> which matcored
/opt/pgsql/bin/matcored
启动matcored时指定监听端口54321
pg92@digoal-PowerEdge-R610-> matcored 54321
pg92@digoal-PowerEdge-R610-> ps -efw|grep matcored
pg92     13041     1  0 16:32 pts/1    00:00:00 matcored 54321
pg92     13043 12932  0 16:32 pts/1    00:00:00 grep --color=auto matcored
pg92@digoal-PowerEdge-R610-> netstat -anp|grep 54321
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:54321         0.0.0.0:*               LISTEN      13041/matcored

在数据库中创建函数

pg92@digoal-PowerEdge-R610-> cd /data01/soft_bak/postgresql-9.2.4/contrib/pg_matlab/
pg92@digoal-PowerEdge-R610-> cat create_mat_pg_operators.sql
-- + operator

CREATE OR REPLACE FUNCTION mat_pg_add(DOUBLE PRECISION[], DOUBLE PRECISION[]) RETURNS DOUBLE PRECISION[]
AS 'libmatpg.so', 'mat_pg_add'
LANGUAGE C STRICT IMMUTABLE;

DROP OPERATOR + (DOUBLE PRECISION[],DOUBLE PRECISION[]);
CREATE OPERATOR + (
        PROCEDURE = mat_pg_add,
        LEFTARG = DOUBLE PRECISION[],
        RIGHTARG = DOUBLE PRECISION[]
);

报错 :

pg92@digoal-PowerEdge-R610-> psql -f create_mat_pg_operators.sql
psql:create_mat_pg_operators.sql:5: ERROR:  could not access file "libmatpg.so": No such file or directory
psql:create_mat_pg_operators.sql:7: ERROR:  operator does not exist: double precision[] + double precision[]
psql:create_mat_pg_operators.sql:12: ERROR:  function mat_pg_add(double precision[], double precision[]) does not exist

这个动态库文件不存在

pg92@digoal-PowerEdge-R610-> ll /opt/pgsql/lib/libmatpg*
ls: cannot access /opt/pgsql/lib/libmatpg*: No such file or directory

解决办法 :

root@digoal-PowerEdge-R610-> cd /opt/pgsql/lib
root@digoal-PowerEdge-R610-> mv matpg.so libmatpg.so
pg92@digoal-PowerEdge-R610-> psql -f create_mat_pg_operators.sql
CREATE FUNCTION
psql:create_mat_pg_operators.sql:7: ERROR:  operator does not exist: double precision[] + double precision[]
CREATE OPERATOR

[安装说明]

How to compile, install and run
===============================

Before compilation, you need to set a few environment variables.

- PATH (if needed, if PostgreSQL is installed into a custom directory)
- MATLABROOT, so the Makefiles find the proper include and shared library files
- LD_LIBRARY_PATH or modify /etc/ld.so.conf so the libmatcore.so is found.

The main Makefile has the PGM_DIR and LIB_DIR, matcored and libmatcore.so
are installed there, respectively.

The C header file pg_matlab/libmatpg/mat_pg_funcs.h contains the hardcoded
port number (54321 by default) that the PostgreSQL module will connect to.

After setting all the above, run "make" from the main pg_matlab directory.
Then run "make install", you most likely need to be root to do this, but it
depends on the installation directories.

You need to install the SQL script into the database you want to use Matlab,
replace "dbname" with the proper name in the command below:

$ psql -f libmatpg/create_mat_pg_operators.sql dbname

When it's done, you need to run the installed "matcored" with the following
command:

$ matcored NNN

where NNN is the port number. Use the same port number as you used above.
You should use a special user for running matcored and this user must have
"ulimit -c 0" set, so the core dumps from the failing daemon children don't
fill up the disk.
vi /etc/security/limits.conf
* soft    core    unlimited
* hard    core    unlimited

When matcored is running, you can use commands like these in PostgreSQL
to add two vectors:

SELECT mat_pg_add( '{1,2,3,4}' , '{2,3,4,5}' );

or

SELECT '{1,2,3,4}'::float8[] + '{2,3,4,5}'::float8[];

[参考]

1. http://www.postgresql.org/message-id/7A0C0515-3896-4ABF-985A-09EB3B97AB3A@cybertec.at

2. http://www.cybertec.at/en/products/pg_matlab-matlab-postgresql-integration/

3. http://www.cybertec.at/en/products/pg_matlab-matlab-postgresql-integration/pg_matlab-sample-code/

4. http://www.cybertec.at/en/media/downloads/

5. http://download.csdn.net/detail/weizi4332/5581569

6. http://bbs.weiphone.com/read-htm-tid-6280022.html

7. https://kb.wisc.edu/cae/page.php?id=29336

8. http://thepiratebay.sx/torrent/8315857/

9. http://hi.baidu.com/chenyangh/item/e7b7ac192e70b0fcddeeca61

10. http://blog.csdn.net/rumswell/article/details/11881475