Skip to content

Commit b73c3a1

Browse files
anarazelanarazel
committed
tableam: basic documentation.
This adds documentation about the user oriented parts of table access methods (i.e. the default_table_access_method GUC and the USING clause for CREATE TABLE etc), adds a basic chapter about the table access method interface, and adds a note to storage.sgml that it's contents don't necessarily apply for non-builtin AMs. Author: Haribabu Kommi and Andres Freund Discussion: https://postgr.es/m/20180703070645.wchpu5muyto5n647@alap3.anarazel.de
1 parent ab9ed9b commit b73c3a1

13 files changed

+228
-16
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -587,8 +587,9 @@
587587
The catalog <structname>pg_am</structname> stores information about
588588
relation access methods. There is one row for each access method supported
589589
by the system.
590-
Currently, only indexes have access methods. The requirements for index
591-
access methods are discussed in detail in <xref linkend="indexam"/>.
590+
Currently, only table and indexes have access methods. The requirements for table
591+
and index access methods are discussed in detail in <xref linkend="tableam"/> and
592+
<xref linkend="indexam"/> respectively.
592593
</para>
593594

594595
<table>
@@ -634,8 +635,8 @@
634635
<entry><type>char</type></entry>
635636
<entry></entry>
636637
<entry>
637-
Currently always <literal>i</literal> to indicate an index access
638-
method; other values may be allowed in future
638+
<literal>t</literal> = table (including materialized views),
639+
<literal>i</literal> = index.
639640
</entry>
640641
</row>
641642
</tbody>

‎doc/src/sgml/config.sgml

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7294,6 +7294,23 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
72947294
</listitem>
72957295
</varlistentry>
72967296

7297+
<varlistentry id="guc-default-table-access-method" xreflabel="default_table_access_method">
7298+
<term><varname>default_table_access_method</varname> (<type>string</type>)
7299+
<indexterm>
7300+
<primary><varname>default_table_access_method</varname> configuration parameter</primary>
7301+
</indexterm>
7302+
</term>
7303+
<listitem>
7304+
<para>
7305+
This parameter specifies the default table access method to use when
7306+
creating tables or materialized views if the <command>CREATE</command>
7307+
command does not explicitly specify an access method, or when
7308+
<command>SELECT ... INTO</command> is used, which does not allow to
7309+
specify a table access method. The default is <literal>heap</literal>.
7310+
</para>
7311+
</listitem>
7312+
</varlistentry>
7313+
72977314
<varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
72987315
<term><varname>default_tablespace</varname> (<type>string</type>)
72997316
<indexterm>

‎doc/src/sgml/filelist.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,7 @@
8989
<!ENTITY gin SYSTEM "gin.sgml">
9090
<!ENTITY brin SYSTEM "brin.sgml">
9191
<!ENTITY planstats SYSTEM "planstats.sgml">
92+
<!ENTITY tableam SYSTEM "tableam.sgml">
9293
<!ENTITY indexam SYSTEM "indexam.sgml">
9394
<!ENTITY nls SYSTEM "nls.sgml">
9495
<!ENTITY plhandler SYSTEM "plhandler.sgml">

‎doc/src/sgml/indexam.sgml

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,14 @@
33
<chapter id="indexam">
44
<title>Index Access Method Interface Definition</title>
55

6+
<indexterm>
7+
<primary>Index Access Method</primary>
8+
</indexterm>
9+
<indexterm>
10+
<primary>indexam</primary>
11+
<secondary>Index Access Method</secondary>
12+
</indexterm>
13+
614
<para>
715
This chapter defines the interface between the core
816
<productname>PostgreSQL</productname> system and <firstterm>index access
@@ -50,8 +58,8 @@
5058
Each index access method is described by a row in the
5159
<link linkend="catalog-pg-am"><structname>pg_am</structname></link>
5260
system catalog. The <structname>pg_am</structname> entry
53-
specifies a name and a <firstterm>handler function</firstterm> for the access
54-
method. These entries can be created and deleted using the
61+
specifies a name and a <firstterm>handler function</firstterm> for the index
62+
access method. These entries can be created and deleted using the
5563
<xref linkend="sql-create-access-method"/> and
5664
<xref linkend="sql-drop-access-method"/> SQL commands.
5765
</para>

‎doc/src/sgml/postgres.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -250,6 +250,7 @@
250250
&tablesample-method;
251251
&custom-scan;
252252
&geqo;
253+
&tableam;
253254
&indexam;
254255
&generic-wal;
255256
&btree;

