Re: SQL Property Graph Queries (SQL/PGQ)

My previous message was held for moderation because of the total size
of the patches. Resending again with a zipped file.

On Sat, Mar 7, 2026 at 10:22 PM Henson Choi <assam258(at)gmail(dot)com> 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.
>

Thanks for doing this exercise.

>
> 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.

Right. Thanks to pg_dump, we have that advantage.

>
> 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.
>

+1.

> TEMPORARY PROPERTY GRAPH
> Oracle does not support temporary property graphs (ORA-00901).
> PG supports them; the graph is dropped automatically at session end.
>

check. I think this will have its use cases. At least something
related to temporary object mgmt (vs GTT for example) that they don't
have. :)

> LATERAL + GRAPH_TABLE
> Oracle disallows referencing outer variables inside a GRAPH_TABLE
> WHERE clause (ORA-40996). PG supports LATERAL + GRAPH_TABLE directly.
>

check.

>
> 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.
>

check.

>
> 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.

Thanks for bringing this to my notice. I spent some time on it, and I
have a patch which supports this. Please read more at the end of this
email.

>
> 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.
>

check

>
> 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.
>

It's as per the standard. Standard allows sharing labels across tables.

>
> 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.
>

Right. That's the architectural difference between the two.

>
> 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.
Thanks for the summary.

There are two new patches 0004 and 0005 in the attached patchset.
Below is a description of those.

All property reference and refactoring
--------------------------------------------------
According to the SQL/PGQ standard, all properties reference
<variable>.* is allowed in COLUMNs, but it's not allowed anywhere else
e.g. in WHERE clause or buried inside a value expression. The current
error message is misleading in saying ""*" not allowed here". Instead
it should say "not supported" in COLUMNs and "not allowed" in other
places. Patch 0004 is the patch fixing the error message. It is not
accurate since it only checks whether <variable>.* appears in COLUMNs
clause or not. It doesn't differentiate between it appearing directly
in COLUMNs vs buried in another value expression. But I think it
serves the purpose for now. It's a limitation that we will eventually
remove. So it may be ok.

I attempted to support it and turned out to be relatively simple. So
we can support it in the first version itself. However, the patch is
much larger. In order to expand all properties reference, we need to
find all the properties associated with the set of labels which
results from evaluating label expression. The properties are not
directly associated with a label, but through an element. Hence we
need to find at least one element associated with each label in the set.
Further the set of labels that an empty label expression results into
is all the labels which have at least one element of a given element
kind in the property graph. That's another reason why we want to find
at least one element associated with each of the labels. If we are
looking up element label catalog, why not to fetch all the elements
during transformation itself rather than waiting all the way till
the rewriting phase. So I changed the code to do that. And I think the
resultant code is much simpler, moves the error handling to
appropriate places and simplifies a lot of the rewriteGraphTable.c
code. Flip side is transform* functions are heavier, however in the
end it's code simplification. Since we are expanding the empty label
expression during transformation phase itself, we replace empty label
expression with a disjunction. But we need to know whether the
original label expression was empty or not in the ruleutils and when
consolidating path elements (generate_queries_for_path_pattern()). The
later usage will vanish once we support label disjunction. So I
introduced a flag to retain that status. All that refactoring is patch
0005.

Peter, if you think 0005 is larger to be acceptable at this stage, we
could revisit it later as well.

In order to apply 0005, 0004 is needed even though 0005 nullifies that
change. I have separated 0004, so that those changes can be committed
even if we decide to postpone 0005.

--
Best Wishes,
Ashutosh Bapat