AddRasterConstraints failing to create extents in raster_column view

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

AddRasterConstraints failing to create extents in raster_column view

ryagz
Hi,

I have very large table of about 50GB, while uploading to database i am dividing the images into tiles and uploading them as rows in the table. After inserting all the data i am Creating index, AddingRasterConstraints and respective overview constraints.

The problem i am facing is AddRasterConstraints function is some times not creating extents in the raster_column view. While loading back the data i am trying to use raster_columns table to get the raster information so as to speed up the loading process. So, it is very important for me that AddRasterConstraints returns appropriate values. Can you please let me know wat could the problem possibly be and any information on how to debug and fix the problem is highly appreciated.

Other problem i would like to report is, AddRasterConstraints is taking too much time. For the table mentioned above which has 64x64 tile in each row and 2263390 rows in the table it took about 3850 sec which was about 20% of total upload time(19440 sec). Is AddRasterConstraints expected to take so long? if so is there any way to optimize it?

Thanks in advance.
Ryagz
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

Bborie Park-2
All constraints generated through AddRasterConstraints() is done on
the data in the table.  You can always load one raster file and apply
those constraints that can are common for all raster files, usually
SRID, scale, skew, blocksize, same alignment, number of bands, pixel
types, nodata_values and out-db.

What output is there when you try to apply the extent constraint?  Are
you getting the message "Unable to get the extent of the raster
column. Attempting memory efficient (slower) approach"?

See what happens when you call the underlying extent constraint
addition function:

SELECT _add_raster_constraint_extent(SCHEMA_NAME, TABLE_NAME, COLUMN_NAME);

-bborie

On Tue, Apr 16, 2013 at 6:46 PM, ryagz <[hidden email]> wrote:

> Hi,
>
> I have very large table of about 50GB, while uploading to database i am
> dividing the images into tiles and uploading them as rows in the table.
> After inserting all the data i am Creating index, AddingRasterConstraints
> and respective overview constraints.
>
> The problem i am facing is AddRasterConstraints function is some times not
> creating extents in the raster_column view. While loading back the data i am
> trying to use raster_columns table to get the raster information so as to
> speed up the loading process. So, it is very important for me that
> AddRasterConstraints returns appropriate values. Can you please let me know
> wat could the problem possibly be and any information on how to debug and
> fix the problem is highly appreciated.
>
> Other problem i would like to report is, AddRasterConstraints is taking too
> much time. For the table mentioned above which has 64x64 tile in each row
> and 2263390 rows in the table it took about 3850 sec which was about 20% of
> total upload time(19440 sec). Is AddRasterConstraints expected to take so
> long? if so is there any way to optimize it?
>
> Thanks in advance.
> Ryagz
>
>
>
> --
> View this message in context: http://postgis.17.x6.nabble.com/AddRasterConstraints-failing-to-create-extents-in-raster-column-view-tp5003029.html
> Sent from the PostGIS - Dev mailing list archive at Nabble.com.
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

ryagz
>All constraints generated through AddRasterConstraints() is done on
>the data in the table.  You can always load one raster file and apply
>those constraints that can are common for all raster files, usually
>SRID, scale, skew, blocksize, same alignment, number of bands, pixel
>types, nodata_values and out-db.


If i get values like SRID, Scale etc from a raster file can i directly set those as rasterConstraints? if so, can u please refer me to how it can be done.

>What output is there when you try to apply the extent constraint?  Are
>you getting the message "Unable to get the extent of the raster
>column. Attempting memory efficient (slower) approach"?

_add_raster_constraint_extent is also taking quite some time, i would have to apply this on base table and all the overview tables. so cumulatively its large. Please let me know if this can also be reduced some way.

>See what happens when you call the underlying extent constraint
>addition function:
>SELECT _add_raster_constraint_extent(SCHEMA_NAME, TABLE_NAME, COLUMN_NAME);

I tried the query SELECT _add_raster_constraint_extent('tkp_testing', 'test_wb32_64', 'rast');

