Re: [pgrouting-users] bug in pgRouting: server closed the connection unexpectedly
Mario Basa
mario.basa at gmail.com
Tue Nov 15 19:33:21 EST 2011
Hello Milo,
The underlying Boost library, which pgRouting uses for its Dijkstra
and A-Star searches, requires an integer parameter I believe.
So, even if we change the "source" and "target" into long, it might
have to be typecast-ed again into int.
Regards,
Mario.
On Wed, Nov 16, 2011 at 6:42 AM, Milo van der Linden <milo at dogodigi.net> wrote:
> Although I am no C programmer, out of curiosity, I dug into
> https://github.com/pgRouting/pgrouting/blob/master/core/src/dijkstra.c
>
> the file dijkstra.c shows "source" and "target" are declared as int. A
> short term solution would be to set it to long; a better solution
> would be to set them to whatever datatype is set on the columns in the
> table, in my opinion this would involve some c magic ;-)
>
> The functions to determine the data type of columns in c are here:
> http://www.postgresql.org/docs/8.2/static/xfunc-c.html
> and a good example is in paragraph 33.9.11
>
> Kind regards,
>
> Milo van der Linden
>
> 2011/11/14 E. . <thnxfernuttin at hotmail.com>:
>> Hi Steve,
>>
>> thanks for your swift reply!
>>
>> well, rebuilding the table helps in that the source and target values are
>> not too high anymore then.
>>
>> i have a bunch of info here for you, i'll split it:
>>
>> the query is:
>>
>> SELECT * FROM shortest_path('SELECT id, source::integer,
>> target::integer, cost::double precision FROM ma_routing', 64629654,
>> 64630762, false,
>> false)
>>
>> sample data to get crashing:
>>
>> source | target | cost | reverse_cost | sens
>> | x1 | y1 | x2 | y2 | rule | to_cost |
>> length | ogc_fid | id | length_shortest | length_reverse_shortes t
>> ----------+----------+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+----+-----------------+-------------------------
>> 64629390 | 64629391 | 2.98541825999812 | 2.98541825999812 | double sens |
>> -73.167897 | 42.705496 | -73.167836 | 42.705791 | | |
>> 33.1713139999791 | 582 | 1 | |
>> 64629654 | 64630762 | 15.4108990193286 | 15.4108990193286 | double sens |
>> -73.115287 | 42.646935 | -73.113203 | 42.647023 | | |
>> 171.232211325874 | &nb sp; 718 | 2 |
>> |
>> 64629691 | 64629692 | 1.03336877316695 | 1.03336877316695 | double sens |
>> -73.047407 | 42.648043 | -73.047296 | 42.648106 | | |
>> 11.4818752574105 | 738 | 3 | |
>> (3 rows)
>>
>>
>> if you change source and target in any existing table and make it at least
>> as high as the values here, crashing is guaranteed!
>>
>>
>>
>> ok, here comes the whole thing, this was my original pos t with a lot of bla
>> bla:
>>
>>
>> While running a shortest_path query i had the following error message:
>>
>> SELECT * FROM shortest_path('SELECT id, source::integer,
>> target::integer, cost::double precision FROM ma_routing', 64629654,
>> 64630762, false,
>> false)
>>
>> " server closed the connection unexpectedly".
>>
>> I do not have negative cost or reverse_cost values. In another thread a long
>> time ago it was advised to rebuild the "source" and "target" tables using
>> "assign_vertex_id".
>>
>> The posting is at:
>> http://postgis.refractions.net/pipermail/postgis-users/2008-June/020137.html
>>
>> That function i cannot find.
>>
>> For the following samples goes:
>> Projection _should_ be in 4326.
>>
>>
>> Here's a sample of the data in the routing table:
>>
>> select * from ma_routing limit 3;
>>
>> source | target | cost | reverse_cost |&nbs p;
>> sens | x1 | y1 | x2 | y2 | rule | to_cost
>> | length | ogc_fid | id | length_shortest |
>> length_reverse_shortest
>> ----------+----------+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+----+-----------------+-------------------------
>> 64629390 | 64629391 | 2.98541825999812 | 2.98541825999812 | double sens |
>> -73.167897 | 42.705496 | -73.167836 | 42.705791 | | |
>> 33.1713139999791 | 582 | 1 | | &
>> nbsp;
>> 64629654 | 64630762 | 15.4108990193286 | 15.4108990193286 | double sens |
>> -73.115287 | 42.646935 | -73.113203 | 42.647023 | | |
>> 171.232211325874 | 718 | 2 | |
>> 64629691 | 64629692 | 1.03336877316695 | 1.03336877316695 | double sens |
>> -73.047407 | 42.648043 | -73.047296 | 42.648106 | | |
>> 11.4818752574105 | 738 | 3 | |
>> (3 rows)
>>
>> OK, let's take line 2: source=64629654 , target=64630762.
>> Let's do a lookup where the source is the target of line 2, to go from
>> source to target:
>>
>> select * from ma_routing where source=64630762;
>>
>> source | target | cost | reverse_cost | sens
>> | x1 | y1 | x2 | y2 | rule | to_cost |
>> length | ogc_fid | id | length_shortest | length_reverse_shortest
>> ----------+-------
>> ---+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+-------+-----------------+-------------------------
>> 64630762 | 64629653 | 10.5842324315852 | 10.5842324315852 | double sens |
>> -73.115501 | 42.647023 | -73.115287 | 42.648065 | | |
>> 117.602582573169 | 1502 | 5692 |
>> |
>> 64630762 | 64630760 | 10.324871753826 | 10.324871753826 | double sens |
>> -73.115287 | 42.646049 | -73.114822 | 42.647023 | | |
>> 114.720797264734 | 7776 | 11879 |
>> |
>> (2 rows)
>>
>>
>> Maybe i am overlooking something.
>> The geometry column is in another table.
>>
>> Roads table is called "027_nosr_r".
>>
>> Sorry if this is going to be confusing but i want to get the same rows from
>> the "roads" table (which is the origin) as from the routing table.
>>
>>
>> Here's a sample of the roads table:
>>
>> select * from "027_nosr_r" limit 3;
>>
>> ogc_fid | &nb
>> sp;
>> wkb_geometry
>> | fnode_ | tnode_ | lpoly_ | rpoly_ | length | reg25_ | rd_1
>> | rd_2 | rd_3 | rd_4 | rd_5 | rd_6 | rd_7 | rd_8 | rd_9 |
>> rd_10 | rd_11 | rd_12 | rd_13 | rd_14 | rd_ 15 | rd_16 |
>> rd_17 | rd_18 | rd_19 | rd_20 | rd_21 | rd_22 |
>> rd_23 | rd_24 | rd_25 | rd_26 | rd_27 | rd_28 |
>> textsearchable_index_col
>> ---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+------------+-------+-------+--------------------------
>> 582 | 0102000020E6100000030000002D060
>> FD3BE4A52C0BABA63B14D5A4540B0FECF61BE4A52C0ADA415DF505A4540DAC534D3BD4A52C0D367075C575A4540
>> | 64629390 | 64629391 | 206361314 | 206361314 | 0.000000 | 3915987 | 3915987
>> | 0 | 0 | 0 | 6 | 100 | 6 | 0 | 1
>> | | | | |
>> | |   ; | 0 | 0 | 0 | 0 | 0 | 0 |
>> fc#4 | 25003 | 0 | 2500346225 |
>> | |
>> 718 |
>> 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540
>> | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 | 3916275
>> | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St  
>> ; | | | | |
>> | | 0 | 0 | 0 | 0 | 0 | 0 |
>> fc#4 at hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220
>> | 01220 | 'st':2 'brown':1
>> 738 |
>> 0102000020E6100000020000007FA65EB7084352C06CB3B112F3524540D34ECDE5064352C0B22B2D23F5524540 &nb
>> sp; | 64629691 | 64629692
>> | 206354450 | 206354451 | 0.000000 | 3916317 | 3916317 | 0 | 0 | 0
>> | 6 | 100 | 6 | 0 |
>> | | | | |
>> | & nbsp; | | 0 | 0 | 0 | 0 | 0 | 0 |
>> fc#4 | 25003 | 0 | 2500360225 |
>> | |
>> (3 rows)
>>
>>
>>
>> Let's take 64629654 as fnode_, which is the "source column" of the road,
>> like in line 2 of the first sql, source=64629654 , target=64630762.
>>
>> select * from "027_nosr_r" where fnode_=64629654 ;
>>
>>
>> ogc_fid | &
>> nbsp;
>> wkb_geometry
>> | fnode_ | tnode_ | lpoly_ | rpoly_ | length | reg25_ | rd_1
>> | rd_2 | rd_3 | rd_4 | rd_5 | rd_6 | rd_7 | rd_8 | rd_9 |
>> rd_10 | rd_11 | rd_12 | rd_13 | rd_14 | rd_15 | rd_16 |
>> rd_17 | rd_18 | rd_19 | rd_20 | rd_21 | rd_22 |
>> rd_23 | rd_24 | rd_25 | rd_26 | rd_27 | rd_28 |
>> textsearchable_index_col
>> ---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+---------------
>> --------------------------+-------+-------+------------+-------+-------+--------------------------
>> 718 |
>> 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540
>> | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 | 3916275
>> | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown
>> St | | | | |
>> | | &nbs p; 0 | 0 | 0 | 0 | 0 | 0 |
>> fc#4 at hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220
>> | 01220 | 'st':2 'brown':1
>> (1 row)
>>
>>
>> OK, let's consider line 2 of the routing table: source=64629654 ,
>> target=64630762. Same second sample above, only this time from the "roads"
>> table, not the routing table.
>> Let's do a lookup where the source is the target of line 2, to go from
>> source to target:
>>
>> tnode_="target":
>>
>> select * from "027_nosr_r" where fnode_=64629654 limit 3;
>>
>>
>> ogc_fid | &nbs
>> p;
>> wkb_geometry
>> | fnode_ | tnode_ | lpoly_ | rpoly_ | length | reg25_ | rd_1
>> | rd_2 | rd_3 | rd_4 | rd_5 | rd_6 | rd_7 | rd_8 | rd_9 |
>> rd_10 &nb sp; | rd_11 | rd_12 | rd_13 | rd_14 | rd_15 |
>> rd_16 | rd_17 | rd_18 | rd_19 | rd_20 | rd_21 | rd_22 |
>> rd_23 | rd_24 | rd_25 | rd_26 | rd_27 | rd_28 |
>> textsearchable_index_col
>> ---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+--------
>> ----+-------+-------+--------------------------
>> 718 |
>> 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540
>> | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 | 3916275
>> | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown
>> St | | | | |
>> | | 0 | 0 |   ; 0 | 0 | 0 | 0 |
>> fc#4 at hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220
>> | 01220 | 'st':2 'brown':1
>> (1 row)
>>
>>
>>
>>
>> thanks,
>>
>> EJ
>>
>>
>>
>>> Date: Sun, 13 Nov 2011 11:11:29 -0500
>>> From: woodbri at swoodbridge.com
>>> To: pgrouting-users at lists.osgeo.org
>>> Subject: Re: [pgrouting-users] bug in pgRouting: server closed the
>>> connection unexpectedly
>>>
>>> On 11/13/2011 3:36 AM, E. . wrote:
>>> > hi all,
>>> >
>>> > i finally figured out what the problem is.
>>> >
>>> > high values of "source" and "target" fields crash pgRouting.
>>> >
>>> > with high i mean this:
>>> >
>>> > source | target |
>>> > ----------+---------
>>> > 64629390 | 64629391 |
>>> > 64629654 | 64630762 |
>>> >
>>> > must be a register overflow.
>>> >
>>> > that's prpbably why the solution was to rebuild the tables, if the
>>> > source and targets fields will be renumbered.
>>>
>>> Hi EJ,
>>>
>>> Thank you for the report. Which method were you using? Dijkstra, astar,
>>> shooting star? Can you give use the query the was causing the fault? It
>>> might also be helpful if you could provide the results of:
>>>
>>> select min(source), min(target), max(source), max(target), count(*)
>>> from <your table>;
>>>
>>> Does renumbering the table solve your problem?
>>> What was the error/crash message you were getting?
>>>
>>> In my mind, it is a bug to crash the server or the connection, we should
>>> understand and fail gracefully if we have to but not crash.
>>>
>>> Thank you for help,
>>>
>>> -Steve
>>> ____________ ___________________________________
>>> Pgrouting-users mailing list
>>> Pgrouting-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>
>> _______________________________________________
>> Pgrouting-users mailing list
>> Pgrouting-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>
>>
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
>
More information about the Pgrouting-users
mailing list