MIME-Version: 1.0
References: <a855795d-e697-4fa5-8698-d20122126567@eisentraut.org>
 <20240216192301.q2tf4bqjydymg3g2@awork3.anarazel.de> <9c5edd61-c415-475b-a088-7bd7461b21a4@eisentraut.org>
 <5fa7f8a7-ce94-4a2a-ab93-4d43f97a1a41@enterprisedb.com> <CAExHW5vv9roBJq=CkJJY=z0AO7G4zxTCYXeMK07AdnHARXnOsQ@mail.gmail.com>
 <CAExHW5s3BL_qUwQ=ye8RX7GnVcGC+FeR7h6jFocBZC5JFXATEQ@mail.gmail.com>
 <04c0abe2-e9c5-4ffc-97b2-bedc1e3cd178@eisentraut.org> <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>
In-Reply-To: <CABRHmytyWxGMaQejiZ28OubtqnOUfzB9nZQSctyvsmxvS_cKCw@mail.gmail.com>
From: Ajay Pal <ajay.pal.k@gmail.com>
Date: Tue, 13 Aug 2024 16:08:07 +0530
Message-ID: <CABRHmyuvgFEXNgUsxXiSHh01iKcLrqyvcDWeOP+wuXjbMRYkEw@mail.gmail.com>
Subject: Re: SQL Property Graph Queries (SQL/PGQ)
To: Imran Zaheer <imran.zhir@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="00000000000036439c061f8e3545"
Archived-At: <https://www.postgresql.org/message-id/CABRHmyuvgFEXNgUsxXiSHh01iKcLrqyvcDWeOP%2BwuXjbMRYkEw%40mail.gmail.com>
Precedence: bulk

--00000000000036439c061f8e3545
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello,

Further testing found that using a property graph with the plpgsql
function crashed the server. Please take a look at the attached SQL
file for reference tables.

postgres=3D# create or replace function func() returns int as
postgres-# $$
postgres$# declare person_av varchar;
postgres$# begin
postgres$#
postgres$#         SELECT person_a into person_av FROM GRAPH_TABLE
(students_graph
postgres$#           MATCH
postgres$#           (a IS person) -[e IS friends]-> (b IS person
WHERE b.name =3D 'Bob')
postgres$#           WHERE a.name=3D'John'
postgres$#           COLUMNS (a.name AS person_a, b.name AS person_b)
postgres$#         );
postgres$#
postgres$#         return person_av;
postgres$# end
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=3D# select func();
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
!?>

Please let me know if you need more details.

Thanks
Ajay

