Look into different ways to substitute a bbox and zoom token

[pnorman]

Tokens are well-established, with Mapnik making use of them too. All the same, they're still a hack that relies on parsing SQL as text and writing SQL carefully. SQL injection isn't a security worry here, since it's intentional the style author can write arbitrary SQL, but the same flaws with manipulating SQL as text lead to bugs when writing queries.

My inclination is towards prepared statements

The server would execute something like

PREPARE water (geometry(polygon, 3857), int) AS
  SELECT geom FROM foo WHERE geom && $1
;

This would be generated by taking the middle line from the style and surrounding it with the PREPARE and ;. water would be something derived from the layer and provider names and be unique.

A prepared statement could be made with pgx's func (*Conn) PrepareEx but requires knowing the oid of the geometrytype and might not allow specifying typmods.

func (*Conn) Exec can take a prepared statement name, e.g.

p.pool.Exec("water", bboxForTile, zoom)

Issues with prepared statements might be

1. Cached query plans. These are normally good, but a query used over the range of zooms where it goes between sequential and index scans would have problems. It might be necessary to prepare a query for each zoom.

2. Interactions with pgbouncer and pgpool. These can work with prepared statements, but something like PostGIS data type OIDs might change across them, leading to problems with PrepareEx.

[ARolek]

This is related to #275

[pnorman]

I did some more thinking and investigations. I started from a few assumptions

1. Users need to be able to input named parameters, not positional ones which will be hard to remember.
2. We don't have to maintain full backwards compatibility if we're dropping ST_AsBinary anyways
3. Syntax errors and missing tables need to be found on startup or config loading, not the first time a particular zoom is requested

Some background information

1. pg itself only supports positional parameters, but some client interfaces allow :foo parameters. pgx isn't one of them.
2. With nearly all style queries, using NULL for a bounding box will return zero results, and in geom && NULL is false, so exits quickly. It's also semantically correct, as the bounding box is either unknown or does not exist.

I recommend the following:

On startup do the following per layer

sql := strings.Replace(l.sql, ":bbox", "$1", -1)
sql := strings.Replace(sql, ":zoom", "$2", -1)
// etc for any other tokens in the future

prepare := `PREPARE "tegola_` + strings.Replace(l.name, `"`, `\"`, -1) + `(geometry(polygon, ` + l.srid + `), int) AS` 
sql := prepare + "\n" + sql
// SQL instead of PrepareEx to allow for typmods
p.pool.Exec(sql);

Then for type sniffing, if required

// Not sure how to handle nulls in Go - pgtype is probably what's needed.
rows, err := p.pool.Query("tegola_" + strings.Replace(l.name, `"`, `\"`, -1), NullGeom, NullZoom)
if err != nil {
    return err
}

Then do magic with FieldDescription

Later, when running the query, run p.pool.Query("tegola_" + strings.Replace(l.name, ", ", -1), RealBbox, RealZoom)

The following seem relevant for PostGIS types: https://github.com/cridenour/go-postgis https://godoc.org/github.com/mc2soft/pq-types

But I figure others know Go + PostGIS better than I do, so I didn't dig extensively ;)