diff --git a/doc/src/sgml/ref/alter_property_graph.sgml b/doc/src/sgml/ref/alter_property_graph.sgml index 604c518011..0a20b482b8 100644 --- a/doc/src/sgml/ref/alter_property_graph.sgml +++ b/doc/src/sgml/ref/alter_property_graph.sgml @@ -33,7 +33,7 @@ ALTER PROPERTY GRAPH name DROP ALTER PROPERTY GRAPH name ALTER {VERTEX|NODE|EDGE|RELATIONSHIP} TABLE element_table_alias - { ADD LABEL label_name [ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { expression [ AS property_name ] } [, ...] ) ] } [ ... ] + { ADD LABEL label_name [ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ALL COLUMNS EXCEPT | PROPERTIES ( { expression [ AS property_name ] } [, ...] ) ] } [ ... ] ALTER PROPERTY GRAPH name ALTER {VERTEX|NODE|EDGE|RELATIONSHIP} TABLE element_table_alias diff --git a/doc/src/sgml/ref/create_property_graph.sgml b/doc/src/sgml/ref/create_property_graph.sgml index f88d1194cb..36b710cf7b 100644 --- a/doc/src/sgml/ref/create_property_graph.sgml +++ b/doc/src/sgml/ref/create_property_graph.sgml @@ -38,11 +38,11 @@ CREATE [ TEMP | TEMPORARY ] PROPERTY GRAPH nameand element_table_label_and_properties is either: - NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { expression [ AS property_name ] } [, ...] ) + NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { expression [ AS property_name ] } [, ...] ) | PROPERTIES ALL COLUMNS EXCEPT( { expression [ AS property_name ] } [, ...] ) or: - { { LABEL label_name | DEFAULT LABEL } [ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { expression [ AS property_name ] } [, ...] ) ] } [...] + { { LABEL label_name | DEFAULT LABEL } [ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { expression [ AS property_name ] } [, ...] ) ] } [...] | PROPERTIES ALL COLUMNS EXCEPT( { expression [ AS property_name ] } [, ...] ) @@ -185,6 +185,9 @@ CREATE [ TEMP | TEMPORARY ] PROPERTY GRAPH namePROPERTIES ALL COLUMNS EXCEPT( { expression [ AS property_name ] } [, ...] ) diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c index 6b15749c5e..db863d0e8d 100644 --- a/src/backend/commands/propgraphcmds.c +++ b/src/backend/commands/propgraphcmds.c @@ -700,6 +700,8 @@ insert_property_records(Oid graphid, Oid ellabeloid, Oid pgerelid, const PropGra Relation rel; ListCell *lc; + pstate = make_parsestate(NULL); + if (properties->all) { Relation attRelation; @@ -707,6 +709,29 @@ insert_property_records(Oid graphid, Oid ellabeloid, Oid pgerelid, const PropGra ScanKeyData key[1]; HeapTuple attributeTuple; + /* Check if exceptlist cols are valid before proceeding */ + if (properties->except) + { + ColumnRef *cr; + ResTarget *rt; + + char *cname = NULL; + + foreach(lc, properties->except) + { + rt = lfirst_node(ResTarget, lc); + cr = (ColumnRef *) rt->val; + cname = strVal(linitial(cr->fields)); + + if (!get_attnum(pgerelid, cname)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + cname, get_rel_name(pgerelid)), + parser_errposition(pstate, rt->location))); + } + } + attRelation = table_open(AttributeRelationId, RowShareLock); ScanKeyInit(&key[0], Anum_pg_attribute_attrelid, @@ -729,11 +754,12 @@ insert_property_records(Oid graphid, Oid ellabeloid, Oid pgerelid, const PropGra cr->fields = list_make1(makeString(pstrdup(NameStr(att->attname)))); cr->location = -1; - rt->name = pstrdup(NameStr(att->attname)); + rt->name = NULL; rt->val = (Node *) cr; rt->location = -1; - proplist = lappend(proplist, rt); + if (!list_member(properties->except, rt)) + proplist = lappend(proplist, rt); } systable_endscan(scan); table_close(attRelation, RowShareLock); @@ -756,7 +782,6 @@ insert_property_records(Oid graphid, Oid ellabeloid, Oid pgerelid, const PropGra rel = table_open(pgerelid, AccessShareLock); - pstate = make_parsestate(NULL); nsitem = addRangeTableEntryForRelation(pstate, rel, AccessShareLock, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 03016742cb..8bd5fd16af 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -9383,6 +9383,16 @@ element_table_properties: pr->all = true; pr->location = @1; + $$ = (Node *) pr; + } + | PROPERTIES ALL COLUMNS EXCEPT '(' xml_attribute_list ')' + { + PropGraphProperties *pr = makeNode(PropGraphProperties); + + pr->all = true; + pr->except = $6; + pr->location = @1; + $$ = (Node *) pr; } | PROPERTIES '(' xml_attribute_list ')' diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index c4223fda57..db5243cb6a 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -4119,6 +4119,7 @@ typedef struct PropGraphProperties { NodeTag type; List *properties; + List *except; bool all; ParseLoc location; } PropGraphProperties; diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out index 43316fbc02..f5d648ef5d 100644 --- a/src/test/regress/expected/create_property_graph.out +++ b/src/test/regress/expected/create_property_graph.out @@ -73,10 +73,12 @@ CREATE PROPERTY GRAPH g4 e2 KEY (a, x) SOURCE KEY (a) REFERENCES t1 (a) DESTINATION KEY (x, t) REFERENCES t3 (x, y) - PROPERTIES ALL COLUMNS + PROPERTIES ALL COLUMNS EXCEPT (t) ); ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k * 2 AS kk); ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (k); +ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ADD LABEL t2l1 PROPERTIES ALL COLUMNS EXCEPT (i, j); +ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 DROP LABEL t2l1; CREATE TABLE t11 (a int PRIMARY KEY); CREATE TABLE t12 (b int PRIMARY KEY); CREATE TABLE t13 ( @@ -181,6 +183,8 @@ ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t1 ADD LABEL t3l1 PROPERTIES (a AS x, ERROR: mismatching properties names in definition of label "t3l1" ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t1 ADD LABEL t3l1 PROPERTIES (a AS x); -- mismatching number of properties on label ERROR: mismatching number of properties in definition of label "t3l1" +ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t1 ADD LABEL t3l1 PROPERTIES ALL COLUMNS EXCEPT (abc); -- invalid col name +ERROR: column "abc" of relation "t1" does not exist ALTER PROPERTY GRAPH g1 OWNER TO regress_graph_user1; SET ROLE regress_graph_user1; GRANT SELECT ON PROPERTY GRAPH g1 TO regress_graph_user2; @@ -308,7 +312,6 @@ SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_g regression | create_property_graph_tests | g4 | e1 | i | i regression | create_property_graph_tests | g4 | e1 | t | t regression | create_property_graph_tests | g4 | e2 | a | a - regression | create_property_graph_tests | g4 | e2 | t | t regression | create_property_graph_tests | g4 | e2 | x | x regression | create_property_graph_tests | g4 | t2 | i_j | (i + j) regression | create_property_graph_tests | g4 | t2 | kk | (k * 2) @@ -320,7 +323,7 @@ SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_g regression | create_property_graph_tests | g5 | t13 | c | c regression | create_property_graph_tests | g5 | t13 | d | d regression | create_property_graph_tests | g5 | t13 | e | e -(35 rows) +(34 rows) SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_name, label_name, property_name; property_graph_catalog | property_graph_schema | property_graph_name | label_name | property_name @@ -354,7 +357,6 @@ SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_nam regression | create_property_graph_tests | g4 | e1 | i regression | create_property_graph_tests | g4 | e1 | t regression | create_property_graph_tests | g4 | e2 | a - regression | create_property_graph_tests | g4 | e2 | t regression | create_property_graph_tests | g4 | e2 | x regression | create_property_graph_tests | g4 | t2 | i_j regression | create_property_graph_tests | g4 | t2 | kk @@ -367,7 +369,7 @@ SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_nam regression | create_property_graph_tests | g5 | t13 | c regression | create_property_graph_tests | g5 | t13 | d regression | create_property_graph_tests | g5 | t13 | e -(42 rows) +(41 rows) SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_name; property_graph_catalog | property_graph_schema | property_graph_name | label_name @@ -412,6 +414,7 @@ SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_ regression | create_property_graph_tests | g4 | a | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | a regression | create_property_graph_tests | g4 | i | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | i regression | create_property_graph_tests | g4 | i_j | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | i_j + regression | create_property_graph_tests | g4 | k | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | k regression | create_property_graph_tests | g4 | kk | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | kk regression | create_property_graph_tests | g4 | t | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | t regression | create_property_graph_tests | g4 | x | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | x @@ -422,7 +425,7 @@ SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_ regression | create_property_graph_tests | g5 | c | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | c regression | create_property_graph_tests | g5 | d | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | d regression | create_property_graph_tests | g5 | e | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | e -(27 rows) +(28 rows) SELECT * FROM information_schema.pg_property_graph_privileges WHERE grantee LIKE 'regress%' ORDER BY property_graph_name; grantor | grantee | property_graph_catalog | property_graph_schema | property_graph_name | privilege_type | is_grantable @@ -458,7 +461,7 @@ CREATE PROPERTY GRAPH create_property_graph_tests.g4 ) EDGE TABLES ( e1 KEY (a, i) SOURCE KEY (a) REFERENCES t1 (a) DESTINATION KEY (i) REFERENCES t2 (i) PROPERTIES (a, i, t), - e2 KEY (a, x) SOURCE KEY (a) REFERENCES t1 (a) DESTINATION KEY (x, t) REFERENCES t3 (x, y) PROPERTIES (a, t, x) + e2 KEY (a, x) SOURCE KEY (a) REFERENCES t1 (a) DESTINATION KEY (x, t) REFERENCES t3 (x, y) PROPERTIES (a, x) ) SELECT pg_get_propgraphdef('pg_type'::regclass); -- error ERROR: "pg_type" is not a property graph diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out index 87ab3e31af..a9d6ba8b57 100644 --- a/src/test/regress/expected/graph_table.out +++ b/src/test/regress/expected/graph_table.out @@ -604,5 +604,27 @@ SELECT * FROM customers_us_redacted; redacted1 (1 row) +-- test except column name list +CREATE TABLE t1 (a int, b text); +CREATE TABLE t2 (i int PRIMARY KEY, j int, k int); +CREATE PROPERTY GRAPH except_list_test + VERTEX TABLES (t1 KEY (a), t2 KEY (i) LABEL l1 PROPERTIES ALL COLUMNS EXCEPT (j, k)); +SELECT * FROM GRAPH_TABLE(except_list_test MATCH (p:l1) COLUMNS(p.i)); + i +--- +(0 rows) + +SELECT * FROM GRAPH_TABLE(except_list_test MATCH (p:l1) COLUMNS(p.j)); -- error: does not exist +ERROR: property "j" does not exist +SELECT * FROM GRAPH_TABLE(except_list_test MATCH (p:l1) COLUMNS(p.k)); -- error: does not exist +ERROR: property "k" does not exist +SELECT * FROM information_schema.pg_label_properties WHERE property_graph_name = 'except_list_test'; + property_graph_catalog | property_graph_schema | property_graph_name | label_name | property_name +------------------------+-----------------------+---------------------+------------+--------------- + regression | graph_table_tests | except_list_test | l1 | i + regression | graph_table_tests | except_list_test | t1 | a + regression | graph_table_tests | except_list_test | t1 | b +(3 rows) + -- leave for pg_upgrade/pg_dump tests --DROP SCHEMA graph_table_tests CASCADE; diff --git a/src/test/regress/sql/create_property_graph.sql b/src/test/regress/sql/create_property_graph.sql index 4f9b5c0349..875b2b15c7 100644 --- a/src/test/regress/sql/create_property_graph.sql +++ b/src/test/regress/sql/create_property_graph.sql @@ -65,11 +65,13 @@ CREATE PROPERTY GRAPH g4 e2 KEY (a, x) SOURCE KEY (a) REFERENCES t1 (a) DESTINATION KEY (x, t) REFERENCES t3 (x, y) - PROPERTIES ALL COLUMNS + PROPERTIES ALL COLUMNS EXCEPT (t) ); ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 ADD PROPERTIES (k * 2 AS kk); ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ALTER LABEL t2 DROP PROPERTIES (k); +ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 ADD LABEL t2l1 PROPERTIES ALL COLUMNS EXCEPT (i, j); +ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t2 DROP LABEL t2l1; CREATE TABLE t11 (a int PRIMARY KEY); CREATE TABLE t12 (b int PRIMARY KEY); @@ -139,6 +141,7 @@ CREATE PROPERTY GRAPH gx ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t1 ADD LABEL t3l1 PROPERTIES (a AS x, b AS yy, b AS zz); -- mismatching number of properties on label ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t1 ADD LABEL t3l1 PROPERTIES (a AS x, b AS zz); -- mismatching property names on label ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t1 ADD LABEL t3l1 PROPERTIES (a AS x); -- mismatching number of properties on label +ALTER PROPERTY GRAPH g4 ALTER VERTEX TABLE t1 ADD LABEL t3l1 PROPERTIES ALL COLUMNS EXCEPT (abc); -- invalid col name ALTER PROPERTY GRAPH g1 OWNER TO regress_graph_user1; diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql index f34616163a..425bee3ce1 100644 --- a/src/test/regress/sql/graph_table.sql +++ b/src/test/regress/sql/graph_table.sql @@ -394,5 +394,19 @@ CREATE VIEW customers_us_redacted AS SELECT * FROM GRAPH_TABLE (myshop2 MATCH (c SELECT * FROM customers_us_redacted; +-- test except column name list + +CREATE TABLE t1 (a int, b text); +CREATE TABLE t2 (i int PRIMARY KEY, j int, k int); + +CREATE PROPERTY GRAPH except_list_test + VERTEX TABLES (t1 KEY (a), t2 KEY (i) LABEL l1 PROPERTIES ALL COLUMNS EXCEPT (j, k)); + +SELECT * FROM GRAPH_TABLE(except_list_test MATCH (p:l1) COLUMNS(p.i)); +SELECT * FROM GRAPH_TABLE(except_list_test MATCH (p:l1) COLUMNS(p.j)); -- error: does not exist +SELECT * FROM GRAPH_TABLE(except_list_test MATCH (p:l1) COLUMNS(p.k)); -- error: does not exist + +SELECT * FROM information_schema.pg_label_properties WHERE property_graph_name = 'except_list_test'; + -- leave for pg_upgrade/pg_dump tests --DROP SCHEMA graph_table_tests CASCADE;