【PostgreSQL17】MATERIALIZED VIEWの仕様が変わったらしく躓きまくった話と対処法

プログラミング等

どうもこじらです。

最近Macbookを買いました。絶賛zshだったりAppleScriptだったりで遊びまくっています。

あれですね、MacはWindowsよりスマートじゃないですねw

ショートカットキー覚えて、画面操作をカスタマイズしてやっと真価を発揮して、あとLinuxの知識ないと概念的な部分から勉強する必要があって、軽くパソコン触りたい人にとってはひたすら非効率だと思います。

世のMacユーザの9割はWindowsでいいじゃんって使い方をしてそうでならない。

Mac高ぇんじゃ。絶対一般向けの仕様じゃないぞ。

 

まぁそんな話はおいといて本題に入ります。

エラー内容

エラー内容は以下。funcAの中で呼んでるtableAがないって言われていますね。

SQLエラー [42P01]: ERROR: relation "tableA" does not exist
場所: PL/pgSQL function funcA(integer) line 10 at SQL statement

 

DBあるある。「ないって言ってるけどない訳じゃない」のやつですかね。

以下SQLでテーブルが存在していることは確認してます。

SELECT * FROM information_schema.TABLES WHERE table_name = 'tableA';

 

does not existねぇ…。ってことは権限がないのかなぁ…。っていう謎だけどDB的には正しい推測をしつつ色々調べてました。

 

今回躓くまでの経緯として、Macbookを買ってWindows環境で動かしていたDockerをMac環境でも動かしたいということで、Javaのプロジェクトにくっつけておいたdocker-compose.ymlを使用してPostgreSQLを起動しようとしたところ、Docker起動時に実行するように仕込んでいるCREATE MATERIALIZED VIEW文がエラーを吐いた感じです。

 

Dockerの起動構成

docker-compose.ymlの内容は以下です。

services:
  postgres:
    image: postgres:alpine3.20
  ports:
  - 5432:5432
  volumes:
  - dbdata:/var/lib/postgresql/data
  - ./docker-local/postgres/init:/docker-entrypoint-initdb.d
  environment:
    POSTGRES_USER: postgres
    POSTGRES_PASSWORD: postgres
    POSTGRES_DB: postgres
  TZ: "Asia/Tokyo"
  restart: always

volumes:
  dbdata:
    driver: local
  driver_opts:
    type: none
    device: ./docker-local/volumes/postgres
    o: bind

 

Dockerの起動構成はWindows環境と同じ、ディレクトリ構成も同じ。

Windows環境では問題なく起動できていた。

じゃあなんで起動に失敗したんだ…？

 

とりあえず、現状を理解するために「本当に権限がないことが理由か？」を確認することにしました。

CREATE MATERIALIZED VIEW文とtableAの関係性

私のCREATE MATERIALIZED VIEW文はFUNCTION経由でtableAを参照しています。

 

MViewの作成文はこんな感じ。中でFUNCTION(funcA)を呼んでいます。

CREATE MATERIALIZED VIEW mviewA AS
select id from tableB b where funcA(b.id) > 4000;

 

funcAは中でtableAを参照しています。そのため、

mviewA
↓
funcA
↓
tableA

といったルートで参照している訳ですね。

 

【原因1層目】スキーマの問題

funcA内でtableAを参照する時、スキーマは指定していません。

シンプルなSELECT文なので、スキーマくらいしか問題になりうるところがないなと思い、一旦スキーマを明示的に指定する形でSELECT文を修正して試してみました。

select * from tableA;

↓

select * from schemaA.tableA;

 

いけました。問題はスキーマを省略していて、なぜかschemaAを参照できていなかったからでした。

 

原因と解決方法は分かったので、スキーマ名指定して終わりでも問題ないですが、「それじゃエンジニアじゃねぇ！」っていう新人研修時に叩き込まれたいらんプライドを発動させ、さらに原因を調べていきました。

 

search_path…？

Geminiと会話していたら、PostgreSQLにはsearch_pathという概念があることを知りました。

スキーマを省略した際に、どのスキーマを優先させて、どの順番に参照を試みるかといったものです。

 

まぁたしかにそういう機能がないとスキーマの省略はできないですもんね。

 

とりあえず、エラーになるパターンがCREATE MATERIALIZED VIEW文で呼び出したときのみということは分かっていました。

CREATE MATERIALIZED VIEW文内で実行しているSELECT文を抜き出して実行したところ、問題なく実行できたので。

・MView経由

CREATE MATERIALIZED VIEW mviewA AS
select id from tableB b where funcA(b.id) > 4000; -- NG

・素のSQL

