agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH v2 3/3] Replace matview WITH OLD DATA 3+ messages / 2 participants [nested] [flat]
* [PATCH v2 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 b5a8e3441a..65633b8bfa 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> @@ -160,7 +161,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 @@ -168,6 +169,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 c5d78252a1..e2948ac966 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -334,18 +334,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, NULL); + } - return ExecRefreshMatView(refresh, NULL, NULL, NULL); + return address; } return InvalidObjectAddress; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index b1100bdec1..0647955005 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4860,6 +4860,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 4b0ee5d10d..ae84cb522e 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 */ Node *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.45.2 --nrp556lrhtfteavd-- ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH v1 1/1] fix pg_largeobject_metadata file transfer @ 2026-02-04 20:04 Nathan Bossart <nathan@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: Nathan Bossart @ 2026-02-04 20:04 UTC (permalink / raw) --- src/bin/pg_dump/pg_dump.c | 39 ++++++++++++++++++++++++++++----------- 1 file changed, 28 insertions(+), 11 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 2bebefd0ba2..3ba57492fa6 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -379,6 +379,7 @@ static const char *getFormattedTypeName(Archive *fout, Oid oid, OidOptions opts) static void getLOs(Archive *fout); static void dumpLO(Archive *fout, const LoInfo *loinfo); static int dumpLOs(Archive *fout, const void *arg); +static void dumpLOTruncation(Archive *fout); static void dumpPolicy(Archive *fout, const PolicyInfo *polinfo); static void dumpPublication(Archive *fout, const PublicationInfo *pubinfo); static void dumpPublicationTable(Archive *fout, const PublicationRelInfo *pubrinfo); @@ -1157,7 +1158,10 @@ main(int argc, char **argv) * subsequent COMMENT and SECURITY LABEL commands work. pg_upgrade * can't copy/link the files from older versions because aclitem * (needed by pg_largeobject_metadata.lomacl) changed its storage - * format in v16. + * format in v16. At the end of the dump, we'll generate a TRUNCATE + * command for pg_largeobject_metadata so that it's contents are + * cleared in preparation for the subsequent file transfer by + * pg_upgrade. */ if (fout->remoteVersion >= 160000) lo_metadata->dataObj->filtercond = "WHERE oid IN " @@ -1243,6 +1247,13 @@ main(int argc, char **argv) for (i = 0; i < numObjs; i++) dumpDumpableObject(fout, dobjs[i]); + /* + * For binary upgrades, set relfrozenxids, relminmxids, and relfilenodes + * of pg_largeobject and maybe pg_largeobject_metadata, and remove all + * their files. We will transfer them from the old cluster as needed. + */ + dumpLOTruncation(fout); + /* * Set up options info to ensure we dump what we want. */ @@ -3671,6 +3682,20 @@ dumpDatabase(Archive *fout) .dropStmt = delQry->data, .deps = &dbDumpId)); + PQclear(res); + + free(qdatname); + destroyPQExpBuffer(dbQry); + destroyPQExpBuffer(delQry); + destroyPQExpBuffer(creaQry); + destroyPQExpBuffer(labelq); +} + +static void +dumpLOTruncation(Archive *fout) +{ + DumpOptions *dopt = fout->dopt; + /* * pg_largeobject comes from the old system intact, so set its * relfrozenxids, relminmxids and relfilenode. @@ -3769,14 +3794,14 @@ dumpDatabase(Archive *fout) ArchiveEntry(fout, nilCatalogId, createDumpId(), ARCHIVE_OPTS(.tag = "pg_largeobject", .description = "pg_largeobject", - .section = SECTION_PRE_DATA, + .section = SECTION_POST_DATA, .createStmt = loOutQry->data)); if (fout->remoteVersion >= 160000) ArchiveEntry(fout, nilCatalogId, createDumpId(), ARCHIVE_OPTS(.tag = "pg_largeobject_metadata", .description = "pg_largeobject_metadata", - .section = SECTION_PRE_DATA, + .section = SECTION_POST_DATA, .createStmt = lomOutQry->data)); PQclear(lo_res); @@ -3787,14 +3812,6 @@ dumpDatabase(Archive *fout) destroyPQExpBuffer(loOutQry); destroyPQExpBuffer(lomOutQry); } - - PQclear(res); - - free(qdatname); - destroyPQExpBuffer(dbQry); - destroyPQExpBuffer(delQry); - destroyPQExpBuffer(creaQry); - destroyPQExpBuffer(labelq); } /* -- 2.50.1 (Apple Git-155) --b49++7dxSxUMQ6ai-- ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH v2 1/1] fix pg_largeobject_metadata file transfer @ 2026-02-05 17:26 Nathan Bossart <nathan@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: Nathan Bossart @ 2026-02-05 17:26 UTC (permalink / raw) --- src/backend/catalog/objectaddress.c | 2 +- src/bin/pg_dump/pg_dump.c | 53 +++++++++++++---------------- 2 files changed, 24 insertions(+), 31 deletions(-) diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index 02af64b82c6..1762146c09a 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -1046,7 +1046,7 @@ get_object_address(ObjectType objtype, Node *object, address.classId = LargeObjectRelationId; address.objectId = oidparse(object); address.objectSubId = 0; - if (!LargeObjectExists(address.objectId)) + if (!LargeObjectExists(address.objectId) && !IsBinaryUpgrade) { if (!missing_ok) ereport(ERROR, diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 2bebefd0ba2..6bcb2f61fe8 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -1129,19 +1129,11 @@ main(int argc, char **argv) */ if (dopt.binary_upgrade && fout->remoteVersion >= 120000) { - TableInfo *lo_metadata = findTableByOid(LargeObjectMetadataRelationId); - TableInfo *shdepend = findTableByOid(SharedDependRelationId); + TableInfo *shdepend; - makeTableDataInfo(&dopt, lo_metadata); + shdepend = findTableByOid(SharedDependRelationId); makeTableDataInfo(&dopt, shdepend); - /* - * Save pg_largeobject_metadata's dump ID for use as a dependency for - * pg_shdepend and any large object comments/seclabels. - */ - lo_metadata_dumpId = lo_metadata->dataObj->dobj.dumpId; - addObjectDependency(&shdepend->dataObj->dobj, lo_metadata_dumpId); - /* * Only dump large object shdepend rows for this database. */ @@ -1149,22 +1141,20 @@ main(int argc, char **argv) "AND dbid = (SELECT oid FROM pg_database " " WHERE datname = current_database())"; - /* - * If upgrading from v16 or newer, only dump large objects with - * comments/seclabels. For these upgrades, pg_upgrade can copy/link - * pg_largeobject_metadata's files (which is usually faster) but we - * still need to dump LOs with comments/seclabels here so that the - * subsequent COMMENT and SECURITY LABEL commands work. pg_upgrade - * can't copy/link the files from older versions because aclitem - * (needed by pg_largeobject_metadata.lomacl) changed its storage - * format in v16. - */ - if (fout->remoteVersion >= 160000) - lo_metadata->dataObj->filtercond = "WHERE oid IN " - "(SELECT objoid FROM pg_description " - "WHERE classoid = " CppAsString2(LargeObjectRelationId) " " - "UNION SELECT objoid FROM pg_seclabel " - "WHERE classoid = " CppAsString2(LargeObjectRelationId) ")"; + if (fout->remoteVersion < 160000) + { + TableInfo *lo_metadata; + + lo_metadata = findTableByOid(LargeObjectMetadataRelationId); + makeTableDataInfo(&dopt, lo_metadata); + + /* + * Save pg_largeobject_metadata's dump ID for use as a dependency + * for pg_shdepend and any large object comments/seclabels. + */ + lo_metadata_dumpId = lo_metadata->dataObj->dobj.dumpId; + addObjectDependency(&shdepend->dataObj->dobj, lo_metadata_dumpId); + } } /* @@ -4079,7 +4069,7 @@ getLOs(Archive *fout) * We should've saved pg_largeobject_metadata's dump ID before * this point. */ - Assert(lo_metadata_dumpId); + Assert(lo_metadata_dumpId || fout->remoteVersion >= 160000); loinfo->dobj.dump &= ~(DUMP_COMPONENT_DATA | DUMP_COMPONENT_ACL | DUMP_COMPONENT_DEFINITION); @@ -4088,9 +4078,12 @@ getLOs(Archive *fout) * pg_largeobject_metadata so that any large object * comments/seclables are dumped after it. */ - loinfo->dobj.dependencies = (DumpId *) pg_malloc(sizeof(DumpId)); - loinfo->dobj.dependencies[0] = lo_metadata_dumpId; - loinfo->dobj.nDeps = loinfo->dobj.allocDeps = 1; + if (fout->remoteVersion < 160000) + { + loinfo->dobj.dependencies = (DumpId *) pg_malloc(sizeof(DumpId)); + loinfo->dobj.dependencies[0] = lo_metadata_dumpId; + loinfo->dobj.nDeps = loinfo->dobj.allocDeps = 1; + } } else loinfo->dobj.dump &= ~DUMP_COMPONENT_DATA; -- 2.50.1 (Apple Git-155) --07s5YKCMhGM2F9Zp-- ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-02-05 17:26 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 v2 3/3] Replace matview WITH OLD DATA Erik Wienhold <ewie@ewie.name> 2026-02-04 20:04 [PATCH v1 1/1] fix pg_largeobject_metadata file transfer Nathan Bossart <nathan@postgresql.org> 2026-02-05 17:26 [PATCH v2 1/1] fix pg_largeobject_metadata file transfer Nathan Bossart <nathan@postgresql.org>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox