Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
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
Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
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. > > regards, tom lane Core dump is not generated. How can I generate a back-trace? ciao Lia -- Emilia Venturato email+jabber: [EMAIL PROTECTED] 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
Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
Emilia Venturato <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
Michael Fuhr <[EMAIL PROTECTED]> 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 200; -- 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
Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
On Thu, Jun 15, 2006 at 11:48:37PM -0400, Tom Lane wrote: > "Emilia Venturato" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
"Emilia Venturato" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2481: select from table's join with geometries doesn't go
The following bug has been logged online: Bug reference: 2481 Logged by: Emilia Venturato Email address: [EMAIL PROTECTED] 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
