GPT4All 2.0.2连接PostgreSQL遇提示超上下文窗口的参数调整咨询

问题场景 #

使用GPT4All 2.0.2版本，结合Langchain包连接PostgreSQL数据库执行查询，代码如下：

from langchain.callbacks.streaming_stdout import StreamingStdOutCallbackHandler
from langchain_experimental.sql import SQLDatabaseChain
from langchain import SQLDatabase
from langchain.llms import GPT4All
import os

username = "postgres" 
password = "password" 
host = "127.0.0.1" # internal IP 
port = "5432"
mydatabase = "reporting_db"

pg_uri = f"postgresql+psycopg2://{username}:{password}@{host}:{port}/{mydatabase}"
my_db = SQLDatabase.from_uri(pg_uri)

PROMPT = """ 
Given an input question, first create a syntactically correct postgresql query to run,  
then look at the results of the query and return the answer.  
The question: {question}
"""

path = "./models/mistral-7b-openorca.Q4_0.gguf"

callbacks = [StreamingStdOutCallbackHandler()]

llm = GPT4All(model = path,
              callbacks=callbacks,
              n_threads=3,
              max_tokens=5162,
              verbose=True
             )


db_chain = SQLDatabaseChain.from_llm(llm = llm, 
                     db = my_db,         
                     verbose=True
                     )

question = "Describe the table Sales"
answer = db_chain.run(PROMPT.format(question=question)
                     )
print(answer)

错误信息 #

运行后触发如下SQL语法错误，本质是LLM上下文窗口超限导致输出异常：

ERROR:  sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at 
or near "ERROR"
LINE 1: ERROR: The prompt size exceeds the context window size and c...
    ^

[SQL: ERROR: The prompt size exceeds the context window size and cannot be processed.]
(Background on this error at: https://sqlalche.me/e/20/f405)

解决方案 #

错误核心是提示内容超出LLM的上下文窗口限制，需调整以下参数和配置：

• 降低max_tokens参数：当前设置的max_tokens=5162超出模型实际可用上下文空间，Mistral-7B虽标称8192上下文窗口，但需预留空间给系统提示和输出，建议调低至2048或4096。
• 优化SQLDatabaseChain配置：添加top_k参数限制查询返回行数，减少结果数据占用上下文；同时直接将自定义提示传入链的prompt参数，避免重复拼接导致提示冗余。
• 简化自定义PROMPT：压缩提示内容长度，去掉不必要的换行和冗余描述。

修改后的代码示例 #

from langchain.callbacks.streaming_stdout import StreamingStdOutCallbackHandler
from langchain_experimental.sql import SQLDatabaseChain
from langchain import SQLDatabase
from langchain.llms import GPT4All
import os

username = "postgres" 
password = "password" 
host = "127.0.0.1" # internal IP 
port = "5432"
mydatabase = "reporting_db"

pg_uri = f"postgresql+psycopg2://{username}:{password}@{host}:{port}/{mydatabase}"
my_db = SQLDatabase.from_uri(pg_uri)

# 简化提示内容
PROMPT = """Given the question, generate a valid PostgreSQL query, run it, then return the answer. Question: {question}"""

path = "./models/mistral-7b-openorca.Q4_0.gguf"

callbacks = [StreamingStdOutCallbackHandler()]

# 调低max_tokens参数
llm = GPT4All(model = path,
              callbacks=callbacks,
              n_threads=3,
              max_tokens=2048,
              verbose=True
             )


# 添加top_k参数限制返回行数，直接传入prompt
db_chain = SQLDatabaseChain.from_llm(llm = llm, 
                     db = my_db,         
                     verbose=True,
                     prompt=PROMPT,
                     top_k=5
                     )

question = "Describe the table Sales"
answer = db_chain.run(question)
print(answer)

内容的提问来源于stack exchange，提问作者AndCh