PostgreSQLを入れてからパスワード認証で使えるようにしてC言語で関数を作って取り込むまで

C言語で実装した関数が使えないことが分かったので，ここに書き捨てることにする．
ついでに動作確認に手元へpsqlの環境を作ったので合わせてメモをしておく．

PostgreSQL

インストール

sudo apt-get install postgresql-9.5 postgresql-server-dev-9.5

設定

• パスワードを設定するsqlを走らせる．
  パスワードがコマンドラインヒストリに残るのが気にするなら，ファイルを作って実行した方がいいかも．下の例はのパスワードは`postgres'

  create_password.sql

  ALTER ROLE postgres WITH PASSWORD 'postgres';
  

  sudo su postgres -c 'psql < create_password.sql'
  

• パスワード認証するよう設定

  --- pg_hba.conf.orig
  +++ pg_hba.conf
  @@ -82,7 +82,7 @@
   # maintenance (custom daily cronjobs, replication, and similar tasks).
   #
   # Database administrative login by Unix domain socket
  -local   all             postgres                                peer
  +local   all             postgres                                md5
  
   # TYPE  DATABASE        USER            ADDRESS                 METHOD
  

• postgresqlの再起動

  sudo systemctl restart postgresql.service
  

• ログインできる

  psql -U postgres
  

関数

DB作る，TBL作る

-dオプションは-dをつけなくてもいいけど．

create_tbl.sql

DROP TABLE IF EXISTS _test;

CREATE TABLE _test (
    _value  REAL
);

sudo su postgres -c 'createdb testdb'
psql -U postgres -d testdb < create_test.sql

データを入れる

make_data.py

import numpy as np
from psycopg2 import connect

dbattr = {'user':'postgres',
        'password':'postgres',
        'host':'localhost',
        'dbname':'testdb'}

data = [[val] for val in map(float, np.random.normal(10,10,size=100))]
with connect(**dbattr) as con:
    cur = con.cursor()
    cur.executemany('INSERT INTO _test (_value) VALUES (%s)',data)
    con.commit()

関数実装してビルド

_idごとに_valueを合計して，和が大きい順に順位を返す関数を実装することにする．

mypos.c

#include "postgres.h"
#include "fmgr.h"

/* 値が
           <0   <-- -1 を返す
   0<= and <5   <--  0 を返す
   5<= and <10  <--  5 を返す
   10<=         <-- 10 を返す
   のいずれに該当するか判定して返す */

PG_FUNCTION_INFO_V1(mypos);

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern Datum mypos(PG_FUNCTION_ARGS);

/* 判定位置 */
int thresholds[3]   = {0, 5, 10};

/* 返り値リスト */
int returnvalues[4] = { -1, 0, 5, 10 };

Datum mypos(PG_FUNCTION_ARGS)
{
    float4 x = PG_GETARG_FLOAT4(0);

    int i;

    for (i=0; i<3; ++i)
        if (x<thresholds[i])
            break;

    PG_RETURN_INT32(returnvalues[i]);
}

Makefile

CC := gcc
CFLAGS := -shared -I /usr/include/postgresql/9.5/server -lpq -fPIC -O3

all: mypos.o

mypos.o: mypos.c
    $(CC) $(CFLAGS) -o $@ $^

clean:
    rm -f *.o

関数登録

mypos.sql

CREATE OR REPLACE FUNCTION mypos(REAL)
RETURNS INTEGER
AS '/tmp/mypos.o'
LANGUAGE C
WITH (isStrict);

make
psql -U postgres -d testdb < mypos.sql

使ってみる

psql -U postgres -d testdb -c 'SELECT _value, mypos(_value) from _test'

結果

  _value   | mypos 
-----------+-------
   24.7577 |    10
  -8.12542 |    -1
   3.29176 |     0
   10.6288 |    10
   5.76772 |     5
   8.46606 |     5
   8.84471 |     5
  -0.35494 |    -1
   25.6629 |    10
   11.2771 |    10
   6.81214 |     5
   4.78453 |     0
   5.88952 |     5
   25.6756 |    10
   3.30392 |     0
   1.73068 |     0
   1.61298 |     0
   27.7148 |    10
   4.05851 |     0
  0.154022 |     0
  -7.96923 |    -1
   21.3401 |    10
    8.5594 |     5
   16.3689 |    10
    14.203 |    10
     4.584 |     0
(stdin):