and the return result was 'f'.

Output message was :

NOTICE:  Unable to get the extent of a sample raster

Total query runtime: 355277 ms.
1 row retrieved.
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

Bborie Park-2
No, you can't specify the criteria of a raster constraint.  What
you're forgetting is that even if you could set the criteria of the
constraint, the table still needs to inspect the data in the table to
ensure that the constraint isn't violated.  This inspection is where
most of the time is spent.

Based upon the output message, your PostGIS install is either 2.0.0 or
2.0.1.  Upgrade to 2.0.3 as some changes were made to attempt
alternate routes...

-bborie

On Wed, Apr 17, 2013 at 7:04 AM, ryagz <[hidden email]> wrote:

>>All constraints generated through AddRasterConstraints() is done on
>>the data in the table.  You can always load one raster file and apply
>>those constraints that can are common for all raster files, usually
>>SRID, scale, skew, blocksize, same alignment, number of bands, pixel
>>types, nodata_values and out-db.
>
>
> If i get values like SRID, Scale etc from a raster file can i directly set
> those as rasterConstraints? if so, can u please refer me to how it can be
> done.
>
>>What output is there when you try to apply the extent constraint?  Are
>>you getting the message "Unable to get the extent of the raster
>>column. Attempting memory efficient (slower) approach"?
>
> _add_raster_constraint_extent is also taking quite some time, i would have
> to apply this on base table and all the overview tables. so cumulatively its
> large. Please let me know if this can also be reduced some way.
>
>>See what happens when you call the underlying extent constraint
>>addition function:
>>SELECT _add_raster_constraint_extent(SCHEMA_NAME, TABLE_NAME, COLUMN_NAME);
>
> I tried the query SELECT _add_raster_constraint_extent('tkp_testing',
> 'test_wb32_64', 'rast');
>
> and the return result was 'f'.
>
> Output message was :
>
> NOTICE:  Unable to get the extent of a sample raster
>
> Total query runtime: 355277 ms.
> 1 row retrieved.
>
>
>
>
> --
> View this message in context: http://postgis.17.x6.nabble.com/AddRasterConstraints-failing-to-create-extents-in-raster-column-view-tp5003029p5003038.html
> Sent from the PostGIS - Dev mailing list archive at Nabble.com.
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

ryagz
Hi,

I uninstalled postgis 2.0.1 and installed 2.0.3. and ran the query. The result is still the same

NOTICE:  Unable to get the extent of a sample raster
Total query runtime: 1668838 ms.
1 row retrieved.

can u suggest what would be wrong?
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

Bborie Park-2
Based upon the message, I'm guessing you didn't upgrade the SQL.  Run
the script: rtpostgis_upgrade_20_minor.sql.

-bborie

On Thu, Apr 18, 2013 at 10:48 PM, ryagz <[hidden email]> wrote:

> Hi,
>
> I uninstalled postgis 2.0.1 and installed 2.0.3. and ran the query. The
> result is still the same
>
> NOTICE:  Unable to get the extent of a sample raster
> Total query runtime: 1668838 ms.
> 1 row retrieved.
>
> can u suggest what would be wrong?
>
>
>
> --
> View this message in context: http://postgis.17.x6.nabble.com/AddRasterConstraints-failing-to-create-extents-in-raster-column-view-tp5003029p5003051.html
> Sent from the PostGIS - Dev mailing list archive at Nabble.com.
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

ryagz
I ran the .sql file, it ran a sequence of sql queries and then gave some errors and roll back like bellow:

psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3764: ERROR:  function "st_union" already exists with same a
rgument types
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3771: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3780: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3786: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3868: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3874: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3880: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3886: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3892: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3898: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3922: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3952: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:3968: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4006: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4012: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4028: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4071: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4084: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4100: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4142: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4155: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4171: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4218: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4224: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4240: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4274: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4280: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4301: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4327: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4333: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4349: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4385: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4391: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4407: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4412: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4461: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4467: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4483: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4488: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4541: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4547: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4563: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4568: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4621: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4627: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4761: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4771: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4846: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:4867: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5001: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5011: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5086: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5107: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5145: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5155: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5177: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5210: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5216: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5248: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5329: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5339: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5397: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
psql:C:/Program Files (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
pgrade_20_minor.sql:5406: ERROR:  current transaction is aborted, commands ignor
ed until end of transaction block
ROLLBACK

-Ryagz
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

Bborie Park-2
Something else wrong.  I just tested upgrading from 2.0.1 to 2.0.3 using
rtpostgis_upgrade_20_minor.sql and there were no problems.

Where'd you get your postgis installer?

-bborie

On 04/19/2013 10:09 AM, ryagz wrote:

> I ran the .sql file, it ran a sequence of sql queries and then gave some
> errors and roll back like bellow:
>
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3764: ERROR:  function "st_union" already exists with
> same a
> rgument types
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3771: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3780: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3786: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3868: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3874: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3880: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3886: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3892: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3898: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3922: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3952: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:3968: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4006: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4012: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4028: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4071: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4084: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4100: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4142: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4155: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4171: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4218: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4224: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4240: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4274: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4280: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4301: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4327: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4333: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4349: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4385: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4391: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4407: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4412: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4461: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4467: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4483: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4488: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4541: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4547: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4563: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4568: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4621: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4627: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4761: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4771: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4846: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:4867: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5001: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5011: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5086: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5107: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5145: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5155: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5177: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5210: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5216: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5248: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5329: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5339: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5397: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> psql:C:/Program Files
> (x86)/PostgreSQL/9.1/share/contrib/postgis-2.0/rtpostgis_u
> pgrade_20_minor.sql:5406: ERROR:  current transaction is aborted, commands
> ignor
> ed until end of transaction block
> ROLLBACK
>
> -Ryagz
>
>
>
> --
> View this message in context: http://postgis.17.x6.nabble.com/AddRasterConstraints-failing-to-create-extents-in-raster-column-view-tp5003029p5003059.html
> Sent from the PostGIS - Dev mailing list archive at Nabble.com.
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

ryagz
Usually I install posgis form the postgresql's stack builder. That is where i got 2.0.1.

Today i also tried re installing postgresql and postgis, the stack builder directly gave 2.0.3.  I tried addRasterConstraints on the same table and I am getting same error.  May be i will try uploading to fresh table again and see if this can be reproduced.
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

ryagz
Hi,

This is pretty strange, I have Freshly uploaded with postgis 2.0.3 and i am still getting the same error.

One more observation which i have made is extents constraints has failed only when i gave tile size 64*64, it is succeeding for case when tiles size is 128*128 and 256*256.

The number of rows in 128*128 is 549120 and  in 64*64 is 2190390. Would the reason for the error be too many number of rows in the table?

-Ryagz
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

Bborie Park-2
The number of rows itself wouldn't be the problem.  What is the exact
error message you getting in 2.0.3?

-bborie

On Mon, Apr 22, 2013 at 1:52 AM, ryagz <[hidden email]> wrote:

> Hi,
>
> This is pretty strange, I have Freshly uploaded with postgis 2.0.3 and i am
> still getting the same error.
>
> One more observation which i have made is extents constraints has failed
> only when i gave tile size 64*64, it is succeeding for case when tiles size
> is 128*128 and 256*256.
>
> The number of rows in 128*128 is 549120 and  in 64*64 is 2190390. Would the
> reason for the error be too many number of rows in the table?
>
> -Ryagz
>
>
>
> --
> View this message in context: http://postgis.17.x6.nabble.com/AddRasterConstraints-failing-to-create-extents-in-raster-column-view-tp5003029p5003070.html
> Sent from the PostGIS - Dev mailing list archive at Nabble.com.
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

ryagz
I ran this query from PGAdmins Query tool and the History found in that tool is like this:

SELECT _add_raster_constraint_extent('tkp_raster_satellite', 'wb32_64_1', 'rast');
NOTICE:  Unable to get the extent of a sample raster
Total query runtime: 1119183 ms.
1 row retrieved.
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

Bborie Park-2
Strange.

Can you provide the output of the following?

SELECT version() || ' ' || postgis_full_version();

If you're actually using 2.0.3, the NOTICE that you provided has
additional information.  The source for
_add_raster_constraint_extent() confirms that.

http://trac.osgeo.org/postgis/browser/tags/2.0.3/raster/rt_pg/rtpostgis.sql.in.c

Can you confirm that you're using EnterpriseDB's instance of
PostgreSQL and PostGIS?  I'm wondering if their instances are shipping
with out-of-date files...

You can always try the latest windows binaries available at:

http://postgis.net/windows_downloads

-bborie

On Mon, Apr 22, 2013 at 7:22 PM, ryagz <[hidden email]> wrote:

> I ran this query from PGAdmins Query tool and the History found in that tool
> is like this:
>
> SELECT _add_raster_constraint_extent('tkp_raster_satellite', 'wb32_64_1',
> 'rast');
> NOTICE:  Unable to get the extent of a sample raster
> Total query runtime: 1119183 ms.
> 1 row retrieved.
>
>
>
>
> --
> View this message in context: http://postgis.17.x6.nabble.com/AddRasterConstraints-failing-to-create-extents-in-raster-column-view-tp5003029p5003086.html
> Sent from the PostGIS - Dev mailing list archive at Nabble.com.
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

ryagz
I ran the query and the result is :

"PostgreSQL 9.1.6, compiled by Visual C++ build 1500, 32-bit POSTGIS="2.0.3 r11128" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" (core procs from "2.0.1 r9979" need upgrade) RASTER (raster procs from "2.0.1 r9979" need upgrade)"

In the history there is this message:

-- Executing query:
SELECT version() || ' ' || postgis_full_version();
NOTICE:  Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?

Total query runtime: 12 ms.
1 row retrieved.

I am not sure if my postgreSql is enterprise DB i downloaded the setup from the website long time back. Can you let me know how to find if its enterprise DB or not. So that i will check and let you know.

Reply | Threaded
Open this post in threaded view
|

Re: AddRasterConstraints failing to create extents in raster_column view

Bborie Park-2
I'm sure you're running EnterpriseDB's build of PostgreSQL due to it
being build on VC++.  Based upon the output, it looks like there is a
version mismatch between the underlying PostGIS library and the SQL
functions in your database.

(core procs
from "2.0.1 r9979" need upgrade) RASTER (raster procs from "2.0.1 r9979"
need upgrade)"

Run postgis_upgrade_20_minor.sql and rtpostgis_upgrade_20_minor.sql on
your database...

-bborie

On Mon, Apr 22, 2013 at 10:52 PM, ryagz <[hidden email]> wrote:

> I ran the query and the result is :
>
> "PostgreSQL 9.1.6, compiled by Visual C++ build 1500, 32-bit POSTGIS="2.0.3
> r11128" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL
> 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" (core procs
> from "2.0.1 r9979" need upgrade) RASTER (raster procs from "2.0.1 r9979"
> need upgrade)"
>
> In the history there is this message:
>
> -- Executing query:
> SELECT version() || ' ' || postgis_full_version();
> NOTICE:  Function postgis_topology_scripts_installed() not found. Is
> topology support enabled and topology.sql installed?
>
> Total query runtime: 12 ms.
> 1 row retrieved.
>
> I am not sure if my postgreSql is enterprise DB i downloaded the setup from
> the website long time back. Can you let me know how to find if its
> enterprise DB or not. So that i will check and let you know.
>
>
>
>
>
> --
> View this message in context: http://postgis.17.x6.nabble.com/AddRasterConstraints-failing-to-create-extents-in-raster-column-view-tp5003029p5003090.html
> Sent from the PostGIS - Dev mailing list archive at Nabble.com.
> _______________________________________________
> postgis-devel mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel