v14-0006-Property-collation-and-edge-vertex-link-support.patch
application/octet-stream
Filename: v14-0006-Property-collation-and-edge-vertex-link-support.patch
Type: application/octet-stream
Part: 9
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: format-patch
Series: patch v14-0006
Subject: Property collation and edge-vertex link support
| File | + | − |
|---|---|---|
| doc/src/sgml/catalogs.sgml | 49 | 0 |
| src/backend/catalog/information_schema.sql | 5 | 3 |
| src/backend/commands/propgraphcmds.c | 200 | 22 |
| src/backend/nodes/nodeFuncs.c | 2 | 3 |
| src/backend/parser/parse_collate.c | 1 | 7 |
| src/backend/parser/parse_graphtable.c | 11 | 5 |
| src/backend/rewrite/rewriteGraphTable.c | 101 | 21 |
| src/include/catalog/pg_propgraph_element.h | 11 | 0 |
| src/include/catalog/pg_propgraph_property.h | 6 | 0 |
| src/include/nodes/primnodes.h | 2 | 0 |
| src/test/regress/expected/create_property_graph.out | 221 | 40 |
| src/test/regress/expected/graph_table.out | 98 | 13 |
| src/test/regress/expected/oidjoins.out | 1 | 0 |
| src/test/regress/sql/create_property_graph.sql | 77 | 0 |
| src/test/regress/sql/graph_table.sql | 31 | 10 |
From 9415de892ec88fb3eee0dc0a05346530ae775921 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Mon, 28 Oct 2024 16:36:27 +0530
Subject: [PATCH v14 06/15] Property collation and edge-vertex link support
The commit has following changes.
1. collation of a property
--------------------------
The values with differing collations can not be collated. This means
that we will not able to compare, sort, or order values of properties
with the same name if they have different collations in different
elements or different labels. This restricts the property's usage in
graph table query. Hence the collation of all the properties with the
same name needs to be the same. Enforce this.
Often the collation of a property reference is required before
graph_table is rewritten. For example, assign_query_collation() is
called during transformSelectStmt() which is called before calling
rewriteGraphTable(). Hence the collation of a property needs to be
accessible before any property reference is accessed. Hence the
collation of a property is stored in pg_propgraph_property.
Note: This is not explicitly specified in Section 9.15, "Consistency
check of a tabular property graph descriptor", syntax rule 4.c.iii.2 of
SQL/PGQ standard.
2. collation of source and destination keys of an edge
------------------------------------------------------
If collations of edge key and the corresponding referenced key differ,
an edge may end up being adjacent to undesired vertex. Prohibit such a
case when key columns are explicitly specified.
When edge and vertex keys are derived from the foreign key constraint,
we do not check collations again since the constraint itself ensures
one-to-many edge-to-vertex mapping.
Note: This is not specified in Section 9.14, "Creation of an edge table
descriptor".
3. Edge-vertex link quals
-----------------------------------------
When creating an edge element make sure that there exists an equality
operators that can be used to match vertex and edge keys. Fail the DDL if
such an operator does not exist. Use the same equality operators to build
vertex-edge quals in build_edge_vertex_link_quals(). Also add a
dependency of the edge on the equality operator so that it can not be
dropped without dropping the edge.
Note for reviewers:
The code in ATAddForeignKeyConstraint() looks up three different
equality operators and it's difficult to separate the code looking up
just PK=FK operators. So could not move some common code to a function
which can be used at both places.
4. Tests
--------
Adds tests for above items.
The collation tests, test that
a. the collation is correctly set for the columns projected by
GRAPH_TABLE clause.
b. quals corresponding to the various WHERE clause in GRAPH_TABLE have
correct collation set
c. quals corresponding to the edge-vertex links have correct collation
set.
Note: These tests combined with tests in collate.sql and other collation
specific tests indicate that we have covered all scenarios testing
collations in the context of GRAPH_TABLE. More collation specific tests
may be added as required.
Author: Ashutosh Bapat
---
doc/src/sgml/catalogs.sgml | 49 ++++
src/backend/catalog/information_schema.sql | 8 +-
src/backend/commands/propgraphcmds.c | 222 +++++++++++++--
src/backend/nodes/nodeFuncs.c | 5 +-
src/backend/parser/parse_collate.c | 8 +-
src/backend/parser/parse_graphtable.c | 16 +-
src/backend/rewrite/rewriteGraphTable.c | 122 ++++++--
src/include/catalog/pg_propgraph_element.h | 11 +
src/include/catalog/pg_propgraph_property.h | 6 +
src/include/nodes/primnodes.h | 2 +
.../expected/create_property_graph.out | 261 +++++++++++++++---
src/test/regress/expected/graph_table.out | 111 +++++++-
src/test/regress/expected/oidjoins.out | 1 +
.../regress/sql/create_property_graph.sql | 77 ++++++
src/test/regress/sql/graph_table.sql | 41 ++-
15 files changed, 816 insertions(+), 124 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index a7cd262942e..4f62ca00502 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6476,6 +6476,18 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pgesrceqop</structfield> <type>oid[]</type>
+ (references <link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ For an edge, an array of equality operators for
+ <structfield>pgesrcref</structfield> =
+ <structfield>pgesrckey</structfield> comparison. (Null for a vertex.)
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>pgedestkey</structfield> <type>int2[]</type>
@@ -6504,6 +6516,18 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
and the destination vertex.
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pgedesteqop</structfield> <type>oid[]</type>
+ (references <link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ For an edge, an array of equality operators for
+ <structfield>pgedestref</structfield> =
+ <structfield>pgedestkey</structfield> comparison. (Null for a vertex.)
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -6775,6 +6799,31 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
multiple times in different elements and labels.)
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pgptypmod</structfield> <type>int4</type>
+ </para>
+ <para>
+ <literal>typmod</literal> to be applied to the data type of this property.
+ (This is required to be fixed for a given property in a property graph,
+ even if the property is defined multiple times in different elements and
+ labels.)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pgpcollation</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The defined collation of this property, or zero if the property is not of
+ a collatable data type. (This is required to be fixed for a given
+ property in a property graph, even if the property is defined multiple
+ times in different elements and labels.)
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 279388bdf75..9b793a0cadf 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -3271,9 +3271,9 @@ CREATE VIEW pg_property_data_types AS
CAST(null AS sql_identifier) AS character_set_catalog,
CAST(null AS sql_identifier) AS character_set_schema,
CAST(null AS sql_identifier) AS character_set_name,
- CAST(null AS sql_identifier) AS collation_catalog, -- FIXME
- CAST(null AS sql_identifier) AS collation_schema, -- FIXME
- CAST(null AS sql_identifier) AS collation_name, -- FIXME
+ CAST(current_database() AS sql_identifier) AS collation_catalog,
+ CAST(nc.nspname AS sql_identifier) AS collation_schema,
+ CAST(c.collname AS sql_identifier) AS collation_name,
CAST(null AS cardinal_number) AS numeric_precision,
CAST(null AS cardinal_number) AS numeric_precision_radix,
CAST(null AS cardinal_number) AS numeric_scale,
@@ -3297,6 +3297,8 @@ CREATE VIEW pg_property_data_types AS
JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON pgp.pgptypid = t.oid
LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
+ LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid))
+ ON pgp.pgpcollation = c.oid AND (nc.nspname, c.collname) <> ('pg_catalog', 'default')
WHERE pg.relkind = 'g'
AND (NOT pg_is_other_temp_schema(npg.oid))
diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c
index bf333dd2dc2..c26976b2a2a 100644
--- a/src/backend/commands/propgraphcmds.c
+++ b/src/backend/commands/propgraphcmds.c
@@ -14,20 +14,27 @@
#include "access/genam.h"
#include "access/htup_details.h"
+#include "access/nbtree.h"
#include "access/table.h"
#include "access/xact.h"
#include "catalog/catalog.h"
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_class.h"
+#include "catalog/pg_collation_d.h"
+#include "catalog/pg_operator_d.h"
#include "catalog/pg_propgraph_element.h"
#include "catalog/pg_propgraph_element_label.h"
#include "catalog/pg_propgraph_label.h"
#include "catalog/pg_propgraph_label_property.h"
#include "catalog/pg_propgraph_property.h"
+#include "commands/defrem.h"
#include "commands/propgraphcmds.h"
#include "commands/tablecmds.h"
#include "nodes/nodeFuncs.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_collate.h"
+#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
#include "parser/parse_target.h"
#include "utils/array.h"
@@ -52,12 +59,14 @@ struct element_info
Oid srcrelid;
ArrayType *srckey;
ArrayType *srcref;
+ ArrayType *srceqop;
char *destvertex;
Oid destvertexid;
Oid destrelid;
ArrayType *destkey;
ArrayType *destref;
+ ArrayType *desteqop;
List *labels;
};
@@ -68,8 +77,8 @@ static ArrayType *propgraph_element_get_key(ParseState *pstate, const List *keyc
static void propgraph_edge_get_ref_keys(ParseState *pstate, const List *keycols, const List *refcols,
Relation edge_rel, Relation ref_rel,
const char *aliasname, int location, const char *type,
- ArrayType **outkey, ArrayType **outref);
-static ArrayType *array_from_column_list(ParseState *pstate, const List *colnames, int location, Relation element_rel);
+ ArrayType **outkey, ArrayType **outref, ArrayType **outeqop);
+static AttrNumber *array_from_column_list(ParseState *pstate, const List *colnames, int location, Relation element_rel);
static ArrayType *array_from_attnums(int numattrs, const AttrNumber *attnums);
static Oid insert_element_record(ObjectAddress pgaddress, struct element_info *einfo);
static Oid insert_label_record(Oid graphid, Oid peoid, const char *label);
@@ -217,10 +226,10 @@ CreatePropGraph(ParseState *pstate, const CreatePropGraphStmt *stmt)
propgraph_edge_get_ref_keys(pstate, edge->esrckey, edge->esrcvertexcols, rel, srcrel,
einfo->aliasname, edge->location, "SOURCE",
- &einfo->srckey, &einfo->srcref);
+ &einfo->srckey, &einfo->srcref, &einfo->srceqop);
propgraph_edge_get_ref_keys(pstate, edge->edestkey, edge->edestvertexcols, rel, destrel,
einfo->aliasname, edge->location, "DESTINATION",
- &einfo->destkey, &einfo->destref);
+ &einfo->destkey, &einfo->destref, &einfo->desteqop);
einfo->labels = edge->labels;
@@ -336,7 +345,8 @@ propgraph_element_get_key(ParseState *pstate, const List *key_clause, Relation e
}
else
{
- a = array_from_column_list(pstate, key_clause, location, element_rel);
+ a = array_from_attnums(list_length(key_clause),
+ array_from_column_list(pstate, key_clause, location, element_rel));
}
return a;
@@ -360,8 +370,15 @@ static void
propgraph_edge_get_ref_keys(ParseState *pstate, const List *keycols, const List *refcols,
Relation edge_rel, Relation ref_rel,
const char *aliasname, int location, const char *type,
- ArrayType **outkey, ArrayType **outref)
+ ArrayType **outkey, ArrayType **outref, ArrayType **outeqop)
{
+ int nkeys;
+ AttrNumber *keyattnums;
+ AttrNumber *refattnums;
+ Oid *keyeqops;
+ Datum *datums;
+ int i;
+
Assert((keycols && refcols) || (!keycols && !refcols));
if (keycols)
@@ -372,8 +389,89 @@ propgraph_edge_get_ref_keys(ParseState *pstate, const List *keycols, const List
errmsg("mismatching number of columns in %s vertex definition of edge \"%s\"", type, aliasname),
parser_errposition(pstate, location));
- *outkey = array_from_column_list(pstate, keycols, location, edge_rel);
- *outref = array_from_column_list(pstate, refcols, location, ref_rel);
+ nkeys = list_length(keycols);
+ keyattnums = array_from_column_list(pstate, keycols, location, edge_rel);
+ refattnums = array_from_column_list(pstate, refcols, location, ref_rel);
+ keyeqops = palloc_array(Oid, nkeys);
+
+ for (i = 0; i < nkeys; i++)
+ {
+ Oid keytype;
+ int32 keytypmod;
+ Oid keycoll;
+ Oid reftype;
+ int32 reftypmod;
+ Oid refcoll;
+ Oid opc;
+ Oid opf;
+ StrategyNumber strategy;
+
+ /*
+ * Lookup equality operator to be used for edge and vertex key.
+ * Vertex key is equivalent to primary key and edge key is similar
+ * to foreign key since edge key references vertex key. Hence
+ * vertex key is used as left operand and edge key is used as
+ * right operand. The method used to find the equality operators
+ * is similar to the method used to find equality operators for
+ * FK/PK comparison in ATAddForeignKeyConstraint() except that
+ * opclass of the the vertex key type is used as a starting point.
+ * Since we need only equality operators we use both BT and HASH
+ * strategies.
+ *
+ * If the required operators do not exist, we can not construct
+ * quals linking an edge to its adjacent vertexes.
+ */
+ get_atttypetypmodcoll(RelationGetRelid(edge_rel), keyattnums[i], &keytype, &keytypmod, &keycoll);
+ get_atttypetypmodcoll(RelationGetRelid(ref_rel), refattnums[i], &reftype, &reftypmod, &refcoll);
+ keyeqops[i] = InvalidOid;
+ strategy = BTEqualStrategyNumber;
+ opc = GetDefaultOpClass(reftype, BTREE_AM_OID);
+ if (!OidIsValid(opc))
+ {
+ opc = GetDefaultOpClass(reftype, HASH_AM_OID);
+ strategy = HTEqualStrategyNumber;
+ }
+ if (OidIsValid(opc))
+ {
+ opf = get_opclass_family(opc);
+ if (OidIsValid(opf))
+ {
+ keyeqops[i] = get_opfamily_member(opf, reftype, keytype, strategy);
+ if (!OidIsValid(keyeqops[i]))
+ {
+ /* Last resort, implicit cast. */
+ if (can_coerce_type(1, &keytype, &reftype, COERCION_IMPLICIT))
+ keyeqops[i] = get_opfamily_member(opf, reftype, reftype, strategy);
+ }
+ }
+ }
+
+ if (!OidIsValid(keyeqops[i]))
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("no equality operator exists for %s key comparison of edge \"%s\"",
+ type, aliasname),
+ parser_errposition(pstate, location));
+
+ /*
+ * If collations of key attribute and referenced attribute are
+ * different, an edge may end up being adjacent to undesired
+ * vertexes. Prohibit such a case.
+ *
+ * PK/FK allows different collations as long as they are
+ * deterministic for backward compatibility. But we can be a bit
+ * stricter here and follow SQL standard.
+ */
+ if (keycoll != refcoll &&
+ keycoll != DEFAULT_COLLATION_OID && refcoll != DEFAULT_COLLATION_OID &&
+ OidIsValid(keycoll) && OidIsValid(refcoll))
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("collation mismatch in %s key of edge \"%s\": %s vs. %s",
+ type, aliasname,
+ get_collation_name(keycoll), get_collation_name(refcoll)),
+ parser_errposition(pstate, location));
+ }
}
else
{
@@ -402,25 +500,34 @@ propgraph_edge_get_ref_keys(ParseState *pstate, const List *keycols, const List
Assert(fk);
- *outkey = array_from_attnums(fk->nkeys, fk->conkey);
- *outref = array_from_attnums(fk->nkeys, fk->confkey);
+ nkeys = fk->nkeys;
+ keyattnums = fk->conkey;
+ refattnums = fk->confkey;
+ keyeqops = fk->conpfeqop;
}
+
+ *outkey = array_from_attnums(nkeys, keyattnums);
+ *outref = array_from_attnums(nkeys, refattnums);
+ datums = (Datum *) palloc(sizeof(Datum) * nkeys);
+ for (i = 0; i < nkeys; i++)
+ datums[i] = ObjectIdGetDatum(keyeqops[i]);
+ *outeqop = construct_array_builtin(datums, nkeys, OIDOID);
}
/*
* Convert list of column names in the specified relation into an array of
* column numbers.
*/
-static ArrayType *
+static AttrNumber *
array_from_column_list(ParseState *pstate, const List *colnames, int location, Relation element_rel)
{
int numattrs;
- Datum *attnumsd;
+ AttrNumber *attnums;
int i;
ListCell *lc;
numattrs = list_length(colnames);
- attnumsd = palloc_array(Datum, numattrs);
+ attnums = palloc_array(AttrNumber, numattrs);
i = 0;
foreach(lc, colnames)
@@ -436,14 +543,14 @@ array_from_column_list(ParseState *pstate, const List *colnames, int location, R
errmsg("column \"%s\" of relation \"%s\" does not exist",
colname, get_rel_name(relid)),
parser_errposition(pstate, location)));
- attnumsd[i++] = Int16GetDatum(attnum);
+ attnums[i++] = attnum;
}
for (int j = 0; j < numattrs; j++)
{
for (int k = j + 1; k < numattrs; k++)
{
- if (DatumGetInt16(attnumsd[j]) == DatumGetInt16(attnumsd[k]))
+ if (attnums[j] == attnums[k])
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("graph key columns list must not contain duplicates"),
@@ -451,7 +558,7 @@ array_from_column_list(ParseState *pstate, const List *colnames, int location, R
}
}
- return construct_array_builtin(attnumsd, numattrs, INT2OID);
+ return attnums;
}
static ArrayType *
@@ -484,6 +591,23 @@ array_of_attnums_to_objectaddrs(Oid relid, ArrayType *arr, ObjectAddresses *addr
}
}
+static void
+array_of_opers_to_objectaddrs(ArrayType *arr, ObjectAddresses *addrs)
+{
+ Datum *opersd;
+ int numopers;
+
+ deconstruct_array_builtin(arr, OIDOID, &opersd, NULL, &numopers);
+
+ for (int i = 0; i < numopers; i++)
+ {
+ ObjectAddress referenced;
+
+ ObjectAddressSet(referenced, OperatorRelationId, DatumGetObjectId(opersd[i]));
+ add_exact_object_address(&referenced, addrs);
+ }
+}
+
/*
* Insert a record for an element into the pg_propgraph_element catalog. Also
* inserts labels and properties into their respective catalogs.
@@ -524,6 +648,10 @@ insert_element_record(ObjectAddress pgaddress, struct element_info *einfo)
values[Anum_pg_propgraph_element_pgesrcref - 1] = PointerGetDatum(einfo->srcref);
else
nulls[Anum_pg_propgraph_element_pgesrcref - 1] = true;
+ if (einfo->srceqop)
+ values[Anum_pg_propgraph_element_pgesrceqop - 1] = PointerGetDatum(einfo->srceqop);
+ else
+ nulls[Anum_pg_propgraph_element_pgesrceqop - 1] = true;
if (einfo->destkey)
values[Anum_pg_propgraph_element_pgedestkey - 1] = PointerGetDatum(einfo->destkey);
else
@@ -532,6 +660,10 @@ insert_element_record(ObjectAddress pgaddress, struct element_info *einfo)
values[Anum_pg_propgraph_element_pgedestref - 1] = PointerGetDatum(einfo->destref);
else
nulls[Anum_pg_propgraph_element_pgedestref - 1] = true;
+ if (einfo->desteqop)
+ values[Anum_pg_propgraph_element_pgedesteqop - 1] = PointerGetDatum(einfo->desteqop);
+ else
+ nulls[Anum_pg_propgraph_element_pgedesteqop - 1] = true;
tup = heap_form_tuple(RelationGetDescr(rel), values, nulls);
CatalogTupleInsert(rel, tup);
@@ -549,13 +681,17 @@ insert_element_record(ObjectAddress pgaddress, struct element_info *einfo)
add_exact_object_address(&referenced, addrs);
array_of_attnums_to_objectaddrs(einfo->relid, einfo->key, addrs);
- /* Add dependencies on vertices */
+ /*
+ * Add dependencies on vertices and equality operators used for key
+ * comparison.
+ */
if (einfo->srcvertexid)
{
ObjectAddressSet(referenced, PropgraphElementRelationId, einfo->srcvertexid);
add_exact_object_address(&referenced, addrs);
array_of_attnums_to_objectaddrs(einfo->relid, einfo->srckey, addrs);
array_of_attnums_to_objectaddrs(einfo->srcrelid, einfo->srcref, addrs);
+ array_of_opers_to_objectaddrs(einfo->srceqop, addrs);
}
if (einfo->destvertexid)
{
@@ -563,10 +699,9 @@ insert_element_record(ObjectAddress pgaddress, struct element_info *einfo)
add_exact_object_address(&referenced, addrs);
array_of_attnums_to_objectaddrs(einfo->relid, einfo->destkey, addrs);
array_of_attnums_to_objectaddrs(einfo->destrelid, einfo->destref, addrs);
+ array_of_opers_to_objectaddrs(einfo->desteqop, addrs);
}
- /* TODO: dependencies on equality operators, like for foreign keys */
-
record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL);
table_close(rel, NoLock);
@@ -766,6 +901,7 @@ insert_property_records(Oid graphid, Oid ellabeloid, Oid pgerelid, const PropGra
table_close(rel, NoLock);
tp = transformTargetList(pstate, proplist, EXPR_KIND_PROPGRAPH_PROPERTY);
+ assign_expr_collations(pstate, (Node *) tp);
foreach(lc, tp)
{
@@ -784,9 +920,15 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
{
Oid propoid;
Oid exprtypid;
+ int32 exprtypmod;
+ Oid exprcollation;
Oid proptypid;
+ int32 proptypmod;
+ Oid propcollation;
exprtypid = exprType((const Node *) expr);
+ exprcollation = exprCollation((const Node *) expr);
+ exprtypmod = exprTypmod((const Node *) expr);
/*
* Insert into pg_propgraph_property if not already existing.
@@ -803,6 +945,8 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
ObjectAddress referenced;
proptypid = exprtypid;
+ proptypmod = exprtypmod;
+ propcollation = exprcollation;
rel = table_open(PropgraphPropertyRelationId, RowExclusiveLock);
@@ -812,6 +956,8 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
namestrcpy(&propnamedata, propname);
values[Anum_pg_propgraph_property_pgpname - 1] = NameGetDatum(&propnamedata);
values[Anum_pg_propgraph_property_pgptypid - 1] = ObjectIdGetDatum(proptypid);
+ values[Anum_pg_propgraph_property_pgptypmod - 1] = Int32GetDatum(proptypmod);
+ values[Anum_pg_propgraph_property_pgpcollation - 1] = ObjectIdGetDatum(propcollation);
tup = heap_form_tuple(RelationGetDescr(rel), values, nulls);
CatalogTupleInsert(rel, tup);
@@ -828,7 +974,13 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
}
else
{
- proptypid = GetSysCacheOid1(PROPGRAPHPROPOID, Anum_pg_propgraph_property_pgptypid, ObjectIdGetDatum(propoid));
+ HeapTuple pgptup = SearchSysCache1(PROPGRAPHPROPOID, ObjectIdGetDatum(propoid));
+ Form_pg_propgraph_property pgpform = (Form_pg_propgraph_property) GETSTRUCT(pgptup);
+
+ proptypid = pgpform->pgptypid;
+ proptypmod = pgpform->pgptypmod;
+ propcollation = pgpform->pgpcollation;
+ ReleaseSysCache(pgptup);
}
/*
@@ -845,6 +997,32 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
errdetail("In a property graph, a property of the same name has to have the same data type in each label."));
}
+ /* Similarly for collation */
+ if (propcollation != exprcollation)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("property \"%s\" collation mismatch: %s vs. %s",
+ propname, get_collation_name(propcollation), get_collation_name(exprcollation)),
+ errdetail("In a property graph, a property of the same name has to have the same collation in each label."));
+ }
+
+ /*
+ * And typmod. It does not seem to be necessary to enforce typmod
+ * consistency across properties with the same name. But when properties
+ * with the same name have different typmods, it is not clear which one
+ * should be used as the typmod of the graph property when typmod of a
+ * property is requested before fetching any of the property expressions.
+ */
+ if (proptypmod != exprtypmod)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("property \"%s\" data type modifier mismatch: %d vs. %d",
+ propname, proptypmod, exprtypmod),
+ errdetail("In a property graph, a property of the same name has to have the same type modifier in each label."));
+ }
+
/*
* Insert into pg_propgraph_label_property
*/
@@ -1236,10 +1414,10 @@ AlterPropGraph(ParseState *pstate, const AlterPropGraphStmt *stmt)
propgraph_edge_get_ref_keys(pstate, edge->esrckey, edge->esrcvertexcols, rel, srcrel,
einfo->aliasname, edge->location, "SOURCE",
- &einfo->srckey, &einfo->srcref);
+ &einfo->srckey, &einfo->srcref, &einfo->srceqop);
propgraph_edge_get_ref_keys(pstate, edge->edestkey, edge->edestvertexcols, rel, destrel,
einfo->aliasname, edge->location, "DESTINATION",
- &einfo->destkey, &einfo->destref);
+ &einfo->destkey, &einfo->destref, &einfo->desteqop);
einfo->labels = edge->labels;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 3e4c52b89c5..81463e67117 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -540,8 +540,7 @@ exprTypmod(const Node *expr)
case T_PlaceHolderVar:
return exprTypmod((Node *) ((const PlaceHolderVar *) expr)->phexpr);
case T_GraphPropertyRef:
- /* TODO */
- return -1;
+ return ((const GraphPropertyRef *) expr)->typmod;
default:
break;
}
@@ -1065,7 +1064,7 @@ exprCollation(const Node *expr)
coll = exprCollation((Node *) ((const PlaceHolderVar *) expr)->phexpr);
break;
case T_GraphPropertyRef:
- coll = DEFAULT_COLLATION_OID; /* FIXME */
+ coll = ((const GraphPropertyRef *) expr)->collation;
break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
diff --git a/src/backend/parser/parse_collate.c b/src/backend/parser/parse_collate.c
index 60bcc5f4145..8f912065a01 100644
--- a/src/backend/parser/parse_collate.c
+++ b/src/backend/parser/parse_collate.c
@@ -546,6 +546,7 @@ assign_collations_walker(Node *node, assign_collations_context *context)
case T_CaseTestExpr:
case T_SetToDefault:
case T_CurrentOfExpr:
+ case T_GraphPropertyRef:
/*
* General case for childless expression nodes. These should
@@ -571,13 +572,6 @@ assign_collations_walker(Node *node, assign_collations_context *context)
location = exprLocation(node);
break;
- case T_GraphPropertyRef:
- /* FIXME */
- collation = DEFAULT_COLLATION_OID;
- strength = COLLATE_IMPLICIT;
- location = -1;
- break;
-
default:
{
/*
diff --git a/src/backend/parser/parse_graphtable.c b/src/backend/parser/parse_graphtable.c
index b088306b5b3..a8769a67b6a 100644
--- a/src/backend/parser/parse_graphtable.c
+++ b/src/backend/parser/parse_graphtable.c
@@ -56,18 +56,24 @@ transformGraphTablePropertyRef(ParseState *pstate, ColumnRef *cref)
if (list_member(gpstate->variables, field1))
{
GraphPropertyRef *gpr = makeNode(GraphPropertyRef);
- Oid propid;
+ HeapTuple pgptup;
+ Form_pg_propgraph_property pgpform;
- propid = GetSysCacheOid2(PROPGRAPHPROPNAME, Anum_pg_propgraph_property_oid, ObjectIdGetDatum(gpstate->graphid), CStringGetDatum(propname));
- if (!propid)
+ pgptup = SearchSysCache2(PROPGRAPHPROPNAME, ObjectIdGetDatum(gpstate->graphid), CStringGetDatum(propname));
+ if (!HeapTupleIsValid(pgptup))
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
errmsg("property \"%s\" does not exist", propname));
+ pgpform = (Form_pg_propgraph_property) GETSTRUCT(pgptup);
gpr->location = cref->location;
gpr->elvarname = elvarname;
- gpr->propid = propid;
- gpr->typeId = GetSysCacheOid1(PROPGRAPHPROPOID, Anum_pg_propgraph_property_pgptypid, ObjectIdGetDatum(propid));
+ gpr->propid = pgpform->oid;
+ gpr->typeId = pgpform->pgptypid;
+ gpr->typmod = pgpform->pgptypmod;
+ gpr->collation = pgpform->pgpcollation;
+
+ ReleaseSysCache(pgptup);
return (Node *) gpr;
}
diff --git a/src/backend/rewrite/rewriteGraphTable.c b/src/backend/rewrite/rewriteGraphTable.c
index 0716a186596..8bb8a38b569 100644
--- a/src/backend/rewrite/rewriteGraphTable.c
+++ b/src/backend/rewrite/rewriteGraphTable.c
@@ -14,6 +14,7 @@
#include "postgres.h"
#include "access/table.h"
+#include "catalog/pg_operator.h"
#include "catalog/pg_propgraph_element.h"
#include "catalog/pg_propgraph_element_label.h"
#include "catalog/pg_propgraph_label.h"
@@ -23,7 +24,10 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
#include "parser/analyze.h"
+#include "parser/parse_collate.h"
+#include "parser/parse_func.h"
#include "parser/parse_node.h"
+#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "parser/parse_relation.h"
@@ -83,7 +87,7 @@ struct path_element
};
static Node *replace_property_refs(Oid propgraphid, Node *node, const List *mappings);
-static List *build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum);
+static List *build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, Oid refid, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum, AttrNumber catalog_eqop_attnum);
static List *generate_queries_for_path_pattern(RangeTblEntry *rte, List *element_patterns);
static Query *generate_query_for_graph_path(RangeTblEntry *rte, List *path);
static Node *generate_setop_from_pathqueries(List *pathqueries, List **rtable, List **targetlist);
@@ -376,6 +380,23 @@ generate_queries_for_path_pattern_recurse(RangeTblEntry *rte, List *pathqueries,
/*
* Construct a query representing given graph path.
*
+ * The query contains:
+ *
+ * 1. targetlist corresponding the COLUMNS clause of GRAPH_TABLE clause
+ *
+ * 2. quals corresponding to the WHERE clause of individual elements, WHERE
+ * clause in GRAPH_TABLE clause and quals representing edge-vertex links.
+ *
+ * 3. fromlist containing all elements in the path
+ *
+ * The collations of property expressions are obtained from the catalog and
+ * substituted in place of a property reference. The collations of expressions
+ * in COLUMNS and WHERE clauses are assigned before rewriting the graph table.
+ * The collations of the edge-vertex link quals are assigned when crafting those
+ * quals. Thus everything in the query that requires collation assignment has
+ * been taken care of already. So no collation assignment is required in this
+ * function.
+ *
* More details in the prologue of generate_queries_for_path_pattern().
*/
static Query *
@@ -551,6 +572,12 @@ generate_query_for_empty_path_pattern(RangeTblEntry *rte)
/*
* Construct a query which is UNION of given path queries.
*
+ * The UNION query derives collations of its targetlist entries from the
+ * corresponding targetlist entries of the path queries and projects it. The
+ * targetlists of path queries being UNION'ed already have collations assigned.
+ * The same collations are used for targetlist of UNION query. Thus there is no
+ * separate collation assignment required in this function.
+ *
* The function destroys given pathqueries list while constructing
* SetOperationStmt recrursively. Hence the function always returns with
* `pathqueries` set to NIL.
@@ -744,11 +771,15 @@ create_gpe_for_element(struct path_factor *pf, Oid elemoid)
* each time.
*/
pe->src_quals = build_edge_vertex_link_quals(eletup, pf->factorpos + 1, pf->src_pf->factorpos + 1,
+ pe->srcvertexid,
Anum_pg_propgraph_element_pgesrckey,
- Anum_pg_propgraph_element_pgesrcref);
+ Anum_pg_propgraph_element_pgesrcref,
+ Anum_pg_propgraph_element_pgesrceqop);
pe->dest_quals = build_edge_vertex_link_quals(eletup, pf->factorpos + 1, pf->dest_pf->factorpos + 1,
+ pe->destvertexid,
Anum_pg_propgraph_element_pgedestkey,
- Anum_pg_propgraph_element_pgedestref);
+ Anum_pg_propgraph_element_pgedestref,
+ Anum_pg_propgraph_element_pgedesteqop);
}
ReleaseSysCache(eletup);
@@ -1117,18 +1148,26 @@ replace_property_refs(Oid propgraphid, Node *node, const List *mappings)
}
/*
- * Build join qualification expressions between edge and vertex tables.
+ * Build join qualification expressions between edge and vertex tables using
+ * equality operators identified at the time of creating the edge.
*/
static List *
-build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum)
+build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, Oid refid, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum, AttrNumber catalog_eqop_attnum)
{
List *quals = NIL;
Form_pg_propgraph_element pgeform;
Datum datum;
Datum *d1,
- *d2;
+ *d2,
+ *d3;
int n1,
- n2;
+ n2,
+ n3;
+ ParseState *pstate = make_parsestate(NULL);
+ HeapTuple reftup = SearchSysCache1(PROPGRAPHELOID, ObjectIdGetDatum(refid));
+ Oid refrelid = ((Form_pg_propgraph_element) GETSTRUCT(reftup))->pgerelid;
+
+ ReleaseSysCache(reftup);
pgeform = (Form_pg_propgraph_element) GETSTRUCT(edgetup);
@@ -1138,33 +1177,74 @@ build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, AttrNum
datum = SysCacheGetAttrNotNull(PROPGRAPHELOID, edgetup, catalog_ref_attnum);
deconstruct_array_builtin(DatumGetArrayTypeP(datum), INT2OID, &d2, NULL, &n2);
+ datum = SysCacheGetAttrNotNull(PROPGRAPHELOID, edgetup, catalog_eqop_attnum);
+ deconstruct_array_builtin(DatumGetArrayTypeP(datum), OIDOID, &d3, NULL, &n3);
+
if (n1 != n2)
elog(ERROR, "array size key (%d) vs ref (%d) mismatch for element ID %u", catalog_key_attnum, catalog_ref_attnum, pgeform->oid);
+ if (n1 != n3)
+ elog(ERROR, "array size key (%d) vs operator (%d) mismatch for element ID %u", catalog_key_attnum, catalog_eqop_attnum, pgeform->oid);
for (int i = 0; i < n1; i++)
{
AttrNumber keyattn = DatumGetInt16(d1[i]);
AttrNumber refattn = DatumGetInt16(d2[i]);
+ Oid eqop = DatumGetObjectId(d3[i]);
+ Var *keyvar;
+ Var *refvar;
Oid atttypid;
- TypeCacheEntry *typentry;
- OpExpr *op;
+ int32 atttypmod;
+ Oid attcoll;
+ HeapTuple tup;
+ Form_pg_operator opform;
+ List *args;
+ Oid actual_arg_types[2];
+ Oid declared_arg_types[2];
+ OpExpr *linkqual;
+
+ get_atttypetypmodcoll(pgeform->pgerelid, keyattn, &atttypid, &atttypmod, &attcoll);
+ keyvar = makeVar(edgerti, keyattn, atttypid, atttypmod, attcoll, 0);
+ get_atttypetypmodcoll(refrelid, refattn, &atttypid, &atttypmod, &attcoll);
+ refvar = makeVar(refrti, refattn, atttypid, atttypmod, attcoll, 0);
+
+ tup = SearchSysCache1(OPEROID, ObjectIdGetDatum(eqop));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for operator %u", eqop);
+ opform = (Form_pg_operator) GETSTRUCT(tup);
+ /* An equality operator is a binary operator returning boolean result. */
+ Assert(opform->oprkind == 'b'
+ && RegProcedureIsValid(opform->oprcode)
+ && opform->oprresult == BOOLOID
+ && !get_func_retset(opform->oprcode));
/*
- * TODO: Assumes types the same on both sides; no collations yet. Some
- * of this could probably be shared with foreign key triggers.
+ * Prepare operands and cast them to the types required by the
+ * equality operator. Similar to PK/FK qauls, referenced vertex key is
+ * used as left operand and referencing edge key is used as right
+ * operand.
*/
- atttypid = get_atttype(pgeform->pgerelid, keyattn);
- typentry = lookup_type_cache(atttypid, TYPECACHE_EQ_OPR);
-
- op = makeNode(OpExpr);
- op->location = -1;
- op->opno = typentry->eq_opr;
- op->opresulttype = BOOLOID;
- op->args = list_make2(makeVar(edgerti, keyattn, atttypid, -1, 0, 0),
- makeVar(refrti, refattn, atttypid, -1, 0, 0));
- quals = lappend(quals, op);
+ args = list_make2(refvar, keyvar);
+ actual_arg_types[0] = exprType((Node *) refvar);
+ actual_arg_types[1] = exprType((Node *) keyvar);
+ declared_arg_types[0] = opform->oprleft;
+ declared_arg_types[1] = opform->oprright;
+ make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types);
+
+ linkqual = makeNode(OpExpr);
+ linkqual->opno = opform->oid;
+ linkqual->opfuncid = opform->oprcode;
+ linkqual->opresulttype = opform->oprresult;
+ linkqual->opretset = false;
+ /* opcollid and inputcollid will be set by parse_collate.c */
+ linkqual->args = args;
+ linkqual->location = -1;
+
+ ReleaseSysCache(tup);
+ quals = lappend(quals, linkqual);
}
+ assign_expr_collations(pstate, (Node *) quals);
+
return quals;
}
diff --git a/src/include/catalog/pg_propgraph_element.h b/src/include/catalog/pg_propgraph_element.h
index 2bc2066b6c1..fea06c0963e 100644
--- a/src/include/catalog/pg_propgraph_element.h
+++ b/src/include/catalog/pg_propgraph_element.h
@@ -62,6 +62,11 @@ CATALOG(pg_propgraph_element,8299,PropgraphElementRelationId)
*/
int16 pgesrcref[1];
+ /*
+ * for edges: Oids of the equality operators for comparing source keys
+ */
+ Oid pgesrceqop[1];
+
/*
* for edges: destination vertex key (column numbers in pgerelid relation)
*/
@@ -72,6 +77,12 @@ CATALOG(pg_propgraph_element,8299,PropgraphElementRelationId)
* in relation reached via pgedestvertexid)
*/
int16 pgedestref[1];
+
+ /*
+ * for edges: Oids of the equality operators for comparing destination
+ * keys
+ */
+ Oid pgedesteqop[1];
#endif
} FormData_pg_propgraph_element;
diff --git a/src/include/catalog/pg_propgraph_property.h b/src/include/catalog/pg_propgraph_property.h
index b8921ace30c..240b34f0390 100644
--- a/src/include/catalog/pg_propgraph_property.h
+++ b/src/include/catalog/pg_propgraph_property.h
@@ -36,6 +36,12 @@ CATALOG(pg_propgraph_property,8306,PropgraphPropertyRelationId)
/* data type of the property */
Oid pgptypid BKI_LOOKUP_OPT(pg_type);
+
+ /* typemod of the property */
+ int32 pgptypmod;
+
+ /* collation of the property */
+ Oid pgpcollation BKI_LOOKUP_OPT(pg_collation);
} FormData_pg_propgraph_property;
/* ----------------
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 0a5e1b35b15..6f3c597cd9e 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -2176,6 +2176,8 @@ typedef struct GraphPropertyRef
const char *elvarname;
Oid propid;
Oid typeId;
+ int32 typmod;
+ Oid collation;
ParseLoc location;
} GraphPropertyRef;
diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out
index 70c4afda52c..d35c02b462c 100644
--- a/src/test/regress/expected/create_property_graph.out
+++ b/src/test/regress/expected/create_property_graph.out
@@ -187,6 +187,94 @@ GRANT SELECT ON PROPERTY GRAPH g1 TO regress_graph_user2;
GRANT UPDATE ON PROPERTY GRAPH g1 TO regress_graph_user2; -- fail
ERROR: invalid privilege type UPDATE for property graph
RESET ROLE;
+-- collation
+CREATE TABLE tc1 (a int, b text);
+CREATE TABLE tc2 (a int, b text);
+CREATE TABLE tc3 (a int, b text COLLATE "C");
+CREATE TABLE ec1 (ek1 int, ek2 int, eb text);
+CREATE TABLE ec2 (ek1 int, ek2 int, eb text COLLATE "POSIX");
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a), tc3 KEY (a)); -- fail
+ERROR: property "b" collation mismatch: default vs. C
+DETAIL: In a property graph, a property of the same name has to have the same collation in each label.
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a),
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)); -- fail
+ERROR: property "eb" collation mismatch: default vs. POSIX
+DETAIL: In a property graph, a property of the same name has to have the same collation in each label.
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a) DEFAULT LABEL PROPERTIES (a), tc3 KEY (b))
+ EDGE TABLES (
+ ec2 KEY (ek1, eb)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (eb) REFERENCES tc3 (b)); -- fail
+ERROR: collation mismatch in DESTINATION key of edge "ec2": POSIX vs. C
+LINE 4: ec2 KEY (ek1, eb)
+ ^
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a));
+ALTER PROPERTY GRAPH gc1 ADD VERTEX TABLES (tc3 KEY (a)); -- fail
+ERROR: property "b" collation mismatch: default vs. C
+DETAIL: In a property graph, a property of the same name has to have the same collation in each label.
+ALTER PROPERTY GRAPH gc1 ADD EDGE TABLES (
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)); -- fail
+ERROR: property "eb" collation mismatch: default vs. POSIX
+DETAIL: In a property graph, a property of the same name has to have the same collation in each label.
+ALTER PROPERTY GRAPH gc1
+ ADD VERTEX TABLES (
+ tc3 KEY (a) DEFAULT LABEL PROPERTIES (a, b COLLATE pg_catalog.DEFAULT AS b));
+ALTER PROPERTY GRAPH gc1 ADD EDGE TABLES (
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb COLLATE pg_catalog.DEFAULT AS eb));
+DROP PROPERTY GRAPH gc1;
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (
+ tc1 KEY (a) DEFAULT LABEL PROPERTIES (a, b::varchar COLLATE "C" AS b),
+ tc2 KEY (a) DEFAULT LABEL PROPERTIES (a, (b COLLATE "C")::varchar AS b),
+ tc3 KEY (a) DEFAULT LABEL PROPERTIES (a, b::varchar AS b))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb),
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb COLLATE pg_catalog.DEFAULT AS eb));
+-- type incosistency check
+CREATE TABLE v1 (a int primary key, b text);
+CREATE TABLE e(k1 text, k2 text, c text);
+CREATE TABLE v2 (m text, n text);
+CREATE PROPERTY GRAPH gt
+ VERTEX TABLES (v1 KEY (a), v2 KEY (m))
+ EDGE TABLES (
+ e KEY (k1, k2)
+ SOURCE KEY (k1) REFERENCES v1(a)
+ DESTINATION KEY (k2) REFERENCES v2(m)); -- fail
+ERROR: no equality operator exists for SOURCE key comparison of edge "e"
+LINE 4: e KEY (k1, k2)
+ ^
+ALTER TABLE e DROP COLUMN k1, ADD COLUMN k1 bigint primary key;
+CREATE PROPERTY GRAPH gt
+ VERTEX TABLES (v1 KEY (a), v2 KEY (m))
+ EDGE TABLES (
+ e KEY (k1, k2)
+ SOURCE KEY (k1) REFERENCES v1(a)
+ DESTINATION KEY (k2) REFERENCES v2(m));
-- information schema
SELECT * FROM information_schema.property_graphs ORDER BY property_graph_name;
property_graph_catalog | property_graph_schema | property_graph_name
@@ -196,7 +284,9 @@ SELECT * FROM information_schema.property_graphs ORDER BY property_graph_name;
regression | create_property_graph_tests | g3
regression | create_property_graph_tests | g4
regression | create_property_graph_tests | g5
-(5 rows)
+ regression | create_property_graph_tests | gc1
+ regression | create_property_graph_tests | gt
+(7 rows)
SELECT * FROM information_schema.pg_element_tables ORDER BY property_graph_name, element_table_alias;
property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | element_table_kind | table_catalog | table_schema | table_name | element_table_definition
@@ -216,7 +306,15 @@ SELECT * FROM information_schema.pg_element_tables ORDER BY property_graph_name,
regression | create_property_graph_tests | g5 | t11 | VERTEX | regression | create_property_graph_tests | t11 |
regression | create_property_graph_tests | g5 | t12 | VERTEX | regression | create_property_graph_tests | t12 |
regression | create_property_graph_tests | g5 | t13 | EDGE | regression | create_property_graph_tests | t13 |
-(15 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | EDGE | regression | create_property_graph_tests | ec1 |
+ regression | create_property_graph_tests | gc1 | ec2 | EDGE | regression | create_property_graph_tests | ec2 |
+ regression | create_property_graph_tests | gc1 | tc1 | VERTEX | regression | create_property_graph_tests | tc1 |
+ regression | create_property_graph_tests | gc1 | tc2 | VERTEX | regression | create_property_graph_tests | tc2 |
+ regression | create_property_graph_tests | gc1 | tc3 | VERTEX | regression | create_property_graph_tests | tc3 |
+ regression | create_property_graph_tests | gt | e | EDGE | regression | create_property_graph_tests | e |
+ regression | create_property_graph_tests | gt | v1 | VERTEX | regression | create_property_graph_tests | v1 |
+ regression | create_property_graph_tests | gt | v2 | VERTEX | regression | create_property_graph_tests | v2 |
+(23 rows)
SELECT * FROM information_schema.pg_element_table_key_columns ORDER BY property_graph_name, element_table_alias, ordinal_position;
property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | column_name | ordinal_position
@@ -240,7 +338,18 @@ SELECT * FROM information_schema.pg_element_table_key_columns ORDER BY property_
regression | create_property_graph_tests | g5 | t11 | a | 1
regression | create_property_graph_tests | g5 | t12 | b | 1
regression | create_property_graph_tests | g5 | t13 | c | 1
-(19 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | ek1 | 1
+ regression | create_property_graph_tests | gc1 | ec1 | ek2 | 2
+ regression | create_property_graph_tests | gc1 | ec2 | ek1 | 1
+ regression | create_property_graph_tests | gc1 | ec2 | ek2 | 2
+ regression | create_property_graph_tests | gc1 | tc1 | a | 1
+ regression | create_property_graph_tests | gc1 | tc2 | a | 1
+ regression | create_property_graph_tests | gc1 | tc3 | a | 1
+ regression | create_property_graph_tests | gt | e | k1 | 1
+ regression | create_property_graph_tests | gt | e | k2 | 2
+ regression | create_property_graph_tests | gt | v1 | a | 1
+ regression | create_property_graph_tests | gt | v2 | m | 1
+(30 rows)
SELECT * FROM information_schema.pg_edge_table_components ORDER BY property_graph_name, edge_table_alias, edge_end DESC, ordinal_position;
property_graph_catalog | property_graph_schema | property_graph_name | edge_table_alias | vertex_table_alias | edge_end | edge_table_column_name | vertex_table_column_name | ordinal_position
@@ -257,7 +366,13 @@ SELECT * FROM information_schema.pg_edge_table_components ORDER BY property_grap
regression | create_property_graph_tests | g4 | e2 | t3 | DESTINATION | t | y | 2
regression | create_property_graph_tests | g5 | t13 | t11 | SOURCE | d | a | 1
regression | create_property_graph_tests | g5 | t13 | t12 | DESTINATION | e | b | 1
-(12 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | tc1 | SOURCE | ek1 | a | 1
+ regression | create_property_graph_tests | gc1 | ec1 | tc2 | DESTINATION | ek2 | a | 1
+ regression | create_property_graph_tests | gc1 | ec2 | tc1 | SOURCE | ek1 | a | 1
+ regression | create_property_graph_tests | gc1 | ec2 | tc2 | DESTINATION | ek2 | a | 1
+ regression | create_property_graph_tests | gt | e | v1 | SOURCE | k1 | a | 1
+ regression | create_property_graph_tests | gt | e | v2 | DESTINATION | k2 | m | 1
+(18 rows)
SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph_name, element_table_alias, label_name;
property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | label_name
@@ -280,11 +395,19 @@ SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph
regression | create_property_graph_tests | g5 | t11 | t11
regression | create_property_graph_tests | g5 | t12 | t12
regression | create_property_graph_tests | g5 | t13 | t13
-(18 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | ec1
+ regression | create_property_graph_tests | gc1 | ec2 | ec2
+ regression | create_property_graph_tests | gc1 | tc1 | tc1
+ regression | create_property_graph_tests | gc1 | tc2 | tc2
+ regression | create_property_graph_tests | gc1 | tc3 | tc3
+ regression | create_property_graph_tests | gt | e | e
+ regression | create_property_graph_tests | gt | v1 | v1
+ regression | create_property_graph_tests | gt | v2 | v2
+(26 rows)
SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_graph_name, element_table_alias, property_name;
- property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | property_name | property_expression
-------------------------+-----------------------------+---------------------+---------------------+---------------+---------------------
+ property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | property_name | property_expression
+------------------------+-----------------------------+---------------------+---------------------+---------------+--------------------------------------
regression | create_property_graph_tests | g2 | e1 | a | a
regression | create_property_graph_tests | g2 | e1 | i | i
regression | create_property_graph_tests | g2 | e1 | t | t
@@ -320,7 +443,26 @@ 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)
+ regression | create_property_graph_tests | gc1 | ec1 | eb | eb
+ regression | create_property_graph_tests | gc1 | ec1 | ek1 | ek1
+ regression | create_property_graph_tests | gc1 | ec1 | ek2 | ek2
+ regression | create_property_graph_tests | gc1 | ec2 | eb | (eb COLLATE "default")
+ regression | create_property_graph_tests | gc1 | ec2 | ek1 | ek1
+ regression | create_property_graph_tests | gc1 | ec2 | ek2 | ek2
+ regression | create_property_graph_tests | gc1 | tc1 | a | a
+ regression | create_property_graph_tests | gc1 | tc1 | b | ((b)::character varying COLLATE "C")
+ regression | create_property_graph_tests | gc1 | tc2 | a | a
+ regression | create_property_graph_tests | gc1 | tc2 | b | ((b)::character varying COLLATE "C")
+ regression | create_property_graph_tests | gc1 | tc3 | a | a
+ regression | create_property_graph_tests | gc1 | tc3 | b | (b)::character varying
+ regression | create_property_graph_tests | gt | e | c | c
+ regression | create_property_graph_tests | gt | e | k1 | k1
+ regression | create_property_graph_tests | gt | e | k2 | k2
+ regression | create_property_graph_tests | gt | v1 | a | a
+ regression | create_property_graph_tests | gt | v1 | b | b
+ regression | create_property_graph_tests | gt | v2 | m | m
+ regression | create_property_graph_tests | gt | v2 | n | n
+(54 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
@@ -367,7 +509,26 @@ 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)
+ regression | create_property_graph_tests | gc1 | ec1 | eb
+ regression | create_property_graph_tests | gc1 | ec1 | ek1
+ regression | create_property_graph_tests | gc1 | ec1 | ek2
+ regression | create_property_graph_tests | gc1 | ec2 | eb
+ regression | create_property_graph_tests | gc1 | ec2 | ek1
+ regression | create_property_graph_tests | gc1 | ec2 | ek2
+ regression | create_property_graph_tests | gc1 | tc1 | a
+ regression | create_property_graph_tests | gc1 | tc1 | b
+ regression | create_property_graph_tests | gc1 | tc2 | a
+ regression | create_property_graph_tests | gc1 | tc2 | b
+ regression | create_property_graph_tests | gc1 | tc3 | a
+ regression | create_property_graph_tests | gc1 | tc3 | b
+ regression | create_property_graph_tests | gt | e | c
+ regression | create_property_graph_tests | gt | e | k1
+ regression | create_property_graph_tests | gt | e | k2
+ regression | create_property_graph_tests | gt | v1 | a
+ regression | create_property_graph_tests | gt | v1 | b
+ regression | create_property_graph_tests | gt | v2 | m
+ regression | create_property_graph_tests | gt | v2 | n
+(61 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
@@ -390,39 +551,59 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n
regression | create_property_graph_tests | g5 | t11
regression | create_property_graph_tests | g5 | t12
regression | create_property_graph_tests | g5 | t13
-(18 rows)
+ regression | create_property_graph_tests | gc1 | ec1
+ regression | create_property_graph_tests | gc1 | ec2
+ regression | create_property_graph_tests | gc1 | tc1
+ regression | create_property_graph_tests | gc1 | tc2
+ regression | create_property_graph_tests | gc1 | tc3
+ regression | create_property_graph_tests | gt | e
+ regression | create_property_graph_tests | gt | v1
+ regression | create_property_graph_tests | gt | v2
+(26 rows)
SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_name, property_name;
- property_graph_catalog | property_graph_schema | property_graph_name | property_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier
-------------------------+-----------------------------+---------------------+---------------+-----------+--------------------------+------------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+---------------------------+--------------------------+------------------------+---------------+--------------+------------+---------------------+----------------
- regression | create_property_graph_tests | g2 | a | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | a
- regression | create_property_graph_tests | g2 | b | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | b
- regression | create_property_graph_tests | g2 | i | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | i
- regression | create_property_graph_tests | g2 | j | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | j
- regression | create_property_graph_tests | g2 | k | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | k
- regression | create_property_graph_tests | g2 | t | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | t
- regression | create_property_graph_tests | g2 | x | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | x
- regression | create_property_graph_tests | g2 | y | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | y
- regression | create_property_graph_tests | g2 | z | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | z
- regression | create_property_graph_tests | g3 | a | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | a
- regression | create_property_graph_tests | g3 | b | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | b
- regression | create_property_graph_tests | g3 | x | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | x
- regression | create_property_graph_tests | g3 | y | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | y
- regression | create_property_graph_tests | g3 | z | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | z
- 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 | 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
- regression | create_property_graph_tests | g4 | yy | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | yy
- regression | create_property_graph_tests | g4 | zz | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | zz
- regression | create_property_graph_tests | g5 | a | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | a
- regression | create_property_graph_tests | g5 | b | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | b
- 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)
+ property_graph_catalog | property_graph_schema | property_graph_name | property_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier
+------------------------+-----------------------------+---------------------+---------------+-------------------+--------------------------+------------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+---------------------------+--------------------------+------------------------+---------------+--------------+------------+---------------------+----------------
+ regression | create_property_graph_tests | g2 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | g2 | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b
+ regression | create_property_graph_tests | g2 | i | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i
+ regression | create_property_graph_tests | g2 | j | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | j
+ regression | create_property_graph_tests | g2 | k | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | k
+ regression | create_property_graph_tests | g2 | t | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | t
+ regression | create_property_graph_tests | g2 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x
+ regression | create_property_graph_tests | g2 | y | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | y
+ regression | create_property_graph_tests | g2 | z | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | z
+ regression | create_property_graph_tests | g3 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | g3 | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b
+ regression | create_property_graph_tests | g3 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x
+ regression | create_property_graph_tests | g3 | y | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | y
+ regression | create_property_graph_tests | g3 | z | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | z
+ regression | create_property_graph_tests | g4 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | g4 | i | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i
+ regression | create_property_graph_tests | g4 | i_j | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i_j
+ regression | create_property_graph_tests | g4 | kk | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | kk
+ regression | create_property_graph_tests | g4 | t | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | t
+ regression | create_property_graph_tests | g4 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x
+ regression | create_property_graph_tests | g4 | yy | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | yy
+ regression | create_property_graph_tests | g4 | zz | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | zz
+ regression | create_property_graph_tests | g5 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | g5 | b | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | b
+ regression | create_property_graph_tests | g5 | c | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | c
+ regression | create_property_graph_tests | g5 | d | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | d
+ regression | create_property_graph_tests | g5 | e | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | e
+ regression | create_property_graph_tests | gc1 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | gc1 | b | character varying | | | | | | regression | pg_catalog | C | | | | | | | regression | pg_catalog | varchar | | | | | b
+ regression | create_property_graph_tests | gc1 | eb | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | eb
+ regression | create_property_graph_tests | gc1 | ek1 | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | ek1
+ regression | create_property_graph_tests | gc1 | ek2 | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | ek2
+ regression | create_property_graph_tests | gt | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | gt | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b
+ regression | create_property_graph_tests | gt | c | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | c
+ regression | create_property_graph_tests | gt | k1 | bigint | | | | | | regression | | | | | | | | | regression | pg_catalog | int8 | | | | | k1
+ regression | create_property_graph_tests | gt | k2 | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | k2
+ regression | create_property_graph_tests | gt | m | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | m
+ regression | create_property_graph_tests | gt | n | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | n
+(39 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
diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out
index 3796297cb07..0a574b10f4b 100644
--- a/src/test/regress/expected/graph_table.out
+++ b/src/test/regress/expected/graph_table.out
@@ -517,7 +517,8 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a)->(b)->(a is vl1) COLUMNS (a.vname AS sel
v13 | v23 | 30 | 1030
(2 rows)
--- add an edge with same vertex as source and destination to test loops
+-- add an edge with same vertex as source and destination to test loops. Also
+-- use this for collation tests
CREATE TABLE e3_3 (src_id int,
dest_id int,
ename varchar(10),
@@ -530,8 +531,35 @@ ALTER PROPERTY GRAPH g1 ADD EDGE TABLES (
LABEL l1 PROPERTIES (ename AS elname)
);
INSERT INTO e3_3 VALUES (2003, 2003, 'e331', 10010);
+INSERT INTO e3_3 VALUES (2003, 2003, 'E331', 10010);
-- cyclic pattern with edge patterns with same variable name
-SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name ASC;
+ self | loop_name
+------+-----------
+ v33 | e331
+ v33 | E331
+(2 rows)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name COLLATE "C" ASC;
+ self | loop_name
+------+-----------
+ v33 | E331
+ v33 | e331
+(2 rows)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b IS el2 WHERE b.ename > 'E331' COLLATE "C"]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
+ self | loop_name
+------+-----------
+ v33 | e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.ename > 'E331' COLLATE "C" COLUMNS (a.vname AS self, b.ename AS loop_name));
+ self | loop_name
+------+-----------
+ v33 | e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) WHERE loop_name > 'E331' COLLATE "C";
self | loop_name
------+-----------
v33 | e331
@@ -541,40 +569,89 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(c)-[b]->(d) COLUMNS (a.vname AS an
ERROR: An edge can not connect more than two vertexes even in a cyclic pattern.
-- property graph with some of the elements, labels and properties same as the
-- previous one. Test whether components from the specified property graph are
--- used.
+-- used. Also use this for collation tests
create property graph g2
vertex tables (
v1
- label l1 properties ('g2.' || vname as elname),
+ label l1 properties ('g2.' || vname COLLATE "C" as elname),
v2 key (id1, id2)
- label l1 properties ('g2.' || vname as elname),
+ label l1 properties ('g2.' || vname COLLATE "C" as elname),
v3
- label l1 properties ('g2.' || vname as elname)
+ label l1 properties ('g2.' || vname COLLATE "C" as elname)
)
edge tables (
e1_2 key (id_1, id_2_1, id_2_2)
source key (id_1) references v1 (id)
destination key (id_2_1, id_2_2) references v2 (id1, id2)
- label l1 properties ('g2.' || ename as elname),
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
e1_3
source key (id_1) references v1 (id)
destination key (id_3) references v3 (id)
- label l1 properties ('g2.' || ename as elname),
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
e2_3 key (id_2_1, id_2_2, id_3)
source key (id_2_1, id_2_2) references v2 (id1, id2)
destination key (id_3) references v3 (id)
- label l1 properties ('g2.' || ename as elname)
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
+ e3_3 KEY (src_id, dest_id)
+ SOURCE KEY (src_id) REFERENCES v3 (id)
+ DESTINATION KEY (src_id) REFERENCES v3 (id)
+ LABEL l1 PROPERTIES ('g2.' || ename COLLATE "C" as elname)
);
-select sn, cn, dn from graph_table (g2 match (src : l1)-[conn : l1]->(dest : l1) columns (src.elname as sn, conn.elname as cn, dest.elname as dn));
+select sn, cn, dn from graph_table (g2 match (src : l1)-[conn : l1]->(dest : l1) columns (src.elname as sn, conn.elname as cn, dest.elname as dn)) ORDER BY 1, 2, 3;
sn | cn | dn
--------+---------+--------
- g2.v12 | g2.e122 | g2.v21
g2.v11 | g2.e121 | g2.v22
- g2.v13 | g2.e123 | g2.v23
g2.v11 | g2.e131 | g2.v33
g2.v11 | g2.e132 | g2.v31
+ g2.v12 | g2.e122 | g2.v21
+ g2.v13 | g2.e123 | g2.v23
g2.v22 | g2.e231 | g2.v32
-(6 rows)
+ g2.v33 | g2.E331 | g2.v33
+ g2.v33 | g2.e331 | g2.v33
+(8 rows)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname COLLATE pg_catalog."default" AS loop_name)) ORDER BY loop_name ASC;
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.e331
+ g2.v33 | g2.E331
+(2 rows)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b WHERE b.elname > 'g2.E331']->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name));
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.elname > 'g2.E331' COLUMNS (a.elname AS self, b.elname AS loop_name));
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name)) WHERE loop_name > 'g2.E331';
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b WHERE b.elname > 'g2.e331' COLLATE pg_catalog."default"]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name));
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.E331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.elname > 'g2.e331' COLLATE pg_catalog."default" COLUMNS (a.elname AS self, b.elname AS loop_name));
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.E331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name)) WHERE loop_name > 'g2.e331' COLLATE pg_catalog."default";
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.E331
+(1 row)
CREATE VIEW customers_us AS SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name));
SELECT pg_get_viewdef('customers_us'::regclass);
@@ -671,6 +748,14 @@ SELECT *
1 | customer1 | US | redacted1
(1 row)
+-- graph table in a subquery
+SELECT * FROM customers co WHERE co.customer_id =
+ (SELECT customer_id FROM GRAPH_TABLE (myshop2 MATCH (cg IS customers WHERE cg.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (cg.customer_id)));
+ customer_id | name | address
+-------------+-----------+---------
+ 1 | customer1 | US
+(1 row)
+
-- query within graph table
SELECT sname, dname
FROM GRAPH_TABLE (g1 MATCH (src)->(dest)
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index 3b41fc7ba04..9ad17275f24 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -277,3 +277,4 @@ NOTICE: checking pg_propgraph_label_property {plppropid} => pg_propgraph_proper
NOTICE: checking pg_propgraph_label_property {plpellabelid} => pg_propgraph_element_label {oid}
NOTICE: checking pg_propgraph_property {pgppgid} => pg_class {oid}
NOTICE: checking pg_propgraph_property {pgptypid} => pg_type {oid}
+NOTICE: checking pg_propgraph_property {pgpcollation} => pg_collation {oid}
diff --git a/src/test/regress/sql/create_property_graph.sql b/src/test/regress/sql/create_property_graph.sql
index 4f9b5c0349b..9a1fe4fc6de 100644
--- a/src/test/regress/sql/create_property_graph.sql
+++ b/src/test/regress/sql/create_property_graph.sql
@@ -147,6 +147,83 @@ GRANT SELECT ON PROPERTY GRAPH g1 TO regress_graph_user2;
GRANT UPDATE ON PROPERTY GRAPH g1 TO regress_graph_user2; -- fail
RESET ROLE;
+-- collation
+CREATE TABLE tc1 (a int, b text);
+CREATE TABLE tc2 (a int, b text);
+CREATE TABLE tc3 (a int, b text COLLATE "C");
+
+CREATE TABLE ec1 (ek1 int, ek2 int, eb text);
+CREATE TABLE ec2 (ek1 int, ek2 int, eb text COLLATE "POSIX");
+
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a), tc3 KEY (a)); -- fail
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a),
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)); -- fail
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a) DEFAULT LABEL PROPERTIES (a), tc3 KEY (b))
+ EDGE TABLES (
+ ec2 KEY (ek1, eb)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (eb) REFERENCES tc3 (b)); -- fail
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a));
+ALTER PROPERTY GRAPH gc1 ADD VERTEX TABLES (tc3 KEY (a)); -- fail
+ALTER PROPERTY GRAPH gc1 ADD EDGE TABLES (
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)); -- fail
+ALTER PROPERTY GRAPH gc1
+ ADD VERTEX TABLES (
+ tc3 KEY (a) DEFAULT LABEL PROPERTIES (a, b COLLATE pg_catalog.DEFAULT AS b));
+ALTER PROPERTY GRAPH gc1 ADD EDGE TABLES (
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb COLLATE pg_catalog.DEFAULT AS eb));
+DROP PROPERTY GRAPH gc1;
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (
+ tc1 KEY (a) DEFAULT LABEL PROPERTIES (a, b::varchar COLLATE "C" AS b),
+ tc2 KEY (a) DEFAULT LABEL PROPERTIES (a, (b COLLATE "C")::varchar AS b),
+ tc3 KEY (a) DEFAULT LABEL PROPERTIES (a, b::varchar AS b))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb),
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb COLLATE pg_catalog.DEFAULT AS eb));
+
+-- type incosistency check
+CREATE TABLE v1 (a int primary key, b text);
+CREATE TABLE e(k1 text, k2 text, c text);
+CREATE TABLE v2 (m text, n text);
+CREATE PROPERTY GRAPH gt
+ VERTEX TABLES (v1 KEY (a), v2 KEY (m))
+ EDGE TABLES (
+ e KEY (k1, k2)
+ SOURCE KEY (k1) REFERENCES v1(a)
+ DESTINATION KEY (k2) REFERENCES v2(m)); -- fail
+ALTER TABLE e DROP COLUMN k1, ADD COLUMN k1 bigint primary key;
+CREATE PROPERTY GRAPH gt
+ VERTEX TABLES (v1 KEY (a), v2 KEY (m))
+ EDGE TABLES (
+ e KEY (k1, k2)
+ SOURCE KEY (k1) REFERENCES v1(a)
+ DESTINATION KEY (k2) REFERENCES v2(m));
-- information schema
diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql
index 905be9df01b..977bf2774e5 100644
--- a/src/test/regress/sql/graph_table.sql
+++ b/src/test/regress/sql/graph_table.sql
@@ -322,7 +322,8 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a is vl1)->(b)->(a is vl2) WHERE a.vname <>
SELECT * FROM GRAPH_TABLE (g1 MATCH (a is vl1)->(b)->(a) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)->(b)->(a is vl1) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
--- add an edge with same vertex as source and destination to test loops
+-- add an edge with same vertex as source and destination to test loops. Also
+-- use this for collation tests
CREATE TABLE e3_3 (src_id int,
dest_id int,
ename varchar(10),
@@ -336,37 +337,53 @@ ALTER PROPERTY GRAPH g1 ADD EDGE TABLES (
);
INSERT INTO e3_3 VALUES (2003, 2003, 'e331', 10010);
+INSERT INTO e3_3 VALUES (2003, 2003, 'E331', 10010);
-- cyclic pattern with edge patterns with same variable name
-SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name ASC;
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name COLLATE "C" ASC;
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b IS el2 WHERE b.ename > 'E331' COLLATE "C"]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.ename > 'E331' COLLATE "C" COLUMNS (a.vname AS self, b.ename AS loop_name));
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) WHERE loop_name > 'E331' COLLATE "C";
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(c)-[b]->(d) COLUMNS (a.vname AS aname, b.ename AS bname, c.vname AS cname, d.vname AS dname)); --error
-- property graph with some of the elements, labels and properties same as the
-- previous one. Test whether components from the specified property graph are
--- used.
+-- used. Also use this for collation tests
create property graph g2
vertex tables (
v1
- label l1 properties ('g2.' || vname as elname),
+ label l1 properties ('g2.' || vname COLLATE "C" as elname),
v2 key (id1, id2)
- label l1 properties ('g2.' || vname as elname),
+ label l1 properties ('g2.' || vname COLLATE "C" as elname),
v3
- label l1 properties ('g2.' || vname as elname)
+ label l1 properties ('g2.' || vname COLLATE "C" as elname)
)
edge tables (
e1_2 key (id_1, id_2_1, id_2_2)
source key (id_1) references v1 (id)
destination key (id_2_1, id_2_2) references v2 (id1, id2)
- label l1 properties ('g2.' || ename as elname),
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
e1_3
source key (id_1) references v1 (id)
destination key (id_3) references v3 (id)
- label l1 properties ('g2.' || ename as elname),
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
e2_3 key (id_2_1, id_2_2, id_3)
source key (id_2_1, id_2_2) references v2 (id1, id2)
destination key (id_3) references v3 (id)
- label l1 properties ('g2.' || ename as elname)
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
+ e3_3 KEY (src_id, dest_id)
+ SOURCE KEY (src_id) REFERENCES v3 (id)
+ DESTINATION KEY (src_id) REFERENCES v3 (id)
+ LABEL l1 PROPERTIES ('g2.' || ename COLLATE "C" as elname)
);
-select sn, cn, dn from graph_table (g2 match (src : l1)-[conn : l1]->(dest : l1) columns (src.elname as sn, conn.elname as cn, dest.elname as dn));
+select sn, cn, dn from graph_table (g2 match (src : l1)-[conn : l1]->(dest : l1) columns (src.elname as sn, conn.elname as cn, dest.elname as dn)) ORDER BY 1, 2, 3;
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname COLLATE pg_catalog."default" AS loop_name)) ORDER BY loop_name ASC;
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b WHERE b.elname > 'g2.E331']->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name));
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.elname > 'g2.E331' COLUMNS (a.elname AS self, b.elname AS loop_name));
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name)) WHERE loop_name > 'g2.E331';
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b WHERE b.elname > 'g2.e331' COLLATE pg_catalog."default"]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name));
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.elname > 'g2.e331' COLLATE pg_catalog."default" COLUMNS (a.elname AS self, b.elname AS loop_name));
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name)) WHERE loop_name > 'g2.e331' COLLATE pg_catalog."default";
CREATE VIEW customers_us AS SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name));
@@ -428,6 +445,10 @@ SELECT *
COLUMNS (cg.name_redacted AS customer_name_redacted))
WHERE co.customer_id = 1;
+-- graph table in a subquery
+SELECT * FROM customers co WHERE co.customer_id =
+ (SELECT customer_id FROM GRAPH_TABLE (myshop2 MATCH (cg IS customers WHERE cg.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (cg.customer_id)));
+
-- query within graph table
SELECT sname, dname
FROM GRAPH_TABLE (g1 MATCH (src)->(dest)
--
2.39.5