Re: SQL Property Graph Queries (SQL/PGQ) - Mailing list pgsql-hackers

Ashutosh Bapat писал(а) 2025-02-25 17:20:
> On Tue, Feb 25, 2025 at 3:17 PM Vladlen Popolitov
> <v.popolitov@postgrespro.ru> wrote:
>> 
>> 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.
> 
> When a property is projected in a COLUMNS clause, it will be added to
> the SELECT list of the rewritten query, which in turn could be a UNION
> query. Having the same typmod for all the properties with the same
> name makes it easy to write the SELECT list of UNION. Otherwise, we
> have to find a common typmod for all the properties. This restriction
> is not expected to be permanent but makes the first set of patches
> easy, which are quite large as is. I think in some next version, we
> will lift this restriction as well as lift the restriction on all
> properties with the same name needing the same datatype. According to
> the SQL standard, they can be of different data types as long as
> there's a common type to which they all can be cast to. It may be
> possible to lift this restriction in this version itself, if we find
> enough developer and reviewer bandwidth.
Hi!

  Thank you for explanation. I expected, that it has some internal
dependency, but did not find it.
I am running graph queries now, and I will see how they interact with
typmod.

-- 
Best regards,

Vladlen Popolitov.