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

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