‎doc/src/sgml/ref/create_access_method.sgml

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -61,7 +61,8 @@ CREATE ACCESS METHOD <replaceable class="parameter">name</replaceable>
6161
<listitem>
6262
<para>
6363
This clause specifies the type of access method to define.
64-
Only <literal>INDEX</literal> is supported at present.
64+
Only <literal>TABLE</literal> and <literal>INDEX</literal>
65+
are supported at present.
6566
</para>
6667
</listitem>
6768
</varlistentry>
@@ -75,10 +76,13 @@ CREATE ACCESS METHOD <replaceable class="parameter">name</replaceable>
7576
that represents the access method. The handler function must be
7677
declared to take a single argument of type <type>internal</type>,
7778
and its return type depends on the type of access method;
78-
for <literal>INDEX</literal> access methods, it must
79-
be <type>index_am_handler</type>. The C-level API that the handler
80-
function must implement varies depending on the type of access method.
81-
The index access method API is described in <xref linkend="indexam"/>.
79+
for <literal>TABLE</literal> access methods, it must
80+
be <type>table_am_handler</type> and for <literal>INDEX</literal>
81+
access methods, it must be <type>index_am_handler</type>.
82+
The C-level API that the handler function must implement varies
83+
depending on the type of access method. The table access method API
84+
is described in <xref linkend="tableam"/> and the index access method
85+
API is described in <xref linkend="indexam"/>.
8286
</para>
8387
</listitem>
8488
</varlistentry>

‎doc/src/sgml/ref/create_materialized_view.sgml

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ PostgreSQL documentation
2323
<synopsis>
2424
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
2525
[ (<replaceable>column_name</replaceable> [, ...] ) ]
26+
[ USING <replaceable class="parameter">method</replaceable> ]
2627
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
2728
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
2829
AS <replaceable>query</replaceable>
@@ -85,6 +86,21 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
8586
</listitem>
8687
</varlistentry>
8788

