fly.io postgres: relation "table_name" does not exist. の解決
はじめに
お世話になっていた Heroku が2022/11/28から無料枠を廃止してしまいました。引っ越し先として Fly.io に LINEBOT のアプリケーションを移行させた際につまづいた問題の話です。
[補足]
Heroku -> Fly.io への移行は、ベル15の開発ブログ様の記事[1]を参考に行いましたが、非常に簡単なものとなっています。
- Homebrewでfly.ioが提供しているコマンドラインツールflyctlをインストール
$ brew install flyctl
- fly.ioへログイン
$ flyctl auth login
- ソースコードがあるディレクトリに移動し、アプリケーションを新たに作る。アプリ名, 地域, postgresql, redisの使用有無を設定すれば終了です。これも指示に従って答えていけば一瞬で終わります。
$ flyctl launch
- デプロイする
$ flyctl deploy
これだけでデプロイ完了です。
問題点
Fly.io は3GBの永続的なボリュームを無料で提供していて、postgreSQLを課金なしで利用することができます。そこで、新しくFly上にDBを立てて、旧DB(Heroku Add-ons)から乗り換えることにしました。変更点はDBのURLを示す環境変数の値を新しいものへと修正するだけです。(のはずと思っていました。)
しかし、以下のようなプログラムに対して、
relation "user_location" does not exist.
というエラーを吐いてしまいます。
from sqlalchemy import create_engine, text
# DBへの接続
DATABASE_URL = os.environ.get("DATABASE_URL")
engine = create_engine(DATABASE_URL.replace("postgres", "postgresql"), echo=True)
with engine.begin() as conn:
result = (conn.execute(
text(
"SELECT location_name FROM user_location WHERE user_id = :user_id"
),
{"user_id": event.source.user_id}
)).all()
flyctlコマンドを用いることで、アプリケーションにアタッチされているDBへの接続をコンソール上から行うことができるので、本当にuser_location
テーブルが存在しないかどうか確かめてみます。
$ flyctl postgres connect -a {Application name}
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | user_location | table | postgres
あるじゃんかよ...。
前置きが長くなりましたが、確かに存在するはずのテーブルがrelation user_location does not exist.
のように存在しないよ!とエラーが出てしまうのが本記事で紹介する問題です。
既存の解決法を試してみた結果
色々調査してみると、同様のエラーとその解決法を紹介しているサイトが複数見つかったので[2][3]、それらをまとめます。
原因1: 参照したいテーブルがスキーマ検索パスに含まれていない
そもそもデータベースはデータベース > スキーマ > テーブルのような階層構造をとっています。
例えばあるデータベースが次のような構造で、テーブル
- データベース
- スキーマ
A - テーブル
A_1 - テーブル
A_2
- テーブル
- スキーマ
B - テーブル
B_1 - テーブル
B_2
- テーブル
- スキーマ
このとき、スキーマ検索パスにスキーマ
実際に、自分のDBのスキーマ探索パスを調べてみると以下のようになりました。
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | user_location | table | postgres
postgres=# show search_path;
search_path
-----------------
"$user", public
テーブルuser_location
が属しているスキーマpublic
(postgresがデフォルトで作成するスキーマ)は問題なくスキーマ検索パスに含まれていることがわかります。
原因2: テーブル名に大文字と小文字が混在している
テーブル定義を行うときにテーブルの名前をmixed-caseで定義すると同様の問題が起こるそうです。この場合の原因は、SQL文で指定したテーブル名をpostgresqlが勝手に小文字と認識してしまうことだそう。TEST_TABLE
という名前のテーブルがあったとき、
- SELECT * FROM TEST_TABLE → SELECT * FROM test_table
- SELECT * FROM "TEST_TABLE" → SELECT * FROM TEST_TABLE
のように、ダブルクオーテーションで囲ってやることで大文字を大文字として認識させることが可能になります。
しかし、私が問題を抱えているテーブルの名前はuser_location
つまり、lower-caseで命名されているのでこの解決策も効きませんでした。
原因と解決法
調べても調べても、私が直面しているエラーの原因は見つからず困っていると、ふと思ったのです。
そもそも参照しているデータベースが違う...?
データベースは、データベース > スキーマ > テーブル という構造をとると上述しましたが、これはそもそもデータベースから異なるものを参照しているのではないかという疑いを持ちます。
Flyから与えられたDB接続用のURLを環境変数として設定してアクセスしているため、データベースは正しいものだと思い込んでいました。
実際にデータベースクラスタに存在するデータベース一覧を確認してみると、
postgres=# select * from pg_database;
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
-------+---------------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------------
##### | postgres | 10 | 6 | en_US.utf8 | en_US.utf8 | f | t | -1 | 13756 | 727 | 1 | 1663 |
##### | {app_name} | 10 | 6 | en_US.utf8 | en_US.utf8 | f | t | -1 | 13756 | 727 | 1 | 1663 |
1 | template1 | 10 | 6 | en_US.utf8 | en_US.utf8 | t | t | -1 | 13756 | 727 | 1 | 1663 | {=c/flypgadmin,flypgadmin=CTc/flypgadmin}
##### | template0 | 10 | 6 | en_US.utf8 | en_US.utf8 | t | f | -1 | 13756 | 727 | 1 | 1663 | {=c/flypgadmin,flypgadmin=CTc/flypgadmin}
データベースが4つ存在する!!
おそらくデフォルトで存在するであろうtemplate0
, template1
, postgres
の3つに加えて、{app_name}
という名前のデータベースが確認できます。
flyctlコマンド(flyctl postgres connect -a {Application name})によって接続されるデータベースはpostgres
でしたが、これを{app_name}
に変えて、user_location
テーブルを作成します。
$ flyctl proxy 5455:5432 -a {app_db_name}
$ psql postgres://postgres:{secret_key}@localhost:5455/{app_name}
{app_name}=# CREATE TABLE user_location( ... )
そして、再度 LINEBOT を動かしてみると、user_location
テーブルが見つからない問題は発生せず、無事動くようになりました🎉
Discussion