agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH v4 3/3] Replace matview WITH OLD DATA 3+ messages / 2 participants [nested] [flat]
* [PATCH v4 3/3] Replace matview WITH OLD DATA @ 2024-07-26 21:33 Erik Wienhold <ewie@ewie.name> 0 siblings, 0 replies; 3+ messages in thread From: Erik Wienhold @ 2024-07-26 21:33 UTC (permalink / raw) --- .../sgml/ref/create_materialized_view.sgml | 16 +++++++++-- src/backend/commands/createas.c | 26 +++++++++++------ src/backend/parser/gram.y | 16 +++++++++++ src/include/nodes/primnodes.h | 1 + src/test/regress/expected/matview.out | 28 +++++++++++++++++++ src/test/regress/sql/matview.sql | 15 ++++++++++ 6 files changed, 90 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml index 5e03320eb7..1352e9de40 100644 --- a/doc/src/sgml/ref/create_materialized_view.sgml +++ b/doc/src/sgml/ref/create_materialized_view.sgml @@ -27,7 +27,7 @@ CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_nam [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ] AS <replaceable>query</replaceable> - [ WITH [ NO ] DATA ] + [ WITH [ NO | OLD ] DATA ] </synopsis> </refsynopsisdiv> @@ -37,7 +37,8 @@ CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_nam <para> <command>CREATE MATERIALIZED VIEW</command> defines a materialized view of a query. The query is executed and used to populate the view at the time - the command is issued (unless <command>WITH NO DATA</command> is used) and may be + the command is issued (unless <command>WITH NO DATA</command> or + <command>WITH OLD DATA</command> is used) and may be refreshed later using <command>REFRESH MATERIALIZED VIEW</command>. </para> @@ -162,7 +163,7 @@ CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_nam </varlistentry> <varlistentry> - <term><literal>WITH [ NO ] DATA</literal></term> + <term><literal>WITH [ NO | OLD ] DATA</literal></term> <listitem> <para> This clause specifies whether or not the materialized view should be @@ -170,6 +171,15 @@ CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_nam flagged as unscannable and cannot be queried until <command>REFRESH MATERIALIZED VIEW</command> is used. </para> + + <para> + The form <command>WITH OLD DATA</command> keeps the already stored data + when replacing an existing materialized view to keep it populated. For + newly created materialized views, this has the same effect as + <command>WITH DATA</command>. Use this form if you want to use + <command>REFRESH MATERIALIZED VIEW CONCURRENTLY</command> as it requires + a populated materialized view. + </para> </listitem> </varlistentry> diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index d4bc5e5c08..8db000554c 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -332,18 +332,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt, /* An existing materialized view can be replaced. */ if (is_matview && into->replace) { - RefreshMatViewStmt *refresh; - /* Change the relation to match the new query and other options. */ - (void) create_ctas_nodata(query->targetList, into); + address = create_ctas_nodata(query->targetList, into); - /* Refresh the materialized view with a fake statement. */ - refresh = makeNode(RefreshMatViewStmt); - refresh->relation = into->rel; - refresh->skipData = into->skipData; - refresh->concurrent = false; + /* + * Refresh the materialized view with a fake statement unless we + * must keep the old data. + */ + if (!into->keepData) + { + RefreshMatViewStmt *refresh; + + refresh = makeNode(RefreshMatViewStmt); + refresh->relation = into->rel; + refresh->skipData = into->skipData; + refresh->concurrent = false; + + address = ExecRefreshMatView(refresh, NULL, NULL); + } - return ExecRefreshMatView(refresh, NULL, NULL); + return address; } return InvalidObjectAddress; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index dadc6b630a..1be1a95cd9 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4838,6 +4838,22 @@ CreateMatViewStmt: $7->replace = true; $$ = (Node *) ctas; } + | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt WITH OLD DATA_P + { + CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt); + + ctas->query = $9; + ctas->into = $7; + ctas->objtype = OBJECT_MATVIEW; + ctas->is_select_into = false; + ctas->if_not_exists = false; + /* cram additional flags into the IntoClause */ + $7->rel->relpersistence = $4; + $7->skipData = false; + $7->keepData = true; + $7->replace = true; + $$ = (Node *) ctas; + } ; create_mv_target: diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 533fa847ab..6fa3291c64 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -168,6 +168,7 @@ typedef struct IntoClause /* materialized view's SELECT query */ struct Query *viewQuery pg_node_attr(query_jumble_ignore); bool skipData; /* true for WITH NO DATA */ + bool keepData; /* true for WITH OLD DATA */ bool replace; /* replace existing matview? */ } IntoClause; diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index cefd0d442c..47dfd88bff 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -751,6 +751,23 @@ SELECT * FROM mvtest_replace; 3 (1 row) +-- replace query but keep old data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 5 AS a + WITH OLD DATA; +SELECT * FROM mvtest_replace; + a +--- + 3 +(1 row) + +REFRESH MATERIALIZED VIEW mvtest_replace; +SELECT * FROM mvtest_replace; + a +--- + 5 +(1 row) + -- add column CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS SELECT 4 AS a, 1 b; @@ -905,3 +922,14 @@ ERROR: syntax error at or near "NOT" LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep... ^ DROP MATERIALIZED VIEW mvtest_replace; +-- Create new matview WITH OLD DATA. This populates the new matview as if +-- WITH DATA had been specified. +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 17 AS a + WITH OLD DATA; +SELECT * FROM mvtest_replace; + a +---- + 17 +(1 row) + diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index c12f0243c9..b268237c24 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -338,6 +338,14 @@ SELECT * FROM mvtest_replace; -- error: not populated REFRESH MATERIALIZED VIEW mvtest_replace; SELECT * FROM mvtest_replace; +-- replace query but keep old data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 5 AS a + WITH OLD DATA; +SELECT * FROM mvtest_replace; +REFRESH MATERIALIZED VIEW mvtest_replace; +SELECT * FROM mvtest_replace; + -- add column CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS SELECT 4 AS a, 1 b; @@ -422,3 +430,10 @@ CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS SELECT 1 AS a; DROP MATERIALIZED VIEW mvtest_replace; + +-- Create new matview WITH OLD DATA. This populates the new matview as if +-- WITH DATA had been specified. +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 17 AS a + WITH OLD DATA; +SELECT * FROM mvtest_replace; -- 2.47.0 --hht4bymbvbva5al5-- ^ 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
* [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
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-07-26 21:33 [PATCH v4 3/3] Replace matview WITH OLD DATA 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