[postgis-tickets] [PostGIS] #4129: Cannot update SecondarySnapshot during a parallel operation

[PostGIS]

#4129: Cannot update SecondarySnapshot during a parallel operation
---------------------+----------------------------------------
  Reporter:  davidp  |      Owner:  Bborie Park
      Type:  defect  |     Status:  new
  Priority:  high    |  Milestone:  PostGIS 2.4.6
 Component:  raster  |    Version:  2.4.x
Resolution:          |   Keywords:  SecondarySnapshot parallel
---------------------+----------------------------------------

Comment (by davidp):

 Yes, we are using synchronous streaming replication. Pretty simle scenario
 with one Master and one Hot Stanby.

 There is SQL query that causes errors:

 {{{
 with adrm as( select *, ST_Distance( ST_TRANSFORM( ST_SETSRID(
 ST_MAKEPOINT( $1, $2 ), $4 ), 5514 ), definicni_bod) distance
         from ruian.rn_adresni_misto
         where ST_DWithin( ST_TRANSFORM( ST_SETSRID( ST_MAKEPOINT( $1, $2
 ), $4 ), 5514 ), definicni_bod, $5 )
         order by distance asc limit $3 )
 select json_agg( json_build_object( 'address', address, 'coords', coords,
 'distance', distance )) as result
         from(select json_build_object( 'adrm_kod', adrm.kod, 'kraj',
 ruian.rn_vusc.nazev, 'kraj_kod', ruian.rn_okres.vusc_kod, 'okres',
 ruian.rn_okres.nazev, 'okres_kod', ruian.rn_obec.okres_kod, 'obec',
 ruian.rn_obec.nazev, 'obec_kod', ruian.rn_obec.kod, 'cast_obce',
 ruian.rn_cast_obce.nazev, 'cast_obce_kod', ruian.rn_cast_obce.kod,
 'ulice', ruian.rn_ulice.nazev, 'ulice_kod', ruian.rn_ulice.kod,
 'stavebni_objekt', ruian.rn_stavebni_objekt.kod, 'co',
 cislo_orientacni_hodnota, 'co_pism', cislo_orientacni_pismeno, 'cd',
 cislo_domovni, 'psc', adrp_psc ) address,
                         st_asgeojson( ST_Transform( adrm.definicni_bod, $4
 ))::json -> 'coordinates' coords,
                         round( adrm.distance::numeric, 2 ) distance
                         from adrm
                         left join ruian.rn_ulice on
                         adrm.ulice_kod = ruian.rn_ulice.kod
                         left join ruian.rn_stavebni_objekt on
                         ruian.rn_stavebni_objekt.kod = adrm.stavobj_kod
                         left join ruian.rn_cast_obce on
                         ruian.rn_cast_obce.kod =
 ruian.rn_stavebni_objekt.cobce_kod
                         left join ruian.rn_obec on
                         ruian.rn_ulice.obec_kod = ruian.rn_obec.kod
                         or ruian.rn_cast_obce.obec_kod = ruian.rn_obec.kod
                         left join ruian.rn_okres on
                         ruian.rn_obec.okres_kod = ruian.rn_okres.kod
                         left join ruian.rn_vusc on
                         ruian.rn_okres.vusc_kod = ruian.rn_vusc.kod) a;
 }}}

 where:
 $1 = X coordinate,
 $2 = Y coordinate,
 $3 = row limit,
 $4 = coordinates SRID,
 $5 = search radius

 Error appears randomly when this query is executed in batch mode - eg 20k
 of executions... When i transformed this query into function (parallel
 unsafe), errors disappears.

 Hope that helps...

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4129#comment:7>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.