Introduction

In my previous blog about table access method here, we discussed the basis of PostgreSQL’s table access method APIs and the difference between a heap tuple and Tuple Table Slot (TTS). In this blog, let’s talk more about the particular API calls that helps PostgreSQL core to achieve sequential scan.

APIs Involved

To achieve a sequential scan, the following table access method API callbacks will be involved:

• relation_size()
• slot_callbacks()
• scan_begin()
• scan_getnextslot()
• scan_end()

By default, PostgreSQL uses heap access method, and it implements the above APIs as:

• table_block_relation_size()
• heapam_slot_callbacks()
• heap_beginscan()
• heap_getnextslot()
• heap_endscan()

these are located in src/backend/access/heap/heapam_handler.c.

Basic Workflow

API Function Calls

table_block_relation_size(Relation rel, ForkNumber forkNumber):

This is the first function to be called during the query planning phase and it is supposed to physically examine the size of the given Relation rel and ForkNumber forkNumber. It shall returns the total number of blocks (8KB size by default) to the caller so that it can plan the best query strategy based on how large of a table we are working with.

What is a relation?

A relation, in most cases, represents a table, an index or a view. In our case, it represents a table and it contains important information about this table, such as the object identifiers (OIDs) for database, table space, number of attributes, other flags…etc

What is a fork number?

A relation is composed of multiple forks

• MAIN_FORKNUM (0: stores user data)
• FSM_FORKNUM (1: stores free space map data)
• VISIBILITY_FORKNUM (2: visibility data)
• INIT_FORKNUM (3: used to reset WAL-logged table)

more on fork number here

heapam_slot_callbacks()

This function is called to return an appropriate TTS Operations so executor is able to convert a heap tuple to Tuple Table Slot (TTS). For heap access method, it uses this TTS callback with consideration of buffer manager:

const TupleTableSlotOps TTSOpsBufferHeapTuple = {
	.base_slot_size = sizeof(BufferHeapTupleTableSlot),
	.init = tts_buffer_heap_init,
	.release = tts_buffer_heap_release,
	.clear = tts_buffer_heap_clear,
	.getsomeattrs = tts_buffer_heap_getsomeattrs,
	.getsysattr = tts_buffer_heap_getsysattr,
	.materialize = tts_buffer_heap_materialize,
	.copyslot = tts_buffer_heap_copyslot,
	.get_heap_tuple = tts_buffer_heap_get_heap_tuple,

	/* A buffer heap tuple table slot can not "own" a minimal tuple. */
	.get_minimal_tuple = NULL,
	.copy_heap_tuple = tts_buffer_heap_copy_heap_tuple,
	.copy_minimal_tuple = tts_buffer_heap_copy_minimal_tuple
};

scan_begin() and scan_end()

Called before and after the actual sequential scan. scan_begin is responsible for initializing a HeapScanDesc that describes the status of current status, including values such as: current block number, total number of blocks to be scanned, scan mode, snapshot…etc. The definition can be located in src/include/backend/access/heapam.h.

Scan_end(), on the other hand, cleans up the HeapScanDesc allocated in scan_begin()

scan_getnextslot()

Core of sequential scan. This function is responsible to retrieve a tuple from storage engine. In PostgreSQL, it requests a tuple from buffer manager module, which can either read the tuple from its own memory buffer or read it from physical storage.

This is why it is possible to create your own database storage engine with this API, It is possible to read and write tuples purely from and to memory to form an in-memory database.

If you still have data to scan, this function needs to return true, so executor will call it again. If all the data has been exhausted, it returns false to indicate the end of scan.

Summary

This is the basic workflow of a sequential scan in PostgreSQL, which utilizes the table access method APIs. In the next blog, we will explore other APIs to handle other DML commands such as insert, update, delete, truncate..etc.

Cary Huang

Cary is a Senior Software Developer in HighGo Software Canada with 8 years of industrial experience developing innovative software solutions in C/C++ in the field of smart grid & metering prior to joining HighGo. He holds a bachelor degree in Electrical Engineering from University of British Columnbia (UBC) in Vancouver in 2012 and has extensive hands-on experience in technologies such as: Advanced Networking, Network & Data security, Smart Metering Innovations, deployment management with Docker, Software Engineering Lifecycle, scalability, authentication, cryptography, PostgreSQL & non-relational database, web services, firewalls, embedded systems, RTOS, ARM, PKI, Cisco equipment, functional and Architecture Design.