Unexpected termination looping over table.

[Matt Gibbins <ma...@fastmail.com.au>]

Have encountered an intriguing issue with loading a table with a large 
number of rows into Apache AGE.

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; $$;

Re: Unexpected termination looping over table.

[John Gemignani <jo...@bitnine.net>]

Hi Matt,

Would you be able to create an issue, bug, or question thread with this
over on Github?

https://github.com/apache/age/issues

This is generally where most of the activity for Apache AGE is. It also
allows us to track issues better.

Let me know if you have any additional questions.

Thank you in advance!

John

On Sun, Oct 1, 2023 at 7:58 PM Matt Gibbins <ma...@fastmail.com.au>
wrote:

>
> Have encountered an intriguing issue with loading a table with a large
> number of rows into Apache AGE.
>
> 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; $$;
>
>
>