Обсуждение: ERROR: XX000: cannot update SecondarySnapshot during a paralleloperation

On 3/12/19 7:54 PM, fuzk wrote:
> Dear Sir/Madam
> 
> I got an error when I execute the following select sentence.
> Would you please solve the problem for me?

What version of Postgres?

> Thank you .
> 
> Alan Fu.
> 
> postgres=# \set VERBOSITY verbose
> postgres=# SELECT 
> round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as 
> NUMERIC),4)||'KM' field_value from had_link;
> 
> ERROR:  XX000: cannot update SecondarySnapshot during a parallel operation
> CONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys 
> WHERE srid = 4326 LIMIT 1"
> parallel worker
> LOCATION:  GetLatestSnapshot, snapmgr.c:387

I'm guessing ST_length is not parallel safe.

What is your setting for?:

max_parallel_workers_per_gather

> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Mar 14, 2019 at 1:20 PM fuzk <fuzk80_76@163.com> wrote:
>
> Dear Adrian,
>
> My setting is as following.
>
> max_parallel_workers_per_gather=32
>
> I am looking forward to hearing from you.

What version of postgres and what version of postgis are you using ?

> At 2019-03-13 22:31:11, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
> >On 3/12/19 7:54 PM, fuzk wrote:
> >> postgres=# \set VERBOSITY verbose
> >> postgres=# SELECT
> >> round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as
> >> NUMERIC),4)||'KM' field_value from had_link;
> >>
> >> ERROR:  XX000: cannot update SecondarySnapshot during a parallel operation
> >> CONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys
> >> WHERE srid = 4326 LIMIT 1"
> >> parallel worker
> >> LOCATION:  GetLatestSnapshot, snapmgr.c:387

I'm not familiar at all with geography, but if I read the code
correctly, ST_Length / geography_length will call
spheroid_init_from_srid(), which eventuallly calls GetProj4StringSPI()
that run a query using SPI.  I'm not sure why exactly
GetLatestSnapshot() is called here, or if SPI is really allowed in
parallel workers, but that's the problem here apparently.  I'm adding
Paul as he'll probably have a way better answer than me.

On 3/14/19 6:14 AM, Julien Rouhaud wrote:
> On Thu, Mar 14, 2019 at 1:20 PM fuzk <fuzk80_76@163.com> wrote:
>>
>> Dear Adrian,
>>
>> My setting is as following.
>>
>> max_parallel_workers_per_gather=32
>>
>> I am looking forward to hearing from you.
> 
> What version of postgres and what version of postgis are you using ?
> 
>> At 2019-03-13 22:31:11, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
>>> On 3/12/19 7:54 PM, fuzk wrote:
>>>> postgres=# \set VERBOSITY verbose
>>>> postgres=# SELECT
>>>> round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as
>>>> NUMERIC),4)||'KM' field_value from had_link;
>>>>
>>>> ERROR:  XX000: cannot update SecondarySnapshot during a parallel operation
>>>> CONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys
>>>> WHERE srid = 4326 LIMIT 1"
>>>> parallel worker
>>>> LOCATION:  GetLatestSnapshot, snapmgr.c:387
> 
> I'm not familiar at all with geography, but if I read the code
> correctly, ST_Length / geography_length will call
> spheroid_init_from_srid(), which eventuallly calls GetProj4StringSPI()
> that run a query using SPI.  I'm not sure why exactly
> GetLatestSnapshot() is called here, or if SPI is really allowed in
> parallel workers, but that's the problem here apparently.  I'm adding
> Paul as he'll probably have a way better answer than me.
> 

https://trac.osgeo.org/postgis/ticket/4129
"Cannot update SecondarySnapshot during a parallel operation"

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/13/19 10:54 PM, fuzk wrote:
> Dear Adrian,
> 
> My setting is as following.
> 
> max_parallel_workers_per_gather=32

Not sure if it is possible without affecting other operations, but you 
could set the above to 0 to temporarily disable parallel queries and see 
if that eliminates the error.

Also see:

https://trac.osgeo.org/postgis/ticket/4129

> 
> I am looking forward to hearing from you.
> 
> Many thanks
> Alan.
> 
> 
> 
> 
> 
> 
> 
> At 2019-03-13 22:31:11, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
>>On 3/12/19 7:54 PM, fuzk wrote:
>>> Dear Sir/Madam
>>> 
>>> I got an error when I execute the following select sentence.
>>> Would you please solve the problem for me?
>>
>>What version of Postgres?
>>
>>> Thank you .
>>> 
>>> Alan Fu.
>>> 
>>> postgres=# \set VERBOSITY verbose
>>> postgres=# SELECT 
>>> round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as 
>>> NUMERIC),4)||'KM' field_value from had_link;
>>> 
>>> ERROR:  XX000: cannot update SecondarySnapshot during a parallel operation
>>> CONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys 
>>> WHERE srid = 4326 LIMIT 1"
>>> parallel worker
>>> LOCATION:  GetLatestSnapshot, snapmgr.c:387
>>
>>I'm guessing ST_length is not parallel safe.
>>
>>What is your setting for?:
>>
>>max_parallel_workers_per_gather
>>
>>> 
>>> 
>>
>>
>>-- 
>>Adrian Klaver
>>adrian.klaver@aklaver.com
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Mar 14, 2019 at 3:25 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 3/14/19 6:14 AM, Julien Rouhaud wrote:
> > On Thu, Mar 14, 2019 at 1:20 PM fuzk <fuzk80_76@163.com> wrote:
> >>
> >> Dear Adrian,
> >>
> >> My setting is as following.
> >>
> >> max_parallel_workers_per_gather=32
> >>
> >> I am looking forward to hearing from you.
> >
> > What version of postgres and what version of postgis are you using ?
> >
> >> At 2019-03-13 22:31:11, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
> >>> On 3/12/19 7:54 PM, fuzk wrote:
> >>>> postgres=# \set VERBOSITY verbose
> >>>> postgres=# SELECT
> >>>> round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as
> >>>> NUMERIC),4)||'KM' field_value from had_link;
> >>>>
> >>>> ERROR:  XX000: cannot update SecondarySnapshot during a parallel operation
> >>>> CONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys
> >>>> WHERE srid = 4326 LIMIT 1"
> >>>> parallel worker
> >>>> LOCATION:  GetLatestSnapshot, snapmgr.c:387
> >
> > I'm not familiar at all with geography, but if I read the code
> > correctly, ST_Length / geography_length will call
> > spheroid_init_from_srid(), which eventuallly calls GetProj4StringSPI()
> > that run a query using SPI.  I'm not sure why exactly
> > GetLatestSnapshot() is called here, or if SPI is really allowed in
> > parallel workers, but that's the problem here apparently.  I'm adding
> > Paul as he'll probably have a way better answer than me.
> >
>
> https://trac.osgeo.org/postgis/ticket/4129
> "Cannot update SecondarySnapshot during a parallel operation"

Ah, I didn't look there indeed, thanks!  So postgis people are already
aware, that's a good news.

On Thu, Mar 14, 2019 at 8:43 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> On Thu, Mar 14, 2019 at 3:25 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> >
> > On 3/14/19 6:14 AM, Julien Rouhaud wrote:
> > > On Thu, Mar 14, 2019 at 1:20 PM fuzk <fuzk80_76@163.com> wrote:
> > >>
> > >> Dear Adrian,
> > >>
> > >> My setting is as following.
> > >>
> > >> max_parallel_workers_per_gather=32
> > >>
> > >> I am looking forward to hearing from you.
> > >
> > > What version of postgres and what version of postgis are you using ?
> > >
> > >> At 2019-03-13 22:31:11, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
> > >>> On 3/12/19 7:54 PM, fuzk wrote:
> > >>>> postgres=# \set VERBOSITY verbose
> > >>>> postgres=# SELECT
> > >>>> round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as
> > >>>> NUMERIC),4)||'KM' field_value from had_link;
> > >>>>
> > >>>> ERROR:  XX000: cannot update SecondarySnapshot during a parallel operation
> > >>>> CONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys
> > >>>> WHERE srid = 4326 LIMIT 1"
> > >>>> parallel worker
> > >>>> LOCATION:  GetLatestSnapshot, snapmgr.c:387
> > >
> > > I'm not familiar at all with geography, but if I read the code
> > > correctly, ST_Length / geography_length will call
> > > spheroid_init_from_srid(), which eventuallly calls GetProj4StringSPI()
> > > that run a query using SPI.  I'm not sure why exactly
> > > GetLatestSnapshot() is called here, or if SPI is really allowed in
> > > parallel workers, but that's the problem here apparently.  I'm adding
> > > Paul as he'll probably have a way better answer than me.
> > >
> >
> > https://trac.osgeo.org/postgis/ticket/4129
> > "Cannot update SecondarySnapshot during a parallel operation"
>
> Ah, I didn't look there indeed, thanks!  So postgis people are already
> aware, that's a good news.

Aware but unable to replicate, so nothing is happening on that front.
If you can create a set of data, SQL statements and configuration that
replicates, that would aid immensely.

On Thu, Mar 14, 2019 at 4:59 PM Paul Ramsey <pramsey@cleverelephant.ca> wrote:
>
> On Thu, Mar 14, 2019 at 8:43 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >
> > On Thu, Mar 14, 2019 at 3:25 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> > >
> > > On 3/14/19 6:14 AM, Julien Rouhaud wrote:
> > > > On Thu, Mar 14, 2019 at 1:20 PM fuzk <fuzk80_76@163.com> wrote:
> > > >>
> > > >> Dear Adrian,
> > > >>
> > > >> My setting is as following.
> > > >>
> > > >> max_parallel_workers_per_gather=32
> > > >>
> > > >> I am looking forward to hearing from you.
> > > >
> > > > What version of postgres and what version of postgis are you using ?
> > > >
> > > >> At 2019-03-13 22:31:11, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
> > > >>> On 3/12/19 7:54 PM, fuzk wrote:
> > > >>>> postgres=# \set VERBOSITY verbose
> > > >>>> postgres=# SELECT
> > > >>>> round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as
> > > >>>> NUMERIC),4)||'KM' field_value from had_link;
> > > >>>>
> > > >>>> ERROR:  XX000: cannot update SecondarySnapshot during a parallel operation
> > > >>>> CONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys
> > > >>>> WHERE srid = 4326 LIMIT 1"
> > > >>>> parallel worker
> > > >>>> LOCATION:  GetLatestSnapshot, snapmgr.c:387
> > > >
> > > > I'm not familiar at all with geography, but if I read the code
> > > > correctly, ST_Length / geography_length will call
> > > > spheroid_init_from_srid(), which eventuallly calls GetProj4StringSPI()
> > > > that run a query using SPI.  I'm not sure why exactly
> > > > GetLatestSnapshot() is called here, or if SPI is really allowed in
> > > > parallel workers, but that's the problem here apparently.  I'm adding
> > > > Paul as he'll probably have a way better answer than me.
> > > >
> > >
> > > https://trac.osgeo.org/postgis/ticket/4129
> > > "Cannot update SecondarySnapshot during a parallel operation"
> >
> > Ah, I didn't look there indeed, thanks!  So postgis people are already
> > aware, that's a good news.
>
> Aware but unable to replicate, so nothing is happening on that front.
> If you can create a set of data, SQL statements and configuration that
> replicates, that would aid immensely.

I also tried to reproduce on latest postgis 2.4 / pg11 with anything
even slightly related to what could call GetLatestSnapshot() with
force_parallel_mode enabled and parallel_leader_participation disabled
(also postgis installcheck), and I couldn't hit this problem (while
I'm sure that the underlying query was run).  I start to think that
this may be due to a third-party module loaded that could call
GetLatestSnapshot(), otherwise I have no  explanation.

On Fri, Mar 15, 2019 at 6:09 AM Julien Rouhaud <rjuju123@gmail.com> wrote:

> > > > https://trac.osgeo.org/postgis/ticket/4129

> I also tried to reproduce on latest postgis 2.4 / pg11 with anything
> even slightly related to what could call GetLatestSnapshot() with
> force_parallel_mode enabled and parallel_leader_participation disabled
> (also postgis installcheck), and I couldn't hit this problem (while
> I'm sure that the underlying query was run).  I start to think that
> this may be due to a third-party module loaded that could call
> GetLatestSnapshot(), otherwise I have no  explanation.

I don't know much about PostGIS but this does seem very strange.
Comment #7 in the Trac bug says that the error occurs only
intermittently.  Hmm, so what could reach GetLatestSnapshot() only
occasionally...?  Generally that is used for things that are doing RI
checks and other special things involving write queries, but these
aren't write queries, or shouldn't be.  It should be perfectly OK for
SPI stuff to happen inside PARALLEL SAFE functions, as long as they
only do read-only queries; I hope that any SRID lookup-type activity
hiding in these functions is just doing read-only work (for example
we've found a few core function that we had to mark as UNSAFE after we
realised that they could run user-supplied queries that could do
anything).

A fast way to find out would be to get one of these people who can
reproduce the problem to recompile PostgreSQL with that error changed
to a PANIC, and examine the resulting smoldering core.  (Someone had a
proposal to make PostgreSQL errors optionally dump the function call
stack with backtrace(3) even in regular production builds, which would
make this kind of investigations go faster, I wonder what happened to
that.)

-- 
Thomas Munro
https://enterprisedb.com

Thomas Munro <thomas.munro@gmail.com> writes:
> A fast way to find out would be to get one of these people who can
> reproduce the problem to recompile PostgreSQL with that error changed
> to a PANIC, and examine the resulting smoldering core.  (Someone had a
> proposal to make PostgreSQL errors optionally dump the function call
> stack with backtrace(3) even in regular production builds, which would
> make this kind of investigations go faster, I wonder what happened to
> that.)

Can't speak for other people, but I remember experimenting with
glibc's backtrace(3) and being so underwhelmed by the usefulness
of the information presented that I thought incorporating it would
be mostly a waste of effort.  Maybe there's an argument that it's
better than nothing at all; but I think we'd still be driven to
asking people to get stack traces with better tools.

            regards, tom lane

On 3/14/19 7:12 PM, fuzk wrote:
> When I set max_parallel_workers_per_gather=0, the select statement can 
> execute successfully. The problem has been solved. Thank you all very 
> much. Alan
> 


Well the problem has been hidden, not solved. It did establish that 
invoking parallel query has something to do with it. There also the fact 
that by max_parallel_workers_per_gather is set to 0 by default. So the 
setting you had previously(32) would imply that someone wanted to use 
parallel query.  If it is re-enabled then you are back to the error.  To 
help solve the problem for yourself and others could you supply relevant 
information to the this problem report:

https://trac.osgeo.org/postgis/ticket/4129



-- 
Adrian Klaver
adrian.klaver@aklaver.com