Re: BUG #2481: select from table's join with geometries doesn't go - Mailing list pgsql-bugs

Alle 07:36, venerd=EC 16 giugno 2006, hai probabilmente scritto:
> On Thu, Jun 15, 2006 at 11:48:37PM -0400, Tom Lane wrote:
> > "Emilia Venturato" <venturato@faunalia.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.h=
tm
>l
> http://postgis.refractions.net/pipermail/postgis-users/2006-June/012282.h=
tm
>l
>
> Emilia, did you and Sandro (strk) have off-list discussion about
> this problem?=20

Yes, we had off-list discussion.

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

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

                                               postgis_full_version=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
---------------------------------------
 POSTGIS=3D"1.1.2" GEOS=3D"2.2.1-CAPI-1.0.1" PROJ=3D"Rel. 4.4.9, 29 Oct 200=
4"=20
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 ..."?=20=20

It goes well.

> Do you=20
> 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.=
=20
Maybe this could explain why create table go well and only select doesn't g=
o.


Making test I found also that query plan changes if I select geometric fiel=
d=20
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=20
wwf_terr_ecos_multigeom b on a.ecoregion_code=3Db.eco_code;


QUERY PLAN
Merge Join  (cost=3D1184.56..1415.71 rows=3D9222 width=3D78224) (actual=20
time=3D259.035..355.384 rows=3D18444 loops=3D1)
    Merge Cond: (("outer".eco_code)::text =3D "inner"."?column9?")
    ->  Index Scan using index_eco_code on wwf_terr_ecos_multigeom b=20=20
(cost=3D0.00..98.97 rows=3D1654 width=3D78131) (actual time=3D16.307..51.60=
7=20
rows=3D1653 loops=3D1)
    ->  Sort  (cost=3D1184.56..1207.62 rows=3D9222 width=3D103) (actual=20
time=3D242.567..259.277 rows=3D18443 loops=3D1)
          Sort Key: (a.ecoregion_code)::text
          ->  Seq Scan on small_carnivore_fisso a  (cost=3D0.00..269.22=20
rows=3D9222 width=3D103) (actual time=3D11.380..75.130 rows=3D9222 loops=3D=
1)
  Total runtime: 363.247 ms


-- without geometric field:

QUERY PLAN
Hash Join  (cost=3D52.67..483.28 rows=3D9222 width=3D113) (actual time=3D3.=
113..28.000=20
rows=3D18444 loops=3D1)
    Hash Cond: (("outer".ecoregion_code)::text =3D ("inner".eco_code)::text)
    ->  Seq Scan on small_carnivore_fisso a  (cost=3D0.00..269.22 rows=3D92=
22=20
width=3D103) (actual time=3D0.013..7.099 rows=3D9222 loops=3D1)
    ->  Hash  (cost=3D48.54..48.54 rows=3D1654 width=3D10) (actual time=3D3=
.002..3.002=20
rows=3D1654 loops=3D1)
          ->  Seq Scan on wwf_terr_ecos_multigeom b  (cost=3D0.00..48.54=20
rows=3D1654 width=3D10) (actual time=3D0.008..1.414 rows=3D1654 loops=3D1)
  Total runtime: 34.492 ms


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

Thank you very much.
Lia



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