ERROR: function pgr_dijkstra(unknown, start_vid => bigint, end_vid => bigint, directed => boolean) does not exist LINE 7: pgr_dijkstra ( ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. で、ずっと悩んでいる件について

2020年7月18日

	m_conn->prepare(                    // HACK 1. 2文を1文に(PQExec仕様)、2. JOIN周り、 3. ORDER BYを消せないか?
		"find_shortest_path_by_dijkstra_calc",
		R"(
			INSERT INTO s_dijkstra_memoization (source, target, node, edge, length_m)
			
				SELECT
					$1, $2, d.node, d.edge, coalesce(ways.length_m, 0) AS length_m
				FROM
					pgr_dijkstra (
						'SELECT gid AS id, source, target, length_m AS cost from ways',
						start_vid:=$1::bigint, end_vid:=$2::bigint, directed:=false::boolean
					) as d LEFT JOIN ways ON ways.gid = d.edge
			
			WHERE NOT EXISTS (SELECT source, target FROM s_dijkstra_memoization WHERE source = $1 AND target = $2);
		)",
					//RETURNING node, edge, length_m;
		2
	);

のところで、問題が発生しているのは分かるのだけど、私にとっては上記な呪文であり、さっぱり分からない。

とりあえず、psql から地道に部分的に動かしてみて、問題点を探っている。

ca_sim=# SELECT gid, source, target, length_m AS cost from ways;

gid | source | target | cost
------+--------+--------+--------------------
1 | 55 | 23 | 121.17749638577058
2 | 844 | 1 | 710.1279290280786
3 | 4807 | 2 | 40.26044319993349
4 | 6296 | 3 | 251.21876036796243
5 | 22 | 4 | 2181.7785758382133
6 | 18 | 6 | 435.2439693722031
7 | 4304 | 6 | 950.7269653866767
8 | 6289 | 7 | 29.068747479235597

となるが、

ca_sim=# SELECT * FROM pgr_dijkstra(
'SELECT gid, source, target, length_m AS cost FROM ways',
2, 3
);
ERROR: Column 'id' not Found
CONTEXT: SQL function "pgr_dijkstra" statement 1

となる。pgr_dijkstraを実行する為には、パラメータの名前まで注意しなければなならないらしい。

ca_sim=# SELECT * FROM pgr_dijkstra(
'SELECT gid AS id, source, target, length_m AS cost FROM ways',
2, 3
);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+--------------------+--------------------
1 | 1 | 2 | 342 | 224.26753559377073 | 0
2 | 2 | 229 | 8773 | 295.36268951709405 | 224.26753559377073
3 | 3 | 6359 | 8771 | 41.04204131422657 | 519.6302251108648
4 | 4 | 6357 | 344 | 136.12897946273213 | 560.6722664250914
5 | 5 | 231 | 8688 | 58.55503032553214 | 696.8012458878235
6 | 6 | 6294 | 8690 | 191.73693713817062 | 755.3562762133556
7 | 7 | 6296 | 4 | 251.21876036796243 | 947.0932133515262
8 | 8 | 3 | -1 | 0 | 1198.3119737194886
(8 rows)

くるしまぎれに、

start_vid:=$1::bigint, end_vid:=$2

の部分に、

1、2

を、実数でベタ書きしたら、エラーが取れて、シミュレーションが走りだした(原因不明)

しかし、

start_vid:=$1::bigint, end_vid:=$2 → $1, $2

では、

ERROR: function pgr_dijkstra(unknown, unknown, unknown, directed => boolean) is not unique
LINE 7: pgr_dijkstra(
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.

Error:ERROR: function pgr_dijkstra(unknown, unknown, unknown, directed => boolean) is not unique
LINE 7: pgr_dijkstra(
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.

となってしまう。


ブログを読んで頂いたからから、以下のアドバイスを頂いたので、その観点より挑戦。

上の箇所の$1や$2は、SQLインジェクションされないためのprepare文のプレースホルダーです。
ドキュメントの該当箇所は次のものです。
https://www.postgresql.org/docs/current/sql-prepare.html
$1と$2に該当するものをうしろから代入してあげれば動く(はず)と思います。
MySQLは、たしか「?」がプレースホルダで、SQLiteも「?」がプレースホルダ(https://docs.python.org/ja/3/library/sqlite3.html をみたら、合ってました)だったように覚えています。PostgreSQLは$+数字をつかうことになっています。

そこで、このページを印刷しながら、比較して、色々試してみた

                pgr_dijkstra(
                    (SELECT gid AS id, source, target, length_m AS cost from ways),
                    start_vid:=$1::bigint, end_vid:=$2::bigint, directed:=false::boolean
                ) as d LEFT JOIN ways ON ways.gid = d.edge

ERROR:  subquery must return only one column
LINE 8:       (SELECT gid AS id, source, target, length_m AS cost fr...
              ^

Error:ERROR:  subquery must return only one column
LINE 8:       (SELECT gid AS id, source, target, length_m AS cost fr...
            FROM
                pgr_dijkstra(
                    'SELECT gid AS id, source, target, length_m AS cost from ways',
                    (start_vid:=$1::bigint), (end_vid:=$2::bigint), directed:=false::boolean
                ) as d LEFT JOIN ways ON ways.gid = d.edge
ERROR:  syntax error at or near ":="
LINE 9:       (start_vid:=$1::bigint), (end_vid:=$2::bigint), direct...
                        ^

Error:ERROR:  syntax error at or near ":="
LINE 9:       (start_vid:=$1::bigint), (end_vid:=$2::bigint), direct...

すでに試してみたが、これもやってみた

                pgr_dijkstra(
                    'SELECT gid AS id, source, target, length_m AS cost from ways',
                    $1, $2, directed:=false::boolean
                ) as d LEFT JOIN ways ON ways.gid = d.edge
ERROR:  function pgr_dijkstra(unknown, unknown, unknown, directed => boolean) is not unique
LINE 7:      pgr_dijkstra(
             ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

Error:ERROR:  function pgr_dijkstra(unknown, unknown, unknown, directed => boolean) is not unique
LINE 7:      pgr_dijkstra(
             ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

なんかエラーが増えてきたようだ(unknownが3つになった)。

他の行と比較してみながら、start_vid と end_vid を消してみた(これが記載されていない別の行があったので)

            FROM
                pgr_dijkstra(
                    'SELECT gid AS id, source, target, length_m AS cost from ways',
                    $1::bigint, $2::bigint, directed:=false::boolean
                ) as d LEFT JOIN ways ON ways.gid = d.edge

これで動いた。

けど、理由はさっぱり分かりません。$1::bigint, $2::bigint がキャストの書式?

トライアンド&エラーでやろうとすると、こういう目にあう、という良い教訓として下さい。

2020年7月18日2020/07,江端さんの技術メモ

Posted by ebata