ラベル postgresql の投稿を表示しています。 すべての投稿を表示
ラベル postgresql の投稿を表示しています。 すべての投稿を表示

2017年2月15日水曜日

plpgsql 全角半角変換

そういえば、こういうの書いたなーと思って… PostgreSQL で、全角と半角の変換を行うストアドプロシジャです。
CREATE FUNCTION kanahan2zen(strhan text) RETURNS text
    AS $_$DECLARE
strHan ALIAS FOR $1;
str TEXT;
i integer;

-- 通常カナ(半角2バイト)
--tZen2 VARCHAR[] := ARRAY[ "ガ" ];
tZen2 VARCHAR[] := ARRAY['ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ', 'ヴ'];
--tHan2 VARCHAR[] := ARRAY[ "ガ" ];
tHan2 VARCHAR[] := ARRAY['ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ', 'ヴ'];

BEGIN

str := strHan;
-- 半角2バイトの変換
for i in 1..26 loop
str := replace(str, tHan2[i], tZen2[i]);
end loop;

-- 半角1バイトの変換
str := translate(upper(str)
, 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョワイエカケー、。・」「゙ ,<.>/?_}]*:+;{[~@|\`^=-)(&%$#""!'
, 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョヮヰヱヵヶー、。・」「゛ ,<.>/?_}]*:+;{[ ̄@|¥`^=-)(&%$#”!');

return str;

END;
$_$
    LANGUAGE plpgsql;




--
-- TOC entry 26 (class 1255 OID 18986)
-- Dependencies: 5 590
-- Name: kanazen2han(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION kanazen2han(strzen text) RETURNS text
    AS $_$DECLARE
strZen ALIAS FOR $1;
str TEXT;
i integer;

-- 通常カナ(半角2バイト)
--tZen2 VARCHAR[] := ARRAY[ "ガ" ];
tZen2 VARCHAR[] := ARRAY['ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ', 'ヴ'];
--tHan2 VARCHAR[] := ARRAY[ "ガ" ];
tHan2 VARCHAR[] := ARRAY['ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ', 'ヴ'];

BEGIN

str := strZen;
-- 半角2バイトの変換
for i in 1..26 loop
str := replace(str, tZen2[i], tHan2[i]);
end loop;

-- 半角1バイトの変換
str := translate(upper(str)
, 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョヮヰヱヵヶー、。・」「゛ ,<.>/?_}]*:+;{[ ̄@|¥`^=-)(&%$#”!'
, 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョワイエカケー、。・」「゙ ,<.>/?_}]*:+;{[~@|\`^=-)(&%$#""!');

return str;

END;
$_$
    LANGUAGE plpgsql;

2016年1月19日火曜日

ウィンドウズのバッチファイルで曜日を取得する

参考:バッチで日付の操作 Linux でシェルスクリプトを書くよりも、Windows のバッチファイルは曲者で苦労します。 今回は、定期で PostgreSQL のバックアップファイルをとりたいので、曜日でローテションする事を考えました。
@echo off
REM 日付の曜日を求める
REM 日付はYYYY/MM/DD 形式とする。結果は環境変数 yobi へ返す。
REM 引数がないときは本日とする。
if "%1"=="" (  
    set orgdate=%date:~-10,10%
) else (
    set orgdate=%1
)

:年月日の分割
set yy=%orgdate:~-10,4%
set mm=%orgdate:~-5,2%
set dd=%orgdate:~-2,2%
set dayfile=%yy%%mm%%dd%

:月日の数値化(8進数対策)
set /a mm=1%mm%-100
set /a dd=1%dd%-100
if "%mm%" LEQ "2" (set /a yy=yy-1&&set /a mm=mm+12)
set /a ans=yy + yy/4 - yy/100 + yy/400 + (13*mm+8)/5 + dd
set /a wday=ans %% 7
if %wday%==0 (
 set youbi=日曜日
 set syoubi=日
) else if %wday%==1 (
 set youbi=月曜日
 set syoubi=月
) else if %wday%==2 (
 set youbi=火曜日
 set syoubi=火
) else if %wday%==3 (
 set youbi=水曜日
 set syoubi=水
) else if %wday%==4 (
 set youbi=木曜日
 set syoubi=木
) else if %wday%==5 (
 set youbi=金曜日
 set syoubi=金
) else if %wday%==6 (
 set youbi=土曜日
 set syoubi=土
)
:echo %wday%
:echo %youbi%
バックアップは、こんな感じ
REM 環境変数のスコープをバッチ内に限定
Setlocal
set PGPASSWORD=%1%
set CURPATH=%~dp0
REM PostgreSQL インストール・ディレクトリ
set PGPATH=C:\Program Files\PostgreSQL\9.4\bin
REM バックアップ先ディレクトリ
set BKDIR=C:\DatabaseBackup
call %CURPATH%youbi.bat
REM mydb のバックアップを取る
"%PGPATH%\pg_dump.exe" -h localhost -p 5432 -U postgres -Fc -v -f%BKDIR%\%youbi%backup.dmp mydb 2> %BKDIR%\%youbi%backup.log
:"%PGPATH%\pg_dump.exe" -h localhost -p 5432 -U postgres -Fc -v -f%BKDIR%\%dayfile%backup.dmp mydb 2> %BKDIR%\%dayfile%backup.log
古いファイルを削除する手段がわかった
set BKDIR=C:\DatabaseBackup
forfiles /p %BKDIR% /m "*.dmp" /d -30 /c "cmd /c del /Q @file"
forfiles /p %BKDIR% /m "*.log" /d -30 /c "cmd /c del /Q @file"

2015年12月15日火曜日

Windows版 PostGIS x64版の shp2pgsql が動かない

PostgreSQL は、x86 版のサーバを使用するよりも x64 版のサーバを使った方が良いんじゃないかなー?と思って、x64版をインストールしたんですわ。

そしたら、shp2pgsql が 0xc000007b とかエラー吐いて動かないとか言われました。
ググってみたら、この問題は、もう、かれこれ3年以上前から放置されています。
しかも回答者の認識も「 libiconv-2 は windows では使われてないから無くても大丈夫あるよ」てな感じで、脱力感が半端ない。
その他、あちこちで、この問題に関して質問されているけども、ほぼ放置プレー。

はいはい、またかよっ。

って、事で libiconv-2.dll の x64 版を、どこかから入手しなくては!
検索したら、dll のダウンロードサイトが軒並み引っかかりますが、この手のサイトから dll をダウンロードする勇気は、私にはありません。マルウェア配布の巣窟なんじゃないか?と思うのが普通の感覚でしょう。

はい、QGIS Lyon x64 版をインストールして

C:\Program Files\QGIS Lyon\apps\grass\grass-6.4.3\bin\libiconv-2.dll

を

C:\Program Files\PostgreSQL\9.4\bin

にコピーしましょう。


Postgis のメンテナーさんは、grass のメンテナーさんにコンタクトを取って、PostGISのインストーラに libiconv-2.dll をちゃんとバンドルした方が良いです。

2015年4月6日月曜日

postgis: 関数 ST_AsGeoJSON はユニークではありません 備忘録

function ST_AsGeoJSON is not unique 関数 ST_AsGeoJSON がユニークではありません。 こんなエラーが出た場合。 バージョンアップしてる環境に、古いDB をリストアすると、PostGIS の残骸が追加されるので、削除が必要
DROP FUNCTION IF EXISTS ST_AsGeoJson(geometry); -- this one changed to use default args 
DROP FUNCTION IF EXISTS ST_AsGeoJson(geography); -- this one changed to use default args 
DROP FUNCTION IF EXISTS ST_AsGeoJson(geometry,int4); -- this one changed to use default args 
DROP FUNCTION IF EXISTS ST_AsGeoJson(geography,int4); -- this one changed to use default args 
DROP FUNCTION IF EXISTS ST_AsGeoJson(int4,geometry); -- this one changed to use default args
DROP FUNCTION IF EXISTS ST_AsGeoJson(int4,geography); -- this one changed to use default args
DROP FUNCTION IF EXISTS ST_AsGeoJson(int4,geometry,int4); -- this one changed to use default args
DROP FUNCTION IF EXISTS ST_AsGeoJson(int4,geography,int4); -- this one changed to use default args
http://stackoverflow.com/questions/24204589/error-using-st-asgeojson-with-postgis

2013年11月14日木曜日

postgresql から json

 php で postgresql からデータを取り出して JSON に加工して返すとか、めんどくさいなぁと思って調べてみました。社員に、どうやって実装してたん?て聞いたら、データ取り出して加工してたという事でした。うんうん、やっぱそうだよなー、面倒くせぇwwwwて思ってたんですが、ラッキーな事に、version 9.2 から row_to_json という関数がサポートされたようです。 まずは、データ準備
create table hoge (id integer primary key, name text);
insert into hoge (id,name) values (1, 'fuga');
insert into hoge (id,name) values (2, 'moge');
ほんで、取り出し
select array_to_json( array_agg( row_to_json(foo) ) )
  from (select id, name from hoge) foo;
結果
[{"id":1,"name":"fuga"},{"id":2,"name":"moge"}]
いやー、素晴らしいですね! 参考:Faster JSON Generation with PostgreSQL

2013年3月9日土曜日

Webサービスの難しさ

 会社で納品しているシステムで、PostgreSQL からデータを引いてサーバ側で地図を描画して配信しているシステムがあるんです。このシステムの難しさは、5分間隔とかでリアルタイムに大量の軌跡を描画しなければならん所です。しかも、24時間以内の長さで、好きな期間を指定できるのと、軌跡を表示する移動体を自由に選択できるところも難しいです。輪をかけて難易度を上げているのが、軌跡の点の上にマウスを置いとくと、その点の通過時刻を表示するという仕様です。この仕様のおかげで、データを点としてデータベースに保持するよりありません。大抵、負荷のかかる見方をするのが、直近の24時間程度という特性もあります。

 ポリラインとか、ポリゴンで登録されていれば、空間インデックスにかけて関係する部分だけを取り出し描画すれば良いんですが、軌跡の場合は、点と点を結ぶ線の情報が必要なので、空間インデックスで絞るわけにもいきません。なので、指定された期間中のデータを総なめする必要があります。

 さて、閲覧する人数が増えて負荷がかかると、PostgreSQL に対する負荷が膨大になり、ストールを起こします。PostgreSQL のチューニングもパンパンにやってて、1個のクエリー自体は普段、10 から 20 mm sec 程度で、それが移動体分になります。しょうがないんで、MySQL でやれば、もっと速いんじゃないの?と指示してテストさせてみましたが、あまりチューニング無しの MySQL だとスタート時点で、100 mm sec 以上かかっていて、MySQLに変更したからと言ってバラ色の人生が待ち受けている訳でもなく、成果も見込めないので、MySQL は辞めました。まぁ、MySQLがそんなに性能が凄い良いとも思っていなかったのを裏付けした格好になります。

 次に、pgpool-2 を使って、接続イニシャルを減らす方法を試してみました。しかし、pgpool-2 だと、通常接続の 1.2倍のペナルティを受けてしまいました。十分にプール数を確保しても、そうだったので、今時のPostgreSQLは、相当に速いんだなぁと実感できました。

 システム構成上、今のところスケール・アウトができないので、今度は memcached を使って PostgreSQL の負荷を低減させる方策を思いつきました。冒頭に示した要件があるので、キーを [移動体番号]-[日付+時刻] にして、5分間隔に軌跡の点を保存して、そこから引くようにしてみました。ところが、実装して試してみると遅い。はい、冷静に考えると5分刻みデータなので24時間だと12*24= 288回のリクエストを発行する計算になります。これが移動体1台分。今度は socket の往復分のコストが馬鹿にならず、性能が 1/2 に落ちました。これを裏付けるべく、15分間隔にして試してみたところ、性能が 1.5 倍になりました。という訳で、memcached も使いよう。今回のケースでは威力を発揮できない感じです。

 結局、結論としては、いつか NoSQL でやらないと、この部分は破綻すると提言していたように、Redis に置き換えてやってみるよりない感じになりました。

 5分間隔でPostgreSQLのテーブルにポリラインを登録しておいて、そこから引くという構成も考えられます。しかし、更新負荷等のバランスから考えて、NoSQL に置き換えるのが王道かな?と考えている次第であります。

2012年5月18日金曜日

soci-3.1.0 on windows build 忘備録

SOCI 一部、boost::geometry のサンプルにも使われています。SQL-Client 接続のライブラリです。 個別のライブラリでゴリゴリ書くのが、だんだんと面倒くさくなってきたし、ビルドが cmake に移行してきたので、コンパイルに挑戦してみようかなぁと。  まず、soci-3.1.0 ディレクトリに移動して、
C:\Downloads\soci-3.1.0> mkdir build
と、build ディレクトリを作成します。 今回は、sqlite3, postgresql, odbc をターゲットに構築してみました。sqlite3 や postgresql のライブラリの構築は、ここでは扱いません。 以下のような build.bat を作成しました。
cd build
cmake .. -G "NMake Makefiles" -DCMAKE_BUILD_TYPE=Release ^
  -DWITH_SQLITE3=ON ^
  -DSQLITE3_INCLUDE_DIR=c:/libs/sqlite3/include ^
  -DSQLITE3_LIBRARIES=c:/libs/sqlite3/lib ^
  -DSQLITE3_LIBRARY=c:/libs/sqlite3/lib/sqlite3.lib ^
  -DSQLITE_INCLUDE_DIR=c:/libs/sqlite3/include ^
  -DSQLITE_LIBRARIES=c:/libs/sqlite3/lib ^
  -DSQLITE_LIBRARY=c:/libs/sqlite3/lib/sqlite3.lib ^
  -DWITH_ODBC=ON ^
  -DWITH_POSTGRESQL=ON ^
  -DPOSTGRESQL_INCLUDE_DIR=C:/libs/postgres/include ^
  -DPOSTGRESQL_LIBRARIES=C:/libs/postgres/lib/libpq.lib ^
  -DBOOST_RELEASE_VERSION=1.49.0 ^
  -DBOOST_INCLUDE_DIR=C:/downloads/boost-1_49_0 ^
  -DBOOST_LIBRARIES=C:/downloads/boost-1_49_0/stage/lib
cd ..
変な ^ マークは、バッチファイルを複数行連結させる時のおまじないです。linux 系では \ に相当します。
ドキュメントには、SQLITE_INCLUDE_DIR, SQLITE_LIBRARIES を設定しろと書いていますが、間違ってます。
SQLITE3_INCLUDE_DIR が正しく、同様に SQLITE3_LIBRARIES かと思いきや CMakeFiles.txt 中で SQLITE3_LIBRARY から SQLITE3_LIBRARIES が思い切り上書きされています。ですので、SQLITE3_LIBRARY に sqlite3 の libディレクトリファイルを指定します。
postgresql も libpq.lib を指定します。

初期ポストで、ここに示していた修正は間違っていたので消去しました


windows だと、リンクに失敗しますので、soci-3.1.0/backends/soci-postgresql.h を修正します。
...
#ifndef SOCI_POSTGRESQL_H_INCLUDED
#define SOCI_POSTGRESQL_H_INCLUDED

#ifdef _WIN32
# ifdef SOCI_DLL
#  ifdef SOCI_POSTGRESQL_SOURCE
#   define SOCI_POSTGRESQL_DECL __declspec(dllexport)
#  else
#   define SOCI_POSTGRESQL_DECL __declspec(dllimport)
#  endif // SOCI_POSTGRESQL_SOURCE
# endif // SOCI_DLL
// ここの2行を追加します。
#pragma comment( lib, "ws2_32.lib")
#pragma comment( lib, "secur32.lib")
#endif // _WIN32
//
// If SOCI_POSTGRESQL_DECL isn't defined yet define it now
...
それでは、準備が整いましたので、soci-3.1.0 のディレクトリに移動して
C:\Downloads\soci-3.1.0> build
C:\Downloads\soci-3.1.0> cd build
C:\Downloads\soci-3.1.0\build> nmake
以上で完了です。 今回、spatialite は使用しませんでした。 インストールは、管理者権限で nmake install です。 2012/05/18 追記:どうも、うまくコンパイルできていないくさい・・・ backends/*/CMakelists.txt の変更が間違ってると思われるので削除。 2012/05/18 追記:修正して追記。 2012/05/18 追記:同じ所で、ひっかかった。http://www.mail-archive.com/soci-users@lists.sourceforge.net/msg00931.html 2012/05/18 追記:うーん・・・やっぱ、ゴリゴリ書くか・・・

2010年10月21日木曜日

Postgresql 値比較備忘録

 今回は、PostgreSQL の Stored Procedure にて、こんなコードではまった。
  ...
  IF TG_OP = 'UPDATE' THEN
    IF OLD.name <> NEW.name THEN
       UPDATE ...
    END IF;
  END IF;
  ...

この場合、OLD.name か、NEW.name が null の場合に条件に合致しない。正しくは
  ...
  IF TG_OP = 'UPDATE' THEN
    IF COALESCE(OLD.name,'') <> COALESCE(NEW.name,'') THEN
       UPDATE ...
    END IF;
  END IF;
  ...

2010年5月29日土曜日

rails と postgresql schema

 うちの社員から、スキーマは分けないんですか?と、問われて、そんな事は無理だろう・・・と即座に思った。でも、まぁ、ちょっと実験してみるか・・・と、やってみたら、簡単に切り替える事ができた…。ショックでした…。


create schema test;
create table foos (
id serial primary key,
bar_id integer,
name varchar(16)
);
create table bars(
id serial primary key,
name varchar(8)
);
create table test.bars (
id serial primary key,
name varchar(8)
);


こんな感じのリレーションを作成しておいて


class FoosController < ApplicationController
before_filter :change_schema

def change_schema
Bar.set_table_name 'test.bars'
end
end


とか、やっちゃったら、簡単にスキーマが切り替えられたんです。怖いです。よくよく考えてみると、テーブル名は規約から外れるわけじゃないので、そうなのかもしれません。

 postgresql の slony-i のマスター・スレーブ方式ですが、DB単位じゃないですか?schema単位でマスター・スレーブ方式が選択できると、途端に幅が広がったりしませんか?相互にスキーマを転送できると、凄い事になりそうです。

2009年2月23日月曜日

SPI で insert or update

 よくSQLでは、レコードが存在していれば更新し、存在していなければ挿入したいという事がありますが、それをPostgreSQL SPIで実現するための備忘録。尚、コードはバッファ・オーバーランに関して無考慮なので、そのまま踏襲しないように。


bool record_insert_hoge(
int id,
const char* namae
) {
char buf[ 512 ];
sprintf( buf, "insert into hoge (id, namae) values (%d,'%s');", id, namae );
return (SPI_OK_INSERT == SPI_execute( buf, false, 1 ));
}

bool record_update_hoge(
int id,
const char* namae
) {
char buf[ 512 ];
sprintf( buf, "update hoge set namae='%s' where id=%d returning *;", namae, id );
if( SPI_OK_UPDATE_RETURNING != SPI_execute( buf, false, 1 ) ) {
return false;
}
return (SPI_processed == 1);
}

bool record_insert_or_update_hoge(
int id,
const char* namae
) {
if( !record_update_hoge( id, namae ) ) {
return record_insert_hoge( id, namae );
}
return true;
}

2009年1月8日木曜日

OKかたづいた

 結局、仕様をよく読まずにコーディングしてハマっていた…という事だった。
palloc は現メモリコンテキストに、SPI_palloc は上位メモリコンテキストに、でもSPI_pfree は、互換性のためだけに存在して、pfree でメモリは解放。名前の付け方、わかんねーよ…と他人のせいにしてみる…。ちくしょー(;_;)。
 今まで、フリーしたメモリ領域に無断でアクセスしていたため、他のプロセスやスレッドに再利用されている場合に限りセグメンテーション・フォルトを起こしていた。という事のようだ。ようやく安定稼働して、これで公開しても大丈夫だろう。バグ直した通知しなきゃ…。

原因がわかった

 かたづいてなかったの続き、ようやく原因がわかった。postgresql の SPI_finish の呼び出し時期が早かったようだ。しかし、まだ釈然としない。SPI_finish の説明では、呼び出し元のメモリコンテキストにスイッチし、その呼び出し元のメモリコンテキスト内では、palloc で確保されたメモリは有効であると記述されているが、その状況を待たずして pfree されてしまっていたという事のようだ。
 一時的に利用するだけなので自動変数で良いと推察される所にわざわざ palloc と pfree を利用する公式のサンプルコードにしても、その意図がわからなくて不安になります。
 今渡こそかたづくだろ…。

2008年12月9日火曜日

Postgresql と C++

 postgresql でサーバサイド・プログラミングをしようと思って、


extern "C" {
#include <postgres.h>
#include <executor/spi.h>
#include <funcapi.h>
}

...


のようにしたが、以下のようなエラーが出てしまいます。


/usr/include/postgresql/8.3/server/nodes/parsenodes.h:167: error: expected unqualified-id before ‘typeid’
/usr/include/postgresql/8.3/server/nodes/parsenodes.h:238: error: expected unqualified-id before ‘typename’


サクッとググると、こんなページが…。うぉーっ、変数に予約語使うのは、勘弁しでぐれ〜。