ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

fuzk
Dear Sir/Madam

I got an error when I execute the following select sentence.
Would you please solve the problem for me?
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


 

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re:Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

fuzk

Dear Adrian,

My setting is as following.

max_parallel_workers_per_gather=32

I am looking forward to hearing from you.

Many thanks
Alan.







At 2019-03-13 22:31:11, "Adrian Klaver" <[hidden email]> 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 >[hidden email]


 

Reply | Threaded
Open this post in threaded view
|

Re: Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Julien Rouhaud
On Thu, Mar 14, 2019 at 1:20 PM fuzk <[hidden email]> 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" <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Adrian Klaver-4
On 3/14/19 6:14 AM, Julien Rouhaud wrote:

> On Thu, Mar 14, 2019 at 1:20 PM fuzk <[hidden email]> 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" <[hidden email]> 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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Adrian Klaver-4
In reply to this post by fuzk
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" <[hidden email]> 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
>>[hidden email]
>
>
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Julien Rouhaud
In reply to this post by Adrian Klaver-4
On Thu, Mar 14, 2019 at 3:25 PM Adrian Klaver <[hidden email]> wrote:

>
> On 3/14/19 6:14 AM, Julien Rouhaud wrote:
> > On Thu, Mar 14, 2019 at 1:20 PM fuzk <[hidden email]> 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" <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Paul Ramsey
On Thu, Mar 14, 2019 at 8:43 AM Julien Rouhaud <[hidden email]> wrote:

>
> On Thu, Mar 14, 2019 at 3:25 PM Adrian Klaver <[hidden email]> wrote:
> >
> > On 3/14/19 6:14 AM, Julien Rouhaud wrote:
> > > On Thu, Mar 14, 2019 at 1:20 PM fuzk <[hidden email]> 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" <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Julien Rouhaud
On Thu, Mar 14, 2019 at 4:59 PM Paul Ramsey <[hidden email]> wrote:

>
> On Thu, Mar 14, 2019 at 8:43 AM Julien Rouhaud <[hidden email]> wrote:
> >
> > On Thu, Mar 14, 2019 at 3:25 PM Adrian Klaver <[hidden email]> wrote:
> > >
> > > On 3/14/19 6:14 AM, Julien Rouhaud wrote:
> > > > On Thu, Mar 14, 2019 at 1:20 PM fuzk <[hidden email]> 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" <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Thomas Munro-5
On Fri, Mar 15, 2019 at 6:09 AM Julien Rouhaud <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re:Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

fuzk
In reply to this post by Adrian Klaver-4
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






At 2019-03-14 22:29:24, "Adrian Klaver" <[hidden email]> wrote: >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" <[hidden email]> 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 >>>[hidden email] >> >> >> > > >-- >Adrian Klaver >[hidden email]


 

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Tom Lane-2
In reply to this post by Thomas Munro-5
Thomas Munro <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

Adrian Klaver-4
In reply to this post by fuzk
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
[hidden email]