On Tue, Aug 13, 2024 at 3:22=E2=80=AFPM Ajay Pal <ajay.pal.k@gmail.com> wro=
te:
>
> Hello,
>
> With the attached patch found below error when try to use "Any
> directed edge" syntax.
>
> postgres=3D# SELECT * FROM GRAPH_TABLE (students_graph
> postgres(#   MATCH
> postgres(#   (a IS person )  - [] - (b IS person)
> postgres(#   COLUMNS (a.name AS person_a, b.name AS person_b)
> postgres(# );
> ERROR:  unsupported element pattern kind: undirected edge
>
> If this syntax is supported then should behave as below,
>
> PERSON_A   PERSON_B
> ---------- ----------
> Bob    John
> John    Mary
> Alice    Mary
> Mary    Bob
> Mary    John
> Bob    Mary
> John    Bob
> Mary    Alice
>
> 8 rows selected.
>
> Attaching the sql file for reference.
>
> Thanks
> Ajay
>
> On Sat, Aug 10, 2024 at 2:52=E2=80=AFPM Imran Zaheer <imran.zhir@gmail.co=
m> wrote:
> >
> > Hi Ashutosh,
> >
> > Thanks for the feedback.
> >
> > > Do you intend to support EXCEPT in the label expression as well or
> > > just properties?
> > >
> >
> > I only implemented it for the properties because I couldn't find any
> > example for Label expression using EXCEPT clause. So I thought it was
> > only meant to be for the properties.
> > But if you can confirm that we do use EXCEPT clauses with label
> > expressions as well then I can try supporting that too.
> >
> > >
> > > Please do not top-post on hackers.
> > >
> > > Always sent the whole patchset. Otherwise, CI bot gets confused. It
> > > doesn't pick up patchset from the previous emails.
> > >
> > Okay, I will take care of that.
> >
> > > About the functionality: It's not clear to me whether an EXCEPT shoul=
d
> > > be applicable only at the time of property graph creation or it shoul=
d
> > > be applicable always. I.e. when a property graph is dumped, should it
> > > have EXCEPT in it or have a list of columns surviving except list?
> > > What if a column in except list is dropped after creating a property
> > > graph?
> > >
> >
> > I did some testing on that,  for now we are just dumping the columns
> > surviving the except list.
> > If an exceptional table column is deleted afterwards it doesn't show
> > any effect on the graph. I also tested this scenario with duckdb pgq
> > extension [1], deleting the col doesn't affect the graph.
> >
> > > Some comments on the code
> >
> > I am attaching a new patch after trying to fix according to you comment=
s
> >
> > > 1. You could use list_member() in insert_property_records() to check
> > > whether a given column is in the list of exceptions after you have
> > > enveloped in String node.
> >
> > * I have changed to code to use list_member(), but I have to make
> > ResTarget->name from `pstrdup(NameStr(att->attname));` to `NULL`
> > We are using `xml_attribute_list` for our columns list and while
> > making this list in gram.y we are assigning `rt->name` as NULL [2],
> > this causes list_member() func to fail while comparing except_list
> > nodes. That's why I am changing rt->name from string value to NULL in
> > propgraphcmds.c in this patch.
> >
> > * Also, in order to use list_member() func I have to add a separate
> > for loop to iterate through the exceptional columns to generate the
> > error message if col is not valid. My question is, is it ok to use two
> > separate for loops (one to check except cols validity &
> > other(list_memeber) to check existence of scanned col in except list).
> > In the previous patch I was using single for loop to validate both
> > things.
> >
> > > 2. The SELECT with GRAPH_TABLE queries are tested in graph_table.sql.
> > > We don't include those in create_property_graph.sql
> >
> > * I have moved the graph_table queries from create_property_graph.sql
> > to graph_table.sql.
> > * But in graph_table.sql I didn't use the existing graphs because
> > those graphs and tables look like there for some specific test
> > scenario, so I created my separate graph and table for my test
> > scenario. I didn't drop the graph and the table as we will be dropping
> > the schema at the end but Peter E has this comment "-- leave for
> > pg_upgrade/pg_dump tests".
> >
> > > 3. Instead of creating a new property graph in the test, you may
> > > modify one of the existing property graphs to have a label with excep=
t
> > > list and then query it.
> > >
> >
> > * I have modified the graphs in create_property_graph.sql in order to
> > test except list cols in the alter command and create graph command.
> >
> > > We are aiming a minimal set of features in the first version. I will
> > > let Peter E. decide whether to consider this as minimal set feature o=
r
> > > not. The feature looks useful to me.
> >
> > Thanks if you find this patch useful. I am attaching the modified patch=
.
> >
> > > 0001 - same as previous one
> > > 0002 - fixes pgperltidy complaints
> > > 0003 - fixes compilation failure
> > > 0004 - fixes issue seen on CI
> > > 0005 - adds support for WHERE clause in graph pattern missing in the
> > > first patch.
> > > 0006 - adds full support for cyclic path patterns
> >
> > 0007 - adds support for except cols list in graph properties
> >
> > Thanks
> > Imran Zaheer
> >
> > [1]: https://github.com/cwida/duckpgq-extension
> > [2]: https://github.com/postgres/postgres/blob/f5a1311fccd2ed24a9fb42aa=
47a17d1df7126039/src/backend/parser/gram.y#L16166

--00000000000036439c061f8e3545
Content-Type: application/octet-stream; name="testcase.sql"
Content-Disposition: attachment; filename="testcase.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_lzsafxbb0>
X-Attachment-Id: f_lzsafxbb0

CgoKQ1JFQVRFIFRBQkxFIHVuaXZlcnNpdHkgKAogICAgaWQgaW50ZWdlciBHRU5FUkFURUQgQUxX
QVlTIEFTIElERU5USVRZIChTVEFSVCBXSVRIIDEgSU5DUkVNRU5UIEJZIDEpLAogICAgbmFtZSB2
YXJjaGFyKDEwKSwKICAgIENPTlNUUkFJTlQgdV9wayBQUklNQVJZIEtFWSAoaWQpKTsKCklOU0VS
VCBJTlRPIHVuaXZlcnNpdHkgKG5hbWUpIFZBTFVFUyAoJ0FCQycpOwpJTlNFUlQgSU5UTyB1bml2
ZXJzaXR5IChuYW1lKSBWQUxVRVMgKCdYWVonKTsKCkNSRUFURSBUQUJMRSBwZXJzb25zICgKICAg
ICBwZXJzb25faWQgaW50ZWdlciBHRU5FUkFURUQgQUxXQVlTIEFTIElERU5USVRZIChTVEFSVCBX
SVRIIDEgSU5DUkVNRU5UCiAgICAgQlkgMSksCiAgICAgbmFtZSB2YXJjaGFyKDEwKSwKICAgICBi
aXJ0aGRhdGUgREFURSwKICAgICBoZWlnaHQgaW50ZWdlciAsCiAgICAgaHJfZGF0YSBKU09OLAog
ICAgIENPTlNUUkFJTlQgcGVyc29uX3BrIFBSSU1BUlkgS0VZIChwZXJzb25faWQpCiAgICk7CgpJ
TlNFUlQgSU5UTyBwZXJzb25zIChuYW1lLCBoZWlnaHQsIGJpcnRoZGF0ZSwgaHJfZGF0YSkgVkFM
VUVTICgnSm9obicsIDEuODAsIHRvX2RhdGUoJzEzLzA2LzE5NjMnLCAnREQvTU0vWVlZWScpLCAn
eyJkZXBhcnRtZW50IjoiSVQiLCJyb2xlIjoiU29mdHdhcmUgRGV2ZWxvcGVyIn0nKTsKCklOU0VS
VCBJTlRPIHBlcnNvbnMgKG5hbWUsIGhlaWdodCwgYmlydGhkYXRlLCBocl9kYXRhKSBWQUxVRVMg
KCdNYXJ5JywgMS42NSwgdG9fZGF0ZSgnMjUvMDkvMTk4MicsICdERC9NTS9ZWVlZJyksICd7ImRl
cGFydG1lbnQiOiJIUiIsInJvbGUiOiJIUiBNYW5hZ2VyIn0nKTsKCklOU0VSVCBJTlRPIHBlcnNv
bnMgKG5hbWUsIGhlaWdodCwgYmlydGhkYXRlLCBocl9kYXRhKSBWQUxVRVMgKCdCb2InLCAxLjc1
LCB0b19kYXRlKCcxMS8wMy8xOTY2JywgJ0REL01NL1lZWVknKSwgJ3siZGVwYXJ0bWVudCI6IklU
Iiwicm9sZSI6IlRlY2huaWNhbCBDb25zdWx0YW50In0nKTsKCklOU0VSVCBJTlRPIHBlcnNvbnMg
KG5hbWUsIGhlaWdodCwgYmlydGhkYXRlLCBocl9kYXRhKSBWQUxVRVMgKCdBbGljZScsIDEuNzAs
IHRvX2RhdGUoJzAxLzAyLzE5ODcnLCAnREQvTU0vWVlZWScpLCAneyJkZXBhcnRtZW50IjoiSFIi
LCJyb2xlIjoiSFIgQXNzaXN0YW50In0nKTsKCkNSRUFURSBUQUJMRSBzdHVkZW50cyAoCiAgICAg
IHNfaWQgaW50ZWdlciBHRU5FUkFURUQgQUxXQVlTIEFTIElERU5USVRZIChTVEFSVCBXSVRIIDEg
SU5DUkVNRU5UIEJZIDEpLAogICAgICBzX3VuaXZfaWQgaW50ZWdlciwKICAgICAgc19wZXJzb25f
aWQgaW50ZWdlciwKICAgICAgc3ViamVjdCBWQVJDSEFSKDEwKSwKICAgICAgaGVpZ2h0IGludGVn
ZXIsCiAgICAgIENPTlNUUkFJTlQgc3R1ZF9wazEgUFJJTUFSWSBLRVkgKHNfaWQpCiAgICAgIC0t
LENPTlNUUkFJTlQgc3R1ZF9ma19wZXJzb24xIEZPUkVJR04gS0VZIChzX3BlcnNvbl9pZCkgUkVG
RVJFTkNFUyBwZXJzb25zKHBlcnNvbl9pZCksCiAgICAgIC0tQ09OU1RSQUlOVCBzdHVkX2ZrX3Vu
aXYxIEZPUkVJR04gS0VZIChzX3VuaXZfaWQpIFJFRkVSRU5DRVMgdW5pdmVyc2l0eShpZCkKICAg
ICk7CgoKSU5TRVJUIElOVE8gc3R1ZGVudHMoc191bml2X2lkLCBzX3BlcnNvbl9pZCxzdWJqZWN0
LCBoZWlnaHQpIFZBTFVFUyAoMSwxLCdBcnRzJywxLjgwKTsKSU5TRVJUIElOVE8gc3R1ZGVudHMo
c191bml2X2lkLCBzX3BlcnNvbl9pZCxzdWJqZWN0LCBoZWlnaHQpIFZBTFVFUyAoMSwzLCdNdXNp
YycsMS42NSk7CklOU0VSVCBJTlRPIHN0dWRlbnRzKHNfdW5pdl9pZCwgc19wZXJzb25faWQsc3Vi
amVjdCwgaGVpZ2h0KSBWQUxVRVMgKDIsMiwnTWF0aCcsMS43NSk7CklOU0VSVCBJTlRPIHN0dWRl
bnRzKHNfdW5pdl9pZCwgc19wZXJzb25faWQsc3ViamVjdCwgaGVpZ2h0KSBWQUxVRVMgKDIsNCwn
U2NpZW5jZScsMS43MCk7CgoKQ1JFQVRFIFRBQkxFIHN0dWRlbnRfb2YgKAogICAgICBzX2lkIGlu
dGVnZXIgR0VORVJBVEVEIEFMV0FZUyBBUyBJREVOVElUWSAoU1RBUlQgV0lUSCAxIElOQ1JFTUVO
VCBCWSAxKSwKICAgICAgc191bml2X2lkIGludGVnZXIsCiAgICAgIHNfcGVyc29uX2lkIGludGVn
ZXIsCiAgICAgIHN1YmplY3QgdmFyY2hhcigxMCksCiAgICAgIENPTlNUUkFJTlQgc3R1ZF9wayBQ
UklNQVJZIEtFWSAoc19pZCksCiAgICAgIENPTlNUUkFJTlQgc3R1ZF9ma19wZXJzb24gRk9SRUlH
TiBLRVkgKHNfcGVyc29uX2lkKSBSRUZFUkVOQ0VTIHBlcnNvbnMocGVyc29uX2lkKSwKICAgICAg
Q09OU1RSQUlOVCBzdHVkX2ZrX3VuaXYgRk9SRUlHTiBLRVkgKHNfdW5pdl9pZCkgUkVGRVJFTkNF
UyB1bml2ZXJzaXR5KGlkKQogICAgKTsKCklOU0VSVCBJTlRPIHN0dWRlbnRfb2Yoc191bml2X2lk
LCBzX3BlcnNvbl9pZCxzdWJqZWN0KSBWQUxVRVMgKDEsMSwnQXJ0cycpOwpJTlNFUlQgSU5UTyBz
dHVkZW50X29mKHNfdW5pdl9pZCwgc19wZXJzb25faWQsc3ViamVjdCkgVkFMVUVTICgxLDMsJ011
c2ljJyk7CklOU0VSVCBJTlRPIHN0dWRlbnRfb2Yoc191bml2X2lkLCBzX3BlcnNvbl9pZCxzdWJq
ZWN0KSBWQUxVRVMgKDIsMiwnTWF0aCcpOwpJTlNFUlQgSU5UTyBzdHVkZW50X29mKHNfdW5pdl9p
ZCwgc19wZXJzb25faWQsc3ViamVjdCkgVkFMVUVTICgyLDQsJ1NjaWVuY2UnKTsKCkNSRUFURSBU
QUJMRSBmcmllbmRzICgKICAgIGZyaWVuZHNoaXBfaWQgaW50ZWdlciBHRU5FUkFURUQgQUxXQVlT
IEFTIElERU5USVRZIChTVEFSVCBXSVRIIDEgSU5DUkVNRU5UIEJZIDEpLAogICAgcGVyc29uX2Eg
aW50ZWdlciwKICAgIHBlcnNvbl9iIGludGVnZXIsCiAgICBtZWV0aW5nX2RhdGUgREFURSwKICAg
IENPTlNUUkFJTlQgZmtfcGVyc29uX2FfaWQgRk9SRUlHTiBLRVkgKHBlcnNvbl9hKSBSRUZFUkVO
Q0VTIHBlcnNvbnMocGVyc29uX2lkKSwKICAgIENPTlNUUkFJTlQgZmtfcGVyc29uX2JfaWQgRk9S
RUlHTiBLRVkgKHBlcnNvbl9iKSBSRUZFUkVOQ0VTIHBlcnNvbnMocGVyc29uX2lkKSwKICAgIENP
TlNUUkFJTlQgZnNfcGsgUFJJTUFSWSBLRVkgKGZyaWVuZHNoaXBfaWQpCik7CgpJTlNFUlQgSU5U
TyBmcmllbmRzIChwZXJzb25fYSwgcGVyc29uX2IsIG1lZXRpbmdfZGF0ZSkgVkFMVUVTICgxLCAz
LCB0b19kYXRlKCcwMS8wOS8yMDAwJywgJ0REL01NL1lZWVknKSk7CklOU0VSVCBJTlRPIGZyaWVu
ZHMgKHBlcnNvbl9hLCBwZXJzb25fYiwgbWVldGluZ19kYXRlKSBWQUxVRVMgKDIsIDQsIHRvX2Rh
dGUoJzE5LzA5LzIwMDAnLCAnREQvTU0vWVlZWScpKTsKSU5TRVJUIElOVE8gZnJpZW5kcyAocGVy
c29uX2EsIHBlcnNvbl9iLCBtZWV0aW5nX2RhdGUpIFZBTFVFUyAoMiwgMSwgdG9fZGF0ZSgnMTkv
MDkvMjAwMCcsICdERC9NTS9ZWVlZJykpOwpJTlNFUlQgSU5UTyBmcmllbmRzIChwZXJzb25fYSwg
cGVyc29uX2IsIG1lZXRpbmdfZGF0ZSkgVkFMVUVTICgzLCAyLCB0b19kYXRlKCcxMC8wNy8yMDAx
JywgJ0REL01NL1lZWVknKSk7CgoKQ1JFQVRFIFBST1BFUlRZIEdSQVBIIHN0dWRlbnRzX2dyYXBo
CiAgVkVSVEVYIFRBQkxFUyAoCiAgICBwZXJzb25zIEtFWSAocGVyc29uX2lkKQogICAgICBMQUJF
TCBwZXJzb24KICAgICAgICBQUk9QRVJUSUVTIChwZXJzb25faWQsIG5hbWUsIGJpcnRoZGF0ZSBB
UyBkb2IpCiAgICAgIExBQkVMIHBlcnNvbl9odAogICAgICAgIFBST1BFUlRJRVMgKGhlaWdodCks
CiAgICB1bml2ZXJzaXR5IEtFWSAoaWQpCiAgKQogIEVER0UgVEFCTEVTICgKICAgIGZyaWVuZHMK
ICAgICAgS0VZIChmcmllbmRzaGlwX2lkKQogICAgICBTT1VSQ0UgS0VZIChwZXJzb25fYSkgUkVG
RVJFTkNFUyBwZXJzb25zKHBlcnNvbl9pZCkKICAgICAgREVTVElOQVRJT04gS0VZIChwZXJzb25f
YikgUkVGRVJFTkNFUyBwZXJzb25zKHBlcnNvbl9pZCkKICAgICAgUFJPUEVSVElFUyAoZnJpZW5k
c2hpcF9pZCwgbWVldGluZ19kYXRlKSwKICAgIHN0dWRlbnRfb2YKICAgICAgU09VUkNFIEtFWSAo
c19wZXJzb25faWQpIFJFRkVSRU5DRVMgcGVyc29ucyhwZXJzb25faWQpCiAgICAgIERFU1RJTkFU
SU9OIEtFWSAoc191bml2X2lkKSBSRUZFUkVOQ0VTIHVuaXZlcnNpdHkoaWQpCiAgICAgIFBST1BF
UlRJRVMgKHN1YmplY3QpCiAgKTsKCgpTRUxFQ1QgKiBGUk9NIEdSQVBIX1RBQkxFIChzdHVkZW50
c19ncmFwaAogIE1BVENICiAgKGEgSVMgcGVyc29uICkgIC0gW10gLSAoYiBJUyBwZXJzb24pCiAg
Q09MVU1OUyAoYS5uYW1lIEFTIHBlcnNvbl9hLCBiLm5hbWUgQVMgcGVyc29uX2IpCik7Cg==
--00000000000036439c061f8e3545--