Add layer name to message when SQL error occurs

[dwoznicki]

This is more a wishlist request than a real issue, but when a tile fails to load due to an SQL error, etc. in one of the layers, it would be nice if the provider layer causing the error was printed out along with the error message.

For reference, I wrote this syntactically valid, but otherwise terrible SQL query for one of my provider layers.

SELECT ST_AsMVTGeom(geometry, !BBOX!) AS geometry, osm_id, name WHERE geometry && !BBOX!

Forgot to include the table name :face_in_clouds:

When I try to request a tile that loads data from this layer, of course I get an error.

2022-07-18 11:10:13 [ERROR] handle_map_layer_zxy.go:175: error marshalling tile: ERROR: column "geometry" does not exist (SQLSTATE 42703)

In this case, it would have been helpful if the error message was something like error marshalling tile for layer "my_layer_name": ERROR: column "geometry" does not exist (SQLSTATE 42703).

[ARolek]

@dwoznicki I agree that would be nice, but I'm not sure this is possible when using ST_AsMVT. The error you're seeing there is generated by the database. What version of PostGIS are you using? This request might need to be fulfilled by that team.

[dwoznicki]

Right, I forgot about how the ST_AsMVTGeom query is formed. Hmm, it's possible this is something PostGIS could provide a better error message, but I'm not sure how they'd get access to the provider layer name.

Regardless, I think this can probably be accomplished in plpgsql using functions. Here's a proof of concept.

-- The exec_geoms_query function takes in the SQL to execute as text and the name of the provider layer,
-- and returns the result of the SQL query, or throws a custom exception if something goes wrong.
CREATE OR REPLACE FUNCTION exec_geoms_query(sql text, layer text)
RETURNS SETOF geometry                    
LANGUAGE plpgsql                          
AS
$$
DECLARE
    exception_message text;
BEGIN
    BEGIN
        RETURN QUERY EXECUTE sql;
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS exception_message = MESSAGE_TEXT;
        RAISE EXCEPTION 'caught exception in layer "%": %', layer, exception_message;
    END;
END;
$$;

I can now use this function as the input to ST_AsMVTGeom. For testing purposes, I just tried selecting from a nonexistent table.

SELECT ST_AsMVTGeom(exec_geoms_query('SELECT geometry FROM nonexistent_table LIMIT 1', 'my_layer'), ST_MakeEnvelope(-2.0663680475625e+07,-2.0663680475625e+07,2.0663680475625e+07,2.0663680475625e+07,3857));

When I execute this from a file with psql, I get the following error.

psql:test.sql:1: ERROR:  caught exception in layer "my_layer": relation "nonexistent_table" does not exist
CONTEXT:  PL/pgSQL function exec_geoms_query(text,text) line 9 at RAISE

Here are my thoughts on this implementation:

• It makes debugging SQL errors a lot easier.
• Executing strings is dangerous, though this should be okay if the end user has no control over the SQL tokens, or if the tokens are sanitized.
• Even without the provider layer name in the error message, I've found that I can still debug these SQL errors. It's just more time consuming.
• This adds a dependency on plpgsql.
• The current implementation is not backwards compatible with PostgreSQL versions < 9.2 because of GET STACKED DIAGNOSTICS.

[ARolek]

@dwoznicki sorry for the slow response. I really like this idea! Generally speaking I like using database functions over forming up complex SQL inside of tegola. I have found it's much easier to maintain. Typically I will create a function like

layer_water(!BBOX!, !ZOOM!)

And that will return a TABLE. I do this for every layer, and often have materialized views I'm leveraging based on the !ZOOM! token. Using this approach you could actually call exec_geoms_query() inside the "layer functions" and keep the tegola config very minimal.

I think your list of "thoughts on this implement" is very accurate. I honestly would consider this best practice in a lot of ways, particularly because you can push all string sanitization, prepared statements formed in plpgsql function.

Thanks for following up with this suggestion / solution.

[dwoznicki]

I'm going to close this since, as you've pointed out, I can actually write custom functions (one per layer) to improve my own debug messages.