Seeding SQL error while all layers gives normal result in operation

[flacombe]

Dear maintainers,

I'm running Tegola 0.13.0 and it gives me a SQL syntax error when calling tegola cache seed --bounds="$BOUNDS" --min-zoom 2 --max-zoom 6 --overwrite --config /opt/tegola_config/config.toml

Error: error seeding tile ({Z:2 X:0 Y:1}): ERROR: syntax error at or near ")" (SQLSTATE 42601)

When running Tegola normally, I've got no error and all layers give normal results

Despite this message is unexpected, it's hard to find a valid solution as it doesn't give the layer name and the complete SQL query Tegola intends to run.

Do you have any additional hints that could explain why I got this error please?

Here is my complete config.toml file:

[[providers]]
name = "postgis"
type = "mvt_postgis"
host = "${DB_HOST}"
port = "${DB_PORT}"
database = "${DB_DATABASE}"
user = "${DB_USER}"
password = "${DB_PWD}"
srid = 3857
max_connections = 20
[[providers.layers]]
name = "pdm_project_poteaux"
sql = "SELECT gid, ST_AsMVTGeom(ST_SimplifyPreserveTopology(geom, !PIXEL_WIDTH! / 4), !BBOX!) AS geom, osm_id::text AS osm_id, tags::text AS tags FROM pdm_project_poteaux WHERE geom && !BBOX!"
geometry_type = "Point"
id_fieldname = "gid"

[[providers.layers]]
name = "pdm_stats_poteaux"
sql = "SELECT ST_AsMVTGeom(ST_SimplifyPreserveTopology(geom, !PIXEL_WIDTH! / 4), !BBOX!) AS geom, stats::text AS stats, project, boundary, name, admin_level, nb FROM pdm_boundary_tiles WHERE project='2021-01_poteaux' AND geom && !BBOX! AND ((admin_level=4 AND !ZOOM! <= 5) OR (admin_level=6 AND !ZOOM! BETWEEN 5 AND 8) OR (admin_level=8 AND !ZOOM! >= 8))"
geometry_type = "Point"

[[providers.layers]]
name = "pdm_project_substations"
sql = "SELECT gid, ST_AsMVTGeom(ST_SimplifyPreserveTopology(geom, !PIXEL_WIDTH! / 4), !BBOX!) AS geom, osm_id::text AS osm_id, tags::text AS tags FROM pdm_project_substations WHERE geom && !BBOX!"
geometry_type = "Point"
id_fieldname = "gid"

[[providers.layers]]
name = "pdm_stats_substations"
sql = "SELECT ST_AsMVTGeom(ST_SimplifyPreserveTopology(geom, !PIXEL_WIDTH! / 4), !BBOX!) AS geom, stats::text AS stats, project, boundary, name, admin_level, nb FROM pdm_boundary_tiles WHERE project='2021-01_substations' AND geom && !BBOX! AND ((admin_level=4 AND !ZOOM! <= 5) OR (admin_level=6 AND !ZOOM! BETWEEN 5 AND 8) OR (admin_level=8 AND !ZOOM! >= 8))"
geometry_type = "Point"


[[maps]]
name = "pdm_project_poteaux"
center = [ 0.0, 0.0, 2.0,]
[[maps.layers]]
min_zoom = 10
max_zoom = 15
provider_layer = "postgis.pdm_project_poteaux"


[[maps]]
name = "pdm_stats_poteaux"
center = [ 0.0, 0.0, 2.0,]
[[maps.layers]]
min_zoom = 2
max_zoom = 15
provider_layer = "postgis.pdm_stats_poteaux"

                            
[[maps]]
name = "pdm_project_substations"
center = [ 0.0, 0.0, 2.0,]
[[maps.layers]]
min_zoom = 7
max_zoom = 15
provider_layer = "postgis.pdm_project_substations"


[[maps]]
name = "pdm_stats_substations"
center = [ 0.0, 0.0, 2.0,]
[[maps.layers]]
min_zoom = 2
max_zoom = 15
provider_layer = "postgis.pdm_stats_substations"


[webserver]
port = ":8082"

[cache]
type = "file"
basepath = "/tmp/tegola"

Best regards

[ARolek]

@flacombe Have you found the debugging section in the docs yet? You can set some environment variables which will show you the SQL that's being sent to PostGIS. I think the EXECUTE_SQL value would be helpful for you. When you have a specific tile you want to debug like this, you can form up a curl request to tegola for that specific z/x/y, then watch the server logs for the SQL error. With the EXECUTE_SQL debug flag set, you will see the various SQL statements that are sent to PostGIS, which you can then capture and execute against PostGIS directly.

[flacombe]

Hi @ARolek and thank you for your answer.

I wasn't aware of TEGOLA_SQL_DEBUG=EXECUTE_SQL and it's great.

The SQL query responsible of error during seeding is SELECT () AS data.

As error message was: Error: error seeding tile ({Z:2 X:0 Y:1}): ERROR: syntax error at or near ")" (SQLSTATE 42601), I tried to curl pdm_project_poteaux/2/0/1.mvt and get following log:
2021-05-24 21:19:50 [INFO] handle_map_layer_zxy.go:97: map (pdm_project_poteaux) has no layers, at zoom 2

It's a valid output as this map hasn't any layer defined at zoom 2, seeding should adapt to maps defined in config.toml.

Currently my seed command is /opt/tegola cache seed --bounds="$BOUNDS" --min-zoom 2 --max-zoom 6 --overwrite --config /opt/tegola_config/config.toml. config.toml defines 4 maps, 2 with layers from 0 to 18 and 2 others with layers from 7 to 18.

[ARolek]

@flacombe thanks for the follow-up. It seems like the seed command is not detecting which zooms are present in the map prior to trying to seed. I will need to dig into this some more to figure out why. It seems the server is handling this correctly though. I will mark this as a bug.

[flowrean]

This looks like a duplicate of #751.