Hi Ashutosh,
I adapted the PGQ regression test cases to run on Oracle and
cross-verified the results to assess behavioral compatibility. Below
is a summary of the methodology, key differences found, and overall
assessment.
1. KEY DIFFERENCES
------------------
1.1 Supported by PG, not Oracle
ALTER PROPERTY GRAPH
Oracle rejects ALTER PROPERTY GRAPH syntax entirely (ORA-03048).
Modifying a graph requires DROP and recreate. PG supports ADD/DROP
VERTEX/EDGE TABLES, ALTER, RENAME TO, and SET SCHEMA in-place.
NODE TABLES / RELATIONSHIP TABLES synonyms
SQL/PGQ standard defines NODE/RELATIONSHIP as synonyms for
VERTEX/EDGE. Oracle accepts only VERTEX/EDGE (ORA-02000). PG
supports both.
TEMPORARY PROPERTY GRAPH
Oracle does not support temporary property graphs (ORA-00901).
PG supports them; the graph is dropped automatically at session end.
LATERAL + GRAPH_TABLE
Oracle disallows referencing outer variables inside a GRAPH_TABLE
WHERE clause (ORA-40996). PG supports LATERAL + GRAPH_TABLE directly.
1.2 Syntax difference -- Oracle workaround available
GRAPH_TABLE inside UDFs
Oracle's PL/SQL compiler cannot statically parse GRAPH_TABLE
(ORA-49028). EXECUTE IMMEDIATE (dynamic SQL) works as a workaround
and supports SYS_REFCURSOR and pipelined functions. PG supports both
static and dynamic usage. Note: the Oracle workaround carries a
performance cost -- no compile-time optimization, and per-row FETCH
overhead with pipelined functions.
1.3 Supported by Oracle, not PG
a.* star reference in COLUMNS
Oracle allows COLUMNS (a.*) to return all properties of an element
variable across all its labels. PG rejects this ("*" not allowed
here). A workaround exists: list properties explicitly. This is the
only SQL/PGQ query execution capability where Oracle is ahead of the
current PG implementation.
ALTER PROPERTY GRAPH COMPILE (item 1.18)
Oracle provides a COMPILE clause to recover from false invalidation
caused by its coarse-grained dependency tracking. PG does not have
this -- nor does it need it. PG uses strict dependency management,
so false invalidation does not occur.
1.4 Semantic difference
Label sharing policy
Oracle rejects defining the same label on more than one vertex/edge
table (ORA-42409 "label already defined"). Labels are bound to
exactly one table. PG allows label sharing across tables as long as
the property definitions (name, type, count) are consistent. PG's
approach is more flexible.
1.5 Design philosophy difference
Dependency management
Oracle allows DROP TABLE on a table referenced by a graph, and DROP
PROPERTY GRAPH on a graph that views depend on. Dependents are left
invalid and fail at next access (lazy validation). PG rejects the
DROP if dependents exist; CASCADE must be explicit. This is not
PGQ-specific -- PG follows the SQL standard (DROP ... CASCADE |
RESTRICT), while Oracle uses lazy invalidation.
3. ASSESSMENT
-------------
Within the tested scope, results matched Oracle except where noted
above. The only Oracle capabilities not present in PG are a.* in
COLUMNS (workaround: explicit property listing) and ALTER PROPERTY
GRAPH COMPILE (unnecessary in PG due to strict dependency management).
On the DDL side, PG is actually more complete than Oracle: ALTER
PROPERTY GRAPH, DROP PROPERTY GRAPH CASCADE, standard synonyms
(NODE/RELATIONSHIP), and TEMPORARY PROPERTY GRAPH are all supported
in PG but not in Oracle.
On the query side, all tested GRAPH_TABLE queries -- pattern matching,
WHERE clauses, SQL integration -- produced identical results. However,
the full SQL/PGQ specification goes well beyond the tested scope:
quantified path patterns, shortest path, graph types, ACYCLIC/SIMPLE
path constraints are not yet implemented. Oracle has broader query
coverage overall.
Regards,
Henson
