Re: BUG #2481: select from table's join with geometries doesn't go

Alle 07:36, venerdì 16 giugno 2006, hai probabilmente scritto:
> On Thu, Jun 15, 2006 at 11:48:37PM -0400, Tom Lane wrote:
> > "Emilia Venturato" <venturato(at)faunalia(dot)it> writes:
> > > Postgis developper said it could be a postgresql bug.
> >
> > Or it could be a postgis bug. Without a test case we can use to
> > reproduce the problem, it's all speculation. Please send a complete,
> > self-contained test case...
>
> This report resembles a message Emilia posted in postgis-users a
> couple of weeks ago. The only public discussion is a request for
> the PostGIS version and copy of the data:
>
> http://postgis.refractions.net/pipermail/postgis-users/2006-June/012281.htm
>l
> http://postgis.refractions.net/pipermail/postgis-users/2006-June/012282.htm
>l
>
> Emilia, did you and Sandro (strk) have off-list discussion about
> this problem?

Yes, we had off-list discussion.

> What do version() and postgis_full_version() return?

version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.4
20060507 (prerelease) (Debian 4.0.3-3)

postgis_full_version
------------------------------------------------------------------------------------------------------------------
POSTGIS="1.1.2" GEOS="2.2.1-CAPI-1.0.1" PROJ="Rel. 4.4.9, 29 Oct 2004"
USE_STATS (procs from 1.1.1 need upgrade)

> What happens if you select the geometry column without a join, i.e.,
> "SELECT the_geom FROM wwf_terr_ecos_multigeom WHERE ..."?

It goes well.

> Do you
> get the segmentation fault with the original query if you select
> AsText(the_geom) or AsEWKT(the_geom) instead of just the_geom?

I tried with astext and it's doesn't go.

> Did the segmentation fault leave a core dump in your $PGDATA directory
> or somewhere beneath it? If not then you might need to adjust your
> coredumpsize resource limit.

I understand it was psql to crash, not postgresql. Postgres doesn't stop.
Maybe this could explain why create table go well and only select doesn't go.

Making test I found also that query plan changes if I select geometric field
or not. Particulary It seems have problem with merge condition:

-- If I make select with geometric field:

select a.*, b.the_geom from small_carnivore_fisso a join
wwf_terr_ecos_multigeom b on a.ecoregion_code=b.eco_code;

QUERY PLAN
Merge Join (cost=1184.56..1415.71 rows=9222 width=78224) (actual
time=259.035..355.384 rows=18444 loops=1)
Merge Cond: (("outer".eco_code)::text = "inner"."?column9?")
-> Index Scan using index_eco_code on wwf_terr_ecos_multigeom b
(cost=0.00..98.97 rows=1654 width=78131) (actual time=16.307..51.607
rows=1653 loops=1)
-> Sort (cost=1184.56..1207.62 rows=9222 width=103) (actual
time=242.567..259.277 rows=18443 loops=1)
Sort Key: (a.ecoregion_code)::text
-> Seq Scan on small_carnivore_fisso a (cost=0.00..269.22
rows=9222 width=103) (actual time=11.380..75.130 rows=9222 loops=1)
Total runtime: 363.247 ms

-- without geometric field:

QUERY PLAN
Hash Join (cost=52.67..483.28 rows=9222 width=113) (actual time=3.113..28.000
rows=18444 loops=1)
Hash Cond: (("outer".ecoregion_code)::text = ("inner".eco_code)::text)
-> Seq Scan on small_carnivore_fisso a (cost=0.00..269.22 rows=9222
width=103) (actual time=0.013..7.099 rows=9222 loops=1)
-> Hash (cost=48.54..48.54 rows=1654 width=10) (actual time=3.002..3.002
rows=1654 loops=1)
-> Seq Scan on wwf_terr_ecos_multigeom b (cost=0.00..48.54
rows=1654 width=10) (actual time=0.008..1.414 rows=1654 loops=1)
Total runtime: 34.492 ms

I prepared a file.zip with problem summary and data. It's 16 Mb. It's
downloadable from http://www.faunalia.it/download/bug2481.tar.gz

Thank you very much.
Lia

--
Emilia Venturato
email+jabber: venturato(at)faunalia(dot)it
www.faunalia.it
Tel: (+39) 347-2770007 Tel+Fax: (+39) 0587-213742
Piazza Garibaldi 5 - 56025 Pontedera (PI), Italy