PgRouting/pgr_dijkstra: How to log output, i.e raising a notice message after every 100, 500, 0r 1.000 calculated routes or so.

[adeways2000]

I am writing a code PL/pgSQL that processes both input layers (households[points] and network[linestrings]).

Expectation

The processing should produce an output layer with all routes/connections from every household to every other household. Given an input layer with around 1.000 (n) households the resulting routes/connections layer should have around 1.000.000 routes/connections (n * n).

Problem

I want the procedure to produce some logging output, i.e raising a notice message after every 1.000 calculated routes or so.

Sample Data

set client_min_messages = default;
set search_path = working_schema, pg_default, public;

do
$$
declare

string1 varchar;
begin

--Creates Source and Target columns if they are not existing
ALTER TABLE roadlines
	ADD COLUMN if not exists source integer;

ALTER TABLE roadlines
	ADD COLUMN if not exists target integer;
		
--Create topology	
SELECT pgr_createTopology INTO string1('roadlines', 0.001, 'geom', clean := 'true'); 
RAISE notice '% - pgr_createTopology: %', substr(cast(clock_timestamp() as varchar), 12, 8), string1;

--Create node network	
SELECT pgr_nodeNetwork INTO string1('roadlines',0.001, 'id', 'geom');
RAISE notice '% - pgr_nodeNetwork: %', substr(cast(clock_timestamp() as varchar), 12, 8), string1;

--Create topology for the noded table
SELECT pgr_createTopology INTO string1('roadlines_noded', 0.001, 'geom', clean := 'true'); 
RAISE notice '% - pgr_createTopology: %', substr(cast(clock_timestamp() as varchar), 12, 8), string1;

--Adds the length column
ALTER TABLE roadlines_noded
	ADD COLUMN d_cost_length double precision;
RAISE notice '% - the length column is Created: ', substr(cast(clock_timestamp() as varchar), 12, 8);

--Fills the length column which will be considered as cost
UPDATE roadlines_noded SET d_cost_length = ST_Length(ST_Transform(geom, 25832)::geometry);
RAISE notice '% - the d_cost_length is Calculated: ', substr(cast(clock_timestamp() as varchar), 12, 8);

--Identifies the potential problems on the graph
SELECT pgr_analyzegraph INTO string1('roadlines_noded', 0.001, 'geom');
RAISE notice '% - pgr_analyzegraph: %', substr(cast(clock_timestamp() as varchar), 12, 8), string1;

--Creates a new Djikstira result table
CREATE TABLE dijkstra_routing_table as
SELECT *
	FROM pgr_dijkstra('SELECT id, source::INT4, target::INT4, d_cost_length AS cost FROM roadlines_noded',
	
		-- Collects the array of start_vid,  end_vid, only when the intersection between a roadlines layer and a point layer is true
		-- Buffer is applied to the point layer in order to facilitate the intersection of a point layer and a line layer.
		(	
			SELECT array_agg(distinct(case when st_intersects(ST_StartPoint(w_noded.geom), st_buffer(adr_pts.geom,0.1)) then source else target end))
			FROM roadlines_noded as w_noded
			INNER JOIN housepoints as adr_pts on st_intersects(w_noded.geom, st_buffer(adr_pts.geom))),
				  
		(	SELECT array_agg(distinct(case when st_intersects(ST_StartPoint(w_noded.geom), st_buffer(adr_pts.geom, 0.1)) then source else target end))
			FROM roadlines_noded as w_noded
			INNER JOIN housepoints as adr_pts on st_intersects(w_noded.geom, st_buffer(adr_pts.geom))))
			
	INNER JOIN roadlines_noded ON edge = id
	ORDER BY seq;

RAISE notice '% - djikstra_routing_table is Created: ', substr(cast(clock_timestamp() as varchar), 12, 8);

		
RAISE notice '% - Ende', substr(cast(clock_timestamp() as varchar), 12, 8);
RAISE notice ' ';
end;
$$
language plpgsql;

Platform/versions

PostgreSQL 14.1, compiled by Visual C++ build 1914, 64-bit
POSTGIS="3.2.0 3.2.0" [EXTENSION] PGSQL="140" GEOS="3.10.1-CAPI-1.16.0" PROJ="7.2.1"
pgr_version: 3.3.0
PgAdmin 4

[cvvergara]

What is been asked for

It can not be done due to the way postgres function works:
https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-C-RETURN-SET
Basically what is says in pseudo code where pgRouting (nor any extension/function coded with C) does not have control over what the SRF_ postgres macros.

fn (data) {
if (SRF_IS_FIRSTCALL()) {
  calculate result
  save result in memory
  save row number to output in memory
}
retrieve result from memory
funcctx = SRF_PERCALL_SETUP();
get row number to output
if (funcctx->call_cntr < funcctx->max_calls) {
   prepare output row
   SRF_RETURN_NEXT(funcctx, result);
} else {
  SRF_RETURN_DONE(funcctx);
}
}

So when a SELECT * FROM fn id done
function fn is called once per row output, where in the first call the calculations are performed, and in all the calls a row is returned

So if seq represents the i'th call, then before the first row everything is calculated

SELECT * FROM pgr_Dijkstra(
  'select id, source, target, cost, reverse_cost from edges',
  ARRAY[6, 1], 17);
 seq | path_seq | start_vid | node | edge | cost | agg_cost
-----+----------+-----------+------+------+------+----------
/* All rows are calculated */   1 |        1 |         1 |    1 |    6 |    1 |        0  
   2 |        2 |         1 |    3 |    7 |    1 |        1
   3 |        3 |         1 |    7 |    8 |    1 |        2
   4 |        4 |         1 |   11 |   11 |    1 |        3
   5 |        5 |         1 |   12 |   13 |    1 |        4
   6 |        6 |         1 |   17 |   -1 |    0 |        5
   7 |        1 |         6 |    6 |    4 |    1 |        0
   8 |        2 |         6 |    7 |    8 |    1 |        1
   9 |        3 |         6 |   11 |   11 |    1 |        2
  10 |        4 |         6 |   12 |   13 |    1 |        3
  11 |        5 |         6 |   17 |   -1 |    0 |        4
(11 rows)

I am not saying that this is impossible, but what comes to my mind, the code that would need to be modified first is postgresSQL's, like have an SRF_IS_1000_CALL where appropriate actions can take place. And then pgRouting's C/C++ code change the code to accomodate that need.

What I think it is needed

• suppose that table households_combinations has the calculations needed:

id	source	target
1	1	2
2	1	3
3	1	4
...
999999	1000	9999

Then (In pseudo code):

create function foo()
last_i = 1
FOR 1000 IN 1..1000000 LOOP BY 1000
   RETURN QUERY 
   SELECT * from pgr_dijkstra(< the edges >, 'SELECT * FROM households_combinations WHERE id >= last_i and id < i');
   RAISE NOTICE '%', i;
END LOOP;

[adeways2000]

I will look into this and if it works I will let everyone know.