AGE extension requires explicit commit after LOAD/SET for proper initialization in non-autocommit clients

[NathanInbar]

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:

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:

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