Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
"Emilia Venturato"
Date:
The following bug has been logged online:

Bug reference:      2481
Logged by:          Emilia Venturato
Email address:      venturato@faunalia.it
PostgreSQL version: 8.1.4
Operating system:   Debian etch
Description:        select from table's join with geometries doesn't go
Details:

--I have data about animals in a table:

 \d small_carnivore_fisso;
        Tabella "public.small_carnivore_fisso"
      Colonna      |       Tipo        | Modificatori
-------------------+-------------------+--------------
 id                | integer           |
 family            | character varying |
 genus             | character varying |
 species           | character varying |
 common_name       | character varying |
 riscrit           | character varying |
 red_list_cat_desc | character varying |
 ecoregion_code    | character varying |

-- I have geographical data about animal in the table:
\d wwf_terr_ecos_multigeom
    Tabella "public.wwf_terr_ecos_multigeom"
 Colonna  |         Tipo          | Modificatori
----------+-----------------------+--------------
 eco_code | character varying(50) |
 eco_name | character varying(99) |
 the_geom | geometry              |


-- a join without geometry goes well:
select a.*, b.eco_code from small_carnivore_species a join
wwf_terr_ecos_multigeom b on a.ecoregion_code=b.eco_code;


-- a join with geometry doesn't:

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

-- the geometries are valid
select isvalid(the_geom) from wwf_terr_ecos_multigeom where
isvalid(the_geom) is false;
 isvalid
---------
(0 righe)


-- if I use 'create table as...' instead only select, the table is done:
create table foo as select a.*, b.the_geom from small_carnivore_fisso a join
wwf_terr_ecos_multigeom b on a.ecoregion_code=b.eco_code;
SELECT


If I reduce the record number the select sometime goes, sometime doesn't. I
cannot define a record limit wich make the difference.
Postgis developper said it could be a postgresql bug.

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

From
Tom Lane
Date:
"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...

            regards, tom lane

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

From
Michael Fuhr
Date:
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.html
http://postgis.refractions.net/pipermail/postgis-users/2006-June/012282.html

Emilia, did you and Sandro (strk) have off-list discussion about
this problem?  What do version() and postgis_full_version() return?
What happens if you select the geometry column without a join, i.e.,
"SELECT the_geom FROM wwf_terr_ecos_multigeom WHERE ..."?  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?

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.

--
Michael Fuhr

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

From
Michael Fuhr
Date:
On Fri, Jun 16, 2006 at 11:16:50AM +0200, Emilia Venturato wrote:
> > 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.

Did psql create a core dump?  If not then check your coredumpsize
resource limit.  For example, if you're using bash, then what's the
output of "ulimit -c"?  If it's 0 then run "ulimit -c unlimited".
With a core dump you can use a debugger to get a stack trace that
should show where the problem is.

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

The query plan shouldn't affect psql's behavior but selecting
different columns might.  Notice that the estimated column width
is much higher when you select the geometry column than when you
don't:

[with]
> Merge Join  (cost=1184.56..1415.71 rows=9222 width=78224) (actual
> time=259.035..355.384 rows=18444 loops=1)

[without]
> Hash Join  (cost=52.67..483.28 rows=9222 width=113) (actual time=3.113..28.000
> rows=18444 loops=1)

> 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

A HEAD request against that file shows it to be 116M (121747346),
not 16M, and it appears to be on a slow link (curl estimates over
an hour to download).  Can you create a smaller test case?

--
Michael Fuhr

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

From
Michael Fuhr
Date:
On Fri, Jun 16, 2006 at 06:53:28AM -0600, Michael Fuhr wrote:
> The query plan shouldn't affect psql's behavior but selecting
> different columns might.  Notice that the estimated column width
> is much higher when you select the geometry column than when you
> don't:
>
> [with]
> > Merge Join  (cost=1184.56..1415.71 rows=9222 width=78224) (actual
> > time=259.035..355.384 rows=18444 loops=1)

That's a lot of data -- are you aware that psql (via libpq) fetches
the entire result set before displaying it?  In most cases 18444
rows wouldn't be a problem, but with rows that wide it becomes a
big problem because the client has to store it all in memory.  I
wonder if that's causing psql to segfault, although I'd expect a
graceful error like "out of memory for query result" unless maybe
psql consumes so much memory that the OS has problems.  How much
memory does the box have and what's your datasize resource limit?

Do you get the segfault if you LIMIT the result set to a small
number of rows?  If you really need all that data then try using a
cursor so you can fetch data a few rows at a time instead of all
at once.

--
Michael Fuhr

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

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> That's a lot of data -- are you aware that psql (via libpq) fetches
> the entire result set before displaying it?  In most cases 18444
> rows wouldn't be a problem, but with rows that wide it becomes a
> big problem because the client has to store it all in memory.  I
> wonder if that's causing psql to segfault, although I'd expect a
> graceful error like "out of memory for query result" unless maybe
> psql consumes so much memory that the OS has problems.

I'm wondering the same --- psql is definitely designed to survive
out-of-memory:

regression=# select * from tenk1 a, tenk1 b limit 2000000;
-- time passes ...
out of memory for query result
regression=#

Emilia might have found some corner case where it doesn't, though;
perhaps a malloc call that's not error-checked.  A stack trace from
the psql core dump would be useful.

            regards, tom lane

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

From
Tom Lane
Date:
Emilia Venturato <venturato@faunalia.it> writes:
> Alle 16:18, venerdì 16 giugno 2006, Tom Lane ha probabilmente scritto:
>> Emilia might have found some corner case where it doesn't, though;
>> perhaps a malloc call that's not error-checked.  A stack trace from
>> the psql core dump would be useful.

> Core dump is not generated.
> How can I generate a back-trace?

You probably need to adjust your "ulimit -c" setting to get a core dump
to be generated.  Alternatively and maybe faster, just run psql under
gdb to start with:

    $ gdb /path/to/psql
    gdb> run arguments-for-psql-go-here
    ... interact with psql normally, provoke crash
    gdb will report SIGSEGV and give you a prompt:
    gdb> bt
    ... stack trace printed here ...
    gdb> quit
    $

If the stack trace contains only numeric addresses then it won't be any
help; in that case please recompile psql with debugging support so you
can get a useful trace.

            regards, tom lane

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

From
Emilia Venturato
Date:
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

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

From
Emilia Venturato
Date:
Alle 16:18, venerd=C3=AC 16 giugno 2006, Tom Lane ha probabilmente scritto:

>
> Emilia might have found some corner case where it doesn't, though;
> perhaps a malloc call that's not error-checked.  A stack trace from
> the psql core dump would be useful.
>
>             regards, tom lane

Core dump is not generated.
How can I generate a back-trace?

ciao
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