NathanInbar opened a new issue, #2195:
URL: https://github.com/apache/age/issues/2195
**Is your feature request related to a problem? Please describe.**
When using Apache AGE with database clients that do not default to
autocommit (e.g., psycopg, JDBC), the extension requires an explicit COMMIT
after initial setup (LOAD 'age', SET search_path) for the changes to truly
persist across different client connections. This behavior is undocumented and
inconsistent with typical PostgreSQL extension behavior.
For example, the following fails to allow a new connection to discover the
created graph:
```python
import psycopg
connection_params = {
"host": "localhost",
"port": 5432,
"user": "postgres",
"password": "your_password",
"dbname": "your_database"
}
# --- First Connection ---
connection = psycopg.connect(**connection_params)
connection.execute("LOAD 'age'")
connection.execute("SET search_path = ag_catalog, '$user', public")
with connection.transaction(), connection.cursor() as cursor:
cursor.execute("SELECT * FROM create_graph('my_graph')")
# The graph 'my_graph' is created and this transaction is committed here
# by the 'with connection.transaction()' block.
with connection.cursor() as cursor:
cursor.execute("SELECT name FROM ag_graph;")
# Within this same connection, 'my_graph' is visible immediately.
assert 'my_graph' in [row[0] for row in cursor.fetchall()]
print("Assertion Passed (within same initial connection)")
connection.close()
# --- Test with a new (second) connection ---
connection = psycopg.connect(**connection_params)
# In the new connection, LOAD 'age' and SET search_path are still needed
# for AGE functions to be available in this session.
connection.execute("LOAD 'age'")
connection.execute("SET search_path = ag_catalog, '$user', public")
with connection.cursor() as cursor:
cursor.execute("SELECT name FROM ag_graph;")
# PROBLEM: 'my_graph' is NOT found by the new connection.
# This assertion will fail, demonstrating the lack of persistence from
the first connection's setup.
assert 'my_graph' in [row[0] for row in cursor.fetchall()]
connection.close()
```
Later queries from a new connection fail to find the created graph. Adding
an explicit connection.commit() in the first connection after the initial setup
commands resolves the issue:
```python
import psycopg
connection_params = {
"host": "localhost",
"port": 5432,
"user": "postgres",
"password": "your_password",
"dbname": "your_database"
}
# --- First Connection (with commit) ---
connection = psycopg.connect(**connection_params)
connection.execute("LOAD 'age'")
connection.execute("SET search_path = ag_catalog, '$user', public")
connection.commit() # <----------------- This crucial commit makes AGE's
setup persistent.
with connection.transaction(), connection.cursor() as cursor:
cursor.execute("SELECT * FROM create_graph('my_graph')")
# The graph 'my_graph' is created and this transaction is committed here.
with connection.cursor() as cursor:
cursor.execute("SELECT name FROM ag_graph;")
assert 'my_graph' in [row[0] for row in cursor.fetchall()]
print("Assertion Passed (within same initial connection, with commit)")
connection.close()
# --- Test with a new (second) connection ---
connection = psycopg.connect(**connection_params)
connection.execute("LOAD 'age'")
connection.execute("SET search_path = ag_catalog, '$user', public")
# No explicit commit needed here, as the persistent setup was committed by
the first connection.
with connection.cursor() as cursor:
cursor.execute("SELECT name FROM ag_graph;")
# SUCCESS: 'my_graph' is now found by the new connection.
assert 'my_graph' in [row[0] for row in cursor.fetchall()]
connection.close()
```
This demonstrates that the persistence of my_graph relies on a commit after
LOAD 'age' and SET search_path in the session where AGE is first prepared.
**Describe the solution you'd like**
Preferred: Modify AGE so that LOAD 'age' and SET search_path take effect
immediately, even within an open transaction, and that their internal
persistent effects are not implicitly tied to a subsequent explicit COMMIT from
the client connection, aligning with how PostgreSQL handles other extensions.
Acceptable fallback: Document this requirement prominently in the official
setup guides.
**Describe alternatives you've considered**
- Always calling commit() manually after setup (current workaround)
- Enabling autocommit in the client session, which changes global connection
behavior
**Additional context**
This behavior suggests that AGE's initialization (LOAD 'age', SET
search_path) performs operations that modify persistent catalog-like data,
which then requires a transaction COMMIT to be visible globally (i.e., to other
new sessions). This is unexpected for session-level configuration commands.
Test Environment:
- PostgreSQL version: PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
- Apache AGE version: 1.5.0
- Python 3.12.11
- Client: psycopg 3.2.9
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
