Have encountered an intriguing issue processing a table with a large number of rows. The process is to loop over the table processing each row executing the Apache AGE cypher function over each row individually.The looping is necessary because of encountering a limit on the number of rows that the cypher function would process.
The process terminates unexpectedly with the following message. Notable that it runs for quite some time before termination.:
| 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 |
Of note the the string refers to a value in the field
service_key.
The first instance of the service_key when ordered is in row 7741 shown below.
| row_num | service_key | service_id | trip_id | trip_headsign | route_id | direction_id | shape_id | wheelchair_accessible |
| 7741 | a01a724103fbb3d059b8387bf043dbc8 | FR | 307 | Gungahlin Pl | X1 | 0 | 1002 | 1 |
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 attributes of the table allservices.trips are as follows:
- Total size with indexes: 60 MB
- Number of rows: 231,131
This is the function definition and the process to load the table allservices.trips into the Apache AGE graph schema.
Any assistance in refining the process to ensure completion welcome.
Regards
Matt.
|
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; $$;
|
