Extremely long delay grabbing type info for string array (and likely other types) on CockroachDB

Chưa có ai nhận issue này.

I was having atrocious and unacceptable delays in my production environment that I wasn't seeing locally, using CockroachDB cloud. Found the cause was the introspection of types. I'm using the latest version of the CockroachDB drivers.


I wrote this hack to work around the issue. It caches the result in local memory and also caches it to redis so that new instances don't see it. You can change the key for the redis cache using an environment variable so that new versions aren't locked to old values.

If someone wants to turn it into part of the product, please be my guest. I won't have time for it. In the mean time. here's the hack that does monkey patching:

```python
introspection_result_cache: dict[tuple[str, int, str], Any] = {}

orig_introspection_types = asyncpg.Connection._introspect_types

INTROSPECTION_KEY = os.environ.get(
"ASYNCPG_INTROSPECTION_CACHE_KEY", "ASYNCPG_INTROSPECTION_CACHE_KEY"
)

introspection_lock = asyncio.Lock()

class FauxResult:
_binary_fields = ("kind", "elemdelim")
column_order = [
"oid",
"ns",
"name",
"kind",
"basetype",
"elemtype",
"elemdelim",
"range_subtype",
"attrtypoids",
"attrnames",
"depth",
"basetype_name",
"elemtype_name",
"range_subtype_name",
]

def __init__(self, row=None, data: dict | None = None) -> None:
if row:
self.data = dict(row)
else:
assert data
self.data = data

def __getattr__(self, name: str) -> Any:
return self.data[name]

def __getitem__(self, idx_or_column_name: int | str) -> Any:
if isinstance(idx_or_column_name, int):
return self.data[self.column_order[idx_or_column_name]]
return self.data[idx_or_column_name]

def for_serialization(self) -> dict:
result = copy.copy(self.data)
for field in self._binary_fields:
if (value := self.data.get(field)) is not None:
result[field] = value.decode()
return result

@classmethod
def from_serialization(cls, data: dict) -> Self:
for field in cls._binary_fields:
if (value := data.get(field)) is not None:
data[field] = value.encode()
return cls(data=data)

class FauxPreparedStatementState:
def __init__(self, name) -> None:
self.name = name

async def to_redis_cache(
host: str, port: int, database: str, inspection_types: tuple[list, Any]
) -> None:
pss = FauxPreparedStatementState(inspection_types[1].name)
results = [FauxResult(row) for row in inspection_types[0]]
await redis_client().set(
INTROSPECTION_KEY + f"-{host}-{port}-{database}",
orjson.dumps([[result.for_serialization() for result in results], pss.name]),
)

async def from_redis_cache(host: str, port: int, database: str) -> tuple[list, Any] | None:
data = await redis_client().get(INTROSPECTION_KEY + f"-{host}-{port}-{database}")
if data is None:
return None
results, pss_name = orjson.loads(data)
pss = FauxPreparedStatementState(pss_name)
return [FauxResult.from_serialization(row) for row in results], pss

def apply_introspection_caching():

async def new_introspect_types(self, *args, **kwargs) -> Any:
host: str
port: int
database: str
host, port = self._addr
database = self._params.database
if (cached_val := introspection_result_cache.get((host, port, database))) is not None:
return cached_val
async with introspection_lock:
redis_cached_value = await from_redis_cache(host, port, database)
if redis_cached_value is not None:
introspection_result_cache[host, port, database] = redis_cached_value
return redis_cached_value
result = await orig_introspection_types(self, *args, **kwargs)
await to_redis_cache(host, port, database, result)
return result

asyncpg.Connection._introspect_types = new_introspect_types

```

Issue xác định asyncpg.Connection._introspect_types là điểm bắt đầu và báo cáo độ trễ nghiêm trọng khi introspection kiểu dữ liệu của CockroachDB; hãy bắt đầu từ đó và tái hiện hành vi với CockroachDB. Workaround caching được đề xuất cho thấy một hướng khả thi, nhưng bản sửa lỗi dự kiến và các tiêu chí hoàn thành cần có sự thống nhất của các maintainer.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.