Search Postgresql Archives

Left lateral join with for update and skip locked

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

I have a rather big query which should match id's from various tables together. To be able to use multiprocessing I'm currently using postgresql 9.5-dev, because of the SKIP LOCKED feature.

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?


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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux