wrkm.app
Search
Databases

Mats Kindahl: Building a Table Access Method, Part 9: UNLOGGED tables and surviving a restart

Everything that pg_memtable stores lives in shared memory, and a restart of the server destroys it, but the handle is left in the catalog. Since nothing special is done to inform Postgres that tables that use the memtable table access method (MemTables) do not survive a restart, it will treat the table as permanent, with “interesting” consequences. In this post you’ll learn about table persistence, how unlogged tables deal with a server restart, how to make every MemTable unlogged so t

Editor
Nov 22, 2026 · 20 min read
Mats Kindahl: Building a Table Access Method, Part 9: UNLOGGED tables and surviving a restart

Everything that pg_memtable stores lives in shared memory, and a restart of the server destroys it, but the handle is left in the catalog. Since nothing special is done to inform Postgres that tables that use the memtable table access method (MemTables) do not survive a restart, it will treat the table as permanent, with “interesting” consequences. In this post you’ll learn about table persistence, how unlogged tables deal with a server restart, how to make every MemTable unlogged so that Postgres handles MemTables correctly, and how to recreate the arena of a table after a restart.

In Part 8 you could see how a backend lets go of an arena that another backend has dropped or truncated. All of that assumed that the server keeps running, but servers can crash or restart while tables are being updated. When that happens, Postgres might run recovery when the server starts again, and it has to deal with tables that were only partly written to disk. Recovery is not needed when the server does a controlled restart, but a MemTable loses its arena in both cases, so the table record in pg_class, which survives a restart, points to an arena that no longer exists. This post looks at what recovery does, how it is handled for table access methods, and what is necessary to make a MemTable behave in a reasonable manner after a restart. Before discussing recovery, you need to look at how storage is managed for table access methods, including heap tables, and at what the persistence of a table means. A digression into the storage for a table Relations that have storage in Postgres, such as tables and indexes, have multiple forks that contain information about the data in the table. There are up to four forks for each table, which are all used by the heap table to store information:

The main fork contains the data of the relation, which typically are the pages with the rows of a table (or the pages of an index). The fsm fork contains the free space map, which keeps track of how much space there is in each page, so that an insert can find a page with room for the row. The vm fork contains the visibility map, which records pages with rows that are visible to every transaction. The init fork exists only for unlogged relations. It contains the relation as it looks when it is empty. It is used for recovery of unlogged tables, and you will see in a moment how it is used.

Each fork is a separate file with its own series of blocks. The forks are defined by the enumeration ForkNumber: typedef enum ForkNumber { InvalidForkNumber = -1, MAIN_FORKNUM = 0, FSM_FORKNUM, VISIBILITYMAP_FORKNUM, INIT_FORKNUM, } ForkNumber;

#define MAX_FORKNUM INIT_FORKNUM Each fork has a name, which you can find in the global variable forkNames. This is also used as the suffix of the file name for the fork, with the exception of the main fork, which has no suffix. const char const forkNames[] = { [MAIN_FORKNUM] = "main", [FSM_FORKNUM] = "fsm", [VISIBILITYMAP_FORKNUM] = "vm", [INIT_FORKNUM] = "init", }; In other words, the files of a relation with the number 16407 are 16407, 16407_fsm, 16407_vm and 16407_init. They are stored in the base/ directory under the data directory, so for a table in the database with the OID 5, the main fork is the file base/5/16407. The storage manager keeps the state of a relation in a structure with an array for each thing that it tracks per fork, indexed by the fork number. smgr_cached_nblocks is the last known size of each fork, and md_seg_fds the open files of each fork: typedef struct SMgrRelationData { RelFileLocatorBackend smgr_rlocator; / relation physical identifier / BlockNumber smgr_targblock; BlockNumber smgr_cached_nblocks[MAX_FORKNUM + 1]; / last known size */ ... int md_num_open_segs[MAX_FORKNUM + 1]; struct _MdfdVec *md_seg_fds[MAX_FORKNUM + 1]; ... } SMgrRelationData; You can fetch the structure of a relation from inside an extension using RelationGetSmgr, and use smgrexists to see if a fork exists. The size of each fork is available from SQL with pg_relation_size. Table persistence You now know how the tables are stored, but not all tables are created equal. Postgres has the notion of temporary tables, unlogged tables, and plain (permanent) tables. When a table is created, the persistence of the table is stored in the relpersistence column of pg_class: p for a permanent table, u for an unlogged table, and t for a temporary table. Postgres uses relpersistence in several different situations to decide what to do. For example, physical standbys, logical replication and pg_dump assume that permanent tables have their contents in the WAL. For unlogged tables, the assumption is that the contents are not logged and are lost after a crash. The rows of a MemTable are not written to the WAL, so nothing in the table survives a restart. For all practical purposes it is an unlogged table, and you should ensure that this is reflected in pg_class, or stange things will start to happen. Postgres hands the persistence of the table to the table access method as the persistence argument of relation_set_new_filelocator. The current version of the callback of pg_memtable has not used it, which means that a plain CREATE TABLE gets recorded as permanent: mats=# CREATE TABLE my_mem (id int) USING memtable; CREATE TABLE mats=# INSERT INTO my_mem VALUES (1), (2), (3); INSERT 0 3 mats=# SELECT relname, relpersistence FROM pg_class WHERE relname = 'my_mem'; relname | relpersistence ----------+---------------- my_table | p (1 row) You can see the difference between an unlogged heap table and a MemTable by first creating two tables and running VACUUM on the heap table and then running a query with pg_relation_size to extract the information about the forks and sizes. CREATE UNLOGGED TABLE my_heap (id int, v text); INSERT INTO my_heap SELECT g, 'v' || g FROM generate_series(1, 1000) g; VACUUM my_heap;

