Cypher fails after pg_dump/restore: ag_graph.graphid is a bare oid and keeps the source database's value
- 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
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