pg_pathman icon indicating copy to clipboard operation
pg_pathman copied to clipboard

Published 20 hours ago • verified publisher icon postgrespro

cannot update SecondarySnapshot during a parallel operation (with postgis)

Open Toexplore opened this issue 4 years ago • 0 comments

Problem description

Environment:

  • postgres 11
  • pathman 1.5.8
  • postgis 2.5.2

SQL Statement

CREATE OR REPLACE FUNCTION utmzone(geometry)
  RETURNS integer AS
$BODY$
DECLARE
    geomgeog geometry;
    zone int;
    pref int;

BEGIN
    geomgeog:= ST_Transform($1,4326);

    IF (ST_Y(geomgeog))>0 THEN
       pref:=32600;
    ELSE
       pref:=32700;
    END IF;

    zone:=floor((ST_X(geomgeog)+180)/6)+1;
    IF ( zone > 60 ) THEN zone := 60; END IF;

    RETURN zone+pref;
END;
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE
  COST 100;

INSERT INTO utm_dots SELECT geography(ST_SetSRID(ST_Point(i,j),4326)) As the_geog, utmzone(ST_SetSRID(ST_Point(i,j),4326)) As utm_srid FROM generate_series(-179,179,0.1) As i CROSS JOIN generate_series(-89,89,0.1) As j;

CREATE TABLE utm_dots ( the_geog geography, utm_srid integer);

SELECT '#304.a', Count(*) FROM utm_dots WHERE ST_DWithin(the_geog, 'POINT(0 0)'::geography, 3000000);

Errors（important! with postgres parallel query）

When we execute statement above in parallel query model (set force_parallel_mode TO on), the following error always occure:

cannot update SecondarySnapshot during a parallel operation

Why?

The sql statement above uses the datatype "geography", actually, it will call a new sql statment " select xxx from spatial_ref_sys" internal by spi_exec, call stack:

  • Statement 1: SELECT '#304.a', Count(*) FROM utm_dots WHERE ST_DWithin(the_geog, 'POINT(0 0)'::geography, 3000000); (leader progress)
    • SubStatement 2: SELECT proj4text FROM spatial_ref_sys WHERE srid = %d LIMIT 1 (new spi_connect/spi_execute, worker progress)

That's ok without postgres parallel query. But in parallel query model, the Statement 1 is planned and executed in leader progress, the SubStatement 2 is executed only in workers progress(not leader progress). In pg_pathman, it uses GetLatestSnapshot() for pathman_config_contains_relation() and errors occur. I think this is the problem, can GetCatalogSnapshot replace GetLatestSnapshot？

The call stack below:

#0  0x00007fe8b1f064eb in raise () from /lib64/libpthread.so.0
#1  0x0000000000bb2743 in polar_program_error_handler (postgres_signal_arg=11) at postgres.c:5663
#2  <signal handler called>
#3  GetLatestSnapshot () at snapmgr.c:392
#4  0x00007fe8a1e541b4 in pathman_config_contains_relation (relid=32767, values=0x7fff458e85e0, isnull=<optimized out>, xmin=0x0, iptr=0x7fff458e85d0) at src/init.c:653
#5  0x00007fe8a1e58d3e in get_pathman_relation_info (relid=<optimized out>) at src/relation_info.c:330
#6  0x00007fe8a1e58f2f in get_pathman_relation_info (relid=1) at src/relation_info.c:370
#7  0x00007fe8a1e73bf8 in disable_standard_inheritance (parse=<optimized out>, context=<optimized out>) at src/planner_tree_modification.c:481
#8  0x00007fe8a1e73d5f in pathman_transform_query_walker (node=0x4026748, context=0x7fff458e81bc) at src/planner_tree_modification.c:387
#9  0x00007fe8a1e73df6 in pathman_transform_query_walker (node=0x0, context=0x7fff458e81bc) at src/planner_tree_modification.c:353
#10 0x00007fe8a1e6eae4 in pathman_planner_hook (parse=0x0, cursorOptions=1166967228, boundParams=0x72) at src/hooks.c:672
#11 0x0000000000a90d5f in planner (parse=parse@entry=0x4026748, cursorOptions=cursorOptions@entry=379539968, boundParams=boundParams@entry=0x0) at planner.c:269
#12 0x0000000000bb4ece in pg_plan_query (querytree=querytree@entry=0x4026748, cursorOptions=379539968, cursorOptions@entry=256, boundParams=boundParams@entry=0x0)
    at postgres.c:906
#13 0x0000000000bb5059 in pg_plan_queries (querytrees=querytrees@entry=0x402aa18, cursorOptions=256, boundParams=boundParams@entry=0x0) at postgres.c:972
#14 0x0000000000ce7bfb in BuildCachedPlan (plansource=plansource@entry=0x40264b8, qlist=0x402aa18, qlist@entry=0x0, boundParams=boundParams@entry=0x0, 
--Type <RET> for more, q to quit, c to continue without paging--
    queryEnv=queryEnv@entry=0x0) at plancache.c:938
#15 0x0000000000ce8459 in GetCachedPlan (plansource=plansource@entry=0x40264b8, boundParams=boundParams@entry=0x0, useResOwner=<optimized out>, queryEnv=0x0)
    at plancache.c:1213
#16 0x00000000009dfab2 in _SPI_execute_plan (plan=plan@entry=0x7fff458e91d0, paramLI=paramLI@entry=0x0, snapshot=snapshot@entry=0x0, 
    crosscheck_snapshot=crosscheck_snapshot@entry=0x0, read_only=read_only@entry=true, fire_triggers=fire_triggers@entry=true, tcount=tcount@entry=1) at spi.c:2180
#17 0x00000000009e0197 in SPI_execute (src=src@entry=0x7fff458e9240 "SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1", read_only=read_only@entry=true, 
    tcount=tcount@entry=1) at spi.c:462
#18 0x00007fe8a1483d67 in GetProj4StringSPI (srid=srid@entry=4326) at /home/postgres/ganos/sql4pg/geometry/common/lwgeom_transform.c:366
#19 0x00007fe8a1483f47 in GetProj4String (srid=4326) at /home/postgres/ganos/sql4pg/geometry/common/lwgeom_transform.c:415
#20 AddToPROJ4SRSCache (other_srid=4326, srid=4326, PROJ4Cache=0x4000620) at /home/postgres/ganos/sql4pg/geometry/common/lwgeom_transform.c:514
#21 AddToPROJ4Cache (cache=cache@entry=0x4000620, srid=srid@entry=4326, other_srid=other_srid@entry=4326) at /home/postgres/ganos/sql4pg/geometry/common/lwgeom_transform.c:494
#22 0x00007fe8a14848dd in GetProjectionsUsingFCInfo (fcinfo=fcinfo@entry=0x4022128, srid1=4326, srid2=4326, pj1=pj1@entry=0x7fff458e9470, pj2=pj2@entry=0x7fff458e9480)
    at /home/postgres/ganos/sql4pg/geometry/common/lwgeom_transform.c:727
#23 0x00007fe8a14849b7 in spheroid_init_from_srid (fcinfo=fcinfo@entry=0x4022128, srid=<optimized out>, s=s@entry=0x7fff458e94e0)
    at /home/postgres/ganos/sql4pg/geometry/common/lwgeom_transform.c:748
#24 0x00007fe8a1496cfb in geography_dwithin (fcinfo=0x4022128) at /home/postgres/ganos/sql4pg/geometry/operation/geography_measurement.c:301
--Type <RET> for more, q to quit, c to continue without paging--
#25 0x000000000098a49d in ExecInterpExpr (state=0x4000bc8, econtext=0x4000078, isnull=0x7fff458e971f) at execExprInterp.c:678
#26 0x000000000099dae1 in ExecEvalExprSwitchContext (isNull=0x7fff458e971f, econtext=0x4000078, state=0x4000bc8) at ../../../src/include/executor/executor.h:335
#27 ExecQual (state=state@entry=0x4000bc8, econtext=econtext@entry=0x4000078) at ../../../src/include/executor/executor.h:404
#28 0x000000000099de16 in ExecScan (node=0x4000390, accessMtd=accessMtd@entry=0x9cdef9 <SeqNext>, recheckMtd=recheckMtd@entry=0x9cde20 <SeqRecheck>) at execScan.c:190
#29 0x00000000009cded7 in ExecSeqScan (pstate=<optimized out>) at nodeSeqscan.c:143
#30 0x000000000099b1b0 in ExecProcNodeFirst (node=0x4000390) at execProcnode.c:474
#31 0x000000000098f1ed in ExecProcNode (node=0x4000390) at ../../../src/include/executor/executor.h:269
#32 ExecutePlan (estate=estate@entry=0x3fff9b8, planstate=0x4000390, use_parallel_mode=false, operation=operation@entry=CMD_SELECT, sendTuples=sendTuples@entry=true, 
    numberTuples=numberTuples@entry=0, direction=direction@entry=ForwardScanDirection, dest=dest@entry=0x3f8ee58, execute_once=execute_once@entry=true) at execMain.c:2763
#33 0x0000000000995efe in standard_ExecutorRun (queryDesc=queryDesc@entry=0x3f8fa98, direction=direction@entry=ForwardScanDirection, count=count@entry=0, 
    execute_once=execute_once@entry=true) at execMain.c:1023
#34 0x00007fe8a2ec9c7b in pgsp_ExecutorRun (queryDesc=0x3f8fa98, direction=ForwardScanDirection, count=0, execute_once=<optimized out>) at polar_stat_plans.c:921
#35 0x00007fe8a2ab1da0 in pgss_ExecutorRun (queryDesc=0x3f8fa98, direction=ForwardScanDirection, count=0, execute_once=<optimized out>) at pg_stat_statements.c:923
#36 0x00007fe8a26a8609 in explain_ExecutorRun (queryDesc=0x3f8fa98, direction=ForwardScanDirection, count=0, execute_once=<optimized out>) at auto_explain.c:268
#37 0x00007fe8a1c3ecce in pgc_ExecutorRun (queryDesc=0x3f8fa98, direction=ForwardScanDirection, count=0, execute_once=<optimized out>) at polar_concurrency_control.c:1184
#38 0x0000000000995fa3 in ExecutorRun (queryDesc=queryDesc@entry=0x3f8fa98, direction=direction@entry=ForwardScanDirection, count=0, execute_once=execute_once@entry=true)
--Type <RET> for more, q to quit, c to continue without paging--
    at execMain.c:775
#39 0x00000000009980d7 in ParallelQueryMain (seg=0x3e230d0, toc=0x7fe8a17c2000) at execParallel.c:1402
#40 0x00000000007c4826 in ParallelWorkerMain (main_arg=<optimized out>) at parallel.c:1419
#41 0x0000000000ad19d2 in StartBackgroundWorker () at bgworker.c:875
#42 0x0000000000ae25de in do_start_bgworker (rw=rw@entry=0x3e44b50) at postmaster.c:6149
#43 0x0000000000ae29a0 in maybe_start_bgworkers (polar_start_mount_process=polar_start_mount_process@entry=false) at postmaster.c:6372
#44 0x0000000000ae4696 in sigusr1_handler (postgres_signal_arg=<optimized out>) at postmaster.c:5508
#45 <signal handler called>
#46 0x00007fe8af0acfc3 in __select_nocancel () from /lib64/libc.so.6
#47 0x0000000000ae4bb2 in ServerLoop () at postmaster.c:1757
#48 0x0000000000ae6159 in PostmasterMain (argc=argc@entry=5, argv=argv@entry=0x3de3fc0) at postmaster.c:1459
#49 0x0000000000a185cf in main (argc=5, argv=0x3de3fc0) at main.c:228

Toexplore avatar Aug 03 '20 02:08 Toexplore