CREATE TABLE my_mem (id int, v text) USING memtable; INSERT INTO my_mem SELECT g, 'v' || g FROM generate_series(1, 1000) g;

Note: No fork other than main exists when a table is created. The heap table access method creates the others when it needs them. The free space map is created by inserts once the table has grown beyond its first page, and by VACUUM. The visibility map is created by VACUUM, which is why the example runs one.

Querying both tables shows the difference. The query also uses pg_relation_filepath to show where the file of each table is, or would be if it existed. mats=# SELECT t.table_name, mats-# c.relpersistence, mats-# pg_relation_filepath(c.oid) AS filepath, mats-# f.fork, mats-# pg_relation_size(c.oid, f.fork) AS bytes mats-# FROM unnest(array['my_heap', 'my_mem']) WITH ORDINALITY AS t(table_name, n) mats-# JOIN pg_class c ON c.oid = t.table_name::regclass mats-# CROSS JOIN unnest(array['main', 'fsm', 'vm', 'init']) WITH ORDINALITY AS f(fork, k) mats-# ORDER BY t.n, f.k; table_name | relpersistence | filepath | fork | bytes ------------+----------------+-------------------+------+------- my_heap | u | base/5/16394 | main | 49152 my_heap | u | base/5/16394 | fsm | 24576 my_heap | u | base/5/16394 | vm | 8192 my_heap | u | base/5/16394 | init | 0 my_mem | p | base/5/3784225416 | main | 0 my_mem | p | base/5/3784225416 | fsm | 0 my_mem | p | base/5/3784225416 | vm | 0 my_mem | p | base/5/3784225416 | init | 0 (8 rows) As you can see, the init fork of the heap table is an empty file, since an empty heap table is an empty file. In contrast, a my_mem is recorded as a permanent table (p) although it has no files and its rows are not logged. In addition, my_mem has no forks, which is why they are all zero. Its relfilenode is the handle of a DSM segment and not the number of a file, and the path that pg_relation_filepath reports for it does not exist. How Postgres handles unlogged tables When creating an unlogged table, the TAM callback relation_set_new_filelocator is called with a u in the persistence parameter to indicate that this is an unlogged table. It is then up to the table access method to create the init fork if one is needed, which the heap table access method does. The heap table access method does that by calling smgrcreate for INIT_FORKNUM and logging the creation of the new fork. This creates an empty relation for the init fork. If the server has to run recovery after a crash, ResetUnloggedRelations is called twice for each unlogged table. The first call is made before the log is replayed, and it removes all forks of the unlogged relations except the init fork. The second call is made when the replay is done, and it copies the init fork over the main fork of each unlogged relation. The table is then empty after a crash, and what was in it is gone. After a clean shutdown no recovery is needed, and the data of an unlogged table is kept. In contrast, a MemTable has no files and hence no init fork either. The memory of the arena is destroyed by every restart regardless of whether it was a clean shutdown or a crash, so the table will be empty after any restart. There is nothing for ResetUnloggedRelations to do, and as a consequence, recreating the arena is left to the table access method. However, the table persistence for MemTables is still p (permanent), which causes problems since Postgres expect these tables to be logged. Making memtable unlogged To both ensure that MemTables are created as unlogged tables and also prevent them from being altered to be logged tables, you need to deal with both CREATE TABLE and ALTER TABLE. In both cases, the ProcessUtility hook from Part 4 sees the statements before Postgres creates the table, so this is where we will add callbacks to deal with each case. First, the hook is extended with functions that run before and after standard_ProcessUtility. Each of them will be called before and after the main processing of the statemtements is done. static void memtable_ProcessUtility(PlannedStmt* pstmt, const char* queryString, bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment* queryEnv, DestReceiver* dest, QueryCompletion* qc) { switch (nodeTag(pstmt->utilityStmt)) { case T_CreateStmt: memtable_before_create_table(pstmt->utilityStmt, &readOnlyTree, &pstmt); break;

