A few days ago I posted a question on general concerning the new feature SKIP LOCKED in PostgreSQL 9.5-dev.

For the orginal question can be found here: http://www.postgresql.org/message-id/54f723c3.1020...@boerngen-schmidt.de


What I'm trying to archieve is to match a point from my data which meets certain conditions to a given point. The given points are selected by 'Select id as "start" ....', then the left lateral join should go through each given point and find a corresponding point which is not in the same region and between 5 and 10 km away. If I did understand the lateral join right, it will be executed like a loop for every previously selected row in a non lateral statement.

The Lateral statement does return a result, which I do not expect. I returns an end point multiple times for diverent start points. Why? I thought, that the selected point by the lateral is locked by the FOR UPDATE and if the lateral selects a point that is already locked it will be skipped by the SKIP LOCKED.

SELECT
   start,
   destination,
   ST_Distance(start_geom, end_geom) AS distance_meter
   FROM (
     SELECT id as "start", geom as start_geom
     FROM de_sim_points_start
     WHERE NOT used AND rs = '057700032032'
     ORDER BY RANDOM()
     LIMIT 200
     FOR UPDATE SKIP LOCKED
   ) AS s
LEFT JOIN LATERAL (
     SELECT id as destination, geom as end_geom
     FROM de_sim_points_end
     WHERE NOT used AND rs IN (
       SELECT sk.rs
       FROM de_commuter_kreise ck
         INNER JOIN de_shp_kreise sk
           ON sk.rs = ck.rs AND ST_DWithin((SELECT ST_Union(geom) FROM
de_shp WHERE rs ='057700032032'), sk.geom, 5000)
       UNION
       SELECT cg.rs
       FROM de_commuter_gemeinden cg
         INNER JOIN de_shp_gemeinden sg
           ON sg.rs = cg.rs AND ST_DWithin((SELECT ST_Union(geom) FROM
de_shp WHERE rs = '057700032032'), sg.geom, 5000)
     )
        AND NOT ST_DWithin(geom, start_geom, 2000) AND ST_DWithin(geom,
start_geom, 5000)
     FOR UPDATE SKIP LOCKED
     LIMIT 1
) AS e ON TRUE

What I think the query is doing:
1. It SELECTs the startpoints
2. Then for each row it selects in the lateral join a corresponding endpoint
2.1 skipping already locked endpoints and find the next not lock one
2.2 While it selects this endpoint it is lock due to the FOR UPDATE
3. Result is presented

BUT what happens is, that I get the same endpoint a couple of times. It seems to me that the lateral join does not evaluate the SKIP LOCKED right, since this endpoint occurs multiple times. Is this a bug or a feature?

p.s.
I also would be glad if you guys could tell me a better way to match points as I would really help me with my mastersthesis.

- Benjamin




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to