- [[PostgreSQL/トリガ]]

#contents

* リンク [#h9b0cd5e]

- [[日本PostgreSQLユーザ会:http://www.postgresql.jp/]]
- [[本家:http://www.postgresql.org/]]
- [[旧版の書庫:http://www.postgresql.org/ftp/source/OLD/]]

* サービス開始時にconfファイルのアクセス権エラーが出る [#v8cab16e]

CentOS6 で、

 service postgres start

とすると、以下のエラーが出る。

 postmaster cannot access the server configuration file "/pgdata/postgresql.conf": Permission denied

データ格納位置を標準から /pgdata に変更し、/etc/init.d/postgres をそれに合わせて変更したために、SELinux の制限にひっかかったのが原因。

SELinux は否定しないが、デフォルトでオフにならんかな。ファイアウォール(iptables)のデフォルトは any-any-accept なのにな。

結局は SELinux を Permissive に変更して終わり。

 setenforce 0


* DB 作成時に Encoding, Collation, Ctype を指定する [#m44efe76]

CentOS 上、yum で PostgreSQL をインストールして起動すると initdb が "--no-local" オプションなしで実行されるため、こんな状態になる。

 [user@localhost ~]$ psql -l
                              List of databases
    Name    | Owner | Encoding |  Collation  |    Ctype    | Access privileges 
 -----------+-------+----------+-------------+-------------+-------------------
  postgres  | pgsql | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
  template0 | pgsql | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgsql
                                                           : pgsql=CTc/pgsql
  template1 | pgsql | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgsql
                                                           : pgsql=CTc/pgsql

initdb の処理を書き換えてあげる解決策がいいんだけど、既存サーバであるなどの理由でいじれないケースがある。

よって、createdb するときに指定する方法をメモ。

 createdb -E UTF-8 --lc-collate=ja_JP.UTF-8 --lc-ctype=ja_JP.UTF-8 -T template0 newdb


* psql インタフェース上でのクエリ結果をファイルに出力する [#o534c573]

\o を使えばできる。

 db=> \o /home/user/result.txt
 db=> select * from users;

戻るのはどうするのかな...

* psql コマンドにてパスワードを訊かれないようにする [#rb21e6a0]

シェルスクリプト中で psql を介したクエリ実行をしたい場合に、パスワードを訊かれるのは困る。 cronで実行させると失敗してしまう。

これを回避するには、実行ユーザのホームディレクトリに .pgpass ファイルを作成し、そこにパスワードを書いておく。

書式は以下のとおり。

 hostname:port:database:username:password

PostgreSQL マニュアル中では[[ここに書かれている:http://www.postgresql.jp/document/9.2/html/libpq-pgpass.html]]

* パスワードを設定 [#had51135]

createuser で追加したユーザのパスワードを後から変更する。

ここでは、dbuser というユーザにパスワード password1 を設定する。

psql インタフェースより、

 alter role dbuser with password 'password1'


* 7系から8系に移行したらトラブルになった(1) [#n4fb29d6]

7系で、日付フィールドから文字列を切り出す場合に次のようにしていた。

 select substr(date, 1, 4)

date は date 型フィールド。

8系ではこれがエラーになる。

 function substr does not exist

エラーメッセージを見ると、pg_catalog にそんな関数はない、と言う。

pg_catalog を見ると、 substr(text, int, int) となっている。

つまり、date 型を明示的に text にキャストしてあげないとエラーが出るようになったと言うこと。

 select substr(date::text, 1, 4)

こうすれば通る。

少しはまった。


*コンパイル時のオプションの確認方法 [#b69672b8]

 pg_config --configure

他に、

 pg_config --version

もある。

 pg_config --help

で説明。

* 関数やトリガの内容を確認する [#qaa45174]

 マニュアル読めばそのうちたどり着く話ですが。

 入門書なんかだと、トリガや関数の定義方法にはふれてますけど、確認方法や消去方法はかかれてないです。

 トリガは &color(blue){pg_trigger}; に書かれてます。

 関数定義は、&color(blue){pg_proc}; ですな。


*シーケンスに任意の値をセットする方法 [#y28b6fe7]

あまり困ることがなかったからほったらかしにしておいた問題点について、ようやく困ってしまったので、調べたメモ。

 select setval('seq_name', 1);

とすると、シーケンス seq_name に値"1"がセットされます。

* テーブル所有者変更 [#h1483253]

 テーブルの所有者情報は、pg_class に格納されているので、

 select relname, relowner from pg_class where relname not like 'pg_%';

 とすると、テーブル名と所有者が出る。(全テーブルを出したければ、where以後の句を削除)

 あとは、ユーザIDを調べて、たとえば100に変えたければ、

 update pg_class set relowner=100 where relname='table_name';

としてやれば良い。
 ただし、こういう変更をするときは、そのテーブルにアクセスしているユーザがいないこと。

*列を追加してデフォルト値をセット [#gab26557]

2ステップの作業

 alter table tablename add culumn columnname bool;
 alter table tablename alter column columnname set default false;

これで、列 columnnameを追加して、デフォルト値を FALSE にセットできた。

*PL/pgSQLを使えるようにする [#i47b3014]

コマンドラインから

 createlang plpgsql <dbname>

いちいち面倒な場合は、

 createlang plpgsql template1

としておくと、以後createdbされるデータベースは最初から使えるようになる。

*C関数 [#q6420680]

例えば1からnまでの総和を求める簡単なプログラム sumone.cを作る。

 int sumone(int num) {
 
   int i, sum;
 
   if ( num < 0   ) { return -1; }
   if ( num > 100 ) { return -1; }
 
   for (i=1; i<=num; i++) { sum += i; }
 
   return sum;
 
 }

コンパイルする。

 gcc -fpic -I/usr/local/pgsql/include/server -c ./sumone.c

共有ライブラリの形にする

 gcc -shared -o sumone.so sumone.o

適当な場所に置く(この例は不適当。postgresユーザで適当なユーザライブラリ配置ディレクトリを掘ってそこに置くのがいい)

 mv sumone.so /tmp/

データベース上に関数を作成する

 psql <dbname>
 db=# create function sumone(int4) returns int4 as '/tmp/sumone.so' language 'c';
 CREATE FUNCTION

使ってみる。
 db=# select sumone(4);
  sumone
 --------
      10
 (1 row)

1+2+3+4=10だからあってますね。

もし、ひとつの*.soファイルの中に複数の関数がある場合は、どのシンボル(関数)をPostgreSQL外部関数として登録するかを明示する必要があります。

たとえば、

 int func1()
 int func2()

のふたつの関数をfunctions.soライブラリに含めている場合、これを同名のPostgreSQL外部関数として登録するには、

 db=# create function func1() returns int4 as 'functions.so','func1' language 'c';
 db=# create function func2() returns int4 as 'functions.so','func2' language 'c';

とします。共有ライブラリ指定のあとにシンボルを指定するわけです。

この指定をせずに登録しようとすると、

 ERROR:  function () does not exist

となって失敗します。(エラーメッセージからは原因が分かりづらい…)

* 権限の付与 [#u4ca26f6]

 細かいことと、詳しい話は抜きにして。

 ある環境でデータベースを作って開発作業をしてて、今度は別の環境で作業することになった際、データベースの作成者(所有者)が異なる状況になってしまった。

 でも、開発資産のDBアクセスユーザを変更すると、二つの開発環境で整合性を取るのが面倒になるので、データベース側でこの差異を吸収したい。

 開発資産ではuser1へ接続しようとしていて、データベースはuser2で作成されているとすると、データベースtestdbに対して、user1がアクセスできるようにすれば良い。

 createuser user1
 
 psql testdb
 testdb=# grant all on <table> to user1;

このようにして、アクセスが必要なテーブルに対して、権限を付与すればOK。

#br

ただ、上記の例はアクセス全権を付与しているので、ケースによっては権限を絞るなりの注意は当然必要。 今回はこれで良し。

* DB システム初期化(initdb)時の --no-locale 設定 [#zf895a71]

 PostgreSQL のインストールがひととおり終わって、initdb する際、決まり文句として

 initdb --no-locale

とするが、このオプションがマニュアルに記載されていないことに今頃気付いた(2010/03/21)
 指定しないとどうなるかについて、調べている記事を見つけたのでリンク置いておく。

  [[initdb の locale 設定:http://blog.zaq.ne.jp/okura/article/20/]]

 ドキュメントには載ってないが、初期化の際は、このオプションは必須。

* ユーザ一覧を見る [#u68530e8]

 select * from pg_user

psql で接続する先は、template1 で良い

なお、postgres データベースにつないで、下記でも良い。

 select * from pg_shadow;

トップ   編集 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS