A Brief Analysis of PostgreSQL TRUNCATE

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

-- Cannot truncate only a partitioned parent table
=> 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 the inheritance parent table, only the parent is cleaned
=> truncate table only parenttable;
TRUNCATE TABLE
=> select tableoid::regclass,count(*) from parenttable group by tableoid::regclass ;
  tableoid  | count
------------+-------
 childtable |     1

-- Directly truncate the inheritance parent table, child tables are also cleaned
=>  truncate table parenttable;
TRUNCATE TABLE
=> select tableoid::regclass,count(*) from parenttable group by tableoid::regclass ;
 tableoid | count
----------+-------
(0 rows)

-- bigserial creates a column sequence by default
=> create table tableserial (a  bigserial not null,b name);
CREATE TABLE
=> \d+ tableserial;
                                               Table "public.tableserial"
 Column |  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 current value is 1000
=> select currval('tableserial_a_seq'::regclass);
 currval
---------
    1000

-- Direct truncate does not reset sequences by default
=>  truncate table tableserial;
TRUNCATE TABLE
=> select currval('tableserial_a_seq'::regclass) cur,nextval('tableserial_a_seq'::regclass);
 cur  | nextval
------+---------
 1000 |    1001

-- Explicitly specify RESTART IDENTITY to reset sequences
=>  truncate table tableserial RESTART IDENTITY;
TRUNCATE TABLE
-- Note: seq is reset on nextval
=>  select currval('tableserial_a_seq'::regclass) cur,nextval('tableserial_a_seq'::regclass);
 cur  | nextval
------+---------
 1001 |       1

-- Create primary table, foreign key table, and data
=>  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 | abc
  2 | abc
  3 | abc
(3 rows)

-- Foreign key table frn_tab depends on pri_tab's data
=> select * from frn_tab;
 id
----
  1
  2
(2 rows)

-- With foreign key references on the primary table, CASCADE is required on the foreign key table, otherwise truncate fails
=> 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

-- Clear foreign key constrained tables together
=> truncate table pri_tab cascade;
NOTICE:  00000: truncate cascades to table "frn_tab"
LOCATION:  ExecuteTruncateGuts, tablecmds.c:1725
TRUNCATE TABLE
=> select * from pri_tab;
 id | name
----+------
(0 rows)

=>  select * from frn_tab;
 id
----
(0 rows)

=> begin;
BEGIN
=> truncate t1;
TRUNCATE TABLE
=> rollback;
ROLLBACK
=> select count(*) from t1;
 count
-------
   100

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 path
=> select oid from  pg_database where datname='lzldb';
  oid
--------
 418679

-- When first created, each rel's oid = relfilenode
=> select relname,oid,relfilenode,relkind from pg_class where  relname like 'lzl%';
 relname |  oid   | relfilenode | relkind
---------+--------+-------------+---------
 lzl     | 428363 |      428363 | r
 lzl_idx | 428366 |      428366 | i
 lzl_seq | 428367 |      428367 | S
(3 rows)

=> truncate table lzl;
TRUNCATE TABLE
=> select relname,oid,relfilenode,relkind from pg_class where  relname like 'lzl%';
 relname |  oid   | relfilenode | relkind
---------+--------+-------------+---------
 lzl     | 428363 |      428370 | r
 lzl_idx | 428366 |      428371 | i
 lzl_seq | 428367 |      428367 | S
-- After truncate, table and index were rebuilt, but sequence was not

M=> truncate table lzl RESTART IDENTITY;
TRUNCATE TABLE
=> select relname,oid,relfilenode,relkind from pg_class where  relname like 'lzl%';
 relname |  oid   | relfilenode | relkind
---------+--------+-------------+---------
 lzl     | 428363 |      428372 | r
 lzl_idx | 428366 |      428373 | i
 lzl_seq | 428367 |      428367 | S
-- Even with explicit RESTART, sequence was still not rebuilt

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 | r
 lzl_idx | 428366 |      428373 | i
 lzl_seq | 428367 |      428374 | S
-- Explicitly restarting the sequence DOES rebuild it

 \d+ lzl;
                                              Table "public.lzl"
 Column |  Type   | Collation | Nullable |           Default            | Storage | Stats target | Description
--------+---------+-----------+----------+------------------------------+---------+--------------+-------------
 a      | integer |           |          | nextval('lzl_seq'::regclass) | plain   |              |

=> ALTER SEQUENCE lzl_seq OWNED BY lzl.a;
ALTER SEQUENCE

-- Check sequence owner information
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.objid=s.oid AND d.classid='pg_class'::regclass AND d.refclassid='pg_class'::regclass
JOIN pg_class t ON t.oid=d.refobjid
JOIN pg_namespace n ON n.oid=t.relnamespace
JOIN pg_attribute a ON a.attrelid=t.oid AND a.attnum=d.refobjsubid
WHERE s.relkind='S' AND d.deptype='a';
        seq        |  sch   |     tab     | col
