Re: Potential "AIO / io workers" inter-worker locking issue in PG18?

Hi Thom,

As an extension to what I already wrote: as the processing gets stuck 
during UPDATEs, I realized the pg_aios view is likely not involved, as 
the current AIO implementation of PG18 only affects the read operations 
like sequential and bitmap heap scans.

So not seeing anything listed in the pg_aios view might be normal? That 
said, I have attempted to view and refresh the view during other stages 
of the processing, with pgAdmin showing apparently read operations, but 
still no records displayed in pg_aios. Maybe I am hitting the "refresh" 
button on the wrong time though...

But maybe the whole new AIO thing isn't involved in these, and it is 
another issue in PG18. Just to summarize my observations once again:

- Multi-threaded processing implemented in Python using pyodbc and 
concurrent.futures apparently getting stuck waiting for PostgreSQL to 
return. The processing step involved should return in ***less than 10 
seconds*** for the small Italy extract, but can take >1h (up to >6) when 
it gets randomly stuck (some runs successful without delay, others not).

- pgAdmin showing all sessions associated with the threads as 'Active' 
with no wait events nor blocking PIDs during the whole time the 
processing appears stuck in PostgreSQL.

- No other sessions like VACUUM visible in pgAdmin during the time the 
processing appears stuck except the main 'postgres' user session.

- All locks as shown in pg_locks are granted, and most if not all are 
fastpath, with only AccessShareLock and RowExclusiveLock on the table 
and its indexes involved. A couple of ExclusiveLock on virtualxid and 
transactionid.

- 'Top' in Ubuntu showing multiple backend 'postgres' processes 
continuously at high core usage, one for each thread (each Python thread 
of course uses its own connection).

- pg_aios view empty, but the processing is UPDATEs, so probably no 
surprise.

- The processing *DOES* eventually continue after this particular 
anomaly, with no further consequences and expected results at the end of 
the total processing flow, so it is not a true dead-lock.

- I have noticed it gets stuck when processing OpenStreetMap scrub or 
grassland of the Italy extract of Geofabrik. However, as written above, 
some processing runs are fine on the same data, while others get stuck 
and delayed. The issue may or may not involve PostGIS though considering 
this and the fact that the processing step getting stuck involves 
PostGIS functions.

- In pgAdmin, the SQL statements as generated by my geoprocessing 
workflow and as being processed by PostgreSQL when the processing is 
stuck, look like this:

UPDATE osm.landcover_scrubs_small_scale_2_ply AS t1 SET area_geo = 
t2.area_geo, perim_geo = t2.perim_geo, compact_geo = CASE WHEN 
t2.area_geo > 0 THEN ((power(t2.perim_geo,2) / t2.area_geo) / (4 * 
pi())) ELSE 0 END, npoints_geo = t2.npoints_geo, comp_npoints_geo = CASE 
WHEN t2.npoints_geo > 0 THEN (CASE WHEN t2.area_geo > 0 THEN 
((power(t2.perim_geo,2) / t2.area_geo) / (4 * pi())) ELSE 0 END / 
t2.npoints_geo) ELSE 0 END, convex_ratio_geo = CASE WHEN 
ST_Area(ST_ConvexHull(way)::geography,true) > 0 THEN (t2.area_geo / 
ST_Area(ST_ConvexHull(way)::geography,true)) ELSE 1 END FROM (SELECT 
objectid,ST_Area(way::geography,true) AS 
area_geo,ST_Perimeter(way::geography,true) AS perim_geo,ST_NPoints(way) 
AS npoints_geo FROM osm.landcover_scrubs_small_scale_2_ply)  AS t2 WHERE 
(t2.objectid = t1.objectid) AND t1.objectid IN (SELECT t3.objectid FROM 
mini_test.osm.osm_tmp_28128_ch5 AS t3)

- All of this worked fine in PG <= 17.

Marco

>
> So, to confirm, you get the issue with as little as 3 io_workers?
>
> Also, what is pg_aios telling you during this time?
>
> Thom
>