PostgreSQL create or replace view和重建視圖 有什么區別？

一、 replace vs 重建

? ? ? ?遇到開發提了個問題，create or replace view和重建視圖（drop+create）有什么區別，查詢資料整理了一下。

1.?create or replace

  • 當存在同名視圖時，嘗試將其替換
  • 新視圖語句必須與現有視圖查詢具有相同的列（即相同的列名、列順序和數據類型）
  • pg 8.1開始，之前向視圖末尾添加新列
  • 總體而言，改動限制較大，但replace后不影響權限和依賴于該視圖的對象

2.?drop+create

  • 新視圖定義不依賴原視圖，靈活度高
  • 重建后權限丟失，必須重新授權
-- 對象授權查詢
SELECT * FROM information_schema.table_privileges WHERE table_name='視圖名' and grantor<>grantee;
  • 依賴于該視圖的對象需要一起重建，復雜度可能較高

二、 源碼學習

1.?CREATE OR REPLACE VIEW

? ? ? ?按照 "CREATE OR REPLACE VIEW" 關鍵字搜索，這部分代碼在ATExecCmd函數（tablecmds.c文件）。可以看到它對應的命令類型叫AT_AddColumnToView，對應操作為調用ATExecAddColumn函數為視圖新加列。

/** ATExecCmd: dispatch a subcommand to appropriate execution routine*/
static void
ATExecCmd(List **wqueue, AlteredTableInfo *tab,AlterTableCmd *cmd, LOCKMODE lockmode, int cur_pass,AlterTableUtilityContext *context)
{ObjectAddress address = InvalidObjectAddress;Relation	rel = tab->rel;switch (cmd->subtype){case AT_AddColumn:		/* ADD COLUMN */case AT_AddColumnToView:	/* add column via CREATE OR REPLACE VIEW */address = ATExecAddColumn(wqueue, tab, rel, &cmd,false, false,lockmode, cur_pass, context);break;
...

2.?ATExecAddColumn函數

比較長的一個函數，主要操作如下：

  • 權限檢查

/** Add a column to a table.  The return value is the address of the* new column in the parent relation.** cmd is pass-by-ref so that we can replace it with the parse-transformed* copy (but that happens only after we check for IF NOT EXISTS).*/
static ObjectAddress
ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,AlterTableCmd **cmd,bool recurse, bool recursing,LOCKMODE lockmode, int cur_pass,AlterTableUtilityContext *context)
{Oid			myrelid = RelationGetRelid(rel);ColumnDef  *colDef = castNode(ColumnDef, (*cmd)->def);bool		if_not_exists = (*cmd)->missing_ok;Relation	pgclass,attrdesc;
.../* At top level, permission check was done in ATPrepCmd, else do it */if (recursing)ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);if (rel->rd_rel->relispartition && !recursing)ereport(ERROR,(errcode(ERRCODE_WRONG_OBJECT_TYPE),errmsg("cannot add column to a partition")));

  • 獲取表鎖，等級為RowExclusiveLock

attrdesc = table_open(AttributeRelationId, RowExclusiveLock);

  • 判斷是否為遞歸子表加列，能否進行merge列

    若是merge，要求更新前后列類型及排序規則一致（Child column must match on type, typmod, and collation）

	/** Are we adding the column to a recursion child?  If so, check whether to* merge with an existing definition for the column.  If we do merge, we* must not recurse.  Children will already have the column, and recursing* into them would mess up attinhcount.*/if (colDef->inhcount > 0){HeapTuple	tuple;/* Does child already have a column by this name? */tuple = SearchSysCacheCopyAttName(myrelid, colDef->colname);if (HeapTupleIsValid(tuple)){Form_pg_attribute childatt = (Form_pg_attribute) GETSTRUCT(tuple);Oid			ctypeId;int32		ctypmod;Oid			ccollid;/* Child column must match on type, typmod, and collation */typenameTypeIdAndMod(NULL, colDef->typeName, &ctypeId, &ctypmod);if (ctypeId != childatt->atttypid ||ctypmod != childatt->atttypmod)ereport(ERROR,(errcode(ERRCODE_DATATYPE_MISMATCH),errmsg("child table \"%s\" has different type for column \"%s\"",RelationGetRelationName(rel), colDef->colname)));ccollid = GetColumnDefCollation(NULL, colDef, ctypeId);if (ccollid != childatt->attcollation)ereport(ERROR,(errcode(ERRCODE_COLLATION_MISMATCH),errmsg("child table \"%s\" has different collation for column \"%s\"",RelationGetRelationName(rel), colDef->colname),errdetail("\"%s\" versus \"%s\"",get_collation_name(ccollid),get_collation_name(childatt->attcollation))));/* Bump the existing child att's inhcount */childatt->attinhcount++;CatalogTupleUpdate(attrdesc, &tuple->t_self, tuple);heap_freetuple(tuple);/* Inform the user about the merge */ereport(NOTICE,(errmsg("merging definition of column \"%s\" for child \"%s\"",colDef->colname, RelationGetRelationName(rel))));table_close(attrdesc, RowExclusiveLock);return InvalidObjectAddress;}}

  • 一些檢查，例如列名是否已存在，列數是否超出限制等

	/* skip if the name already exists and if_not_exists is true */if (!check_for_column_name_collision(rel, colDef->colname, if_not_exists)){table_close(attrdesc, RowExclusiveLock);return InvalidObjectAddress;}
.../* Determine the new attribute's number */newattnum = ((Form_pg_class) GETSTRUCT(reltup))->relnatts + 1;if (newattnum > MaxHeapAttributeNumber)ereport(ERROR,(errcode(ERRCODE_TOO_MANY_COLUMNS),errmsg("tables can have at most %d columns",MaxHeapAttributeNumber)));

  • 填充pg_attribute及pg_class信息

	/* construct new attribute's pg_attribute entry */attribute.attrelid = myrelid;namestrcpy(&(attribute.attname), colDef->colname);attribute.atttypid = typeOid;attribute.attstattarget = (newattnum > 0) ? -1 : 0;attribute.attlen = tform->typlen;attribute.attnum = newattnum;attribute.attndims = list_length(colDef->typeName->arrayBounds);attribute.atttypmod = typmod;attribute.attbyval = tform->typbyval;attribute.attalign = tform->typalign;attribute.attstorage = tform->typstorage;attribute.attcompression = GetAttributeCompression(typeOid,colDef->compression);attribute.attnotnull = colDef->is_not_null;attribute.atthasdef = false;attribute.atthasmissing = false;attribute.attidentity = colDef->identity;attribute.attgenerated = colDef->generated;attribute.attisdropped = false;attribute.attislocal = colDef->is_local;attribute.attinhcount = colDef->inhcount;attribute.attcollation = collOid;/* attribute.attacl is handled by InsertPgAttributeTuples() */ReleaseSysCache(typeTuple);tupdesc = CreateTupleDesc(lengthof(aattr), (FormData_pg_attribute **) &aattr);InsertPgAttributeTuples(attrdesc, tupdesc, myrelid, NULL, NULL);table_close(attrdesc, RowExclusiveLock);/** Update pg_class tuple as appropriate*/((Form_pg_class) GETSTRUCT(reltup))->relnatts = newattnum;CatalogTupleUpdate(pgclass, &reltup->t_self, reltup);heap_freetuple(reltup);/* Post creation hook for new attribute */InvokeObjectPostCreateHook(RelationRelationId, myrelid, newattnum);table_close(pgclass, RowExclusiveLock);/* Make the attribute's catalog entry visible */CommandCounterIncrement();

  • 填充默認值

/** Store the DEFAULT, if any, in the catalogs*/if (colDef->raw_default){RawColumnDefault *rawEnt;rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault));rawEnt->attnum = attribute.attnum;rawEnt->raw_default = copyObject(colDef->raw_default);/** Attempt to skip a complete table rewrite by storing the specified* DEFAULT value outside of the heap.  This may be disabled inside* AddRelationNewConstraints if the optimization cannot be applied.*/rawEnt->missingMode = (!colDef->generated);rawEnt->generated = colDef->generated;/** This function is intended for CREATE TABLE, so it processes a* _list_ of defaults, but we just do one.*/AddRelationNewConstraints(rel, list_make1(rawEnt), NIL,false, true, false, NULL);/* Make the additional catalog changes visible */CommandCounterIncrement();/** Did the request for a missing value work? If not we'll have to do a* rewrite*/if (!rawEnt->missingMode)tab->rewrite |= AT_REWRITE_DEFAULT_VAL;}/** Tell Phase 3 to fill in the default expression, if there is one.** If there is no default, Phase 3 doesn't have to do anything, because* that effectively means that the default is NULL.  The heap tuple access* routines always check for attnum > # of attributes in tuple, and return* NULL if so, so without any modification of the tuple data we will get* the effect of NULL values in the new column.** An exception occurs when the new column is of a domain type: the domain* might have a NOT NULL constraint, or a check constraint that indirectly* rejects nulls.  If there are any domain constraints then we construct* an explicit NULL default value that will be passed through* CoerceToDomain processing.  (This is a tad inefficient, since it causes* rewriting the table which we really don't have to do, but the present* design of domain processing doesn't offer any simple way of checking* the constraints more directly.)** Note: we use build_column_default, and not just the cooked default* returned by AddRelationNewConstraints, so that the right thing happens* when a datatype's default applies.** Note: it might seem that this should happen at the end of Phase 2, so* that the effects of subsequent subcommands can be taken into account.* It's intentional that we do it now, though.  The new column should be* filled according to what is said in the ADD COLUMN subcommand, so that* the effects are the same as if this subcommand had been run by itself* and the later subcommands had been issued in new ALTER TABLE commands.** We can skip this entirely for relations without storage, since Phase 3* is certainly not going to touch them.  System attributes don't have* interesting defaults, either.*/if (RELKIND_HAS_STORAGE(relkind) && attribute.attnum > 0){/** For an identity column, we can't use build_column_default(),* because the sequence ownership isn't set yet.  So do it manually.*/if (colDef->identity){NextValueExpr *nve = makeNode(NextValueExpr);nve->seqid = RangeVarGetRelid(colDef->identitySequence, NoLock, false);nve->typeId = typeOid;defval = (Expr *) nve;/* must do a rewrite for identity columns */tab->rewrite |= AT_REWRITE_DEFAULT_VAL;}elsedefval = (Expr *) build_column_default(rel, attribute.attnum);if (!defval && DomainHasConstraints(typeOid)){Oid			baseTypeId;int32		baseTypeMod;Oid			baseTypeColl;baseTypeMod = typmod;baseTypeId = getBaseTypeAndTypmod(typeOid, &baseTypeMod);baseTypeColl = get_typcollation(baseTypeId);defval = (Expr *) makeNullConst(baseTypeId, baseTypeMod, baseTypeColl);defval = (Expr *) coerce_to_target_type(NULL,(Node *) defval,baseTypeId,typeOid,typmod,COERCION_ASSIGNMENT,COERCE_IMPLICIT_CAST,-1);if (defval == NULL) /* should not happen */elog(ERROR, "failed to coerce base type to domain");}if (defval){NewColumnValue *newval;newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));newval->attnum = attribute.attnum;newval->expr = expression_planner(defval);newval->is_generated = (colDef->generated != '\0');tab->newvals = lappend(tab->newvals, newval);}if (DomainHasConstraints(typeOid))tab->rewrite |= AT_REWRITE_DEFAULT_VAL;if (!TupleDescAttr(rel->rd_att, attribute.attnum - 1)->atthasmissing){/** If the new column is NOT NULL, and there is no missing value,* tell Phase 3 it needs to check for NULLs.*/tab->verify_new_notnull |= colDef->is_not_null;}}

  • 更新pg_attribute的atttypid及attcollation字段

	/** Add needed dependency entries for the new column.*/add_column_datatype_dependency(myrelid, newattnum, attribute.atttypid);add_column_collation_dependency(myrelid, newattnum, attribute.attcollation);

  • 若有子表，為每個子表遞歸調用該函數新增列

