Tifeatures exits with asyncpg.exceptions.UndefinedTableError

[rodrigoalmeida94]

When connecting to an empty database (i.e. no tables) tifeatures returns the following exception:

2022-08-04 14:15:44.133 CEST
using new connection
2022-08-04 14:15:44.500 CEST
ERROR: Traceback (most recent call last):
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 635, in lifespan
2022-08-04 14:15:44.500 CEST
 async with self.lifespan_context(app):
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 530, in __aenter__
2022-08-04 14:15:44.500 CEST
 await self._router.startup()
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 612, in startup
2022-08-04 14:15:44.500 CEST
 await handler()
2022-08-04 14:15:44.500 CEST
 File "/app/./cerulean_cloud/cloud_run_tifeatures/handler.py", line 108, in startup_event
2022-08-04 14:15:44.500 CEST
 await register_table_catalog(app)
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/tifeatures/db.py", line 51, in register_table_catalog
2022-08-04 14:15:44.500 CEST
 app.state.table_catalog = await get_table_index(app.state.pool)
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/tifeatures/dbmodel.py", line 246, in get_table_index
2022-08-04 14:15:44.500 CEST
 rows = await conn.fetch_b(
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/buildpg/asyncpg.py", line 64, in fetch_b
2022-08-04 14:15:44.500 CEST
 return await self.fetch(query, *args, timeout=_timeout)
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 621, in fetch
2022-08-04 14:15:44.500 CEST
 return await self._execute(
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1659, in _execute
2022-08-04 14:15:44.500 CEST
 result, _ = await self.__execute(
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1684, in __execute
2022-08-04 14:15:44.500 CEST
 return await self._do_execute(
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1711, in _do_execute

2022-08-04 14:15:44.500 CEST
 stmt = await self._get_statement(
2022-08-04 14:15:44.500 CEST
 File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 398, in _get_statement
2022-08-04 14:15:44.500 CEST
 statement = await self._protocol.prepare(
2022-08-04 14:15:44.500 CEST
 File "asyncpg/protocol/protocol.pyx", line 168, in prepare
2022-08-04 14:15:44.500 CEST
asyncpg.exceptions.UndefinedTableError: relation "geometry_columns" does not exist
2022-08-04 14:15:44.500 CEST
2022-08-04 14:15:44.500 CEST
ERROR: Application startup failed. Exiting.
2022-08-04 14:15:44.715 CEST
Container called exit(3).

In a lot of deployment cases, the DB is deployed and is empty (and could be that the tifeatures deployment occurs at the same time) so I would think it makes sense to fail silently in these cases.

[vincentsarago]

To be honest I'm not sure why users would like to have tifeatures up without any tables in the database. At least we should return a better error message (or allow empty db) 🤷

For now you can do this:

• ignore get_table_index error
• register empty table_catalog
• add an endpoint for the user to refresh the catalog (🤮)

"""tifeatures app."""

from typing import Any, List

import jinja2

from tifeatures import __version__ as tifeatures_version
from tifeatures.db import close_db_connection, connect_to_db, register_table_catalog
from tifeatures.errors import DEFAULT_STATUS_CODES, add_exception_handlers
from tifeatures.factory import Endpoints
from tifeatures.layer import FunctionRegistry
from tifeatures.middleware import CacheControlMiddleware
from tifeatures.settings import APISettings

from fastapi import FastAPI

from starlette.middleware.cors import CORSMiddleware
from starlette.templating import Jinja2Templates
from starlette_cramjam.middleware import CompressionMiddleware
from starlette.requests import Request

settings = APISettings()

app = FastAPI(
    title=settings.name,
    version=tifeatures_version,
    openapi_url="/api",
    docs_url="/api.html",
)

# custom template directory
templates_location: List[Any] = (
    [jinja2.FileSystemLoader(settings.template_directory)]
    if settings.template_directory
    else []
)
# default template directory
templates_location.append(jinja2.PackageLoader(__package__, "templates"))

templates = Jinja2Templates(
    directory="",  # we need to set a dummy directory variable, see https://github.com/encode/starlette/issues/1214
    loader=jinja2.ChoiceLoader(templates_location),
)

# Register endpoints.
endpoints = Endpoints(title=settings.name, templates=templates)
app.include_router(endpoints.router)

# We add the function registry to the application state
app.state.tifeatures_function_catalog = FunctionRegistry()

# Set all CORS enabled origins
if settings.cors_origins:
    app.add_middleware(
        CORSMiddleware,
        allow_origins=settings.cors_origins,
        allow_credentials=True,
        allow_methods=["GET"],
        allow_headers=["*"],
    )

app.add_middleware(CacheControlMiddleware, cachecontrol=settings.cachecontrol)
app.add_middleware(CompressionMiddleware)
add_exception_handlers(app, DEFAULT_STATUS_CODES)


@app.on_event("startup")
async def startup_event() -> None:
    """Connect to database on startup."""
    await connect_to_db(app)
    try:
        await register_table_catalog(app)
    except:
        app.state.table_catalog = {}


@app.get("/register", include_in_schema=False)
async def register_table(request: Request):
    await register_table_catalog(request.app)


@app.on_event("shutdown")
async def shutdown_event() -> None:
    """Close database connection."""
    await close_db_connection(app)


@app.get("/healthz", description="Health Check", tags=["Health Check"])
def ping():
    """Health check."""
    return {"ping": "pong!"}

[vincentsarago]

@rodrigoalmeida94 I'm not sure about the statement

When connecting to an empty database (i.e. no tables)

I'm not able to recreate the issue when pointing the application to empty schema (with no table).

🤷

[rodrigoalmeida94]

Interesting! That was my original assumption, which is wrong. @vincentsarago could it be it's about a database that doesn't have postgis installed (not that it is empty?)