89+
<varlistentry>
90+
<term><literal>USING <replaceable class="parameter">method</replaceable></literal></term>
91+
<listitem>
92+
<para>
93+
This optional clause specifies the table access method to use to store
94+
the contents for the new materialized view; the method needs be an
95+
access method of type <literal>TABLE</literal>. See <xref
96+
linkend="tableam"/> for more information. If this option is not
97+
specified, the default table access method is chosen for the new
98+
materialized view. See <xref linkend="guc-default-table-access-method"/>
99+
for more information.
100+
</para>
101+
</listitem>
102+
</varlistentry>
103+
88104
<varlistentry>
89105
<term><literal>WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
90106
<listitem>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
2929
] )
3030
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
3131
[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
32+
[ USING <replaceable class="parameter">method</replaceable> ]
3233
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
3334
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
3435
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -40,6 +41,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
4041
[, ... ]
4142
) ]
4243
[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
44+
[ USING <replaceable class="parameter">method</replaceable> ]
4345
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
4446
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
4547
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -51,6 +53,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
5153
[, ... ]
5254
) ] { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }
5355
[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
56+
[ USING <replaceable class="parameter">method</replaceable> ]
5457
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
5558
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
5659
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -1165,6 +1168,20 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
11651168
</listitem>
11661169
</varlistentry>
11671170

1171+
<varlistentry id="sql-createtable-method">
1172+
<term><literal>USING <replaceable class="parameter">method</replaceable></literal></term>
1173+
<listitem>
1174+
<para>
1175+
This optional clause specifies the table access method to use to store
1176+
the contents for the new table; the method needs be an access method of
1177+
type <literal>TABLE</literal>. See <xref linkend="tableam"/> for more
1178+
information. If this option is not specified, the default table access
1179+
method is chosen for the new materialized view. See <xref
1180+
linkend="guc-default-table-access-method"/> for more information.
1181+
</para>
1182+
</listitem>
1183+
</varlistentry>
1184+
11681185
<varlistentry>
11691186
<term><literal>WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
11701187
<listitem>
@@ -1238,7 +1255,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
12381255
</listitem>
12391256
</varlistentry>
12401257

1241-
<varlistentry>
1258+
<varlistentry id="sql-createtable-tablespace">
12421259
<term><literal>TABLESPACE <replaceable class="parameter">tablespace_name</replaceable></literal></term>
12431260
<listitem>
12441261
<para>

‎doc/src/sgml/ref/create_table_as.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ PostgreSQL documentation
2323
<synopsis>
2424
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
2525
[ (<replaceable>column_name</replaceable> [, ...] ) ]
26+
[ USING <replaceable class="parameter">method</replaceable> ]
2627
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
2728
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
2829
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -120,6 +121,20 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
120121
</listitem>
121122
</varlistentry>
122123

124+
<varlistentry>
125+
<term><literal>USING <replaceable class="parameter">method</replaceable></literal></term>
126+
<listitem>
127+
<para>
128+
This optional clause specifies the table access method to use to store
129+
the contents for the new table; the method needs be an access method of
130+
type <literal>TABLE</literal>. See <xref linkend="tableam"/> for more
131+
information. If this option is not specified, the default table access
132+
method is chosen for the new materialized view. See <xref
133+
linkend="guc-default-table-access-method"/> for more information.
134+
</para>
135+
</listitem>
136+
</varlistentry>
137+
123138
<varlistentry>
124139
<term><literal>WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
125140
<listitem>

‎doc/src/sgml/ref/select_into.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,16 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
104104
<command>CREATE TABLE AS</command> offers a superset of the
105105
functionality provided by <command>SELECT INTO</command>.
106106
</para>
107+
108+
<para>
109+
In contrast to <command>CREATE TABLE AS</command> <command>SELECT
110+
INTO</command> does not allow to specify properties like a table's access
111+
method with <xref linkend="sql-createtable-method" /> or the table's
112+
tablespace with <xref linkend="sql-createtable-tablespace" />. Use <xref
113+
linkend="sql-createtableas"/> if necessary. Therefore the default table
114+
access method is chosen for the new table. See <xref
115+
linkend="guc-default-table-access-method"/> for more information.
116+
</para>
107117
</refsect1>
108118

109119
<refsect1>

‎doc/src/sgml/storage.sgml

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -188,6 +188,14 @@ for the database's files; in particular, its system catalogs are stored
188188
there.
189189
</para>
190190

191+
<para>
192+
Note that the following sections describe the way the builtin
193+
<literal>heap</literal> <link linkend="tableam">table access method</link>,
194+
and the builtin <link linkend="indexam">index access methods</link> work. Due
195+
to the extensible nature of <productname>PostgreSQL</productname> other types
196+
of access method might work similar or not.
197+
</para>
198+
191199
<para>
192200
Each table and index is stored in a separate file. For ordinary relations,
193201
these files are named after the table or index's <firstterm>filenode</firstterm> number,
@@ -695,10 +703,11 @@ erased (they will be recreated automatically as needed).
695703
This section provides an overview of the page format used within
696704
<productname>PostgreSQL</productname> tables and indexes.<footnote>
697705
<para>
698-
Actually, index access methods need not use this page format.
699-
All the existing index methods do use this basic format,
700-
but the data kept on index metapages usually doesn't follow
701-
the item layout rules.
706+
Actually, neither table nor index access methods need not use this page
707+
format. All the existing index methods do use this basic format, but the
708+
data kept on index metapages usually doesn't follow the item layout
709+
rules. The <literal>heap</literal> table access method also always uses
710+
this format.
702711
</para>
703712
</footnote>
704713
Sequences and <acronym>TOAST</acronym> tables are formatted just like a regular table.

‎doc/src/sgml/tableam.sgml

Lines changed: 110 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,110 @@
1+
<!-- doc/src/sgml/tableam.sgml -->
2+
3+
<chapter id="tableam">
4+
<title>Table Access Method Interface Definition</title>
5+
6+
<indexterm>
7+
<primary>Table Access Method</primary>
8+
</indexterm>
9+
<indexterm>
10+
<primary>tableam</primary>
11+
<secondary>Table Access Method</secondary>
12+
</indexterm>
13+
14+
<para>
15+
This chapter explains the interface between the core
16+
<productname>PostgreSQL</productname> system and <firstterm>table access
17+
methods</firstterm>, which manage the storage for tables. The core system
18+
knows little about these access methods beyond what is specified here, so
19+
it is possible to develop entirely new access method types by writing
20+
add-on code.
21+
</para>
22+
23+
<para>
24+
Each table access method is described by a row in the <link
25+
linkend="catalog-pg-am"><structname>pg_am</structname></link> system
26+
catalog. The <structname>pg_am</structname> entry specifies a name and a
27+
<firstterm>handler function</firstterm> for the table access method. These
28+
entries can be created and deleted using the <xref
29+
linkend="sql-create-access-method"/> and <xref
30+
linkend="sql-drop-access-method"/> SQL commands.
31+
</para>
32+
33+
<para>
34+
A table access method handler function must be declared to accept a single
35+
argument of type <type>internal</type> and to return the pseudo-type
36+
<type>table_am_handler</type>. The argument is a dummy value that simply
37+
serves to prevent handler functions from being called directly from SQL commands.
38+
39+
The result of the function must be a pointer to a struct of type
40+
<structname>TableAmRoutine</structname>, which contains everything that the
41+
core code needs to know to make use of the table access method. The return
42+
value needs to be of server lifetime, which is typically achieved by
43+
defining it as a <literal>static const</literal> variable in global
44+
scope. The <structname>TableAmRoutine</structname> struct, also called the
45+
access method's <firstterm>API struct</firstterm>, defines the behavior of
46+
the access method using callbacks. These callbacks are pointers to plain C
47+
functions and are not visible or callable at the SQL level. All the
48+
callbacks and their behavior is defined in the
49+
<structname>TableAmRoutine</structname> structure (with comments inside the
50+
struct defining the requirements for callbacks). Most callbacks have
51+
wrapper functions, which are documented for the point of view of a user,
52+
rather than an implementor, of the table access method. For details,
53+
please refer to the <ulink url="https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/access/tableam.h;hb=HEAD">
54+
<filename>src/include/access/tableam.h</filename></ulink> file.
55+
</para>
56+
57+
<para>
58+
To implement a access method, an implementor will typically need to
59+
implement a AM specific type of tuple table slot (see
60+
<ulink url="https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/executor/tuptable.h;hb=HEAD">
61+
<filename>src/include/executor/tuptable.h</filename></ulink>) which allows
62+
code outside the access method to hold references to tuples of the AM, and
63+
to access the columns of the tuple.
64+
</para>
65+
66+
<para>
67+
Currently the the way an AM actually stores data is fairly
68+
unconstrained. It is e.g. possible to use postgres' shared buffer cache,
69+
but not required. In case shared buffers are used, it likely makes to
70+
postgres' standard page layout described in <xref
71+
linkend="storage-page-layout"/>.
72+
</para>
73+
74+
<para>
75+
One fairly large constraint of the table access method API is that,
76+
currently, if the AM wants to support modifications and/or indexes, it is
77+
necessary that each tuple has a tuple identifier (<acronym>TID</acronym>)
78+
consisting of a block number and an item number (see also <xref
79+
linkend="storage-page-layout"/>). It is not strictly necessary that the
80+
sub-parts of <acronym>TIDs</acronym> have the same meaning they e.g. have
81+
for <literal>heap</literal>, but if bitmap scan support is desired (it is
82+
optional), the block number needs to provide locality.
83+
</para>
84+
85+
<para>
86+
For crash safety an AM can use postgres' <link
87+
linkend="wal"><acronym>WAL</acronym></link>, or a custom approach can be
88+
implemented. If <acronym>WAL</acronym> is chosen, either <link
89+
linkend="generic-wal">Generic WAL Records</link> can be used &mdash; which
90+
implies higher WAL volume but is easy, or a new type of
91+
<acronym>WAL</acronym> records can be implemented &mdash; but that
92+
currently requires modifications of core code (namely modifying
93+
<filename>src/include/access/rmgrlist.h</filename>).
94+
</para>
95+
96+
<para>
97+
To implement transactional support in a manner that allows different table
98+
access methods be accessed within a single transaction, it likely is
99+
necessary to closely integrate with the machinery in
100+
<filename>src/backend/access/transam/xlog.c</filename>.
101+
</para>
102+
103+
<para>
104+
Any developer of a new <literal>table access method</literal> can refer to
105+
the existing <literal>heap</literal> implementation present in
106+
<filename>src/backend/heap/heapam_handler.c</filename> for more details of
107+
how it is implemented.
108+
</para>
109+
110+
</chapter>

‎src/include/access/tableam.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,9 @@
99
*
1010
* src/include/access/tableam.h
1111
*
12+
* NOTES
13+
* See tableam.sgml for higher level documentation.
14+
*
1215
*-------------------------------------------------------------------------
1316
*/
1417
#ifndef TABLEAM_H

0 commit comments

Comments
 (0)