Overview

In this article, I will inspect the postgresql code to find out the implementation of alter table command, specifically, the add column subcommand of the alter table command. The code for this article is from postgresql commit hash 21e3a8bc3544a1cfcff85933bc9c0664af32a8b8.

usage

alter table myt add col1 int;
alter table myt alter col2 type text;
alter table myt add constraint col2_min_length CHECK (lenght(col2) > 6);
alter table myt set unlogged;

syntax

![[Pasted image 20240323143948.png]]

![[Pasted image 20240323144141.png]]

Each alter table command has a AlterTableStmt data structure. An AlterTableStmt includes one or more AlterTableCmd commands. subcommands are add, drop, rename, set, etc.

execution

code path

exec_simple_query
PortalRun
ProcessUtility
ProcessUtilitySlow
AlterTable
ATController
ATRewriteCatalogs
ATExecCmd
ATExecAddColumn
ATParseTransformCmd
transformAlterTableStmt

Inside function ProcessUtilitySlow, before function AlterTable, a function AlterTableGetLockLevel is called to lock the being-altered table.

transformAlterTableStmt

transformAlterTableStmt transform original AlterTableCmd commands into more new AlterTableCmd commands.

• open the being-altered table without any lock
• set up pstate
• set up CreateStmtContext
• for sql alter table add col1 int, it needs invoke transformColumnDefinition which invokes transformColumnType. For this simple sql, transformColumnType only verifies the existence of the column type.
• Postprocess constraints, include index constraints, foreign key constraints, check constraints
• if any constraint exists, create a new AlterTableCmd with type AT_AddConstraint.
• close the table
• record the new list of AlterTableCmd commands in AlterTableStmt

ATExecAddColumn

• since this function recurses, it could be driven to stack overflow, hence check_stack_depth now.
• At top level, permission check was done in ATPrepCmd, else do it
• open pg_attribute table with RowExclusiveLock
• check for column name collision
• do parse transformation through ATParseTransformCmd
• update information in pg_attribute table, through InsertPgAttributeTuples
• update information in pg_class table, through CatalogTupleUpdate
• run post creation hook for new attribute
• Make the attribute's catalog entry visible, through CommandCounterIncrement
• Store the DEFAULT, if any, in the catalogs
• Add needed dependency entries for the new column.
• find any inherited tables by inspecting pg_class.relhassubclass
• if found, then recursively invokes ATExecAddColumn
• returns the ObjectAddress which represents the newly added column