Description
- asyncpg version 0.15.0
- PostgreSQL version 9.6.3 (prod), 10.1 (dev)
- Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install? Yes, AWS Aurora (PostgreSQL). I can also reproduce it with my local PostgreSQL Docker container.
- Python version 3.6.5.
- Platform Docker container based on latest
python:3.6running on AWS ECS (prod); local venv install on macOS (dev). - Do you use pgbouncer? No.
- Did you install asyncpg with pip? Yes.
- If you built asyncpg locally, which version of Cython did you use? N/A
- Can the issue be reproduced under both asyncio and
uvloop? We use uvloop, definitely happening there. Haven't tried vanilla asyncio.
What's happening
We are experiencing intermittent RuntimeErrors with the message no decoder for OID 3910. This appears to be similar to #122, #133, and #241.
Per Postgres' pg_type.h, 3910 is the OID for the type tstzrange.
It's not happening constantly – only a couple of times in the last 24 hours – but it is happening. I've provided a bunch of detail below but please let me know if I can add anything else to help track this down.
Environment details
The schema we are using is as follows:
CREATE TYPE region_type AS ENUM (
'city',
'college',
'event',
'high school',
'neighborhood',
'test',
'university'
);
CREATE TABLE regions (
id text PRIMARY KEY,
bounds geography(Polygon) NOT NULL,
type region_type NOT NULL,
detailed_name text NOT NULL,
display_name text,
short_name text,
active tstzrange,
version integer NOT NULL DEFAULT 0,
modified timestamp with time zone NOT NULL DEFAULT now()
);
CREATE INDEX regions_active_idx ON regions USING GIST (active);
CREATE INDEX regions_bounds_idx ON regions USING GIST (bounds);And the query that's raising this error every once in a while:
SELECT *, ST_Distance(bounds, $1::geography) AS edge_distance
FROM regions
WHERE (active IS NULL OR now() <@ active) AND ST_DWithin(bounds, $1::geography, $2)
ORDER BY edge_distance;In this query, $1 is a point representing the user's reported location and $2 is a "fudge factor" in meters.
Usage notes
All of our application's interactions with Postgres are wrapped in transactions using this async context manager:
class PostgresTransactionManager:
def __init__(self, *, write: bool = False, **kwargs) -> None:
self.write = write
self.transaction_kwargs = kwargs
async def __aenter__(self) -> asyncpg.Connection:
# Get a shared singleton asyncpg.pool.Pool; write kwarg controls the pool's host (master or read replica)
pool = await Postgres.get_pool(write=self.write)
self.connection_manager = pool.acquire()
connection = await self.connection_manager.__aenter__()
self.transaction = connection.transaction(**self.transaction_kwargs)
await self.transaction.__aenter__()
return connection
async def __aexit__(self, exc_type, exc, tb):
await self.transaction.__aexit__(exc_type, exc, tb)
await self.connection_manager.__aexit__(exc_type, exc, tb)In practice it looks like this:
async with PostgresTransactionManager() as pg:
records = await pg.fetch(query, *args)The method below, which calls set_type_codec a few times, is passed as the init kwarg to asyncpg.create_pool(). This is called lazily by our Postgres class, the first time .get_pool() is called. Subsequent calls to Postgres.get_pool() return this singleton pool.
async def configure_connection(connection):
await connection.set_type_codec(
'geography',
schema='public', # PostGIS types are installed into public
encoder=encode_geometry,
decoder=decode_geometry,
format='binary',
)
await connection.set_type_codec(
'geometry',
schema='public',
encoder=encode_geometry,
decoder=decode_geometry,
format='binary',
)
await connection.set_type_codec(
'json',
schema='pg_catalog', # Built-in types live in pg_catalog
encoder=json.dumps,
decoder=json.loads,
)
await connection.set_type_codec(
'jsonb',
schema='pg_catalog',
encoder=json.dumps,
decoder=json.loads,
)