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

["themiguelamador (via GitHub)" <gi...@apache.org>]

themiguelamador opened a new issue, #2503:
URL: https://github.com/apache/age/issues/2503

   ### 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.
   


-- 
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