Getting this weird error for `index.

At a glance

The community member is experiencing a "UniqueViolation" error when trying to insert nodes into a pg_vector vector store. The error indicates that the id value they are trying to insert already exists in the database. To solve this issue, the community member reset the database table's ID sequence by checking the current sequence value, finding the maximum existing ID, and then setting the sequence to start from the next available ID value. This resolved the problem and allowed the community member to successfully insert the new nodes.

JJoshhhh

Getting this weird error for index.insert_nodes(child_nodes) where child_node = TextNode(id_=str(uuid4()), text=formatted_text)

Plain Text

UniqueViolation: duplicate key value violates unique constraint "data_pg_vector_store_pkey"
DETAIL:  Key (id)=(93735) already exists.

I'm using pg_vector as my vector_store and have inserted many nodes in a similar fashion as the above. This is the first time I've seen this error

1 comment

JJoshhhh

No idea how my db table sequence got out of wack, but here's how I solved this:

Plain Text

-----------------------------------------------------------------------------------------------------------------------------------
-- RESET DB TABLE ID SEQUENCE
-----------------------------------------------------------------------------------------------------------------------------------

-- Get pg_vector's sequence name
SELECT pg_get_serial_sequence('data_pg_vector_store', 'id'); -- `public.data_pg_vector_store_id_seq`

-- Check the next value of the sequence
SELECT nextval('public.data_pg_vector_store_id_seq') - 1; -- 93735 (Problem identified!)

-- Double check settings
SELECT * FROM public.data_pg_vector_store_id_seq;
-- Returns:
-- last_value    log_cnt    is_called
-- 93736    32    TRUE

-- Double check latest values
SELECT id FROM data_pg_vector_store ORDER BY id DESC LIMIT 10; -- 191161

-- Reset the sequence to be +1 more than the last ID value
SELECT setval('public.data_pg_vector_store_id_seq', COALESCE((SELECT MAX(id)+1 FROM data_pg_vector_store), 1), false); -- 191162 --> Set the sequence to start from 191162

-- SUCCESS!

Add a reply

Find answers from the community

Getting this weird error for `index.