agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v5 1/2] In-place table persistence change 6+ messages / 2 participants [nested] [flat]
* [PATCH v5 1/2] In-place table persistence change @ 2020-11-11 12:51 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Kyotaro Horiguchi @ 2020-11-11 12:51 UTC (permalink / raw) Even though ALTER TABLE SET LOGGED/UNLOGGED does not require data rewriting, currently it runs heap rewrite which causes large amount of file I/O. This patch makes the command run without heap rewrite. Addition to that, SET LOGGED while wal_level > minimal emits WAL using XLOG_FPI instead of massive number of HEAP_INSERT's, which should be smaller. Also this allows for the cleanup of files left behind in the crash of the transaction that created it. --- src/backend/access/rmgrdesc/smgrdesc.c | 23 ++ src/backend/access/transam/README | 8 + src/backend/access/transam/xlog.c | 17 + src/backend/catalog/storage.c | 436 +++++++++++++++++++++++-- src/backend/commands/tablecmds.c | 246 +++++++++++--- src/backend/storage/buffer/bufmgr.c | 88 +++++ src/backend/storage/file/reinit.c | 316 ++++++++++++------ src/backend/storage/smgr/md.c | 13 +- src/backend/storage/smgr/smgr.c | 6 + src/common/relpath.c | 4 +- src/include/catalog/storage.h | 2 + src/include/catalog/storage_xlog.h | 22 +- src/include/common/relpath.h | 6 +- src/include/storage/bufmgr.h | 2 + src/include/storage/md.h | 2 + src/include/storage/reinit.h | 3 +- src/include/storage/smgr.h | 1 + 17 files changed, 1028 insertions(+), 167 deletions(-) diff --git a/src/backend/access/rmgrdesc/smgrdesc.c b/src/backend/access/rmgrdesc/smgrdesc.c index 7755553d57..2c109b8ca4 100644 --- a/src/backend/access/rmgrdesc/smgrdesc.c +++ b/src/backend/access/rmgrdesc/smgrdesc.c @@ -40,6 +40,23 @@ smgr_desc(StringInfo buf, XLogReaderState *record) xlrec->blkno, xlrec->flags); pfree(path); } + else if (info == XLOG_SMGR_UNLINK) + { + xl_smgr_unlink *xlrec = (xl_smgr_unlink *) rec; + char *path = relpathperm(xlrec->rnode, xlrec->forkNum); + + appendStringInfoString(buf, path); + pfree(path); + } + else if (info == XLOG_SMGR_BUFPERSISTENCE) + { + xl_smgr_bufpersistence *xlrec = (xl_smgr_bufpersistence *) rec; + char *path = relpathperm(xlrec->rnode, MAIN_FORKNUM); + + appendStringInfoString(buf, path); + appendStringInfo(buf, " persistence %d", xlrec->persistence); + pfree(path); + } } const char * @@ -55,6 +72,12 @@ smgr_identify(uint8 info) case XLOG_SMGR_TRUNCATE: id = "TRUNCATE"; break; + case XLOG_SMGR_UNLINK: + id = "UNLINK"; + break; + case XLOG_SMGR_BUFPERSISTENCE: + id = "BUFPERSISTENCE"; + break; } return id; diff --git a/src/backend/access/transam/README b/src/backend/access/transam/README index 1edc8180c1..547107a771 100644 --- a/src/backend/access/transam/README +++ b/src/backend/access/transam/README @@ -724,6 +724,14 @@ we must panic and abort recovery. The DBA will have to manually clean up then restart recovery. This is part of the reason for not writing a WAL entry until we've successfully done the original action. +The CLEANUP fork file +-------------------------------- + +An CLEANUP fork is created when a new relation file is created to mark +the relfilenode needs to be cleaned up at recovery time. In contrast +to 4 above, failure to remove an CLEANUP fork file will lead to data +loss, in which case the server will shut down. + Skipping WAL for New RelFileNode -------------------------------- diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index b18257c198..6dcbcbe387 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -40,6 +40,7 @@ #include "catalog/catversion.h" #include "catalog/pg_control.h" #include "catalog/pg_database.h" +#include "catalog/storage.h" #include "commands/progress.h" #include "commands/tablespace.h" #include "common/controldata_utils.h" @@ -4442,6 +4443,14 @@ ReadRecord(XLogReaderState *xlogreader, int emode, { ereport(DEBUG1, (errmsg_internal("reached end of WAL in pg_wal, entering archive recovery"))); + + /* cleanup garbage files left during crash recovery */ + ResetUnloggedRelations(UNLOGGED_RELATION_DROP_BUFFER | + UNLOGGED_RELATION_CLEANUP); + + /* run rollback cleanup if any */ + smgrDoPendingDeletes(false); + InArchiveRecovery = true; if (StandbyModeRequested) StandbyMode = true; @@ -7455,6 +7464,14 @@ StartupXLOG(void) } } + /* cleanup garbage files left during crash recovery */ + if (!InArchiveRecovery) + ResetUnloggedRelations(UNLOGGED_RELATION_DROP_BUFFER | + UNLOGGED_RELATION_CLEANUP); + + /* run rollback cleanup if any */ + smgrDoPendingDeletes(false); + /* Allow resource managers to do any required cleanup. */ for (rmid = 0; rmid <= RM_MAX_ID; rmid++) { diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index cba7a9ada0..c54d70747f 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -19,6 +19,7 @@ #include "postgres.h" +#include "access/amapi.h" #include "access/parallel.h" #include "access/visibilitymap.h" #include "access/xact.h" @@ -27,6 +28,7 @@ #include "access/xlogutils.h" #include "catalog/storage.h" #include "catalog/storage_xlog.h" +#include "common/hashfn.h" #include "miscadmin.h" #include "storage/freespace.h" #include "storage/smgr.h" @@ -57,9 +59,16 @@ int wal_skip_threshold = 2048; /* in kilobytes */ * but I'm being paranoid. */ +#define PDOP_DELETE (0) +#define PDOP_UNLINK_FORK (1 << 0) +#define PDOP_SET_PERSISTENCE (1 << 1) + typedef struct PendingRelDelete { RelFileNode relnode; /* relation that may need to be deleted */ + int op; /* operation mask */ + bool bufpersistence; /* buffer persistence to set */ + int unlink_forknum; /* forknum to unlink */ BackendId backend; /* InvalidBackendId if not a temp rel */ bool atCommit; /* T=delete at commit; F=delete at abort */ int nestLevel; /* xact nesting level of request */ @@ -75,6 +84,24 @@ typedef struct PendingRelSync static PendingRelDelete *pendingDeletes = NULL; /* head of linked list */ HTAB *pendingSyncHash = NULL; +typedef struct SRelHashEntry +{ + SMgrRelation srel; + char status; /* for simplehash use */ +} SRelHashEntry; + +/* define hashtable for workarea for pending deletes */ +#define SH_PREFIX srelhash +#define SH_ELEMENT_TYPE SRelHashEntry +#define SH_KEY_TYPE SMgrRelation +#define SH_KEY srel +#define SH_HASH_KEY(tb, key) \ + hash_bytes((unsigned char *)&key, sizeof(SMgrRelation)) +#define SH_EQUAL(tb, a, b) (memcmp(&a, &b, sizeof(SMgrRelation)) == 0) +#define SH_SCOPE static inline +#define SH_DEFINE +#define SH_DECLARE +#include "lib/simplehash.h" /* * AddPendingSync @@ -143,7 +170,17 @@ RelationCreateStorage(RelFileNode rnode, char relpersistence) return NULL; /* placate compiler */ } + /* + * We are going to create a new storage file. If server crashes before the + * current transaction ends the file needs to be cleaned up but there's no + * clue to the orphan files. The cleanup fork works as the sentinel to + * identify that situation. + */ srel = smgropen(rnode, backend); + smgrcreate(srel, CLEANUP2_FORKNUM, false); + log_smgrcreate(&rnode, CLEANUP2_FORKNUM); + smgrimmedsync(srel, CLEANUP2_FORKNUM); + smgrcreate(srel, MAIN_FORKNUM, false); if (needs_wal) @@ -153,12 +190,25 @@ RelationCreateStorage(RelFileNode rnode, char relpersistence) pending = (PendingRelDelete *) MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); pending->relnode = rnode; + pending->op = PDOP_DELETE; pending->backend = backend; pending->atCommit = false; /* delete if abort */ pending->nestLevel = GetCurrentTransactionNestLevel(); pending->next = pendingDeletes; pendingDeletes = pending; + /* drop cleanup fork at commit */ + pending = (PendingRelDelete *) + MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); + pending->relnode = rnode; + pending->op = PDOP_UNLINK_FORK; + pending->unlink_forknum = CLEANUP2_FORKNUM; + pending->backend = backend; + pending->atCommit = true; + pending->nestLevel = GetCurrentTransactionNestLevel(); + pending->next = pendingDeletes; + pendingDeletes = pending; + if (relpersistence == RELPERSISTENCE_PERMANENT && !XLogIsNeeded()) { Assert(backend == InvalidBackendId); @@ -168,6 +218,218 @@ RelationCreateStorage(RelFileNode rnode, char relpersistence) return srel; } +/* + * RelationCreateInitFork + * Create physical storage for the init fork of a relation. + * + * Create the init fork for the relation. + * + * This function is transactional. The creation is WAL-logged, and if the + * transaction aborts later on, the init fork will be removed. + */ +void +RelationCreateInitFork(Relation rel) +{ + RelFileNode rnode = rel->rd_node; + PendingRelDelete *pending; + SMgrRelation srel; + PendingRelDelete *prev; + PendingRelDelete *next; + bool create = true; + + /* switch buffer persistence */ + SetRelationBuffersPersistence(rel->rd_smgr, false, false); + + /* + * If we have entries for init-fork operation of this relation, that means + * that we have already registered pending delete entries to drop + * preexisting init fork since before the current transaction started. This + * function reverts that change just by removing the entries. + */ + prev = NULL; + for (pending = pendingDeletes; pending != NULL; pending = next) + { + next = pending->next; + if (RelFileNodeEquals(rnode, pending->relnode) && + pending->op != PDOP_DELETE && + ((pending->op & PDOP_UNLINK_FORK) != 0 && + pending->unlink_forknum == CLEANUP_FORKNUM)) + { + if (prev) + prev->next = next; + else + pendingDeletes = next; + pfree(pending); + + create = false; + } + else + { + /* unrelated entry, don't touch it */ + prev = pending; + } + } + + if (!create) + return; + + /* + * We are going to create the init fork. If server crashes before the + * current transaction ends the init fork left alone corrupts data while + * recovery. The cleanup fork works as the sentinel to identify that + * situation. + */ + srel = smgropen(rnode, InvalidBackendId); + smgrcreate(srel, CLEANUP_FORKNUM, false); + log_smgrcreate(&rnode, CLEANUP_FORKNUM); + smgrimmedsync(srel, CLEANUP_FORKNUM); + + /* We don't have existing init fork, create it. */ + smgrcreate(srel, INIT_FORKNUM, false); + + /* + * index-init fork needs further initialization. ambuildempty shoud do + * WAL-log and file sync by itself but otherwise we do that by myself. + */ + if (rel->rd_rel->relkind == RELKIND_INDEX) + rel->rd_indam->ambuildempty(rel); + else + { + log_smgrcreate(&rnode, INIT_FORKNUM); + smgrimmedsync(srel, INIT_FORKNUM); + } + + /* drop this init fork file at abort and revert persistence */ + pending = (PendingRelDelete *) + MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); + pending->relnode = rnode; + pending->op = PDOP_UNLINK_FORK | PDOP_SET_PERSISTENCE; + pending->unlink_forknum = INIT_FORKNUM; + pending->bufpersistence = true; + pending->backend = InvalidBackendId; + pending->atCommit = false; + pending->nestLevel = GetCurrentTransactionNestLevel(); + pending->next = pendingDeletes; + pendingDeletes = pending; + + /* drop cleanup fork at abort */ + pending = (PendingRelDelete *) + MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); + pending->relnode = rnode; + pending->op = PDOP_UNLINK_FORK; + pending->unlink_forknum = CLEANUP_FORKNUM; + pending->backend = InvalidBackendId; + pending->atCommit = false; + pending->nestLevel = GetCurrentTransactionNestLevel(); + pending->next = pendingDeletes; + pendingDeletes = pending; + + /* drop cleanup fork at commit */ + pending = (PendingRelDelete *) + MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); + pending->relnode = rnode; + pending->op = PDOP_UNLINK_FORK; + pending->unlink_forknum = CLEANUP_FORKNUM; + pending->backend = InvalidBackendId; + pending->atCommit = true; + pending->nestLevel = GetCurrentTransactionNestLevel(); + pending->next = pendingDeletes; + pendingDeletes = pending; +} + +/* + * RelationDropInitFork + * Delete physical storage for the init fork of a relation. + * + * Register pending-delete of the init fork. The real deletion is performed by + * smgrDoPendingDeletes at commit. + * + * This function is transactional. If the transaction aborts later on, the + * deletion doesn't happen. + */ +void +RelationDropInitFork(Relation rel) +{ + RelFileNode rnode = rel->rd_node; + PendingRelDelete *pending; + PendingRelDelete *prev; + PendingRelDelete *next; + bool inxact_created = false; + + /* switch buffer persistence */ + SetRelationBuffersPersistence(rel->rd_smgr, true, false); + + /* + * If we have entries for init-fork operation of this relation, that means + * that we have created the init fork in the current transaction. We + * immediately remove the init and cleanup forks immediately in that case. + * Otherwise just reister pending-delete for the existing init fork. + */ + prev = NULL; + for (pending = pendingDeletes; pending != NULL; pending = next) + { + next = pending->next; + if (RelFileNodeEquals(rnode, pending->relnode) && + pending->op != PDOP_DELETE && + ((pending->op & PDOP_UNLINK_FORK) != 0 && + pending->unlink_forknum == CLEANUP_FORKNUM)) + { + /* unlink list entry */ + if (prev) + prev->next = next; + else + pendingDeletes = next; + pfree(pending); + + inxact_created = true; + } + else + { + /* unrelated entry, don't touch it */ + prev = pending; + } + } + + if (inxact_created) + { + SMgrRelation srel = smgropen(rnode, InvalidBackendId); + + /* + * INIT/CLEANUP forks never be loaded to shared buffer so no point in + * dropping buffers for these files. + */ + log_smgrunlink(&rnode, INIT_FORKNUM); + smgrunlink(srel, INIT_FORKNUM, false); + log_smgrunlink(&rnode, CLEANUP_FORKNUM); + smgrunlink(srel, CLEANUP_FORKNUM, false); + return; + } + + /* register drop of this init fork file at commit */ + pending = (PendingRelDelete *) + MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); + pending->relnode = rnode; + pending->op = PDOP_UNLINK_FORK; + pending->unlink_forknum = INIT_FORKNUM; + pending->backend = InvalidBackendId; + pending->atCommit = true; + pending->nestLevel = GetCurrentTransactionNestLevel(); + pending->next = pendingDeletes; + pendingDeletes = pending; + + /* revert buffer-persistence changes at abort */ + pending = (PendingRelDelete *) + MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); + pending->relnode = rnode; + pending->op = PDOP_SET_PERSISTENCE; + pending->bufpersistence = false; + pending->backend = InvalidBackendId; + pending->atCommit = false; + pending->nestLevel = GetCurrentTransactionNestLevel(); + pending->next = pendingDeletes; + pendingDeletes = pending; +} + /* * Perform XLogInsert of an XLOG_SMGR_CREATE record to WAL. */ @@ -187,6 +449,44 @@ log_smgrcreate(const RelFileNode *rnode, ForkNumber forkNum) XLogInsert(RM_SMGR_ID, XLOG_SMGR_CREATE | XLR_SPECIAL_REL_UPDATE); } +/* + * Perform XLogInsert of an XLOG_SMGR_UNLINK record to WAL. + */ +void +log_smgrunlink(const RelFileNode *rnode, ForkNumber forkNum) +{ + xl_smgr_unlink xlrec; + + /* + * Make an XLOG entry reporting the file unlink. + */ + xlrec.rnode = *rnode; + xlrec.forkNum = forkNum; + + XLogBeginInsert(); + XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + XLogInsert(RM_SMGR_ID, XLOG_SMGR_UNLINK | XLR_SPECIAL_REL_UPDATE); +} + +/* + * Perform XLogInsert of an XLOG_SMGR_BUFPERSISTENCE record to WAL. + */ +void +log_smgrbufpersistence(const RelFileNode *rnode, bool persistence) +{ + xl_smgr_bufpersistence xlrec; + + /* + * Make an XLOG entry reporting the change of buffer persistence. + */ + xlrec.rnode = *rnode; + xlrec.persistence = persistence; + + XLogBeginInsert(); + XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + XLogInsert(RM_SMGR_ID, XLOG_SMGR_BUFPERSISTENCE | XLR_SPECIAL_REL_UPDATE); +} + /* * RelationDropStorage * Schedule unlinking of physical storage at transaction commit. @@ -200,6 +500,7 @@ RelationDropStorage(Relation rel) pending = (PendingRelDelete *) MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); pending->relnode = rel->rd_node; + pending->op = PDOP_DELETE; pending->backend = rel->rd_backend; pending->atCommit = true; /* delete if commit */ pending->nestLevel = GetCurrentTransactionNestLevel(); @@ -602,59 +903,97 @@ smgrDoPendingDeletes(bool isCommit) int nrels = 0, maxrels = 0; SMgrRelation *srels = NULL; + srelhash_hash *close_srels = NULL; + bool found; prev = NULL; for (pending = pendingDeletes; pending != NULL; pending = next) { + SMgrRelation srel; + next = pending->next; if (pending->nestLevel < nestLevel) { /* outer-level entries should not be processed yet */ prev = pending; + continue; } + + /* unlink list entry first, so we don't retry on failure */ + if (prev) + prev->next = next; else + pendingDeletes = next; + + if (pending->atCommit != isCommit) { - /* unlink list entry first, so we don't retry on failure */ - if (prev) - prev->next = next; - else - pendingDeletes = next; - /* do deletion if called for */ - if (pending->atCommit == isCommit) - { - SMgrRelation srel; - - srel = smgropen(pending->relnode, pending->backend); - - /* allocate the initial array, or extend it, if needed */ - if (maxrels == 0) - { - maxrels = 8; - srels = palloc(sizeof(SMgrRelation) * maxrels); - } - else if (maxrels <= nrels) - { - maxrels *= 2; - srels = repalloc(srels, sizeof(SMgrRelation) * maxrels); - } - - srels[nrels++] = srel; - } /* must explicitly free the list entry */ pfree(pending); /* prev does not change */ + continue; + } + + if (close_srels == NULL) + close_srels = srelhash_create(CurrentMemoryContext, 32, NULL); + + srel = smgropen(pending->relnode, pending->backend); + + /* Uniquify the smgr relations */ + srelhash_insert(close_srels, srel, &found); + + if (pending->op != PDOP_DELETE) + { + if (pending->op & PDOP_UNLINK_FORK) + { + /* other forks needs to drop buffers */ + Assert(pending->unlink_forknum == INIT_FORKNUM || + pending->unlink_forknum == CLEANUP_FORKNUM || + pending->unlink_forknum == CLEANUP2_FORKNUM); + + log_smgrunlink(&pending->relnode, pending->unlink_forknum); + smgrunlink(srel, pending->unlink_forknum, false); + + } + + if (pending->op & PDOP_SET_PERSISTENCE) + SetRelationBuffersPersistence(srel, pending->bufpersistence, + InRecovery); + } + else + { + /* allocate the initial array, or extend it, if needed */ + if (maxrels == 0) + { + maxrels = 8; + srels = palloc(sizeof(SMgrRelation) * maxrels); + } + else if (maxrels <= nrels) + { + maxrels *= 2; + srels = repalloc(srels, sizeof(SMgrRelation) * maxrels); + } + + srels[nrels++] = srel; } } if (nrels > 0) { smgrdounlinkall(srels, nrels, false); - - for (int i = 0; i < nrels; i++) - smgrclose(srels[i]); - pfree(srels); } + + if (close_srels) + { + srelhash_iterator i; + SRelHashEntry *ent; + + /* close smgr relatoins */ + srelhash_start_iterate(close_srels, &i); + while ((ent = srelhash_iterate(close_srels, &i)) != NULL) + smgrclose(ent->srel); + srelhash_destroy(close_srels); + } } /* @@ -824,7 +1163,8 @@ smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr) for (pending = pendingDeletes; pending != NULL; pending = pending->next) { if (pending->nestLevel >= nestLevel && pending->atCommit == forCommit - && pending->backend == InvalidBackendId) + && pending->backend == InvalidBackendId + && pending->op == PDOP_DELETE) nrels++; } if (nrels == 0) @@ -837,7 +1177,8 @@ smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr) for (pending = pendingDeletes; pending != NULL; pending = pending->next) { if (pending->nestLevel >= nestLevel && pending->atCommit == forCommit - && pending->backend == InvalidBackendId) + && pending->backend == InvalidBackendId && + pending->op == PDOP_DELETE) { *rptr = pending->relnode; rptr++; @@ -917,6 +1258,15 @@ smgr_redo(XLogReaderState *record) reln = smgropen(xlrec->rnode, InvalidBackendId); smgrcreate(reln, xlrec->forkNum, true); } + else if (info == XLOG_SMGR_UNLINK) + { + xl_smgr_unlink *xlrec = (xl_smgr_unlink *) XLogRecGetData(record); + SMgrRelation reln; + + reln = smgropen(xlrec->rnode, InvalidBackendId); + smgrunlink(reln, xlrec->forkNum, true); + smgrclose(reln); + } else if (info == XLOG_SMGR_TRUNCATE) { xl_smgr_truncate *xlrec = (xl_smgr_truncate *) XLogRecGetData(record); @@ -1005,6 +1355,28 @@ smgr_redo(XLogReaderState *record) FreeFakeRelcacheEntry(rel); } + else if (info == XLOG_SMGR_BUFPERSISTENCE) + { + xl_smgr_bufpersistence *xlrec = + (xl_smgr_bufpersistence *) XLogRecGetData(record); + SMgrRelation reln; + PendingRelDelete *pending; + + reln = smgropen(xlrec->rnode, InvalidBackendId); + SetRelationBuffersPersistence(reln, xlrec->persistence, true); + + /* revert buffer-persistence changes at abort */ + pending = (PendingRelDelete *) + MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); + pending->relnode = xlrec->rnode; + pending->op = PDOP_SET_PERSISTENCE; + pending->bufpersistence = !xlrec->persistence; + pending->backend = InvalidBackendId; + pending->atCommit = false; + pending->nestLevel = GetCurrentTransactionNestLevel(); + pending->next = pendingDeletes; + pendingDeletes = pending; + } else elog(PANIC, "smgr_redo: unknown op code %u", info); } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 993da56d43..37a15d31ee 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -50,6 +50,7 @@ #include "commands/defrem.h" #include "commands/event_trigger.h" #include "commands/policy.h" +#include "commands/progress.h" #include "commands/sequence.h" #include "commands/tablecmds.h" #include "commands/tablespace.h" @@ -4917,6 +4918,170 @@ ATParseTransformCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, return newcmd; } +/* + * RelationChangePersistence: do in-place persistence change of a relation + */ +static void +RelationChangePersistence(AlteredTableInfo *tab, char persistence, + LOCKMODE lockmode) +{ + Relation rel; + Relation classRel; + HeapTuple tuple, + newtuple; + Datum new_val[Natts_pg_class]; + bool new_null[Natts_pg_class], + new_repl[Natts_pg_class]; + int i; + List *relids; + ListCell *lc_oid; + + Assert(tab->rewrite == AT_REWRITE_ALTER_PERSISTENCE); + Assert(lockmode == AccessExclusiveLock); + + /* + * Under the following condition, we need to call ATRewriteTable, which + * cannot be false in the AT_REWRITE_ALTER_PERSISTENCE case. + */ + Assert(tab->constraints == NULL && tab->partition_constraint == NULL && + tab->newvals == NULL && !tab->verify_new_notnull); + + rel = table_open(tab->relid, lockmode); + + Assert(rel->rd_rel->relpersistence != persistence); + + elog(DEBUG1, "perform im-place persistnce change"); + + RelationOpenSmgr(rel); + + /* + * First we collect all relations that we need to change persistence. + */ + + /* Collect OIDs of indexes and toast relations */ + relids = RelationGetIndexList(rel); + relids = lcons_oid(rel->rd_id, relids); + + /* Add toast relation if any */ + if (OidIsValid(rel->rd_rel->reltoastrelid)) + { + List *toastidx; + Relation toastrel = table_open(rel->rd_rel->reltoastrelid, lockmode); + + RelationOpenSmgr(toastrel); + relids = lappend_oid(relids, rel->rd_rel->reltoastrelid); + toastidx = RelationGetIndexList(toastrel); + relids = list_concat(relids, toastidx); + pfree(toastidx); + table_close(toastrel, NoLock); + } + + table_close(rel, NoLock); + + /* Make changes in storage */ + classRel = table_open(RelationRelationId, RowExclusiveLock); + + foreach (lc_oid, relids) + { + Oid reloid = lfirst_oid(lc_oid); + Relation r = relation_open(reloid, lockmode); + + /* + * Some access methods do not accept in-place persistence change. For + * example, GiST uses page LSNs to figure out whether a block has + * changed, where UNLOGGED GiST indexes use fake LSNs that are + * incompatible with real LSNs used for LOGGED ones. + * + * XXXX: We don't bother allowing in-place persistence change for index + * methods other than btree for now. + */ + if (r->rd_rel->relkind == RELKIND_INDEX && + r->rd_rel->relam != BTREE_AM_OID) + { + int reindex_flags; + + /* reindex doesn't allow concurrent use of the index */ + table_close(r, NoLock); + + reindex_flags = + REINDEX_REL_SUPPRESS_INDEX_USE | + REINDEX_REL_CHECK_CONSTRAINTS; + + /* Set the same persistence with the parent relation. */ + if (persistence == RELPERSISTENCE_UNLOGGED) + reindex_flags |= REINDEX_REL_FORCE_INDEXES_UNLOGGED; + else + reindex_flags |= REINDEX_REL_FORCE_INDEXES_PERMANENT; + + reindex_index(reloid, reindex_flags, persistence, 0); + + continue; + } + + RelationOpenSmgr(r); + + /* Create or drop init fork */ + if (persistence == RELPERSISTENCE_UNLOGGED) + RelationCreateInitFork(r); + else + RelationDropInitFork(r); + + /* + * When this relation gets WAL-logged, immediately sync all files but + * initfork to establish the initial state on storage. Buffers have + * already flushed out by RelationCreate(Drop)InitFork called just + * above. Initfork should have been synced as needed. + */ + if (persistence == RELPERSISTENCE_PERMANENT) + { + for (i = 0 ; i < INIT_FORKNUM ; i++) + { + if (smgrexists(r->rd_smgr, i)) + smgrimmedsync(r->rd_smgr, i); + } + } + + /* Update catalog */ + tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", reloid); + + memset(new_val, 0, sizeof(new_val)); + memset(new_null, false, sizeof(new_null)); + memset(new_repl, false, sizeof(new_repl)); + + new_val[Anum_pg_class_relpersistence - 1] = CharGetDatum(persistence); + new_null[Anum_pg_class_relpersistence - 1] = false; + new_repl[Anum_pg_class_relpersistence - 1] = true; + + newtuple = heap_modify_tuple(tuple, RelationGetDescr(classRel), + new_val, new_null, new_repl); + + CatalogTupleUpdate(classRel, &newtuple->t_self, newtuple); + heap_freetuple(newtuple); + + /* + * While wal_level >= replica, switching to LOGGED requires the + * relation content to be WAL-logged to recover the table. + */ + if (persistence == RELPERSISTENCE_PERMANENT && XLogIsNeeded()) + { + ForkNumber fork; + + for (fork = 0; fork < INIT_FORKNUM ; fork++) + { + if (smgrexists(r->rd_smgr, fork)) + log_newpage_range(r, fork, + 0, smgrnblocks(r->rd_smgr, fork), false); + } + } + + table_close(r, NoLock); + } + + table_close(classRel, NoLock); +} + /* * ATRewriteTables: ALTER TABLE phase 3 */ @@ -5037,45 +5202,52 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode, tab->relid, tab->rewrite); - /* - * Create transient table that will receive the modified data. - * - * Ensure it is marked correctly as logged or unlogged. We have - * to do this here so that buffers for the new relfilenode will - * have the right persistence set, and at the same time ensure - * that the original filenode's buffers will get read in with the - * correct setting (i.e. the original one). Otherwise a rollback - * after the rewrite would possibly result with buffers for the - * original filenode having the wrong persistence setting. - * - * NB: This relies on swap_relation_files() also swapping the - * persistence. That wouldn't work for pg_class, but that can't be - * unlogged anyway. - */ - OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, persistence, - lockmode); + if (tab->rewrite == AT_REWRITE_ALTER_PERSISTENCE) + RelationChangePersistence(tab, persistence, lockmode); + else + { + /* + * Create transient table that will receive the modified data. + * + * Ensure it is marked correctly as logged or unlogged. We + * have to do this here so that buffers for the new relfilenode + * will have the right persistence set, and at the same time + * ensure that the original filenode's buffers will get read in + * with the correct setting (i.e. the original one). Otherwise + * a rollback after the rewrite would possibly result with + * buffers for the original filenode having the wrong + * persistence setting. + * + * NB: This relies on swap_relation_files() also swapping the + * persistence. That wouldn't work for pg_class, but that can't + * be unlogged anyway. + */ + OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, persistence, + lockmode); - /* - * Copy the heap data into the new table with the desired - * modifications, and test the current data within the table - * against new constraints generated by ALTER TABLE commands. - */ - ATRewriteTable(tab, OIDNewHeap, lockmode); + /* + * Copy the heap data into the new table with the desired + * modifications, and test the current data within the table + * against new constraints generated by ALTER TABLE commands. + */ + ATRewriteTable(tab, OIDNewHeap, lockmode); - /* - * Swap the physical files of the old and new heaps, then rebuild - * indexes and discard the old heap. We can use RecentXmin for - * the table's new relfrozenxid because we rewrote all the tuples - * in ATRewriteTable, so no older Xid remains in the table. Also, - * we never try to swap toast tables by content, since we have no - * interest in letting this code work on system catalogs. - */ - finish_heap_swap(tab->relid, OIDNewHeap, - false, false, true, - !OidIsValid(tab->newTableSpace), - RecentXmin, - ReadNextMultiXactId(), - persistence); + /* + * Swap the physical files of the old and new heaps, then + * rebuild indexes and discard the old heap. We can use + * RecentXmin for the table's new relfrozenxid because we + * rewrote all the tuples in ATRewriteTable, so no older Xid + * remains in the table. Also, we never try to swap toast + * tables by content, since we have no interest in letting this + * code work on system catalogs. + */ + finish_heap_swap(tab->relid, OIDNewHeap, + false, false, true, + !OidIsValid(tab->newTableSpace), + RecentXmin, + ReadNextMultiXactId(), + persistence); + } } else { diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 561c212092..eacbdc6447 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -37,6 +37,7 @@ #include "access/xlog.h" #include "catalog/catalog.h" #include "catalog/storage.h" +#include "catalog/storage_xlog.h" #include "executor/instrument.h" #include "lib/binaryheap.h" #include "miscadmin.h" @@ -3094,6 +3095,93 @@ DropRelFileNodeBuffers(SMgrRelation smgr_reln, ForkNumber *forkNum, } } +/* --------------------------------------------------------------------- + * SetRelFileNodeBuffersPersistence + * + * This function changes the persistence of all buffer pages of a relation + * then writes all dirty pages of the relation out to disk when switching + * to PERMANENT. (or more accurately, out to kernel disk buffers), + * ensuring that the kernel has an up-to-date view of the relation. + * + * Generally, the caller should be holding AccessExclusiveLock on the + * target relation to ensure that no other backend is busy dirtying + * more blocks of the relation; the effects can't be expected to last + * after the lock is released. + * + * XXX currently it sequentially searches the buffer pool, should be + * changed to more clever ways of searching. This routine is not + * used in any performance-critical code paths, so it's not worth + * adding additional overhead to normal paths to make it go faster; + * but see also DropRelFileNodeBuffers. + * -------------------------------------------------------------------- + */ +void +SetRelationBuffersPersistence(SMgrRelation srel, bool permanent, bool isRedo) +{ + int i; + RelFileNodeBackend rnode = srel->smgr_rnode; + + Assert (!RelFileNodeBackendIsTemp(rnode)); + + if (!isRedo) + log_smgrbufpersistence(&srel->smgr_rnode.node, permanent); + + ResourceOwnerEnlargeBuffers(CurrentResourceOwner); + + for (i = 0; i < NBuffers; i++) + { + BufferDesc *bufHdr = GetBufferDescriptor(i); + uint32 buf_state; + + if (!RelFileNodeEquals(bufHdr->tag.rnode, rnode.node)) + continue; + + ReservePrivateRefCountEntry(); + + buf_state = LockBufHdr(bufHdr); + + if (!RelFileNodeEquals(bufHdr->tag.rnode, rnode.node)) + { + UnlockBufHdr(bufHdr, buf_state); + continue; + } + + if (permanent) + { + /* Init fork is being dropped, drop buffers for it. */ + if (bufHdr->tag.forkNum == INIT_FORKNUM) + { + InvalidateBuffer(bufHdr); + continue; + } + + buf_state |= BM_PERMANENT; + pg_atomic_write_u32(&bufHdr->state, buf_state); + + /* we flush this buffer when switching to PERMANENT */ + if ((buf_state & (BM_VALID | BM_DIRTY)) == (BM_VALID | BM_DIRTY)) + { + PinBuffer_Locked(bufHdr); + LWLockAcquire(BufferDescriptorGetContentLock(bufHdr), + LW_SHARED); + FlushBuffer(bufHdr, srel); + LWLockRelease(BufferDescriptorGetContentLock(bufHdr)); + UnpinBuffer(bufHdr, true); + } + else + UnlockBufHdr(bufHdr, buf_state); + } + else + { + /* init fork is always BM_PERMANENT. See BufferAlloc */ + if (bufHdr->tag.forkNum != INIT_FORKNUM) + buf_state &= ~BM_PERMANENT; + + UnlockBufHdr(bufHdr, buf_state); + } + } +} + /* --------------------------------------------------------------------- * DropRelFileNodesAllBuffers * diff --git a/src/backend/storage/file/reinit.c b/src/backend/storage/file/reinit.c index 40c758d789..0eac1956cc 100644 --- a/src/backend/storage/file/reinit.c +++ b/src/backend/storage/file/reinit.c @@ -16,29 +16,50 @@ #include <unistd.h> +#include "access/xlog.h" +#include "catalog/pg_tablespace_d.h" #include "common/relpath.h" +#include "storage/bufmgr.h" #include "storage/copydir.h" #include "storage/fd.h" +#include "storage/md.h" #include "storage/reinit.h" +#include "storage/smgr.h" #include "utils/hsearch.h" #include "utils/memutils.h" static void ResetUnloggedRelationsInTablespaceDir(const char *tsdirname, - int op); + Oid tspid, int op); static void ResetUnloggedRelationsInDbspaceDir(const char *dbspacedirname, - int op); + Oid tspid, Oid dbid, int op); typedef struct { Oid reloid; /* hash key */ -} unlogged_relation_entry; + bool has_init; /* has INIT fork */ + bool dirty_init; /* needs to remove INIT fork */ + bool dirty_all; /* needs to remove all forks */ +} relfile_entry; /* - * Reset unlogged relations from before the last restart. + * Clean up and reset relation files from before the last restart. * - * If op includes UNLOGGED_RELATION_CLEANUP, we remove all forks of any - * relation with an "init" fork, except for the "init" fork itself. + * If op includes UNLOGGED_RELATION_CLEANUP, we perform different operations + * depending on the existence of the "cleanup" forks. * + * If CLEANUP_FORKNUM (clup) is present, we remove the init fork of the same + * relation along with the clup fork. + * + * If CLEANUP2_FORKNUM (cln2) is present we remove the whole relation along + * with the cln2 fork. + * + * Otherwise, if the "init" fork is found. we remove all forks of any relation + * with the "init" fork, except for the "init" fork itself. + * + * + * If op includes UNLOGGED_RELATION_DROP_BUFFER, we drop all buffers for all + * relations that have the "cleanup" and/or the "init" forks. + * * If op includes UNLOGGED_RELATION_INIT, we copy the "init" fork to the main * fork. */ @@ -68,7 +89,7 @@ ResetUnloggedRelations(int op) /* * First process unlogged files in pg_default ($PGDATA/base) */ - ResetUnloggedRelationsInTablespaceDir("base", op); + ResetUnloggedRelationsInTablespaceDir("base", DEFAULTTABLESPACE_OID, op); /* * Cycle through directories for all non-default tablespaces. @@ -77,13 +98,19 @@ ResetUnloggedRelations(int op) while ((spc_de = ReadDir(spc_dir, "pg_tblspc")) != NULL) { + Oid tspid; + if (strcmp(spc_de->d_name, ".") == 0 || strcmp(spc_de->d_name, "..") == 0) continue; snprintf(temp_path, sizeof(temp_path), "pg_tblspc/%s/%s", spc_de->d_name, TABLESPACE_VERSION_DIRECTORY); - ResetUnloggedRelationsInTablespaceDir(temp_path, op); + + tspid = atooid(spc_de->d_name); + Assert(tspid != 0); + + ResetUnloggedRelationsInTablespaceDir(temp_path, tspid, op); } FreeDir(spc_dir); @@ -99,7 +126,8 @@ ResetUnloggedRelations(int op) * Process one tablespace directory for ResetUnloggedRelations */ static void -ResetUnloggedRelationsInTablespaceDir(const char *tsdirname, int op) +ResetUnloggedRelationsInTablespaceDir(const char *tsdirname, + Oid tspid, int op) { DIR *ts_dir; struct dirent *de; @@ -126,6 +154,8 @@ ResetUnloggedRelationsInTablespaceDir(const char *tsdirname, int op) while ((de = ReadDir(ts_dir, tsdirname)) != NULL) { + Oid dbid; + /* * We're only interested in the per-database directories, which have * numeric names. Note that this code will also (properly) ignore "." @@ -136,7 +166,10 @@ ResetUnloggedRelationsInTablespaceDir(const char *tsdirname, int op) snprintf(dbspace_path, sizeof(dbspace_path), "%s/%s", tsdirname, de->d_name); - ResetUnloggedRelationsInDbspaceDir(dbspace_path, op); + dbid = atooid(de->d_name); + Assert(dbid != 0); + + ResetUnloggedRelationsInDbspaceDir(dbspace_path, tspid, dbid, op); } FreeDir(ts_dir); @@ -146,125 +179,226 @@ ResetUnloggedRelationsInTablespaceDir(const char *tsdirname, int op) * Process one per-dbspace directory for ResetUnloggedRelations */ static void -ResetUnloggedRelationsInDbspaceDir(const char *dbspacedirname, int op) +ResetUnloggedRelationsInDbspaceDir(const char *dbspacedirname, + Oid tspid, Oid dbid, int op) { DIR *dbspace_dir; struct dirent *de; char rm_path[MAXPGPATH * 2]; + HTAB *hash; + HASHCTL ctl; /* Caller must specify at least one operation. */ - Assert((op & (UNLOGGED_RELATION_CLEANUP | UNLOGGED_RELATION_INIT)) != 0); + Assert((op & (UNLOGGED_RELATION_CLEANUP | + UNLOGGED_RELATION_DROP_BUFFER | + UNLOGGED_RELATION_INIT)) != 0); /* * Cleanup is a two-pass operation. First, we go through and identify all * the files with init forks. Then, we go through again and nuke * everything with the same OID except the init fork. */ + + /* + * It's possible that someone could create a ton of unlogged relations + * in the same database & tablespace, so we'd better use a hash table + * rather than an array or linked list to keep track of which files + * need to be reset. Otherwise, this cleanup operation would be + * O(n^2). + */ + memset(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(Oid); + ctl.entrysize = sizeof(relfile_entry); + hash = hash_create("relfilenode cleanup hash", + 32, &ctl, HASH_ELEM | HASH_BLOBS); + + /* Collect INIT and CLEANUP forks in the directory. */ + dbspace_dir = AllocateDir(dbspacedirname); + while ((de = ReadDir(dbspace_dir, dbspacedirname)) != NULL) + { + int oidchars; + ForkNumber forkNum; + + /* Skip anything that doesn't look like a relation data file. */ + if (!parse_filename_for_nontemp_relation(de->d_name, &oidchars, + &forkNum)) + continue; + + if (forkNum == INIT_FORKNUM || + forkNum == CLEANUP_FORKNUM || forkNum == CLEANUP2_FORKNUM) + { + Oid key; + relfile_entry *ent; + bool found; + + /* + * Record the relfilenode information. If it has the CLEANUP fork, + * the relfilenode is in dirty state, where clean up is needed. + */ + key = atooid(de->d_name); + ent = hash_search(hash, &key, HASH_ENTER, &found); + + if (!found) + { + ent->has_init = false; + ent->dirty_init = false; + ent->dirty_all = false; + } + + if (forkNum == CLEANUP_FORKNUM) + ent->dirty_init = true; + else if (forkNum == CLEANUP2_FORKNUM) + ent->dirty_all = true; + else + { + Assert(forkNum == INIT_FORKNUM); + ent->has_init = true; + } + } + } + + /* Done with the first pass. */ + FreeDir(dbspace_dir); + + /* nothing to do if we don't have init nor cleanup forks */ + if (hash_get_num_entries(hash) < 1) + { + hash_destroy(hash); + return; + } + + if ((op & UNLOGGED_RELATION_DROP_BUFFER) != 0) + { + /* + * When we come here after recovery, smgr object for this file might + * have been created. In that case we need to drop all buffers then the + * smgr object before initializing the unlogged relation. This is safe + * as far as no other backends have accessed the relation before + * starting archive recovery. + */ + HASH_SEQ_STATUS status; + relfile_entry *ent; + SMgrRelation *srels = palloc(sizeof(SMgrRelation) * 8); + int maxrels = 8; + int nrels = 0; + int i; + + Assert(!HotStandbyActive()); + + hash_seq_init(&status, hash); + while((ent = (relfile_entry *) hash_seq_search(&status)) != NULL) + { + RelFileNodeBackend rel; + + /* + * The relation is persistent and stays remain persistent. Don't + * drop the buffers for this relation. + */ + if (ent->has_init && ent->dirty_init) + continue; + + if (maxrels <= nrels) + { + maxrels *= 2; + srels = repalloc(srels, sizeof(SMgrRelation) * maxrels); + } + + rel.backend = InvalidBackendId; + rel.node.spcNode = tspid; + rel.node.dbNode = dbid; + rel.node.relNode = ent->reloid; + + srels[nrels++] = smgropen(rel.node, InvalidBackendId); + } + + DropRelFileNodesAllBuffers(srels, nrels); + + for (i = 0 ; i < nrels ; i++) + smgrclose(srels[i]); + } + + /* + * Now, make a second pass and remove anything that matches. + */ if ((op & UNLOGGED_RELATION_CLEANUP) != 0) { - HTAB *hash; - HASHCTL ctl; - - /* - * It's possible that someone could create a ton of unlogged relations - * in the same database & tablespace, so we'd better use a hash table - * rather than an array or linked list to keep track of which files - * need to be reset. Otherwise, this cleanup operation would be - * O(n^2). - */ - ctl.keysize = sizeof(Oid); - ctl.entrysize = sizeof(unlogged_relation_entry); - ctl.hcxt = CurrentMemoryContext; - hash = hash_create("unlogged relation OIDs", 32, &ctl, - HASH_ELEM | HASH_BLOBS | HASH_CONTEXT); - - /* Scan the directory. */ - dbspace_dir = AllocateDir(dbspacedirname); - while ((de = ReadDir(dbspace_dir, dbspacedirname)) != NULL) - { - ForkNumber forkNum; - int oidchars; - unlogged_relation_entry ent; - - /* Skip anything that doesn't look like a relation data file. */ - if (!parse_filename_for_nontemp_relation(de->d_name, &oidchars, - &forkNum)) - continue; - - /* Also skip it unless this is the init fork. */ - if (forkNum != INIT_FORKNUM) - continue; - - /* - * Put the OID portion of the name into the hash table, if it - * isn't already. - */ - ent.reloid = atooid(de->d_name); - (void) hash_search(hash, &ent, HASH_ENTER, NULL); - } - - /* Done with the first pass. */ - FreeDir(dbspace_dir); - - /* - * If we didn't find any init forks, there's no point in continuing; - * we can bail out now. - */ - if (hash_get_num_entries(hash) == 0) - { - hash_destroy(hash); - return; - } - - /* - * Now, make a second pass and remove anything that matches. - */ dbspace_dir = AllocateDir(dbspacedirname); while ((de = ReadDir(dbspace_dir, dbspacedirname)) != NULL) { - ForkNumber forkNum; - int oidchars; - unlogged_relation_entry ent; + ForkNumber forkNum; + int oidchars; + Oid key; + relfile_entry *ent; + RelFileNodeBackend rel; /* Skip anything that doesn't look like a relation data file. */ if (!parse_filename_for_nontemp_relation(de->d_name, &oidchars, &forkNum)) continue; - /* We never remove the init fork. */ - if (forkNum == INIT_FORKNUM) - continue; - /* * See whether the OID portion of the name shows up in the hash * table. If so, nuke it! */ - ent.reloid = atooid(de->d_name); - if (hash_search(hash, &ent, HASH_FIND, NULL)) + key = atooid(de->d_name); + ent = hash_search(hash, &key, HASH_FIND, NULL); + + if (!ent) + continue; + + if (!ent->dirty_all) { - snprintf(rm_path, sizeof(rm_path), "%s/%s", - dbspacedirname, de->d_name); - if (unlink(rm_path) < 0) - ereport(ERROR, - (errcode_for_file_access(), - errmsg("could not remove file \"%s\": %m", - rm_path))); + /* clean permanent relations don't need cleanup */ + if (!ent->has_init) + continue; + + if (ent->dirty_init) + { + /* + * The crashed trasaction did SET UNLOGGED. This relation + * is restored to a LOGGED relation. + */ + if (forkNum != INIT_FORKNUM && forkNum != CLEANUP_FORKNUM) + continue; + } else - elog(DEBUG2, "unlinked file \"%s\"", rm_path); + { + /* + * we don't remove the INIT fork of a non-dirty + * relfilenode + */ + if (forkNum == INIT_FORKNUM) + continue; + } } + + /* so, nuke it! */ + snprintf(rm_path, sizeof(rm_path), "%s/%s", + dbspacedirname, de->d_name); + if (unlink(rm_path) < 0) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not remove file \"%s\": %m", + rm_path))); + + rel.backend = InvalidBackendId; + rel.node.spcNode = tspid; + rel.node.dbNode = dbid; + rel.node.relNode = atooid(de->d_name); + + ForgetRelationForkSyncRequests(rel, forkNum); } /* Cleanup is complete. */ FreeDir(dbspace_dir); - hash_destroy(hash); } + hash_destroy(hash); + hash = NULL; + /* * Initialization happens after cleanup is complete: we copy each init - * fork file to the corresponding main fork file. Note that if we are - * asked to do both cleanup and init, we may never get here: if the - * cleanup code determines that there are no init forks in this dbspace, - * it will return before we get to this point. + * fork file to the corresponding main fork file. */ if ((op & UNLOGGED_RELATION_INIT) != 0) { diff --git a/src/backend/storage/smgr/md.c b/src/backend/storage/smgr/md.c index 0643d714fb..6b37195c52 100644 --- a/src/backend/storage/smgr/md.c +++ b/src/backend/storage/smgr/md.c @@ -338,8 +338,10 @@ mdunlinkfork(RelFileNodeBackend rnode, ForkNumber forkNum, bool isRedo) if (ret == 0 || errno != ENOENT) { ret = unlink(path); + + /* failure of removing cleanup fork leads to a data loss. */ if (ret < 0 && errno != ENOENT) - ereport(WARNING, + ereport((forkNum != CLEANUP_FORKNUM ? WARNING : ERROR), (errcode_for_file_access(), errmsg("could not remove file \"%s\": %m", path))); } @@ -1024,6 +1026,15 @@ register_forget_request(RelFileNodeBackend rnode, ForkNumber forknum, RegisterSyncRequest(&tag, SYNC_FORGET_REQUEST, true /* retryOnError */ ); } +/* + * ForgetRelationForkSyncRequests -- forget any fsyncs and unlinks for a fork + */ +void +ForgetRelationForkSyncRequests(RelFileNodeBackend rnode, ForkNumber forknum) +{ + register_forget_request(rnode, forknum, 0); +} + /* * ForgetDatabaseSyncRequests -- forget any fsyncs and unlinks for a DB */ diff --git a/src/backend/storage/smgr/smgr.c b/src/backend/storage/smgr/smgr.c index 4dc24649df..96480e321d 100644 --- a/src/backend/storage/smgr/smgr.c +++ b/src/backend/storage/smgr/smgr.c @@ -662,6 +662,12 @@ smgrimmedsync(SMgrRelation reln, ForkNumber forknum) smgrsw[reln->smgr_which].smgr_immedsync(reln, forknum); } +void +smgrunlink(SMgrRelation reln, ForkNumber forknum, bool isRedo) +{ + smgrsw[reln->smgr_which].smgr_unlink(reln->smgr_rnode, forknum, isRedo); +} + /* * AtEOXact_SMgr * diff --git a/src/common/relpath.c b/src/common/relpath.c index 1f5c426ec0..479dcc248e 100644 --- a/src/common/relpath.c +++ b/src/common/relpath.c @@ -34,7 +34,9 @@ const char *const forkNames[] = { "main", /* MAIN_FORKNUM */ "fsm", /* FSM_FORKNUM */ "vm", /* VISIBILITYMAP_FORKNUM */ - "init" /* INIT_FORKNUM */ + "init", /* INIT_FORKNUM */ + "clup", /* CLEANUP_FORKNUM */ + "cln2" /* CLEANUP2_FORKNUM */ }; StaticAssertDecl(lengthof(forkNames) == (MAX_FORKNUM + 1), diff --git a/src/include/catalog/storage.h b/src/include/catalog/storage.h index 0ab32b44e9..382623159c 100644 --- a/src/include/catalog/storage.h +++ b/src/include/catalog/storage.h @@ -23,6 +23,8 @@ extern int wal_skip_threshold; extern SMgrRelation RelationCreateStorage(RelFileNode rnode, char relpersistence); +extern void RelationCreateInitFork(Relation rel); +extern void RelationDropInitFork(Relation rel); extern void RelationDropStorage(Relation rel); extern void RelationPreserveStorage(RelFileNode rnode, bool atCommit); extern void RelationPreTruncate(Relation rel); diff --git a/src/include/catalog/storage_xlog.h b/src/include/catalog/storage_xlog.h index f0814f1458..0fd0832a8b 100644 --- a/src/include/catalog/storage_xlog.h +++ b/src/include/catalog/storage_xlog.h @@ -22,13 +22,17 @@ /* * Declarations for smgr-related XLOG records * - * Note: we log file creation and truncation here, but logging of deletion - * actions is handled by xact.c, because it is part of transaction commit. + * Note: we log file creation, truncation, deletion and persistence change + * here. logging of deletion actions is mainly handled by xact.c, because it is + * part of transaction commit, but we log deletions happens outside of a + * transaction. */ /* XLOG gives us high 4 bits */ #define XLOG_SMGR_CREATE 0x10 #define XLOG_SMGR_TRUNCATE 0x20 +#define XLOG_SMGR_UNLINK 0x30 +#define XLOG_SMGR_BUFPERSISTENCE 0x40 typedef struct xl_smgr_create { @@ -36,6 +40,18 @@ typedef struct xl_smgr_create ForkNumber forkNum; } xl_smgr_create; +typedef struct xl_smgr_unlink +{ + RelFileNode rnode; + ForkNumber forkNum; +} xl_smgr_unlink; + +typedef struct xl_smgr_bufpersistence +{ + RelFileNode rnode; + bool persistence; +} xl_smgr_bufpersistence; + /* flags for xl_smgr_truncate */ #define SMGR_TRUNCATE_HEAP 0x0001 #define SMGR_TRUNCATE_VM 0x0002 @@ -51,6 +67,8 @@ typedef struct xl_smgr_truncate } xl_smgr_truncate; extern void log_smgrcreate(const RelFileNode *rnode, ForkNumber forkNum); +extern void log_smgrunlink(const RelFileNode *rnode, ForkNumber forkNum); +extern void log_smgrbufpersistence(const RelFileNode *rnode, bool persistence); extern void smgr_redo(XLogReaderState *record); extern void smgr_desc(StringInfo buf, XLogReaderState *record); diff --git a/src/include/common/relpath.h b/src/include/common/relpath.h index a44be11ca0..040070aa2b 100644 --- a/src/include/common/relpath.h +++ b/src/include/common/relpath.h @@ -43,7 +43,9 @@ typedef enum ForkNumber MAIN_FORKNUM = 0, FSM_FORKNUM, VISIBILITYMAP_FORKNUM, - INIT_FORKNUM + INIT_FORKNUM, + CLEANUP_FORKNUM, + CLEANUP2_FORKNUM /* * NOTE: if you add a new fork, change MAX_FORKNUM and possibly @@ -52,7 +54,7 @@ typedef enum ForkNumber */ } ForkNumber; -#define MAX_FORKNUM INIT_FORKNUM +#define MAX_FORKNUM CLEANUP2_FORKNUM #define FORKNAMECHARS 4 /* max chars for a fork name */ diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index fb00fda6a7..ccb0a388f6 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -205,6 +205,8 @@ extern void FlushRelationsAllBuffers(struct SMgrRelationData **smgrs, int nrels) extern void FlushDatabaseBuffers(Oid dbid); extern void DropRelFileNodeBuffers(struct SMgrRelationData *smgr_reln, ForkNumber *forkNum, int nforks, BlockNumber *firstDelBlock); +extern void SetRelationBuffersPersistence(struct SMgrRelationData *srel, + bool permanent, bool isRedo); extern void DropRelFileNodesAllBuffers(struct SMgrRelationData **smgr_reln, int nnodes); extern void DropDatabaseBuffers(Oid dbid); diff --git a/src/include/storage/md.h b/src/include/storage/md.h index 752b440864..3cbbbf2edd 100644 --- a/src/include/storage/md.h +++ b/src/include/storage/md.h @@ -41,6 +41,8 @@ extern void mdtruncate(SMgrRelation reln, ForkNumber forknum, BlockNumber nblocks); extern void mdimmedsync(SMgrRelation reln, ForkNumber forknum); +extern void ForgetRelationForkSyncRequests(RelFileNodeBackend rnode, + ForkNumber forknum); extern void ForgetDatabaseSyncRequests(Oid dbid); extern void DropRelationFiles(RelFileNode *delrels, int ndelrels, bool isRedo); diff --git a/src/include/storage/reinit.h b/src/include/storage/reinit.h index fad1e5c473..b969ba8e86 100644 --- a/src/include/storage/reinit.h +++ b/src/include/storage/reinit.h @@ -23,6 +23,7 @@ extern bool parse_filename_for_nontemp_relation(const char *name, int *oidchars, ForkNumber *fork); #define UNLOGGED_RELATION_CLEANUP 0x0001 -#define UNLOGGED_RELATION_INIT 0x0002 +#define UNLOGGED_RELATION_DROP_BUFFER 0x0002 +#define UNLOGGED_RELATION_INIT 0x0004 #endif /* REINIT_H */ diff --git a/src/include/storage/smgr.h b/src/include/storage/smgr.h index a6fbf7b6a6..1ac3e4a74a 100644 --- a/src/include/storage/smgr.h +++ b/src/include/storage/smgr.h @@ -86,6 +86,7 @@ extern void smgrclose(SMgrRelation reln); extern void smgrcloseall(void); extern void smgrclosenode(RelFileNodeBackend rnode); extern void smgrcreate(SMgrRelation reln, ForkNumber forknum, bool isRedo); +extern void smgrunlink(SMgrRelation reln, ForkNumber forknum, bool isRedo); extern void smgrdosyncall(SMgrRelation *rels, int nrels); extern void smgrdounlinkall(SMgrRelation *rels, int nrels, bool isRedo); extern void smgrextend(SMgrRelation reln, ForkNumber forknum, -- 2.27.0 ----Next_Part(Thu_Jan_14_17_32_17_2021_289)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v5-0002-New-command-ALTER-TABLE-ALL-IN-TABLESPACE-SET-LOG.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v4 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW @ 2024-05-21 16:35 Erik Wienhold <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Erik Wienhold @ 2024-05-21 16:35 UTC (permalink / raw) --- .../sgml/ref/create_materialized_view.sgml | 15 +- src/backend/commands/createas.c | 207 ++++++++++++++---- src/backend/commands/tablecmds.c | 8 +- src/backend/commands/view.c | 106 ++++++--- src/backend/parser/gram.y | 15 ++ src/bin/psql/tab-complete.in.c | 15 +- src/include/commands/view.h | 3 + src/include/nodes/parsenodes.h | 2 +- src/include/nodes/primnodes.h | 1 + src/test/regress/expected/matview.out | 191 ++++++++++++++++ src/test/regress/sql/matview.sql | 108 +++++++++ 11 files changed, 582 insertions(+), 89 deletions(-) diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml index 62d897931c..5e03320eb7 100644 --- a/doc/src/sgml/ref/create_materialized_view.sgml +++ b/doc/src/sgml/ref/create_materialized_view.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> +CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ] [ USING <replaceable class="parameter">method</replaceable> ] [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] @@ -60,6 +60,17 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> <title>Parameters</title> <variablelist> + <varlistentry> + <term><literal>OR REPLACE</literal></term> + <listitem> + <para> + Replaces a materialized view if it already exists. + Specifying <literal>OR REPLACE</literal> together with + <literal>IF NOT EXISTS</literal> is an error. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>IF NOT EXISTS</literal></term> <listitem> @@ -67,7 +78,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> Do not throw an error if a materialized view with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing materialized view is anything like the one that would - have been created. + have been created, unless you use <literal>OR REPLACE</literal> instead. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 5c92e48a56..d4bc5e5c08 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -79,55 +79,151 @@ static void intorel_destroy(DestReceiver *self); static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into) { - CreateStmt *create = makeNode(CreateStmt); - bool is_matview; + bool is_matview, + replace = false; char relkind; - Datum toast_options; - const char *const validnsps[] = HEAP_RELOPT_NAMESPACES; + Oid matviewOid = InvalidOid; ObjectAddress intoRelationAddr; /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */ is_matview = (into->viewQuery != NULL); relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION; - /* - * Create the target relation by faking up a CREATE TABLE parsetree and - * passing it to DefineRelation. - */ - create->relation = into->rel; - create->tableElts = attrList; - create->inhRelations = NIL; - create->ofTypename = NULL; - create->constraints = NIL; - create->options = into->options; - create->oncommit = into->onCommit; - create->tablespacename = into->tableSpaceName; - create->if_not_exists = false; - create->accessMethod = into->accessMethod; + /* Check if an existing materialized view needs to be replaced. */ + if (is_matview) + { + LOCKMODE lockmode; - /* - * Create the relation. (This will error out if there's an existing view, - * so we don't need more code to complain if "replace" is false.) - */ - intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, NULL); + lockmode = into->replace ? AccessExclusiveLock : NoLock; + (void) RangeVarGetAndCheckCreationNamespace(into->rel, lockmode, + &matviewOid); + replace = OidIsValid(matviewOid) && into->replace; + } - /* - * If necessary, create a TOAST table for the target table. Note that - * NewRelationCreateToastTable ends with CommandCounterIncrement(), so - * that the TOAST table will be visible for insertion. - */ - CommandCounterIncrement(); + if (is_matview && replace) + { + Relation rel; + List *atcmds = NIL; + AlterTableCmd *atcmd; + TupleDesc descriptor; + + rel = relation_open(matviewOid, NoLock); + + if (rel->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(rel))); + + CheckTableNotInUse(rel, "CREATE OR REPLACE MATERIALIZED VIEW"); + + descriptor = BuildDescForRelation(attrList); + checkViewColumns(descriptor, rel->rd_att, true); + + /* Add new attributes via ALTER TABLE. */ + if (list_length(attrList) > rel->rd_att->natts) + { + ListCell *c; + int skip = rel->rd_att->natts; + + foreach(c, attrList) + { + if (skip > 0) + { + skip--; + continue; + } + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_AddColumnToView; + atcmd->def = (Node *) lfirst(c); + atcmds = lappend(atcmds, atcmd); + } + } + + /* Set access method via ALTER TABLE. */ + if (into->accessMethod != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetAccessMethod; + atcmd->name = into->accessMethod; + atcmds = lappend(atcmds, atcmd); + } + + /* Set tablespace via ALTER TABLE. */ + if (into->tableSpaceName != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetTableSpace; + atcmd->name = into->tableSpaceName; + atcmds = lappend(atcmds, atcmd); + } + + /* Set storage parameters via ALTER TABLE. */ + if (into->options != NIL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_ReplaceRelOptions; + atcmd->def = (Node *) into->options; + atcmds = lappend(atcmds, atcmd); + } + + if (atcmds != NIL) + { + AlterTableInternal(matviewOid, atcmds, true); + CommandCounterIncrement(); + } + + relation_close(rel, NoLock); + + ObjectAddressSet(intoRelationAddr, RelationRelationId, matviewOid); + } + else + { + CreateStmt *create = makeNode(CreateStmt); + Datum toast_options; + const static char *validnsps[] = HEAP_RELOPT_NAMESPACES; + + /* + * Create the target relation by faking up a CREATE TABLE parsetree + * and passing it to DefineRelation. + */ + create->relation = into->rel; + create->tableElts = attrList; + create->inhRelations = NIL; + create->ofTypename = NULL; + create->constraints = NIL; + create->options = into->options; + create->oncommit = into->onCommit; + create->tablespacename = into->tableSpaceName; + create->if_not_exists = false; + create->accessMethod = into->accessMethod; + + /* + * Create the relation. (This will error out if there's an existing + * view, so we don't need more code to complain if "replace" is + * false.) + */ + intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, + NULL); - /* parse and validate reloptions for the toast table */ - toast_options = transformRelOptions((Datum) 0, - create->options, - "toast", - validnsps, - true, false); + /* + * If necessary, create a TOAST table for the target table. Note that + * NewRelationCreateToastTable ends with CommandCounterIncrement(), so + * that the TOAST table will be visible for insertion. + */ + CommandCounterIncrement(); + + /* parse and validate reloptions for the toast table */ + toast_options = transformRelOptions((Datum) 0, + create->options, + "toast", + validnsps, + true, false); - (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); + (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); - NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + } /* Create the "view" part of a materialized view. */ if (is_matview) @@ -135,7 +231,7 @@ create_ctas_internal(List *attrList, IntoClause *into) /* StoreViewQuery scribbles on tree, so make a copy */ Query *query = copyObject(into->viewQuery); - StoreViewQuery(intoRelationAddr.objectId, query, false); + StoreViewQuery(intoRelationAddr.objectId, query, replace); CommandCounterIncrement(); } @@ -232,7 +328,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt, /* Check if the relation exists or not */ if (CreateTableAsRelExists(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); + + /* Refresh the materialized view with a fake statement. */ + refresh = makeNode(RefreshMatViewStmt); + refresh->relation = into->rel; + refresh->skipData = into->skipData; + refresh->concurrent = false; + + return ExecRefreshMatView(refresh, NULL, NULL); + } + return InvalidObjectAddress; + } /* * Create the tuple receiver object and insert info it will need @@ -400,14 +515,15 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) oldrelid = get_relname_relid(into->rel->relname, nspid); if (OidIsValid(oldrelid)) { - if (!ctas->if_not_exists) + if (!ctas->if_not_exists && !into->replace) ereport(ERROR, (errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", into->rel->relname))); /* - * The relation exists and IF NOT EXISTS has been specified. + * The relation exists and IF NOT EXISTS or OR REPLACE has been + * specified. * * If we are in an extension script, insist that the pre-existing * object be a member of the extension, to avoid security risks. @@ -415,11 +531,12 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) ObjectAddressSet(address, RelationRelationId, oldrelid); checkMembershipInCurrentExtension(&address); - /* OK to skip */ - ereport(NOTICE, - (errcode(ERRCODE_DUPLICATE_TABLE), - errmsg("relation \"%s\" already exists, skipping", - into->rel->relname))); + if (ctas->if_not_exists) + /* OK to skip */ + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("relation \"%s\" already exists, skipping", + into->rel->relname))); return true; } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 4345b96de5..436232bcf2 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -4516,7 +4516,7 @@ AlterTableGetLockLevel(List *cmds) * Subcommands that may be visible to concurrent SELECTs */ case AT_DropColumn: /* change visible to SELECT */ - case AT_AddColumnToView: /* CREATE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ case AT_DropOids: /* used to equiv to DropColumn */ case AT_EnableAlwaysRule: /* may change SELECT rules */ case AT_EnableReplicaRule: /* may change SELECT rules */ @@ -4820,8 +4820,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* Recursion occurs during execution phase */ pass = AT_PASS_ADD_COL; break; - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ - ATSimplePermissions(cmd->subtype, rel, ATT_VIEW); + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ + ATSimplePermissions(cmd->subtype, rel, ATT_VIEW | ATT_MATVIEW); ATPrepAddColumn(wqueue, rel, recurse, recursing, true, cmd, lockmode, context); /* Recursion occurs during execution phase */ @@ -5252,7 +5252,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, switch (cmd->subtype) { case AT_AddColumn: /* ADD COLUMN */ - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ address = ATExecAddColumn(wqueue, tab, rel, &cmd, cmd->recurse, false, lockmode, cur_pass, context); diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 2bd49eb55e..65b9b2e9bb 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -30,8 +30,6 @@ #include "utils/lsyscache.h" #include "utils/rel.h" -static void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc); - /*--------------------------------------------------------------------- * DefineVirtualRelation * @@ -129,7 +127,7 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * column list. */ descriptor = BuildDescForRelation(attrList); - checkViewColumns(descriptor, rel->rd_att); + checkViewColumns(descriptor, rel->rd_att, false); /* * If new attributes have been added, we must add pg_attribute entries @@ -263,15 +261,22 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * added to generate specific complaints. Also, we allow the new view to have * more columns than the old. */ -static void -checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) +void +checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, bool is_matview) { int i; if (newdesc->natts < olddesc->natts) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } for (i = 0; i < olddesc->natts; i++) { @@ -280,17 +285,34 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) /* XXX msg not right, but we don't support DROP COL on view anyway */ if (newattr->attisdropped != oldattr->attisdropped) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change name of view column \"%s\" to \"%s\"", - NameStr(oldattr->attname), - NameStr(newattr->attname)), - errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of materialized view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead.")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + } /* * We cannot allow type, typmod, or collation to change, since these @@ -299,26 +321,48 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) */ if (newattr->atttypid != oldattr->atttypid || newattr->atttypmod != oldattr->atttypmod) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change data type of view column \"%s\" from %s to %s", - NameStr(oldattr->attname), - format_type_with_typemod(oldattr->atttypid, - oldattr->atttypmod), - format_type_with_typemod(newattr->atttypid, - newattr->atttypmod)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of materialized view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod)))); + } /* * At this point, attcollations should be both valid or both invalid, * so applying get_collation_name unconditionally should be fine. */ if (newattr->attcollation != oldattr->attcollation) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", - NameStr(oldattr->attname), - get_collation_name(oldattr->attcollation), - get_collation_name(newattr->attcollation)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of materialized view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation)))); + } } /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index dd458182f0..6b32233127 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4809,6 +4809,21 @@ CreateMatViewStmt: $8->skipData = !($11); $$ = (Node *) ctas; } + | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data + { + 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 = !($10); + $7->replace = true; + $$ = (Node *) ctas; + } ; create_mv_target: diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 1be0056af7..122e44267e 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2144,7 +2144,7 @@ match_previous_words(int pattern_id, /* complete with something you can create or replace */ else if (TailMatches("CREATE", "OR", "REPLACE")) COMPLETE_WITH("FUNCTION", "PROCEDURE", "LANGUAGE", "RULE", "VIEW", - "AGGREGATE", "TRANSFORM", "TRIGGER"); + "AGGREGATE", "TRANSFORM", "TRIGGER", "MATERIALIZED VIEW"); /* DROP, but not DROP embedded in other commands */ /* complete with something you can drop */ @@ -3964,13 +3964,16 @@ match_previous_words(int pattern_id, COMPLETE_WITH("SELECT"); /* CREATE MATERIALIZED VIEW */ - else if (Matches("CREATE", "MATERIALIZED")) + else if (Matches("CREATE", "MATERIALIZED") || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED")) COMPLETE_WITH("VIEW"); - /* Complete CREATE MATERIALIZED VIEW <name> with AS */ - else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny)) + /* Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> with AS */ + else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny)) COMPLETE_WITH("AS"); - /* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */ - else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS")) + /* Complete "CREATE [ OR REPLACE ] MATERIALIZED VIEW <sth> AS with "SELECT" */ + else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny, "AS")) COMPLETE_WITH("SELECT"); /* CREATE EVENT TRIGGER */ diff --git a/src/include/commands/view.h b/src/include/commands/view.h index d2d8588989..7eacdaaceb 100644 --- a/src/include/commands/view.h +++ b/src/include/commands/view.h @@ -22,4 +22,7 @@ extern ObjectAddress DefineView(ViewStmt *stmt, const char *queryString, extern void StoreViewQuery(Oid viewOid, Query *viewParse, bool replace); +extern void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, + bool is_matview); + #endif /* VIEW_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b40b661ec8..b71db02c24 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2358,7 +2358,7 @@ typedef struct AlterTableStmt typedef enum AlterTableType { AT_AddColumn, /* add column */ - AT_AddColumnToView, /* implicitly via CREATE OR REPLACE VIEW */ + AT_AddColumnToView, /* implicitly via CREATE OR REPLACE [MATERIALIZED] VIEW */ AT_ColumnDefault, /* alter column default */ AT_CookedColumnDefault, /* add a pre-cooked column default */ AT_DropNotNull, /* alter column drop not null */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index b0ef1952e8..533fa847ab 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 replace; /* replace existing matview? */ } IntoClause; diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 038ab73517..e2e2a13396 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -694,3 +694,194 @@ NOTICE: relation "matview_ine_tab" already exists, skipping (0 rows) DROP MATERIALIZED VIEW matview_ine_tab; +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +NOTICE: materialized view "mvtest_replace" does not exist, skipping +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + a +--- + 1 +(1 row) + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + a +--- + 2 +(1 row) + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +ERROR: materialized view "mvtest_replace" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW mvtest_replace; +SELECT * FROM mvtest_replace; + a +--- + 3 +(1 row) + +-- add column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 4 AS a, 1 b; +SELECT * FROM mvtest_replace; + a | b +---+--- + 4 | 1 +(1 row) + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+------------+---------+-------- + 4 | 1 | mvtest_replace | | | heap +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+-----------------+------------------+-------- + 5 | 1 | mvtest_replace | {fillfactor=50} | regress_tblspace | heap2 +(1 row) + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; + a | b | a | b +---+---+---+--- + 6 | 1 | 6 | 1 +(1 row) + +DROP VIEW mvtest_replace_v; +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; + a | b +---+--- + 7 | 1 +(1 row) + +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 7 | 1 +(1 row) + +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 8 | 1 +(1 row) + +RESET enable_seqscan; +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +ERROR: cannot change data type of materialized view column "b" from integer to text +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +ERROR: cannot change name of materialized view column "b" to "b2" +HINT: Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead. +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +ERROR: cannot change collation of materialized view column "c" from "C" to "POSIX" +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +ERROR: cannot drop columns from materialized view +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +ERROR: "mvtest_not_mv" is not a materialized view +DROP VIEW mvtest_not_mv; +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; +ERROR: syntax error at or near "NOT" +LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep... + ^ +DROP MATERIALIZED VIEW mvtest_replace; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index b74ee305e0..c12f0243c9 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -314,3 +314,111 @@ 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 DROP MATERIALIZED VIEW matview_ine_tab; + +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- + +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +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; +SELECT * FROM mvtest_replace; + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; +DROP VIEW mvtest_replace_v; + +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; + +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +SELECT * FROM mvtest_replace; + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +SELECT * FROM mvtest_replace; + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +SELECT * FROM mvtest_replace; + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +SELECT * FROM mvtest_replace; + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +DROP VIEW mvtest_not_mv; + +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; + +DROP MATERIALIZED VIEW mvtest_replace; -- 2.47.0 --hht4bymbvbva5al5 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v1 1/2] Add CREATE OR REPLACE MATERIALIZED VIEW @ 2024-05-21 16:35 Erik Wienhold <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Erik Wienhold @ 2024-05-21 16:35 UTC (permalink / raw) --- .../sgml/ref/create_materialized_view.sgml | 15 +- src/backend/commands/createas.c | 207 ++++++++++++++---- src/backend/commands/tablecmds.c | 8 +- src/backend/commands/view.c | 106 ++++++--- src/backend/parser/gram.y | 15 ++ src/bin/psql/tab-complete.c | 2 +- src/include/commands/view.h | 3 + src/include/nodes/parsenodes.h | 2 +- src/include/nodes/primnodes.h | 1 + src/test/regress/expected/matview.out | 191 ++++++++++++++++ src/test/regress/sql/matview.sql | 108 +++++++++ 11 files changed, 574 insertions(+), 84 deletions(-) diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml index 0d2fea2b97..b5a8e3441a 100644 --- a/doc/src/sgml/ref/create_materialized_view.sgml +++ b/doc/src/sgml/ref/create_materialized_view.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> +CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ] [ USING <replaceable class="parameter">method</replaceable> ] [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] @@ -60,6 +60,17 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> <title>Parameters</title> <variablelist> + <varlistentry> + <term><literal>OR REPLACE</literal></term> + <listitem> + <para> + Replaces a materialized view if it already exists. + Specifying <literal>OR REPLACE</literal> together with + <literal>IF NOT EXISTS</literal> is an error. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>IF NOT EXISTS</literal></term> <listitem> @@ -67,7 +78,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> Do not throw an error if a materialized view with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing materialized view is anything like the one that would - have been created. + have been created, unless you use <literal>OR REPLACE</literal> instead. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 62050f4dc5..1f34665521 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -79,55 +79,151 @@ static void intorel_destroy(DestReceiver *self); static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into) { - CreateStmt *create = makeNode(CreateStmt); - bool is_matview; + bool is_matview, + replace = false; char relkind; - Datum toast_options; - static char *validnsps[] = HEAP_RELOPT_NAMESPACES; + Oid matviewOid = InvalidOid; ObjectAddress intoRelationAddr; /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */ is_matview = (into->viewQuery != NULL); relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION; - /* - * Create the target relation by faking up a CREATE TABLE parsetree and - * passing it to DefineRelation. - */ - create->relation = into->rel; - create->tableElts = attrList; - create->inhRelations = NIL; - create->ofTypename = NULL; - create->constraints = NIL; - create->options = into->options; - create->oncommit = into->onCommit; - create->tablespacename = into->tableSpaceName; - create->if_not_exists = false; - create->accessMethod = into->accessMethod; + /* Check if an existing materialized view needs to be replaced. */ + if (is_matview) + { + LOCKMODE lockmode; - /* - * Create the relation. (This will error out if there's an existing view, - * so we don't need more code to complain if "replace" is false.) - */ - intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, NULL); + lockmode = into->replace ? AccessExclusiveLock : NoLock; + (void) RangeVarGetAndCheckCreationNamespace(into->rel, lockmode, + &matviewOid); + replace = OidIsValid(matviewOid) && into->replace; + } - /* - * If necessary, create a TOAST table for the target table. Note that - * NewRelationCreateToastTable ends with CommandCounterIncrement(), so - * that the TOAST table will be visible for insertion. - */ - CommandCounterIncrement(); + if (is_matview && replace) + { + Relation rel; + List *atcmds = NIL; + AlterTableCmd *atcmd; + TupleDesc descriptor; + + rel = relation_open(matviewOid, NoLock); + + if (rel->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(rel))); + + CheckTableNotInUse(rel, "CREATE OR REPLACE MATERIALIZED VIEW"); + + descriptor = BuildDescForRelation(attrList); + checkViewColumns(descriptor, rel->rd_att, true); + + /* Add new attributes via ALTER TABLE. */ + if (list_length(attrList) > rel->rd_att->natts) + { + ListCell *c; + int skip = rel->rd_att->natts; + + foreach(c, attrList) + { + if (skip > 0) + { + skip--; + continue; + } + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_AddColumnToView; + atcmd->def = (Node *) lfirst(c); + atcmds = lappend(atcmds, atcmd); + } + } + + /* Set access method via ALTER TABLE. */ + if (into->accessMethod != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetAccessMethod; + atcmd->name = into->accessMethod; + atcmds = lappend(atcmds, atcmd); + } + + /* Set tablespace via ALTER TABLE. */ + if (into->tableSpaceName != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetTableSpace; + atcmd->name = into->tableSpaceName; + atcmds = lappend(atcmds, atcmd); + } + + /* Set storage parameters via ALTER TABLE. */ + if (into->options != NIL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_ReplaceRelOptions; + atcmd->def = (Node *) into->options; + atcmds = lappend(atcmds, atcmd); + } + + if (atcmds != NIL) + { + AlterTableInternal(matviewOid, atcmds, true); + CommandCounterIncrement(); + } + + relation_close(rel, NoLock); + + ObjectAddressSet(intoRelationAddr, RelationRelationId, matviewOid); + } + else + { + CreateStmt *create = makeNode(CreateStmt); + Datum toast_options; + static char *validnsps[] = HEAP_RELOPT_NAMESPACES; + + /* + * Create the target relation by faking up a CREATE TABLE parsetree + * and passing it to DefineRelation. + */ + create->relation = into->rel; + create->tableElts = attrList; + create->inhRelations = NIL; + create->ofTypename = NULL; + create->constraints = NIL; + create->options = into->options; + create->oncommit = into->onCommit; + create->tablespacename = into->tableSpaceName; + create->if_not_exists = false; + create->accessMethod = into->accessMethod; + + /* + * Create the relation. (This will error out if there's an existing + * view, so we don't need more code to complain if "replace" is + * false.) + */ + intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, + NULL); - /* parse and validate reloptions for the toast table */ - toast_options = transformRelOptions((Datum) 0, - create->options, - "toast", - validnsps, - true, false); + /* + * If necessary, create a TOAST table for the target table. Note that + * NewRelationCreateToastTable ends with CommandCounterIncrement(), so + * that the TOAST table will be visible for insertion. + */ + CommandCounterIncrement(); + + /* parse and validate reloptions for the toast table */ + toast_options = transformRelOptions((Datum) 0, + create->options, + "toast", + validnsps, + true, false); - (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); + (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); - NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + } /* Create the "view" part of a materialized view. */ if (is_matview) @@ -135,7 +231,7 @@ create_ctas_internal(List *attrList, IntoClause *into) /* StoreViewQuery scribbles on tree, so make a copy */ Query *query = (Query *) copyObject(into->viewQuery); - StoreViewQuery(intoRelationAddr.objectId, query, false); + StoreViewQuery(intoRelationAddr.objectId, query, replace); CommandCounterIncrement(); } @@ -236,7 +332,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt, /* Check if the relation exists or not */ if (CreateTableAsRelExists(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); + + /* Refresh the materialized view with a fake statement. */ + refresh = makeNode(RefreshMatViewStmt); + refresh->relation = into->rel; + refresh->skipData = into->skipData; + refresh->concurrent = false; + + return ExecRefreshMatView(refresh, NULL, NULL, NULL); + } + return InvalidObjectAddress; + } /* * Create the tuple receiver object and insert info it will need @@ -397,14 +512,15 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) oldrelid = get_relname_relid(into->rel->relname, nspid); if (OidIsValid(oldrelid)) { - if (!ctas->if_not_exists) + if (!ctas->if_not_exists && !into->replace) ereport(ERROR, (errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", into->rel->relname))); /* - * The relation exists and IF NOT EXISTS has been specified. + * The relation exists and IF NOT EXISTS or OR REPLACE has been + * specified. * * If we are in an extension script, insist that the pre-existing * object be a member of the extension, to avoid security risks. @@ -412,11 +528,12 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) ObjectAddressSet(address, RelationRelationId, oldrelid); checkMembershipInCurrentExtension(&address); - /* OK to skip */ - ereport(NOTICE, - (errcode(ERRCODE_DUPLICATE_TABLE), - errmsg("relation \"%s\" already exists, skipping", - into->rel->relname))); + if (ctas->if_not_exists) + /* OK to skip */ + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("relation \"%s\" already exists, skipping", + into->rel->relname))); return true; } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 9e9dc5c2c1..4660aa1230 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -4484,7 +4484,7 @@ AlterTableGetLockLevel(List *cmds) * Subcommands that may be visible to concurrent SELECTs */ case AT_DropColumn: /* change visible to SELECT */ - case AT_AddColumnToView: /* CREATE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ case AT_DropOids: /* used to equiv to DropColumn */ case AT_EnableAlwaysRule: /* may change SELECT rules */ case AT_EnableReplicaRule: /* may change SELECT rules */ @@ -4795,8 +4795,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* Recursion occurs during execution phase */ pass = AT_PASS_ADD_COL; break; - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ - ATSimplePermissions(cmd->subtype, rel, ATT_VIEW); + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ + ATSimplePermissions(cmd->subtype, rel, ATT_VIEW | ATT_MATVIEW); ATPrepAddColumn(wqueue, rel, recurse, recursing, true, cmd, lockmode, context); /* Recursion occurs during execution phase */ @@ -5220,7 +5220,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, switch (cmd->subtype) { case AT_AddColumn: /* ADD COLUMN */ - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ address = ATExecAddColumn(wqueue, tab, rel, &cmd, cmd->recurse, false, lockmode, cur_pass, context); diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index fdad833832..76532aa35d 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -30,8 +30,6 @@ #include "utils/lsyscache.h" #include "utils/rel.h" -static void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc); - /*--------------------------------------------------------------------- * DefineVirtualRelation * @@ -130,7 +128,7 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * column list. */ descriptor = BuildDescForRelation(attrList); - checkViewColumns(descriptor, rel->rd_att); + checkViewColumns(descriptor, rel->rd_att, false); /* * If new attributes have been added, we must add pg_attribute entries @@ -263,15 +261,22 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * added to generate specific complaints. Also, we allow the new view to have * more columns than the old. */ -static void -checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) +void +checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, bool is_matview) { int i; if (newdesc->natts < olddesc->natts) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } for (i = 0; i < olddesc->natts; i++) { @@ -280,17 +285,34 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) /* XXX msg not right, but we don't support DROP COL on view anyway */ if (newattr->attisdropped != oldattr->attisdropped) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change name of view column \"%s\" to \"%s\"", - NameStr(oldattr->attname), - NameStr(newattr->attname)), - errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of materialized view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead.")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + } /* * We cannot allow type, typmod, or collation to change, since these @@ -299,26 +321,48 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) */ if (newattr->atttypid != oldattr->atttypid || newattr->atttypmod != oldattr->atttypmod) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change data type of view column \"%s\" from %s to %s", - NameStr(oldattr->attname), - format_type_with_typemod(oldattr->atttypid, - oldattr->atttypmod), - format_type_with_typemod(newattr->atttypid, - newattr->atttypmod)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of materialized view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod)))); + } /* * At this point, attcollations should be both valid or both invalid, * so applying get_collation_name unconditionally should be fine. */ if (newattr->attcollation != oldattr->attcollation) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", - NameStr(oldattr->attname), - get_collation_name(oldattr->attcollation), - get_collation_name(newattr->attcollation)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of materialized view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation)))); + } } /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a043fd4c66..ed477806d1 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4831,6 +4831,21 @@ CreateMatViewStmt: $8->skipData = !($11); $$ = (Node *) ctas; } + | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data + { + 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 = !($10); + $7->replace = true; + $$ = (Node *) ctas; + } ; create_mv_target: diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index d453e224d9..f4d7f99bc5 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1811,7 +1811,7 @@ psql_completion(const char *text, int start, int end) /* complete with something you can create or replace */ else if (TailMatches("CREATE", "OR", "REPLACE")) COMPLETE_WITH("FUNCTION", "PROCEDURE", "LANGUAGE", "RULE", "VIEW", - "AGGREGATE", "TRANSFORM", "TRIGGER"); + "AGGREGATE", "TRANSFORM", "TRIGGER", "MATERIALIZED VIEW"); /* DROP, but not DROP embedded in other commands */ /* complete with something you can drop */ diff --git a/src/include/commands/view.h b/src/include/commands/view.h index d2d8588989..7eacdaaceb 100644 --- a/src/include/commands/view.h +++ b/src/include/commands/view.h @@ -22,4 +22,7 @@ extern ObjectAddress DefineView(ViewStmt *stmt, const char *queryString, extern void StoreViewQuery(Oid viewOid, Query *viewParse, bool replace); +extern void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, + bool is_matview); + #endif /* VIEW_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 85a62b538e..cbc4b608e3 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2351,7 +2351,7 @@ typedef struct AlterTableStmt typedef enum AlterTableType { AT_AddColumn, /* add column */ - AT_AddColumnToView, /* implicitly via CREATE OR REPLACE VIEW */ + AT_AddColumnToView, /* implicitly via CREATE OR REPLACE [MATERIALIZED] VIEW */ AT_ColumnDefault, /* alter column default */ AT_CookedColumnDefault, /* add a pre-cooked column default */ AT_DropNotNull, /* alter column drop not null */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index ea47652adb..4b0ee5d10d 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 replace; /* replace existing matview? */ } IntoClause; diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 038ab73517..e2e2a13396 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -694,3 +694,194 @@ NOTICE: relation "matview_ine_tab" already exists, skipping (0 rows) DROP MATERIALIZED VIEW matview_ine_tab; +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +NOTICE: materialized view "mvtest_replace" does not exist, skipping +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + a +--- + 1 +(1 row) + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + a +--- + 2 +(1 row) + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +ERROR: materialized view "mvtest_replace" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW mvtest_replace; +SELECT * FROM mvtest_replace; + a +--- + 3 +(1 row) + +-- add column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 4 AS a, 1 b; +SELECT * FROM mvtest_replace; + a | b +---+--- + 4 | 1 +(1 row) + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+------------+---------+-------- + 4 | 1 | mvtest_replace | | | heap +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+-----------------+------------------+-------- + 5 | 1 | mvtest_replace | {fillfactor=50} | regress_tblspace | heap2 +(1 row) + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; + a | b | a | b +---+---+---+--- + 6 | 1 | 6 | 1 +(1 row) + +DROP VIEW mvtest_replace_v; +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; + a | b +---+--- + 7 | 1 +(1 row) + +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 7 | 1 +(1 row) + +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 8 | 1 +(1 row) + +RESET enable_seqscan; +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +ERROR: cannot change data type of materialized view column "b" from integer to text +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +ERROR: cannot change name of materialized view column "b" to "b2" +HINT: Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead. +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +ERROR: cannot change collation of materialized view column "c" from "C" to "POSIX" +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +ERROR: cannot drop columns from materialized view +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +ERROR: "mvtest_not_mv" is not a materialized view +DROP VIEW mvtest_not_mv; +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; +ERROR: syntax error at or near "NOT" +LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep... + ^ +DROP MATERIALIZED VIEW mvtest_replace; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index b74ee305e0..c12f0243c9 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -314,3 +314,111 @@ 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 DROP MATERIALIZED VIEW matview_ine_tab; + +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- + +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +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; +SELECT * FROM mvtest_replace; + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; +DROP VIEW mvtest_replace_v; + +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; + +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +SELECT * FROM mvtest_replace; + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +SELECT * FROM mvtest_replace; + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +SELECT * FROM mvtest_replace; + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +SELECT * FROM mvtest_replace; + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +DROP VIEW mvtest_not_mv; + +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; + +DROP MATERIALIZED VIEW mvtest_replace; -- 2.45.2 --kl5hblbqqgigaot5 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v1-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v2 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW @ 2024-05-21 16:35 Erik Wienhold <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Erik Wienhold @ 2024-05-21 16:35 UTC (permalink / raw) --- .../sgml/ref/create_materialized_view.sgml | 15 +- src/backend/commands/createas.c | 207 ++++++++++++++---- src/backend/commands/tablecmds.c | 8 +- src/backend/commands/view.c | 106 ++++++--- src/backend/parser/gram.y | 15 ++ src/bin/psql/tab-complete.c | 15 +- src/include/commands/view.h | 3 + src/include/nodes/parsenodes.h | 2 +- src/include/nodes/primnodes.h | 1 + src/test/regress/expected/matview.out | 191 ++++++++++++++++ src/test/regress/sql/matview.sql | 108 +++++++++ 11 files changed, 582 insertions(+), 89 deletions(-) diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml index 0d2fea2b97..b5a8e3441a 100644 --- a/doc/src/sgml/ref/create_materialized_view.sgml +++ b/doc/src/sgml/ref/create_materialized_view.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> +CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ] [ USING <replaceable class="parameter">method</replaceable> ] [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] @@ -60,6 +60,17 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> <title>Parameters</title> <variablelist> + <varlistentry> + <term><literal>OR REPLACE</literal></term> + <listitem> + <para> + Replaces a materialized view if it already exists. + Specifying <literal>OR REPLACE</literal> together with + <literal>IF NOT EXISTS</literal> is an error. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>IF NOT EXISTS</literal></term> <listitem> @@ -67,7 +78,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> Do not throw an error if a materialized view with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing materialized view is anything like the one that would - have been created. + have been created, unless you use <literal>OR REPLACE</literal> instead. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 2c8a93b6e5..c5d78252a1 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -79,55 +79,151 @@ static void intorel_destroy(DestReceiver *self); static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into) { - CreateStmt *create = makeNode(CreateStmt); - bool is_matview; + bool is_matview, + replace = false; char relkind; - Datum toast_options; - static char *validnsps[] = HEAP_RELOPT_NAMESPACES; + Oid matviewOid = InvalidOid; ObjectAddress intoRelationAddr; /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */ is_matview = (into->viewQuery != NULL); relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION; - /* - * Create the target relation by faking up a CREATE TABLE parsetree and - * passing it to DefineRelation. - */ - create->relation = into->rel; - create->tableElts = attrList; - create->inhRelations = NIL; - create->ofTypename = NULL; - create->constraints = NIL; - create->options = into->options; - create->oncommit = into->onCommit; - create->tablespacename = into->tableSpaceName; - create->if_not_exists = false; - create->accessMethod = into->accessMethod; + /* Check if an existing materialized view needs to be replaced. */ + if (is_matview) + { + LOCKMODE lockmode; - /* - * Create the relation. (This will error out if there's an existing view, - * so we don't need more code to complain if "replace" is false.) - */ - intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, NULL); + lockmode = into->replace ? AccessExclusiveLock : NoLock; + (void) RangeVarGetAndCheckCreationNamespace(into->rel, lockmode, + &matviewOid); + replace = OidIsValid(matviewOid) && into->replace; + } - /* - * If necessary, create a TOAST table for the target table. Note that - * NewRelationCreateToastTable ends with CommandCounterIncrement(), so - * that the TOAST table will be visible for insertion. - */ - CommandCounterIncrement(); + if (is_matview && replace) + { + Relation rel; + List *atcmds = NIL; + AlterTableCmd *atcmd; + TupleDesc descriptor; + + rel = relation_open(matviewOid, NoLock); + + if (rel->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(rel))); + + CheckTableNotInUse(rel, "CREATE OR REPLACE MATERIALIZED VIEW"); + + descriptor = BuildDescForRelation(attrList); + checkViewColumns(descriptor, rel->rd_att, true); + + /* Add new attributes via ALTER TABLE. */ + if (list_length(attrList) > rel->rd_att->natts) + { + ListCell *c; + int skip = rel->rd_att->natts; + + foreach(c, attrList) + { + if (skip > 0) + { + skip--; + continue; + } + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_AddColumnToView; + atcmd->def = (Node *) lfirst(c); + atcmds = lappend(atcmds, atcmd); + } + } + + /* Set access method via ALTER TABLE. */ + if (into->accessMethod != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetAccessMethod; + atcmd->name = into->accessMethod; + atcmds = lappend(atcmds, atcmd); + } + + /* Set tablespace via ALTER TABLE. */ + if (into->tableSpaceName != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetTableSpace; + atcmd->name = into->tableSpaceName; + atcmds = lappend(atcmds, atcmd); + } + + /* Set storage parameters via ALTER TABLE. */ + if (into->options != NIL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_ReplaceRelOptions; + atcmd->def = (Node *) into->options; + atcmds = lappend(atcmds, atcmd); + } + + if (atcmds != NIL) + { + AlterTableInternal(matviewOid, atcmds, true); + CommandCounterIncrement(); + } + + relation_close(rel, NoLock); + + ObjectAddressSet(intoRelationAddr, RelationRelationId, matviewOid); + } + else + { + CreateStmt *create = makeNode(CreateStmt); + Datum toast_options; + static char *validnsps[] = HEAP_RELOPT_NAMESPACES; + + /* + * Create the target relation by faking up a CREATE TABLE parsetree + * and passing it to DefineRelation. + */ + create->relation = into->rel; + create->tableElts = attrList; + create->inhRelations = NIL; + create->ofTypename = NULL; + create->constraints = NIL; + create->options = into->options; + create->oncommit = into->onCommit; + create->tablespacename = into->tableSpaceName; + create->if_not_exists = false; + create->accessMethod = into->accessMethod; + + /* + * Create the relation. (This will error out if there's an existing + * view, so we don't need more code to complain if "replace" is + * false.) + */ + intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, + NULL); - /* parse and validate reloptions for the toast table */ - toast_options = transformRelOptions((Datum) 0, - create->options, - "toast", - validnsps, - true, false); + /* + * If necessary, create a TOAST table for the target table. Note that + * NewRelationCreateToastTable ends with CommandCounterIncrement(), so + * that the TOAST table will be visible for insertion. + */ + CommandCounterIncrement(); + + /* parse and validate reloptions for the toast table */ + toast_options = transformRelOptions((Datum) 0, + create->options, + "toast", + validnsps, + true, false); - (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); + (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); - NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + } /* Create the "view" part of a materialized view. */ if (is_matview) @@ -135,7 +231,7 @@ create_ctas_internal(List *attrList, IntoClause *into) /* StoreViewQuery scribbles on tree, so make a copy */ Query *query = (Query *) copyObject(into->viewQuery); - StoreViewQuery(intoRelationAddr.objectId, query, false); + StoreViewQuery(intoRelationAddr.objectId, query, replace); CommandCounterIncrement(); } @@ -234,7 +330,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt, /* Check if the relation exists or not */ if (CreateTableAsRelExists(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); + + /* Refresh the materialized view with a fake statement. */ + refresh = makeNode(RefreshMatViewStmt); + refresh->relation = into->rel; + refresh->skipData = into->skipData; + refresh->concurrent = false; + + return ExecRefreshMatView(refresh, NULL, NULL, NULL); + } + return InvalidObjectAddress; + } /* * Create the tuple receiver object and insert info it will need @@ -395,14 +510,15 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) oldrelid = get_relname_relid(into->rel->relname, nspid); if (OidIsValid(oldrelid)) { - if (!ctas->if_not_exists) + if (!ctas->if_not_exists && !into->replace) ereport(ERROR, (errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", into->rel->relname))); /* - * The relation exists and IF NOT EXISTS has been specified. + * The relation exists and IF NOT EXISTS or OR REPLACE has been + * specified. * * If we are in an extension script, insist that the pre-existing * object be a member of the extension, to avoid security risks. @@ -410,11 +526,12 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) ObjectAddressSet(address, RelationRelationId, oldrelid); checkMembershipInCurrentExtension(&address); - /* OK to skip */ - ereport(NOTICE, - (errcode(ERRCODE_DUPLICATE_TABLE), - errmsg("relation \"%s\" already exists, skipping", - into->rel->relname))); + if (ctas->if_not_exists) + /* OK to skip */ + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("relation \"%s\" already exists, skipping", + into->rel->relname))); return true; } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 0b2a52463f..5274daaf3a 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -4484,7 +4484,7 @@ AlterTableGetLockLevel(List *cmds) * Subcommands that may be visible to concurrent SELECTs */ case AT_DropColumn: /* change visible to SELECT */ - case AT_AddColumnToView: /* CREATE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ case AT_DropOids: /* used to equiv to DropColumn */ case AT_EnableAlwaysRule: /* may change SELECT rules */ case AT_EnableReplicaRule: /* may change SELECT rules */ @@ -4795,8 +4795,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* Recursion occurs during execution phase */ pass = AT_PASS_ADD_COL; break; - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ - ATSimplePermissions(cmd->subtype, rel, ATT_VIEW); + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ + ATSimplePermissions(cmd->subtype, rel, ATT_VIEW | ATT_MATVIEW); ATPrepAddColumn(wqueue, rel, recurse, recursing, true, cmd, lockmode, context); /* Recursion occurs during execution phase */ @@ -5220,7 +5220,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, switch (cmd->subtype) { case AT_AddColumn: /* ADD COLUMN */ - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ address = ATExecAddColumn(wqueue, tab, rel, &cmd, cmd->recurse, false, lockmode, cur_pass, context); diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index fdad833832..76532aa35d 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -30,8 +30,6 @@ #include "utils/lsyscache.h" #include "utils/rel.h" -static void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc); - /*--------------------------------------------------------------------- * DefineVirtualRelation * @@ -130,7 +128,7 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * column list. */ descriptor = BuildDescForRelation(attrList); - checkViewColumns(descriptor, rel->rd_att); + checkViewColumns(descriptor, rel->rd_att, false); /* * If new attributes have been added, we must add pg_attribute entries @@ -263,15 +261,22 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * added to generate specific complaints. Also, we allow the new view to have * more columns than the old. */ -static void -checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) +void +checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, bool is_matview) { int i; if (newdesc->natts < olddesc->natts) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } for (i = 0; i < olddesc->natts; i++) { @@ -280,17 +285,34 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) /* XXX msg not right, but we don't support DROP COL on view anyway */ if (newattr->attisdropped != oldattr->attisdropped) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change name of view column \"%s\" to \"%s\"", - NameStr(oldattr->attname), - NameStr(newattr->attname)), - errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of materialized view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead.")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + } /* * We cannot allow type, typmod, or collation to change, since these @@ -299,26 +321,48 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) */ if (newattr->atttypid != oldattr->atttypid || newattr->atttypmod != oldattr->atttypmod) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change data type of view column \"%s\" from %s to %s", - NameStr(oldattr->attname), - format_type_with_typemod(oldattr->atttypid, - oldattr->atttypmod), - format_type_with_typemod(newattr->atttypid, - newattr->atttypmod)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of materialized view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod)))); + } /* * At this point, attcollations should be both valid or both invalid, * so applying get_collation_name unconditionally should be fine. */ if (newattr->attcollation != oldattr->attcollation) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", - NameStr(oldattr->attname), - get_collation_name(oldattr->attcollation), - get_collation_name(newattr->attcollation)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of materialized view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation)))); + } } /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a043fd4c66..ed477806d1 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4831,6 +4831,21 @@ CreateMatViewStmt: $8->skipData = !($11); $$ = (Node *) ctas; } + | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data + { + 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 = !($10); + $7->replace = true; + $$ = (Node *) ctas; + } ; create_mv_target: diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 891face1b6..f3291e79d8 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1811,7 +1811,7 @@ psql_completion(const char *text, int start, int end) /* complete with something you can create or replace */ else if (TailMatches("CREATE", "OR", "REPLACE")) COMPLETE_WITH("FUNCTION", "PROCEDURE", "LANGUAGE", "RULE", "VIEW", - "AGGREGATE", "TRANSFORM", "TRIGGER"); + "AGGREGATE", "TRANSFORM", "TRIGGER", "MATERIALIZED VIEW"); /* DROP, but not DROP embedded in other commands */ /* complete with something you can drop */ @@ -3619,13 +3619,16 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH("SELECT"); /* CREATE MATERIALIZED VIEW */ - else if (Matches("CREATE", "MATERIALIZED")) + else if (Matches("CREATE", "MATERIALIZED") || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED")) COMPLETE_WITH("VIEW"); - /* Complete CREATE MATERIALIZED VIEW <name> with AS */ - else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny)) + /* Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> with AS */ + else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny)) COMPLETE_WITH("AS"); - /* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */ - else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS")) + /* Complete "CREATE [ OR REPLACE ] MATERIALIZED VIEW <sth> AS with "SELECT" */ + else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny, "AS")) COMPLETE_WITH("SELECT"); /* CREATE EVENT TRIGGER */ diff --git a/src/include/commands/view.h b/src/include/commands/view.h index d2d8588989..7eacdaaceb 100644 --- a/src/include/commands/view.h +++ b/src/include/commands/view.h @@ -22,4 +22,7 @@ extern ObjectAddress DefineView(ViewStmt *stmt, const char *queryString, extern void StoreViewQuery(Oid viewOid, Query *viewParse, bool replace); +extern void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, + bool is_matview); + #endif /* VIEW_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 85a62b538e..cbc4b608e3 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2351,7 +2351,7 @@ typedef struct AlterTableStmt typedef enum AlterTableType { AT_AddColumn, /* add column */ - AT_AddColumnToView, /* implicitly via CREATE OR REPLACE VIEW */ + AT_AddColumnToView, /* implicitly via CREATE OR REPLACE [MATERIALIZED] VIEW */ AT_ColumnDefault, /* alter column default */ AT_CookedColumnDefault, /* add a pre-cooked column default */ AT_DropNotNull, /* alter column drop not null */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index ea47652adb..4b0ee5d10d 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 replace; /* replace existing matview? */ } IntoClause; diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 038ab73517..e2e2a13396 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -694,3 +694,194 @@ NOTICE: relation "matview_ine_tab" already exists, skipping (0 rows) DROP MATERIALIZED VIEW matview_ine_tab; +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +NOTICE: materialized view "mvtest_replace" does not exist, skipping +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + a +--- + 1 +(1 row) + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + a +--- + 2 +(1 row) + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +ERROR: materialized view "mvtest_replace" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW mvtest_replace; +SELECT * FROM mvtest_replace; + a +--- + 3 +(1 row) + +-- add column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 4 AS a, 1 b; +SELECT * FROM mvtest_replace; + a | b +---+--- + 4 | 1 +(1 row) + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+------------+---------+-------- + 4 | 1 | mvtest_replace | | | heap +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+-----------------+------------------+-------- + 5 | 1 | mvtest_replace | {fillfactor=50} | regress_tblspace | heap2 +(1 row) + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; + a | b | a | b +---+---+---+--- + 6 | 1 | 6 | 1 +(1 row) + +DROP VIEW mvtest_replace_v; +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; + a | b +---+--- + 7 | 1 +(1 row) + +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 7 | 1 +(1 row) + +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 8 | 1 +(1 row) + +RESET enable_seqscan; +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +ERROR: cannot change data type of materialized view column "b" from integer to text +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +ERROR: cannot change name of materialized view column "b" to "b2" +HINT: Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead. +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +ERROR: cannot change collation of materialized view column "c" from "C" to "POSIX" +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +ERROR: cannot drop columns from materialized view +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +ERROR: "mvtest_not_mv" is not a materialized view +DROP VIEW mvtest_not_mv; +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; +ERROR: syntax error at or near "NOT" +LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep... + ^ +DROP MATERIALIZED VIEW mvtest_replace; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index b74ee305e0..c12f0243c9 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -314,3 +314,111 @@ 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 DROP MATERIALIZED VIEW matview_ine_tab; + +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- + +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +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; +SELECT * FROM mvtest_replace; + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; +DROP VIEW mvtest_replace_v; + +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; + +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +SELECT * FROM mvtest_replace; + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +SELECT * FROM mvtest_replace; + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +SELECT * FROM mvtest_replace; + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +SELECT * FROM mvtest_replace; + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +DROP VIEW mvtest_not_mv; + +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; + +DROP MATERIALIZED VIEW mvtest_replace; -- 2.45.2 --nrp556lrhtfteavd Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v6 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW @ 2024-05-21 16:35 Erik Wienhold <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Erik Wienhold @ 2024-05-21 16:35 UTC (permalink / raw) --- .../sgml/ref/create_materialized_view.sgml | 15 +- src/backend/commands/createas.c | 207 ++++++++++++++---- src/backend/commands/tablecmds.c | 8 +- src/backend/commands/view.c | 106 ++++++--- src/backend/parser/gram.y | 15 ++ src/bin/psql/tab-complete.in.c | 26 ++- src/include/commands/view.h | 3 + src/include/nodes/parsenodes.h | 2 +- src/include/nodes/primnodes.h | 1 + src/test/regress/expected/matview.out | 191 ++++++++++++++++ src/test/regress/sql/matview.sql | 108 +++++++++ 11 files changed, 589 insertions(+), 93 deletions(-) diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml index 62d897931c3..5e03320eb73 100644 --- a/doc/src/sgml/ref/create_materialized_view.sgml +++ b/doc/src/sgml/ref/create_materialized_view.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> +CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ] [ USING <replaceable class="parameter">method</replaceable> ] [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] @@ -60,6 +60,17 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> <title>Parameters</title> <variablelist> + <varlistentry> + <term><literal>OR REPLACE</literal></term> + <listitem> + <para> + Replaces a materialized view if it already exists. + Specifying <literal>OR REPLACE</literal> together with + <literal>IF NOT EXISTS</literal> is an error. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>IF NOT EXISTS</literal></term> <listitem> @@ -67,7 +78,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> Do not throw an error if a materialized view with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing materialized view is anything like the one that would - have been created. + have been created, unless you use <literal>OR REPLACE</literal> instead. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 44b4665ccd3..9f37ed7f177 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -79,55 +79,151 @@ static void intorel_destroy(DestReceiver *self); static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into) { - CreateStmt *create = makeNode(CreateStmt); - bool is_matview; + bool is_matview, + replace = false; char relkind; - Datum toast_options; - const char *const validnsps[] = HEAP_RELOPT_NAMESPACES; + Oid matviewOid = InvalidOid; ObjectAddress intoRelationAddr; /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */ is_matview = (into->viewQuery != NULL); relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION; - /* - * Create the target relation by faking up a CREATE TABLE parsetree and - * passing it to DefineRelation. - */ - create->relation = into->rel; - create->tableElts = attrList; - create->inhRelations = NIL; - create->ofTypename = NULL; - create->constraints = NIL; - create->options = into->options; - create->oncommit = into->onCommit; - create->tablespacename = into->tableSpaceName; - create->if_not_exists = false; - create->accessMethod = into->accessMethod; + /* Check if an existing materialized view needs to be replaced. */ + if (is_matview) + { + LOCKMODE lockmode; - /* - * Create the relation. (This will error out if there's an existing view, - * so we don't need more code to complain if "replace" is false.) - */ - intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, NULL); + lockmode = into->replace ? AccessExclusiveLock : NoLock; + (void) RangeVarGetAndCheckCreationNamespace(into->rel, lockmode, + &matviewOid); + replace = OidIsValid(matviewOid) && into->replace; + } - /* - * If necessary, create a TOAST table for the target table. Note that - * NewRelationCreateToastTable ends with CommandCounterIncrement(), so - * that the TOAST table will be visible for insertion. - */ - CommandCounterIncrement(); + if (is_matview && replace) + { + Relation rel; + List *atcmds = NIL; + AlterTableCmd *atcmd; + TupleDesc descriptor; + + rel = relation_open(matviewOid, NoLock); + + if (rel->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(rel))); + + CheckTableNotInUse(rel, "CREATE OR REPLACE MATERIALIZED VIEW"); + + descriptor = BuildDescForRelation(attrList); + checkViewColumns(descriptor, rel->rd_att, true); + + /* Add new attributes via ALTER TABLE. */ + if (list_length(attrList) > rel->rd_att->natts) + { + ListCell *c; + int skip = rel->rd_att->natts; + + foreach(c, attrList) + { + if (skip > 0) + { + skip--; + continue; + } + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_AddColumnToView; + atcmd->def = (Node *) lfirst(c); + atcmds = lappend(atcmds, atcmd); + } + } + + /* Set access method via ALTER TABLE. */ + if (into->accessMethod != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetAccessMethod; + atcmd->name = into->accessMethod; + atcmds = lappend(atcmds, atcmd); + } + + /* Set tablespace via ALTER TABLE. */ + if (into->tableSpaceName != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetTableSpace; + atcmd->name = into->tableSpaceName; + atcmds = lappend(atcmds, atcmd); + } + + /* Set storage parameters via ALTER TABLE. */ + if (into->options != NIL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_ReplaceRelOptions; + atcmd->def = (Node *) into->options; + atcmds = lappend(atcmds, atcmd); + } + + if (atcmds != NIL) + { + AlterTableInternal(matviewOid, atcmds, true); + CommandCounterIncrement(); + } + + relation_close(rel, NoLock); + + ObjectAddressSet(intoRelationAddr, RelationRelationId, matviewOid); + } + else + { + CreateStmt *create = makeNode(CreateStmt); + Datum toast_options; + const static char *validnsps[] = HEAP_RELOPT_NAMESPACES; + + /* + * Create the target relation by faking up a CREATE TABLE parsetree + * and passing it to DefineRelation. + */ + create->relation = into->rel; + create->tableElts = attrList; + create->inhRelations = NIL; + create->ofTypename = NULL; + create->constraints = NIL; + create->options = into->options; + create->oncommit = into->onCommit; + create->tablespacename = into->tableSpaceName; + create->if_not_exists = false; + create->accessMethod = into->accessMethod; + + /* + * Create the relation. (This will error out if there's an existing + * view, so we don't need more code to complain if "replace" is + * false.) + */ + intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, + NULL); - /* parse and validate reloptions for the toast table */ - toast_options = transformRelOptions((Datum) 0, - create->options, - "toast", - validnsps, - true, false); + /* + * If necessary, create a TOAST table for the target table. Note that + * NewRelationCreateToastTable ends with CommandCounterIncrement(), so + * that the TOAST table will be visible for insertion. + */ + CommandCounterIncrement(); + + /* parse and validate reloptions for the toast table */ + toast_options = transformRelOptions((Datum) 0, + create->options, + "toast", + validnsps, + true, false); - (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); + (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); - NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + } /* Create the "view" part of a materialized view. */ if (is_matview) @@ -135,7 +231,7 @@ create_ctas_internal(List *attrList, IntoClause *into) /* StoreViewQuery scribbles on tree, so make a copy */ Query *query = copyObject(into->viewQuery); - StoreViewQuery(intoRelationAddr.objectId, query, false); + StoreViewQuery(intoRelationAddr.objectId, query, replace); CommandCounterIncrement(); } @@ -232,7 +328,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt, /* Check if the relation exists or not */ if (CreateTableAsRelExists(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); + + /* Refresh the materialized view with a fake statement. */ + refresh = makeNode(RefreshMatViewStmt); + refresh->relation = into->rel; + refresh->skipData = into->skipData; + refresh->concurrent = false; + + return ExecRefreshMatView(refresh, pstate->p_sourcetext, qc); + } + return InvalidObjectAddress; + } /* * Create the tuple receiver object and insert info it will need @@ -401,14 +516,15 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) oldrelid = get_relname_relid(into->rel->relname, nspid); if (OidIsValid(oldrelid)) { - if (!ctas->if_not_exists) + if (!ctas->if_not_exists && !into->replace) ereport(ERROR, (errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", into->rel->relname))); /* - * The relation exists and IF NOT EXISTS has been specified. + * The relation exists and IF NOT EXISTS or OR REPLACE has been + * specified. * * If we are in an extension script, insist that the pre-existing * object be a member of the extension, to avoid security risks. @@ -416,11 +532,12 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) ObjectAddressSet(address, RelationRelationId, oldrelid); checkMembershipInCurrentExtension(&address); - /* OK to skip */ - ereport(NOTICE, - (errcode(ERRCODE_DUPLICATE_TABLE), - errmsg("relation \"%s\" already exists, skipping", - into->rel->relname))); + if (ctas->if_not_exists) + /* OK to skip */ + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("relation \"%s\" already exists, skipping", + into->rel->relname))); return true; } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 59156a1c1f6..49713bc0422 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -4605,7 +4605,7 @@ AlterTableGetLockLevel(List *cmds) * Subcommands that may be visible to concurrent SELECTs */ case AT_DropColumn: /* change visible to SELECT */ - case AT_AddColumnToView: /* CREATE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ case AT_DropOids: /* used to equiv to DropColumn */ case AT_EnableAlwaysRule: /* may change SELECT rules */ case AT_EnableReplicaRule: /* may change SELECT rules */ @@ -4900,8 +4900,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* Recursion occurs during execution phase */ pass = AT_PASS_ADD_COL; break; - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ - ATSimplePermissions(cmd->subtype, rel, ATT_VIEW); + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ + ATSimplePermissions(cmd->subtype, rel, ATT_VIEW | ATT_MATVIEW); ATPrepAddColumn(wqueue, rel, recurse, recursing, true, cmd, lockmode, context); /* Recursion occurs during execution phase */ @@ -5332,7 +5332,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, switch (cmd->subtype) { case AT_AddColumn: /* ADD COLUMN */ - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ address = ATExecAddColumn(wqueue, tab, rel, &cmd, cmd->recurse, false, lockmode, cur_pass, context); diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 6f0301555e0..53d4cacc4c1 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -30,8 +30,6 @@ #include "utils/lsyscache.h" #include "utils/rel.h" -static void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc); - /*--------------------------------------------------------------------- * DefineVirtualRelation * @@ -129,7 +127,7 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * column list. */ descriptor = BuildDescForRelation(attrList); - checkViewColumns(descriptor, rel->rd_att); + checkViewColumns(descriptor, rel->rd_att, false); /* * If new attributes have been added, we must add pg_attribute entries @@ -263,15 +261,22 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * added to generate specific complaints. Also, we allow the new view to have * more columns than the old. */ -static void -checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) +void +checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, bool is_matview) { int i; if (newdesc->natts < olddesc->natts) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } for (i = 0; i < olddesc->natts; i++) { @@ -280,17 +285,34 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) /* XXX msg not right, but we don't support DROP COL on view anyway */ if (newattr->attisdropped != oldattr->attisdropped) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change name of view column \"%s\" to \"%s\"", - NameStr(oldattr->attname), - NameStr(newattr->attname)), - errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of materialized view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead.")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + } /* * We cannot allow type, typmod, or collation to change, since these @@ -299,26 +321,48 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) */ if (newattr->atttypid != oldattr->atttypid || newattr->atttypmod != oldattr->atttypmod) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change data type of view column \"%s\" from %s to %s", - NameStr(oldattr->attname), - format_type_with_typemod(oldattr->atttypid, - oldattr->atttypmod), - format_type_with_typemod(newattr->atttypid, - newattr->atttypmod)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of materialized view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod)))); + } /* * At this point, attcollations should be both valid or both invalid, * so applying get_collation_name unconditionally should be fine. */ if (newattr->attcollation != oldattr->attcollation) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", - NameStr(oldattr->attname), - get_collation_name(oldattr->attcollation), - get_collation_name(newattr->attcollation)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of materialized view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation)))); + } } /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 271ae26cbaf..d2edc460378 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4895,6 +4895,21 @@ CreateMatViewStmt: $8->skipData = !($11); $$ = (Node *) ctas; } + | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data + { + 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 = !($10); + $7->replace = true; + $$ = (Node *) ctas; + } ; create_mv_target: diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 8432be641ac..e862b135a73 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2162,7 +2162,7 @@ match_previous_words(int pattern_id, /* complete with something you can create or replace */ else if (TailMatches("CREATE", "OR", "REPLACE")) COMPLETE_WITH("FUNCTION", "PROCEDURE", "LANGUAGE", "RULE", "VIEW", - "AGGREGATE", "TRANSFORM", "TRIGGER"); + "AGGREGATE", "TRANSFORM", "TRIGGER", "MATERIALIZED VIEW"); /* DROP, but not DROP embedded in other commands */ /* complete with something you can drop */ @@ -4008,28 +4008,34 @@ match_previous_words(int pattern_id, COMPLETE_WITH("SELECT"); /* CREATE MATERIALIZED VIEW */ - else if (Matches("CREATE", "MATERIALIZED")) + else if (Matches("CREATE", "MATERIALIZED") || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED")) COMPLETE_WITH("VIEW"); - /* Complete CREATE MATERIALIZED VIEW <name> with AS or USING */ - else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny)) + /* Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> with AS or USING */ + else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny)) COMPLETE_WITH("AS", "USING"); /* - * Complete CREATE MATERIALIZED VIEW <name> USING with list of access + * Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> USING with list of access * methods */ - else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING")) + else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING") || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny, "USING")) COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods); - /* Complete CREATE MATERIALIZED VIEW <name> USING <access method> with AS */ - else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny)) + /* Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> USING <access method> with AS */ + else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny) || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny)) COMPLETE_WITH("AS"); /* - * Complete CREATE MATERIALIZED VIEW <name> [USING <access method> ] AS + * Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> [USING <access method> ] AS * with "SELECT" */ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") || - Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny, "AS")) + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny, "AS") || + Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny, "AS") || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny, "AS")) COMPLETE_WITH("SELECT"); /* CREATE EVENT TRIGGER */ diff --git a/src/include/commands/view.h b/src/include/commands/view.h index c41f51b161c..95290f0a1c4 100644 --- a/src/include/commands/view.h +++ b/src/include/commands/view.h @@ -22,4 +22,7 @@ extern ObjectAddress DefineView(ViewStmt *stmt, const char *queryString, extern void StoreViewQuery(Oid viewOid, Query *viewParse, bool replace); +extern void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, + bool is_matview); + #endif /* VIEW_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 23c9e3c5abf..5a25ba96ab3 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2402,7 +2402,7 @@ typedef struct AlterTableStmt typedef enum AlterTableType { AT_AddColumn, /* add column */ - AT_AddColumnToView, /* implicitly via CREATE OR REPLACE VIEW */ + AT_AddColumnToView, /* implicitly via CREATE OR REPLACE [MATERIALIZED] VIEW */ AT_ColumnDefault, /* alter column default */ AT_CookedColumnDefault, /* add a pre-cooked column default */ AT_DropNotNull, /* alter column drop not null */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index d0576da3e25..e800d13a6e0 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -169,6 +169,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 replace; /* replace existing matview? */ } IntoClause; diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 54939ecc6b0..c0cf8ecbffc 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -694,3 +694,194 @@ NOTICE: relation "matview_ine_tab" already exists, skipping (0 rows) DROP MATERIALIZED VIEW matview_ine_tab; +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +NOTICE: materialized view "mvtest_replace" does not exist, skipping +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + a +--- + 1 +(1 row) + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + a +--- + 2 +(1 row) + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +ERROR: materialized view "mvtest_replace" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW mvtest_replace; +SELECT * FROM mvtest_replace; + a +--- + 3 +(1 row) + +-- add column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 4 AS a, 1 b; +SELECT * FROM mvtest_replace; + a | b +---+--- + 4 | 1 +(1 row) + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+------------+---------+-------- + 4 | 1 | mvtest_replace | | | heap +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+-----------------+------------------+-------- + 5 | 1 | mvtest_replace | {fillfactor=50} | regress_tblspace | heap2 +(1 row) + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; + a | b | a | b +---+---+---+--- + 6 | 1 | 6 | 1 +(1 row) + +DROP VIEW mvtest_replace_v; +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; + a | b +---+--- + 7 | 1 +(1 row) + +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 7 | 1 +(1 row) + +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 8 | 1 +(1 row) + +RESET enable_seqscan; +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +ERROR: cannot change data type of materialized view column "b" from integer to text +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +ERROR: cannot change name of materialized view column "b" to "b2" +HINT: Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead. +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +ERROR: cannot change collation of materialized view column "c" from "C" to "POSIX" +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +ERROR: cannot drop columns from materialized view +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +ERROR: "mvtest_not_mv" is not a materialized view +DROP VIEW mvtest_not_mv; +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; +ERROR: syntax error at or near "NOT" +LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep... + ^ +DROP MATERIALIZED VIEW mvtest_replace; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 6704eeae2df..d6a4dc4b857 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -314,3 +314,111 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; + +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- + +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +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; +SELECT * FROM mvtest_replace; + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; +DROP VIEW mvtest_replace_v; + +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; + +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +SELECT * FROM mvtest_replace; + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +SELECT * FROM mvtest_replace; + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +SELECT * FROM mvtest_replace; + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +SELECT * FROM mvtest_replace; + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +DROP VIEW mvtest_not_mv; + +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; + +DROP MATERIALIZED VIEW mvtest_replace; -- 2.48.1 --aewcrbystcgftlni Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="v6-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v3 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW @ 2024-05-21 16:35 Erik Wienhold <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Erik Wienhold @ 2024-05-21 16:35 UTC (permalink / raw) --- .../sgml/ref/create_materialized_view.sgml | 15 +- src/backend/commands/createas.c | 207 ++++++++++++++---- src/backend/commands/tablecmds.c | 8 +- src/backend/commands/view.c | 106 ++++++--- src/backend/parser/gram.y | 15 ++ src/bin/psql/tab-complete.c | 15 +- src/include/commands/view.h | 3 + src/include/nodes/parsenodes.h | 2 +- src/include/nodes/primnodes.h | 1 + src/test/regress/expected/matview.out | 191 ++++++++++++++++ src/test/regress/sql/matview.sql | 108 +++++++++ 11 files changed, 582 insertions(+), 89 deletions(-) diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml index 0d2fea2b97..b5a8e3441a 100644 --- a/doc/src/sgml/ref/create_materialized_view.sgml +++ b/doc/src/sgml/ref/create_materialized_view.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> +CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ] [ USING <replaceable class="parameter">method</replaceable> ] [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] @@ -60,6 +60,17 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> <title>Parameters</title> <variablelist> + <varlistentry> + <term><literal>OR REPLACE</literal></term> + <listitem> + <para> + Replaces a materialized view if it already exists. + Specifying <literal>OR REPLACE</literal> together with + <literal>IF NOT EXISTS</literal> is an error. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>IF NOT EXISTS</literal></term> <listitem> @@ -67,7 +78,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable> Do not throw an error if a materialized view with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing materialized view is anything like the one that would - have been created. + have been created, unless you use <literal>OR REPLACE</literal> instead. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 0b629b1f79..e4ed3748f9 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -79,55 +79,151 @@ static void intorel_destroy(DestReceiver *self); static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into) { - CreateStmt *create = makeNode(CreateStmt); - bool is_matview; + bool is_matview, + replace = false; char relkind; - Datum toast_options; - const char *const validnsps[] = HEAP_RELOPT_NAMESPACES; + Oid matviewOid = InvalidOid; ObjectAddress intoRelationAddr; /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */ is_matview = (into->viewQuery != NULL); relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION; - /* - * Create the target relation by faking up a CREATE TABLE parsetree and - * passing it to DefineRelation. - */ - create->relation = into->rel; - create->tableElts = attrList; - create->inhRelations = NIL; - create->ofTypename = NULL; - create->constraints = NIL; - create->options = into->options; - create->oncommit = into->onCommit; - create->tablespacename = into->tableSpaceName; - create->if_not_exists = false; - create->accessMethod = into->accessMethod; + /* Check if an existing materialized view needs to be replaced. */ + if (is_matview) + { + LOCKMODE lockmode; - /* - * Create the relation. (This will error out if there's an existing view, - * so we don't need more code to complain if "replace" is false.) - */ - intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, NULL); + lockmode = into->replace ? AccessExclusiveLock : NoLock; + (void) RangeVarGetAndCheckCreationNamespace(into->rel, lockmode, + &matviewOid); + replace = OidIsValid(matviewOid) && into->replace; + } - /* - * If necessary, create a TOAST table for the target table. Note that - * NewRelationCreateToastTable ends with CommandCounterIncrement(), so - * that the TOAST table will be visible for insertion. - */ - CommandCounterIncrement(); + if (is_matview && replace) + { + Relation rel; + List *atcmds = NIL; + AlterTableCmd *atcmd; + TupleDesc descriptor; + + rel = relation_open(matviewOid, NoLock); + + if (rel->rd_rel->relkind != RELKIND_MATVIEW) + ereport(ERROR, + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a materialized view", + RelationGetRelationName(rel))); + + CheckTableNotInUse(rel, "CREATE OR REPLACE MATERIALIZED VIEW"); + + descriptor = BuildDescForRelation(attrList); + checkViewColumns(descriptor, rel->rd_att, true); + + /* Add new attributes via ALTER TABLE. */ + if (list_length(attrList) > rel->rd_att->natts) + { + ListCell *c; + int skip = rel->rd_att->natts; + + foreach(c, attrList) + { + if (skip > 0) + { + skip--; + continue; + } + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_AddColumnToView; + atcmd->def = (Node *) lfirst(c); + atcmds = lappend(atcmds, atcmd); + } + } + + /* Set access method via ALTER TABLE. */ + if (into->accessMethod != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetAccessMethod; + atcmd->name = into->accessMethod; + atcmds = lappend(atcmds, atcmd); + } + + /* Set tablespace via ALTER TABLE. */ + if (into->tableSpaceName != NULL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_SetTableSpace; + atcmd->name = into->tableSpaceName; + atcmds = lappend(atcmds, atcmd); + } + + /* Set storage parameters via ALTER TABLE. */ + if (into->options != NIL) + { + atcmd = makeNode(AlterTableCmd); + atcmd->subtype = AT_ReplaceRelOptions; + atcmd->def = (Node *) into->options; + atcmds = lappend(atcmds, atcmd); + } + + if (atcmds != NIL) + { + AlterTableInternal(matviewOid, atcmds, true); + CommandCounterIncrement(); + } + + relation_close(rel, NoLock); + + ObjectAddressSet(intoRelationAddr, RelationRelationId, matviewOid); + } + else + { + CreateStmt *create = makeNode(CreateStmt); + Datum toast_options; + const static char *validnsps[] = HEAP_RELOPT_NAMESPACES; + + /* + * Create the target relation by faking up a CREATE TABLE parsetree + * and passing it to DefineRelation. + */ + create->relation = into->rel; + create->tableElts = attrList; + create->inhRelations = NIL; + create->ofTypename = NULL; + create->constraints = NIL; + create->options = into->options; + create->oncommit = into->onCommit; + create->tablespacename = into->tableSpaceName; + create->if_not_exists = false; + create->accessMethod = into->accessMethod; + + /* + * Create the relation. (This will error out if there's an existing + * view, so we don't need more code to complain if "replace" is + * false.) + */ + intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, + NULL); - /* parse and validate reloptions for the toast table */ - toast_options = transformRelOptions((Datum) 0, - create->options, - "toast", - validnsps, - true, false); + /* + * If necessary, create a TOAST table for the target table. Note that + * NewRelationCreateToastTable ends with CommandCounterIncrement(), so + * that the TOAST table will be visible for insertion. + */ + CommandCounterIncrement(); + + /* parse and validate reloptions for the toast table */ + toast_options = transformRelOptions((Datum) 0, + create->options, + "toast", + validnsps, + true, false); - (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); + (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); - NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + } /* Create the "view" part of a materialized view. */ if (is_matview) @@ -135,7 +231,7 @@ create_ctas_internal(List *attrList, IntoClause *into) /* StoreViewQuery scribbles on tree, so make a copy */ Query *query = (Query *) copyObject(into->viewQuery); - StoreViewQuery(intoRelationAddr.objectId, query, false); + StoreViewQuery(intoRelationAddr.objectId, query, replace); CommandCounterIncrement(); } @@ -231,7 +327,26 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt, /* Check if the relation exists or not */ if (CreateTableAsRelExists(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); + + /* Refresh the materialized view with a fake statement. */ + refresh = makeNode(RefreshMatViewStmt); + refresh->relation = into->rel; + refresh->skipData = into->skipData; + refresh->concurrent = false; + + return ExecRefreshMatView(refresh, NULL, NULL); + } + return InvalidObjectAddress; + } /* * Create the tuple receiver object and insert info it will need @@ -392,14 +507,15 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) oldrelid = get_relname_relid(into->rel->relname, nspid); if (OidIsValid(oldrelid)) { - if (!ctas->if_not_exists) + if (!ctas->if_not_exists && !into->replace) ereport(ERROR, (errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", into->rel->relname))); /* - * The relation exists and IF NOT EXISTS has been specified. + * The relation exists and IF NOT EXISTS or OR REPLACE has been + * specified. * * If we are in an extension script, insist that the pre-existing * object be a member of the extension, to avoid security risks. @@ -407,11 +523,12 @@ CreateTableAsRelExists(CreateTableAsStmt *ctas) ObjectAddressSet(address, RelationRelationId, oldrelid); checkMembershipInCurrentExtension(&address); - /* OK to skip */ - ereport(NOTICE, - (errcode(ERRCODE_DUPLICATE_TABLE), - errmsg("relation \"%s\" already exists, skipping", - into->rel->relname))); + if (ctas->if_not_exists) + /* OK to skip */ + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("relation \"%s\" already exists, skipping", + into->rel->relname))); return true; } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index b3cc6f8f69..bcc08f9420 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -4480,7 +4480,7 @@ AlterTableGetLockLevel(List *cmds) * Subcommands that may be visible to concurrent SELECTs */ case AT_DropColumn: /* change visible to SELECT */ - case AT_AddColumnToView: /* CREATE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ case AT_DropOids: /* used to equiv to DropColumn */ case AT_EnableAlwaysRule: /* may change SELECT rules */ case AT_EnableReplicaRule: /* may change SELECT rules */ @@ -4783,8 +4783,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* Recursion occurs during execution phase */ pass = AT_PASS_ADD_COL; break; - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ - ATSimplePermissions(cmd->subtype, rel, ATT_VIEW); + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ + ATSimplePermissions(cmd->subtype, rel, ATT_VIEW | ATT_MATVIEW); ATPrepAddColumn(wqueue, rel, recurse, recursing, true, cmd, lockmode, context); /* Recursion occurs during execution phase */ @@ -5178,7 +5178,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, switch (cmd->subtype) { case AT_AddColumn: /* ADD COLUMN */ - case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */ + case AT_AddColumnToView: /* via CREATE OR REPLACE [MATERIALIZED] VIEW */ address = ATExecAddColumn(wqueue, tab, rel, &cmd, cmd->recurse, false, lockmode, cur_pass, context); diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index fdad833832..76532aa35d 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -30,8 +30,6 @@ #include "utils/lsyscache.h" #include "utils/rel.h" -static void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc); - /*--------------------------------------------------------------------- * DefineVirtualRelation * @@ -130,7 +128,7 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * column list. */ descriptor = BuildDescForRelation(attrList); - checkViewColumns(descriptor, rel->rd_att); + checkViewColumns(descriptor, rel->rd_att, false); /* * If new attributes have been added, we must add pg_attribute entries @@ -263,15 +261,22 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, * added to generate specific complaints. Also, we allow the new view to have * more columns than the old. */ -static void -checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) +void +checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, bool is_matview) { int i; if (newdesc->natts < olddesc->natts) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } for (i = 0; i < olddesc->natts; i++) { @@ -280,17 +285,34 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) /* XXX msg not right, but we don't support DROP COL on view anyway */ if (newattr->attisdropped != oldattr->attisdropped) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot drop columns from view"))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from materialized view")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop columns from view"))); + } if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change name of view column \"%s\" to \"%s\"", - NameStr(oldattr->attname), - NameStr(newattr->attname)), - errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of materialized view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead.")); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change name of view column \"%s\" to \"%s\"", + NameStr(oldattr->attname), + NameStr(newattr->attname)), + errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead."))); + } /* * We cannot allow type, typmod, or collation to change, since these @@ -299,26 +321,48 @@ checkViewColumns(TupleDesc newdesc, TupleDesc olddesc) */ if (newattr->atttypid != oldattr->atttypid || newattr->atttypmod != oldattr->atttypmod) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change data type of view column \"%s\" from %s to %s", - NameStr(oldattr->attname), - format_type_with_typemod(oldattr->atttypid, - oldattr->atttypmod), - format_type_with_typemod(newattr->atttypid, - newattr->atttypmod)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of materialized view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change data type of view column \"%s\" from %s to %s", + NameStr(oldattr->attname), + format_type_with_typemod(oldattr->atttypid, + oldattr->atttypmod), + format_type_with_typemod(newattr->atttypid, + newattr->atttypmod)))); + } /* * At this point, attcollations should be both valid or both invalid, * so applying get_collation_name unconditionally should be fine. */ if (newattr->attcollation != oldattr->attcollation) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", - NameStr(oldattr->attname), - get_collation_name(oldattr->attcollation), - get_collation_name(newattr->attcollation)))); + { + if (is_matview) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of materialized view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation))); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"", + NameStr(oldattr->attname), + get_collation_name(oldattr->attcollation), + get_collation_name(newattr->attcollation)))); + } } /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 84cef57a70..b02b2f42d2 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4779,6 +4779,21 @@ CreateMatViewStmt: $8->skipData = !($11); $$ = (Node *) ctas; } + | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data + { + 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 = !($10); + $7->replace = true; + $$ = (Node *) ctas; + } ; create_mv_target: diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index a7ccde6d7d..c50a3781db 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1811,7 +1811,7 @@ psql_completion(const char *text, int start, int end) /* complete with something you can create or replace */ else if (TailMatches("CREATE", "OR", "REPLACE")) COMPLETE_WITH("FUNCTION", "PROCEDURE", "LANGUAGE", "RULE", "VIEW", - "AGGREGATE", "TRANSFORM", "TRIGGER"); + "AGGREGATE", "TRANSFORM", "TRIGGER", "MATERIALIZED VIEW"); /* DROP, but not DROP embedded in other commands */ /* complete with something you can drop */ @@ -3599,13 +3599,16 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH("SELECT"); /* CREATE MATERIALIZED VIEW */ - else if (Matches("CREATE", "MATERIALIZED")) + else if (Matches("CREATE", "MATERIALIZED") || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED")) COMPLETE_WITH("VIEW"); - /* Complete CREATE MATERIALIZED VIEW <name> with AS */ - else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny)) + /* Complete CREATE [ OR REPLACE ] MATERIALIZED VIEW <name> with AS */ + else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny) || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny)) COMPLETE_WITH("AS"); - /* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */ - else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS")) + /* Complete "CREATE [ OR REPLACE ] MATERIALIZED VIEW <sth> AS with "SELECT" */ + else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") || + Matches("CREATE", "OR", "REPLACE", "MATERIALIZED", "VIEW", MatchAny, "AS")) COMPLETE_WITH("SELECT"); /* CREATE EVENT TRIGGER */ diff --git a/src/include/commands/view.h b/src/include/commands/view.h index d2d8588989..7eacdaaceb 100644 --- a/src/include/commands/view.h +++ b/src/include/commands/view.h @@ -22,4 +22,7 @@ extern ObjectAddress DefineView(ViewStmt *stmt, const char *queryString, extern void StoreViewQuery(Oid viewOid, Query *viewParse, bool replace); +extern void checkViewColumns(TupleDesc newdesc, TupleDesc olddesc, + bool is_matview); + #endif /* VIEW_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 124d853e49..3ba536eee3 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2338,7 +2338,7 @@ typedef struct AlterTableStmt typedef enum AlterTableType { AT_AddColumn, /* add column */ - AT_AddColumnToView, /* implicitly via CREATE OR REPLACE VIEW */ + AT_AddColumnToView, /* implicitly via CREATE OR REPLACE [MATERIALIZED] VIEW */ AT_ColumnDefault, /* alter column default */ AT_CookedColumnDefault, /* add a pre-cooked column default */ AT_DropNotNull, /* alter column drop not null */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index ea47652adb..4b0ee5d10d 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 replace; /* replace existing matview? */ } IntoClause; diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 038ab73517..e2e2a13396 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -694,3 +694,194 @@ NOTICE: relation "matview_ine_tab" already exists, skipping (0 rows) DROP MATERIALIZED VIEW matview_ine_tab; +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +NOTICE: materialized view "mvtest_replace" does not exist, skipping +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + a +--- + 1 +(1 row) + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + a +--- + 2 +(1 row) + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +ERROR: materialized view "mvtest_replace" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW mvtest_replace; +SELECT * FROM mvtest_replace; + a +--- + 3 +(1 row) + +-- add column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 4 AS a, 1 b; +SELECT * FROM mvtest_replace; + a | b +---+--- + 4 | 1 +(1 row) + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+------------+---------+-------- + 4 | 1 | mvtest_replace | | | heap +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + a | b | relname | reloptions | spcname | amname +---+---+----------------+-----------------+------------------+-------- + 5 | 1 | mvtest_replace | {fillfactor=50} | regress_tblspace | heap2 +(1 row) + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; + a | b | a | b +---+---+---+--- + 6 | 1 | 6 | 1 +(1 row) + +DROP VIEW mvtest_replace_v; +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; + a | b +---+--- + 7 | 1 +(1 row) + +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 7 | 1 +(1 row) + +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; + QUERY PLAN +--------------------------------------------------------- + Index Scan using mvtest_replace_b_idx on mvtest_replace + Index Cond: (b = 1) +(2 rows) + +SELECT * FROM mvtest_replace WHERE b = 1; + a | b +---+--- + 8 | 1 +(1 row) + +RESET enable_seqscan; +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +ERROR: cannot change data type of materialized view column "b" from integer to text +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +ERROR: cannot change name of materialized view column "b" to "b2" +HINT: Use ALTER MATERIALIZED VIEW ... RENAME COLUMN ... to change name of materialized view column instead. +SELECT * FROM mvtest_replace; + a | b +---+--- + 8 | 1 +(1 row) + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +ERROR: cannot change collation of materialized view column "c" from "C" to "POSIX" +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +ERROR: cannot drop columns from materialized view +SELECT * FROM mvtest_replace; + a | b | c +----+---+--- + 11 | 1 | y +(1 row) + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +ERROR: "mvtest_not_mv" is not a materialized view +DROP VIEW mvtest_not_mv; +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; +ERROR: syntax error at or near "NOT" +LINE 1: CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_rep... + ^ +DROP MATERIALIZED VIEW mvtest_replace; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index b74ee305e0..c12f0243c9 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -314,3 +314,111 @@ 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 DROP MATERIALIZED VIEW matview_ine_tab; + +-- +-- test CREATE OR REPLACE MATERIALIZED VIEW +-- + +-- matview does not already exist +DROP MATERIALIZED VIEW IF EXISTS mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 1 AS a; +SELECT * FROM mvtest_replace; + +-- replace query with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 2 AS a; +SELECT * FROM mvtest_replace; + +-- replace query without data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 3 AS a + WITH NO DATA; +SELECT * FROM mvtest_replace; -- error: not populated +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; +SELECT * FROM mvtest_replace; + +-- replace table options +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace + USING heap2 + WITH (fillfactor = 50) + TABLESPACE regress_tblspace + AS SELECT 5 AS a, 1 AS b; +SELECT m.*, c.relname, c.reloptions, s.spcname, a.amname + FROM mvtest_replace m + CROSS JOIN pg_class c + LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace + LEFT JOIN pg_am a ON a.oid = c.relam + WHERE c.relname = 'mvtest_replace'; + +-- can replace matview that has a dependent view +CREATE VIEW mvtest_replace_v AS + SELECT * FROM mvtest_replace; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 6 AS a, 1 AS b; +SELECT * FROM mvtest_replace, mvtest_replace_v; +DROP VIEW mvtest_replace_v; + +-- index gets rebuilt when replacing with data +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 7 AS a, 1 AS b; +CREATE UNIQUE INDEX ON mvtest_replace (b); +SELECT * FROM mvtest_replace; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 8 AS a, 1 AS b; +SET enable_seqscan = off; -- force index scan +EXPLAIN (COSTS OFF) SELECT * FROM mvtest_replace WHERE b = 1; +SELECT * FROM mvtest_replace WHERE b = 1; +RESET enable_seqscan; + +-- cannot change column data type +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 9 AS a, 'x' AS b; -- error +SELECT * FROM mvtest_replace; + +-- cannot rename column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 10 AS a, 1 AS b2; -- error +SELECT * FROM mvtest_replace; + +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 11 AS a, 1 AS b, 'y' COLLATE "C" AS c; +SELECT * FROM mvtest_replace; + +-- cannot change column collation +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 12 AS a, 1 AS b, 'x' COLLATE "POSIX" AS c; -- error +SELECT * FROM mvtest_replace; + +-- cannot drop column +CREATE OR REPLACE MATERIALIZED VIEW mvtest_replace AS + SELECT 13 AS a, 1 AS b; -- error +SELECT * FROM mvtest_replace; + +-- must target a matview +CREATE VIEW mvtest_not_mv AS + SELECT 1 AS a; +CREATE OR REPLACE MATERIALIZED VIEW mvtest_not_mv AS + SELECT 1 AS a; -- error +DROP VIEW mvtest_not_mv; + +-- cannot use OR REPLACE with IF NOT EXISTS +CREATE OR REPLACE MATERIALIZED VIEW IF NOT EXISTS mvtest_replace AS + SELECT 1 AS a; + +DROP MATERIALIZED VIEW mvtest_replace; -- 2.46.0 --muysnh7l3kienazz Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-05-21 16:35 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-11-11 12:51 [PATCH v5 1/2] In-place table persistence change Kyotaro Horiguchi <[email protected]> 2024-05-21 16:35 [PATCH v4 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW Erik Wienhold <[email protected]> 2024-05-21 16:35 [PATCH v1 1/2] Add CREATE OR REPLACE MATERIALIZED VIEW Erik Wienhold <[email protected]> 2024-05-21 16:35 [PATCH v2 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW Erik Wienhold <[email protected]> 2024-05-21 16:35 [PATCH v6 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW Erik Wienhold <[email protected]> 2024-05-21 16:35 [PATCH v3 1/3] Add CREATE OR REPLACE MATERIALIZED VIEW Erik Wienhold <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox