Skip site navigation (1) Skip section navigation (2)

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

From: Emilia Venturato <venturato(at)faunalia(dot)it>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org,strk(at)refractions(dot)net
Subject: Re: BUG #2481: select from table's join with geometries doesn't go
Date: 2006-06-16 09:16:50
Message-ID: 200606161116.53687.venturato@faunalia.it (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Klaus GuntermannDate: 2006-06-16 10:05:04
Subject: BUG #2483: Wrong error report about missing entry in from clause
Previous:From: Devrim GUNDUZDate: 2006-06-16 06:04:21
Subject: Re: BUG #2480: Installation Error of RMP for RHEL4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group