pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUnexpected termination looping over table.
3+ messages / 2 participants
[nested] [flat]
* Unexpected termination looping over table.
@ 2023-10-02 00:57 Matt Gibbins <matt_gibbins@fastmail.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: Matt Gibbins @ 2023-10-02 00:57 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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; $$;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unexpected termination looping over table.
@ 2023-10-02 01:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matt Gibbins <matt_gibbins@fastmail.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2023-10-02 01:09 UTC (permalink / raw)
To: Matt Gibbins <matt_gibbins@fastmail.com.au>; +Cc: pgsql-performance@lists.postgresql.org
Matt Gibbins <matt_gibbins@fastmail.com.au> writes:
> 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
There is no occurrence of "could not find rte" anywhere in the
core Postgres source code. I surmise that you're using some
extension that isn't happy.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unexpected termination looping over table.
@ 2023-10-02 02:36 Matt Gibbins <matt_gibbins@fastmail.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Matt Gibbins @ 2023-10-02 02:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
On 2/10/23 12:09, Tom Lane wrote:
> Matt Gibbins <matt_gibbins@fastmail.com.au> writes:
>> 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
> There is no occurrence of "could not find rte" anywhere in the
> core Postgres source code. I surmise that you're using some
> extension that isn't happy.
>
> regards, tom lane
Tom
Thanks for your response. I am using the Apache AGE extension which
enables graph data processing on PostgreSQL.
https://age.apache.org/
I'll enquire with the Apache AGE list for an answer.
Regards
Matt.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-02 02:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-02 00:57 Unexpected termination looping over table. Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-10-02 01:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-02 02:36 ` Matt Gibbins <matt_gibbins@fastmail.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox