Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#2050 closed defect (fixed)

Unable to add constraint: 'extent'. Skipping

Reported by: mgm1701 Owned by: pracine
Priority: medium Milestone: PostGIS 2.0.2
Component: raster Version: 2.0.x
Keywords: Cc:

Description

When executing: SELECT AddRasterConstraints?('auslig','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE); The following results

NOTICE: Adding SRID constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_srid_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_srid(name,name,name) line 33 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 57 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding scale-X constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_scalex_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_scale(name,name,name,character) line 38 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 60 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding scale-Y constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_scaley_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_scale(name,name,name,character) line 38 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 63 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding blocksize-X constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_width_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_blocksize(name,name,name,text) line 37 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 71 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding blocksize-Y constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_height_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_blocksize(name,name,name,text) line 37 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 74 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding alignment constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_same_alignment_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_alignment(name,name,name) line 29 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 82 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding number of bands constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_num_bands_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_num_bands(name,name,name) line 31 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 88 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding pixel type constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_pixel_types_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_pixel_types(name,name,name) line 44 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 91 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding nodata value constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_nodata_values_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_nodata_values(name,name,name) line 48 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 94 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding out-of-database constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: The constraint "enforce_out_db_rast" already exists. To replace the existing constraint, delete the constraint and call ApplyRasterConstraints? again CONTEXT: PL/pgSQL function _add_raster_constraint_out_db(name,name,name) line 48 at RETURN PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 97 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding maximum extent constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Unable to get the extent of a sample raster CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 100 at assignment PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN WARNING: Unable to add constraint: 'extent'. Skipping CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN Total query runtime: 505575 ms. 1 row retrieved.

And there no data is in extent.raster_columns

SELECT r_table_name, r_raster_column, srid, scale_x, scale_y, blocksize_x, blocksize_y, same_alignment, regular_blocking, num_bands, pixel_types, nodata_values, out_db, extent

FROM raster_columns

r_table_name r_raster_column srid scale_x scale_y blocksize_x blocksize_y same_alignment regular_blocking num_bands pixel_types nodata_values out_db extent auslig rast 4283 0.0025 -0.0025 2 2 f f 1 {32BF} {-9999} {f} auslig_sd52 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000805F4000000000000030C00000000000805F40703D0AD7A33026C00000000000806040703D0AD7A33026C0000000000080604000000000000030C00000000000805F4000000000000030C0 auslig_sd53 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000080604000000000000030C00000000000806040EC51B81E85EB25C0295C8FC2F5286140EC51B81E85EB25C0295C8FC2F528614000000000000030C0000000000080604000000000000030C0 auslig_sd54 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB100000010000000500000048E17A14AEAB614000000000000030C048E17A14AEAB614000000000000022C0EC51B81E852F624000000000000022C0EC51B81E852F624000000000000030C048E17A14AEAB614000000000000030C0 auslig_se51 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000005E4000000000000034C00000000000005E40D7A3703D0AD72BC00000000000805F40D7A3703D0AD72BC00000000000805F4000000000000034C00000000000005E4000000000000034C0 auslig_se52 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000805F4000000000000034C00000000000805F4000000000000030C0000000000080604000000000000030C0000000000080604000000000000034C00000000000805F4000000000000034C0 auslig_se53 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000080604000000000000034C0000000000080604000000000000030C0000000000040614000000000000030C0000000000040614000000000000034C0000000000080604000000000000034C0 auslig_se54 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000040614000000000000034C0000000000040614000000000000030C0000000000000624000000000000030C0000000000000624000000000000034C0000000000040614000000000000034C0 auslig_se55 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000000624000000000000034C0000000000000624000000000000030C0EB51B81E858F624000000000000030C0EB51B81E858F624000000000000034C0000000000000624000000000000034C0 auslig_sf50 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000C3F5285C8F5A5C40FFFFFFFFFFFF37C0C3F5285C8F5A5C4014AE47E17A9433C00000000000005E4014AE47E17A9433C00000000000005E40FFFFFFFFFFFF37C0C3F5285C8F5A5C40FFFFFFFFFFFF37C0 auslig_sf51 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000005E4000000000000038C00000000000005E4000000000000034C00000000000805F4000000000000034C00000000000805F4000000000000038C00000000000005E4000000000000038C0 auslig_sf52 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000805F4000000000000038C00000000000805F4000000000000034C0000000000080604000000000000034C0000000000080604000000000000038C00000000000805F4000000000000038C0 auslig_sf53 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000080604000000000000038C0000000000080604000000000000034C0000000000040614000000000000034C0000000000040614000000000000038C0000000000080604000000000000038C0 auslig_sf54 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000040614000000000000038C0000000000040614000000000000034C0000000000000624000000000000034C0000000000000624000000000000038C0000000000040614000000000000038C0 auslig_sf55 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000000624000000000000038C0000000000000624000000000000034C0CDCCCCCCCC0C634000000000000034C0CDCCCCCCCC0C634000000000000038C0000000000000624000000000000038C0 auslig_sg50 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000001F85EB51B83A5C400000000000003CC01F85EB51B83A5C4000000000000038C05C8FC2F528005E4000000000000038C05C8FC2F528005E400000000000003CC01F85EB51B83A5C400000000000003CC0 auslig_sg51 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000005E400000000000003CC00000000000005E4000000000000038C00000000000805F4000000000000038C00000000000805F400000000000003CC00000000000005E400000000000003CC0 auslig_sg52 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000805F400000000000003CC00000000000805F4000000000000038C0000000000080604000000000000038C000000000008060400000000000003CC00000000000805F400000000000003CC0 auslig_sg53 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB100000010000000500000000000000008060400000000000003CC0000000000080604000000000000038C0000000000040614000000000000038C000000000004061400000000000003CC000000000008060400000000000003CC0 auslig_sg54 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB100000010000000500000000000000004061400000000000003CC0000000000040614000000000000038C0000000000000624000000000000038C000000000000062400000000000003CC000000000004061400000000000003CC0 auslig_sg55 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB100000010000000500000000000000000062400000000000003CC0000000000000624000000000000038C00000000000C0624000000000000038C00000000000C062400000000000003CC000000000000062400000000000003CC0 auslig_sg56 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000C062400000000000003CC00000000000C0624000000000000038C0999999999931634000000000000038C099999999993163400000000000003CC00000000000C062400000000000003CC0 auslig_sh50 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB100000010000000500000085EB51B81E655C4000000000000040C085EB51B81E655C400000000000003CC00000000000005E400000000000003CC00000000000005E4000000000000040C085EB51B81E655C4000000000000040C0 auslig_sh51 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000005E4000000000000040C00000000000005E400000000000003CC00000000000805F400000000000003CC00000000000805F4000000000000040C00000000000005E4000000000000040C0 auslig_sh52 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000805F40B81E85EB512840C00000000000805F40FFFFFFFFFFFF3BC00000000000806040FFFFFFFFFFFF3BC00000000000806040B81E85EB512840C00000000000805F40B81E85EB512840C0 auslig_sh53 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000080604000000000000040C000000000008060400000000000003CC000000000004061400000000000003CC0000000000040614000000000000040C0000000000080604000000000000040C0 auslig_sh54 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000040614000000000000040C000000000004061400000000000003CC000000000000062400000000000003CC0000000000000624000000000000040C0000000000040614000000000000040C0 auslig_sh55 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000000624000000000000040C000000000000062400000000000003CC00000000000C062400000000000003CC00000000000C0624000000000000040C0000000000000624000000000000040C0 auslig_sh56 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000C0624000000000000040C00000000000C062400000000000003CC014AE47E17A3463400000000000003CC014AE47E17A34634000000000000040C00000000000C0624000000000000040C0 auslig_si50 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB100000010000000500000052B81E85EBBD5C400BD7A3703D9A41C052B81E85EBBD5C4000000000000040C05C8FC2F528005E4000000000000040C05C8FC2F528005E400BD7A3703D9A41C052B81E85EBBD5C400BD7A3703D9A41C0 auslig_si51 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000005E40AE47E17A143E41C00000000000005E4000000000000040C00000000000805F4000000000000040C00000000000805F40AE47E17A143E41C00000000000005E40AE47E17A143E41C0 auslig_si53 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB100000010000000500000000000000008060400BD7A3703D0A42C0000000000080604000000000000040C0000000000040614000000000000040C000000000004061400BD7A3703D0A42C000000000008060400BD7A3703D0A42C0 auslig_si54 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000040614000000000000042C0000000000040614000000000000040C0000000000000624000000000000040C0000000000000624000000000000042C0000000000040614000000000000042C0 auslig_si55 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000000624000000000000042C0000000000000624000000000000040C0000000000090624000000000000040C0000000000090624000000000000042C0000000000000624000000000000042C0 auslig_si56 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000000000000090624000000000000042C0000000000090624000000000000040C0EB51B81E8513634000000000000040C0EB51B81E8513634000000000000042C0000000000090624000000000000042C0 auslig_sj54 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB1000000100000005000000F6285C8FC26D6140AE47E17A146E43C0F6285C8FC26D614000000000000042C0000000000000624000000000000042C00000000000006240AE47E17A146E43C0F6285C8FC26D6140AE47E17A146E43C0 auslig_sj55 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000000000000000006240A4703D0AD7C343C0000000000000624000000000000042C08FC2F5285CC7624000000000000042C08FC2F5285CC76240A4703D0AD7C343C00000000000006240A4703D0AD7C343C0 auslig_sk55 rast 4283 0.0025 -0.0025 2 2 t f 1 {32BF} {-9999} {f} 0103000020BB10000001000000050000005C8FC2F528FA6140D8A3703D0ADF45C05C8FC2F528FA6140EC51B81E85C343C052B81E85EB8F6240EC51B81E85C343C052B81E85EB8F6240D8A3703D0ADF45C05C8FC2F528FA6140D8A3703D0ADF45C0

"POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER"

I believe it could be a memory problem as postgresql memory usage sky rockets to 100%.

Attachments (1)

auslig.sql (13.6 KB) - added by mgm1701 7 years ago.
auslig tables

Download all attachments as: .zip

Change History (6)

Changed 7 years ago by mgm1701

Attachment: auslig.sql added

auslig tables

comment:1 Changed 7 years ago by Bborie Park

You'll need to provide more details about the table. How many rows are there? And your dump of raster_columns shows that there are data in that view. So, what data is missing.

You will also want to use formatting from now on as your entire ticket is a mess.

comment:2 Changed 7 years ago by Bborie Park

Based upon the message "Unable to get the extent of a sample raster", try the following

SELECT st_ashexewkb(st_convexhull(st_collect(st_convexhull(rast)))) FROM auslig

That is the query underlying the extent constraint.

comment:3 Changed 7 years ago by Bborie Park

After looking at your sql file, you're using partitions. So, I'm going to assume many inherited tables, each holding many rows. It that situation, you'll more than likely run out of memory. ST_Collect tries to be fast, which means it tries to cache input geometries until the end. I'll add a fallback approach using ST_MemUnion, which does not caching but is orders of magnitude slower.

comment:4 Changed 7 years ago by Bborie Park

Resolution: fixed
Status: newclosed

Added in r10455.

comment:5 Changed 7 years ago by Bborie Park

Added in r10456 for 2.0

Note: See TracTickets for help on using tickets.