Postgresql@17でベクトル保存を確認する（Mac）

### postgresql@17をインストールし、サービスを開始する：
```bash
brew services stop postgresql@16
brew install postgresql@17
brew services start postgresql@17
brew services list

Name          Status  User File
postgresql@14 none    tn
postgresql@16 none
postgresql@17 started tn   ~/Library/LaunchAgents/homebrew.mxcl.postgresql@17.plist
```
dbを作成する：
```bash
createdb testdb;
psql -U tn -d testdb

WARNING: psql major version 16, server major version 17.
         Some psql features might not work.
```
psqlを再インストールしても16.4のままだった：
```bash
~ $brew reinstall libpq
~ $psql --version
psql (PostgreSQL) 16.4
```

vector拡張のインストール、@17でも予想通りのエラー：
```bash
testdb=# create extension vector;

ERROR:  extension "vector" is not available
DETAIL:  Could not open extension control file "/usr/local/share/postgresql@17/extension/vector.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.
```


### ベクトル拡張のインストール
 始めてインストール場合は、*1と同様に：
 ```bash
git clone --branch v0.7.4 https://github.com/pgvector/pgvector.git
cd pgvector
export PG_CONFIG=/usr/local/opt/postgresql@17/bin/pg_config
make PG_CONFIG=$PG_CONFIG all
make PG_CONFIG=$PG_CONFIG install
 ```
 今回の筆者のようにmakeが2回目以上の場合：
 ```bash
 cd ~/pgvector
 export PG_CONFIG=/usr/local/opt/postgresql@17/bin/pg_config
make PG_CONFIG=$PG_CONFIG clean
make PG_CONFIG=$PG_CONFIG all 
make PG_CONFIG=$PG_CONFIG install
 ```