/** Propagate to children as appropriate.  Unlike most other ALTER* routines, we have to do this one level of recursion at a time; we can't* use find_all_inheritors to do it in one pass.*/children =find_inheritance_children(RelationGetRelid(rel), lockmode);/** If we are told not to recurse, there had better not be any child* tables; else the addition would put them out of step.*/if (children && !recurse)ereport(ERROR,(errcode(ERRCODE_INVALID_TABLE_DEFINITION),errmsg("column must be added to child tables too")));/* Children should see column as singly inherited */if (!recursing){childcmd = copyObject(*cmd);colDef = castNode(ColumnDef, childcmd->def);colDef->inhcount = 1;colDef->is_local = false;}elsechildcmd = *cmd;		/* no need to copy again */foreach(child, children){Oid			childrelid = lfirst_oid(child);Relation	childrel;AlteredTableInfo *childtab;/* find_inheritance_children already got lock */childrel = table_open(childrelid, NoLock);CheckTableNotInUse(childrel, "ALTER TABLE");/* Find or create work queue entry for this table */childtab = ATGetQueueEntry(wqueue, childrel);/* Recurse to child; return value is ignored */ATExecAddColumn(wqueue, childtab, childrel,&childcmd, recurse, true,lockmode, cur_pass, context);table_close(childrel, NoLock);}ObjectAddressSubSet(address, RelationRelationId, myrelid, newattnum);return address;
}

