Re: BUG #16024: segfault ip 0000560103865c60 error 4 in postgres

From: Stephan Knauss <sknauss(at)gmx(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16024: segfault ip 0000560103865c60 error 4 in postgres
Date: 2019-09-25 23:21:49
Message-ID: 7e355f85-508a-314f-e655-2d6107074e2e@gmx.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello Tom,

thank you for the quick reply.

On 26.09.2019 00:45, Tom Lane wrote:
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> A self-contained test case would be much handier. The stack trace
> provides a suggestive hint about what you're doing:

I fully agree on that. Unfortunately I have no idea what actually
breaks. The query was running unchanged for quite a while now. It
creates statistics for OpenStreetMap data a few times a day.

It could either be that the input data has changed in a way now
triggering a bug in postgres or that one of the recent updates broke it.
I do update from the official postgresql repository.

So I can't come up with a small repro case. I could dump the full DB.

>> #15 0x0000564007ab5455 in exec_simple_query (query_string=0x5640089ed678
>> "DROP TABLE if EXISTS loose;CREATE UNLOGGED TABLE loose (id bigint, node
>> bigint, lat float, lon float, x integer, y integer);INSERT INTO loose SELECT
>> id, nodes[1] AS node FROM planet_osm_ways WHERE ARR"...)
>> at ./build/../src/backend/tcop/postgres.c:1109
>
> but that's not enough to reproduce the problem.

The full query is:

$sql = "DROP TABLE if EXISTS loose;";
$sql .= "CREATE UNLOGGED TABLE loose (id bigint, node bigint, lat float,
lon float, x integer, y integer);";
$sql .= "INSERT INTO loose SELECT id, nodes[1] AS node FROM
planet_osm_ways WHERE
ARRAY['motorway','trunk','primary','secondary','tertiary'] && tags AND
NOT ARRAY['construction','proposed'] && tags AND nodes[1] <>
nodes[array_upper(nodes, 1)];";
$sql .= "INSERT INTO loose SELECT id, nodes[array_upper(nodes, 1)] AS
node FROM planet_osm_ways WHERE
ARRAY['motorway','trunk','primary','secondary','tertiary'] && tags AND
NOT ARRAY['construction','proposed'] && tags AND nodes[1] <>
nodes[array_upper(nodes, 1)];";
$sql .= "DELETE FROM loose AS l WHERE (SELECT count(1) FROM
planet_osm_ways AS w WHERE w.nodes && ARRAY[l.node] ) > 1;";
$sql .= "DELETE FROM loose AS l WHERE 0=(SELECT COUNT(1) FROM
planet_osm_nodes AS n WHERE n.id=l.node);";
$sql .= "DELETE FROM loose AS l USING planet_osm_point AS p WHERE
p.osm_id =l.node AND p.tags->'noexit' = 'yes';";
$sql .= "UPDATE loose AS l SET lat=(n.lat/10000000::float),
lon=(n.lon/10000000::float), x=FLOOR(
((n.lon/10000000::float)+180)/360*(1<<19) )::integer, y=FLOOR(
(1.0-ln(tan(radians((n.lat/10000000::float)))+1.0/cos(radians((n.lat/10000000::float))))/pi())/2.0*(1<<19)
)::integer FROM planet_osm_nodes AS n WHERE n.id=l.node;";
$sql .= "DELETE FROM loose AS l using planet_osm_nodes AS n WHERE
n.id=l.node AND NOT ST_Within(ST_SetSRID(ST_POINT(n.lon/10000000::float,
n.lat/10000000::float), 4326), st_transform((select
st_transform(st_union(way),4326) as w FROM planet_osm_polygon where
osm_id='-2067731' or osm_id='-49903' or osm_id='-50371' or
osm_id='-49915' or osm_id='-49898' or osm_id='-2108121' or
osm_id='-536780' or osm_id='-2103120'), 4326));";

> Note that in psql, the way to submit a multi-query string like that
> is to backslash-escape all but the last semicolon:
>
> DROP TABLE if EXISTS loose\;CREATE UNLOGGED ... y integer)\;INSERT INTO ...
>
> That might be necessary to reproduce the problem, or not.

I am not sure what you try to say. I am quite confident that I can
separate individual commands in the query with a semicolon. It also
worked quite well in the past and seems to be backed by the documentation:
https://www.postgresql.org/docs/9.6/sql-syntax-lexical.html

What do you mean by backslash-escaping? Or are you referring to the psql
command line utility? This is not used in my case. I submit the query
from PHP. But it sounds unrelated to me. Based on the call-stack is
looks crashing way more inside some processing routines.

Anything else I can support you tracking down this crash?

Stephan

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-09-25 23:31:42 BUG #16025: pg_default tablespace is removable but not creatable
Previous Message Tom Lane 2019-09-25 22:45:37 Re: BUG #16024: segfault ip 0000560103865c60 error 4 in postgres