Introduction

This article explores the internal implementation of the ALTER TABLE command in PostgreSQL, focusing on the ADD COLUMN subcommand. The analysis is based on PostgreSQL commit hash 21e3a8bc3544a1cfcff85933bc9c0664af32a8b8.

Usage Examples

Below are common usages of the ALTER TABLE command:

ALTER TABLE myt ADD col1 INT;
ALTER TABLE myt ALTER col2 TYPE TEXT;
ALTER TABLE myt ADD CONSTRAINT col2_min_length CHECK (length(col2) > 6);
ALTER TABLE myt SET UNLOGGED;

Syntax Overview

The ALTER TABLE statement is represented internally by the AlterTableStmt structure. This structure contains one or more AlterTableCmd entries, each representing a specific operation such as ADD, DROP, RENAME, or SET.

Execution Flow

The execution path of the ALTER TABLE command follows this sequence:

exec_simple_query
PortalRun
ProcessUtility
ProcessUtilitySlow
AlterTable
ATController
ATRewriteCatalogs
ATExecCmd
ATExecAddColumn
ATParseTransformCmd
transformAlterTableStmt

Before the AlterTable function is called from ProcessUtilitySlow, the table being altered is locked using the AlterTableGetLockLevel function.

Key Functions

transformAlterTableStmt

This function trensforms the original AlterTableCmd commands into a new list of commands. Here's a breakdown of the process:

• Opens the target table without acquiring a lock.
• Sets up the parser state (pstate) and the CreateStmtContext.
• Processes column deifnitions via transformColumnDefinition and transformColumnType.
• Handles constraints and creates additional AlterTableCmd entries as needed (e.g., AT_AddConstraint for check constraints).
• Closes the table and updates the AlterTableStmt with the new command list.

ATExecAddColumn

This function handles the actual addition of a new column. Key steps include:

• Checking the call stack depth to prevent recursion-related stack overflow.
• Verifying permissions if not already done by ATPrepCmd.
• Opening the pg_attribute table with a RowExclusiveLock.
• Ensuring the new column name does not conflict with existing columns.
• Calling ATParseTransformCmd to parse and transform the column definition.
• Updating the pg_attribute catalog via InsertPgAttributeTuples.
• Updating the pg_class catalog with CatalogTupleUpdate.
• Running post-creation hooks for the new attribute.
• Making the new catalog entry visible using CommandCounterIncrement.
• Storing any default value for the column in the catalog.
• Adding dependency entries for the new column.
• If the table has inherited children (via pg_class.relhassubclass), recursively invoking ATExecAddColumn.
• Returning the ObjectAddress for the newly added column.