select id from tableB b where funcA(b.id) > 4000; -- OK

 

そのため、FUNCTION内にsearch_pathを確認する術がないか、Geminiに聞いてデバッグを試みます。

仕込んだのはこんな感じ。

show search_path into str;
raise info '%', str;

 

そしたら、以下の通りになりました。

・MView経由

 pg_catalog, pg_temp

・素のSQL

 public, public, "$user"

 

pg_catalog, pg_temp？？？なにこれ本当にスキーマ？？？

pg_catalogにもpg_tempにも当然tableAはないのでdoes not existはその通りなんですが、設定されているスキーマが謎すぎました。

 

また色々調べていたら、PostgreSQL17の新しすぎる記事に辿り着きました。

REFRESH MATERIALIZED VIEW
pgsql-jp.github.io

 

REFRESH MATERIALIZED VIEWの記事ですが、バージョン17からはsearch_pathは一時的にpg_catalog, pg_tempになるという内容が書かれています。

絶対これじゃねぇか…。

 

富士通のPostgreSQLをラッパーしてる製品？の説明にも同じような内容があり、CREATE文にも言及しているのでまぁほぼ確定だなと。

2.3.22 保守操作時のsearch_pathの変更
software.fujitsu.com

 

docker-compose.ymlが同じでなぜバージョンに差が…？

同じ起動構成でバージョンに差が出ることある？

それじゃあDocker使うメリットなくね？

とかグチグチ言いながら、とりあえず、Windows環境とMac環境のバージョンを確認をしました。

確認方法は以下。

select version();

 

【原因2層目】docker-compose.ymlのバージョン指定ミス

・Windows側docker

PostgreSQL 16.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit

・Mac側docker

PostgreSQL 17.2 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit

 

Alpineのバージョンは同じ。OSが異なるのは問題ないとして、PostgreSQLのバージョンが違うのは想定外でした。

バージョンが違うってことは、バージョンが違うってことだよな…🤔

 

まぁ状況証拠的にバージョンが同じだった方が怖いんですがw

 

 

docker-compose.ymlを見返してみます。

……。

あ、これってpostgresのバージョン指定にはなってないの！！？

image: postgres:alpine3.20

 

アホでした。

alpine3.20がpostgresのバージョンの指定でもあるのかと思っていました…。

 

修正内容

docker-compose.ymlの修正

イメージを修正しバージョンを明示的に指定しました。

image: postgres:17.2-alpine

Windowsでの実績がある16.4にした方が楽なんですが、いずれ17にアップグレードするんだったら今の時点で最新にしちゃった方が楽だろうという、ここまでの調査に半日要してるのにある意味ドMすぎる思考で17.2を選択しました。

 

FUNCTIONの修正

FUNCTIONのSET句でsearch_pathを明示的に指定するようにしました。

CREATE OR REPLACE FUNCTION funcA(arg1 integer)
RETURNS INTEGER AS $$
-- FUNCTIONの内容
$$ LANGUAGE plpgsql
SET search_path TO schemaA, pg_catalog, pg_temp; -- 追加

 

postgresql.confでsearch_pathを指定する方法も試したんですが、そのやり方では解消不可です。

MviewをCREATEしたり、REFRESHしたりするときに一時的にsearch_pathを切り替えるのがバージョン17の仕様なので、大元のsearch_pathを修正したところで一時的に上書きされます。

 

FUNCTIONの場合、SECURITY DEFINERを指定すれば実行ユーザを変更できて問題解決できるんじゃね？？とも思いましたが、そもそもスキーマと実行ユーザは別の話なので、この方法では無理でした。

 

明示的にスキーマ指定した方がいいんじゃ？

🧒🏻「結局スキーマをFUNCTIONにハードコーディングするなら明示的に指定するのと変わらないんじゃ？」

select * from schemaA.tableA;

 

私「…勘のいいガキは嫌いだよ👿」

 

まぁ、select文実行箇所全てにスキーマを書くのはめんどうだからこの書き方にしたってことにしておきます。

 

てか、なんでPostgres17でsearch_pathの仕様変更したんですかね？

保守時にミスらないようにするためってのはたしかにわかるんですが、影響がでかいしMView作成時も対象にしている理由があんまり理解できてないです。

 

かなり破壊的な仕様変更だと思うんですけどね。

本当はpostgresql.confで解決する方法あったりするのかなぁ…。でもそうしたら保守時のミスの問題は解決されないか…。うーん…。

 

よく分かんないですが、この先のアップデート内容で少しずつPostgres側の意向が掴めてくる気もするので今回はここまでにします。

 

こじらでした

じゃ

タイトルとURLをコピーしました