{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6eaf856e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.chdir(\"../\")\n",
    "from gdb_utils import ConnectionPoolConfig, OpenGaussConnectionPool\n",
    "import py_opengauss\n",
    "\n",
    "DB_HOST = \"127.0.0.1\"\n",
    "DB_PORT = 5432\n",
    "DB_USER = \"gaussdb\"\n",
    "DB_PASSWORD = \"Ysl#1234\"\n",
    "DB_NAME = \"postgres\"\n",
    "\n",
    "\n",
    "def build_dsn() -> str:\n",
    "    return f\"opengauss://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}\"\n",
    "\n",
    "conn = py_opengauss.open(build_dsn())\n",
    "conn.execute(\"ROLLBACK\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "opengauss-pool-multithread",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "During workload stats: {'total': 10, 'available': 0, 'in_use': 1, 'closed': False}\n",
      "After workload stats: {'total': 10, 'available': 10, 'in_use': 0, 'closed': False}\n"
     ]
    }
   ],
   "source": [
    "import threading, time, datetime\n",
    "\n",
    "pool_config = ConnectionPoolConfig(\n",
    "    dsn=build_dsn(),\n",
    "    min_size=1,\n",
    "    max_size=20,\n",
    "    test_on_borrow=True,\n",
    "    test_sql=\"SELECT 1\",\n",
    ")\n",
    "pool = OpenGaussConnectionPool(pool_config)\n",
    "\n",
    "insert_sql = \"INSERT INTO my_schema.table1 (u_uid, process_id, ins_tm, content) VALUES ($1, $2, $3, $4)\"\n",
    "delete_sql = \"DELETE FROM my_schema.table1 WHERE u_uid = $1\"\n",
    "\n",
    "def worker(thread_id: int):\n",
    "    # u_uid is int; keep within 32-bit and unique-ish per thread\n",
    "    uid = thread_id * 100000 + int(time.time() * 1000) % 100000\n",
    "    with pool.connection() as conn:\n",
    "        inserter = conn.prepare(insert_sql)\n",
    "        # deleter = conn.prepare(delete_sql)\n",
    "        inserter(uid, f\"thr{thread_id:02d}\", datetime.datetime.now(), \"worker insert\")\n",
    "        # Hold the connection briefly to observe pool utilization\n",
    "        time.sleep(2)\n",
    "        # deleter(uid)\n",
    "\n",
    "threads = [threading.Thread(target=worker, args=(i,)) for i in range(10)]\n",
    "for t in threads:\n",
    "    t.start()\n",
    "\n",
    "# Check stats while work is in flight\n",
    "print(\"During workload stats:\", pool.stats())\n",
    "\n",
    "for t in threads:\n",
    "    t.join()\n",
    "\n",
    "print(\"After workload stats:\", pool.stats())\n",
    "\n",
    "# Close the pool when done\n",
    "pool.close()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv (3.13.3)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}