再度:
```bash
testdb=# create extension vector;
CREATE EXTENSION
testdb=# \dx
List of installed extensions
  Name   | Version |   Schema   |                     Description                      
---------+---------+------------+------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 vector  | 0.7.4   | public     | vector data type and ivfflat and hnsw access methods
(2 rows)
 ```
 とベクトル拡張が確認できる。

 以下で、*2に習って、dbへの書き込みとその確認を行います。
 予めollamaのインストールとembedding モデル"bge-m3"をollama pullしておきます（モデルは適当ですし、ollamaを使わずにOpenAIのもので、もちろん結構です。その場合は適宜変更ください）。
 ```bash
 !pip install langchain-postgres psycopg langchain-ollama langchain

from langchain_postgres import PGVector
from langchain_postgres.vectorstores import PGVector
from langchain_core.documents import Document
from langchain_ollama import OllamaEmbeddings

embedding = OllamaEmbeddings(
    model="bge-m3"
)

#パスワードはかけていない
connection = "postgresql+psycopg://tn@localhost:5432/testdb" 
collection_name = "my_docs"

vectorstore = PGVector(
    embeddings=embedding,
    collection_name=collection_name,
    connection=connection,
    use_jsonb=True,
)

# langchainが作成したテーブルの確認
!psql -d testdb -c "\dt"
                List of relations
 Schema |          Name           | Type  | Owner 
--------+-------------------------+-------+-------
 public | langchain_pg_collection | table | tn
 public | langchain_pg_embedding  | table | tn
(2 rows)
!psql -d testdb -c "\d langchain_pg_embedding"               

Table "public.langchain_pg_embedding"
    Column     |       Type        | Collation | Nullable | Default 
---------------+-------------------+-----------+----------+---------
 id            | character varying |           | not null | 
 collection_id | uuid              |           |          | 
 embedding     | vector            |           |          | 
 document      | character varying |           |          | 
 cmetadata     | jsonb             |           |          | 
...
# *3のデータで、id:10を修正、id:11を追加した
docs = [
    Document(page_content='there are cats in the pond', metadata={"id": 1, "location": "pond", "topic": "animals"}),
    Document(page_content='ducks are also found in the pond', metadata={"id": 2, "location": "pond", "topic": "animals"}),
    Document(page_content='fresh apples are available at the market', metadata={"id": 3, "location": "market", "topic": "food"}),
    Document(page_content='the market also sells fresh oranges', metadata={"id": 4, "location": "market", "topic": "food"}),
    Document(page_content='the new art exhibit is fascinating', metadata={"id": 5, "location": "museum", "topic": "art"}),
    Document(page_content='a sculpture exhibit is also at the museum', metadata={"id": 6, "location": "museum", "topic": "art"}),
    Document(page_content='a new coffee shop opened on Main Street', metadata={"id": 7, "location": "Main Street", "topic": "food"}),
    Document(page_content='the book club meets at the library', metadata={"id": 8, "location": "library", "topic": "reading"}),
    Document(page_content='the library hosts a weekly story time for kids', metadata={"id": 9, "location": "library", "topic": "reading"}),
    Document(page_content='there are tigers in the yard', metadata={"id": 10, "location": "zoo", "topic": "animals"}),
    Document(page_content='there are dogs in the backyard', metadata={"id": 11, "location": "my home", "topic": "animals"})
]

#　dbに書き込む
vectorstore.add_documents(docs, ids=[doc.metadata['id'] for doc in docs])

# オマケ
results = vectorstore.similarity_search_with_score(query="lion",k=5)
for doc, score in results:
    print(f"* [SIM={score:3f}] {doc.page_content} [{doc.metadata}]")

* [SIM=0.457508] there are tigers in the yard [{'id': 10, 'topic': 'animals', 'location': 'zoo'}]
* [SIM=0.494071] there are dogs in the backyard [{'id': 11, 'topic': 'animals', 'location': 'my home'}]
* [SIM=0.540048] ducks are also found in the pond [{'id': 2, 'topic': 'animals', 'location': 'pond'}]
* [SIM=0.541976] there are cats in the pond [{'id': 1, 'topic': 'animals', 'location': 'pond'}]
* [SIM=0.557055] the book club meets at the library [{'id': 8, 'topic': 'reading', 'location': 'library'}]

```
### データベースからデータを取得する
メモリクリアのために、jupyterカーネルの再起動を行なって:
```bash
!pip install psycopg
import psycopg

conn = psycopg.connect("dbname=testdb user=tn")
cur = conn.cursor()
cur.execute('select * from langchain_pg_embedding')
for row in cur:
    formatted_output = f"id: {row[0]}\n" \
                    f"uuid: {row[1]}\n" \
                    f"page_content: {row[2][:100]}...\n" \
                    f"page_content(string): {row[3]}\n" \
                    f"metadata: {row[4]}\n"
    print(formatted_output)
cur.close()
conn.close()

id: 1
uuid: 46dd887b-7d08-43f5-b89f-6e6650b8594c
page_content: [-0.041045193,0.009569716,-0.093480445,0.01990515,0.00062993786,-0.057626557,-0.02735376,-0.01263911...
page_content(string): there are cats in the pond
metadata: {'id': 1, 'topic': 'animals', 'location': 'pond'}

id: 2
uuid: 46dd887b-7d08-43f5-b89f-6e6650b8594c
page_content: [-0.0365837,0.0019632874,-0.0848764,-0.007010041,-0.028483586,-0.027577631,-1.8776509e-05,-0.0053111...
page_content(string): ducks are also found in the pond
metadata: {'id': 2, 'topic': 'animals', 'location': 'pond'}

id: 3
uuid: 46dd887b-7d08-43f5-b89f-6e6650b8594c
page_content: [0.02102992,0.006635084,-0.05879326,-0.004522216,-0.0065848893,-0.03556204,0.009028944,0.03192697,0....
page_content(string): fresh apples are available at the market
metadata: {'id': 3, 'topic': 'food', 'location': 'market'}

id: 4
uuid: 46dd887b-7d08-43f5-b89f-6e6650b8594c
page_content: [-0.021443207,0.001050144,-0.07157226,0.009703566,9.951767e-05,0.0027933442,-0.01428185,0.008426342,...
page_content(string): the market also sells fresh oranges
metadata: {'id': 4, 'topic': 'food', 'location': 'market'}

id: 5
...
page_content: [-0.04939314,0.0038462288,-0.08330972,0.017653793,-0.023387564,0.011244474,0.02507997,0.012847753,-0...
page_content(string): there are dogs in the backyard
metadata: {'id': 11, 'topic': 'animals', 'location': 'my home'}

Output is truncated. View as a scrollable element or open in a text editor. Adjust cell output settings...
```

ちゃんと呼び出せたので、作成したDBを削除:
```bash
!dropdb -f testdb
```

参考情報：
*1: https://qiita.com/tnagata/items/7e6ae9956bdcaf167d94
*2: https://qiita.com/tnagata/items/c4a08d868b838e3bb3ea
*3: https://github.com/langchain-ai/langchain-postgres/blob/main/examples/vectorstore.ipynb