-------------------+--------+-------------+-----
 tableserial_a_seq | public | tableserial | a
 lzl_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 | r
 lzl_idx | 428366 |      428376 | i
 lzl_seq | 428367 |      428377 | S

void
ExecuteTruncate(TruncateStmt *stmt)
{
...

/*
* Open, exclusive-lock, and check all the explicitly-specified relations
*/
foreach(cell, stmt->relations)
{
...
LOCKMODE lockmode = AccessExclusiveLock;  // Level 8 lock
...

rel = table_open(myrelid, NoLock); // Open table

void
ExecuteTruncate(TruncateStmt *stmt)
{
...
	foreach(cell, stmt->relations)
	{
...
		LOCKMODE	lockmode = AccessExclusiveLock; // Level 8 lock
...
		/* open the relation, we already hold a lock on it */
		rel = table_open(myrelid, NoLock); // Open table
...
		truncate_check_activity(rel);  // Even with the lock, verify it's not in use
...
		if (recurse) // Recursive execution
		{
...
			children = find_all_inheritors(myrelid, lockmode, NULL); // Find all inheritance children

			foreach(child, children)
			{
...
				 // Above only checked the parent table, recursion checks children
				truncate_check_rel(RelationGetRelid(rel), rel->rd_rel);
				truncate_check_activity(rel);

				rels = lappend(rels, rel);  // Add to the list of rels to truncate
				relids = lappend_oid(relids, childrelid);
...
			}
		}
		// Recursion ends
		// truncate only on partitioned parent table? error directly
		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.")));
	}

	// Main function
	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);
	}
}

void
ExecuteTruncateGuts(List *explicit_rels,
					List *relids,
					List *relids_logged,
					DropBehavior behavior, bool restart_seqs)
{
...
	rels = list_copy(explicit_rels);
	if (behavior == DROP_CASCADE)  // If CASCADE option specified, extract all referencing relations
	{
		for (;;)
		{
...
			newrelids = heap_truncate_find_FKs(relids); // Find FKs
			if (newrelids == NIL)
				break;			/* nothing else to add */ // No rels, exit directly

			foreach(cell, newrelids)
			{
...
				rel = table_open(relid, AccessExclusiveLock); // All rels acquire AccessExclusiveLock
				ereport(NOTICE,
						(errmsg("truncate cascades to table \"%s\"",
								RelationGetRelationName(rel))));
				truncate_check_rel(relid, rel->rd_rel);  // Check if it's a truncatable object — must be a data-storing table
				truncate_check_perms(relid, rel->rd_rel);  // Check permissions
				truncate_check_activity(rel);  // Check if in use
...
			}
		}
	}

...
	if (restart_seqs) // Handle restart seq
	{
		foreach(cell, rels)
		{
			Relation	rel = (Relation) lfirst(cell);
			List	   *seqlist = getOwnedSequences(RelationGetRelid(rel));
...
			// Only check sequence permissions
				if (!pg_class_ownercheck(seq_relid, GetUserId()))
					aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_SEQUENCE,
								   RelationGetRelationName(seq_rel));
...
		}
	}

...

	// Execute all BEFORE TRUNCATE triggers
	foreach(cell, rels)
	{
		ExecBSTruncateTriggers(estate, resultRelInfo);
		resultRelInfo++;
	}

// Begin the actual truncate
	foreach(cell, rels)
	{
...
		// If it's a partitioned parent table, do nothing
		if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
			continue;

		// Handle foreign tables
		if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
		{
		...
		}

...
		 // If same transaction (may rollback), directly execute heap_truncate_one_rel without creating new relfilenode
		if (rel->rd_createSubid == mySubid ||
			rel->rd_newRelfilenodeSubid == mySubid)
		{
			/* Immediate, non-rollbackable truncation is OK */
			heap_truncate_one_rel(rel);
		}
		else
		{
...
			// Set NewRelfilenode
			RelationSetNewRelfilenode(rel, rel->rd_rel->relpersistence);

			heap_relid = RelationGetRelid(rel);

			 // Same for 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);
			}

...
			 // Rebuild indexes
			reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST,
							 &reindex_params);
		}

		pgstat_count_truncate(rel); // Update pgstat truncate count
	}

...
	// Reset sequences
	foreach(cell, seq_relids)
	{
		Oid			seq_relid = lfirst_oid(cell);

		ResetSequence(seq_relid);
	}

	// Write WAL
	if (list_length(relids_logged) > 0)
	{
	...
	}

	// Fire AFTER TRUNCATE triggers
	resultRelInfo = resultRelInfos;
	foreach(cell, rels)
	{
		ExecASTruncateTriggers(estate, resultRelInfo);
		resultRelInfo++;
	}
...
}