參考

PostgreSQL: Documentation: 16: CREATE VIEW

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/167310.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/167310.shtml
英文地址，請注明出處：http://en.pswp.cn/news/167310.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

LeetCode算法題解（動態規劃，背包問題）|LeetCode1049. 最后一塊石頭的重量 II、LeetCode494. 目標和

LeetCode算法題解（動態規劃，背包問題）|LeetCode1049. 最后一塊石頭的重量 II、LeetCode494. 目標和

一、LeetCode1049. 最后一塊石頭的重量 II 題目鏈接&#xff1a;1049. 最后一塊石頭的重量 II 題目描述&#xff1a; 有一堆石頭&#xff0c;用整數數組 stones 表示。其中 stones[i] 表示第 i 塊石頭的重量。 每一回合&#xff0c;從中選出任意兩塊石頭&#xff0c;然后將…
閱讀更多...
springboot2.1升級到2.7 actuator丟失部分metrics端點

springboot2.1升級到2.7 actuator丟失部分metrics端點

項目場景&#xff1a; 項目需要升級springboot從2.1升級至2.7 問題描述 發現之前的metrics后面的jvm相關的端口丟了 原因分析&#xff1a; 找到這樣一篇博文https://blog.csdn.net/CL_YD/article/details/120309094&#xff0c;這篇博文意思是對的&#xff0c;但是寫的不太好…
閱讀更多...
Java基于springoot開發的企業招聘求職網站

