pg_settingsをUPDATE文で更新してみる

# はじめに
警告：ちょい長い記事ですが、読んでも普段のデータベース運用に全く役に立たない内容ですｗ

今日、ちょっとPostgreSQLパラメータについて調べ物をしていたら、気になる記述を見つけた。
http://www.postgresql.jp/document/9.5/html/view-pg-settings.html

> pg_settingsビューには挿入も削除もできませんが、更新することは可能です。 pg_settings行に適用されるUPDATEは名前付きのパラメータに対してSETコマンドを実行することと同値です。 

まじかよ！知らんかったわ。じゃあ試してみよう。

# UPDATE文でwork_memを変更する。

~~~
postgres=# SHOW work_mem ;
 work_mem 
----------
 4MB
(1 row)

postgres=# UPDATE pg_settings SET setting = 8192 WHERE name = 'work_mem';
 set_config 
------------
 8MB
(1 row)

UPDATE 0
postgres=# SHOW work_mem ;
 work_mem 
----------
 8MB
(1 row)

postgres=# 
~~~

マジかよ。UPDATEできたよ！
UPDATE 0 という実行結果が少々気持ち悪いが・・・。

# いろんなケースを試してみる

じゃあ、いろんな更新操作をためしてみようじゃないか。

## 無効な値域をSETする。

~~~
postgres=# UPDATE pg_settings SET setting = -100 WHERE name = 'work_mem';
ERROR:  -100 is outside the valid range for parameter "work_mem" (64 .. 2147483647)
~~~

## 異なる型をSETする。

~~~
postgres=# UPDATE pg_settings SET setting = 'hogehoge' WHERE name = 'work_mem';
ERROR:  invalid value for parameter "work_mem": "hogehoge"
~~~

## 説明(short_desc)を更新する。

~~~
postgres=# SELECT name, short_desc FROM pg_settings WHERE name = 'work_mem';
   name   |                        short_desc                        
----------+----------------------------------------------------------
 work_mem | Sets the maximum memory to be used for query workspaces.
(1 row)

postgres=# UPDATE pg_settings SET short_desc = 'hogehoge' WHERE name = 'work_mem';
 set_config 
------------
 4MB
(1 row)

UPDATE 0
postgres=# SELECT name, short_desc FROM pg_settings WHERE name = 'work_mem';
   name   |                        short_desc                        
----------+----------------------------------------------------------
 work_mem | Sets the maximum memory to be used for query workspaces.
(1 row)
~~~

ちょｗｗｗ
UPDATE自体は失敗しないが、更新対象列は更新されない。
ちうか、UPDATEの結果がsettingsの結果を表示しているのが、こうなると気持ち悪い。

## contextがpostmasterのパラメータを変更する。

~~~
postgres=# SHOW shared_buffers;
 shared_buffers 
----------------
 128MB
(1 row)

postgres=# UPDATE pg_settings SET setting = 32768 WHERE name = 'shared_buffers';
ERROR:  parameter "shared_buffers" cannot be changed without restarting the server
~~~

これはエラーになるのか。

## contextがsuperuserのパラメータを一般権限ユーザで変更する。

### スーパーユーザで実行

~~~
postgres=# SHOW wal_compression ;
 wal_compression 
-----------------
 off
(1 row)

postgres=# UPDATE pg_settings SET setting = 'on' WHERE name = 'wal_compression';
 set_config 
------------
 on
(1 row)

UPDATE 0
postgres=# SHOW wal_compression ;
 wal_compression 
-----------------
 on
(1 row)
~~~

### 一般ユーザで実行

~~~
postgres=> SHOW wal_compression ;
 wal_compression 
-----------------
 off
(1 row)

postgres=> UPDATE pg_settings SET setting = 'on' WHERE name = 'wal_compression';
ERROR:  permission denied to set parameter "wal_compression"
~~~

どーやら、許可されないcontexへの操作や権限がない場合にはエラーにするようだ。

## 行の削除

~~~
postgres=# DELETE FROM pg_settings WHERE name = 'work_mem';
ERROR:  cannot delete from view "pg_settings"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
~~~

削除しようとするとエラーになるようだ。
自分でRuleを書けばDELETE可能なんだろうか。意味はないけど。

## 行の挿入

~~~
postgres=# INSERT INTO pg_settings VALUES ('foo','bar', '', 'resource', '', '', 'user', 'text', '', null, null, null, '', '', '', 0, 'off');
ERROR:  cannot insert into view "pg_settings"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
~~~

挿入も同様。
自分でRuleを書けばINSERT可能なんだろうか。意味はないけど。

# 種明かし

どうやらpg_settingsには以下のようなルールが設定されていて、それによって、こんな奇妙な振る舞いになるようだ。

~~~
postgres=> \d+ pg_settings 
                 View "pg_catalog.pg_settings"
     Column      |  Type   | Modifiers | Storage  | Description 
-----------------+---------+-----------+----------+-------------
 name            | text    |           | extended | 
 setting         | text    |           | extended | 
 unit            | text    |           | extended | 
 category        | text    |           | extended | 
 short_desc      | text    |           | extended | 
 extra_desc      | text    |           | extended | 
 context         | text    |           | extended | 
 vartype         | text    |           | extended | 
 source          | text    |           | extended | 
 min_val         | text    |           | extended | 
 max_val         | text    |           | extended | 
 enumvals        | text[]  |           | extended | 
 boot_val        | text    |           | extended | 
 reset_val       | text    |           | extended | 
 sourcefile      | text    |           | extended | 
 sourceline      | integer |           | plain    | 
 pending_restart | boolean |           | plain    | 
View definition:
 SELECT a.name,
    a.setting,
    a.unit,
    a.category,
    a.short_desc,
    a.extra_desc,
    a.context,
    a.vartype,
    a.source,
    a.min_val,
    a.max_val,
    a.enumvals,
    a.boot_val,
    a.reset_val,
    a.sourcefile,
    a.sourceline,
    a.pending_restart
   FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline, pending_restart);
Rules:
 pg_settings_n AS
    ON UPDATE TO pg_settings DO INSTEAD NOTHING
 pg_settings_u AS
    ON UPDATE TO pg_settings
   WHERE new.name = old.name DO  SELECT set_config(old.name, new.setting, false) AS set_config
~~~

だから、pg_settings のsetting以外の列に対してUPDATEしても、それは無視されてしまうんだなー。

# おわりに

ね、役に立たなかったでしょ？