raster2pgsql - large collection of examples of different bugs in -l (overviews)

[gbell]

The big picture here is that the behaviour of the current stable version of raster2pgsql is unpredictable and highly prone to error (both in raster2pgsql and in the resulting SQL) when overview layers are specified.

Since the raster2pgsql overview factor is specified as the log_2() of the scale rather than as the scale, it is very likely that users will encounter these bugs.

The general theme of these errors are:

a) when an overview is created at too high a level, rather than generate a small picture, or refuse to generate the overview, raster2pgsql either segfaults in various ways, or its emitted SQL attempts to generate the overview and fails in a variety of ways, which share a theme of being untidy crashes.

b) When someone tries to add a raster as a table that already exists, unhelpful and messy errors are caused by the generated SQL rather than a clean failure explaining the problem.

raster2pgsql

RELEASE: 2.0.3 GDAL_VERSION=110 (r11128)

Using some examples taken from the recommended samples at ​http://trac.osgeo.org/geotiff/:

​ftp://ftp.remotesensing.org/pub/geotiff/samples/made_up/bogota.tif

​ftp://ftp.remotesensing.org/pub/geotiff/samples/made_up/ilatlon_float.tif

0a. Base case: both import correctly without overviews, with or without -Y.

raster2pgsql -t 100x100 -I -C -M bogota.tif temp.bogota | psql

OK.

(drop table)

raster2pgsql -t 100x100 -I -C -M -Y bogota.tif temp.bogota

OK.

(drop table)

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif temp.bogota | psql

OK.

(drop table)

raster2pgsql -t 100x100 -I -C -M -Y ilatlon_float.tif temp.bogota | psql

OK.

(drop table)

0c. Trying to add without dropping the table. Messy failure.

raster2pgsql -t 100x100 -I -C -M -Y ilatlon_float.tif temp.bogota | psql

OK.

raster2pgsql -t 100x100 -I -C -M -Y ilatlon_float.tif temp.bogota | psql

…

ERROR: current transaction is aborted, commands ignored until end of transaction block

invalid command \.

ERROR: syntax error at or near "0100000100622255302"

LINE 1: 0100000100622255302AA9333F622255302AA933BFA311BC0512A85EC000…

ERROR: current transaction is aborted, commands ignored until end of transaction block …

1. Import with overview 2 and "-Y"

raster2pgsql -t 100x100 -I -C -M bogota.tif -l 2 -Y temp.bogota | psql

OUTPUT

Processing 1/1: bogota.tif … CREATE TABLE ERROR: rt_band_from_wkb: Premature end of WKB on band data reading (rt_api.c:6611) CONTEXT: COPY o_2_bogota, line 9, column rast: "01000001000000000000005E400000000000005EC000000000405D1C4100000000008EF24000000000000000000000000000…"

2. Import with overview 2 and without "-Y"

raster2pgsql -t 100x100 -I -C -M bogota.tif -l 2 temp.bogota | psql

OUTPUT

… LINE 1: INSERT INTO "temp"."o_2_bogota" ("rast") VALUES ('0100000100…

(no tables added)

3. Import with overview 16 and with "-Y"

raster2pgsql -t 100x100 -I -C -M bogota.tif -l 16 -Y temp.bogota | psql

OUTPUT

….

ERROR: unterminated quoted string at or near "'" LINE 1: SELECT AddOverviewConstraints('temp','o_16_bogota','

4. Import with overviews 2 and 16 and with -Y

raster2pgsql -t 100x100 -I -C -M bogota.tif -l 2,16 -Y temp.bogota | psql

OUTPUT

… CREATE TABLE ERROR: rt_raster_from_hexwkb: Raster HEXWKB input must have an even number of characters CONTEXT: COPY o_16_bogota, line 1, column rast: "01000001000000000000008E400000000000008EC00000000040E61A4100000000006AF84000000000000000000000000000…"

5. Import with overviews 2,16,32 and with or without -Y

raster2pgsql -t 100x100 -I -C -M bogota.tif -Y -l 2,16,1000 temp.bogota | psql

raster2pgsql -t 100x100 -I -C -M bogota.tif -l 2,16,1000 temp.bogota | psql

OUTPUT:

… CREATE TABLE ERROR: rt_band_from_wkb: Premature end of WKB on band data reading (rt_api.c:6611) CONTEXT: COPY o_32_bogota, line 1, column rast: "01000001000000000000009E400000000000009EC00000000040E61A4100000000006AF84000000000000000000000000000…"

6. Import with overviews 2,16 and without -Y

raster2pgsql -t 100x100 -I -C -M bogota.tif -Y -l 2,16 temp.bogota

OUTPUT:

… ERROR: rt_raster_from_hexwkb: Raster HEXWKB input must have an even number of characters CONTEXT: COPY o_16_bogota, line 1, column rast: "01000001000000000000008E400000000000008EC00000000040E61A4100000000006AF84000000000000000000000000000…"

7. Add a tiff, then try again. Bad things happen.

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 4 temp.bogota | psql sl

(works)

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 4 temp.bogota | psql sl

(fails horribly)

ERROR: relation "bogota" already exists ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block invalid command \.

ERROR: syntax error at or near "0100000100622255302" LINE 1: 0100000100622255302AA9333F622255302AA933BFA311BC0512A85EC000…

* glibc detected * raster2pgsql: corrupted double-linked list: 0x0000000001e209a0 *

======= Backtrace: ========= /lib64/libc.so.6[0x3d15a7c1a2] /lib64/libc.so.6[0x3d15a7c988] /usr/local/lib/libgdal.so.1(+0x5837c3)[0x7fc5cdba57c3] /usr/local/lib/libgdal.so.1(+0x58383f)[0x7fc5cdba583f] /usr/local/lib/libgdal.so.1(+0x3485fb)[0x7fc5cd96a5fb] /usr/local/lib/libgdal.so.1(_ZN10GDALDriverD1Ev+0x2a)[0x7fc5cdb7264a] /usr/local/lib/libgdal.so.1(_ZN10GDALDriverD0Ev+0x9)[0x7fc5cdb72689] /usr/local/lib/libgdal.so.1(_ZN17GDALDriverManagerD1Ev+0xd2)[0x7fc5cdb74b62] /usr/local/lib/libgdal.so.1(_ZN17GDALDriverManagerD0Ev+0x9)[0x7fc5cdb74c89] /usr/local/lib/libgdal.so.1(+0x2a28e2)[0x7fc5cd8c48e2] /lib64/ld-linux-x86-64.so.2[0x3d1520f5f7] …

8. Variation in overview scale. A works, B doesn't. (overview 2 vs. overview 100)

A

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 1000 temp.bogota | psql sl

B

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 1000 temp.bogota | psql sl

OUTPUT:

A

… COMMIT VACUUM VACUUM

B

… ERROR: rt_raster_from_hexwkb: Raster HEXWKB input must have an even number of characters CONTEXT: COPY o_100_bogota, line 1, column rast: "0100000100B90585EB51B89E3FB90585EB51B89EBFA311BC0512A85EC0000000000000434000000000000000000000000000…"

9. A closer look at the STDOUT output of:

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 100 temp.bogota

OUTPUT:

… VACUUM ANALYZE "temp"."bogota"; VACUUM ANALYZE "temp"."o_100_bogota"; Segmentation fault

10. Various errors depending on how many times the same overview is requested:

(asking for -2 once)

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 2 temp.bogota

* glibc detected * raster2pgsql: corrupted double-linked list: 0x00000000025719a0 * ======= Backtrace: ========= /lib64/libc.so.6[0x3d15a7c1a2] /lib64/libc.so.6[0x3d15a7c988] /usr/local/lib/libgdal.so.1(+0x5837c3)[0x7fdfbc89b7c3] /usr/local/lib/libgdal.so.1(+0x58383f)[0x7fdfbc89b83f] /usr/local/lib/libgdal.so.1(+0x3485fb)[0x7fdfbc6605fb] /usr/local/lib/libgdal.so.1(_ZN10GDALDriverD1Ev+0x2a)[0x7fdfbc86864a] /usr/local/lib/libgdal.so.1(_ZN10GDALDriverD0Ev+0x9)[0x7fdfbc868689] /usr/local/lib/libgdal.so.1(_ZN17GDALDriverManagerD1Ev+0xd2)[0x7fdfbc86ab62] /usr/local/lib/libgdal.so.1(_ZN17GDALDriverManagerD0Ev+0x9)[0x7fdfbc86ac89] /usr/local/lib/libgdal.so.1(+0x2a28e2)[0x7fdfbc5ba8e2] /lib64/ld-linux-x86-64.so.2[0x3d1520f5f7]

11. add another ,2

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 2,2 temp.bogota

OUTPUT:

… SELECT AddRasterConstraints('temp','bogota','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE); SELECT AddRasterConstraints('temp','o_2_bogota','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE); SELECT AddRasterConstraints('temp','o_2_bogota','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE); Segmentation fault

12. and without -y

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -l 2,2 temp.bogota

OUTPUT:

000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'::raster); Segmentation fault

13. Trying to add a non-integer overview level. This is not detected.

OUTPUT:

raster2pgsql -t 100x100 -I -C -M -Y ilatlon_float.tif -l 2.5 temp.bogota

SELECT AddOverviewConstraints('temp','o_2_bogota','rast','temp','bogota','rast',2); END; VACUUM ANALYZE "temp"."bogota"; VACUUM ANALYZE "temp"."o_2_bogota"; Segmentation fault

14. Forgetting to specify the overview factor produces a misleading error message

raster2pgsql -t 100x100 -I -C -M -Y ilatlon_float.tif -l temp.bogota

OUTPUT:

ERROR: Overview factor 0 is not between 2 and 1000

(should be: Overview factor not specified)

15. Large numbers uncover another bug. Maybe a BASH bug.

raster2pgsql -t 100x100 -I -C -M -Y ilatlon_float.tif -l 100000000000 temp.bogota

OUTPUT:

ERROR: Overview factor 1215752192 is not between 2 and 1000

16. Specifying -l multiple times causes segfault (without and without duplication of overview factor)

raster2pgsql -t 100x100 -I -C -M -Y ilatlon_float.tif -l 2 -l 4 temp.bogota

raster2pgsql -t 100x100 -I -C -M -Y ilatlon_float.tif -l 2 -l 2 temp.bogota

OUTPUT:

VACUUM ANALYZE "temp"."bogota"; VACUUM ANALYZE "temp"."o_2_bogota"; Segmentation fault

[gbell]

Also, an outline of a simple fix that might address many of these cases.

1. Calculate when this is occurring most often.

(At a guess, perhaps when the overview will have a size that is smaller than one tile, or some other threshold value).

2. Detect if the raster size, when rescaled to the overview size, will be less than the bug threshhold.

3. Report to the user that the requested overview will be too small for the raster and exit cleanly.

[dustymugs]

Tested on the following:

 PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.7.1, 64-b
it | POSTGIS="2.0.4SVN r11536" GEOS="3.4.0dev-CAPI-1.8.0 r3808" PROJ="Rel. 4.8.0
, 6 March 2012" GDAL="GDAL 1.11dev, released 2013/04/13" LIBXML="2.8.0" LIBJSON=
"UNKNOWN" RASTER

Item 0c: runs as expected. I get the appropriate error message from the database indicating that the table already exists. raster2pgsql never connects (and can't connect) to the database so it has no idea if the table already exists.

root@slack64:/home/software/geo/raster# raster2pgsql -t 100x100 -I -C -M -Y bogota.tif bogota | psql -d test
Processing 1/1: bogota.tif
BEGIN
NOTICE:  CREATE TABLE will create implicit sequence "bogota_rid_seq1" for serial column "bogota.rid"
ERROR:  relation "bogota" already exists
ERROR:  current transaction is aborted, commands ignored until end of transaction block
invalid command \.
ERROR:  syntax error at or near "01000001000000000000004E400000000000004"
LINE 1: 01000001000000000000004E400000000000004EC00000000040E61A4100...
        ^
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
VACUUM

Item 1: Bug confirmed in schema name of overview. Fixed in r11543 for 2.0 and r11544 for -trunk

Item 2: Same bug as Item 1

Item 3: Same bug as Item 1

Item 4: Same bug as Item 1

Item 5: Same bug as Item 1

Item 6: Same bug as Item 1

Item 7: I don't get any glibc issues, just the expected

ERROR:  relation "bogota" already exists

Item 8: description does not match code so assuming scale of 2 for first call to raster2pgsql. If table isn't dropped between the two calls, expected error of ration existing returned. If table is dropped between calls, no problems as same bug as Item 1.

Item 9: Same bug as Item 1

Items 10 - 12: Same bug as Item 1

Item 13: As expected as all scale factors are treated as integers

Item 14: Not misleading as the value after -l is considered the scale value. So, error is correct.

Item 15: 100 billion can't be stored in an int but the error message is correct.

Item 16: Same bug as Item 1

[gbell]

Hi,

Thank you for investigating so thoroughly, great work! Interesting that so many different variations of error output originate in a single bug.

Item 7. I have retested. This bug is real and distinct. Can we leave this bug report open until someone else can attempt to reproduce it?

raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 4 temp.test | psql sl raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 4 temp.test | psql sl

This produces the error for me. It isn't produced when -l4 is missing; it isn't produced with bogota.tif (it can't generate overviews). The stack trace is huge.

glibc is glibc-2.16-28.fc18.x86_64

Perhaps the bug is being exposed (or being caused) by the glibc version.

Item 0c.

"raster2pgsql never connects (and can't connect) to the database so it has no idea if the table already exists."

This statement is true, but I think it is not important to solving this problem.

Is there anything to prevent raster2pgsql from emitting SQL that checks if the named table already exists when it is run, and aborts cleanly if it does exist, with an appropriate message to the user / SQL console?

I would recommend that as a future improvement. I'm reopening for this in particular.

Again, thank you for your time in investigating.

[dustymugs]

What are you running? I notice that there is no information regarding the linux distro or Postgresql version. Are you using packages or compiling your own stack?

As for item 0c, that isn't a problem that raster2pgsql should solve. raster2pgsql only does one job, generate SQL from a set of rasters to load into a PostGIS-enabled database based upon a set of criteria provided by the user. The SQL generated by raster2pgsql IS valid. The problem is that the user is trying to create a table in a database with a table of the same name. If it was a different database, the SQL generated would run fine. I consider this user error. Besides, the database tells you that the table already exists and correctly rolls back any changes.

I'm fine with leaving this ticket open for the glibc issue. I can test once I recreate the basic environment that you have.

[gbell]

Hi dustymugs,

I think this is becoming a difference of opinion about what defines a bug.

In this case, raster2pgsql takes a role somewhat analogous to a software compiler, turning tiffs into SQL program output.

The programs that are produced by r2pg generate *minimally helpful* error output when things go wrong - along with ugly and distracting error output. I think it's great when open source projects aim a bit higher than 'minimally functional'.

"The SQL generated by raster2pgsql IS valid."

No, it is not. This is not the sort of thing someone should see when running valid SQL:

invalid command \. ERROR: syntax error at or near "01000001000000000000004E400000000000004" LINE 1: 01000001000000000000004E400000000000004EC00000000040E61A4100…

I agree that there is an aspect of user error here. Nonetheless, that doesn't excuse sloppy error output to the user or random SQL bugs. Can you imagine if gcc was as unforgiving of user error and generated cryptic and unrelated extra messages around every real compiler warning?

In this case, every PG Raster user must use this program to import their data, and many of those users will be unfamiliar with searching through junk output when things go wrong to find out what has happened.

Perhaps we should move the suggestion to a dev mailing list for quick discussion/vote?

Regarding my environment. It's a fedora 18 x86_64 base with manual builds of postgis, postgresql, gdal and geos.

sl=# select postgis_full_version();

postgis_full_version

---

---

---

POSTGIS="2.0.3 r11128" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GD

AL 1.10.0, released 2013/04/24" LIBXML="2.9.0" TOPOLOGY (topology procs from "2.0.2 r107 89" need upgrade) RASTER (1 row)

Please let me know if you would like versions for any of the fedora rpms.

[gbell]

0) Updated topology to 2.0.3. Retested. No impact on bug (glibc and other messages).

1) "Can you imagine if gcc was as unforgiving of user error and generated cryptic and unrelated extra messages around every real compiler warning?" - forgot to add the smiley

[dustymugs]

Actually, the SQL is correct. That is due to the use of a COPY statement. If you were to use INSERT statements (no -Y flag), the output looks like the following…

BEGIN
psql:bogota.sql:2: NOTICE:  CREATE TABLE will create implicit sequence "bogota_rid_seq1" for serial column "bogota.rid"
psql:bogota.sql:2: ERROR:  relation "bogota" already exists
psql:bogota.sql:3: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:bogota.sql:4: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:bogota.sql:5: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:bogota.sql:6: ERROR:  current transaction is aborted, commands ignored until end of transaction block
...

[dustymugs]

Feel free to move the discussion to postgis-users or -devel.

[gbell]

"If you were to use INSERT statements (no -Y flag), the output looks like the following…"

That may be true, but it is not relevant in any way, since I am not submitting a bug report about that other program mode.

I mean, if you ran menu item 1 in MS Word and it generated junk text, it would hardly console you to find out that menu item 2 runs correctly. The whole point of the bug report is to highlight bizarre output from an execution path through the code.

It's great to hear INSERT mode works correctly. But it would be even better if COPY mode also worked correctly, from a user perspective.

[dustymugs]

Please move this discussion to one of the mailing lists. As I've said before, I'm fine with leaving this ticket open to see if the glibc issue is repeatable elsewhere.

[dustymugs]

I've tested for the glibc issue on Fedora 18 updated with the latest packages. PostgreSQL 9.2.4, PROJ.4.8.0, GEOS -trunk r3823, GDAL -trunk r26081 and PostGIS 2.0.4dev r11543 compiled from source.

[root@localhost temp]# ../bin/raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 4 public.bogota | ../bin/psql -U postgres -d test
}}}}

Running the above repeatedly as per Item #7 results in no glibc stack trace.

Fedora info
{{{
[root@localhost temp]# cat /etc/fedora-release 
Fedora release 18 (Spherical Cow)
}}}

Installed glibc info
{{{
[root@localhost temp]# yum info glibc
Loaded plugins: langpacks, presto, refresh-packagekit
Installed Packages
Name        : glibc
Arch        : x86_64
Version     : 2.16
Release     : 31.fc18
Size        : 13 M
Repo        : installed
From repo   : updates
Summary     : The GNU libc libraries
URL         : http://www.gnu.org/software/glibc/
License     : LGPLv2+ and LGPLv2+ with exceptions and GPLv2+
Description : The glibc package contains standard libraries which are used by
            : multiple programs on the system. In order to save disk space and
            : memory, as well as to make upgrading easier, common system code is
            : kept in one place and shared between programs. This particular
            : package contains the most important sets of shared libraries: the
            : standard C library and the standard math library. Without these
            : two libraries, a Linux system will not function.
}}}

PostgreSQL and PostGIS info

{{{
[root@localhost temp]# ../bin/psql -U postgres -d test -c "SELECT version(), postgis_full_version()"
NOTICE:  Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
                                                   version                      
                              |                                                 
                  postgis_full_version                                          
                          
--------------------------------------------------------------------------------
------------------------------+-------------------------------------------------
--------------------------------------------------------------------------------
--------------------------
 PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.7.2 20121
109 (Red Hat 4.7.2-8), 64-bit | POSTGIS="2.0.4SVN r11543" GEOS="3.4.0dev-CAPI-1.
8.0 r3823" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11dev, released 2013/04/
13" LIBXML="2.9.1" RASTER
(1 row)
}}}

I'd suggest upgrading your packages and trying again. There may be issues with what config flags you're using when compiling the various programs.

[dustymugs]

Take two due to bad formatting…

I've tested for the glibc issue on Fedora 18 updated with the latest packages. PostgreSQL 9.2.4, PROJ.4.8.0, GEOS -trunk r3823, GDAL -trunk r26081 and PostGIS 2.0.4dev r11543 compiled from source.

[root@localhost temp]# ../bin/raster2pgsql -t 100x100 -I -C -M ilatlon_float.tif -Y -l 4 public.bogota | ../bin/psql -U postgres -d test

Running the above repeatedly as per Item #7 results in no glibc stack trace.

Fedora info

[root@localhost temp]# cat /etc/fedora-release 
Fedora release 18 (Spherical Cow)

Installed glibc info

[root@localhost temp]# yum info glibc
Loaded plugins: langpacks, presto, refresh-packagekit
Installed Packages
Name        : glibc
Arch        : x86_64
Version     : 2.16
Release     : 31.fc18
Size        : 13 M
Repo        : installed
From repo   : updates
Summary     : The GNU libc libraries
URL         : http://www.gnu.org/software/glibc/
License     : LGPLv2+ and LGPLv2+ with exceptions and GPLv2+
Description : The glibc package contains standard libraries which are used by
            : multiple programs on the system. In order to save disk space and
            : memory, as well as to make upgrading easier, common system code is
            : kept in one place and shared between programs. This particular
            : package contains the most important sets of shared libraries: the
            : standard C library and the standard math library. Without these
            : two libraries, a Linux system will not function.

PostgreSQL and PostGIS info

[root@localhost temp]# ../bin/psql -U postgres -d test -c "SELECT version(), postgis_full_version()"
NOTICE:  Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
                                                   version                      
                              |                                                 
                  postgis_full_version                                          
                          
--------------------------------------------------------------------------------
------------------------------+-------------------------------------------------
--------------------------------------------------------------------------------
--------------------------
 PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.7.2 20121
109 (Red Hat 4.7.2-8), 64-bit | POSTGIS="2.0.4SVN r11543" GEOS="3.4.0dev-CAPI-1.
8.0 r3823" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11dev, released 2013/04/
13" LIBXML="2.9.1" RASTER
(1 row)

I'd suggest upgrading your packages and trying again. There may be issues with what config flags you're using when compiling the various programs.

[gbell]

Good morning,

I've updated to the latest fc18 rpms using yum update.

I'm no longer seeing the glibc error. Instead I'm seeing the item 1 bug output (at the moment I'm not upgrading to svn to fix that).

So it looks like it's a bug originating in the older rpms for fedora. Good detective work - thanks!

Marking as fixed; will move the other discussion to dev list.