Destructive Operations
ALTER TABLE ... SET LOGGED / SET UNLOGGED
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
set-logged-unlogged ALTER TABLE session_cache SET UNLOGGED;What this does
Rewrites the entire table's storage to switch WAL-logging behavior, holding ACCESS EXCLUSIVE for the full duration. There is no non-blocking alternative in core Postgres.
Safe rewrite
No non-blocking alternative exists. Minimize impact with a bounded lock_timeout and retry
SET lock_timeout = '2s';
ALTER TABLE session_cache SET UNLOGGED;
-- Retry in a loop if lock_timeout expires.