Re: PGQ catalog representation and pg_dump support

On Fri, Sep 4, 2026 at 1:17 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> For example, uniqueness is a requirement:
>
> CREATE TABLE v (a int, b text);
> CREATE TABLE e (id int PRIMARY KEY, va int);
> CREATE PROPERTY GRAPH g
> VERTEX TABLES (v)
> EDGE TABLES (e SOURCE KEY (va) REFERENCES v (a)
> DESTINATION KEY (va) REFERENCES v (a));
>
> This fails, because v has no primary key. But if you do ALTER TABLE v
> ADD PRIMARY KEY (a) first then it succeeds, and you can immediately
> turn around and do ALTER TABLE v DROP CONSTRAINT v_pkey afterwards.
>
> Foreign keys are are a requirement, so this fails:
>
> CREATE TABLE v1 (a int PRIMARY KEY, name text);
> CREATE TABLE v2 (m int PRIMARY KEY, name text);
> CREATE TABLE e (id int PRIMARY KEY, k1 int, k2 int);
> CREATE PROPERTY GRAPH g
> VERTEX TABLES (v1, v2)
> EDGE TABLES (e SOURCE v1 DESTINATION v2);
>
> But if you temporarily add foreign keys with ALTER TABLE e ADD
> CONSTRAINT e_k1_fkey FOREIGN KEY (k1) REFERENCES v1, ADD CONSTRAINT
> e_k2_fkey FOREIGN KEY (k2) REFERENCES v2, then the above command
> succeeds, and you can turn around and drop the foreign keys afterward
> with ALTER TABLE e DROP CONSTRAINT e_k1_fkey.
>

AFAIU SQL/PGQ section 9.12, Syntax rule 4, if an element table
specification does not specify the key, a primary key or a unique key
is used as that element's key. However, if key columns are specified,
the syntax rules do not require them to constitute a unique key or
enforce uniqueness.

AFAIU SQL/PGQ Section 9.14 Syntax rules 9 and 10, if an edge table
specification does not specify the source and destination key columns,
they are inferred from the foreign key constraints of the underlying
table. But if they are specified, the rules do not require them to
constitute a unique key or enforce uniqueness.

Said that Section 9.12 syntax rule 11.c says that the element
descriptor created from the element specification should contain "the
list of columns uniquely identifying a row in ET: ETK.". AFAIU, the
standard expects the user to enforce uniqueness if they explicitly
specify key columns.

The element keys and edge keys in a way are "not enforced" primary key
and foreign key constraints - declarative in nature.

> Or consider this example:
>
> CREATE TABLE t1 (a int PRIMARY KEY, b text);
> CREATE TABLE t2 (a int PRIMARY KEY);
> CREATE PROPERTY GRAPH g
> VERTEX TABLES (t1 LABEL l PROPERTIES ALL COLUMNS,
> t2 LABEL l PROPERTIES ALL COLUMNS);
>
> This fails, because the number of properties doesn't match. If we do
> ALTER TABLE t2 ADD COLUMN b text the this works, and a command like
> SELECT * FROM GRAPH_TABLE (g MATCH (n IS l) COLUMNS (n.a, n.b)) ORDER
> BY a; succeeds. However, afterward we can do ALTER TABLE t2 DROP
> COLUMN b CASCADE (the CASCADE is required), and the same SELECT now
> fails. So once again, we can put the object into a state after
> creation that wouldn't have been valid at creation time.
>

Being able to drop a column of an element table which is referenced in
the property graph is a bug - because of wrong dependency handling. I
will discuss this in a separate email thread about property graph
dependencies.

Please note that PROPERTY ALL COLUMNS does not include columns added
to the element table in future.

--
Best Wishes,
Ashutosh Bapat