Re: Large index operation crashes postgres

Can you do?

alter table placex add column geometry_sector integer;
update placex set geometry_sector = geometry_sector(geometry);

P.

On Wed, Mar 24, 2010 at 1:15 PM, Frans Hals <fhals7(at)googlemail(dot)com> wrote:
> Hi,
>
> running a geo-database from a dump restore where still one of the most
> important indexes is missing and so the search is slow.
> Whenever I try to add the follwing index to the table "placex", one of
> the postmaster processes dies and the server restarts.
>
> I try:
> CREATE INDEX idx_placex_sector ON placex USING btree
> (geometry_sector(geometry), rank_address, osm_type, osm_id);
>
> The table counts around 50.000.000 rows.
> The first 20.000.000 are indexed in 20-30 minutes. Nice!
> Then indexing becomes slow and slower, first taking 100.000 rows in
> ten minutes while further consequently decreasing speed.
> When the job reaches something around row 25.000.000 postgres goes down:
>
> WARNING:  terminating connection because of crash of another server process
> 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.
>
> I have checked RAM and changed the HD with no success.
> Experimenting with a lot of different memory settings in the conf-file
> didn't help either.
> Is there anybody else who experienced this and found a way to create this index?
> Server is postgres 8.3.9 with 4 GB dedicated RAM.
>
> gemoetry_sector function looks like this (postgis):
>
> DECLARE
>  NEWgeometry geometry;
> BEGIN
> -- RAISE WARNING '%',place;
>  NEWgeometry := place;
>  IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR
> ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity')
> OR ST_Y(ST_Centroid(NEWgeometry))::text in
> ('NaN','Infinity','-Infinity') THEN
>    NEWgeometry := ST_buffer(NEWgeometry,0);
>    IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR
> ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity')
> OR ST_Y(ST_Centroid(NEWgeometry))::text in
> ('NaN','Infinity','-Infinity') THEN
>      RETURN NULL;
>    END IF;
>  END IF;
>  RETURN (500-ST_X(ST_Centroid(NEWgeometry))::integer)*1000 +
> (500-ST_Y(ST_Centroid(NEWgeometry))::integer);
> END;
>
> The subcalled St_Centroid is a postgis C-function located in
> /usr/lib/postgresql/8.3/lib/liblwgeom.
>
>
> Anybody out there has an idea what happens or better how to reach the
> 50.000.000?
>
> Thanks
> Frans
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>