From cf7d1aeacb464a4bbfc48ebe42e9ee90a55e8082 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Tue, 6 Oct 2020 16:54:13 +0530 Subject: [PATCH 01/14] make minor type changes --- .../src-lib/Hasura/RQL/DDL/Schema/Function.hs | 4 +-- server/src-lib/Hasura/SQL/Types.hs | 26 +++++++++++-------- 2 files changed, 17 insertions(+), 13 deletions(-) diff --git a/server/src-lib/Hasura/RQL/DDL/Schema/Function.hs b/server/src-lib/Hasura/RQL/DDL/Schema/Function.hs index 1339a932065fd..221dcef0bf78d 100644 --- a/server/src-lib/Hasura/RQL/DDL/Schema/Function.hs +++ b/server/src-lib/Hasura/RQL/DDL/Schema/Function.hs @@ -222,8 +222,8 @@ handleMultipleFunctions qf = \case throw400 NotSupported $ "function " <> qf <<> " is overloaded. Overloaded functions are not supported" -fetchRawFunctioInfo :: MonadTx m => QualifiedFunction -> m RawFunctionInfo -fetchRawFunctioInfo qf@(QualifiedObject sn fn) = +fetchRawFunctionInfo :: MonadTx m => QualifiedFunction -> m RawFunctionInfo +fetchRawFunctionInfo qf@(QualifiedObject sn fn) = handleMultipleFunctions qf =<< map (Q.getAltJ . runIdentity) <$> fetchFromDatabase where fetchFromDatabase = liftTx $ diff --git a/server/src-lib/Hasura/SQL/Types.hs b/server/src-lib/Hasura/SQL/Types.hs index 04bceb43f245f..f2cdd9eaa1300 100644 --- a/server/src-lib/Hasura/SQL/Types.hs +++ b/server/src-lib/Hasura/SQL/Types.hs @@ -195,30 +195,34 @@ instance ToTxt TableName where data TableType = TTBaseTable | TTView + | TTMaterializedView | TTForeignTable | TTLocalTemporary deriving (Eq) tableTyToTxt :: TableType -> T.Text -tableTyToTxt TTBaseTable = "BASE TABLE" -tableTyToTxt TTView = "VIEW" -tableTyToTxt TTForeignTable = "FOREIGN TABLE" -tableTyToTxt TTLocalTemporary = "LOCAL TEMPORARY" +tableTyToTxt TTBaseTable = "BASE TABLE" +tableTyToTxt TTView = "VIEW" +tableTyToTxt TTMaterializedView = "MATERIALIZED VIEW" +tableTyToTxt TTForeignTable = "FOREIGN TABLE" +tableTyToTxt TTLocalTemporary = "LOCAL TEMPORARY" instance Show TableType where show = T.unpack . tableTyToTxt instance Q.FromCol TableType where fromCol bs = flip Q.fromColHelper bs $ PD.enum $ \case - "BASE TABLE" -> Just TTBaseTable - "VIEW" -> Just TTView - "FOREIGN TABLE" -> Just TTForeignTable - "LOCAL TEMPORARY" -> Just TTLocalTemporary - _ -> Nothing + "BASE TABLE" -> Just TTBaseTable + "VIEW" -> Just TTView + "MATERIALIZED VIEW" -> Just TTMaterializedView + "FOREIGN TABLE" -> Just TTForeignTable + "LOCAL TEMPORARY" -> Just TTLocalTemporary + _ -> Nothing isView :: TableType -> Bool -isView TTView = True -isView _ = False +isView TTView = True +isView TTMaterializedView = True +isView _ = False newtype ConstraintName = ConstraintName { getConstraintTxt :: T.Text } From adf1a89f51bb574678e1552052ef2fb42c0dfe09 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Tue, 6 Oct 2020 17:10:26 +0530 Subject: [PATCH 02/14] add migrations and update catalog version --- server/src-rsr/catalog_version.txt | 2 +- server/src-rsr/initialise.sql | 57 +++++++++++++++----------- server/src-rsr/migrations/39_to_40.sql | 46 +++++++++++++++++++++ server/src-rsr/migrations/40_to_39.sql | 37 +++++++++++++++++ 4 files changed, 117 insertions(+), 25 deletions(-) create mode 100644 server/src-rsr/migrations/39_to_40.sql create mode 100644 server/src-rsr/migrations/40_to_39.sql diff --git a/server/src-rsr/catalog_version.txt b/server/src-rsr/catalog_version.txt index a2720097dccb4..425151f3a411f 100644 --- a/server/src-rsr/catalog_version.txt +++ b/server/src-rsr/catalog_version.txt @@ -1 +1 @@ -39 +40 diff --git a/server/src-rsr/initialise.sql b/server/src-rsr/initialise.sql index da7428627cbda..4707b06d704c9 100644 --- a/server/src-rsr/initialise.sql +++ b/server/src-rsr/initialise.sql @@ -568,33 +568,42 @@ CREATE VIEW hdb_catalog.hdb_function_info_agg AS ( ( SELECT e - FROM - ( + FROM + ( + SELECT + description, + has_variadic, + function_type, + return_type_schema, + return_type_name, + return_type_type, + returns_set, + input_arg_types, + input_arg_names, + default_args, + exists( SELECT - description, - has_variadic, - function_type, - return_type_schema, - return_type_name, - return_type_type, - returns_set, - input_arg_types, - input_arg_names, - default_args, - exists( - SELECT - 1 - FROM - information_schema.tables - WHERE - table_schema = return_type_schema - AND table_name = return_type_name - ) AS returns_table - ) AS e + 1 + FROM + information_schema.tables + WHERE + table_schema = return_type_schema + AND table_name = return_type_name + ) + OR exists( + SELECT + 1 + FROM + pg_matviews + WHERE + schemaname = return_type_schema + AND matviewname = return_type_name + ) AS returns_table + ) AS e ) ) AS "function_info" - FROM - hdb_catalog.hdb_function_agg + FROM + hdb_catalog.hdb_function_agg ); CREATE OR REPLACE FUNCTION diff --git a/server/src-rsr/migrations/39_to_40.sql b/server/src-rsr/migrations/39_to_40.sql new file mode 100644 index 0000000000000..18f5c46922f12 --- /dev/null +++ b/server/src-rsr/migrations/39_to_40.sql @@ -0,0 +1,46 @@ +CREATE +OR REPLACE VIEW hdb_catalog.hdb_function_info_agg AS ( + SELECT + function_name, + function_schema, + row_to_json ( + ( + SELECT + e + FROM + ( + SELECT + description, + has_variadic, + function_type, + return_type_schema, + return_type_name, + return_type_type, + returns_set, + input_arg_types, + input_arg_names, + default_args, + exists( + SELECT + 1 + FROM + information_schema.tables + WHERE + table_schema = return_type_schema + AND table_name = return_type_name + ) + OR exists( + SELECT + 1 + FROM + pg_matviews + WHERE + schemaname = return_type_schema + AND matviewname = return_type_name + ) AS returns_table + ) AS e + ) + ) AS "function_info" + FROM + hdb_catalog.hdb_function_agg +); diff --git a/server/src-rsr/migrations/40_to_39.sql b/server/src-rsr/migrations/40_to_39.sql new file mode 100644 index 0000000000000..87212a9775fca --- /dev/null +++ b/server/src-rsr/migrations/40_to_39.sql @@ -0,0 +1,37 @@ +CREATE +OR REPLACE VIEW hdb_catalog.hdb_function_info_agg AS ( + SELECT + function_name, + function_schema, + row_to_json ( + ( + SELECT + e + FROM + ( + SELECT + description, + has_variadic, + function_type, + return_type_schema, + return_type_name, + return_type_type, + returns_set, + input_arg_types, + input_arg_names, + default_args, + exists( + SELECT + 1 + FROM + information_schema.tables + WHERE + table_schema = return_type_schema + AND table_name = return_type_name + ) AS returns_table + ) AS e + ) + ) AS "function_info" + FROM + hdb_catalog.hdb_function_agg +); From b5024f2ac2ffa32984ab7578ecc9b6e23fffadb9 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Tue, 6 Oct 2020 20:51:31 +0530 Subject: [PATCH 03/14] update Changelog --- CHANGELOG.md | 1 + 1 file changed, 1 insertion(+) diff --git a/CHANGELOG.md b/CHANGELOG.md index c5be2875b0200..b0984cfec3bff 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -58,6 +58,7 @@ This release contains the [PDV refactor (#4111)](https://github.com/hasura/graph - server: change `created_at` column type from `timestamp` to `timestamptz` for scheduled triggers tables (fix #5722) - server: allow configuring timeouts for actions (fixes #4966) - server: accept only non-negative integers for batch size and refetch interval (close #5653) (#5759) +- server: track functions which return setof elements from a materialized view (close #5294) - console: allow user to cascade Postgres dependencies when dropping Postgres objects (close #5109) (#5248) - console: mark inconsistent remote schemas in the UI (close #5093) (#5181) - console: remove ONLY as default for ALTER TABLE in column alter operations (close #5512) #5706 From 37bc8f72b368c3b94e54046b07366e7b787c2a30 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Thu, 8 Oct 2020 12:42:26 +0530 Subject: [PATCH 04/14] revert Changelog entry --- CHANGELOG.md | 1 - 1 file changed, 1 deletion(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index 324aac43bd93a..ee4aec1a32fa8 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -60,7 +60,6 @@ This release contains the [PDV refactor (#4111)](https://github.com/hasura/graph - server: accept only non-negative integers for batch size and refetch interval (close #5653) (#5759) - server: limit the length of event trigger names (close #5786) **NOTE:** If you have event triggers with names greater than 42 chars, then you should update their names to avoid running into Postgres identifier limit bug (#5786) -- server: track functions which return setof elements from a materialized view (close #5294) - console: allow user to cascade Postgres dependencies when dropping Postgres objects (close #5109) (#5248) - console: mark inconsistent remote schemas in the UI (close #5093) (#5181) - console: remove ONLY as default for ALTER TABLE in column alter operations (close #5512) #5706 From 2eea091eb940ab3d1032ca294d6bf06af59d68e6 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Thu, 8 Oct 2020 13:21:18 +0530 Subject: [PATCH 05/14] update setup tests --- .../graphql_query/functions/setup.yaml | 37 +++++++++++++++++++ 1 file changed, 37 insertions(+) diff --git a/server/tests-py/queries/graphql_query/functions/setup.yaml b/server/tests-py/queries/graphql_query/functions/setup.yaml index f37bf384b32ec..758e525e4adba 100644 --- a/server/tests-py/queries/graphql_query/functions/setup.yaml +++ b/server/tests-py/queries/graphql_query/functions/setup.yaml @@ -176,3 +176,40 @@ args: name: get_session_var configuration: session_argument: hasura_session + +# track & query functions that return SETOF materialized views +- type: run_sql + args: + sql: | + CREATE TABLE author( + id SERIAL PRIMARY KEY, + first_name TEXT, + last_name TEXT + ); + + INSERT INTO author(first_name, last_name) VALUES + ('enid', 'blyton'), + ('ruskin', 'bond'), + ('franz', 'kafka'); + + CREATE MATERIALIZED VIEW author_mat_view AS + SELECT * FROM author; + + CREATE FUNCTION search_author_mview(search_text text) + RETURNS SETOF author_mat_view AS $FUNCTION$ + SELECT * FROM author_mat_view WHERE + first_name = search_text OR + last_name = search_text + $FUNCTION$ LANGUAGE sql STABLE; + +- type: track_table + args: + name: author + schema: public + +- type: track_function + version: 2 + args: + function: + schema: public + name: search_author_mview From b327a6e79141b27f966bf5cb8324e8a8ee0e1667 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Thu, 8 Oct 2020 13:21:26 +0530 Subject: [PATCH 06/14] update teardown --- server/tests-py/queries/graphql_query/functions/teardown.yaml | 1 + 1 file changed, 1 insertion(+) diff --git a/server/tests-py/queries/graphql_query/functions/teardown.yaml b/server/tests-py/queries/graphql_query/functions/teardown.yaml index dd657eab8cf6b..4da4ae365a6bd 100644 --- a/server/tests-py/queries/graphql_query/functions/teardown.yaml +++ b/server/tests-py/queries/graphql_query/functions/teardown.yaml @@ -9,4 +9,5 @@ args: DROP TABLE integer_column cascade; DROP TABLE "user" cascade; DROP TABLE text_result cascade; + DROP TABLE author cascade; cascade: true From 27442bb5c86536e4df02ae94864ebec8bacb468a Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Thu, 8 Oct 2020 13:21:35 +0530 Subject: [PATCH 07/14] update py file --- server/tests-py/test_graphql_queries.py | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/server/tests-py/test_graphql_queries.py b/server/tests-py/test_graphql_queries.py index 647806ce8755e..aaa50f19246e4 100644 --- a/server/tests-py/test_graphql_queries.py +++ b/server/tests-py/test_graphql_queries.py @@ -544,6 +544,10 @@ def test_track_function_v2_errors(self, hge_ctx): @pytest.mark.parametrize("transport", ['http', 'websocket']) def test_query_get_test_session_id(self, hge_ctx, transport): check_query_f(hge_ctx, self.dir() + '/query_get_test_session_id.yaml') + + @pytest.mark.parametrize("transport", ['http', 'websocket']) + def test_query_search_author_mview(self, hge_ctx, transport): + check_query_f(hge_ctx, self.dir() + '/query_search_author_mview.yaml') @classmethod def dir(cls): From 8b8bbe59863111f7809aa9c2ce384bfb93a61541 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Thu, 8 Oct 2020 13:27:07 +0530 Subject: [PATCH 08/14] add query search text --- .../functions/query_search_author_mview.yaml | 18 ++++++++++++++++++ .../queries/graphql_query/functions/setup.yaml | 6 +++--- 2 files changed, 21 insertions(+), 3 deletions(-) create mode 100644 server/tests-py/queries/graphql_query/functions/query_search_author_mview.yaml diff --git a/server/tests-py/queries/graphql_query/functions/query_search_author_mview.yaml b/server/tests-py/queries/graphql_query/functions/query_search_author_mview.yaml new file mode 100644 index 0000000000000..38fdb37644205 --- /dev/null +++ b/server/tests-py/queries/graphql_query/functions/query_search_author_mview.yaml @@ -0,0 +1,18 @@ +description: Custom GraphQL query using search_author_mview function which returns results from a materialized view +url: /v1/graphql +status: 200 +response: + data: + search_author_mview: + - first_name: franz + last_name: kafka +query: + query: | + query { + search_author_mview( + args: {search: "kafka"} + ) { + first_name + last_name + } + } diff --git a/server/tests-py/queries/graphql_query/functions/setup.yaml b/server/tests-py/queries/graphql_query/functions/setup.yaml index 758e525e4adba..7c1e452aea7aa 100644 --- a/server/tests-py/queries/graphql_query/functions/setup.yaml +++ b/server/tests-py/queries/graphql_query/functions/setup.yaml @@ -195,11 +195,11 @@ args: CREATE MATERIALIZED VIEW author_mat_view AS SELECT * FROM author; - CREATE FUNCTION search_author_mview(search_text text) + CREATE FUNCTION search_author_mview(query text) RETURNS SETOF author_mat_view AS $FUNCTION$ SELECT * FROM author_mat_view WHERE - first_name = search_text OR - last_name = search_text + first_name = query OR + last_name = query $FUNCTION$ LANGUAGE sql STABLE; - type: track_table From 987c36674c98e165cdc1b908034aa4317ec3904a Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Thu, 8 Oct 2020 13:50:28 +0530 Subject: [PATCH 09/14] drop mat view in teardown --- server/tests-py/queries/graphql_query/functions/teardown.yaml | 1 + 1 file changed, 1 insertion(+) diff --git a/server/tests-py/queries/graphql_query/functions/teardown.yaml b/server/tests-py/queries/graphql_query/functions/teardown.yaml index 4da4ae365a6bd..ca14cb896f584 100644 --- a/server/tests-py/queries/graphql_query/functions/teardown.yaml +++ b/server/tests-py/queries/graphql_query/functions/teardown.yaml @@ -10,4 +10,5 @@ args: DROP TABLE "user" cascade; DROP TABLE text_result cascade; DROP TABLE author cascade; + DROP MATERIALIZED VIEW IF EXISTS author_mat_view cascade; cascade: true From 5fdbbdd3050ae8922447959c6918bb3807883da4 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Thu, 8 Oct 2020 14:02:24 +0530 Subject: [PATCH 10/14] fix setup --- server/tests-py/queries/graphql_query/functions/setup.yaml | 5 +++++ 1 file changed, 5 insertions(+) diff --git a/server/tests-py/queries/graphql_query/functions/setup.yaml b/server/tests-py/queries/graphql_query/functions/setup.yaml index 7c1e452aea7aa..cda4ad5ccd57f 100644 --- a/server/tests-py/queries/graphql_query/functions/setup.yaml +++ b/server/tests-py/queries/graphql_query/functions/setup.yaml @@ -207,6 +207,11 @@ args: name: author schema: public +- type: track_table + args: + name: author_mat_view + schema: public + - type: track_function version: 2 args: From ea2214fd0cbc19a15673fedd158d4208632b106b Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Thu, 8 Oct 2020 14:26:28 +0530 Subject: [PATCH 11/14] fix query test --- .../graphql_query/functions/query_search_author_mview.yaml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/server/tests-py/queries/graphql_query/functions/query_search_author_mview.yaml b/server/tests-py/queries/graphql_query/functions/query_search_author_mview.yaml index 38fdb37644205..2c0bed8397a8c 100644 --- a/server/tests-py/queries/graphql_query/functions/query_search_author_mview.yaml +++ b/server/tests-py/queries/graphql_query/functions/query_search_author_mview.yaml @@ -10,7 +10,7 @@ query: query: | query { search_author_mview( - args: {search: "kafka"} + args: {query: "kafka"} ) { first_name last_name From 92c0e4fc4c9137ec3a3fcb84e0503058fd410187 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Fri, 16 Oct 2020 15:23:38 +0530 Subject: [PATCH 12/14] update teardown based on review --- server/tests-py/queries/graphql_query/functions/teardown.yaml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/server/tests-py/queries/graphql_query/functions/teardown.yaml b/server/tests-py/queries/graphql_query/functions/teardown.yaml index ca14cb896f584..168975ce8a8a9 100644 --- a/server/tests-py/queries/graphql_query/functions/teardown.yaml +++ b/server/tests-py/queries/graphql_query/functions/teardown.yaml @@ -10,5 +10,5 @@ args: DROP TABLE "user" cascade; DROP TABLE text_result cascade; DROP TABLE author cascade; - DROP MATERIALIZED VIEW IF EXISTS author_mat_view cascade; + DROP MATERIALIZED VIEW author_mat_view cascade; cascade: true From 8b4347ba45adadf1dce82b842d67a41d07cf831f Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Fri, 16 Oct 2020 15:26:56 +0530 Subject: [PATCH 13/14] update CHANGELOG --- CHANGELOG.md | 1 + 1 file changed, 1 insertion(+) diff --git a/CHANGELOG.md b/CHANGELOG.md index d4edcbac8b49d..3e3de7c6ad3c6 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -79,6 +79,7 @@ This release contains the [PDV refactor (#4111)](https://github.com/hasura/graph - server: limit the length of event trigger names (close #5786) **NOTE:** If you have event triggers with names greater than 42 chars, then you should update their names to avoid running into Postgres identifier limit bug (#5786) - server: validate remote schema queries (fixes #4143) +- server: fix issue with tracking custom functions that return `SETOF` materialized view (close #5294) (#5945) - console: allow user to cascade Postgres dependencies when dropping Postgres objects (close #5109) (#5248) - console: mark inconsistent remote schemas in the UI (close #5093) (#5181) - cli: add missing global flags for seed command (#5565) From a32f4afce805e8546e37e89353115269c4aee8b0 Mon Sep 17 00:00:00 2001 From: Sameer Kolhar Date: Fri, 16 Oct 2020 16:38:22 +0530 Subject: [PATCH 14/14] revert teardown change --- server/tests-py/queries/graphql_query/functions/teardown.yaml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/server/tests-py/queries/graphql_query/functions/teardown.yaml b/server/tests-py/queries/graphql_query/functions/teardown.yaml index 168975ce8a8a9..ca14cb896f584 100644 --- a/server/tests-py/queries/graphql_query/functions/teardown.yaml +++ b/server/tests-py/queries/graphql_query/functions/teardown.yaml @@ -10,5 +10,5 @@ args: DROP TABLE "user" cascade; DROP TABLE text_result cascade; DROP TABLE author cascade; - DROP MATERIALIZED VIEW author_mat_view cascade; + DROP MATERIALIZED VIEW IF EXISTS author_mat_view cascade; cascade: true