Unexpected tipg Service Failure Due to Infinity Value in JSON

[ninyawee]

Today, I found my tipg service fails in an unexpected way. This is rare occurence. I don't expect a fix from this obscure bug.
Just to benefit someone with my workaround.

Error Msg

INFO:     Will watch for changes in these directories: ['/home/ben/Devs/Public/tipg/tipg']
INFO:     Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [64346] using StatReload
['gis']
INFO:     Started server process [64371]
INFO:     Waiting for application startup.
Executing query with parameters: 
schemas=['gis']
tables=None
exclude_tables=None
exclude_table_schemas=None
functions=None
exclude_functions=None
exclude_function_schemas=None
spatial=True
spatial_extent=True
datetime_extent=True
Error executing query: number is infinity when parsed as double: line 1 column 793 (char 792)
ERROR:    Traceback (most recent call last):
  File "/home/ben/Devs/Public/tipg/.venv/lib/python3.12/site-packages/starlette/routing.py", line 693, in lifespan
    async with self.lifespan_context(app) as maybe_state:
               ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/.local/share/mise/installs/python/3.12.7/lib/python3.12/contextlib.py", line 210, in __aenter__
    return await anext(self.gen)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/Devs/Public/tipg/.venv/lib/python3.12/site-packages/fastapi/routing.py", line 133, in merged_lifespan
    async with original_context(app) as maybe_original_state:
               ^^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/.local/share/mise/installs/python/3.12.7/lib/python3.12/contextlib.py", line 210, in __aenter__
    return await anext(self.gen)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/Devs/Public/tipg/tipg/main.py", line 45, in lifespan
    await register_collection_catalog(
  File "/home/ben/Devs/Public/tipg/tipg/collections_.py", line 1025, in register_collection_catalog
    app.state.collection_catalog = await get_collection_index(app.state.pool, **kwargs)
                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/Devs/Public/tipg/tipg/collections_.py", line 938, in get_collection_index
    rows = await conn.fetch_b(
           ^^^^^^^^^^^^^^^^^^^
  File "/home/ben/Devs/Public/tipg/.venv/lib/python3.12/site-packages/buildpg/asyncpg.py", line 64, in fetch_b
    return await self.fetch(query, *args, timeout=_timeout)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/Devs/Public/tipg/.venv/lib/python3.12/site-packages/asyncpg/connection.py", line 690, in fetch
    return await self._execute(
           ^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/Devs/Public/tipg/.venv/lib/python3.12/site-packages/asyncpg/connection.py", line 1864, in _execute
    result, _ = await self.__execute(
                ^^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/Devs/Public/tipg/.venv/lib/python3.12/site-packages/asyncpg/connection.py", line 1961, in __execute
    result, stmt = await self._do_execute(
                   ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ben/Devs/Public/tipg/.venv/lib/python3.12/site-packages/asyncpg/connection.py", line 2024, in _do_execute
    result = await executor(stmt, None)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 206, in bind_execute
  File "asyncpg/protocol/coreproto.pyx", line 84, in asyncpg.protocol.protocol.CoreProtocol._read_server_messages
  File "asyncpg/protocol/coreproto.pyx", line 239, in asyncpg.protocol.protocol.CoreProtocol._process__bind_execute
  File "asyncpg/protocol/coreproto.pyx", line 532, in asyncpg.protocol.protocol.CoreProtocol._parse_data_msgs
  File "asyncpg/protocol/protocol.pyx", line 840, in asyncpg.protocol.protocol.BaseProtocol._decode_row
  File "asyncpg/protocol/prepared_stmt.pyx", line 328, in asyncpg.protocol.protocol.PreparedStatementState._decode_row
  File "asyncpg/protocol/codecs/base.pyx", line 330, in asyncpg.protocol.protocol.Codec.decode
  File "asyncpg/protocol/codecs/base.pyx", line 327, in asyncpg.protocol.protocol.Codec.decode_in_python
orjson.JSONDecodeError: number is infinity when parsed as double: line 1 column 793 (char 792)

ERROR:    Application startup failed. Exiting.

Investigation

I opened my debugger and tried to dig down what is the root cause of this. since it prev. working on the same schema perfectly.

1. I tried to bisect and locate a table that might causing this, despite there is no add or change recently.
2. I duplicate whole schema and try to run new schema against tipg it turn out this is working
3. I renamed my new schema to the old one, this also still work.

What make this error should be inside the schema. but i have no idea what it is

workaround

CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text)
RETURNS void AS $$
DECLARE
    object text;
    buffer text;
    default_ text;
    column_ text;
BEGIN
    EXECUTE 'CREATE SCHEMA ' || dest_schema;

    -- Clone sequences
    FOR object IN
        SELECT sequence_name FROM information_schema.sequences
        WHERE sequence_schema = source_schema
    LOOP
        EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object);
    END LOOP;

    -- Clone tables, adjust sequence defaults, and copy data
    FOR object IN
        SELECT table_name FROM information_schema.tables
        WHERE table_schema = source_schema
    LOOP
        buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
        EXECUTE 'CREATE TABLE ' || buffer ||
            ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object) ||
            ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';

        -- Copy the data
        EXECUTE 'INSERT INTO ' || buffer ||
            ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object);

        -- Update sequence references
        FOR column_, default_ IN
            SELECT column_name, REPLACE(column_default, source_schema, dest_schema)
            FROM information_schema.columns
            WHERE table_schema = dest_schema
              AND table_name = object
              AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'
        LOOP
            EXECUTE 'ALTER TABLE ' || buffer ||
                ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- SELECT clone_schema('foo', 'bar');

CREATE OR REPLACE FUNCTION verify_schema_clone(source_schema text, dest_schema text)
RETURNS TABLE (
    table_name text,
    source_count bigint,
    dest_count bigint,
    row_count_match boolean,
    checksum_match boolean
) AS $$
DECLARE
    curr_table text;
    source_count bigint;
    dest_count bigint;
    source_checksum text;
    dest_checksum text;
BEGIN
    FOR curr_table IN
        SELECT t.table_name FROM information_schema.tables t
        WHERE t.table_schema = source_schema
        AND t.table_type = 'BASE TABLE'
    LOOP
        -- Get row counts
        EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(source_schema) || '.' || quote_ident(curr_table)
            INTO source_count;
        EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(dest_schema) || '.' || quote_ident(curr_table)
            INTO dest_count;

        -- Calculate checksums for all columns
        EXECUTE 'SELECT MD5(STRING_AGG(CAST(t.* AS TEXT), '''' ORDER BY (SELECT NULL)))
                FROM ' || quote_ident(source_schema) || '.' || quote_ident(curr_table) || ' t'
            INTO source_checksum;
        EXECUTE 'SELECT MD5(STRING_AGG(CAST(t.* AS TEXT), '''' ORDER BY (SELECT NULL)))
                FROM ' || quote_ident(dest_schema) || '.' || quote_ident(curr_table) || ' t'
            INTO dest_checksum;

        table_name := curr_table;
        row_count_match := (source_count = dest_count);
        checksum_match := (source_checksum = dest_checksum);

        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Example usage:
-- SELECT * FROM verify_schema_clone('foo', 'bar');