case T_AlterTableStmt:
  memtable_before_alter_table(pstmt->utilityStmt);
  break;

default:
  break;

}

if (prev_ProcessUtility) prev_ProcessUtility(pstmt, queryString, readOnlyTree, context, params, queryEnv, dest, qc); else standard_ProcessUtility(pstmt, queryString, readOnlyTree, context, params, queryEnv, dest, qc);

switch (nodeTag(pstmt->utilityStmt)) { case T_CreateStmt: memtable_after_create_table(pstmt->utilityStmt); break;

case T_TruncateStmt:
  memtable_after_truncate(pstmt->utilityStmt);
  break;

default:
  break;

} } With the ProcessUtility_hook extended to cover the CREATE TABLE and ALTER TABLE, the next step is to deal with the actual functionality. Creating an unlogged table When creating an unlogged table, you normally use the CREATE UNLOGGED TABLE command and then Postgres ensures that this is recorded correctly in pg_class. It is not reasonable that table access method ask the user to write CREATE UNLOGGED TABLE since the decision to not be a logged table is something that the table access method has made. It would be possible to reject creating the table if it is not defined as UNLOGGED, but this is not particularly user-friendly. Instead, the pg_class record created for the create table is updated with the persistence of the table set to unlogged before the main processing takes place. The parse tree of a statement can be shared with a cached plan (a plan that the server keeps and reuses, for example for a prepared statement or for a statement inside a function), so a hook must not change it when readOnlyTree is true. In that case the hook makes a copy of the parse tree instead and uses this while processing. static void memtable_before_create_table(Node* parsetree, bool* readOnlyTree_out, PlannedStmt** pstmt_out) { CreateStmt* cstmt = (CreateStmt*)parsetree; const char* am = memtable_get_access_method(cstmt->accessMethod);

Assert(am != NULL); /* default_table_access_method is never NULL */

if (strcmp(am, MEMTABLE_EXTNAME) == 0 && cstmt->relation->relpersistence == RELPERSISTENCE_PERMANENT) { if (*readOnlyTree_out) { *pstmt_out = copyObject(pstmt_out); readOnlyTree_out = false; cstmt = (CreateStmt)(pstmt_out)->utilityStmt; } cstmt->relation->relpersistence = RELPERSISTENCE_UNLOGGED; } } Since a MemTable can be created either with USING memtable or because default_table_access_method is set to memtable, the function memtable_get_access_method is used to get the access method. static const char memtable_get_access_method(const char accessMethod) { if (accessMethod == NULL) return default_table_access_method; return accessMethod; } After these modifications, you can see the result in the catalog: mats=# CREATE TABLE my_table (id int) USING memtable; CREATE TABLE mats=# \d+ my_table Unlogged table "public.my_table" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+---------+-----------+----------+---------+---------+-------------+--------------+------------- id | integer | | | | plain | | | Access method: memtable

mats=# SELECT relname, relpersistence FROM pg_class WHERE relname = 'my_table'; relname | relpersistence ----------+---------------- my_table | u (1 row)

mats=# CREATE TEMP TABLE my_temp (id int) USING memtable; CREATE TABLE mats=# SELECT relname, relpersistence FROM pg_class WHERE relname = 'my_temp'; relname | relpersistence ---------+---------------- my_temp | t (1 row) A temporary table is unlogged already, so the hook leaves it alone, and it works like any other MemTable. Its arena is released when the table is dropped at the end of the session, and I found no segments left behind after the session exits.

Note: A temporary table is a good use of a MemTable, since it needs no durability. With the current implementation a fixed size arena is reserved when the table is created, so a session with many temporary tables reserves a lot of shared memory.

