Re: BUG #16024: segfault ip 0000560103865c60 error 4 in postgres - Mailing list pgsql-bugs
From | Stephan Knauss |
---|---|
Subject | Re: BUG #16024: segfault ip 0000560103865c60 error 4 in postgres |
Date | |
Msg-id | 7e355f85-508a-314f-e655-2d6107074e2e@gmx.de Whole thread Raw |
In response to | Re: BUG #16024: segfault ip 0000560103865c60 error 4 in postgres (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-bugs |
Hello Tom, thank you for the quick reply. On 26.09.2019 00:45, Tom Lane wrote: > PG Bug reporting form <noreply@postgresql.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
pgsql-bugs by date: