Обсуждение: pgsql: SQL Property Graph Queries (SQL/PGQ)
SQL Property Graph Queries (SQL/PGQ) Implementation of SQL property graph queries, according to SQL/PGQ standard (ISO/IEC 9075-16:2023). This adds: - GRAPH_TABLE table function for graph pattern matching - DDL commands CREATE/ALTER/DROP PROPERTY GRAPH - several new system catalogs and information schema views - psql \dG command - pg_get_propgraphdef() function for pg_dump and psql A property graph is a relation with a new relkind RELKIND_PROPGRAPH. It acts like a view in many ways. It is rewritten to a standard relational query in the rewriter. Access privileges act similar to a security invoker view. (The security definer variant is not currently implemented.) Starting documentation can be found in doc/src/sgml/ddl.sgml and doc/src/sgml/queries.sgml. Author: Peter Eisentraut <peter@eisentraut.org> Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Reviewed-by: Junwang Zhao <zhjwpku@gmail.com> Reviewed-by: Ajay Pal <ajay.pal.k@gmail.com> Reviewed-by: Henson Choi <assam258@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/a855795d-e697-4fa5-8698-d20122126567@eisentraut.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/2f094e7ac691abc9d2fe0f4dcf0feac4a6ce1d9c Modified Files -------------- contrib/pg_overexplain/expected/pg_overexplain.out | 54 + contrib/pg_overexplain/pg_overexplain.c | 20 + contrib/pg_overexplain/sql/pg_overexplain.sql | 11 + doc/src/sgml/catalogs.sgml | 520 +++++- doc/src/sgml/ddl.sgml | 238 +++ doc/src/sgml/features.sgml | 4 +- doc/src/sgml/func/func-info.sgml | 15 + doc/src/sgml/information_schema.sgml | 1092 ++++++++++++ doc/src/sgml/keywords/sql2023-16-nonreserved.txt | 27 + doc/src/sgml/keywords/sql2023-16-reserved.txt | 12 + doc/src/sgml/queries.sgml | 162 ++ doc/src/sgml/ref/allfiles.sgml | 3 + doc/src/sgml/ref/alter_extension.sgml | 3 +- doc/src/sgml/ref/alter_property_graph.sgml | 299 ++++ doc/src/sgml/ref/comment.sgml | 1 + doc/src/sgml/ref/create_property_graph.sgml | 318 ++++ doc/src/sgml/ref/drop_property_graph.sgml | 111 ++ doc/src/sgml/ref/grant.sgml | 7 +- doc/src/sgml/ref/psql-ref.sgml | 13 +- doc/src/sgml/ref/revoke.sgml | 7 + doc/src/sgml/ref/security_label.sgml | 1 + doc/src/sgml/ref/select.sgml | 43 + doc/src/sgml/reference.sgml | 3 + src/backend/catalog/aclchk.c | 36 + src/backend/catalog/dependency.c | 11 + src/backend/catalog/information_schema.sql | 366 ++++ src/backend/catalog/objectaddress.c | 335 ++++ src/backend/catalog/pg_class.c | 2 + src/backend/catalog/sql_features.txt | 100 ++ src/backend/commands/Makefile | 1 + src/backend/commands/alter.c | 28 +- src/backend/commands/dropcmds.c | 1 + src/backend/commands/event_trigger.c | 2 + src/backend/commands/meson.build | 1 + src/backend/commands/propgraphcmds.c | 1882 ++++++++++++++++++++ src/backend/commands/seclabel.c | 1 + src/backend/commands/tablecmds.c | 20 +- src/backend/executor/execMain.c | 17 +- src/backend/nodes/nodeFuncs.c | 71 + src/backend/nodes/outfuncs.c | 9 + src/backend/nodes/print.c | 4 + src/backend/nodes/readfuncs.c | 9 + src/backend/optimizer/path/allpaths.c | 10 + src/backend/optimizer/prep/prepjointree.c | 8 + src/backend/parser/Makefile | 1 + src/backend/parser/analyze.c | 5 +- src/backend/parser/gram.y | 713 +++++++- src/backend/parser/meson.build | 1 + src/backend/parser/parse_agg.c | 11 + src/backend/parser/parse_clause.c | 136 ++ src/backend/parser/parse_collate.c | 1 + src/backend/parser/parse_expr.c | 11 + src/backend/parser/parse_func.c | 3 + src/backend/parser/parse_graphtable.c | 309 ++++ src/backend/parser/parse_relation.c | 99 +- src/backend/parser/parse_target.c | 5 + src/backend/parser/scan.l | 13 +- src/backend/rewrite/Makefile | 1 + src/backend/rewrite/meson.build | 1 + src/backend/rewrite/rewriteGraphTable.c | 1318 ++++++++++++++ src/backend/rewrite/rewriteHandler.c | 12 + src/backend/tcop/utility.c | 34 + src/backend/utils/adt/acl.c | 4 + src/backend/utils/adt/ruleutils.c | 531 ++++++ src/backend/utils/cache/lsyscache.c | 38 + src/backend/utils/cache/plancache.c | 6 +- src/bin/pg_dump/common.c | 3 +- src/bin/pg_dump/dumputils.c | 3 + src/bin/pg_dump/pg_backup_archiver.c | 1 + src/bin/pg_dump/pg_dump.c | 83 +- src/bin/pg_dump/t/002_pg_dump.pl | 28 + src/bin/psql/command.c | 3 +- src/bin/psql/describe.c | 94 +- src/bin/psql/help.c | 5 +- src/bin/psql/tab-complete.in.c | 67 +- src/fe_utils/psqlscan.l | 8 +- src/include/catalog/Makefile | 7 +- src/include/catalog/catversion.h | 2 +- src/include/catalog/meson.build | 5 + src/include/catalog/pg_class.h | 1 + src/include/catalog/pg_proc.dat | 3 + src/include/catalog/pg_propgraph_element.h | 118 ++ src/include/catalog/pg_propgraph_element_label.h | 55 + src/include/catalog/pg_propgraph_label.h | 55 + src/include/catalog/pg_propgraph_label_property.h | 63 + src/include/catalog/pg_propgraph_property.h | 64 + src/include/commands/propgraphcmds.h | 23 + src/include/nodes/parsenodes.h | 139 ++ src/include/nodes/primnodes.h | 24 + src/include/parser/analyze.h | 3 + src/include/parser/kwlist.h | 9 + src/include/parser/parse_graphtable.h | 24 + src/include/parser/parse_node.h | 21 + src/include/parser/parse_relation.h | 8 + src/include/rewrite/rewriteGraphTable.h | 21 + src/include/tcop/cmdtaglist.h | 3 + src/include/utils/acl.h | 1 + src/include/utils/lsyscache.h | 3 + src/interfaces/ecpg/preproc/pgc.l | 12 +- src/interfaces/ecpg/test/ecpg_schedule | 1 + src/interfaces/ecpg/test/expected/sql-sqlpgq.c | 285 +++ .../ecpg/test/expected/sql-sqlpgq.stderr | 190 ++ .../ecpg/test/expected/sql-sqlpgq.stdout | 7 + src/interfaces/ecpg/test/sql/.gitignore | 2 + src/interfaces/ecpg/test/sql/Makefile | 1 + src/interfaces/ecpg/test/sql/meson.build | 1 + src/interfaces/ecpg/test/sql/sqlpgq.pgc | 105 ++ src/test/regress/expected/alter_generic.out | 51 +- .../regress/expected/create_property_graph.out | 926 ++++++++++ src/test/regress/expected/graph_table.out | 976 ++++++++++ src/test/regress/expected/graph_table_rls.out | 774 ++++++++ src/test/regress/expected/object_address.out | 26 +- src/test/regress/expected/oidjoins.out | 12 + src/test/regress/expected/privileges.out | 91 + src/test/regress/parallel_schedule | 6 +- src/test/regress/sql/alter_generic.sql | 34 + src/test/regress/sql/create_property_graph.sql | 365 ++++ src/test/regress/sql/graph_table.sql | 567 ++++++ src/test/regress/sql/graph_table_rls.sql | 363 ++++ src/test/regress/sql/object_address.sql | 10 +- src/test/regress/sql/privileges.sql | 58 + src/tools/pgindent/typedefs.list | 19 + 122 files changed, 14888 insertions(+), 72 deletions(-)
- On 2026-03-16 Mo 5:20 AM, Peter Eisentraut wrote: > SQL Property Graph Queries (SQL/PGQ) > > Implementation of SQL property graph queries, according to SQL/PGQ > standard (ISO/IEC 9075-16:2023). > > This adds: > > - GRAPH_TABLE table function for graph pattern matching > - DDL commands CREATE/ALTER/DROP PROPERTY GRAPH > - several new system catalogs and information schema views > - psql \dG command > - pg_get_propgraphdef() function for pg_dump and psql > > A property graph is a relation with a new relkind RELKIND_PROPGRAPH. > It acts like a view in many ways. It is rewritten to a standard > relational query in the rewriter. Access privileges act similar to a > security invoker view. (The security definer variant is not currently > implemented.) > > Starting documentation can be found in doc/src/sgml/ddl.sgml and > doc/src/sgml/queries.sgml. > The output of make_propgraphdef_labels() is not stable in the face of an upgrade which will not preserve the OID sort of the labels. The explains the failure at <https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=crake&dt=2026-03-16%2009%3A27%3A04&stg=xversion-upgrade-HEAD-HEAD>. I think we need to sort the labels by name, along the lines of the attached. Or else teach the binary upgrade code to use the same labels, as we do for some other things. Not sure how possible that is, nor how worth it. We also need to fence the dependency check in pg_dump.c (also in the attached patch) cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Вложения
On Tue, Mar 17, 2026 at 12:35 AM Andrew Dunstan <andrew@dunslane.net> wrote: > > > - > > > On 2026-03-16 Mo 5:20 AM, Peter Eisentraut wrote: > > SQL Property Graph Queries (SQL/PGQ) > > > > Implementation of SQL property graph queries, according to SQL/PGQ > > standard (ISO/IEC 9075-16:2023). > > > > This adds: > > > > - GRAPH_TABLE table function for graph pattern matching > > - DDL commands CREATE/ALTER/DROP PROPERTY GRAPH > > - several new system catalogs and information schema views > > - psql \dG command > > - pg_get_propgraphdef() function for pg_dump and psql > > > > A property graph is a relation with a new relkind RELKIND_PROPGRAPH. > > It acts like a view in many ways. It is rewritten to a standard > > relational query in the rewriter. Access privileges act similar to a > > security invoker view. (The security definer variant is not currently > > implemented.) > > > > Starting documentation can be found in doc/src/sgml/ddl.sgml and > > doc/src/sgml/queries.sgml. > > > > The output of make_propgraphdef_labels() is not stable in the face of an > upgrade which will not preserve the OID sort of the labels. The explains > the failure at > <https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=crake&dt=2026-03-16%2009%3A27%3A04&stg=xversion-upgrade-HEAD-HEAD>. > I think we need to sort the labels by name, along the lines of the > attached. Or else teach the binary upgrade code to use the same labels, > as we do for some other things. Not sure how possible that is, nor how > worth it. +1 for sorting by label names, the patch LGTM. > > We also need to fence the dependency check in pg_dump.c (also in the > attached patch) +1 > > > cheers > > > andrew > > > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com -- Regards Junwang Zhao
Hi Andrew, On Tue, Mar 17, 2026 at 11:10 AM Junwang Zhao <zhjwpku@gmail.com> wrote: > > On Tue, Mar 17, 2026 at 12:35 AM Andrew Dunstan <andrew@dunslane.net> wrote: > > > > > > - > > > > > > On 2026-03-16 Mo 5:20 AM, Peter Eisentraut wrote: > > > SQL Property Graph Queries (SQL/PGQ) > > > > > > Implementation of SQL property graph queries, according to SQL/PGQ > > > standard (ISO/IEC 9075-16:2023). > > > > > > This adds: > > > > > > - GRAPH_TABLE table function for graph pattern matching > > > - DDL commands CREATE/ALTER/DROP PROPERTY GRAPH > > > - several new system catalogs and information schema views > > > - psql \dG command > > > - pg_get_propgraphdef() function for pg_dump and psql > > > > > > A property graph is a relation with a new relkind RELKIND_PROPGRAPH. > > > It acts like a view in many ways. It is rewritten to a standard > > > relational query in the rewriter. Access privileges act similar to a > > > security invoker view. (The security definer variant is not currently > > > implemented.) > > > > > > Starting documentation can be found in doc/src/sgml/ddl.sgml and > > > doc/src/sgml/queries.sgml. > > > > > > > The output of make_propgraphdef_labels() is not stable in the face of an > > upgrade which will not preserve the OID sort of the labels. The explains > > the failure at > > <https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=crake&dt=2026-03-16%2009%3A27%3A04&stg=xversion-upgrade-HEAD-HEAD>. > > I think we need to sort the labels by name, along the lines of the > > attached. Or else teach the binary upgrade code to use the same labels, > > as we do for some other things. Not sure how possible that is, nor how > > worth it. > > +1 for sorting by label names, the patch LGTM. I just noticed that Peter and Ashutosh had a patch for the same fix by using list_sort[1], you might want to join the discussion there. [1] https://www.postgresql.org/message-id/CAExHW5te5O-QGVhN6YADeFYDDb2oi%3DU50u62ojRcVAiZ-AEF-g%40mail.gmail.com > > > > > We also need to fence the dependency check in pg_dump.c (also in the > > attached patch) > > +1 > > > > > > > cheers > > > > > > andrew > > > > > > > > -- > > Andrew Dunstan > > EDB: https://www.enterprisedb.com > > > > -- > Regards > Junwang Zhao -- Regards Junwang Zhao