The following bug has been logged online:

Bug reference: 2481
Logged by: Emilia Venturato
Email address: venturato (AT) faunalia (DOT) 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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Emilia Venturato" <venturato (AT) faunalia (DOT) it> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Thu, Jun 15, 2006 at 11:48:37PM -0400, Tom Lane wrote:
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/piper...ne/012281.html
http://postgis.refractions.net/piper...ne/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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

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]
[without]
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, Jun 16, 2006 at 06:53:28AM -0600, Michael Fuhr wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Emilia Venturato <venturato (AT) faunalia (DOT) it> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

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

ciao
Lia

--=20
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Alle 07:36, venerd=EC 16 giugno 2006, hai probabilmente scritto:
Yes, we had off-list discussion.

version=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=2 0=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=2 0
---------------------------------------------------------------------------=
---------------------------------------
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)

It goes well.

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

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 (AT) faunalia (DOT) it
www.faunalia.it
Tel: (+39) 347-2770007 Tel+Fax: (+39) 0587-213742
Piazza Garibaldi 5 - 56025 Pontedera (PI), Italy

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings