developmentseed / developmentseed/tipg

Unexpected tipg Service Failure Due to Infinity Value in JSON

Open
#204 0 comments 1 reaction 1 assignee Claimed by @bitner View on GitHub
Dominant language
PLpgSQL
Stars
219
Forks
39
Avg merge
1d 18h
Merged PRs (30d)
6

Description

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
```sql
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');
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.