agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19548: Missing dependency between a graph edge and the related PK
4+ messages / 4 participants
[nested] [flat]
* BUG #19548: Missing dependency between a graph edge and the related PK
@ 2026-07-08 14:44 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-07-08 14:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: christophe.courtois@dalibo.com
The following bug has been logged on the website:
Bug reference: 19548
Logged by: Christophe Courtois
Email address: christophe.courtois@dalibo.com
PostgreSQL version: 19beta1
Operating system: Linux Debian 13
Description:
Hi,
A dependency between a graph and the PK of a relationship
seems to be missing.
In the following example, a PK on the edge table is compulsory,
but this PK can be cascade-dropped and the graph is unchanged.
The graph can be dropped later,
but it cannot be recreated :
ERROR: no key specified and no suitable primary key exists for definition
of element "family"
I imagine that a pg_restore will fail too.
Tested on 19~beta2-1~20260629.2015.g9cfd19bc10a.pgdg13+1 from
apt.postgresql.org
and 20devel freshly compiled.
Full example :
-- persons
CREATE TABLE persons (
id TEXT PRIMARY KEY,
nom TEXT,
sexe TEXT
);
CREATE TABLE family (
id TEXT PRIMARY KEY,
spouse1 TEXT REFERENCES persons(id),
spouse2 TEXT REFERENCES persons(id)
);
CREATE PROPERTY GRAPH wedding
VERTEX TABLES (
persons KEY (id) PROPERTIES ALL COLUMNS
)
EDGE TABLES (
family
SOURCE KEY (spouse1) REFERENCES persons (id)
DESTINATION KEY (spouse2) REFERENCES persons (id)
);
-- Drop the constraint
-- CASCADE does NOT get rid if the graph
ALTER TABLE family DROP CONSTRAINT family_pkey CASCADE ;
-- The graph is still there
\dG+
-- Recreation fails
DROP PROPERTY GRAPH wedding ;
CREATE PROPERTY GRAPH wedding
VERTEX TABLES (
persons KEY (id) PROPERTIES ALL COLUMNS
)
EDGE TABLES (
family
SOURCE KEY (spouse1) REFERENCES persons (id)
DESTINATION KEY (spouse2) REFERENCES persons (id)
);
ERROR: no key specified and no suitable primary key exists for definition
of element "family"
LINE 6: family
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19548: Missing dependency between a graph edge and the related PK
@ 2026-07-16 12:13 Ayoub Kazar <kazarayoub2004@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 4+ messages in thread
From: Ayoub Kazar @ 2026-07-16 12:13 UTC (permalink / raw)
To: christophe.courtois@dalibo.com; pgsql-bugs@lists.postgresql.org
Hi Christophe,
On Thu, Jul 16, 2026 at 12:38 PM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19548
> Logged by: Christophe Courtois
> Email address: christophe.courtois@dalibo.com
> PostgreSQL version: 19beta1
> Operating system: Linux Debian 13
> Description:
>
> Hi,
>
> A dependency between a graph and the PK of a relationship
> seems to be missing.
>
> In the following example, a PK on the edge table is compulsory,
> but this PK can be cascade-dropped and the graph is unchanged.
>
> The graph can be dropped later,
> but it cannot be recreated :
> ERROR: no key specified and no suitable primary key exists for definition
> of element "family"
>
> I imagine that a pg_restore will fail too.
>
> Tested on 19~beta2-1~20260629.2015.g9cfd19bc10a.pgdg13+1 from
> apt.postgresql.org
> and 20devel freshly compiled.
>
> Full example :
>
> -- persons
> CREATE TABLE persons (
> id TEXT PRIMARY KEY,
> nom TEXT,
> sexe TEXT
> );
>
> CREATE TABLE family (
> id TEXT PRIMARY KEY,
> spouse1 TEXT REFERENCES persons(id),
> spouse2 TEXT REFERENCES persons(id)
> );
>
> CREATE PROPERTY GRAPH wedding
> VERTEX TABLES (
> persons KEY (id) PROPERTIES ALL COLUMNS
> )
> EDGE TABLES (
> family
> SOURCE KEY (spouse1) REFERENCES persons (id)
> DESTINATION KEY (spouse2) REFERENCES persons (id)
> );
>
> -- Drop the constraint
> -- CASCADE does NOT get rid if the graph
>
> ALTER TABLE family DROP CONSTRAINT family_pkey CASCADE ;
>
> -- The graph is still there
>
> \dG+
>
> -- Recreation fails
>
> DROP PROPERTY GRAPH wedding ;
>
> CREATE PROPERTY GRAPH wedding
> VERTEX TABLES (
> persons KEY (id) PROPERTIES ALL COLUMNS
> )
> EDGE TABLES (
> family
> SOURCE KEY (spouse1) REFERENCES persons (id)
> DESTINATION KEY (spouse2) REFERENCES persons (id)
> );
> ERROR: no key specified and no suitable primary key exists for definition
> of element "family"
> LINE 6: family
>
>
>
>
>
>
>
I checked with Oracle as well to see the behavior and i can confirm its the
same (its a standard thing, see below).
The KEY, either explicit or implicit through the PK is used just at graph
definition time: the catalog just keeps the column numbers in the relative
graph element table (in your case, the family table). There's no dependency
kept.
See:
select * from pg_propgraph_element;
oid | pgepgid | pgerelid | pgealias | pgekind | pgesrcvertexid |
pgedestvertexid | pgekey | pgesrckey | pgesrcref | pgesrceqop | pgedestkey
| pgedestref | pgedesteqop
-------+---------+----------+----------+---------+----------------+-----------------+--------+-----------+-----------+------------+------------+------------+-------------
25282 | 25281 | 25259 | persons | v | 0 |
0 | {1} | | | | |
|
25291 | 25281 | 25265 | family | e | 25282 |
25282 | {1} | {2} | {1} | {96} | {3} | {1}
| {96}
(2 rows)
If a PK was used (your case), it's to deduce what columns uniquely identify
the edge table. What's required is either a KEY clause or an existing PK on
the element table.
The standard itself doesn't mention keeping the dependency (i argue it
should); it only talks about the different cases of the graph element
table's key.
Regards,
Ayoub Kazar
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19548: Missing dependency between a graph edge and the related PK
@ 2026-07-17 14:25 Peter Eisentraut <peter@eisentraut.org>
parent: Ayoub Kazar <kazarayoub2004@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2026-07-17 14:25 UTC (permalink / raw)
To: Ayoub Kazar <kazarayoub2004@gmail.com>; christophe.courtois@dalibo.com; pgsql-bugs@lists.postgresql.org
On 16.07.26 14:13, Ayoub Kazar wrote:
> I checked with Oracle as well to see the behavior and i can confirm its
> the same (its a standard thing, see below).
>
> The KEY, either explicit or implicit through the PK is used just at
> graph definition time: the catalog just keeps the column numbers in the
> relative graph element table (in your case, the family table). There's
> no dependency kept.
Correct. The current behavior is intentional.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19548: Missing dependency between a graph edge and the related PK
@ 2026-07-17 15:27 Christophe Courtois <christophe.courtois@dalibo.com>
parent: Ayoub Kazar <kazarayoub2004@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Christophe Courtois @ 2026-07-17 15:27 UTC (permalink / raw)
To: Ayoub Kazar <kazarayoub2004@gmail.com>; pgsql-bugs@lists.postgresql.org
Le 16/07/2026 à 14:13, Ayoub Kazar a écrit :
> Hi Christophe,
>
> On Thu, Jul 16, 2026 at 12:38 PM PG Bug reporting form
> <noreply@postgresql.org <mailto:noreply@postgresql.org>> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19548
> Logged by: Christophe Courtois
> Email address: christophe.courtois@dalibo.com
> <mailto:christophe.courtois@dalibo.com>
> PostgreSQL version: 19beta1
> Operating system: Linux Debian 13
> Description:
>
> Hi,
>
> A dependency between a graph and the PK of a relationship
> seems to be missing.
>
> In the following example, a PK on the edge table is compulsory,
> but this PK can be cascade-dropped and the graph is unchanged.
>
> The graph can be dropped later,
> but it cannot be recreated :
> ERROR: no key specified and no suitable primary key exists for
> definition
> of element "family"
>
> I imagine that a pg_restore will fail too.
>
> Tested on 19~beta2-1~20260629.2015.g9cfd19bc10a.pgdg13+1 from
> apt.postgresql.org <http://apt.postgresql.org;
> and 20devel freshly compiled.
>
> Full example :
>
> -- persons
> CREATE TABLE persons (
> id TEXT PRIMARY KEY,
> nom TEXT,
> sexe TEXT
> );
>
> CREATE TABLE family (
> id TEXT PRIMARY KEY,
> spouse1 TEXT REFERENCES persons(id),
> spouse2 TEXT REFERENCES persons(id)
> );
>
> CREATE PROPERTY GRAPH wedding
> VERTEX TABLES (
> persons KEY (id) PROPERTIES ALL COLUMNS
> )
> EDGE TABLES (
> family
> SOURCE KEY (spouse1) REFERENCES persons (id)
> DESTINATION KEY (spouse2) REFERENCES persons (id)
> );
>
> -- Drop the constraint
> -- CASCADE does NOT get rid if the graph
>
> ALTER TABLE family DROP CONSTRAINT family_pkey CASCADE ;
>
> -- The graph is still there
>
> \dG+
>
> -- Recreation fails
>
> DROP PROPERTY GRAPH wedding ;
>
> CREATE PROPERTY GRAPH wedding
> VERTEX TABLES (
> persons KEY (id) PROPERTIES ALL COLUMNS
> )
> EDGE TABLES (
> family
> SOURCE KEY (spouse1) REFERENCES persons (id)
> DESTINATION KEY (spouse2) REFERENCES persons (id)
> );
> ERROR: no key specified and no suitable primary key exists for
> definition
> of element "family"
> LINE 6: family
>
>
>
>
>
>
>
> I checked with Oracle as well to see the behavior and i can confirm its
> the same (its a standard thing, see below).
>
> The KEY, either explicit or implicit through the PK is used just at
> graph definition time: the catalog just keeps the column numbers in the
> relative graph element table (in your case, the family table). There's
> no dependency kept.
>
> See:
>
> select * from pg_propgraph_element;
> oid | pgepgid | pgerelid | pgealias | pgekind | pgesrcvertexid |
> pgedestvertexid | pgekey | pgesrckey | pgesrcref | pgesrceqop |
> pgedestkey | pgedestref | pgedesteqop
> -------+---------+----------+----------+---------+----------------
> +-----------------+--------+-----------+-----------+------------
> +------------+------------+-------------
> 25282 | 25281 | 25259 | persons | v | 0 |
> 0 | {1} | | | |
> | |
> 25291 | 25281 | 25265 | family | e | 25282 |
> 25282 | {1} | {2} | {1} | {96} | {3}
> | {1} | {96}
> (2 rows)
>
>
> If a PK was used (your case), it's to deduce what columns uniquely
> identify the edge table. What's required is either a KEY clause or an
> existing PK on the element table.
>
> The standard itself doesn't mention keeping the dependency (i argue it
> should); it only talks about the different cases of the graph element
> table's key.
Thanks for the explanation!
In fact I can see in pg_get_propgraphdef() that the graph definition is
correctly stored with a KEY (id).
I should have checked the result of pg_dump/pg_restore,
I would have probably found this explanation.
Sorry for the noise.
Yours,
SELECT pg_get_propgraphdef ('wedding'::regclass);
pg_get_propgraphdef
----------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROPERTY GRAPH public.wedding
+
VERTEX TABLES (
+
persons KEY (id) PROPERTIES (id, nom, sexe)
+
)
+
EDGE TABLES (
+
family KEY (id) SOURCE KEY (spouse1) REFERENCES persons (id)
DESTINATION KEY (spouse2) REFERENCES persons (id) PROPERTIES (id,
spouse1, spouse2)+
)
--
_________ ____
| || | Christophe Courtois
| ||__ | Consultant DALIBO
| | | | 43, rue du Faubourg Montmartre
| - | / / 75009 Paris
|___| |___| \/ www.dalibo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-07-17 15:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-08 14:44 BUG #19548: Missing dependency between a graph edge and the related PK PG Bug reporting form <noreply@postgresql.org>
2026-07-16 12:13 ` Ayoub Kazar <kazarayoub2004@gmail.com>
2026-07-17 14:25 ` Peter Eisentraut <peter@eisentraut.org>
2026-07-17 15:27 ` Christophe Courtois <christophe.courtois@dalibo.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox