MIME-Version: 1.0
Date: Tue, 25 Feb 2025 16:47:39 +0700
From: Vladlen Popolitov <v.popolitov@postgrespro.ru>
To: Junwang Zhao <zhjwpku@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Vik Fearing
 <vik@postgresfriends.org>, Ajay Pal <ajay.pal.k@gmail.com>, Imran Zaheer
 <imran.zhir@gmail.com>, Peter Eisentraut <peter@eisentraut.org>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: SQL Property Graph Queries (SQL/PGQ)
In-Reply-To: <CAEG8a3K2pjte+zMnv=VD93tkT=K3Fypt-B92xa4OcGZu2L+ciQ@mail.gmail.com>
References: <a855795d-e697-4fa5-8698-d20122126567@eisentraut.org>
 <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>
 <CABRHmyuvgFEXNgUsxXiSHh01iKcLrqyvcDWeOP+wuXjbMRYkEw@mail.gmail.com>
 <CABRHmytkwZNiXEqLKx7r6w3xVL8pCiFLaG8_a=BkqfcuumM1vA@mail.gmail.com>
 <CAExHW5vqo7iTcVznspb3HHD87Ps3Q=JF6_gg+h5mhyuwhd3Q4Q@mail.gmail.com>
 <CAExHW5serNyfY4v9oy6us=tM7ZyAd3_gYnMjOmRPk8eWEskSog@mail.gmail.com>
 <3b190de8-910a-4091-82f9-e98cf562d832@postgresfriends.org>
 <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>
Message-ID: <ec97368444e41688bbbfcb5dd6a1663b@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/ec97368444e41688bbbfcb5dd6a1663b%40postgrespro.ru>
Precedence: bulk

Hi!

  I would ask about CREATE GRAPH PROPERTY. From my point of view it makes 
very strong
check of types including check of typmod.

  Example:

CREATE TABLE userslist (
   user_id INT primary key,
   name   VARCHAR(40)
);

CREATE TABLE groupslist (
   group_id INT primary key,
   name   VARCHAR(30)
);

CREATE TABLE memberslist (
  member_id INT primary key,
  user_id INT,
  group_id INT,
  CONSTRAINT members_users_fk1 FOREIGN KEY (user_id) REFERENCES userslist 
(user_id),
  CONSTRAINT members_groups_fk1 FOREIGN KEY (group_id) REFERENCES 
groupslist (group_id)
);

CREATE PROPERTY GRAPH members_pg
   VERTEX TABLES (
     userslist
       KEY (user_id)
       LABEL luser
       PROPERTIES ALL COLUMNS,
     groupslist
       KEY (group_id)
       LABEL lgroup
       PROPERTIES ALL COLUMNS
  )
   edge TABLES (
     memberslist
       KEY (member_id)
       SOURCE KEY (user_id) REFERENCES userslist (user_id)
       DESTINATION KEY (group_id) REFERENCES groupslist (group_id)
       LABEL lmember
       PROPERTIES ALL COLUMNS
  );

Last command fails with error:
ERROR:  property "name" data type modifier mismatch: 19 vs. 14
DETAIL:  In a property graph, a property of the same name has to have
the same type modifier in each label.

Labels luser and lgroup both have property "name" originated from tables 
userslist and groupslist
with types VARCHAR(40) and VARCHAR(30). Current implementation treats 
them as fields with
different types. I think, they should not be treated as different types. 
Typmod is additional
constrain, it does not create another type.
Documentation includes:"modifiers, that is optional constraints attached 
to a type declaration,
such as char(5) or numeric(30,2)"

Operations with values using different typmod do not require cast, they  
treated as the
same type.

Also I would add, that Oracle executes the code above without error.

I propose to exclude the check of typmod from the equivalence of types 
check in
src/backend/commands/propgraphcmds.c .

I suppose there is other reason to make this check (not the Standart 
SQL-2023 requirement,
but internal dependency), but I have not realised this reason.

-- 
Best regards,

Vladlen Popolitov.