agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH v4 2/3] Deprecate CREATE MATERIALIZED VIEW IF NOT EXISTS 3+ messages / 2 participants [nested] [flat]
* [PATCH v4 2/3] Deprecate CREATE MATERIALIZED VIEW IF NOT EXISTS @ 2024-05-28 00:19 Erik Wienhold <ewie@ewie.name> 0 siblings, 0 replies; 3+ messages in thread From: Erik Wienhold @ 2024-05-28 00:19 UTC (permalink / raw) --- src/backend/parser/gram.y | 14 ++++++ .../expected/test_extensions.out | 45 +++++++++++++++++++ src/test/regress/expected/matview.out | 20 +++++++++ 3 files changed, 79 insertions(+) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 6b32233127..dadc6b630a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4808,6 +4808,20 @@ CreateMatViewStmt: $8->rel->relpersistence = $2; $8->skipData = !($11); $$ = (Node *) ctas; + + if (ctas->into->rel->schemaname) + ereport(WARNING, + errmsg("IF NOT EXISTS is deprecated in materialized view creation"), + errhint("Use CREATE OR REPLACE MATERIALIZED VIEW %s.%s.", + ctas->into->rel->schemaname, + ctas->into->rel->relname), + parser_errposition(@1)); + else + ereport(WARNING, + errmsg("IF NOT EXISTS is deprecated in materialized view creation"), + errhint("Use CREATE OR REPLACE MATERIALIZED VIEW %s.", + ctas->into->rel->relname), + parser_errposition(@1)); } | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data { diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out index d5388a1fec..9a1e0f7658 100644 --- a/src/test/modules/test_extensions/expected/test_extensions.out +++ b/src/test/modules/test_extensions/expected/test_extensions.out @@ -404,6 +404,11 @@ Objects in extension "test_ext_cor" CREATE COLLATION ext_cine_coll ( LC_COLLATE = "C", LC_CTYPE = "C" ); CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: collation ext_cine_coll is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE COLLATION IF NOT EXISTS ext_cine_coll @@ -412,6 +417,11 @@ extension script file "test_ext_cine--1.0.sql", near line 10 DROP COLLATION ext_cine_coll; CREATE MATERIALIZED VIEW ext_cine_mv AS SELECT 11 AS f1; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: materialized view ext_cine_mv is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1" @@ -420,6 +430,11 @@ DROP MATERIALIZED VIEW ext_cine_mv; CREATE FOREIGN DATA WRAPPER dummy; CREATE SERVER ext_cine_srv FOREIGN DATA WRAPPER dummy; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: server ext_cine_srv is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE SERVER IF NOT EXISTS ext_cine_srv FOREIGN DATA WRAPPER ext_cine_fdw" @@ -427,6 +442,11 @@ extension script file "test_ext_cine--1.0.sql", near line 17 DROP SERVER ext_cine_srv; CREATE SCHEMA ext_cine_schema; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: schema ext_cine_schema is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE SCHEMA IF NOT EXISTS ext_cine_schema" @@ -434,6 +454,11 @@ extension script file "test_ext_cine--1.0.sql", near line 19 DROP SCHEMA ext_cine_schema; CREATE SEQUENCE ext_cine_seq; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: sequence ext_cine_seq is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE SEQUENCE IF NOT EXISTS ext_cine_seq" @@ -441,6 +466,11 @@ extension script file "test_ext_cine--1.0.sql", near line 21 DROP SEQUENCE ext_cine_seq; CREATE TABLE ext_cine_tab1 (x int); CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: table ext_cine_tab1 is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS ext_cine_tab1 (x int)" @@ -448,12 +478,22 @@ extension script file "test_ext_cine--1.0.sql", near line 23 DROP TABLE ext_cine_tab1; CREATE TABLE ext_cine_tab2 AS SELECT 42 AS y; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: table ext_cine_tab2 is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS ext_cine_tab2 AS SELECT 42 AS y" extension script file "test_ext_cine--1.0.sql", near line 25 DROP TABLE ext_cine_tab2; CREATE EXTENSION test_ext_cine; +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; \dx+ test_ext_cine Objects in extension "test_ext_cine" Object description @@ -475,6 +515,11 @@ Objects in extension "test_ext_cine" (14 rows) ALTER EXTENSION test_ext_cine UPDATE TO '1.1'; +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; \dx+ test_ext_cine Objects in extension "test_ext_cine" Object description diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index e2e2a13396..cefd0d442c 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -565,6 +565,10 @@ CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; ERROR: relation "mvtest_mv_foo" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELE... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW mvtest_mv_foo. NOTICE: relation "mvtest_mv_foo" already exists, skipping CREATE UNIQUE INDEX ON mvtest_mv_foo (i); RESET ROLE; @@ -662,12 +666,20 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab. NOTICE: relation "matview_ine_tab" already exists, skipping CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab. NOTICE: relation "matview_ine_tab" already exists, skipping EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) CREATE MATERIALIZED VIEW matview_ine_tab AS @@ -676,6 +688,10 @@ ERROR: relation "matview_ine_tab" already exists EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 2: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab. NOTICE: relation "matview_ine_tab" already exists, skipping QUERY PLAN ------------ @@ -688,6 +704,10 @@ ERROR: relation "matview_ine_tab" already exists EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 2: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab. NOTICE: relation "matview_ine_tab" already exists, skipping QUERY PLAN ------------ -- 2.47.0 --hht4bymbvbva5al5 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0003-Replace-matview-WITH-OLD-DATA.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH] Change default client_connection_check_interval to 2000ms @ 2026-02-05 05:08 Jeremy Schneider <schneider@ardentperf.com> 0 siblings, 0 replies; 3+ messages in thread From: Jeremy Schneider @ 2026-02-05 05:08 UTC (permalink / raw) The default value of client_connection_check_interval is changed from 0 (disabled) to 2000ms (2 seconds). This enables periodic checking for client disconnection during long-running queries by default, which can help detect and clean up queries from disconnected clients more promptly. A value of 0 continues to disable connection checking for users who prefer the previous behavior. --- doc/src/sgml/config.sgml | 9 +++++---- src/backend/utils/misc/guc_parameters.dat | 2 +- src/backend/utils/misc/postgresql.conf.sample | 2 +- 3 files changed, 7 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 5560b95ee60..5bc7f029e80 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1059,10 +1059,11 @@ include_dir 'conf.d' </para> <para> If the value is specified without units, it is taken as milliseconds. - The default value is <literal>0</literal>, which disables connection - checks. Without connection checks, the server will detect the loss of - the connection only at the next interaction with the socket, when it - waits for, receives or sends data. + The default value is <literal>2000</literal> (2 seconds). A value of + <literal>0</literal> disables connection checks. Without connection + checks, the server will detect the loss of the connection only at the + next interaction with the socket, when it waits for, receives or sends + data. </para> <para> For the kernel itself to detect lost TCP connections reliably and within diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index f0260e6e412..91c0d740ce5 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -403,7 +403,7 @@ long_desc => '0 disables connection checks.', flags => 'GUC_UNIT_MS', variable => 'client_connection_check_interval', - boot_val => '0', + boot_val => '2000', min => '0', max => 'INT_MAX', check_hook => 'check_client_connection_check_interval', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index c4f92fcdac8..8dd89d6da4e 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -87,7 +87,7 @@ #tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds; # 0 selects the system default -#client_connection_check_interval = 0 # time between checks for client +#client_connection_check_interval = 2000 # time between checks for client # disconnection while running queries; # 0 for never -- 2.43.0 --MP_/qz2OYyXRpSnvpF48FALDBTl-- ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH] Change default client_connection_check_interval to 2000ms @ 2026-02-05 05:08 Jeremy Schneider <schneider@ardentperf.com> 0 siblings, 0 replies; 3+ messages in thread From: Jeremy Schneider @ 2026-02-05 05:08 UTC (permalink / raw) The default value of client_connection_check_interval is changed from 0 (disabled) to 2000ms (2 seconds). This enables periodic checking for client disconnection during long-running queries by default, which can help detect and clean up queries from disconnected clients more promptly. A value of 0 continues to disable connection checking for users who prefer the previous behavior. --- src/backend/utils/misc/guc_parameters.dat | 2 +- src/backend/utils/misc/postgresql.conf.sample | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index f0260e6e412..91c0d740ce5 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -403,7 +403,7 @@ long_desc => '0 disables connection checks.', flags => 'GUC_UNIT_MS', variable => 'client_connection_check_interval', - boot_val => '0', + boot_val => '2000', min => '0', max => 'INT_MAX', check_hook => 'check_client_connection_check_interval', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index c4f92fcdac8..8dd89d6da4e 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -87,7 +87,7 @@ #tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds; # 0 selects the system default -#client_connection_check_interval = 0 # time between checks for client +#client_connection_check_interval = 2000 # time between checks for client # disconnection while running queries; # 0 for never -- 2.43.0 --MP_/jM6_r96OH5VgTm.Dm/FNCyc-- ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-02-05 05:08 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-05-28 00:19 [PATCH v4 2/3] Deprecate CREATE MATERIALIZED VIEW IF NOT EXISTS Erik Wienhold <ewie@ewie.name> 2026-02-05 05:08 [PATCH] Change default client_connection_check_interval to 2000ms Jeremy Schneider <schneider@ardentperf.com> 2026-02-05 05:08 [PATCH] Change default client_connection_check_interval to 2000ms Jeremy Schneider <schneider@ardentperf.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox