Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Materialized view definitions can't be upgraded or viewed with anonymous path elements #641

Open
jshen-noetic opened this issue Nov 29, 2023 · 0 comments

Comments

@jshen-noetic
Copy link

This is an issue on Agensgraph 2.12.0 and is preventing our team from upgrading to the latest release of Agensgraph. We use materialized views to support our most frequently used queries, and these view queries can return vertex types. When I run pg_upgrade --check to prepare for upgrading to AG 2.13/PG 13, I see the following:

Checking for system-defined composite types in user tables  fatal

Your installation contains system-defined composite type(s) in user tables.
These type OIDs are not stable across PostgreSQL versions,
so this cluster cannot currently be upgraded.  You can
drop the problem columns and restart the upgrade.
A list of the problem columns is in the file:
    tables_using_composite.txt

For materialized views that return vertexes, there is a related error when I try to export our view definitions through Postgres. The view definitions can't be exported with a \d+ command or by inspecting pg_catalog.pg_matviews. Some example output is below.

The key that causes the bug in the below example is the lack of an alias for the married edge in the clause OPTIONAL MATCH ("v")-[:"married" {"year": 1990}]->("gen":"Actor"). If I give that an alias, the view definition is visible, but for optimization purposes in our queries we often don't want the properties on the intermediate elements.

gamma=# 
-- Create a simple graph
CREATE GRAPH trymeout;
SET graph_path=trymeout;

CREATE VLABEL "Actor";
CREATE ELABEL "married";

-- Some data

CREATE (a:"Actor" {name: 'Tom Cruise'});
MATCH (a:"Actor" {name: 'Tom Cruise'}) CREATE (a)-[:married {"year": 1990}]->(:"Actor" {name: 'Nicole Kidman'});
MATCH (a:"Actor" {name: 'Tom Cruise'}) CREATE (a)-[:married {"year": 2006}]->(:"Actor" {name: 'Katie Holmes'});

-- The problematic view definition
CREATE MATERIALIZED VIEW read_me AS (
        SELECT * FROM (
                MATCH ("v":"Actor" ONLY)
                OPTIONAL MATCH ("v")-[:"married" {"year": 1990}]->("gen":"Actor")
                RETURN "v".name AS "v", "gen".name AS gen
                )
        AS _) WITH DATA;

-- The view exists with data
SELECT * from read_me;

-- The PG metadata exists
SELECT schemaname, matviewname from pg_matviews WHERE matviewname='read_me';

-- But the view definition can't be printed out
SELECT schemaname, matviewname, definition from pg_matviews WHERE matviewname='read_me';

Output:

CREATE GRAPH
SET
CREATE VLABEL
CREATE ELABEL
UPDATE 1
UPDATE 2
UPDATE 2
SELECT 3
        v        |       gen
-----------------+-----------------
 "Tom Cruise"    | "Nicole Kidman"
 "Nicole Kidman" |
 "Katie Holmes"  |
(3 rows)

 schemaname | matviewname
------------+-------------
 graph      | read_me
(1 row)

ERROR:  invalid attnum 3 for relation "_"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant