Re: SQL Property Graph Queries (SQL/PGQ)

On Tue, Feb 25, 2025 at 3:17 PM Vladlen Popolitov
<v(dot)popolitov(at)postgrespro(dot)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.

--
Best Wishes,
Ashutosh Bapat