public inbox for [email protected]
help / color / mirror / Atom feedFrom: Junwang Zhao <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: Amit Langote <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: Ajay Pal <[email protected]>
Cc: Imran Zaheer <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: SQL Property Graph Queries (SQL/PGQ)
Date: Mon, 7 Apr 2025 16:13:35 +0800
Message-ID: <CAEG8a3JrO--th6N6oQebqAvZZBHShOJ5Z-fZSS8uNJZs-tQ_tQ@mail.gmail.com> (raw)
In-Reply-To: <CAExHW5taUWaDUPKhcX6uu-aXnCpnxPdJh1wLOg+a7Xdbo8zOLA@mail.gmail.com>
References: <[email protected]>
<CAExHW5s3BL_qUwQ=ye8RX7GnVcGC+FeR7h6jFocBZC5JFXATEQ@mail.gmail.com>
<[email protected]>
<CAExHW5vhiawMAEtRwV=X-zQmJeUVY2iyuskWFFKobm_ZvgMf9Q@mail.gmail.com>
<CAExHW5tf9UDzhNWXHyDNBG4DRoq81-Ty4XqcY8wiixt844B0eA@mail.gmail.com>
<CAExHW5uF7KMcABuNVB1de6JRY3ne-jYEP3+8ycQZGkFA=1OTLw@mail.gmail.com>
<CA+UBfakRHrTqTkx8W0cmxGD7zfnZkZ4nczUvp0FGF68LvMSG-A@mail.gmail.com>
<CAExHW5uykgWS=4fqL8eJqCKOQcHgtK89=Nq7eS_av-28ge=LKQ@mail.gmail.com>
<CA+UBfamThQm9FQ7Kv=HQZPDy5CfULpn6yuU0bzPy-kX-=-jV4Q@mail.gmail.com>
<CABRHmytyWxGMaQejiZ28OubtqnOUfzB9nZQSctyvsmxvS_cKCw@mail.gmail.com>
<CABRHmyuvgFEXNgUsxXiSHh01iKcLrqyvcDWeOP+wuXjbMRYkEw@mail.gmail.com>
<CABRHmytkwZNiXEqLKx7r6w3xVL8pCiFLaG8_a=BkqfcuumM1vA@mail.gmail.com>
<CAExHW5vqo7iTcVznspb3HHD87Ps3Q=JF6_gg+h5mhyuwhd3Q4Q@mail.gmail.com>
<CAExHW5serNyfY4v9oy6us=tM7ZyAd3_gYnMjOmRPk8eWEskSog@mail.gmail.com>
<[email protected]>
<CAExHW5tYchoePjt3Z69zSmgMW50_jxNfpUiodRcQABUY6V8oxw@mail.gmail.com>
<CAExHW5vG8sgT7H=Q6V_+aMF3f3vs+F7Mu_3AFrufhD=BPkBP0A@mail.gmail.com>
<CAExHW5tDDwUK0dVqFRyUag8UsABmFNWhz8E6AVPDV2BXJLsY5w@mail.gmail.com>
<CAEG8a3KFWD-a-jRtA__opQQaC9Z=_+rw=Gtydk1zStR-roMwWw@mail.gmail.com>
<CAExHW5sM+ZGVzR1428FsDHuWc-FU2-6zQr5j91KLh6vZaWY0ow@mail.gmail.com>
<CAExHW5t9OkLPv5g68C9=kaduyaz2HZQ2bHzOT3d421Wm4_teuA@mail.gmail.com>
<CAEG8a3KLQ4GLKA7cLNPfMVNMy7-QEGCW4jVz+pa+ErYbgmFh9w@mail.gmail.com>
<CAExHW5soDmT=yuXOqGLuNbp-_p-0rGD_CbAQ0PqROUKNBtJsBw@mail.gmail.com>
<CAEG8a3L_Q8chyLQsOLtfLFfOHRW=sL_akKrtmh334RfjgGvWKg@mail.gmail.com>
<CAExHW5tOUEx_U185oP2ZO+4m9o7Q2KfLKXRg8QJAcNtRj6hj1Q@mail.gmail.com>
<CAEG8a3++kG+q0zWUGYe81VcR+DHw6goLrXiOtU_jP1NQnb+OrA@mail.gmail.com>
<CAExHW5sGs_c1jmKiUSuG5nnKpiXQZCMJo6GC8ihxFGtj-ADFsQ@mail.gmail.com>
<CAEG8a3L3uZZRT5Ra5=9G-SOCEYULejw5eqQE99VL0YfTeX3-BA@mail.gmail.com>
<CAEG8a3K2pjte+zMnv=VD93tkT=K3Fypt-B92xa4OcGZu2L+ciQ@mail.gmail.com>
<CAEG8a3+Bk3POb9yUAo58pnSDKmGWXhLubV4cqzVhjMje4M+BaQ@mail.gmail.com>
<CAExHW5voR_Jq6bFa4HB+V9p61KFzaMfpJUgU90fVAW+2+r0DrQ@mail.gmail.com>
<CA+HiwqH7mMiTAzP+JtdRnNuToK0u0P7kf+De6e79MhZcPSMhMA@mail.gmail.com>
<CAEG8a3LD=7yHAD0ezi3RKOQn8JypFHqjgB1ScNibqNsyRcqkaQ@mail.gmail.com>
<CAEG8a3LvO33=MD0-gHpDyBXQ+kav14p1Wpb4Ao1Doc49rm-i7g@mail.gmail.com>
<CAExHW5vn-iToe56NTV3xNROsRiYcqppm0EFGaBu5_99-Z82jGQ@mail.gmail.com>
<CAEG8a3JCFJ8-TwAZOeDgnjzhxZ4c1vb2RUuMRN=UiCU=6OKw_Q@mail.gmail.com>
<CAExHW5taUWaDUPKhcX6uu-aXnCpnxPdJh1wLOg+a7Xdbo8zOLA@mail.gmail.com>
Hi Ashutosh,
On Mon, Apr 7, 2025 at 1:19 PM Ashutosh Bapat
<[email protected]> wrote:
>
> On Sat, Apr 5, 2025 at 6:20 PM Junwang Zhao <[email protected]> wrote:
> >
> > Hi Ashutosh and Peter,
> >
> > Since this PGQ feature won't be in PG 18, I'd like to raise a discussion of
> > the possibility of implementing the quantifier feature, which I think is a
> > quite useful feature in the graph database area.
>
> I agree that quantifiers feature is very useful; it's being used in
> many usecases. However, it's a bit of a complex feature. IMO, we
> should keep that discussion as well as the patch in a separate thread,
> so that this patchset doesn't grow too large to review and also
> discussion in this thread can remain focused. Once we get the current
> patch set reviewed and committed we can tackle the quantifier problem
> in a separate discussion. Of course that doesn't mean that we can not
> start discussion, try POC and even a working patch for quantifier
> support.
>
> Peter may think otherwise.
>
> >
> > I'll start with a graph definition first.
> >
> > `Person(id, name, age, sex)` with id as PK
> > `Knows(id, start_id, end_id, since)` with id as PK, start_id and
> > end_id FK referencing Person's id
> >
> > insert into Person values(1, 'A', 31, 'M'), (2, 'B', 30, 'F'), (3,
> > 'C', 33, 'M'), (4, 'D', 31, 'F'), (5, 'E', 32, 'M'), (6, 'F', 33,
> > 'M');
> > insert into Knows values (1, 1, 2, '2020'); -- A knows B since 2020
> > insert into Knows values (2, 1, 3, '2021'); -- A knows C since 2021
> > insert into Knows values (3, 1, 4, '2020'); -- A knows D since 2020
> > insert into Knows values (4, 2, 4, '2023'); -- B knows D since 2023
> > insert into Knows values (5, 3, 5, '2022'); -- C knows E since 2022
> > insert into Knows values (6, 2, 6, '2021'); -- B knows F since 2021
> > insert into Knows values (7, 4, 6, '2020'); -- D knows F since 2020
> >
> > Then we create a property graph:
> >
> > CREATE property graph new_graph
> > VERTEX TABLES (Person)
> > EDGE TABLES (Knows);
> >
> > If we want to find A's non-directly known friends within 3 hops, we can query:
> >
> > select name from graph_table (new_graph match (a:Person WHERE a.name =
> > 'A') --> (b:Person) --> (c:Person) COLUMNS (c.name))
> > union
> > select name from graph_table (new_graph match (a:Person WHERE a.name =
> > 'A') --> (b:Person) -->(c:Person)-->(d:Person) COLUMNS (d.name));
> >
> > Or if we support quantifier, we can simply the query as:
> >
> > select name from graph_table (new_graph match (a:Person WHERE a.name =
> > 'A') -->{2,3} (b:Person) COLUMNS (b.name));
> >
> > In the current design of PostgreSQL, we can rewrite this pattern with
> > quantifiers to
> > the union form with some effort.
> >
> > But what if the pattern is more complicated, for example:
> >
> > 1. select name, since from graph_table (new_graph match (a:Person
> > WHERE a.name = 'A') -[r:Knows]->{2,3} (b:Person) COLUMNS (b.name,
> > r.since));
> > Can we support the r.since column? I guess not, in this case r is a
> > variable length edge.
> >
> > 2. select name, count from graph_table (new_graph match (a:Person
> > WHERE a.name = 'A') -[r:Knows]->{2,3} (b:Person) COLUMNS (b.name,
> > count(r)));
> > Can we support this count aggregation(this is called horizontal
> > aggregation in Oracle's pgql)? How can the executor know the length of
> > the variable length edge?
> >
> > 3. What if the query doesn't specify the Label of edge, and there can
> > be different edge labels of r, can we easily do the rewrite?
> >
> > I did some study of the apache age, they have fixed columns for node
> > labels(id, agtype)
> > and edge labels(id, source_id, end_id, agtype), agtype is kind of
> > json. So they can
> > resolve the above question easily.
> >
> > Above are just my random thoughts of the quantifier feature, I don't have a copy
> > of the PGQ standard, so I'd like to hear your opinion about this.
> >
>
> I think the questions you have raised are valid. If we decide to
> discuss this in a separate thread, I will start that thread just by
> responding to these questions and design I have in mind.
I'm ok with starting a new thread for quantifier discussion, and I'd
really happy to know your design on this.
>
> --
> Best Wishes,
> Ashutosh Bapat
--
Regards
Junwang Zhao
view thread (37+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL Property Graph Queries (SQL/PGQ)
In-Reply-To: <CAEG8a3JrO--th6N6oQebqAvZZBHShOJ5Z-fZSS8uNJZs-tQ_tQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox