VLE Query on read-only replica

[nikoraes]

Hi, I'm using Cloudnative PG with readonly replicas and I'm having issues with VLE queries. I'm always getting the following error message on the standby (replica server, even though I'm just doing a match query.

"message":"cannot acquire lock mode ShareLock on database objects while recovery is in progress",
"hint":"Only RowExclusiveLock or less can be acquired on database objects during recovery."

I tested like this:

connect to primary and run the following to create the graph (same as in tests)

SELECT * FROM cypher('cypher_vle', $$CREATE (b:begin)-[:edge {name: 'main edge', number: 1, dangerous: {type: "all", level: "all"}}]->(u1:middle)-[:edge {name: 'main edge', number: 2, dangerous: {type: "all", level: "all"}, packages: [2,4,6]}]->(u2:middle)-[:edge {name: 'main edge', number: 3, dangerous: {type: "all", level: "all"}}]->(u3:middle)-[:edge {name: 'main edge', number: 4, dangerous: {type: "all", level: "all"}}]->(e:end), (u1)-[:self_loop {name: 'self loop', number: 1, dangerous: {type: "all", level: "all"}}]->(u1), (e)-[:self_loop {name: 'self loop', number: 2, dangerous: {type: "all", level: "all"}}]->(e), (b)-[:alternate_edge {name: 'alternate edge', number: 1, packages: [2,4,6], dangerous: {type: "poisons", level: "all"}}]->(u1), (u2)-[:alternate_edge {name: 'alternate edge', number: 2, packages: [2,4,6], dangerous: {type: "poisons", level: "all"}}]->(u3), (u3)-[:alternate_edge {name: 'alternate edge', number: 3, packages: [2,4,6], dangerous: {type: "poisons", level: "all"}}]->(e), (u2)-[:bypass_edge {name: 'bypass edge', number: 1, packages: [1,3,5,7]}]->(e), (e)-[:alternate_edge {name: 'backup edge', number: 1, packages: [1,3,5,7]}]->(u3), (u3)-[:alternate_edge {name: 'backup edge', number: 2, packages: [1,3,5,7]}]->(u2), (u2)-[:bypass_edge {name: 'bypass edge', number: 2, packages: [1,3,5,7], dangerous: {type: "poisons", level: "all"}}]->(b) RETURN b, e $$) AS (b agtype, e agtype);

Then run this query

SELECT * FROM cypher('cypher_vle', $$MATCH (u:begin)-[*]->(v:end) RETURN count(*) $$) AS (e agtype);

This query return 400 on the primary server.

Then I connect to the standby server (replica) and run the same query. This now returns the error about the ShareLock.

The following queries do work

SELECT * FROM cypher('cypher_vle', $$MATCH (u:begin)-[]->(v:middle) RETURN count(*) $$) AS (e agtype); // returns 2
SELECT * FROM cypher('cypher_vle', $$MATCH (u:middle)-[]->(v:end) RETURN count(*) $$) AS (e agtype); // returns 3

Are there any workaround to make VLE queries work on read-only replicas?

[gregfelice]

I've submitted a fix for this in PR #2345.

Root cause: The VLE global graph cache in age_global_graph.c acquires ShareLock when scanning vertex/edge label tables to populate in-memory hashtables. On standby servers in recovery mode, PostgreSQL only allows RowExclusiveLock or lower, so ShareLock is blocked.

Fix: Change ShareLock to AccessShareLock in the three affected functions (get_ag_labels_names, load_vertex_hashtable, load_edge_hashtable). AccessShareLock is sufficient for read-only scans and is what the similar ag_cache.c code already uses for identical operations. All 31 regression tests pass.