0007-v2-Support-for-EXCEPT-list-in-properties.patch
application/octet-stream
Filename: 0007-v2-Support-for-EXCEPT-list-in-properties.patch
Type: application/octet-stream
Part: 6
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
Series: patch v2-0007
| File | + | − |
|---|---|---|
| doc/src/sgml/ref/alter_property_graph.sgml | 1 | 1 |
| doc/src/sgml/ref/create_property_graph.sgml | 5 | 2 |
| src/backend/commands/propgraphcmds.c | 28 | 3 |
| src/backend/parser/gram.y | 10 | 0 |
| src/include/nodes/parsenodes.h | 1 | 0 |
| src/test/regress/expected/create_property_graph.out | 10 | 7 |
| src/test/regress/expected/graph_table.out | 22 | 0 |
| src/test/regress/sql/create_property_graph.sql | 4 | 1 |
| src/test/regress/sql/graph_table.sql | 14 | 0 |
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 <replaceable class="parameter">name</replaceable> DROP
ALTER PROPERTY GRAPH <replaceable class="parameter">name</replaceable> ALTER
{VERTEX|NODE|EDGE|RELATIONSHIP} TABLE <replaceable class="parameter">element_table_alias</replaceable>
- { ADD LABEL <replaceable class="parameter">label_name</replaceable> [ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">property_name</replaceable> ] } [, ...] ) ] } [ ... ]
+ { ADD LABEL <replaceable class="parameter">label_name</replaceable> [ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ALL COLUMNS EXCEPT | PROPERTIES ( { <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">property_name</replaceable> ] } [, ...] ) ] } [ ... ]
ALTER PROPERTY GRAPH <replaceable class="parameter">name</replaceable> ALTER
{VERTEX|NODE|EDGE|RELATIONSHIP} TABLE <replaceable class="parameter">element_table_alias</replaceable>
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 <replaceable class="parameter">name</
<phrase>and <replaceable class="parameter">element_table_label_and_properties</replaceable> is either:</phrase>
- NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">property_name</replaceable> ] } [, ...] )
+ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">property_name</replaceable> ] } [, ...] ) | PROPERTIES ALL COLUMNS EXCEPT( { <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">property_name</replaceable> ] } [, ...] )
<phrase>or:</phrase>
- { { LABEL <replaceable class="parameter">label_name</replaceable> | DEFAULT LABEL } [ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">property_name</replaceable> ] } [, ...] ) ] } [...]
+ { { LABEL <replaceable class="parameter">label_name</replaceable> | DEFAULT LABEL } [ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES ( { <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">property_name</replaceable> ] } [, ...] ) ] } [...] | PROPERTIES ALL COLUMNS EXCEPT( { <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">property_name</replaceable> ] } [, ...] )
</synopsis>
</refsynopsisdiv>
@@ -185,6 +185,9 @@ CREATE [ TEMP | TEMPORARY ] PROPERTY GRAPH <replaceable class="parameter">name</
expressions, which can refer to the columns of the underlying table, can
be specified as properties. If the expressions are not a plain column
reference, then an explicit property name must also be specified.
+ To expose all the visible columns of the graph element table as label properties
+ except those that are explicitly listed use
+ <literal>PROPERTIES ALL COLUMNS EXCEPT( { <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">property_name</replaceable> ] } [, ...] )</literal>
</para>
</listitem>
</varlistentry>
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;