カテゴリー : PostgreSQL

psql したら Library not loaded: libreadline エラーが出たので brew upgrade postgresql した

ひさしぶりに PostgreSQL を使おうと思い psql コマンドを実行したら Library not loaded: libreadline エラーが出たので brew upgrade postgresql して解決したというお話です。

psql コマンド実行で Library not loaded: libreadline エラー

$ psql
dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
  Referenced from: /usr/local/bin/psql
  Reason: image not found
[1]    61053 trace trap  psql

brew upgrade postgresql で PostgreSQL のバージョンアップ

brew update
brew upgrade postgresql
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
mv /usr/local/var/postgres /usr/local/var/postgres9.3.1
initdb /usr/local/var/postgres
pg_upgrade \
  -b /usr/local/Cellar/postgresql/9.3.1/bin \
  -B /usr/local/Cellar/postgresql/9.6.1/bin \
  -d /usr/local/var/postgres9.3.1 \
  -D /usr/local/var/postgres
cp /usr/local/Cellar/postgresql/9.6.1/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

たまに PostgreSQL を使うぞってときに version アップデートからハマりたくはないですよね。

参考情報

[PostgreSQL] postmaster.opts は自動生成されるファイルなので編集しても無駄

PostgreSQL 7.0 で postmaster の起動時に -i オプションを追加する変更をするために postmaster.opts を修正していたのですが、postgres を再起動すると編集前の状態に戻っていました。

PostgreSQL

どうやら、自動生成されるファイルなので編集しても無駄みたいでした。

続きを読む

[PostgreSQL] ERROR: language “plpgsql” does not exist

PostgreSQL で CREATE FUNCTION したら下記のようなエラーが発生しました。

ERROR:  language "plpgsql" does not exist
HINT:  You need to use "createlang" to load the language into the database.

plpgsql を後から追加するときは、コマンドラインから createlang コマンドでDB名を指定して実行すれば良いみたいです。

$ createlang -d database_name plpgsql

以上です。

【参考】

PostgreSQL で plpgsql がないときは | dTblog | デザインとプログラムの境界をさまようブログ はてなブックマーク - PostgreSQL で plpgsql がないときは | dTblog | デザインとプログラムの境界をさまようブログ

[PostgreSQL] ERROR: must be owner of extension plpgsql

PostgreSQL 8.0.4 から 9.1.5 にDBの移行作業で、pg_dump したデータをインポートしたら下記のようなエラーが発生しました。

CREATE EXTENSION
ERROR:  must be owner of extension plpgsql
 
WARNING:  no privileges could be revoked for "public"
WARNING:  no privileges were granted for "public"

DB を操作しているユーザに「スーパーユーザ」の権限を追加することで解決しました。

■ スーパーユーザ権限を追加する

postgres=# \du
                                         ロール一覧
 ロール名 |                                 属性                                 | メンバー 
----------+----------------------------------------------------------------------+----------
 newuser   | DBを作成できる                                                       | {}
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション | {}
 
postgres=# ALTER USER newuser CREATEUSER ;
ALTER ROLE
 
postgres=# \du
                                         ロール一覧
 ロール名 |                                 属性                                 | メンバー 
----------+----------------------------------------------------------------------+----------
 newuser   | スーパーユーザ, DBを作成できる                                       | {}
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション | {}

以上です。

【参考】

トリガーやファンクションのリストアが失敗していた。 – マンドクセェメモ はてなブックマーク - トリガーやファンクションのリストアが失敗していた。 - マンドクセェメモ

CREATE EXTENSION はてなブックマーク - CREATE EXTENSION

[PostgreSQL] ERROR: missing FROM-clause entry for table

PostgreSQL にて、下記のようなエラーが発生した場合の対応方法をメモ。

エラーメッセージ missing FROM-clause entry for table

ERROR: missing FROM-clause entry for table

バージョン 8.1 以降の 8 系は、SET add_missing_from TO true; で対応できるみたいです。

バージョン8.1への移行

add_missing_fromのデフォルトがfalseになりました(Neil)

FROM参照を持たない問い合わせでテーブルが使用された場合、デフォルトでエラーとなるようになりました。 まだ過去の動作を使用することができますが、そのためにはこのパラメータを’true’にしなければなりません。

ダンプに暗黙的なFROM構文を使用して作成したビューやルールがある場合、既存のダンプファイルをロードするために、add_missing_fromを真に設定しなければならないかもしれません。 これに悩まされるのは一回だけのはずです。 PostgreSQL 8.1は、こうしたビューやルールを標準の明示的なFROM構文に変換するためです。 したがって、今後のダンプではこの問題は起こりません。

ss=> SELECT WORK.* FROM WORK w;  -- 一見正しそうなこのSQLもエラーになる
ERROR:  missing FROM-clause entry FOR TABLE "work"
oss=> SET add_missing_from TO TRUE; -- デフォルト値がfalse
SET
oss=> CREATE VIEW work_v AS SELECT w.* FROM WORK w ;
CREATE VIEW
oss=> \d work_v        VIEWとして読み込ませると定義は自動的に訂正される
    VIEW "public.work_v"
 COLUMN |  TYPE  | Modifiers
--------+--------+-----------
 id     | BIGINT |
 VALUE  | text   |
 stat   | text   |
VIEW definition:
 SELECT w.id, w.value, w.stat
   FROM "work" w;

■ add_missing_from(boolean)
オンの場合、問い合わせによって参照されるテーブルは、既に存在していなければFROM句に追加されます。この振る舞いはSQL標準に準拠しておらず、多くのユーザは(そのエイリアスを参照しなければならないテーブル参照をする場合など)誤りを隠蔽するため、嫌がります。デフォルトはoffです。この変数は、この振る舞いがデフォルトで認められている8.1以前のPostgreSQLリリースとの互換性を有効にします。

この変数が有効になっているとしても、問い合わせによって参照される個々の明示的FROMエントリに対し、警告メッセージが放出されることに注意してください。問い合わせのFROM句に対する問い合わせで参照される全てのテーブルを追加すること(もしくはDELETEの場合のそのUSING句)に依る、この振る舞いに信頼を置かないアプリケーションの更新に、ユーザは消極的になります。

バージョン 9.0 からは add_missing_from が廃止されたみたいなので、SQLクエリを書き換えるしかないようでした。

バージョン 9.0 への移行 / 互換性のない変更点
サーバ設定の非互換

■ 設定 add_missing_from が廃止されました。
これはかなり以前からデフォルトで off となっていました。 (Tom Lane)

以上です。

[Heroku] heroku-postgresql:dev の設定方法

Heroku にて、従来のshared-database から新しくなった heroku-postgresql の dev をインストールしました。

$ heroku addons:add heroku-postgresql:dev
----> Adding heroku-postgresql:dev to myapp... done, v5 (free)
      Attached as HEROKU_POSTGRESQL_SILVER
      Database has been created and is available
        ! WARNING: dev is in beta
        !          increased risk of data loss and downtime
        !          send feedback to dod-feedback@heroku.com
$ heroku pg:info HEROKU_POSTGRESQL_SILVER
=== HEROKU_POSTGRESQL_SILVER
Plan         Dev
Status       available
Connections  1
PG Version   9.1.3
Created      2012-06-01 14:23 UTC
Data Size    0
Tables       0
Rows         0
Conn Info    "host=ec2-xx-xx-xxx-xxx.compute-1.amazonaws.com
             port=5432 dbname=xxxxxxxxxxx
             user=xxxxxxxxxx sslmode=require
             password=xxxxxxxxxxxxxxxxx"
$ heroku pg:promote HEROKU_POSTGRESQL_SILVER
-----> Promoting HEROKU_POSTGRESQL_SILVER to DATABASE_URL... done

以上です。

【参考】

Heroku’s new, free PostgreSQL 9.1 development database はてなブックマーク - Heroku's new, free PostgreSQL 9.1 development database

Heroku Postgres Dev Plan | Heroku Dev Center はてなブックマーク - Heroku Postgres Dev Plan | Heroku Dev Center

Postgres | Heroku Dev Center はてなブックマーク - Postgres | Heroku Dev Center

heroku-postgresql:dev に移行した – HsbtDiary(2012-05-16) はてなブックマーク - heroku-postgresql:dev に移行した - HsbtDiary(2012-05-16)

ヘロク、SaaS型DB「Heroku Postgres」の安価なプランをベータ公開 (Computerworld) – Yahoo!ニュース はてなブックマーク - ヘロク、SaaS型DB「Heroku Postgres」の安価なプランをベータ公開 (Computerworld) - Yahoo!ニュース

[PostgreSQL] ERROR: Cannot insert a duplicate key into unique index

PostgreSQLにて、新規レコードのINSERT時に下記のようなエラーが発生しました。

ERROR: Cannot insert a duplicate key into unique index tbl_xxxx_pkey

原因は、既にDBに存在しているレコードのカラム xxxx_pkey がUNIQUE制約に引っ掛かっていたためでした。

UNIQUE 制約

[ CONSTRAINT name ] UNIQUE

入力

CONSTRAINT name
制約に付与する任意の名前。

出力

status
ERROR: Cannot insert a duplicate key into a unique index.
このエラーは、列に重複する値を挿入しようとした、 その実行時に起こります。

・引用元:CREATE TABLE はてなブックマーク - CREATE TABLE

以上です。

【参考】