Since the record for the table is now properly updated, Postgres treats the table as an unlogged table and also restricts what can be done with it. For example, unlogged tables cannot be added to a publication, and this is now honored with memtable as well: mats=# CREATE PUBLICATION my_pub FOR TABLE my_table; ERROR: cannot add relation "my_table" to publication DETAIL: This operation is not supported for unlogged tables. A standby cannot read an unlogged table, and pg_dump --no-unlogged-table-data leaves out the rows of unlogged tables and dumps only their definitions. That fixes CREATE TABLE for MemTables. Now it is time to move over to ALTER TABLE. Altering an unlogged table The command ALTER TABLE can be used to alter the definition of a table, which includes changing the persistence of a table. ALTER TABLE ... SET LOGGED rewrites an unlogged table as a permanent one, which will make Postgres treat an unlogged table as a logged table. The hook simply rejects this modification for a MemTable. It looks up the relation, checks that it uses memtable, and goes through the commands of the statement: static void memtable_before_alter_table(Node* parsetree) { AlterTableStmt* astmt = (AlterTableStmt*)parsetree; Oid relid = RangeVarGetRelid(astmt->relation, NoLock, true); bool uses_memtable = false; ListCell* lc;

if (!OidIsValid(relid)) return;

(void)memtable_catalog_lookup(relid, &uses_memtable);

if (!uses_memtable) return;

foreach (lc, astmt->cmds) { AlterTableCmd* cmd = lfirst_node(AlterTableCmd, lc);

if (cmd->subtype == AT_SetLogged)
  ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                  errmsg("memtable tables cannot be logged")));

} } mats=# ALTER TABLE my_table SET LOGGED; ERROR: memtable tables cannot be logged We have now dealt with ensuring that MemTables. Recovery and server restarts for unlogged tables When a server restart, it will start to run recovery for the tables, which is done in the StartupXLOG function. For logged tables, this means replaying the WAL to recover all committed transactions (something that will not be covered in this post), but for unlogged tables there is no WAL, so how is that handled? For an unlogged heap table, the function ResetUnloggedRelations is called from StartupXLOG with an “operation” parameter that can be either UNLOGGED_RELATION_CLEANUP or UNLOGGED_RELATION_INIT. These operations are used to create a clean table, and is done in two stages:

Before replying the WAL, it calls ResetUnloggedRelations with UNLOGGED_RELATION_CLEANUP, which will delete all forks except the init fork. After replaying the WAL, it calls ResetUnloggedRelations with UNLOGGED_RELATION_INIT, which will copy the init fork over to the main fork.

The crux here is that Postgres finds the tables to reset by inspecting the file system and looking for any files of the form _init in base// directory and handles the relation with that number. It does not read pg_class and does not know which table access method owns a relation.

To deal with restarts, there are two critical problem that needs to be handled:

  1. Since the arena goes away on a restart but the handle referencing the arena in relfilenode is not removed, it is necessary to ensure that it is not causing problems.
  2. A new empty arena need to be created when the table is used after the restart.

To figure out what happens with the the arena after a server restart, you can check for yourself (with the code before this post):

“sql mats=# CREATE TABLE my_restart (id int) USING memtable; CREATE TABLE “

“bash $ pg_ctl -D data restart “

“sql mats=# SELECT count(*) FROM my_restart; ERROR: could not attach to arena 1782764158 “

Since a restart of the server destroys all DSM segments but does not modify any of the columns for the relation in pg_class, the handle stored in the relfilenode field refers to a segment that does not exist any more. It is not possible to drop the table either to fix this issue since the handle refers to a segment that no longer exists:

“sql mats=# DROP TABLE my_restart; WARNING: AbortTransaction while in COMMIT state ERROR: cannot unpin unknown segment handle PANIC: cannot abort transaction 757, it was already committed server closed the connection unexpectedly “

What happens is that the DROP schedules the release of the arena for the commit callback, which unpins the segment, but unpinning a segment that does not exist causes an error, and an error in a commit callback results in a PANIC. TRUNCATE of such a table fails in the same way, since it releases the old arena that it replaces.

Fortunately, dsm_attach returns NULL for a handle that is unknown, it can be used to check if the arena actually exists given a handle:

“`c static bool memtable_segment_exists(dsm_handle handle) { dsm_segment* seg = dsm_find_mapping(handle);

if (seg != NULL) return true;

seg = dsm_attach(handle); if (seg == NULL) return false;

dsm_detach(seg); return true; } “`

Note that it is not sufficient to just call dsm_find_mapping since that just looks for a segment that is mapped: not if it exists at all. The second call of dsm_attach with the check if it returned NULL is what decides if the segment exists.

With this function, we can just check if the memory segment for the handle actually exists, and otherwise just return an invalid handle:

