YQL-17343 information_schema.table_constraints (#1397)

* init

* canonize

8 files changed, 77 insertions, 9 deletions

diff --git a/ydb/library/yql/parser/pg_catalog/catalog.cpp b/ydb/library/yql/parser/pg_catalog/catalog.cpp
index 2a301546eb..f4f439af5a 100644
--- a/ydb/library/yql/parser/pg_catalog/catalog.cpp
+++ b/ydb/library/yql/parser/pg_catalog/catalog.cpp
@@ -1419,6 +1419,7 @@ struct TCatalog {
             {"pg_catalog", "pg_namespace"},
             {"information_schema", "tables"},
             {"information_schema", "columns"},
+            {"information_schema", "table_constraints"},
         }),
         AllStaticColumns({
             {"pg_catalog", "pg_type", "oid", "oid"},
@@ -1496,6 +1497,10 @@ struct TCatalog {
             {"information_schema", "columns", "table_name", "name"},
             {"information_schema", "columns", "column_name", "name"},
             {"information_schema", "columns", "udt_name", "name"},
+
+            {"information_schema", "table_constraints", "constraint_schema", "name"},
+            {"information_schema", "table_constraints", "table_name", "name"},
+            {"information_schema", "table_constraints", "constraint_type", "varchar"},
         })
     {
         for (const auto& t : StaticTables) {
@@ -1508,6 +1513,10 @@ struct TCatalog {
             tablePtr->push_back(c);
         }
 
+        for (const auto& t : StaticColumns) {
+            Y_ENSURE(!t.second.empty());
+        }
+
         TString typeData;
         Y_ENSURE(NResource::FindExact("pg_type.dat", &typeData));
         TString opData;
diff --git a/ydb/library/yql/tests/sql/dq_file/part18/canondata/result.json b/ydb/library/yql/tests/sql/dq_file/part18/canondata/result.json
index 92442be07d..8dbabdfb54 100644
--- a/ydb/library/yql/tests/sql/dq_file/part18/canondata/result.json
+++ b/ydb/library/yql/tests/sql/dq_file/part18/canondata/result.json
@@ -2169,6 +2169,28 @@
         }
     ],
     "test.test[pg_catalog-pg_trigger-default.txt-Results]": [],
+    "test.test[pg_catalog-table_constraints-default.txt-Analyze]": [
+        {
+            "checksum": "c1f2d837c3623c81dd596a9877913fb8",
+            "size": 948,
+            "uri": "https://{canondata_backend}/1773845/6e61cbdfae8bc6d693a1ad4cc304d9d45edb7242/resource.tar.gz#test.test_pg_catalog-table_constraints-default.txt-Analyze_/plan.txt"
+        }
+    ],
+    "test.test[pg_catalog-table_constraints-default.txt-Debug]": [
+        {
+            "checksum": "f094f606d68a75ab0f9b5ff8a567a579",
+            "size": 612,
+            "uri": "https://{canondata_backend}/1773845/6e61cbdfae8bc6d693a1ad4cc304d9d45edb7242/resource.tar.gz#test.test_pg_catalog-table_constraints-default.txt-Debug_/opt.yql_patched"
+        }
+    ],
+    "test.test[pg_catalog-table_constraints-default.txt-Plan]": [
+        {
+            "checksum": "c1f2d837c3623c81dd596a9877913fb8",
+            "size": 948,
+            "uri": "https://{canondata_backend}/1773845/6e61cbdfae8bc6d693a1ad4cc304d9d45edb7242/resource.tar.gz#test.test_pg_catalog-table_constraints-default.txt-Plan_/plan.txt"
+        }
+    ],
+    "test.test[pg_catalog-table_constraints-default.txt-Results]": [],
     "test.test[produce-discard_reduce_lambda-default.txt-Analyze]": [
         {
             "checksum": "c87ac713fed1eacadd4720a18695a0fc",
diff --git a/ydb/library/yql/tests/sql/sql2yql/canondata/result.json b/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
index 0e74ffabe1..a4e1a06094 100644
--- a/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
+++ b/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
@@ -13376,6 +13376,13 @@
             "uri": "https://{canondata_backend}/1942173/cb2fdcf9620011c608e9c76afbfe1a17bdd9d6f4/resource.tar.gz#test_sql2yql.test_pg_catalog-pg_type_syntax_yql_/sql.yql"
         }
     ],
+    "test_sql2yql.test[pg_catalog-table_constraints]": [
+        {
+            "checksum": "e840ee7a11c3651288535d22aac9332f",
+            "size": 988,
+            "uri": "https://{canondata_backend}/1924537/72a6aff3b150d35ecfbb758aef00fdcd0984547b/resource.tar.gz#test_sql2yql.test_pg_catalog-table_constraints_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[pg_catalog-tables]": [
         {
             "checksum": "fb7f166f5cfb2889f9271ec26d395289",
diff --git a/ydb/library/yql/tests/sql/suites/pg_catalog/table_constraints.sql b/ydb/library/yql/tests/sql/suites/pg_catalog/table_constraints.sql
new file mode 100644
index 0000000000..54ae1c97b7
--- /dev/null
+++ b/ydb/library/yql/tests/sql/suites/pg_catalog/table_constraints.sql
@@ -0,0 +1,9 @@
+--!syntax_pg
+select
+constraint_schema,
+table_name,
+constraint_type
+from
+information_schema.table_constraints
+order by constraint_schema, table_name
+
diff --git a/ydb/library/yql/tests/sql/yt_native_file/part14/canondata/result.json b/ydb/library/yql/tests/sql/yt_native_file/part14/canondata/result.json
index f055f00367..b7845ac1c1 100644
--- a/ydb/library/yql/tests/sql/yt_native_file/part14/canondata/result.json
+++ b/ydb/library/yql/tests/sql/yt_native_file/part14/canondata/result.json
@@ -2386,9 +2386,9 @@
     ],
     "test.test[pg_catalog-pg_tables-default.txt-Results]": [
         {
-            "checksum": "4b6af13d1956b092a48e70a81ba64926",
-            "size": 2936,
-            "uri": "https://{canondata_backend}/1936842/b1edddeb08d352926e347a4f529899d4533cd382/resource.tar.gz#test.test_pg_catalog-pg_tables-default.txt-Results_/results.txt"
+            "checksum": "184cdb7b88a0cbc27eab10f27a1f9594",
+            "size": 3071,
+            "uri": "https://{canondata_backend}/1773845/2d08ad134994d3e36dc49c3a759a1fb95cec5e61/resource.tar.gz#test.test_pg_catalog-pg_tables-default.txt-Results_/results.txt"
         }
     ],
     "test.test[produce-process_rows_sorted_desc_multi_out--Debug]": [
diff --git a/ydb/library/yql/tests/sql/yt_native_file/part16/canondata/result.json b/ydb/library/yql/tests/sql/yt_native_file/part16/canondata/result.json
index db9a2da2b5..c9430751fb 100644
--- a/ydb/library/yql/tests/sql/yt_native_file/part16/canondata/result.json
+++ b/ydb/library/yql/tests/sql/yt_native_file/part16/canondata/result.json
@@ -1888,9 +1888,9 @@
     ],
     "test.test[pg_catalog-columns-default.txt-Results]": [
         {
-            "checksum": "430086549283aa70d6c84267af2663d6",
-            "size": 12690,
-            "uri": "https://{canondata_backend}/1889210/850cc148b59fcf8cae8e282099dbcf559be79b4b/resource.tar.gz#test.test_pg_catalog-columns-default.txt-Results_/results.txt"
+            "checksum": "490a1e0d0509eec4920379998de15566",
+            "size": 13320,
+            "uri": "https://{canondata_backend}/1814674/d705af5d4baa19963255001d314ce2ac75be9a4a/resource.tar.gz#test.test_pg_catalog-columns-default.txt-Results_/results.txt"
         }
     ],
     "test.test[pragma-config_exec--Debug]": [
diff --git a/ydb/library/yql/tests/sql/yt_native_file/part18/canondata/result.json b/ydb/library/yql/tests/sql/yt_native_file/part18/canondata/result.json
index bc2a1e0654..17e584039e 100644
--- a/ydb/library/yql/tests/sql/yt_native_file/part18/canondata/result.json
+++ b/ydb/library/yql/tests/sql/yt_native_file/part18/canondata/result.json
@@ -1842,6 +1842,27 @@
             "uri": "https://{canondata_backend}/1775319/059e812ace6b1d28f72605b86d16778e7d38747a/resource.tar.gz#test.test_pg_catalog-pg_trigger-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[pg_catalog-table_constraints-default.txt-Debug]": [
+        {
+            "checksum": "dd7b4a8788c78f7dd465efd25b015095",
+            "size": 552,
+            "uri": "https://{canondata_backend}/1936947/5cc2f90792dec2288dabb8a22168319aa6b8c6a5/resource.tar.gz#test.test_pg_catalog-table_constraints-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[pg_catalog-table_constraints-default.txt-Plan]": [
+        {
+            "checksum": "c1f2d837c3623c81dd596a9877913fb8",
+            "size": 948,
+            "uri": "https://{canondata_backend}/1936947/5cc2f90792dec2288dabb8a22168319aa6b8c6a5/resource.tar.gz#test.test_pg_catalog-table_constraints-default.txt-Plan_/plan.txt"
+        }
+    ],
+    "test.test[pg_catalog-table_constraints-default.txt-Results]": [
+        {
+            "checksum": "2564ce56a991d95fd896dc4305f8f3fe",
+            "size": 1119,
+            "uri": "https://{canondata_backend}/1936947/5cc2f90792dec2288dabb8a22168319aa6b8c6a5/resource.tar.gz#test.test_pg_catalog-table_constraints-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[produce-discard_reduce_lambda-default.txt-Debug]": [
         {
             "checksum": "776f33da0611fa0335effb3dc55b0563",
diff --git a/ydb/library/yql/tests/sql/yt_native_file/part3/canondata/result.json b/ydb/library/yql/tests/sql/yt_native_file/part3/canondata/result.json
index ab083f6cba..a5e72683da 100644
--- a/ydb/library/yql/tests/sql/yt_native_file/part3/canondata/result.json
+++ b/ydb/library/yql/tests/sql/yt_native_file/part3/canondata/result.json
@@ -1671,9 +1671,9 @@
     ],
     "test.test[pg_catalog-tables-default.txt-Results]": [
         {
-            "checksum": "6a8eecc07133c7ff93e0f4b10a932380",
-            "size": 2939,
-            "uri": "https://{canondata_backend}/1936842/870692fb961448eb455c0be956f87156694c42be/resource.tar.gz#test.test_pg_catalog-tables-default.txt-Results_/results.txt"
+            "checksum": "5f1de0e7b083d586bf8fe01f1669949f",
+            "size": 3074,
+            "uri": "https://{canondata_backend}/1880306/737f33daf0420bb9e30f0173cda634b4738e62c0/resource.tar.gz#test.test_pg_catalog-tables-default.txt-Results_/results.txt"
         }
     ],
     "test.test[produce-process_row_and_columns-default.txt-Debug]": [