Duplicate key value violates unique constraint "pg_extension_name_index"

[digvjs]

Hi All,

I cloned the repository https://github.com/elizaos/eliza.git and checked out the v0.1.7-alpha.1 tag. The database adapter I am using is the PostgreSQL vector DB, specifically the Docker image pgvector/pgvector:pg16 from Docker Hub.

Upon running pnpm start, the PostgreSQL database is populated with all the required tables, but I encounter the following error (see the attached image):

This issue occurs even when I do a fresh clone of the eliza project.

Environment Details:

Node version: 23.3.0
pnpm version: 9.15.2
PostgreSQL Docker Image: pgvector/pgvector:pg16

Thanks in Advance!

[github-actions]

Hello @digvjs! Welcome to the ai16z community. Thank you for opening your first issue; we appreciate your contribution. You are now a ai16z contributor!

[AIFlowML]

Investigation Summary:

PostgresDatabaseAdapter Initialization:
    The PostgresDatabaseAdapter class in packages/adapter-postgres/src/index.ts is responsible for managing the PostgreSQL database connection and operations. It includes methods for initializing the database, handling errors, and executing queries.

Schema Initialization:
    During the init method, the adapter checks if certain tables exist and attempts to initialize the schema by executing SQL scripts. This is where the extension setup might be happening.

Potential Issue:
    If the vector extension is being created in the schema initialization process and it already exists, it would lead to the duplicate key error you're seeing.

Recommended Steps:

Check Schema Files:
    Review the schema.sql file in the adapter-postgres package to see if there are any CREATE EXTENSION commands for the vector extension. Ensure that these commands are wrapped in a conditional check to prevent re-creation if the extension already exists.

Modify Initialization Logic:
    Update the initialization logic to check for the existence of the extension before attempting to create it. This can be done using a query like:

    DO $$ BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'vector') THEN
            CREATE EXTENSION vector;
        END IF;
    END $$;

Database Cleanup:
    As a temporary workaround, manually drop the vector extension from your database and let the initialization process recreate it. Use the following command in your PostgreSQL database:

    DROP EXTENSION IF EXISTS vector;

[AIFlowML]

Can you update me on this ?
Did you made it to run ?
Cav you do clean that db entry and try another run?

[digvjs]

Modified the schema.sql and added below code -

DROP EXTENSION IF EXISTS vector;

DO $$ BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'vector') THEN
                CREATE EXTENSION vector;
        END IF;
END $$;

There is no other place I am creating the extension again.

Still getting below errors -

["✓ Successfully connected to PostgreSQL database"] 

 ⛔ ERRORS
   Error starting agent for character Eliza: 
   {"length":328,"name":"error","severity":"ERROR","code":"23505","detail":"Key (extname)=(vector) already exists.","where":"SQL statement \"CREATE EXTENSION vector\"\nPL/pgSQL function inline_code_block line 3 at SQL statement","schema":"pg_catalog","table":"pg_extension","constraint":"pg_extension_name_index","file":"nbtinsert.c","line":"666","routine":"_bt_check_unique"} 

 ["⛔ error: duplicate key value violates unique constraint \"pg_extension_name_index\""] 

 ⛔ ERRORS
   Error starting agents: 
   {"length":328,"name":"error","severity":"ERROR","code":"23505","detail":"Key (extname)=(vector) already exists.","where":"SQL statement \"CREATE EXTENSION vector\"\nPL/pgSQL function inline_code_block line 3 at SQL statement","schema":"pg_catalog","table":"pg_extension","constraint":"pg_extension_name_index","file":"nbtinsert.c","line":"666","routine":"_bt_check_unique"} 

 ["◎ Run `pnpm start:client` to start the client and visit the outputted URL (http://localhost:5173) to chat with your agents"] 

 ["✓ REST API bound to 0.0.0.0:3000. If running locally, access it at http://localhost:3000."] 

[AIFlowML]

Deffo need to look in the PG