“`c static dsm_handle memtable_catalog_lookup(Oid relid, bool* is_memtable) { HeapTuple tup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));

if (HeapTupleIsValid(tup)) { Form_pg_class classForm = (Form_pg_class)GETSTRUCT(tup); dsm_handle handle = (dsm_handle)classForm->relfilenode; Oid memtable_oid = get_table_am_oid(MEMTABLE_EXTNAME, true); bool result = OidIsValid(memtable_oid) && classForm->relam == memtable_oid;

if (is_memtable != NULL)
  *is_memtable = result;

ReleaseSysCache(tup);

if (handle != DSM_HANDLE_INVALID && !memtable_segment_exists(handle))
  handle = DSM_HANDLE_INVALID;

return handle;

}

if (is_memtable != NULL) *is_memtable = false;

return DSM_HANDLE_INVALID; } “`

That is what a table that lives in memory should look like after a restart. The place to do it is memtable_attach_arena from Part 7, which is where the error came from. Where it used to give up, it now creates a new arena:

“`c static void memtable_attach_arena(Relation rel, MemTableStateCacheEntry* entry) { dsm_handle handle = (dsm_handle)rel->rd_locator.relNumber; dsm_segment* seg = dsm_find_mapping(handle);

if (seg == NULL) { seg = dsm_attach(handle); if (seg == NULL) { /* * Only one backend at a time recreates the arena of a table. A backend * that has to wait for the lock gets the new handle from relfilenode * once the other transaction has committed, and attaches to it. */ LockRelationOid(entry->relid, ShareUpdateExclusiveLock); handle = (dsm_handle)rel->rd_locator.relNumber; seg = dsm_attach(handle); if (seg == NULL) { memtable_recreate_arena(rel, entry); return; } } }

dsm_pin_mapping(seg); /* survive end-of-transaction in this backend */

entry->seg = seg; entry->arena_base = dsm_segment_address(seg); entry->hdr = entry->arena_base; entry->handle = handle; } “`

Recreating an arena is almost the same as what CREATE TABLE does. The arena is created, the abort cleanup from Part 5 is scheduled so that the arena is released if the transaction aborts, and the new handle is written to relfilenode with the function that CREATE TABLE uses:

“`c static void memtable_recreate_arena(Relation rel, MemTableStateCacheEntry* entry) { Oid relid = RelationGetRelid(rel); dsm_handle handle = memtable_create_arena(rel, entry);

memtable_schedule_cleanup(relid, handle, XACT_EVENT_ABORT); memtable_finalize_new_relation(relid); } “`

Seeing it work

Create a few tables and restart the server:

“sql mats=# CREATE TABLE my_table (id int) USING memtable; CREATE TABLE mats=# CREATE TABLE my_dropped (id int) USING memtable; CREATE TABLE mats=# CREATE TABLE my_truncated (id int) USING memtable; CREATE TABLE mats=# INSERT INTO my_table VALUES (1), (2), (3); INSERT 0 3 mats=# SELECT relfilenode FROM pg_class WHERE relname = ‘my_table’; relfilenode ————- 975595960 (1 row) “

“ $ pg_ctl -D data restart “

The table is empty, it has a new arena, and it takes new rows:

“`sql mats=# SELECT count(*) FROM my_table; count ——- 0 (1 row)

mats=# SELECT relfilenode FROM pg_class WHERE relname = ‘my_table’; relfilenode ————- 1214806652 (1 row)

mats=# INSERT INTO my_table VALUES (4); INSERT 0 1 mats=# SELECT * FROM my_table; id —- 4 (1 row) “`

A table that nothing has used since the restart can be dropped or truncated, which took the server down before:

“sql mats=# DROP TABLE my_dropped; DROP TABLE mats=# TRUNCATE my_truncated; TRUNCATE TABLE mats=# SELECT count(*) FROM my_truncated; count ——- 0 (1 row) “

Concluding remarks

MemTable is now correctly recorded as unlogged, which is what it always was. Postgres will now treat it as an unlogged table, the table after a restart will be a proper empty table.

pg_memtable can store rows and read them back, but the only way to find a row is still to read all of them. The next post looks at how a row can be found without reading the whole table.

The code

The code of this post is in the pg_memtable repository. The repository has one commit for each post, so the difference between two of them is the code that a post adds. The functions are split over several source files there, and the code contains fixes that were made after the posts were written, so the layout differs from the listings in the post.

Kaynak: Planet PostgreSQL

#PostgreSQL
Share

From other categories