age: Unexpected termination on looping cypher query - could not find rte

Have encountered an intriguing issue with loading a table with a large number of rows into Apache AGE. The process is looping through PL/pgSQL. The looping terminates with the following message.

SQL Error [42703]: ERROR: could   not find rte for a01a724103fbb3d059b8387bf043dbc8     Where: PL/pgSQL function analysis.create_trips(text,text,text,text,text,text,integer,text,integer)   line 5 at EXECUTE

The database version is PostgreSQL 15.4 (Debian 15.4-2.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit. The Apache AGE version is 1.4.0 for PG15 built from the repository. The error results from the following process.

SELECT create_graph('transport_network');

CREATE OR REPLACE FUNCTION analysis.create_trips
(graph_name text,
service_key text,service_id text, trip_id text, trip_headsign text, route_id text, direction_id int, shape_id text, wheelchair_accessible int)
returns text
LANGUAGE plpgsql
VOLATILE
as $trips$
declare
nodename text := graph_name || '.' || 'trips';
BEGIN
execute
format ('select * from cypher(''%1$s'', $$match (v:routes {id: %6$s})
create(v)-[:USES]->
(t:trips
{service_key: %2$s, service_id: %3$s, id: %4$s, headsign: %5$s, route_id: %6$s, direction_id: %7$s, shape_id: %8$s,
wheelchair_accessible: %9$s})$$) as (t agtype);',
quote_ident(graph_name),
quote_ident(service_key),quote_ident(service_id),
quote_ident(trip_id),quote_ident(trip_headsign),
quote_ident(route_id),to_char(direction_id,'9'),quote_ident(shape_id),to_char(wheelchair_accessible,'9'));
return nodename;
END
$trips$
;

select create_vlabel('transport_network','trips');

do $$
declare temprow record;
graph_name text:='transport_network';
counter integer := 0 ;
begin
for temprow in select service_key, service_id, trip_id from allservices.trips
order by service_key,trip_id
loop
counter := counter+1; -- Prevent replication of row
perform
analysis.create_trips
(graph_name,
a.service_key, a.service_id,
a.trip_id, a.trip_headsign,
a.route_id, a.direction_id, a.shape_id,
a.wheelchair_accessible)
from
(select row_number() over (order by service_key,trip_id) as row_num,
service_key, service_id,
trip_id, trip_headsign,
route_id, direction_id, shape_id,
coalesce(wheelchair_accessible,0) as wheelchair_accessible from allservices.trips) a
where a.row_num=counter
;
end loop;
end; $$;

In this case the string after the rte in the error message refers to the service_key value in the table allservices.trips. The first instance of the record in this instance is.

row_num	service_key	service_id	trip_id	trip_headsign	route_id	direction_id	shape_id	wheelchair_accessible
7741	a01a724103fbb3d059b8387bf043dbc8	FR	307	Gunghalin Pl	X1	0	1002	1

The attributes of the table being processed are:

• Total size with indexes: 60MB
• Number of rows 231,131