Hello list,
I have an interesting error that's coming up on an Ubuntu 64-bit system,
running PostgreSQL 8.4.2, and PostGIS 1.5.1 (packaged by me) as well as 1.4.0
(from Ubuntu repos). The query itself is somewhat complex (I'm sure it could
be optimized better - but it's been working well for quite some time). I've
attached two files - one is a dummed-down version of the query I am running
(crashing.sql). After discussing this with some folks in the postgresql IRC
channel, it seems that the st_geom(), or something PostGIS-related is causing
the error.
Running this query on various data will produce one of two results. One is
the error mentioned in the subject (ERROR: array size exceeds the maximum
allowed (134217727)). I can find very little information on this error. The
other outcome is that it often causes the PostgreSQL backend to segfault (see
gdboutput.txt).
I can attest that this query works fine on many installations of PostgreSQL
8.2+, and PostGIS 1.3+. The only main difference I can see in this case where
I'm getting errors and segfaults is that I'm running this on Ubuntu, whereas
all of my successful cases are on Fedora (7 through 12).
If I replace the st_union() function in the query with accum(), it still
produces the error/segfaults.
Also, strangely, if I remove one of the where conditions near the end of the
query (e.g., " AND (cl.depth = 0)"), it suddenly works fine without any issues
(except that I actually need that condition).
Does this sort of problem look familiar to anyone? I can try to put together
an example if that would be of any use, but since the same data/query works on
other Fedora systems that I am running, I'm not sure how easy it is to
replicate.
Regards,
Mike
SELECT tdesc.* FROM (
SELECT cl.id, cl.parent_id, cl.username, cl.type, cl.text, cl.time, cl.flags, cl.depth, '[stuff]' as extent, csls.selection, csld.drawing, round(date_part('epoch',time)*1000.0) AS epoch_ms, csls.selection, csld.drawing, ctu.target_users, ctg.target_groups, clr.replies, case when (num_target_users>0 or num_target_groups>0) then true else false end AS private FROM testdb.chat_log AS cl
LEFT JOIN (
SELECT '[stuff]' AS selection, st_union(geom) AS ugeom, csl1.chat_id FROM testdb.user_selections AS us1
INNER JOIN testdb.chat_selection_links AS csl1 ON us1.id = csl1.selection_id WHERE (not us1.user_drawing and not csl1.deleted) GROUP BY chat_id
) AS csls ON csls.chat_id = cl.id
LEFT JOIN (
SELECT '[stuff]' AS drawing, st_union(geom) AS ugeom, csl2.chat_id FROM testdb.user_selections AS us2
INNER JOIN testdb.chat_selection_links AS csl2 ON us2.id = csl2.selection_id WHERE (us2.user_drawing and us2.deleted and not csl2.deleted) GROUP BY chat_id
) AS csld ON csld.chat_id = cl.id
LEFT JOIN (
SELECT chat_target_users.id, count(*) AS num_target_users, '[stuff]'::varchar AS target_users, ARRAY['stuff']::text[] AS target_users_array FROM testdb.chat_target_users GROUP BY id
) AS ctu ON cl.id = ctu.id
LEFT JOIN (
SELECT chat_target_groups.id, count(*) AS num_target_groups, '[stuff]'::varchar AS target_groups, ARRAY['stuff']::text[] AS target_groups_array FROM testdb.chat_target_groups GROUP BY id
) AS ctg ON cl.id = ctg.id
LEFT JOIN (
SELECT chat_log.parent_id AS id, '[stuff]' AS replies FROM testdb.chat_log GROUP BY parent_id
) AS clr ON cl.id = clr.id WHERE ((num_target_users is null and num_target_groups is null) or (num_target_users+num_target_groups)=0 or ((cl.username = 'admin' or 'admin' = any(target_users_array) or ARRAY['admin','everyone'] && target_groups_array) and (time>'2010-03-20 00:52:57.230968-04'::timestamptz))) AND (not deleted) AND (cl.depth = 0) ORDER BY cl.id desc LIMIT 25
) AS tdesc ORDER BY tdesc.id asc;GNU gdb (GDB) 7.0-ubuntu
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>...
Reading symbols from /usr/lib/postgresql/8.4/bin/postgres...(no debugging
symbols found)...done.
warning: Can't read pathname for load map: Input/output error.
Loaded symbols for /usr/lib/postgresql/8.4/bin/postgres
Reading symbols from /usr/lib/libxml2.so.2...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libxml2.so.2
Reading symbols from /lib/libpam.so.0...(no debugging symbols found)...done.
Loaded symbols for /lib/libpam.so.0
Reading symbols from /lib/libssl.so.0.9.8...(no debugging symbols
found)...done.
Loaded symbols for /lib/libssl.so.0.9.8
Reading symbols from /lib/libcrypto.so.0.9.8...(no debugging symbols
found)...done.
Loaded symbols for /lib/libcrypto.so.0.9.8
Reading symbols from /usr/lib/libkrb5.so.3...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libkrb5.so.3
Reading symbols from /lib/libcom_err.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib/libcom_err.so.2
Reading symbols from /usr/lib/libgssapi_krb5.so.2...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libgssapi_krb5.so.2
Reading symbols from /lib/libdl.so.2...Reading symbols from
/usr/lib/debug/lib/libdl-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/libm.so.6...Reading symbols from
/usr/lib/debug/lib/libm-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib/libm.so.6
Reading symbols from /usr/lib/libldap_r-2.4.so.2...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libldap_r-2.4.so.2
Reading symbols from /lib/libc.so.6...Reading symbols from
/usr/lib/debug/lib/libc-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib/libc.so.6
Reading symbols from /lib/libz.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib/libz.so.1
Reading symbols from /usr/lib/libk5crypto.so.3...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libk5crypto.so.3
Reading symbols from /usr/lib/libkrb5support.so.0...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libkrb5support.so.0
Reading symbols from /lib/libkeyutils.so.1...(no debugging symbols
found)...done.
Loaded symbols for /lib/libkeyutils.so.1
Reading symbols from /lib/libresolv.so.2...Reading symbols from
/usr/lib/debug/lib/libresolv-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib/libresolv.so.2
Reading symbols from /lib/libpthread.so.0...Reading symbols from
/usr/lib/debug/lib/libpthread-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib/libpthread.so.0
Reading symbols from /lib64/ld-linux-x86-64.so.2...Reading symbols from
/usr/lib/debug/lib/ld-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /usr/lib/liblber-2.4.so.2...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/liblber-2.4.so.2
Reading symbols from /usr/lib/libsasl2.so.2...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libsasl2.so.2
Reading symbols from /usr/lib/libgnutls.so.26...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libgnutls.so.26
Reading symbols from /usr/lib/libtasn1.so.3...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libtasn1.so.3
Reading symbols from /lib/libgcrypt.so.11...(no debugging symbols
found)...done.
Loaded symbols for /lib/libgcrypt.so.11
Reading symbols from /lib/libgpg-error.so.0...(no debugging symbols
found)...done.
Loaded symbols for /lib/libgpg-error.so.0
Reading symbols from /lib/libnss_compat.so.2...Reading symbols from
/usr/lib/debug/lib/libnss_compat-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib/libnss_compat.so.2
Reading symbols from /lib/libnsl.so.1...Reading symbols from
/usr/lib/debug/lib/libnsl-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/libnss_nis.so.2...Reading symbols from
/usr/lib/debug/lib/libnss_nis-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib/libnss_nis.so.2
Reading symbols from /lib/libnss_files.so.2...Reading symbols from
/usr/lib/debug/lib/libnss_files-2.10.1.so...done.
(no debugging symbols found)...done.
Loaded symbols for /lib/libnss_files.so.2
Reading symbols from /usr/lib/postgresql/8.4/lib/postgis-1.5.so...(no debugging
symbols found)...done.
Loaded symbols for /usr/lib/postgresql/8.4/lib/postgis-1.5.so
Reading symbols from /usr/lib/libgeos_c.so.1...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libgeos_c.so.1
Reading symbols from /usr/lib/libproj.so.0...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libproj.so.0
Reading symbols from /usr/lib/libgeos-3.1.1.so...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libgeos-3.1.1.so
Reading symbols from /usr/lib/libstdc++.so.6...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libstdc++.so.6
Reading symbols from /lib/libgcc_s.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib/libgcc_s.so.1
Core was generated by `postgres: postgres testdb [local] SELECT
'.
Program terminated with signal 11, Segmentation fault.
#0 0x00007fa4be23615b in pfree () from /usr/lib/postgresql/8.4/bin/postgres
(gdb) bt full
#0 0x00007fa4be23615b in pfree () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#1 0x00007fa4be18091b in makeMdArrayResult () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#2 0x00007fa4b7f038bc in pgis_accum_finalfn () from
/usr/lib/postgresql/8.4/lib/postgis-1.5.so
No symbol table info available.
#3 0x00007fa4b7f039ee in pgis_geometry_union_finalfn () from
/usr/lib/postgresql/8.4/lib/postgis-1.5.so
No symbol table info available.
#4 0x00007fa4be0c33f5 in ?? () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#5 0x00007fa4be0c3920 in ExecAgg () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#6 0x00007fa4be0b7148 in ExecProcNode () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#7 0x00007fa4be0c9f04 in ExecNestLoop () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#8 0x00007fa4be0b71a8 in ExecProcNode () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#9 0x00007fa4be0c9f31 in ExecNestLoop () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#10 0x00007fa4be0b71a8 in ExecProcNode () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#11 0x00007fa4be0c9f31 in ExecNestLoop () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#12 0x00007fa4be0b71a8 in ExecProcNode () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#13 0x00007fa4be0cb9d1 in ExecSort () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#14 0x00007fa4be0b7168 in ExecProcNode () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
---Type <return> to continue, or q <return> to quit---
#15 0x00007fa4be0cc9f8 in ExecLimit () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#16 0x00007fa4be0b70f5 in ExecProcNode () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#17 0x00007fa4be0cb9d1 in ExecSort () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#18 0x00007fa4be0b7168 in ExecProcNode () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#19 0x00007fa4be0b4bc2 in standard_ExecutorRun () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#20 0x00007fa4be166c17 in ?? () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#21 0x00007fa4be168070 in PortalRun () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#22 0x00007fa4be163021 in ?? () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#23 0x00007fa4be164a0d in PostgresMain () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#24 0x00007fa4be1374d0 in ?? () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#25 0x00007fa4be139c31 in PostmasterMain () from
/usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
#26 0x00007fa4be0e2993 in main () from /usr/lib/postgresql/8.4/bin/postgres
No symbol table info available.
(gdb)
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
