Zenn
🐴

fly.io postgres: relation "table_name" does not exist. の解決

2022/12/11に公開
Heroku
PostgreSQL
#
fly
tech

はじめに

お世話になっていた Heroku が2022/11/28から無料枠を廃止してしまいました。引っ越し先として Fly.io に LINEBOT のアプリケーションを移行させた際につまづいた問題の話です。

https://fly.io/

[補足]
Heroku -> Fly.io への移行は、ベル15の開発ブログ様の記事[1]を参考に行いましたが、非常に簡単なものとなっています。

  1. Homebrewでfly.ioが提供しているコマンドラインツールflyctlをインストール
Terminal
$ brew install flyctl
  1. fly.ioへログイン
Terminal
$ flyctl auth login
  1. ソースコードがあるディレクトリに移動し、アプリケーションを新たに作る。アプリ名, 地域, postgresql, redisの使用有無を設定すれば終了です。これも指示に従って答えていけば一瞬で終わります。
Terminal
$ flyctl launch
  1. デプロイする
Terminal
$ 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テーブルが存在しないかどうか確かめてみます。

Terminal
$ 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_1の検索が目的だとしましょう。

  • データベース
    • スキーマA
      • テーブルA_1
      • テーブルA_2
    • スキーマB
      • テーブルB_1
      • テーブルB_2

このとき、スキーマ検索パスにスキーマAが含まれていないとテーブルA_1の検索ができないというわけです。

実際に、自分の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テーブルを作成します。

Terminal
$ 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テーブルが見つからない問題は発生せず、無事動くようになりました🎉

脚注

  1. https://bel-itigo.com/migrate-from-heroku-to-flyio/ ↩︎

  2. https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/relation-does-not-exist/ ↩︎

  3. https://stackoverflow.com/questions/695289/cannot-simply-use-postgresql-table-name-relation-does-not-exist#:~:text=I had problems,you have to %3B) ↩︎

Discussion