You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@age.apache.org by "NathanInbar (via GitHub)" <gi...@apache.org> on 2025/07/28 22:15:21 UTC
[I] AGE extension requires explicit commit after LOAD/SET for proper initialization in non-autocommit clients [age]
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: dev-unsubscribe@age.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] AGE extension requires explicit commit after LOAD/SET for proper initialization in non-autocommit clients [age]
Posted by "crprashant (via GitHub)" <gi...@apache.org>.
crprashant commented on issue #2195:
URL: https://github.com/apache/age/issues/2195#issuecomment-4329559017
This issue can be closed now , ReadMe.md is updated with relevant documentation for this issue; Thank you @jrgemignani
--
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: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] AGE extension requires explicit commit after LOAD/SET for proper initialization in non-autocommit clients [age]
Posted by "crprashant (via GitHub)" <gi...@apache.org>.
crprashant commented on issue #2195:
URL: https://github.com/apache/age/issues/2195#issuecomment-4315498956
## Status check on PG18 + AGE 1.7.0
I looked into whether this is resolved on the newer stack (PostgreSQL 18 + Apache AGE 1.7.0, released as `PG18/v1.7.0-rc0` on 2026-01-21, which is the first AGE release supporting PG18 via #2251).
**Short answer: not resolved, and arguably not a bug — but the docs could help.**
### What I checked
- Release notes for [`PG18/v1.7.0-rc0`](https://github.com/apache/age/releases/tag/PG18/v1.7.0-rc0) and [`PG17/v1.7.0-rc0`](https://github.com/apache/age/releases/tag/PG17/v1.7.0-rc0) — no entry references `LOAD`, `search_path`, transaction visibility, or cross-session persistence of AGE setup.
- No PRs linked to this issue, no assignee, no milestone.
### Why the behavior persists (and is expected)
The described symptom is actually standard PostgreSQL transaction semantics, not something AGE can "fix" without violating them:
1. `LOAD 'age'` and `SET search_path = ...` are **session-local** — they don't write to the catalog and have nothing to commit.
2. `SELECT * FROM create_graph('my_graph')` **does** write to `ag_graph` and creates a schema — those writes are catalog changes that must be in a **committed top-level transaction** to be visible to other sessions.
3. In `psycopg` with the default (non-autocommit) mode, the very first statement (`LOAD 'age'`) implicitly starts an outer transaction. The later `with connection.transaction():` block then runs as a **savepoint inside that outer transaction**, not as its own top-level transaction. Releasing a savepoint is not a commit — the outer transaction stays open until `connection.commit()` or `connection.close()`.
That's why the explicit `connection.commit()` after `LOAD`/`SET` "fixes" it: it closes the implicit outer transaction, so the subsequent `with connection.transaction()` block becomes a real top-level transaction that actually commits the `create_graph` writes.
PostgreSQL 18 does not change this model, and AGE 1.7.0 contains no change that would alter it.
### Suggested resolutions
Making AGE bypass transactional visibility for `create_graph` would break ACID guarantees, so the "Preferred" option in the original post probably isn't viable. The practical paths forward:
- **Docs (acceptable fallback in the original issue):** add a "Using AGE with non-autocommit clients (psycopg, JDBC, etc.)" section to the README / setup guide explicitly stating:
- `create_graph`, `create_vlabel`, `create_elabel`, `drop_graph`, etc. perform catalog writes.
- In non-autocommit clients, call `connection.commit()` (or set `autocommit=True` for setup) so those writes become visible to other sessions.
- Explain the psycopg "implicit outer transaction + savepoint" gotcha, which is the real source of confusion here.
Happy to open a docs PR if maintainers agree this is the right direction.
--
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: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org