ポストグレスSQLメモ(Hishidama’s PostgreSQL SQL Memo) はてなブックマーク - ポストグレスSQLメモ(Hishidama's PostgreSQL SQL Memo)

[PostgreSQL] 他のPCから接続するための設定

PostgreSQL にて、他ホストから接続するための設定を調べたのでメモ。

postgresq.confファイルの修正

■ /usr/local/pgsql/data/postgresql.conf

#listen_addresses = 'localhost' # what IP interface(s) to listen on; 
                                # defaults to localhost, '*' = any

↓(変更)

listen_addresses = '*'

設定を変更後、PostgreSQLを再起動する。

/etc/rc.d/init.d/postgresql restart

接続できるクライアントを設定する

PostgreSQLの設定ファイルである pg_hba.conf にて、接続できるホストを制限しているので、これを変更します。

■ /usr/local/pgsql/data/pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

■(例)全てのホストからの接続を許可する

全てのホストから、全てのデータベースへの接続を許可する場合、以下の記述を追加する。

host all all 0.0.0.0/0 trust

接続を制限する

pg_hba.confは1行で1レコードを構成しており、1行のフォーマットは以下のとおりである。

KIND DATABASE USER CIDR-ADDRESS METHOD

● KIND

「local」「host」「hostssl」「hostnossl」のいずれか。localはUNIXドメインソケット経由の接続、hostはTCP/IP経由の接続、hostsslはSSL経由の接続、hostnosslはSSLを使用しない接続を意味する。

● DATABASE
接続を許可したいデータベース名を指定。「all」とすると全てのデータベースを意味する。複数のデータベース名を指定したい場合は、カンマで区切る。

● USER
接続を許可するユーザ名を指定。「all」とすると全てのユーザを意味する。ユーザ名の前に「+」を付けるとグループ名を指定したことになる。複数のユーザ名を指定したい場合は、カンマで区切る。

● CIDR-ADDRESS
KINDがlocal以外のときに指定する。接続を許可するクライアントのIPアドレスやネットワークアドレスを指定する。

例1)192.168.0.11/32・・・192.168.0.11からの接続のみを許可する
例2)192.168.0.0/24・・・IPアドレスが192.168.0.xであるクライアントのみを許可する
例3)0.0.0.0/0・・・任意のIPアドレスのクライアントを許可する

● METHOD
ユーザの認証方式を指定する。代表的なものは以下のとおり。
trust・・・認証なし。無条件に接続を許可する。
reject・・・無条件に接続を拒否する。特定のホストやネットワークからの接続を拒否する際に使用。
md5・・・md5を利用したパスワード認証。パスワードを指定していないユーザは接続できない。
password・・・パスワード認証を行うが、BASIC認証のため、パスワードがそのままネットワークを流れてしまう。

・引用元:他ホストから接続するための設定 はてなブックマーク - 他ホストから接続するための設定

以上です。

【参考】

PostgreSQL8.3 クライアント認証 – 読書と技術となんか色々のログ – 楽天ブログ(Blog) はてなブックマーク - PostgreSQL8.3 クライアント認証 - 読書と技術となんか色々のログ - 楽天ブログ(Blog)

[PostgreSQL] NULL でない自身の最初の引数を返す COALESCE 関数が便利

PostgreSQL にて、値がNULLの場合は、代わりの値を返す COALESCE 関数という便利なものを教えてもらったのでメモ。

SUM 関数は、指定したカラム(ここでは money)の値が全て NULL の場合、NULLを返します。

返り値が NULL ではなく、0 にしたいので、最初は下記のように書いていました。

SELECT
	CASE WHEN SUM(money) IS NOT NULL THEN SUM(money) ELSE 0 END AS total_money
	FROM USER;

このような、SUM関数の結果がNULLの場合は、0 を返して欲しいという書き方は、CASE 文を使わなくても、COALESCE 関数を使えば実現できるみたいです。

6.12.2. COALESCE
COALESCE(value [, …])
COALESCE 関数は、NULL でない自身の最初の引数を返します。 データを表示の目的で取り出す際、NULL 値の代わりにデフォルト値を使う場合、時として便利なことがあります。 その例です。

SELECT COALESCE(description, short_description, ‘(none)’) …

・引用元:条件式 はてなブックマーク - 条件式

なので、COALESCE 関数を使って、下記のように書くことができます。

SELECT
	COALESCE(SUM(money), 0) AS total_money
	FROM USER;

以上です。

【参考】

PostgreSQL COALESCEでNULLを空白に変換 – 130単位 はてなブックマーク - PostgreSQL COALESCEでNULLを空白に変換 - 130単位

SQL の COALESCE はてなブックマーク - SQL の COALESCE

[PostgreSQL] ERROR: permission denied for ~ というエラーが出たらテーブル(table)の権限を確認する

PostgreSQL にて “ERROR: permission denied for ~” というエラーが出たら、テーブル(table)の権限がないということなのでこれを確認します。

■ エラー内容

ERROR:  permission denied for table table_name

■ 権限の確認コマンド

\z

■ テーブルへの権限付与(例)

GRANT SELECT, UPDATE, INSERT ON table_name TO role_name;

以上です。

【参考】

ふかふかの部屋 – TSR – PostgreSQL 関連 はてなブックマーク - ふかふかの部屋 - TSR - PostgreSQL 関連

権限の追加 – ロール(ユーザー)の作成 – PostgreSQLの使い方 はてなブックマーク - 権限の追加 - ロール(ユーザー)の作成 - PostgreSQLの使い方