[fastAPI/Gunicorn]: psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "pg_type_typname_nsp_index" #1505
Answered
by
JLHasson
Matthieu-Tinycoaching
asked this question in
Questions
-
First Check
Commit to Help
Example Codefrom fastapi import FastAPI
from sqlmodel import SQLModel, create_engine
from configparser import ConfigParser
from typing import Dict
import os
from typing import Optional, List, Dict, Any
from pydantic import BaseModel
from sqlmodel import Field, SQLModel, Column, JSON, DateTime
import datetime
class SemanticSearchDbPprod(SQLModel, table=True):
__tablename__ = 'semantic_search_pprod'
id: Optional[int] = Field(default=None, primary_key=True)
id_user: int
id_matrices: Optional[List[str]] = None
date_time: datetime.datetime = Field(sa_column=Column(DateTime(timezone=True), nullable=False))
query: str
clean_query: str
semantic_search_result: Optional[Dict[Any, Any]] = Field(default=None, sa_column=Column(JSON))
error_code: int
## Function for setting connection information
def load_connection_info(
ini_filename: str
) -> Dict[str, str]:
parser = ConfigParser()
parser.read(ini_filename)
# Create a dictionary of the variables stored under the "postgresql" section of the .ini
conn_info = {param[0]: param[1] for param in parser.items("postgresql")}
# Adding a New Key–Value Pair of the variable stored under the "system" section of the .ini
conn_info['host'] = parser.get('system', 'host', vars=os.environ)
return conn_info
## Load connection info to "embeddings_sts_tf" database: host, database, user, password
conn_info_sts_embeddings = load_connection_info("./db.ini")
## Create SQLAlchemy engine to connect to postgreSQL Database
sqlEngine_sts_embeddings = create_engine('postgresql://{user}:{pw}@{host}:5432/{db}'.format(host=conn_info_sts_embeddings["host"], db=conn_info_sts_embeddings["database"], user=conn_info_sts_embeddings["user"], pw=conn_info_sts_embeddings["password"]), pool_recycle=3600, echo=False)
## Function for creating database and tables
def create_db_and_tables():
SQLModel.metadata.create_all(sqlEngine_sts_embeddings)
app = FastAPI()
@app.on_event("startup")
def on_startup():
create_db_and_tables()DescriptionThis script works very well when running this fastAPI app with However, when trying to use I got the following error: It seems that the function to create database and tables on startup tries it for each CPU core leading to already existing database/tables. How is it possible to only rnu this function once with gunicorn and avoid this error? Thanks! Operating SystemLinux Operating System DetailsUbuntu 18.04 LTS SQLModel Version0.0.4 Python Version3.8.8 Additional ContextNo response |
Beta Was this translation helpful? Give feedback.
Answered by
JLHasson
Oct 26, 2021
Replies: 2 comments
-
|
It appears this is a race condition when creating a table (https://www.postgresql.org/message-id/CA+TgmoZAdYVtwBfp1FL2sMZbiHCWT4UPrzRLNnX1Nb30Ku3-gg@mail.gmail.com). I'm not 100% on the right way to fix this but one way is to mov your call to |
Beta Was this translation helpful? Give feedback.
0 replies
Answer selected by
YuriiMotov
-
|
Could you try renaming your table and columns to something that probably does not conflict with PostgreSQL keywords. That worked for me. More so the query column name. |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
It appears this is a race condition when creating a table (https://www.postgresql.org/message-id/CA+TgmoZAdYVtwBfp1FL2sMZbiHCWT4UPrzRLNnX1Nb30Ku3-gg@mail.gmail.com). I'm not 100% on the right way to fix this but one way is to mov your call to
create_db_and_tablesinto a separate script that's run once before startup. This will create your tables prior to startup after which the program can access them in parallel. One suggestion for a place to do this that would work well with your Docker setup is in aprestart.shscript (https://github.com/tiangolo/uvicorn-gunicorn-docker/blob/master/docker-images/start.sh#L23)