Cannot use SQLAlchemyCache with with_structured_output: psycopg2.errors.ProgramLimitExceeded: index row requires 20376 bytes, maximum size is 8191

[boxydog]

Checked other resources

• I added a very descriptive title to this issue.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.
• I am sure that this is a bug in LangChain rather than my code.
• The bug is not resolved by updating to the latest stable version of LangChain (or the specific integration package).

Example Code

#!/usr/bin/env python
import os

from langchain.globals import set_llm_cache
from langchain_community.cache import SQLAlchemyCache
from langchain_core.prompts import ChatPromptTemplate
from langchain_openai import ChatOpenAI
from langchain_core.pydantic_v1 import BaseModel, Field
from sqlalchemy import create_engine

_LLM_MODEL_NAME = "gpt-3.5-turbo-0125"


def set_up_llm_cache():
    _db_url = os.environ.get("DATABASE_URL")

    engine = create_engine(_db_url)
    set_llm_cache(SQLAlchemyCache(engine))


class WikiPageInfo(BaseModel):
    """Information about a wikipedia page."""

    # This doc-string is sent to the LLM as the description of the schema,
    # and it can help to improve extraction results.

    # Note that:
    # 1. Each field is an `optional` -- this allows the model to decline to extract it
    # 2. Each field has a `description` -- this description is used by the LLM.
    # Having a good description can help improve extraction results.
    page_title: str | None = Field(default=None, description="The title of the page")
    short_summary: str | None = Field(
        default=None, description="A short summary of the page"
    )
    quality: str | None = Field(
        default=None, description="A guess at the quality of the page "
                                  "as a letter grade: A, B, C, D, F."
    )
    category_list: list[str] = Field(
        default=[], description="A list of wikipedia categories this page is in"
    )
    missing_categories_list: list[str] = Field(
        default=[], description="A list of wikipedia categories this page "
                                "is not in but should be in"
    )


def extract():
    set_up_llm_cache()

    prompt = ChatPromptTemplate.from_messages(
        [
            (
                "system",
                "You are an expert extraction algorithm. "
                "Only extract relevant information from the text. "
                "If you do not know the value of an attribute asked to extract, "
                "return null for the attribute's value.",
            ),
            ("human", "{text}"),
        ]
    )

    llm = ChatOpenAI(model=_LLM_MODEL_NAME, temperature=0)

    runnable = prompt | llm.with_structured_output(schema=WikiPageInfo)

    text = open("llm.wiki.txt").read()
    info = runnable.invoke({"text": text})
    print(info)


if __name__ == "__main__":
    extract()

llm.wiki.txt

Error Message and Stack Trace (if applicable)

sqlalchemy.exc.OperationalError: (psycopg2.errors.ProgramLimitExceeded) index row requires 20400 bytes, maximum size is 8191

$ ./err.py
Traceback (most recent call last):
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.ProgramLimitExceeded: index row requires 20400 bytes, maximum size is 8191

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/Users/me/work/myproject/tmp/./err.py", line 74, in
extract()
File "/Users/me/work/myproject/tmp/./err.py", line 69, in extract
info = runnable.invoke({"text": text})
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/langchain_core/runnables/base.py", line 2499, in invoke
input = step.invoke(
^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/langchain_core/runnables/base.py", line 4525, in invoke
return self.bound.invoke(
^^^^^^^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/langchain_core/language_models/chat_models.py", line 158, in invoke
self.generate_prompt(
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/langchain_core/language_models/chat_models.py", line 560, in generate_prompt
return self.generate(prompt_messages, stop=stop, callbacks=callbacks, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/langchain_core/language_models/chat_models.py", line 421, in generate
raise e
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/langchain_core/language_models/chat_models.py", line 411, in generate
self._generate_with_cache(
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/langchain_core/language_models/chat_models.py", line 651, in _generate_with_cache
llm_cache.update(prompt, llm_string, result.generations)
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/langchain_community/cache.py", line 284, in update
with Session(self.engine) as session, session.begin():
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/util.py", line 147, in exit
with util.safe_reraise():
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/util.py", line 145, in exit
self.commit()
File "", line 2, in commit
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1302, in commit
self._prepare_impl()
File "", line 2, in _prepare_impl
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1277, in _prepare_impl
self.session.flush()
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4341, in flush
self._flush(objects)
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4476, in _flush
with util.safe_reraise():
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4437, in _flush
flush_context.execute()
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
rec.execute(self)
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
util.preloaded.orm_persistence.save_obj(
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
_emit_insert_statements(
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 1048, in _emit_insert_statements
result = connection.execute(
^^^^^^^^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
return self._exec_single_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
self._handle_dbapi_exception(
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/Users/me/work/myproject/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ProgramLimitExceeded) index row requires 20400 bytes, maximum size is 8191

[SQL: INSERT INTO full_llm_cache (prompt, llm, idx, response) VALUES (%(prompt)s, %(llm)s, %(idx)s, %(response)s)]
[parameters: {'prompt': '[{"lc": 1, "type": "constructor", "id": ["langchain", "schema", "messages", "SystemMessage"], "kwargs": {"content": "You are an expert extraction alg ... (35361 characters truncated) ... rocessing}}\n\n[[Category:Large language models| ]]\n[[Category:Deep learning]]\n[[Category:Natural language processing]]\n", "type": "human"}}]', 'llm': '{"lc": 1, "type": "constructor", "id": ["langchain", "chat_models", "openai", "ChatOpenAI"], "kwargs": {"model_name": "gpt-3.5-turbo-0125", "temperat ... (1301 characters truncated) ... list of wikipedia categories this page is not in but should be in', 'default': [], 'type': 'array', 'items': {'type': 'string'}}}}}}])]', 'idx': 0, 'response': '{"lc": 1, "type": "constructor", "id": ["langchain", "schema", "output", "ChatGeneration"], "kwargs": {"generation_info": {"finish_reason": "stop", " ... (1513 characters truncated) ... t generation and classification tasks.", "page_title": "Large language model"}, "id": "call_nPEsgoU6SAZ9IeZqynL977Cr"}], "invalid_tool_calls": []}}}}'}]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Description

I'm trying to use the langchain SQLAlchemyCache with Postgres.

It doesn't work because it is trying to insert some big thing into an index that wants a small thing.

System Info

$ python -m langchain_core.sys_info

System Information
------------------
> OS:  Darwin
> OS Version:  Darwin Kernel Version 21.6.0: Mon Feb 19 20:24:34 PST 2024; root:xnu-8020.240.18.707.4~1/RELEASE_X86_64
> Python Version:  3.11.7 (main, Jan 16 2024, 15:02:38) [Clang 14.0.0 (clang-1400.0.29.202)]

Package Information
-------------------
> langchain_core: 0.1.52
> langchain: 0.1.20
> langchain_community: 0.0.38
> langsmith: 0.1.59
> langchain_openai: 0.1.7
> langchain_text_splitters: 0.0.2

Packages not installed (Not Necessarily a Problem)
--------------------------------------------------
The following packages were not found:

> langgraph
> langserve

$ postgres --version
postgres (PostgreSQL) 15.1

[boxydog]

$ pip freeze | grep -i alchemy
SQLAlchemy==2.0.30
$ pip freeze | grep -i psyco
psycopg2==2.9.9

[keenborder786]

The problem is with the data types associated with the table that postgresql is using for caching, therefore you need to change the data types for the table as follow:

class FullLLMCache(Base):  # type: ignore
    """SQLite table for full LLM Cache (all generations)."""

    __tablename__ = "full_llm_cache_table"
    prompt = Column(Text, primary_key=True)
    llm = Column(Text, primary_key=True)
    idx = Column(Integer, primary_key=True)
    response = Column(Text)

def set_up_llm_cache():
    _db_url = "postgresql+psycopg2://postgres:root@localhost:5432/cache"

    engine = create_engine(_db_url)
    set_llm_cache(SQLAlchemyCache(engine,cache_schema=FullLLMCache))

Text type in postgresql will allow to store strings of any length as oppose to character varying. Please see this