Java基于springoot開發的企業招聘求職網站

演示視頻&#xff1a; https://www.bilibili.com/video/BV1xw411n7Tu/?share_sourcecopy_web&vd_source11344bb73ef9b33550b8202d07ae139b 技術&#xff1a;springootmysqlvuejsbootstrappoi制作word模板 主要功能&#xff1a;求職者可以注冊發布簡歷&#xff0c;選擇簡…
閱讀更多...
案例018:基于微信小程序的實習記錄系統

案例018:基于微信小程序的實習記錄系統

文末獲取源碼 開發語言&#xff1a;Java 框架&#xff1a;SSM JDK版本&#xff1a;JDK1.8 數據庫&#xff1a;mysql 5.7 開發軟件&#xff1a;eclipse/myeclipse/idea Maven包&#xff1a;Maven3.5.4 小程序框架&#xff1a;uniapp 小程序開發軟件&#xff1a;HBuilder X 小程序…
閱讀更多...
【python入門篇】函數（6）

【python入門篇】函數（6）

這一節將詳細介紹Python中函數的用法&#xff0c;包括函數的定義、調用、參數、返回值、作用域等。 函數的概述&#xff1a; Python函數是一種封裝了特定任務的可重用代碼塊。通過將程序分解為更小、更具體的任務&#xff0c;函數提供了一種有效的方式來組織和管理代碼&#xf…
閱讀更多...
保姆級連接FusionInsight MRS kerberos Hive

保姆級連接FusionInsight MRS kerberos Hive

數新網絡&#xff0c;讓每個人享受數據的價值https://xie.infoq.cn/link?targethttps%3A%2F%2Fwww.datacyber.com%2F 概述 本文將介紹在華為云 FusionInsight MRS&#xff08;Managed Relational Service&#xff09;的Kerberos環境中&#xff0c;如何使用Java和DBeaver實現遠…
閱讀更多...
threejs創建一個旋轉的正方體【完整代碼】

threejs創建一個旋轉的正方體【完整代碼】

效果&#xff1a; 中文網three.js docs 1.搭建環境 安裝three 首先我們需要新建一個項目 vue/react都可 這里以vue為演示 npm i three 找到一個新的頁面 在頁面script的地方導入three import * as THREE from "three" 或者自己逐個導入 import {PerspectiveC…
閱讀更多...
京東采銷面對面，洞悉行業新趨勢 京東3C數碼生態大會在武漢圓滿舉行

京東采銷面對面，洞悉行業新趨勢 京東3C數碼生態大會在武漢圓滿舉行

為促進湖北省3C數碼產業發展&#xff0c;本地企業降本增效、促進行業交流、充分發揮京東集團全鏈路生態服務能力&#xff0c;支持地方3C特色產業提質增量。2023年11月23日&#xff0c;由京東零售、京東物流主辦&#xff0c;湖北省電子商務行業協會聯合協辦的“聚力共贏、攜手共…
閱讀更多...
【Kotlin精簡】第9章 Kotlin Flow

【Kotlin精簡】第9章 Kotlin Flow

1 前言 上一章節我們學習了Kotlin的協程【Kotlin精簡】第8章 協程&#xff0c;我們知道 協程實質是對線程切換的封裝&#xff0c;能更加安全實現異步代碼同步化&#xff0c;本質上協程、線程都是服務于并發場景下&#xff0c;其中協程是協作式任務&#xff0c;線程是搶占式任務…
閱讀更多...
保姆級 ARM64 CPU架構下安裝部署Docker + rancher + K8S 說明文檔

保姆級 ARM64 CPU架構下安裝部署Docker + rancher + K8S 說明文檔

1 K8S 簡介 K8S是Kubernetes的簡稱&#xff0c;是一個開源的容器編排平臺&#xff0c;用于自動部署、擴展和管理“容器化&#xff08;containerized&#xff09;應用程序”的系統。它可以跨多個主機聚集在一起&#xff0c;控制和自動化應用的部署與更新。 K8S 架構 Kubernete…
閱讀更多...
從Redis反序列化UserDetails對象異常后中發現FastJson序列化的一些問題

從Redis反序列化UserDetails對象異常后中發現FastJson序列化的一些問題

最近在使用SpringSecurityJWT實現認證授權的時候&#xff0c;出現Redis在反序列化userDetails的異常。通過實踐發現&#xff0c;使用不同的序列化方法和不同的fastJson版本&#xff0c;異常信息各不相同。所以特地記錄了下來。 一、項目代碼 先來看看我項目中redis相關配置信息…
閱讀更多...
視頻號小店常見問題分享，讓你少走彎路，少花冤枉錢！

視頻號小店常見問題分享，讓你少走彎路，少花冤枉錢！

我是電商珠珠 視頻號團隊自22年7月&#xff0c;就開始發展起了自己的電商平臺-視頻號小店。 關于視頻號小店有很多人可能還不太了解&#xff0c;尤其是對于新手來說&#xff0c;并不知道是干什么的。 我踏足電商這個領域也已經五六年了&#xff0c;視頻號小店也做了一年多了…
閱讀更多...
SpringBoot集成MapStruct

SpringBoot集成MapStruct

引入mapstruct依賴 <dependency><groupId>org.mapstruct</groupId><artifactId>mapstruct</artifactId><version>${org.mapstruct.version}</version> </dependency>配置maven-compiler-plugin <build><plugins>&…
閱讀更多...
VMware Workstation 17 虛擬機自啟動失效 解決腳本

VMware Workstation 17 虛擬機自啟動失效 解決腳本

VMware Workstation17新增加了虛擬機自啟配置 但是很奇怪在我的一臺計算機上能夠自啟&#xff0c;在另一臺計算機上就失效 編寫腳本 以命令方式完成虛擬機開機自啟 #虛擬機自啟.batif "%1""hide" goto CmdBegin start mshta vbscript:createobject("w…
閱讀更多...
緩存組件狀態，提升用戶體驗：探索 keep-alive 的神奇世界

緩存組件狀態，提升用戶體驗：探索 keep-alive 的神奇世界

&#x1f90d; 前端開發工程師&#xff08;主業&#xff09;、技術博主&#xff08;副業&#xff09;、已過CET6 &#x1f368; 阿珊和她的貓_CSDN個人主頁 &#x1f560; 牛客高級專題作者、在牛客打造高質量專欄《前端面試必備》 &#x1f35a; 藍橋云課簽約作者、已在藍橋云…
閱讀更多...
Day31| Leetcode 455. 分發餅干 Leetcode 376. 擺動序列 Leetcode 53. 最大子數組和

Day31| Leetcode 455. 分發餅干 Leetcode 376. 擺動序列 Leetcode 53. 最大子數組和

進入貪心了&#xff0c;我覺得本專題是最燒腦的專題 Leetcode 455. 分發餅干 題目鏈接 455 分發餅干 讓大的餅干去滿足需求量大的孩子即是本題的思路&#xff1a; class Solution { public:int findContentChildren(vector<int>& g, vector<int>& s) {…
閱讀更多...
仿ChatGPT對話前端頁面（內含源碼）

仿ChatGPT對話前端頁面（內含源碼）

仿ChatGPT對話前端頁面&#xff08;內含源碼&#xff09; 前言布局樣式和Js部分關鍵點全部源碼 前言 本文主要講解如何做出類似ChatGPT的前端頁面。具體我們的效果圖是長這樣&#xff0c;其中除了時間是動態的之外&#xff0c;其他都是假數據。接下來讓我們從布局和樣式的角度…
閱讀更多...
Android Tombstone 與Debuggerd 原理淺談

Android Tombstone 與Debuggerd 原理淺談

一、前言 Android系統類問題主要有stability、performance、power、security。Android集成一個守護進程tombstoned是android平臺的一個守護進程&#xff0c;它注冊成3個socket服務端&#xff0c;客戶端封裝在crash_dump和debuggerd_client。 crash_dump用于跟蹤定位C crash&am…
閱讀更多...
前端入門（三）Vue生命周期、組件技術、事件總線、

前端入門（三）Vue生命周期、組件技術、事件總線、

文章目錄 Vue生命周期Vue 組件化編程 - .vue文件非單文件組件組件的注意點組件嵌套Vue實例對象和VueComponent實例對象Js對象原型與原型鏈Vue與VueComponent的重要內置關系 應用單文件組件構建 Vue腳手架 - vue.cli項目文件結構refpropsmixin插件scoped樣式 Vue生命周期 1、bef…
閱讀更多...
MBA-論證有效性分析

MBA-論證有效性分析

論證有效性分析∶分析下述論證中存在的缺陷和漏洞&#xff0c;選擇若干要點&#xff0c;寫一篇 600 字左石的文章.對該論證的有效性進行分析和評論。&#xff08;論證有效性分析的一般要點是∶概念特別是核心概念的界定和使用是否準確并前后一致&#xff0c;有無各種明顯的邏輯…
閱讀更多...
最新文章

Copyright @ 2022~2023