案例分析网站,网站建设免,上海今天新闻头条新闻,wix建设网站教程命令选项
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ][ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]1.ONLY:只truncate指定的表。当表有继承子表或有子分区时#xff0c;默认会一起truncate;only可只truncate继承父表。分区父表不能指定only
--不…命令选项
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ][ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]1.ONLY:只truncate指定的表。当表有继承子表或有子分区时默认会一起truncate;only可只truncate继承父表。分区父表不能指定only
--不能truncate only分区父表truncate only parttable;
ERROR: 42809: cannot truncate only a partitioned table
HINT: Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
LOCATION: ExecuteTruncate, tablecmds.c:1655--truncate only继承父表只清理父表truncate table only parenttable;
TRUNCATE TABLEselect tableoid::regclass,count(*) from parenttable group by tableoid::regclass ;tableoid | count
-------------------childtable | 1--直接truncate继承父表子表也会被清理truncate table parenttable;
TRUNCATE TABLEselect tableoid::regclass,count(*) from parenttable group by tableoid::regclass ;tableoid | count
-----------------
(0 rows)2.RESTART IDENTITY CONTINUE IDENTITY:列上的序列是否要重置默认CONTINUE。
--bigserial 默认会创建列上的序列create table tableserial (a bigserial not null,b name);
CREATE TABLE\d tableserial;Table public.tableserialColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------------------------------------------------------------------------------------------a | bigint | | not null | nextval(tableserial_a_seq::regclass) | plain | | b | name | | | | plain | | insert into tableserial(b) select md5(random()::text) from generate_series(1,1000);
INSERT 0 1000
--seq当前值为1000select currval(tableserial_a_seq::regclass);currval
---------1000--直接truncate默认不会重置序列truncate table tableserial;
TRUNCATE TABLEselect currval(tableserial_a_seq::regclass) cur,nextval(tableserial_a_seq::regclass);cur | nextval
---------------1000 | 1001--显示指定RESTART IDENTITY重置序列truncate table tableserial RESTART IDENTITY;
TRUNCATE TABLE
--注意seq在nextval时重置了select currval(tableserial_a_seq::regclass) cur,nextval(tableserial_a_seq::regclass);cur | nextval
---------------1001 | 13.CASCADE清理表及其所有外键表的数据
--创建主表和外键表和数据create table pri_tab(id bigint primary key,name varchar(10));
CREATE TABLE insert into pri_tab values (1,abc),(2,abc),(3,abc);
INSERT 0 3 create table frn_tab(id bigint,FOREIGN KEY (id) REFERENCES pri_tab(id));
CREATE TABLE insert into frn_tab values (1),(2);
INSERT 0 2 select * from pri_tab;id | name
----------1 | abc2 | abc3 | abc
(3 rows)--外键表frn_tab依赖主表pri_tab的数据select * from frn_tab;id
----12
(2 rows)--有主表外键reference时外键表必须跟cascade否则无法清理truncate table pri_tab ;
ERROR: 0A000: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table frn_tab references pri_tab.
HINT: Truncate table frn_tab at the same time, or use TRUNCATE ... CASCADE.
LOCATION: heap_truncate_check_FKs, heap.c:3427--外键约束的表一起清空truncate table pri_tab cascade;
NOTICE: 00000: truncate cascades to table frn_tab
LOCATION: ExecuteTruncateGuts, tablecmds.c:1725
TRUNCATE TABLEselect * from pri_tab;id | name
----------
(0 rows) select * from frn_tab;id
----
(0 rows)
由于外键表依赖主表的数据不能直接truncate主表必须加cascade此时外键表也跟随主表一起清空
4.RESTRICT 是否清理foreign key表。没什么用default选项加不加都是这样。清理附带的外键表应加CASCADE。
MVCC/transaction
pg官方文档有这么一段化 TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred. TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit. transaction-safe意思是可以放在事务块里可以回退 回滚truncate begin;
BEGINtruncate t1;
TRUNCATE TABLErollback;
ROLLBACKselect count(*) from t1;count
-------100not MVCC-safe意思是一个会话在truncate前打了一个快照快照期间如果发生truncate这个快照是可以读到truncate清理后的结果的。这不符合MVCC。 不过这个问题不算太大在会话场景下因为truncate是8级锁快照没有结束的话最低在表上有一个读共享锁所以truncate不会执行。 This will only be an issue for a transaction that did not access the table in question before the DDL command started —any transaction that has done so would hold at least an ACCESS SHARE table lock, which would block the DDL command until that transaction completes 功能更新 truncate更新功能不多只需要注意14的时候支持truncate foreign tables即可。truncate foreign tables前提是fdw得支持TRUNCATE API Also it extends postgres_fdw so that it can issue TRUNCATE command to foreign servers, by adding new routine for that TRUNCATE API. pg truncate和其他库的功能差异 truncate很快、8级锁等特性已经是人尽皆知的事情了相对于其他数据库pg还可以选择是否重置序列RESTART IDENTITY CONTINUE IDENTITY、回滚、简单的授权。
truncate做了什么
create table lzl(a int);
create index lzl_idx on lzl(a);
create sequence lzl_seq start with 1;
alter table lzl alter column a set default nextval(lzl_seq);
--select pg_relation_filepath(lzl);--db路径select oid from pg_database where datnamelzldb;oid
--------418679--刚创建时候各个rel的oidrelfilenodeselect relname,oid,relfilenode,relkind from pg_class where relname like lzl%;relname | oid | relfilenode | relkind
---------------------------------------lzl | 428363 | 428363 | rlzl_idx | 428366 | 428366 | ilzl_seq | 428367 | 428367 | S
(3 rows) truncate table lzl;
TRUNCATE TABLEselect relname,oid,relfilenode,relkind from pg_class where relname like lzl%;relname | oid | relfilenode | relkind
---------------------------------------lzl | 428363 | 428370 | rlzl_idx | 428366 | 428371 | ilzl_seq | 428367 | 428367 | S
--truncate后表和索引重建了sequence却没有M truncate table lzl RESTART IDENTITY;
TRUNCATE TABLEselect relname,oid,relfilenode,relkind from pg_class where relname like lzl%;relname | oid | relfilenode | relkind
---------------------------------------lzl | 428363 | 428372 | rlzl_idx | 428366 | 428373 | ilzl_seq | 428367 | 428367 | S
--显示restartsequence还是没有重建M alter sequence lzl_seq restart;
ALTER SEQUENCE
M select relname,oid,relfilenode,relkind from pg_class where relname like lzl%;relname | oid | relfilenode | relkind
---------------------------------------lzl | 428363 | 428372 | rlzl_idx | 428366 | 428373 | ilzl_seq | 428367 | 428374 | S
--显示restart sequence是会重建sequence的truncate ···RESTART IDENTITY没有重建我们sequencealter sequence lzl_seq restart重建了sequence。应该是RESTART IDENTITY没有理解对。在看下官方文档对RESTART IDENTITY的解释 Automatically restart sequences owned by columns of the truncated table(s). sequence必须owned by表上的列注意不是owner to。虽然\d可以看到表上的sequence但是它可能不属于表 \d lzl;Table public.lzlColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------------------------a | integer | | | nextval(lzl_seq::regclass) | plain | | 用owned by修改sequence的所属表 ALTER SEQUENCE lzl_seq OWNED BY lzl.a;
ALTER SEQUENCE--查看序列的所有者信息
SELECT s.relname AS seq, n.nspname AS sch, t.relname AS tab, a.attname AS col
FROM pg_class s
JOIN pg_depend d ON d.objids.oid AND d.classidpg_class::regclass AND d.refclassidpg_class::regclass
JOIN pg_class t ON t.oidd.refobjid
JOIN pg_namespace n ON n.oidt.relnamespace
JOIN pg_attribute a ON a.attrelidt.oid AND a.attnumd.refobjsubid
WHERE s.relkindS AND d.deptypea;seq | sch | tab | col
---------------------------------------------tableserial_a_seq | public | tableserial | alzl_seq | public | lzl | a truncate table lzl RESTART IDENTITY;
TRUNCATE TABLE
M select relname,oid,relfilenode,relkind from pg_class where relname like lzl%;relname | oid | relfilenode | relkind
---------------------------------------lzl | 428363 | 428375 | rlzl_idx | 428366 | 428376 | ilzl_seq | 428367 | 428377 | Ssequence owned by表上的列时truncate显示带RESTART IDENTITY就会restart这个sequence也就重建了sequence。默认以serial/bigserial方式创建的序列是被表拥有的随表drop而删除那些不被表拥有的序列drop不会删除。 truncate重建特性汇总
直接truncate table会重建表和索引truncate tableRESTART IDENTITY会重建也就是retart属于这个表的sequence。只要不属于这个表的sequence哪怕列上关联了seq的默认值也不会重建这个seq。
源码分析
truncate也是utility命令很快就可以找到入口函数 src/backend/commands/tablecmds.c中的ExecuteTruncate为入口函数注释其实已经说明truncate要获得exclusive lock并检查权限和relation是否ok递归检查所有需要truncate的表
voidExecuteTruncate(TruncateStmt *stmt){.../** Open, exclusive-lock, and check all the explicitly-specified relations*/foreach(cell, stmt-relations){...
LOCKMODE lockmode AccessExclusiveLock; //8级锁
...rel table_open(myrelid, NoLock); //打开表void
ExecuteTruncate(TruncateStmt *stmt)
{
...foreach(cell, stmt-relations){
...LOCKMODE lockmode AccessExclusiveLock; //8级锁
.../* open the relation, we already hold a lock on it */rel table_open(myrelid, NoLock); //打开表
...truncate_check_activity(rel); //虽然已经有锁了但是还是要验证是否在使用
...if (recurse) //递归执行{
...children find_all_inheritors(myrelid, lockmode, NULL); //找到所有继承子表foreach(child, children){
...//上面只检查了父表递归要检查子表truncate_check_rel(RelationGetRelid(rel), rel-rd_rel);truncate_check_activity(rel);rels lappend(rels, rel); //加入到待truncate的rel队列中relids lappend_oid(relids, childrelid);
...}}//递归结束//发现truncate only分区父表直接报错else if (rel-rd_rel-relkind RELKIND_PARTITIONED_TABLE)ereport(ERROR,(errcode(ERRCODE_WRONG_OBJECT_TYPE),errmsg(cannot truncate only a partitioned table),errhint(Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.)));}//主体函数 ExecuteTruncateGuts(rels, relids, relids_logged,stmt-behavior, stmt-restart_seqs);/* And close the rels */foreach(cell, rels){Relation rel (Relation) lfirst(cell);table_close(rel, NoLock);}
}ExecuteTruncateGuts函数不仅被truncate命令调用还被订阅端调用发布订阅可以同步truncate。
void
ExecuteTruncateGuts(List *explicit_rels,List *relids,List *relids_logged,DropBehavior behavior, bool restart_seqs)
{
...rels list_copy(explicit_rels);if (behavior DROP_CASCADE) //如果指定了cascade选项,提取所有reference的relation{for (;;){
...newrelids heap_truncate_find_FKs(relids); //找到fkif (newrelids NIL)break; /* nothing else to add */ //没有rel直接退出foreach(cell, newrelids){
...rel table_open(relid, AccessExclusiveLock); //所有rel获得AccessExclusiveLockereport(NOTICE,(errmsg(truncate cascades to table \%s\,RelationGetRelationName(rel))));truncate_check_rel(relid, rel-rd_rel); //检查是否是可以truncate的对象得是存储数据的表truncate_check_perms(relid, rel-rd_rel); //检查是否有权限truncate_check_activity(rel); //检查是否在使用
...}}}...if (restart_seqs) //restart seq的处理{foreach(cell, rels){Relation rel (Relation) lfirst(cell);List *seqlist getOwnedSequences(RelationGetRelid(rel));
...//只是做sequence的权限检查if (!pg_class_ownercheck(seq_relid, GetUserId()))aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_SEQUENCE,RelationGetRelationName(seq_rel));
...}}...//执行所有before truncate触发器foreach(cell, rels){ExecBSTruncateTriggers(estate, resultRelInfo);resultRelInfo;}//正式开始truncateforeach(cell, rels){
...//如果是分区父表啥都不做if (rel-rd_rel-relkind RELKIND_PARTITIONED_TABLE)continue;//如果是foreign table的处理if (rel-rd_rel-relkind RELKIND_FOREIGN_TABLE){...}...//如果是同一事务因为可能会回退直接执行heap_truncate_one_rel函数不创建新的relfilenodeif (rel-rd_createSubid mySubid ||rel-rd_newRelfilenodeSubid mySubid){/* Immediate, non-rollbackable truncation is OK */heap_truncate_one_rel(rel);}else{
...//设置NewRelfilenodeRelationSetNewRelfilenode(rel, rel-rd_rel-relpersistence);heap_relid RelationGetRelid(rel);//toast同理toast_relid rel-rd_rel-reltoastrelid;if (OidIsValid(toast_relid)){Relation toastrel relation_open(toast_relid,AccessExclusiveLock);RelationSetNewRelfilenode(toastrel,toastrel-rd_rel-relpersistence);table_close(toastrel, NoLock);}...//重建索引reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST,reindex_params);}pgstat_count_truncate(rel); //更新pgstat的truncate计算}...//重置sequence foreach(cell, seq_relids){Oid seq_relid lfirst_oid(cell);ResetSequence(seq_relid);}//写walif (list_length(relids_logged) 0){...}//触发AFTER TRUNCATE triggersresultRelInfo resultRelInfos;foreach(cell, rels){ExecASTruncateTriggers(estate, resultRelInfo);resultRelInfo;}
...
} ExecuteTruncateGuts函数根据truncate选项进行处理处理过程如下
根据cascade选项找到所有reference的外键表触发before truncate触发器执行truncate
如果是同一事务不立即生成NewRelfilenode直接调用函数heap_truncate_one_rel进行truncate如果不是同一事务调用RelationSetNewRelfilenode新建NewRelfilenode
reindex_relation函数重建索引根据restart identity重置sequence写wal日志触发after truncate触发器
后面大概追了下函数套娃比较多 RelationSetNewRelfilenode table_relation_set_new_filenode relation_set_new_filenode在这里插入代码片 heapam_relation_set_new_filenode RelationCreateStorage 然后到src/backend/storage/smgr/smgr.c中的smgrcreate和smgr_create。后面就没看太懂了一到函数指针就有点追不到的感觉先这样吧~··· 对于smgr.c有这样的注释 public interface routines to storage manager switch All file system operations in POSTGRES dispatch through these routines. 任何文件系统操作都会经过smgrstorage manager到这里就是文件系统操作了。
reference
https://www.postgresql.org/docs/15/sql-truncate.html https://www.postgresql.org/docs/current/mvcc-caveats.html https://pgpedia.info/t/truncate.html https://www.orafaq.com/wiki/SQL_FAQ https://learnsql.com/blog/difference-between-truncate-delete-and-drop-table-in-sql/
