apache / apache/age

Cypher fails after pg_dump/restore: ag_graph.graphid is a bare oid and keeps the source database's value

Open
#2,503 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
4.8k
Forks
523
Avg merge
1d 2h
Merged PRs (30d)
9

Description

### Summary

After `pg_dump` + restore of a database containing a graph, **every Cypher query fails** with `graph with oid N does not exist`, even though the restore reports no errors and all label tables and rows arrive intact.

The cause is that `ag_catalog.ag_graph` stores the graph's namespace **twice, in two different types**, and only one of them survives a logical dump:

| Column | Type | Survives `pg_dump`/restore? |
| --- | --- | --- |
| `ag_graph.namespace` | `regnamespace` | ✅ dumped as the schema **name**, re-resolves to the new oid |
| `ag_label.relation` | `regclass` | ✅ dumped as the table **name**, re-resolves |
| `ag_graph.graphid` | `oid` | ❌ dumped as a bare integer — still the **source** database's namespace oid |
| `ag_label.graph` | `oid` | ❌ same, and FK-bound to `graphid` |

`cypher()` resolves the graph through `graphid`, so it looks for a namespace oid that only ever existed in the source cluster.

The failure is **silent at restore time** — `psql -f dump.sql` exits with 0 errors. The first symptom is every query failing later.

### Reproduction

AGE 1.5.0, PostgreSQL 16.9 (Debian 16.9-1.pgdg120+1), aarch64.

```sql
-- source database
CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT create_graph('demo');
SELECT * FROM cypher('demo', $$ CREATE (:P {name: 'a'}) $$) AS (v agtype);
SELECT * FROM cypher('demo', $$ MATCH (n:P) RETURN n.name $$) AS (name agtype); -- "a"
```

```bash
pg_dump -U postgres -d age_repro_src -f age_repro.sql
createdb -U postgres age_repro_dst
psql -U postgres -d age_repro_dst -f age_repro.sql # 0 errors
```

```sql
-- restored database
SELECT name, graphid, namespace::oid AS namespace_oid, graphid = namespace::oid AS healthy
FROM ag_catalog.ag_graph;
-- name | graphid | namespace_oid | healthy
-- demo | 5616016 | 5616051 | f

SELECT count(*) FROM demo."P"; -- 1 (data restored fine)

LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT * FROM cypher('demo', $$ MATCH (n:P) RETURN n.name $$) AS (name agtype);
-- ERROR: graph with oid 5616016 does not exist
```

### Expected

Cypher queries work against a restored database, as they do against the original.

### Workaround

The correct oid is already present in the same row, as `namespace::oid`, so the catalog can be repaired without consulting `pg_namespace`. `ag_label` must be updated first (it joins on the old `graphid`), and `fk_graph_oid` is not deferrable, so it has to be dropped for the duration:

```sql
BEGIN;
ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT fk_graph_oid;

UPDATE ag_catalog.ag_label l
SET graph = g.namespace::oid
FROM ag_catalog.ag_graph g
WHERE l.graph = g.graphid
AND g.graphid IS DISTINCT FROM g.namespace::oid;

UPDATE ag_catalog.ag_graph
SET graphid = namespace::oid
WHERE graphid IS DISTINCT FROM namespace::oid;

ALTER TABLE ag_catalog.ag_label
ADD CONSTRAINT fk_graph_oid FOREIGN KEY (graph) REFERENCES ag_catalog.ag_graph (graphid);
COMMIT;
```

Verified on both the minimal case above and a ~1.3 GB production snapshot (2 graphs, 18 label tables, 6188 edges): Cypher works again afterwards and the statements are idempotent.

### Possible fixes

1. **Type `graphid` as `regnamespace`** (and `ag_label.graph` to match), so `pg_dump` emits names and PostgreSQL re-resolves them — the same mechanism that already makes `namespace` and `relation` correct. Requires a catalog migration for existing installs.
2. **Drop the redundant column** and resolve the graph through `namespace` (already `regnamespace`), since the two are meant to hold the same value.
3. **Self-heal on load**, treating `namespace::oid` as authoritative when the two disagree.
4. At minimum, **detect and report**: a mismatch could raise something like `graph "demo" has a stale graphid (restored from a dump?)` instead of an oid that means nothing to the user.

Happy to open a PR for whichever direction maintainers prefer.

Contributor guide

Open the contributing guide

Research direction

Start with the ag_catalog.ag_graph and ag_catalog.ag_label definitions, then trace the cypher() lookup that uses graphid. Reproduce the pg_dump/restore case described in the issue and compare graphid with namespace::oid; done means restored databases retain a consistent catalog and Cypher queries work afterward.

Written by the indexing model from the issue text.

Assessment

Tech stack
c, postgresql
Domain
databases
Issue type
Bug
Difficulty
5/5
Estimated time
Over a week
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.