Metadata-Version: 2.1
Name: pgdbpool
Version: 1.0rc1
Summary: A tiny database de-multiplexer primarily scoped for Web- / Application Server.
Author: Claus Prüfer
Author-email: Claus Prüfer <pruefer@webcodex.de>
Project-URL: Homepage, https://github.com/clauspruefer/python-dbpool
Project-URL: Issues, https://github.com/clauspruefer/python-dbpool/issues
Project-URL: Documentation, https://pythondocs.webcodex.de/pgdbpool
Project-URL: Changelog, https://github.com/clauspruefer/python-dbpool/CHANGELOG.md
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: GNU Affero General Public License v3 or later (AGPLv3+)
Classifier: Operating System :: OS Independent
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE

# Python PgDatabase-Pool Module

![push main](https://github.com/clauspruefer/python-dbpool/actions/workflows/pylint.yaml/badge.svg)
[![PyPI version](https://badge.fury.io/py/pgdbpool.svg)](https://badge.fury.io/py/pgdbpool)

## 1. Primary Scope

The **pgdbpool** Python module is a tiny **PostgreSQL Database Connection De-Multiplexer**, primarily designed for *Web- / Application Servers*.

## 2. Current Implementation

```text
+----------------------+                         +---------------------
| WebServer Service.py | -- Handler Con #1 ----> | PostgreSQL
| Request / Thread #1  |                         | Backend
+----------------------+                         |
                                                 |
+----------------------+                         |
| WebServer Service.py | -- Handler Con #2 ----> |
| Request / Thread #2  |                         |
+----------------------+                         +---------------------
```

### 2.1. Concept / Simplicity

If configured in a Web Server's WSGI Python script, the pooling logic is straightforward:

1. Check if a free connection in the pool exists.
2. Verify if the connection is usable (SQL ping).
3. Use the connection and protect it from being accessed until the query/queries are completed.
4. Release the connection for reuse.
5. Reconnect to the endpoint if the connection is lost.

## 3. Thread Safety / Global Interpreter Lock

Thread safety is currently ensured via `lock = threading.Lock()`, which relies on a kernel mutex `syscall()`.

While this concept works, the GIL (Global Interpreter Lock) in Python thwarts scalability under heavy loads in a threaded Web Server setup.

>[!IMPORTANT]
> Refer to Section **6: Future** for a potential solution to this problem.

## 4. Dependencies / Installation

**Python 3** and the **psycopg2** module are required.

```bash
# install (debian)
apt-get install python3-psycopg2
pip install pgdbpool
```

## 5. Documentation / Examples

See documentation either at `./doc` or [https://pythondocs.webcodex.de/pgdbpool](https://pythondocs.webcodex.de/pgdbpool)
for detailed explanation / illustrative examples.

## 6. Future

The DB-pooling functionality should also be compatible with the FalconAS
Python Application Server (https://github.com/WEBcodeX1/http-1.2).

The proposed model: **1 Process == 1 Python Interpreter (threading-less)**,
effectively solving the GIL issue.

>[!NOTE]
> The pool should also be configurable to use multiple (read-load-balanced)
> PostgreSQL endpoints.

[![linting: pylint](https://img.shields.io/badge/linting-pylint-yellowgreen)](https://github.com/PyCQA/pylint)
