[postgis-users] St_Union crashing

[David Rowley]

On Thu, Aug 1, 2013 at 6:53 AM, David Rowley <dgrowleyml at gmail.com> wrote:

>
> On Thu, Aug 1, 2013 at 6:47 AM, Bborie Park <dustymugs at gmail.com> wrote:
>
>> David,
>>
>> How much RAM does the server have? How many records are there in the "demelevation"
>> table?
>>
>>  -bborie
>>
>>
> I'm testing on my laptop which has 8GB of RAM.
>
> test=# select count(*) from demelevation;
>  count
> --------
>  375440
> (1 row)
>
> test=# show work_mem;
>  work_mem
> ----------
>  16MB
> (1 row)
>
>
> test=# show shared_buffers;
>  shared_buffers
> ----------------
>  128MB
> (1 row)
>
>
>
To expand on this a little. I was try to test if this was a memory problem
by limiting the number of rasters.

I was seeing that if a just used a small number then the query worked ok:

select st_union(rast) from (select rast from demelevation limit 10) a;

(works)

Though when I increased the number the query was taking a long time, so I
just cancelled the query with ctrl+C in psql.

It seems that if I let the query run it does not crash, it's when I
interrupt the query the crash occurs. I was testing with a limit 300 at the
time.


>
>
>>
>> On Wed, Jul 31, 2013 at 11:43 AM, David Rowley <dgrowleyml at gmail.com>wrote:
>>
>>> I'm experiencing a crash with PostgreSQL 9.2 on windows 64 bit.
>>>
>>> psql 1060 idle 2013-08-01 06:34:21 NZST 0 LOG:  statement: select
>>> st_union(rast) from demelevation limit 1;
>>>  4124  2013-08-01 06:35:07 NZST 0 LOG:  server process (PID 1060) was
>>> terminated by exception 0xC0000005
>>>  4124  2013-08-01 06:35:07 NZST 0 DETAIL:  Failed process was running:
>>> select st_union(rast) from demelevation limit 1;
>>>  4124  2013-08-01 06:35:07 NZST 0 HINT:  See C include file "ntstatus.h"
>>> for a description of the hexadecimal value.
>>>  4124  2013-08-01 06:35:07 NZST 0 LOG:  terminating any other active
>>> server processes
>>>  3612  2013-08-01 06:35:07 NZST 0 WARNING:  terminating connection
>>> because of crash of another server process
>>>  3612  2013-08-01 06:35:07 NZST 0 DETAIL:  The postmaster has commanded
>>> this server process to roll back the current transaction and exit, because
>>> another server process exited abnormally and possibly corrupted shared
>>> memory.
>>>  3612  2013-08-01 06:35:07 NZST 0 HINT:  In a moment you should be able
>>> to reconnect to the database and repeat your command.
>>>  4124  2013-08-01 06:35:07 NZST 0 LOG:  all server processes terminated;
>>> reinitializing
>>>
>>>
>>> test=# select version();
>>>                            version
>>> -------------------------------------------------------------
>>>  PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit
>>>
>>> test=# select postgis_version();
>>>             postgis_version
>>> ---------------------------------------
>>>  2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>>>
>>> I can supply a full recreation script if required. In the meantime here
>>> is the table definition of demelevation
>>>
>>>
>>>                            Table "public.demelevation"
>>>   Column  |  Type   |                         Modifiers
>>>
>>> ----------+---------+------------------------------------------------------------
>>>  rid      | integer | not null default
>>> nextval('demelevation_rid_seq'::regclass)
>>>  rast     | raster  |
>>>  filename | text    |
>>> Indexes:
>>>     "demelevation_pkey" PRIMARY KEY, btree (rid)
>>>     "demelevation_rast_gist" gist (st_convexhull(rast))
>>> Check constraints:
>>>     "enforce_height_rast" CHECK (st_height(rast) = 16)
>>>     "enforce_max_extent_rast" CHECK (st_coveredby(st_convexhull(rast),
>>> '0103000020E6100000010000000C00000063C92F96FCDF64400E74DA40A70148C063C92F96FCBF64400E74DA4
>>>
>>> 0A78147C063C92F96FCBF64408C25BF58F27F46C063C92F96FC7F65408C25BF58F2FF40C0039D36D069C065408C25BF58F2FF40C0039D36D0690066408C25BF58F27F41C0039D36D0696066408C25BF58
>>>
>>> F27F42C0039D36D0696066400E74DA40A70144C0039D36D0692066400E74DA40A70145C0039D36D0696065400E74DA40A78147C0039D36D0692065400E74DA40A70148C063C92F96FCDF64400E74DA40A
>>> 70148C0'::geometry))
>>>     "enforce_nodata_values_rast" CHECK
>>> (_raster_constraint_nodata_values(rast)::numeric(16,10)[] =
>>> '{-32768}'::numeric(16,10)[])
>>>     "enforce_num_bands_rast" CHECK (st_numbands(rast) = 1)
>>>     "enforce_out_db_rast" CHECK (_raster_constraint_out_db(rast) =
>>> '{f}'::boolean[])
>>>     "enforce_pixel_types_rast" CHECK
>>> (_raster_constraint_pixel_types(rast) = '{16BSI}'::text[])
>>>     "enforce_same_alignment_rast" CHECK (st_samealignment(rast,
>>> '01000000004F1BE8B4814E4B3F4F1BE8B4814E4BBF63C92F96FC7F65408C25BF58F2FF40C00000000000000000000000
>>> 0000000000E610000001000100'::raster))
>>>     "enforce_scalex_rast" CHECK (st_scalex(rast)::numeric(16,10) =
>>> 0.000833333333333333::numeric(16,10))
>>>     "enforce_scaley_rast" CHECK (st_scaley(rast)::numeric(16,10) =
>>> (-0.000833333333333333)::numeric(16,10))
>>>     "enforce_srid_rast" CHECK (st_srid(rast) = 4326)
>>>     "enforce_width_rast" CHECK (st_width(rast) = 16)
>>>
>>> Regards
>>>
>>> David
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130801/20aa8bbf/attachment.html>