On Sun, Mar 8, 2026 at 12:52 AM Henson Choi <[email protected]> wrote:
>
> 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.
PGQ doesn’t seem to reject a.*. I think PostgreSQL is still at an early stage
of supporting SQL/PGQ, so missing features like this are acceptable for
now, and we can add support for more syntax in the future.
Here is a snippet from SQL/PGQ standard:
<graph table columns clause> ::=
COLUMNS <left paren> <graph table column definition>
[ { <comma> <graph table column definition> }... ] <right paren>
<graph table column definition> ::=
<value expression> [ AS <column name> ]
| <all properties reference>
<all properties reference> ::=
<element reference> <period> <asterisk>
>
> 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
--
Regards
Junwang Zhao
