BuildPG doesn't quote column names on SELECT statements

[blaketeres]

Issue

TiPg responds with a 500 error if any Postgresql keywords are used as column names. I need to support generic datasets without modifying the column names. I have no control over the column names.

I did some debugging, and this comes down to how buildpg generates its SELECT statements without adding double-quotes around column names. Seems like it would be an easy fix in buildpg, but that repo appears unmaintained.

Steps to reproduce

Build test table

CREATE SCHEMA IF NOT EXISTS test;
CREATE SEQUENCE IF NOT EXISTS test.my_table_ogc_fid_seq;
CREATE TABLE "test"."my_table" (
    "ogc_fid" int4 NOT NULL DEFAULT nextval('test.my_table_ogc_fid_seq'::regclass),
    "end" varchar,  -- this can be any postgresql keyword
    "wkb_geometry" geometry,
    PRIMARY KEY ("ogc_fid")
);

Set env vars

Set environment variables to point to your local database. Also set the following

export TIPG_DB_SCHEMAS='["test"]'

Run TiPg and make request

uvicorn tipg.main:app

$ curl --url '127.0.0.1:8000/collections/test.my_table/items' -v 
*   Trying 127.0.0.1:8000...
* Connected to 127.0.0.1 (127.0.0.1) port 8000
> GET /collections/data_catalog.org_6542_dataset_4819273041a44d3c92cb6015a6ca873e/items HTTP/1.1
> Host: 127.0.0.1:8000
> User-Agent: curl/8.7.1
> Accept: */*
> 
* Request completely sent off
< HTTP/1.1 500 Internal Server Error
< date: Thu, 31 Oct 2024 16:57:24 GMT
< server: uvicorn
< content-length: 44
< content-type: application/json
< 
* Connection #0 to host 127.0.0.1 left intact
{"detail":"syntax error at or near \"end\""}% 

By placing a breakpoint in the linked location, you can see the generated query in variable c here https://github.com/developmentseed/tipg/blob/main/tipg/collections.py#L647 does not have quotes around column names. I believe that double quoting column names will fix this problem.

Thanks in advance for looking at this!

[vincentsarago]

I wonder if this is related to samuelcolvin/buildpg#38 and developmentseed/tifeatures#60

[blaketeres]

Hello @vincentsarago is there any chance I can help provide a solution to this issue? Do we need to fork buildpg?

[vincentsarago]

sure @blaketeres I'm head down on project work and other open source projects so my time is pretty limited right now but if you know a path to resolve this please start a PR 🙏