pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Christophe Courtois <christophe.courtois@dalibo.com>
To: Ayoub Kazar <kazarayoub2004@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19548: Missing dependency between a graph edge and the related PK
Date: Fri, 17 Jul 2026 17:27:45 +0200
Message-ID: <bfdf7b5c-2593-4608-8ba6-b3a8d663166c@dalibo.com> (raw)
In-Reply-To: <CADu+CpRB4nZ-c59Xx30jfW5Vn+3JWSc_b0_+S1GQiwhiLBo9zQ@mail.gmail.com>
References: <19548-6cac2e96468d7cb8@postgresql.org>
<CADu+CpRB4nZ-c59Xx30jfW5Vn+3JWSc_b0_+S1GQiwhiLBo9zQ@mail.gmail.com>
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
view thread (4+ messages)
Message-ID: <bfdf7b5c-2593-4608-8ba6-b3a8d663166c@dalibo.com>
Permalink: ../bfdf7b5c-2593-4608-8ba6-b3a8d663166c@dalibo.com/
Also on: postgresql.org/message-id/bfdf7b5c-2593-4608-8ba6-b3a8d663166c@dalibo.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: christophe.courtois@dalibo.com, kazarayoub2004@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19548: Missing dependency between a graph edge and the related PK
In-Reply-To: <bfdf7b5c-2593-4608-8ba6-b3a8d663166c@dalibo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox