Обсуждение: Left lateral join with for update and skip locked

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.1020206@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",
geomas start_geom     FROM de_sim_points_start     WHERE NOT used AND rs = '057700032032'     ORDER BY RANDOM()
LIMIT200     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
ANDST_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

Benjamin Börngen-Schmidt <benjamin@boerngen-schmidt.de> writes:
> 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.

It sounds like you think SKIP LOCKED means to skip rows locked by your own
transaction.  That's not what it does, AFAIK.  It skips rows you couldn't
get a lock on without waiting ... but if you already have a lock, that's
fine.
        regards, tom lane

Benjamin Börngen-Schmidt wrote:

> 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.

So you want the LATERAL to lock a row, such that that row is not
returned by the "s" arm of the left join in the same query because of
SKIP LOCKED?  That seems flawed to me: the row lock is considered
automatically granted if the would-be locker is the same transaction as
the lock holder.

I am too lazy to reverse engineer your schema.  Are de_sim_points_end
and de_sim_points_start views on the same table(s), or something like
that?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services