本部分包含可能用於 PostgreSQL 研發人員的各種內容。

目錄 pg_auth_members 顯示角色之間的成員資格關連。允許任何非循環的關連。

由於角色身份是叢集範圍的，因此 pg_auth_members 在叢集的所有資料庫之間共享：每個叢集只有一個 pg_auth_members 副本，而不是每個資料庫一個副本。

Table 51.9. pg_auth_members Columns

The catalog pg_am stores information about relation access methods. There is one row for each access method supported by the system. Currently, only indexes have access methods. The requirements for index access methods are discussed in detail in Chapter 60.

Table 51.3. pg_am Columns

Note

Before PostgreSQL 9.6, pg_am contained many additional columns representing properties of index access methods. That data is now only directly visible at the C code level. However,pg_index_column_has_property() and related functions have been added to allow SQL queries to inspect index access method properties; see Table 9.63.

目錄 pg_class 對資料表和大多數具有欄位或其他類似於資料表的內容進行彙整。 包括索引（但也參閱 pg_index）、序列（但請參閱 pg_sequence）、檢視表、具體化檢視表、複合型別和 TOAST 資料表；另請查看 relkind 欄位。以下，當我們指的是所有這些類型的物件時，我們都會說「關連（relation）」。 並非所有欄位對所有關連類型都有意義。

Table 52.11. pg_class Columns

pg_class 中的幾個布林欄位的維護是鬆散的：如果這是正確的狀態，那它們保證為 true，但是當條件不再為真時，可能不會立即重置為 false。例如，relhasindex 由CREATE INDEX 設定，但它永遠不會被 DROP INDEX 清除。相反地，如果 VACUUM 發現資料表沒有索引，則清除 relhasindex。這種安排避免了競爭條件並改善了一致性。

目錄 pg_authid 包含有關資料庫認證識別（角色）的資訊。角色包含「使用者」和「群組」的概念。使用者基本上只是設置了 rolcanlogin 識別的角色。任何角色（無論有或沒有 rolcanlogin）都可以擁有其他角色作為成員；詳見 。

由於此目錄包含密碼，因此不得公開讀取。 是 pg_authid 上的一個公開可讀的檢視表，它隱藏了密碼字串。

包含有關使用者和權限管理的詳細訊息。

由於使用者身份是叢集範圍的，因此 pg_authid 在叢集的所有資料庫之間共享：每個叢集只有一個 pg_authid 副本，而不是每個資料庫一個副本。

Table 51.8. pg_authid Columns

對於 MD5 加密密碼，rolpassword 欄位將以字串 md5 開頭，之後跟 32 個字元的十六進位 MD5 hash。MD5 hash 將是使用者的密碼連接到他們的使用者名稱。例如，如果使用者 joe 的密碼為 xyzzy，則 PostgreSQL 將儲存 xyzzyjoe 的 md5 hash 值。

如果使用 SCRAM-SHA-256 加密密碼，則其格式為：

其中 salt，StoredKey 和 ServerKey 採用 Base64 編碼格式。此格式與 RFC 5803 指定的格式相同。

未遵循這些格式之一的密碼就會被認為是未加密的。

系統目錄（system catalog）是記錄資料庫管理系統儲存結構原始資料的地方，例如關於資料表和欄位的訊息以及內部日誌記錄訊息。PostgreSQL 的系統目錄是一般的資料表。您可以刪除並重新建立資料表、增加欄位、插入和更新內容，並以這種方式嚴重混淆您的系統。當然，通常情況下，不應該手動更改系統目錄，通常有 SQL 命令來執行此操作。（例如，CREATE DATABASE 向 pg_database 系統目錄插入一行 - 實際上是在磁碟上建立數據庫）。對於特別深奧的操作有一些例外，但其中很多已經隨著時間的推移而變為 SQL 命令，因此需要系統目錄的直接操作正在不斷減少。

PostgreSQLuses a message-based protocol for communication between frontends and backends (clients and servers). The protocol is supported overTCP/IPand also over Unix-domain sockets. Port number 5432 has been registered with IANA as the customary TCP port number for servers supporting this protocol, but in practice any non-privileged port number can be used.

This document describes version 3.0 of the protocol, implemented inPostgreSQL7.4 and later. For descriptions of the earlier protocol versions, see previous releases of thePostgreSQLdocumentation. A single server can support multiple protocol versions. The initial startup-request message tells the server which protocol version the client is attempting to use, and then the server follows that protocol if it is able.

In order to serve multiple clients efficiently, the server launches a new“backend”process for each client. In the current implementation, a new child process is created immediately after an incoming connection is detected. This is transparent to the protocol, however. For purposes of the protocol, the terms“backend”and“server”are interchangeable; likewise“frontend”and“client”are interchangeable.

The PostgreSQL source distribution includes several examples of index methods implemented using GiST. The core system currently provides text search support (indexing for tsvectorand tsquery) as well as R-Tree equivalent functionality for some of the built-in geometric data types (see src/backend/access/gist/gistproc.c). The following contrib modules also contain GiST operator classes:btree_gist

B-tree equivalent functionality for several data typescube

Indexing for multidimensional cubeshstore

Module for storing (key, value) pairsintarray

RD-Tree for one-dimensional array of int4 valuesltree

Indexing for tree-like structurespg_trgm

Text similarity using trigram matchingseg

Indexing for “float ranges”

GIN assumes that indexable operators are strict. This means that extractValue will not be called at all on a null item value (instead, a placeholder index entry is created automatically), and extractQuery will not be called on a null query value either (instead, the query is presumed to be unsatisfiable). Note however that null key values contained within a non-null composite item or query value are supported.

目錄 pg_index 包含有關索引的部分信息。其餘的大多數是在 pg_class 中。

Table 51.26. pg_index Columns

目錄 pg_language 註冊了可以撰寫函數或 stored procedure 的語言。有關語言處理程序的更多訊息，請參閱 CREATE LANGUAGE 和第 41 章。

Table 51.29. pg_language Columns

The catalog pg_collation describes the available collations, which are essentially mappings from an SQL name to operating system locale categories. See Section 23.2 for more information.

Table 51.12. pg_collation Columns

Note that the unique key on this catalog is (collname, collencoding, collnamespace) not just (collname, collnamespace). PostgreSQL generally ignores all collations that do not have collencoding equal to either the current database's encoding or -1, and creation of new entries with the same name as an entry with collencoding = -1 is forbidden. Therefore it is sufficient to use a qualified SQL name (schema.name) to identify a collation, even though this is not unique according to the catalog definition. The reason for defining the catalog this way is that initdb fills it in at cluster initialization time with entries for all locales available on the system, so it must be able to hold entries for all encodings that might ever be used in the cluster.

In the template0 database, it could be useful to create collations whose encoding does not match the database encoding, since they could match the encodings of databases later cloned from template0. This would currently have to be done manually.

目錄 pg_database 儲存有關資料庫一些可用的訊息。資料庫是使用 CREATE DATABASE 命令建立的。關於某些參數的含義的詳細訊息，請參閱第 22 章。

與大多數系統目錄不同，pg_database 在叢集的所有資料庫之間共享：每個叢集只有一個 pg_database 副本，而不是每個資料庫一個副本。

Table 51.15. pg_database 欄位

The catalog pg_attribute stores information about table columns. There will be exactly one pg_attribute row for every column in every table in the database. (There will also be attribute entries for indexes, and indeed all objects that have pg_class entries.)

The term attribute is equivalent to column and is used for historical reasons.

Table 51.7. pg_attribute Columns

In a dropped column's pg_attribute entry, atttypid is reset to zero, but attlen and the other fields copied from pg_type are still valid. This arrangement is needed to cope with the situation where the dropped column's data type was later dropped, and so there is no pg_type row anymore. attlen and the other fields can be used to interpret the contents of a row of the table.

The catalog pg_rewrite stores rewrite rules for tables and views.

Table 51.44. pg_rewrite Columns

Note

pg_class.relhasrules must be true if a table has any rules in this catalog.

The catalog pg_opclass defines index access method operator classes. Each operator class defines semantics for index columns of a particular data type and a particular index access method. An operator class essentially specifies that a particular operator family is applicable to a particular indexable column data type. The set of operators from the family that are actually usable with the indexed column are whichever ones accept the column's data type as their left-hand input.

Operator classes are described at length in Section 37.14.

Table 51.33. pg_opclass Columns

An operator class's opcmethod must match the opfmethod of its containing operator family. Also, there must be no more than one pg_opclass row having opcdefault true for any given combination of opcmethod and opcintype.

The catalog pg_proc stores information about functions, procedures, aggregate functions, and window functions (collectively also known as routines). See CREATE FUNCTION, CREATE PROCEDURE, and Section 38.3 for more information.

If prokind indicates that the entry is for an aggregate function, there should be a matching row in pg_aggregate.

Table 52.39. pg_proc Columns

For compiled functions, both built-in and dynamically loaded, prosrc contains the function's C-language name (link symbol). For all other currently-known language types, prosrc contains the function's source text. probin is unused except for dynamically-loaded C functions, for which it gives the name of the shared library file containing the function.

The catalog pg_constraint stores check, primary key, unique, foreign key, and exclusion constraints on tables. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) Not-null constraints are represented in the pg_attribute catalog, not here.

User-defined constraint triggers (created with CREATE CONSTRAINT TRIGGER) also give rise to an entry in this table.

Check constraints on domains are stored here, too.

Table 51.13. pg_constraint Columns

In the case of an exclusion constraint, conkey is only useful for constraint elements that are simple column references. For other cases, a zero appears in conkey and the associated index must be consulted to discover the expression that is constrained. (conkey thus has the same contents as pg_index.indkey for the index.)

Note

consrc is not updated when referenced objects change; for example, it won't track renaming of columns. Rather than relying on this field, it's best to use pg_get_constraintdef() to extract the definition of a check constraint.

Note

pg_class.relchecks needs to agree with the number of check-constraint entries found in this table for each relation.

pg_roles 這個 view 提供對資料庫角色的資訊。 這只是 pg_authid 的一個公共可讀的 view，它將密碼部份予以屏蔽。

此 view 列出底層的 OID 欄位，因此可能需要執行與其他目錄的交叉查詢。

Table 51.82. pg_roles 欄位

The catalog pg_policy stores row level security policies for tables. A policy includes the kind of command that it applies to (possibly all commands), the roles that it applies to, the expression to be added as a security-barrier qualification to queries that include the table, and the expression to be added as a WITH CHECK option for queries that attempt to add new records to the table.

Table 51.38. pg_policy Columns

Note

Policies stored in pg_policy are applied only when pg_class.relrowsecurity is set for their table.

The catalog pg_statistic stores statistical data about the contents of the database. Entries are created by ANALYZE and subsequently used by the query planner. Note that all the statistical data is inherently approximate, even assuming that it is up-to-date.

Normally there is one entry, with stainherit = false, for each table column that has been analyzed. If the table has inheritance children, a second entry with stainherit = true is also created. This row represents the column's statistics over the inheritance tree, i.e., statistics for the data you'd see with SELECT column FROM table*, whereas the stainherit = false row represents the results of SELECT column FROM ONLY table.

pg_statistic also stores statistical data about the values of index expressions. These are described as if they were actual data columns; in particular, starelid references the index. No entry is made for an ordinary non-expression index column, however, since it would be redundant with the entry for the underlying table column. Currently, entries for index expressions always have stainherit = false.

Since different kinds of statistics might be appropriate for different kinds of data, pg_statistic is designed not to assume very much about what sort of statistics it stores. Only extremely general statistics (such as nullness) are given dedicated columns in pg_statistic. Everything else is stored in “slots”, which are groups of associated columns whose content is identified by a code number in one of the slot's columns. For more information see src/include/catalog/pg_statistic.h.

pg_statistic should not be readable by the public, since even statistical information about a table's contents might be considered sensitive. (Example: minimum and maximum values of a salary column might be quite interesting.) pg_stats is a publicly readable view on pg_statistic that only exposes information about those tables that are readable by the current user.

Table 51.50. pg_statistic Columns

系統目錄 pg_tablespace 儲存有關可用資料表空間的訊息。可以將資料表放置在特定的資料表空間中以幫助管理磁碟規畫。

與大多數系統目錄不同，pg_tablespace 在叢集的所有資料庫之間共享：每個叢集只有一個 pg_tablespace 副本，而不是每個資料庫一個副本。

Table 51.54. pg_tablespace 欄位

目錄 pg_namespace 儲存命名空間。命名空間是 SQL 綱要的基礎結構：每個命名空間可以有一個獨立的關連，型別等集合，而不會有名稱衝突。

Table 52.32. pg_namespace Columns

目錄 pg_statistic_ext 包含了延伸的計劃程序統計資訊。此目錄中的每一個資料列相對應於使用 所建立的統計資訊物件。

Table 52.51. pg_statistic_ext Columns

stxkind 欄位會在建立統計物件時填入，指示需要哪種統計類型。在它之後的欄位最初為 NULL，僅在 ANALYZE 計算出相對應的統計量時才填入內容。

檢視表 pg_settings 提供對伺服器的執行時參數的存取。它本質上是 和 指令的替代介面。它也提供 SHOW 無法直接獲得的一些資訊存取，例如最小值和最大值。

Table 52.86. pg_settings Columns

設定內容有幾種可能的值，是為了降低變更組態的複雜度，它們是：

internal

這些設定無法直接更改；它們反映了內部所決定的值，其中一些可以透過使用不同的組態選項重建伺服器，或透過更改提供給 initdb 的選項來調整。

postmaster

這些設定只能在伺服器啟動時套用，因此任何變更都需要重新啟動伺服器。這些設定的值通常儲存在 postgresql.conf 檔案中，或在啟動伺服器時在命令列中給予。當然，也可以在伺服器啟動時設定任何層級較低的設定。

sighup

可以在 postgresql.conf 中對這些設定進行變更，而毌須重新啟動伺服器。只要向 postmaster 發送一個 SIGHUP 信號，使其重新讀取 postgresql.conf 並套用變更。postmaster 還會將 SIGHUP 信號轉發給其子程序，以便它們都獲取新值。

superuser-backend

可以在 postgresql.conf 中對這些設定進行變更，而毌須重新啟動伺服器。它們也可以在連線要求的封包中設定為特別連線（例如，透過 libpq 的 PGOPTIONS 環境變數），但前提是連線使用者是超級使用者。但是，這些設定在啟動後的連線中永遠不會變更。如果你在 postgresql.conf 中更改它們，請向 postmaster 發送一個 SIGHUP 信號，使其重新讀取 postgresql.conf。新值只會影響隨後啟動的連線。

backend

可以在 postgresql.conf 中對這些設定進行變更，而毌須重新啟動伺服器。它們也可以在連線請求封包中設定為特別連線（例如，透過 libpq 的 PGOPTIONS 環境變數）；任何使用者都可以為他們的連線進行這樣的變更。但是，這些設定在啟動後的連線中永遠無法變更。如果你在 postgresql.conf 中更改它們，請向 postmaster 發送一個 SIGHUP 信號，使其重新讀取 postgresql.conf。新值只會影響隨後啟動的連線。

superuser

這些設定可以從 postgresql.conf 設定，也可以透過 SET 指令在連線中設定；但只有超級使用者可以透過 SET 來更改。僅當沒有使用 SET 建立連線專用的值時，postgresql.conf 中的變更才會影響現有連線。

user

這些設定可以從 postgresql.conf 設定，也可以透過 SET 指令在連線中設定。允許任何使用者變更其連線中所使用的值。僅當未使用 SET 未建立連線專用值時，postgresql.conf 中的變更才會影響現有連線。

The catalog pg_trigger stores triggers on tables and views. See for more information.

Table 51.56. pg_trigger Columns

Currently, column-specific triggering is supported only for UPDATE events, and so tgattr is relevant only for that event type. tgtype might contain bits for other event types as well, but those are presumed to be table-wide regardless of what is in tgattr.

Note

When tgconstraint is nonzero, tgconstrrelid, tgconstrindid, tgdeferrable, and tginitdeferred are largely redundant with the referenced pg_constraint entry. However, it is possible for a non-deferrable trigger to be associated with a deferrable constraint: foreign key constraints can have some deferrable and some non-deferrable triggers.

Note

pg_class.relhastriggers must be true if a relation has any triggers in this catalog.

The pg_replication_origin_status view contains information about how far replay for a certain origin has progressed. For more on replication origins see .

Table 51.80. pg_replication_origin_status Columns

The catalog pg_type stores information about data types. Base types and enum types (scalar types) are created with , and domains with . A composite type is automatically created for each table in the database, to represent the row structure of the table. It is also possible to create composite types with CREATE TYPE AS.

Table 51.62. pg_type Columns

Table 51.63. typcategory Codes

Source code formatting uses 4 column tab spacing, with tabs preserved (i.e., tabs are not expanded to spaces). Each logical indentation level is one additional tab stop.

Layout rules (brace positioning, etc) follow BSD conventions. In particular, curly braces for the controlled blocks of if, while, switch, etc go on their own lines.

Limit line lengths so that the code is readable in an 80-column window. (This doesn't mean that you must never go past 80 columns. For instance, breaking a long error message string in arbitrary places just to keep the code within 80 columns is probably not a net gain in readability.)

Do not use C++ style comments (// comments). Strict ANSI C compilers do not accept them. For the same reason, do not use C++ extensions such as declaring new variables mid-block.

The preferred style for multi-line comment blocks is

Note that comment blocks that begin in column 1 will be preserved as-is by pgindent, but it will re-flow indented comment blocks as though they were plain text. If you want to preserve the line breaks in an indented block, add dashes like this:

While submitted patches do not absolutely have to follow these formatting rules, it's a good idea to do so. Your code will get run through pgindent before the next release, so there's no point in making it look nice under some other set of formatting conventions. A good rule of thumb for patches is “make the new code look like the existing code around it”.

The src/tools directory contains sample settings files that can be used with the emacs, xemacs or vim editors to help ensure that they format code according to these conventions.

The text browsing tools more and less can be invoked as:

to make them show tabs appropriately.

This style guide is offered in the hope of maintaining a consistent, user-friendly style throughout all the messages generated by PostgreSQL.

What Goes Where

The primary message should be short, factual, and avoid reference to implementation details such as specific function names. “Short” means “should fit on one line under normal conditions”. Use a detail message if needed to keep the primary message short, or if you feel a need to mention implementation details such as the particular system call that failed. Both primary and detail messages should be factual. Use a hint message for suggestions about what to do to fix the problem, especially if the suggestion might not always be applicable.

For example, instead of:

write:

Rationale: keeping the primary message short helps keep it to the point, and lets clients lay out screen space on the assumption that one line is enough for error messages. Detail and hint messages can be relegated to a verbose mode, or perhaps a pop-up error-details window. Also, details and hints would normally be suppressed from the server log to save space. Reference to implementation details is best avoided since users aren't expected to know the details.

Formatting

Don't put any specific assumptions about formatting into the message texts. Expect clients and the server log to wrap lines to fit their own needs. In long messages, newline characters (\n) can be used to indicate suggested paragraph breaks. Don't end a message with a newline. Don't use tabs or other formatting characters. (In error context displays, newlines are automatically added to separate levels of context such as function calls.)

Rationale: Messages are not necessarily displayed on terminal-type displays. In GUI displays or browsers these formatting instructions are at best ignored.

Quotation Marks

English text should use double quotes when quoting is appropriate. Text in other languages should consistently use one kind of quotes that is consistent with publishing customs and computer output of other programs.

Rationale: The choice of double quotes over single quotes is somewhat arbitrary, but tends to be the preferred use. Some have suggested choosing the kind of quotes depending on the type of object according to SQL conventions (namely, strings single quoted, identifiers double quoted). But this is a language-internal technical issue that many users aren't even familiar with, it won't scale to other kinds of quoted terms, it doesn't translate to other languages, and it's pretty pointless, too.

Use of Quotes

Use quotes always to delimit file names, user-supplied identifiers, and other variables that might contain words. Do not use them to mark up variables that will not contain words (for example, operator names).

There are functions in the backend that will double-quote their own output at need (for example, format_type_be()). Do not put additional quotes around the output of such functions.

Rationale: Objects can have names that create ambiguity when embedded in a message. Be consistent about denoting where a plugged-in name starts and ends. But don't clutter messages with unnecessary or duplicate quote marks.

Grammar and Punctuation

The rules are different for primary error messages and for detail/hint messages:

Primary error messages: Do not capitalize the first letter. Do not end a message with a period. Do not even think about ending a message with an exclamation point.

Detail and hint messages: Use complete sentences, and end each with a period. Capitalize the first word of sentences. Put two spaces after the period if another sentence follows (for English text; might be inappropriate in other languages).

Error context strings: Do not capitalize the first letter and do not end the string with a period. Context strings should normally not be complete sentences.

Rationale: Avoiding punctuation makes it easier for client applications to embed the message into a variety of grammatical contexts. Often, primary messages are not grammatically complete sentences anyway. (And if they're long enough to be more than one sentence, they should be split into primary and detail parts.) However, detail and hint messages are longer and might need to include multiple sentences. For consistency, they should follow complete-sentence style even when there's only one sentence.

Upper Case vs. Lower Case

Use lower case for message wording, including the first letter of a primary error message. Use upper case for SQL commands and key words if they appear in the message.

Rationale: It's easier to make everything look more consistent this way, since some messages are complete sentences and some not.

Avoid Passive Voice

Use the active voice. Use complete sentences when there is an acting subject (“A could not do B”). Use telegram style without subject if the subject would be the program itself; do not use “I” for the program.

Rationale: The program is not human. Don't pretend otherwise.

Present vs. Past Tense

Use past tense if an attempt to do something failed, but could perhaps succeed next time (perhaps after fixing some problem). Use present tense if the failure is certainly permanent.

There is a nontrivial semantic difference between sentences of the form:

and:

The first one means that the attempt to open the file failed. The message should give a reason, such as “disk full” or “file doesn't exist”. The past tense is appropriate because next time the disk might not be full anymore or the file in question might exist.

The second form indicates that the functionality of opening the named file does not exist at all in the program, or that it's conceptually impossible. The present tense is appropriate because the condition will persist indefinitely.

Rationale: Granted, the average user will not be able to draw great conclusions merely from the tense of the message, but since the language provides us with a grammar we should use it correctly.

Type of the Object

When citing the name of an object, state what kind of object it is.

Rationale: Otherwise no one will know what “foo.bar.baz” refers to.

Brackets

Square brackets are only to be used (1) in command synopses to denote optional arguments, or (2) to denote an array subscript.

Rationale: Anything else does not correspond to widely-known customary usage and will confuse people.

Assembling Error Messages

When a message includes text that is generated elsewhere, embed it in this style:

Rationale: It would be difficult to account for all possible error codes to paste this into a single smooth sentence, so some sort of punctuation is needed. Putting the embedded text in parentheses has also been suggested, but it's unnatural if the embedded text is likely to be the most important part of the message, as is often the case.

Reasons for Errors

Messages should always state the reason why an error occurred. For example:

If no reason is known you better fix the code.

Function Names

Don't include the name of the reporting routine in the error text. We have other mechanisms for finding that out when needed, and for most users it's not helpful information. If the error text doesn't make as much sense without the function name, reword it.

Avoid mentioning called function names, either; instead say what the code was trying to do:

If it really seems necessary, mention the system call in the detail message. (In some cases, providing the actual values passed to the system call might be appropriate information for the detail message.)

Rationale: Users don't know what all those functions do.

Tricky Words to Avoid

Unable. “Unable” is nearly the passive voice. Better use “cannot” or “could not”, as appropriate.

Bad. Error messages like “bad result” are really hard to interpret intelligently. It's better to write why the result is “bad”, e.g., “invalid format”.

Illegal. “Illegal” stands for a violation of the law, the rest is “invalid”. Better yet, say why it's invalid.

Unknown. Try to avoid “unknown”. Consider “error: unknown response”. If you don't know what the response is, how do you know it's erroneous? “Unrecognized” is often a better choice. Also, be sure to include the value being complained of.

Find vs. Exists. If the program uses a nontrivial algorithm to locate a resource (e.g., a path search) and that algorithm fails, it is fair to say that the program couldn't “find” the resource. If, on the other hand, the expected location of the resource is known but the program cannot access it there then say that the resource doesn't “exist”. Using “find” in this case sounds weak and confuses the issue.

May vs. Can vs. Might. “May” suggests permission (e.g., "You may borrow my rake."), and has little use in documentation or error messages. “Can” suggests ability (e.g., "I can lift that log."), and “might” suggests possibility (e.g., "It might rain today."). Using the proper word clarifies meaning and assists translation.

Contractions. Avoid contractions, like “can't”; use “cannot” instead.

Proper Spelling

Spell out words in full. For instance, avoid:

• spec

• stats

• parens

• auth

• xact

Rationale: This will improve consistency.

Localization

The view pg_stats provides access to the information stored in the catalog. This view allows access only to rows of pg_statistic that correspond to tables the user has permission to read, and therefore it is safe to allow public read access to this view.

pg_stats is also designed to present the information in a more readable format than the underlying catalog — at the cost that its schema must be extended whenever new slot types are defined for pg_statistic.

Table 51.88. pg_stats Columns

The maximum number of entries in the array fields can be controlled on a column-by-column basis using the ALTER TABLE SET STATISTICS command, or globally by setting the run-time parameter.

GiST stands for Generalized Search Tree. It is a balanced, tree-structured access method, that acts as a base template in which to implement arbitrary indexing schemes. B-trees, R-trees and many other indexing schemes can be implemented in GiST.

One advantage of GiST is that it allows the development of custom data types with the appropriate access methods, by an expert in the domain of the data type, rather than a database expert.

Some of the information here is derived from the University of California at Berkeley's GiST Indexing Project and Marcel Kornacker's thesis, . The GiST implementation in PostgreSQL is primarily maintained by Teodor Sigaev and Oleg Bartunov, and there is more information on their .

C Standard

Code in PostgreSQL should only rely on language features available in the C89 standard. That means a conforming C89 compiler has to be able to compile postgres, at least aside from a few platform dependent pieces. Features from later revision of the C standard or compiler specific features can be used, if a fallback is provided.

For example static inline and _StaticAssert() are currently used, even though they are from newer revisions of the C standard. If not available we respectively fall back to defining the functions without inline, and to using a C89 compatible replacement that performs the same checks, but emits rather cryptic messages.

Function-Like Macros and Inline Functions

Both, macros with arguments and static inline functions, may be used. The latter are preferable if there are multiple-evaluation hazards when written as a macro, as e.g. the case with

or when the macro would be very long. In other cases it's only possible to use macros, or at least easier. For example because expressions of various types need to be passed to the macro.

When the definition of an inline function references symbols (i.e. variables, functions) that are only available as part of the backend, the function may not be visible when included from frontend code.

In this example CurrentMemoryContext, which is only available in the backend, is referenced and the function thus hidden with a #ifndef FRONTEND. This rule exists because some compilers emit references to symbols contained in inline functions even if the function is not used.

Writing Signal Handlers

To be suitable to run inside a signal handler code has to be written very carefully. The fundamental problem is that, unless blocked, a signal handler can interrupt code at any time. If code inside the signal handler uses the same state as code outside chaos may ensue. As an example consider what happens if a signal handler tries to acquire a lock that's already held in the interrupted code.

Barring special arrangements code in signal handlers may only call async-signal safe functions (as defined in POSIX) and access variables of type volatile sig_atomic_t. A few functions in postgres are also deemed signal safe, importantly SetLatch().

In most cases signal handlers should do nothing more than note that a signal has arrived, and wake up code running outside of the handler using a latch. An example of such a handler is the following:

errno is saved and restored because SetLatch() might change it. If that were not done interrupted code that's currently inspecting errno might see the wrong value.

Calling Function Pointers

For clarity, it is preferred to explicitly dereference a function pointer when calling the pointed-to function if the pointer is a simple variable, for example:

(even though emit_log_hook(edata) would also work). When the function pointer is part of a structure, then the extra punctuation can and usually should be omitted, for example:

62.4.1. GiST buffering build

Building large GiST indexes by simply inserting all the tuples tends to be slow, because if the index tuples are scattered across the index and the index is large enough to not fit in cache, the insertions need to perform a lot of random I/O. Beginning in version 9.2, PostgreSQL supports a more efficient method to build GiST indexes based on buffering, which can dramatically reduce the number of random I/Os needed for non-ordered data sets. For well-ordered data sets the benefit is smaller or non-existent, because only a small number of pages receive new tuples at a time, and those pages fit in cache even if the index as whole does not.

However, buffering index build needs to call the penalty function more often, which consumes some extra CPU resources. Also, the buffers used in the buffering build need temporary disk space, up to the size of the resulting index. Buffering can also influence the quality of the resulting index, in both positive and negative directions. That influence depends on various factors, like the distribution of the input data and the operator class implementation.

By default, a GiST index build switches to the buffering method when the index size reaches . It can be manually turned on or off by the buffering parameter to the CREATE INDEX command. The default behavior is good for most cases, but turning buffering off might speed up the build somewhat if the input data is ordered.

This section describes the logical replication protocol, which is the message flow started by the START_REPLICATION SLOT slot_name LOGICAL replication command.

The logical streaming replication protocol builds on the primitives of the physical streaming replication protocol.

52.5.1. Logical Streaming Replication Parameters

The logical replication START_REPLICATION command accepts following parameters:proto_version

Protocol version. Currently only version 1 is supported.publication_names

Comma separated list of publication names for which to subscribe (receive changes). The individual publication names are treated as standard objects names and can be quoted the same as needed.

52.5.2. Logical Replication Protocol Messages

The individual protocol messages are discussed in the following subsections. Individual messages are described in .

All top-level protocol messages begin with a message type byte. While represented in code as a character, this is a signed byte with no associated encoding.

Since the streaming replication protocol supplies a message length there is no need for top-level protocol messages to embed a length in their header.

52.5.3. Logical Replication Protocol Message Flow

With the exception of the START_REPLICATION command and the replay progress messages, all information flows only from the backend to the frontend.

The logical replication protocol sends individual transactions one by one. This means that all messages between a pair of Begin and Commit messages belong to the same transaction.

Every sent transaction contains zero or more DML messages (Insert, Update, Delete). In case of a cascaded setup it can also contain Origin messages. The origin message indicated that the transaction originated on different replication node. Since a replication node in the scope of logical replication protocol can be pretty much anything, the only identifier is the origin name. It's downstream's responsibility to handle this as needed (if needed). The Origin message is always sent before any DML messages in the transaction.

Every DML message contains an arbitrary relation ID, which can be mapped to an ID in the Relation messages. The Relation messages describe the schema of the given relation. The Relation message is sent for a given relation either because it is the first time we send a DML message for given relation in the current session or because the relation definition has changed since the last Relation message was sent for it. The protocol assumes that the client is capable of caching the metadata for as many relations as needed.

All calls to functions that are written in a language other than the current “version 1” interface for compiled languages (this includes functions in user-defined procedural languages and functions written in SQL) go through a call handler function for the specific language. It is the responsibility of the call handler to execute the function in a meaningful way, such as by interpreting the supplied source text. This chapter outlines how a new procedural language's call handler can be written.

The call handler for a procedural language is a “normal” function that must be written in a compiled language such as C, using the version-1 interface, and registered with PostgreSQL as taking no arguments and returning the type language_handler. This special pseudo-type identifies the function as a call handler and prevents it from being called directly in SQL commands. For more details on C language calling conventions and dynamic loading, see .

The call handler is called in the same way as any other function: It receives a pointer to a FunctionCallInfoData struct containing argument values and information about the called function, and it is expected to return aDatum result (and possibly set the isnull field of the FunctionCallInfoData structure, if it wishes to return an SQL null result). The difference between a call handler and an ordinary callee function is that the flinfo->fn_oidfield of the FunctionCallInfoData structure will contain the OID of the actual function to be called, not of the call handler itself. The call handler must use this field to determine which function to execute. Also, the passed argument list has been set up according to the declaration of the target function, not of the call handler.

It's up to the call handler to fetch the entry of the function from the pg_proc system catalog and to analyze the argument and return types of the called function. The AS clause from the CREATE FUNCTION command for the function will be found in the prosrc column of the pg_proc row. This is commonly source text in the procedural language, but in theory it could be something else, such as a path name to a file, or anything else that tells the call handler what to do in detail.

Often, the same function is called many times per SQL statement. A call handler can avoid repeated lookups of information about the called function by using the flinfo->fn_extra field. This will initially be NULL, but can be set by the call handler to point at information about the called function. On subsequent calls, if flinfo->fn_extra is already non-NULL then it can be used and the information lookup step skipped. The call handler must make sure that flinfo->fn_extra is made to point at memory that will live at least until the end of the current query, since an FmgrInfo data structure could be kept that long. One way to do this is to allocate the extra data in the memory context specified by flinfo->fn_mcxt; such data will normally have the same lifespan as the FmgrInfo itself. But the handler could also choose to use a longer-lived memory context so that it can cache function definition information across queries.

When a procedural-language function is invoked as a trigger, no arguments are passed in the usual way, but the FunctionCallInfoData's context field points at a TriggerData structure, rather than being NULL as it is in a plain function call. A language handler should provide mechanisms for procedural-language functions to get at the trigger information.

This is a template for a procedural-language handler written in C:

Only a few thousand lines of code have to be added instead of the dots to complete the call handler.

If a validator is provided by a procedural language, it must be declared as a function taking a single parameter of type oid. The validator's result is ignored, so it is customarily declared to return void. The validator will be called at the end of a CREATE FUNCTION command that has created or updated a function written in the procedural language. The passed-in OID is the OID of the function's pg_proc row. The validator must fetch this row in the usual way, and do whatever checking is appropriate. First, call CheckFunctionValidatorAccess() to diagnose explicit calls to the validator that the user could not achieve through CREATE FUNCTION. Typical checks then include verifying that the function's argument and result types are supported by the language, and that the function's body is syntactically correct in the language. If the validator finds the function to be okay, it should just return. If it finds an error, it should report that via the normal ereport() error reporting mechanism. Throwing an error will force a transaction rollback and thus prevent the incorrect function definition from being committed.

If an inline handler is provided by a procedural language, it must be declared as a function taking a single parameter of type internal. The inline handler's result is ignored, so it is customarily declared to return void. The inline handler will be called when a DO statement is executed specifying the procedural language. The parameter actually passed is a pointer to an InlineCodeBlock struct, which contains information about the DOstatement's parameters, in particular the text of the anonymous code block to be executed. The inline handler should execute this code and return.

The core PostgreSQL distribution includes the GiST operator classes shown in . (Some of the optional modules described in provide additional GiST operator classes.)

Table 62.1. Built-in GiST Operator Classes

For historical reasons, the inet_ops operator class is not the default class for types inet and cidr. To use it, mention the class name in CREATE INDEX, for example

Traditionally, implementing a new index access method meant a lot of difficult work. It was necessary to understand the inner workings of the database, such as the lock manager and Write-Ahead Log. The GiST interface has a high level of abstraction, requiring the access method implementer only to implement the semantics of the data type being accessed. The GiST layer itself takes care of concurrency, logging and searching the tree structure.

This extensibility should not be confused with the extensibility of the other standard search trees in terms of the data they can handle. For example, PostgreSQL supports extensible B-trees and hash indexes. That means that you can use PostgreSQL to build a B-tree or hash over any data type you want. But B-trees only support range predicates (<, =, >), and hash indexes only support equality queries.

So if you index, say, an image collection with a PostgreSQL B-tree, you can only issue queries such as “is imagex equal to imagey”, “is imagex less than imagey” and “is imagex greater than imagey”. Depending on how you define “equals”, “less than” and “greater than” in this context, this could be useful. However, by using a GiST based index, you could create ways to ask domain-specific questions, perhaps “find all images of horses” or “find all over-exposed images”.

All it takes to get a GiST access method up and running is to implement several user-defined methods, which define the behavior of keys in the tree. Of course these methods have to be pretty fancy to support fancy queries, but for all the standard queries (B-trees, R-trees, etc.) they're relatively straightforward. In short, GiST combines extensibility along with generality, code reuse, and a clean interface.

There are seven methods that an index operator class for GiST must provide, and two that are optional. Correctness of the index is ensured by proper implementation of the same,consistent and union methods, while efficiency (size and speed) of the index will depend on the penalty and picksplit methods. The remaining two basic methods are compress and decompress, which allow an index to have internal tree data of a different type than the data it indexes. The leaves are to be of the indexed data type, while the other tree nodes can be of any C struct (but you still have to follow PostgreSQL data type rules here, see about varlena for variable sized data). If the tree's internal data type exists at the SQL level, theSTORAGE option of the CREATE OPERATOR CLASS command can be used. The optional eighth method is distance, which is needed if the operator class wishes to support ordered scans (nearest-neighbor searches). The optional ninth method fetch is needed if the operator class wishes to support index-only scans.consistent

Given an index entry p and a query value q, this function determines whether the index entry is “consistent” with the query; that is, could the predicate “indexed_columnindexable_operator q” be true for any row represented by the index entry? For a leaf index entry this is equivalent to testing the indexable condition, while for an internal tree node this determines whether it is necessary to scan the subtree of the index represented by the tree node. When the result is true, a recheck flag must also be returned. This indicates whether the predicate is certainly true or only possibly true. If recheck = false then the index has tested the predicate condition exactly, whereas if recheck = true the row is only a candidate match. In that case the system will automatically evaluate the indexable_operator against the actual row value to see if it is really a match. This convention allows GiST to support both lossless and lossy index structures.

The SQL declaration of the function must look like this:

And the matching code in the C module could then follow this skeleton:

Here, key is an element in the index and query the value being looked up in the index. The StrategyNumber parameter indicates which operator of your operator class is being applied — it matches one of the operator numbers in the CREATE OPERATOR CLASS command.

Depending on which operators you have included in the class, the data type of query could vary with the operator, since it will be whatever type is on the righthand side of the operator, which might be different from the indexed data type appearing on the lefthand side. (The above code skeleton assumes that only one type is possible; if not, fetching the query argument value would have to depend on the operator.) It is recommended that the SQL declaration of the consistent function use the opclass's indexed data type for the query argument, even though the actual type might be something else depending on the operator.union

This method consolidates information in the tree. Given a set of entries, this function generates a new index entry that represents all the given entries.

The SQL declaration of the function must look like this:

And the matching code in the C module could then follow this skeleton:

As you can see, in this skeleton we're dealing with a data type where union(X, Y, Z) = union(union(X, Y), Z). It's easy enough to support data types where this is not the case, by implementing the proper union algorithm in this GiST support method.

The result of the union function must be a value of the index's storage type, whatever that is (it might or might not be different from the indexed column's type). The unionfunction should return a pointer to newly palloc()ed memory. You can't just return the input value as-is, even if there is no type change.

As shown above, the union function's first internal argument is actually a GistEntryVector pointer. The second argument is a pointer to an integer variable, which can be ignored. (It used to be required that the union function store the size of its result value into that variable, but this is no longer necessary.)compress

Converts the data item into a format suitable for physical storage in an index page.

The SQL declaration of the function must look like this:

And the matching code in the C module could then follow this skeleton:

You have to adapt compressed_data_type to the specific type you're converting to in order to compress your leaf nodes, of course.decompress

The reverse of the compress method. Converts the index representation of the data item into a format that can be manipulated by the other GiST methods in the operator class.

The SQL declaration of the function must look like this:

And the matching code in the C module could then follow this skeleton:

The above skeleton is suitable for the case where no decompression is needed.penalty

Returns a value indicating the “cost” of inserting the new entry into a particular branch of the tree. Items will be inserted down the path of least penalty in the tree. Values returned by penalty should be non-negative. If a negative value is returned, it will be treated as zero.

The SQL declaration of the function must look like this:

And the matching code in the C module could then follow this skeleton:

For historical reasons, the penalty function doesn't just return a float result; instead it has to store the value at the location indicated by the third argument. The return value per se is ignored, though it's conventional to pass back the address of that argument.

The penalty function is crucial to good performance of the index. It'll get used at insertion time to determine which branch to follow when choosing where to add the new entry in the tree. At query time, the more balanced the index, the quicker the lookup.picksplit

When an index page split is necessary, this function decides which entries on the page are to stay on the old page, and which are to move to the new page.

The SQL declaration of the function must look like this:

And the matching code in the C module could then follow this skeleton:

Notice that the picksplit function's result is delivered by modifying the passed-in v structure. The return value per se is ignored, though it's conventional to pass back the address of v.

Like penalty, the picksplit function is crucial to good performance of the index. Designing suitable penalty and picksplit implementations is where the challenge of implementing well-performing GiST indexes lies.same

Returns true if two index entries are identical, false otherwise. (An “index entry” is a value of the index's storage type, not necessarily the original indexed column's type.)

The SQL declaration of the function must look like this:

And the matching code in the C module could then follow this skeleton:

For historical reasons, the same function doesn't just return a Boolean result; instead it has to store the flag at the location indicated by the third argument. The return value per se is ignored, though it's conventional to pass back the address of that argument.distance

Given an index entry p and a query value q, this function determines the index entry's “distance” from the query value. This function must be supplied if the operator class contains any ordering operators. A query using the ordering operator will be implemented by returning index entries with the smallest “distance” values first, so the results must be consistent with the operator's semantics. For a leaf index entry the result just represents the distance to the index entry; for an internal tree node, the result must be the smallest distance that any child entry could have.

The SQL declaration of the function must look like this:

And the matching code in the C module could then follow this skeleton:

The arguments to the distance function are identical to the arguments of the consistent function.

Some approximation is allowed when determining the distance, so long as the result is never greater than the entry's actual distance. Thus, for example, distance to a bounding box is usually sufficient in geometric applications. For an internal tree node, the distance returned must not be greater than the distance to any of the child nodes. If the returned distance is not exact, the function must set *recheck to true. (This is not necessary for internal tree nodes; for them, the calculation is always assumed to be inexact.) In this case the executor will calculate the accurate distance after fetching the tuple from the heap, and reorder the tuples if necessary.

If the distance function returns *recheck = true for any leaf node, the original ordering operator's return type must be float8 or float4, and the distance function's result values must be comparable to those of the original ordering operator, since the executor will sort using both distance function results and recalculated ordering-operator results. Otherwise, the distance function's result values can be any finite float8 values, so long as the relative order of the result values matches the order returned by the ordering operator. (Infinity and minus infinity are used internally to handle cases such as nulls, so it is not recommended that distance functions return these values.)fetch

Converts the compressed index representation of a data item into the original data type, for index-only scans. The returned data must be an exact, non-lossy copy of the originally indexed value.

The SQL declaration of the function must look like this:

The argument is a pointer to a GISTENTRY struct. On entry, its key field contains a non-NULL leaf datum in compressed form. The return value is another GISTENTRY struct, whose key field contains the same datum in its original, uncompressed form. If the opclass's compress function does nothing for leaf entries, the fetch method can return the argument as-is.

The matching code in the C module could then follow this skeleton:

If the compress method is lossy for leaf entries, the operator class cannot support index-only scans, and must not define a fetch function.

All the GiST support methods are normally called in short-lived memory contexts; that is, CurrentMemoryContext will get reset after each tuple is processed. It is therefore not very important to worry about pfree'ing everything you palloc. However, in some cases it's useful for a support method to cache data across repeated calls. To do that, allocate the longer-lived data in fcinfo->flinfo->fn_mcxt, and keep a pointer to it in fcinfo->flinfo->fn_extra. Such data will survive for the life of the index operation (e.g., a single GiST index scan, index build, or index tuple insertion). Be careful to pfree the previous value when replacing a fn_extra value, or the leak will accumulate for the duration of the operation.

Error, warning, and log messages generated within the server code should be created using ereport, or its older cousin elog. The use of this function is complex enough to require some explanation.

There are two required elements for every message: a severity level (ranging from DEBUG to PANIC) and a primary message text. In addition there are optional elements, the most common of which is an error identifier code that follows the SQL spec's SQLSTATE conventions. ereport itself is just a shell function, that exists mainly for the syntactic convenience of making message generation look like a function call in the C source code. The only parameter accepted directly by ereport is the severity level. The primary message text and any optional message elements are generated by calling auxiliary functions, such as errmsg, within the ereport call.

A typical call to ereport might look like this:

This specifies error severity level ERROR (a run-of-the-mill error). The errcode call specifies the SQLSTATE error code using a macro defined in src/include/utils/errcodes.h. The errmsg call provides the primary message text. Notice the extra set of parentheses surrounding the auxiliary function calls — these are annoying but syntactically necessary.

Here is a more complex example:

This illustrates the use of format codes to embed run-time values into a message text. Also, an optional “hint” message is provided.

If the severity level is ERROR or higher, ereport aborts the execution of the user-defined function and does not return to the caller. If the severity level is lower than ERROR, ereport returns normally.

The available auxiliary routines for ereport are:

• errcode(sqlerrcode) specifies the SQLSTATE error identifier code for the condition. If this routine is not called, the error identifier defaults to ERRCODE_INTERNAL_ERROR when the error severity level is ERROR or higher, ERRCODE_WARNING when the error level is WARNING, otherwise (for NOTICE and below) ERRCODE_SUCCESSFUL_COMPLETION. While these defaults are often convenient, always think whether they are appropriate before omitting the errcode() call.

• errmsg(const char *msg, ...) specifies the primary error message text, and possibly run-time values to insert into it. Insertions are specified by sprintf-style format codes. In addition to the standard format codes accepted by sprintf, the format code %m can be used to insert the error message returned by strerror for the current value of errno. %m does not require any corresponding entry in the parameter list for errmsg. Note that the message string will be run through gettext for possible localization before format codes are processed.

• errmsg_internal(const char *msg, ...) is the same as errmsg, except that the message string will not be translated nor included in the internationalization message dictionary. This should be used for “cannot happen” cases that are probably not worth expending translation effort on.

• errmsg_plural(const char *fmt_singular, const char *fmt_plural, unsigned long n, ...) is like errmsg, but with support for various plural forms of the message. fmt_singular is the English singular format, fmt_plural is the English plural format, n is the integer value that determines which plural form is needed, and the remaining arguments are formatted according to the selected format string. For more information see .

• errdetail(const char *msg, ...) supplies an optional “detail” message; this is to be used when there is additional information that seems inappropriate to put in the primary message. The message string is processed in just the same way as for errmsg.

• errdetail_internal(const char *msg, ...) is the same as errdetail, except that the message string will not be translated nor included in the internationalization message dictionary. This should be used for detail messages that are not worth expending translation effort on, for instance because they are too technical to be useful to most users.

• errdetail_plural(const char *fmt_singular, const char *fmt_plural, unsigned long n, ...) is like errdetail, but with support for various plural forms of the message. For more information see .

• errdetail_log(const char *msg, ...) is the same as errdetail except that this string goes only to the server log, never to the client. If both errdetail (or one of its equivalents above) and errdetail_log are used then one string goes to the client and the other to the log. This is useful for error details that are too security-sensitive or too bulky to include in the report sent to the client.

• errdetail_log_plural(const char *fmt_singular, const char *fmt_plural, unsigned long n, ...) is like errdetail_log, but with support for various plural forms of the message. For more information see .

• errhint(const char *msg, ...) supplies an optional “hint” message; this is to be used when offering suggestions about how to fix the problem, as opposed to factual details about what went wrong. The message string is processed in just the same way as for errmsg.

• errcontext(const char *msg, ...) is not normally called directly from an ereport message site; rather it is used in error_context_stack callback functions to provide information about the context in which an error occurred, such as the current location in a PL function. The message string is processed in just the same way as for errmsg. Unlike the other auxiliary functions, this can be called more than once per ereport call; the successive strings thus supplied are concatenated with separating newlines.

• errposition(int cursorpos) specifies the textual location of an error within a query string. Currently it is only useful for errors detected in the lexical and syntactic analysis phases of query processing.

• errtable(Relation rel) specifies a relation whose name and schema name should be included as auxiliary fields in the error report.

• errtablecol(Relation rel, int attnum) specifies a column whose name, table name, and schema name should be included as auxiliary fields in the error report.

• errtableconstraint(Relation rel, const char *conname) specifies a table constraint whose name, table name, and schema name should be included as auxiliary fields in the error report. Indexes should be considered to be constraints for this purpose, whether or not they have an associated pg_constraint entry. Be careful to pass the underlying heap relation, not the index itself, as rel.

• errdatatype(Oid datatypeOid) specifies a data type whose name and schema name should be included as auxiliary fields in the error report.

• errdomainconstraint(Oid datatypeOid, const char *conname) specifies a domain constraint whose name, domain name, and schema name should be included as auxiliary fields in the error report.

• errcode_for_file_access() is a convenience function that selects an appropriate SQLSTATE error identifier for a failure in a file-access-related system call. It uses the saved errno to determine which error code to generate. Usually this should be used in combination with %m in the primary error message text.

• errcode_for_socket_access() is a convenience function that selects an appropriate SQLSTATE error identifier for a failure in a socket-related system call.

• errhidestmt(bool hide_stmt) can be called to specify suppression of the STATEMENT: portion of a message in the postmaster log. Generally this is appropriate if the message text includes the current statement already.

• errhidecontext(bool hide_ctx) can be called to specify suppression of the CONTEXT: portion of a message in the postmaster log. This should only be used for verbose debugging messages where the repeated inclusion of context would bloat the log volume too much.

Note

At most one of the functions errtable, errtablecol, errtableconstraint, errdatatype, or errdomainconstraint should be used in an ereport call. These functions exist to allow applications to extract the name of a database object associated with the error condition without having to examine the potentially-localized error message text. These functions should be used in error reports for which it's likely that applications would wish to have automatic error handling. As of PostgreSQL 9.3, complete coverage exists only for errors in SQLSTATE class 23 (integrity constraint violation), but this is likely to be expanded in future.

There is an older function elog that is still heavily used. An elog call:

is exactly equivalent to:

Notice that the SQLSTATE error code is always defaulted, and the message string is not subject to translation. Therefore, elog should be used only for internal errors and low-level debug logging. Any message that is likely to be of interest to ordinary users should go through ereport. Nonetheless, there are enough internal “cannot happen” error checks in the system that elog is still widely used; it is preferred for those messages for its notational simplicity.

The core PostgreSQL distribution includes the SP-GiST operator classes shown in .

Table 63.1. Built-in SP-GiST Operator Classes

Of the two operator classes for type point, quad_point_ops is the default. kd_point_ops supports the same operators but uses a different index data structure which may offer better performance in some applications.

This section covers implementation details and other tricks that are useful for implementers of SP-GiST operator classes to know.

63.4.1. SP-GiST Limits

Individual leaf tuples and inner tuples must fit on a single index page (8kB by default). Therefore, when indexing values of variable-length data types, long values can only be supported by methods such as radix trees, in which each level of the tree includes a prefix that is short enough to fit on a page, and the final leaf level includes a suffix also short enough to fit on a page. The operator class should set longValuesOK to TRUE only if it is prepared to arrange for this to happen. Otherwise, the SP-GiST core will reject any request to index a value that is too large to fit on an index page.

Likewise, it is the operator class's responsibility that inner tuples do not grow too large to fit on an index page; this limits the number of child nodes that can be used in one inner tuple, as well as the maximum size of a prefix value.

Another limitation is that when an inner tuple's node points to a set of leaf tuples, those tuples must all be in the same index page. (This is a design decision to reduce seeking and save space in the links that chain such tuples together.) If the set of leaf tuples grows too large for a page, a split is performed and an intermediate inner tuple is inserted. For this to fix the problem, the new inner tuple must divide the set of leaf values into more than one node group. If the operator class's picksplit function fails to do that, the SP-GiST core resorts to extraordinary measures described in .

63.4.2. SP-GiST Without Node Labels

Some tree algorithms use a fixed set of nodes for each inner tuple; for example, in a quad-tree there are always exactly four nodes corresponding to the four quadrants around the inner tuple's centroid point. In such a case the code typically works with the nodes by number, and there is no need for explicit node labels. To suppress node labels (and thereby save some space), the picksplit function can return NULL for the nodeLabels array, and likewise the choose function can return NULL for the prefixNodeLabels array during a spgSplitTupleaction. This will in turn result in nodeLabels being NULL during subsequent calls to choose and inner_consistent. In principle, node labels could be used for some inner tuples and omitted for others in the same index.

When working with an inner tuple having unlabeled nodes, it is an error for choose to return spgAddNode, since the set of nodes is supposed to be fixed in such cases.

63.4.3. “All-the-same” Inner Tuples

The SP-GiST core can override the results of the operator class's picksplit function when picksplit fails to divide the supplied leaf values into at least two node categories. When this happens, the new inner tuple is created with multiple nodes that each have the same label (if any) that picksplit gave to the one node it did use, and the leaf values are divided at random among these equivalent nodes. The allTheSame flag is set on the inner tuple to warn the choose and inner_consistent functions that the tuple does not have the node set that they might otherwise expect.

When dealing with an allTheSame tuple, a choose result of spgMatchNode is interpreted to mean that the new value can be assigned to any of the equivalent nodes; the core code will ignore the supplied nodeN value and descend into one of the nodes at random (so as to keep the tree balanced). It is an error for choose to return spgAddNode, since that would make the nodes not all equivalent; the spgSplitTuple action must be used if the value to be inserted doesn't match the existing nodes.

When dealing with an allTheSame tuple, the inner_consistent function should return either all or none of the nodes as targets for continuing the index search, since they are all equivalent. This may or may not require any special-case code, depending on how much the inner_consistent function normally assumes about the meaning of the nodes.

The PostgreSQL source distribution includes several examples of index operator classes for SP-GiST, as described in . Look into src/backend/access/spgist/ and src/backend/utils/adt/ to see the code.

Create vs. insert

由於可能為每個項目插入了許多索引鍵，因此插入 GIN 索引可能會很慢。因此，對於批次插入表格，建議在完成批次插入後刪除 GIN 索引並重新建立。

從 PostgreSQL 8.4 開始，由於使用了延遲索引，因此此建議不太必要（詳見）。但對於非常大的更新，還是最好刪除並重新建立索引。

GIN 索引的建構時間對 maintenance_work_mem 設定非常敏感；在建立索引期間，不需要花費工作記憶體。

在一系列插入已啟用 fastupdate 的現有 GIN 索引期間，只要列表大於 gin_pending_list_limit，系統就會清理待處理項目列表。為了避免觀察的回應時間波動，希望在背景進行待處理列表清理（即透過 autovacuum）。透過增加 gin_pending_list_limit 或使 autovacuum 更積極，可以減少手動清理操作。但是，擴大清理操作的閾值意味著如果確實發生了手動清理，則需要更長時間。

可以透過變更儲存參數來覆蓋各個 GIN 索引的 gin_pending_list_limit，並允許每個 GIN 索引具有自己的清理閾值。例如，可以僅為可以大量更新的 GIN 索引增加閾值，否則可以減少閾值。

開發 GIN 索引的主要目標是在 PostgreSQL 中建立對可高度延展的全文檢索支援，並且通常情況下全文檢索會回傳非常大的結果集合。然而，當查詢包含非常頻繁的單詞時，通常會發生這種情況，因此大型結果集甚至不起作用。由於從磁碟讀取許多 tuple 並對它們進行排序可能需要花費大量時間，因此這對於產品環境來說是不可接受的。（請注意，索引搜尋本身非常快。）

為了便於控制執行此類查詢，GIN 對回傳的資料列數量有一個可配置的軟性上限：gin_fuzzy_search_limit 配置參數。預設設定為 0（表示無限制）。如果設定了非零的限制，則回傳的集合是整個結果集合的子集，隨機選擇。

「軟性上限」表示回傳結果的實際數量可能與指定的限制略有不同，具體取決於查詢和系統隨機數産生器的情況。

從經驗來看，數千以上的值（例如 5000 - 20000）是比較好的範圍。

GIN 代表 Generalized Inverted Index。GIN 設計用於處理要被索引的項目是複合值的情況，並且由索引處理的查詢需要搜索出現在複合項目內的元素值。例如，這些項目可能是文件，查詢可能是搜索包含特定單詞的文件。

我們使用單詞 item 來引用要編入索引的複合值，並使用單詞索引鍵（word key）來引用元素值。GIN 總是儲存和搜索索引鍵，而不是其值。

GIN 索引儲存一組（key, posting list）對，其中 posting list 是 key 對應的一組資料列 ID。同一資料列 ID 可以出現在多個 posting list 當中，因為一個項目可以包含多個關鍵字。每個索引鍵值只儲存一次，因此對於相同鍵出現多次的情況，GIN 索引非常會緊湊。

GIN 是泛用的，因為 GIN 存取方法的語法不需要知道它加速的具體操作。相反地，它使用為特定資料型別定義的自訂策略。該策略定義瞭瞭如何從索引項目和查詢條件中提取關鍵字，以及如何確定包含查詢中某些關鍵值的資料列能夠實際滿足查詢。

GIN 的一個優點是，它允許由資料型別領域的專家而不是資料庫專家使用適當的存取方法開發自訂的資料型別。這與使用 GiST 的優點相同。

PostgreSQL 中的 GIN 實現偏主要由 Teodor Sigaev 和 Oleg Bartunov 維護。在他們的上有更多關於 GIN 的訊息。

主要的 PostgreSQL 版本包括 中所示的 GIN 運算子類。（ 中描述的一些選用套件提供了額外的 GIN 運算子類。）

Table 64.1. Built-in GIN Operator Classes

在 jsonb 型別的兩個運算子類中，jsonb_ops 是預設值。jsonb_path_ops 支援較少的運算子，但為這些運算子提供了更好的效能。有關詳細訊息，請參閱。

SP-GiST offers an interface with a high level of abstraction, requiring the access method developer to implement only methods specific to a given data type. The SP-GiST core is responsible for efficient disk mapping and searching the tree structure. It also takes care of concurrency and logging considerations.

Leaf tuples of an SP-GiST tree contain values of the same data type as the indexed column. Leaf tuples at the root level will always contain the original indexed data value, but leaf tuples at lower levels might contain only a compressed representation, such as a suffix. In that case the operator class support functions must be able to reconstruct the original value using information accumulated from the inner tuples that are passed through to reach the leaf level.

Inner tuples are more complex, since they are branching points in the search tree. Each inner tuple contains a set of one or more nodes, which represent groups of similar leaf values. A node contains a downlink that leads either to another, lower-level inner tuple, or to a short list of leaf tuples that all lie on the same index page. Each node normally has a label that describes it; for example, in a radix tree the node label could be the next character of the string value. (Alternatively, an operator class can omit the node labels, if it works with a fixed set of nodes for all inner tuples; see .) Optionally, an inner tuple can have a prefix value that describes all its members. In a radix tree this could be the common prefix of the represented strings. The prefix value is not necessarily really a prefix, but can be any data needed by the operator class; for example, in a quad-tree it can store the central point that the four quadrants are measured with respect to. A quad-tree inner tuple would then also contain four nodes corresponding to the quadrants around this central point.

Some tree algorithms require knowledge of level (or depth) of the current tuple, so the SP-GiST core provides the possibility for operator classes to manage level counting while descending the tree. There is also support for incrementally reconstructing the represented value when that is needed, and for passing down additional data (called traverse values) during a tree descent.

Note

The SP-GiST core code takes care of null entries. Although SP-GiST indexes do store entries for nulls in indexed columns, this is hidden from the index operator class code: no null index entries or search conditions will ever be passed to the operator class methods. (It is assumed that SP-GiST operators are strict and so cannot succeed for null values.) Null values are therefore not discussed further here.

There are five user-defined methods that an index operator class for SP-GiST must provide. All five follow the convention of accepting two internal arguments, the first of which is a pointer to a C struct containing input values for the support method, while the second argument is a pointer to a C struct where output values must be placed. Four of the methods just return void, since all their results appear in the output struct; but leaf_consistent additionally returns a boolean result. The methods must not modify any fields of their input structs. In all cases, the output struct is initialized to zeroes before calling the user-defined method.

The five user-defined methods are:config

Returns static information about the index implementation, including the data type OIDs of the prefix and node label data types.

The SQL declaration of the function must look like this:

The first argument is a pointer to a spgConfigIn C struct, containing input data for the function. The second argument is a pointer to a spgConfigOut C struct, which the function must fill with result data.

attType is passed in order to support polymorphic index operator classes; for ordinary fixed-data-type operator classes, it will always have the same value and so can be ignored.

Chooses a method for inserting a new value into an inner tuple.

The SQL declaration of the function must look like this:

The first argument is a pointer to a spgChooseIn C struct, containing input data for the function. The second argument is a pointer to a spgChooseOut C struct, which the function must fill with result data.

The choose function can determine either that the new value matches one of the existing child nodes, or that a new child node must be added, or that the new value is inconsistent with the tuple prefix and so the inner tuple must be split to create a less restrictive prefix.

If the new value matches one of the existing child nodes, set resultType to spgMatchNode. Set nodeN to the index (from zero) of that node in the node array. Set levelAdd to the increment in level caused by descending through that node, or leave it as zero if the operator class does not use levels. Set restDatum to equal datum if the operator class does not modify datums from one level to the next, or otherwise set it to the modified value to be used as leafDatum at the next level.

If a new child node must be added, set resultType to spgAddNode. Set nodeLabel to the label to be used for the new node, and set nodeN to the index (from zero) at which to insert the node in the node array. After the node has been added, the choose function will be called again with the modified inner tuple; that call should result in an spgMatchNode result.

If the new value is inconsistent with the tuple prefix, set resultType to spgSplitTuple. This action moves all the existing nodes into a new lower-level inner tuple, and replaces the existing inner tuple with a tuple having a single downlink pointing to the new lower-level inner tuple. Set prefixHasPrefix to indicate whether the new upper tuple should have a prefix, and if so set prefixPrefixDatum to the prefix value. This new prefix value must be sufficiently less restrictive than the original to accept the new value to be indexed. Set prefixNNodes to the number of nodes needed in the new tuple, and set prefixNodeLabels to a palloc'd array holding their labels, or to NULL if node labels are not required. Note that the total size of the new upper tuple must be no more than the total size of the tuple it is replacing; this constrains the lengths of the new prefix and new labels. Set childNodeN to the index (from zero) of the node that will downlink to the new lower-level inner tuple. Set postfixHasPrefix to indicate whether the new lower-level inner tuple should have a prefix, and if so set postfixPrefixDatum to the prefix value. The combination of these two prefixes and the downlink node's label (if any) must have the same meaning as the original prefix, because there is no opportunity to alter the node labels that are moved to the new lower-level tuple, nor to change any child index entries. After the node has been split, the choose function will be called again with the replacement inner tuple. That call may return an spgAddNode result, if no suitable node was created by the spgSplitTuple action. Eventually choose must return spgMatchNode to allow the insertion to descend to the next level.picksplit

Decides how to create a new inner tuple over a set of leaf tuples.

The SQL declaration of the function must look like this:

The first argument is a pointer to a spgPickSplitIn C struct, containing input data for the function. The second argument is a pointer to a spgPickSplitOut C struct, which the function must fill with result data.

nTuples is the number of leaf tuples provided. datums is an array of their datum values. level is the current level that all the leaf tuples share, which will become the level of the new inner tuple.

Set hasPrefix to indicate whether the new inner tuple should have a prefix, and if so set prefixDatum to the prefix value. Set nNodes to indicate the number of nodes that the new inner tuple will contain, and set nodeLabels to an array of their label values, or to NULL if node labels are not required. Set mapTuplesToNodes to an array that gives the index (from zero) of the node that each leaf tuple should be assigned to. Set leafTupleDatums to an array of the values to be stored in the new leaf tuples (these will be the same as the input datums if the operator class does not modify datums from one level to the next). Note that the picksplit function is responsible for palloc'ing the nodeLabels,mapTuplesToNodes and leafTupleDatums arrays.

Returns set of nodes (branches) to follow during tree search.

The SQL declaration of the function must look like this:

The first argument is a pointer to a spgInnerConsistentIn C struct, containing input data for the function. The second argument is a pointer to a spgInnerConsistentOut C struct, which the function must fill with result data.

nNodes must be set to the number of child nodes that need to be visited by the search, and nodeNumbers must be set to an array of their indexes. If the operator class keeps track of levels, set levelAdds to an array of the level increments required when descending to each node to be visited. (Often these increments will be the same for all the nodes, but that's not necessarily so, so an array is used.) If value reconstruction is needed, set reconstructedValues to an array of the values reconstructed for each child node to be visited; otherwise, leave reconstructedValues as NULL. If it is desired to pass down additional out-of-band information (“traverse values”) to lower levels of the tree search, settraversalValues to an array of the appropriate traverse values, one for each child node to be visited; otherwise, leave traversalValues as NULL. Note that the inner_consistentfunction is responsible for palloc'ing the nodeNumbers, levelAdds, reconstructedValues, and traversalValues arrays in the current memory context. However, any output traverse values pointed to by the traversalValues array should be allocated in traversalMemoryContext. Each traverse value must be a single palloc'd chunk.leaf_consistent

Returns true if a leaf tuple satisfies a query.

The SQL declaration of the function must look like this:

The first argument is a pointer to a spgLeafConsistentIn C struct, containing input data for the function. The second argument is a pointer to a spgLeafConsistentOut C struct, which the function must fill with result data.

The array scankeys, of length nkeys, describes the index search condition(s). These conditions are combined with AND — only index entries that satisfy all of them satisfy the query. (Note that nkeys = 0 implies that all index entries satisfy the query.) Usually the consistent function only cares about the sk_strategy and sk_argument fields of each array entry, which respectively give the indexable operator and comparison value. In particular it is not necessary to check sk_flags to see if the comparison value is NULL, because the SP-GiST core code will filter out such conditions. reconstructedValue is the value reconstructed for the parent tuple; it is (Datum) 0 at the root level or if the inner_consistentfunction did not provide a value at the parent level. traversalValue is a pointer to any traverse data passed down from the previous call of inner_consistent on the parent index tuple, or NULL at the root level. level is the current leaf tuple's level, starting at zero for the root level. returnData is true if reconstructed data is required for this query; this will only be so if the config function asserted canReturnData. leafDatum is the key value stored in the current leaf tuple.

The function must return true if the leaf tuple matches the query, or false if not. In the true case, if returnData is true then leafValue must be set to the value originally supplied to be indexed for this leaf tuple. Also, recheck may be set to true if the match is uncertain and so the operator(s) must be re-applied to the actual heap tuple to verify the match.

All the SP-GiST support methods are normally called in a short-lived memory context; that is, CurrentMemoryContext will be reset after processing of each tuple. It is therefore not very important to worry about pfree'ing everything you palloc. (The config method is an exception: it should try to avoid leaking memory. But usually the config method need do nothing but assign constants into the passed parameter struct.)

If the indexed column is of a collatable data type, the index collation will be passed to all the support methods, using the standard PG_GET_COLLATION() mechanism.

在內部，GIN 索引包含在索引鍵上建構的 B-tree 索引，其中每個索引鍵是一個或多個索引項目（例如，陣列的成員）元素，並且葉結點頁面中的每個 tuple 包含指向 heap 指標的 B-tree（“posting tree”）或 heap 指標的簡易列表（“posting list”），其列表足夠小以能與其索引鍵值放進單個索引 tuple。

從 PostgreSQL 9.1 開始，null 索引鍵值可以包含在索引中。此外，placeholder null 值也包含在索引項目中，該索引項目根據 extractValue 的結果判斷為 null 或不包含任何鍵值。這可以進行空項目的搜索。

透過在複合值（欄位號碼，鍵值）上建構單個 B-tree 來實現多欄位 GIN 索引。不同欄位的鍵值可以是不同型別。

64.4.1. GIN 快速更新技術

由於反向索引的固有特性，更新 GIN 索引往往會很慢：插入或更新一個 heap 資料列會導致許多項目插入到索引中（每個索引鍵從索引項目中提取一個）。從 PostgreSQL 8.4 開始，GIN 能夠透過將新的 tuple 插入臨時的未排序待處理條目列表來延遲大部分工作。當資料表被清理或自動分析時，或者當呼叫 gin_clean_pending_list 函數時，又或者待處理列表變得大於 時，使用在初始索引建立期間使用的相同批次插入技術將項目移動到主要的 GIN 資料結構。這大大提升了 GIN 索引更新速度，甚至可以計算額外的清理開銷。此外，額外的工作可以通過背景程序而不是前端查詢處理來完成。

這種方法的主要缺點是除了搜尋一般索引之外，還必須掃描待處理項目列表，因此大量待處理項目將顯著拖慢搜尋速度。另一個缺點是，雖然大多數更新都很快，但是導致待處理列表變得「太大」的更新將導致觸發立即性清理工作，因此比其他更新慢得多。正確使用 autovacuum 可以儘可能地減少這些問題。

如果一致回應時間比更新速度更重要，則可以透過關閉 GIN 索引的 fastupdate 儲存參數來停用待處理項目的使用。有關詳細訊息，請參閱 。

64.4.2. Partial Match Algorithm

GIN可以支援「部分匹配」查詢，其中查詢不確定一個或多個索引鍵能完全匹配，但可能的匹配屬於相當窄的索引鍵值範圍（以比較支持法確定的索引鍵排序順序））。 extractQuery 方法不是回傳要精確匹配的索引鍵值，而是回傳某鍵值，該鍵值是搜尋範圍的下限，並將 pmatch 標示設定為 true。然後使用 comparePartial 方法掃描關鍵的範圍。comparePartial 讓能匹配的索引鍵回傳零，對於仍在搜尋範圍內的非匹配小於零，或者如果索引鍵超出可匹配的範圍則大於零。

The GIN interface has a high level of abstraction, requiring the access method implementer only to implement the semantics of the data type being accessed. The GIN layer itself takes care of concurrency, logging and searching the tree structure.

All it takes to get a GIN access method working is to implement a few user-defined methods, which define the behavior of keys in the tree and the relationships between keys, indexed items, and indexable queries. In short, GIN combines extensibility with generality, code reuse, and a clean interface.

There are two methods that an operator class for GIN must provide:Datum *extractValue(Datum itemValue, int32 *nkeys, bool **nullFlags)

Returns a palloc'd array of keys given an item to be indexed. The number of returned keys must be stored into *nkeys. If any of the keys can be null, also palloc an array of *nkeys bool fields, store its address at *nullFlags, and set these null flags as needed.*nullFlags can be left NULL (its initial value) if all keys are non-null. The return value can be NULL if the item contains no keys.Datum *extractQuery(Datum query, int32 *nkeys, StrategyNumber n, bool **pmatch, Pointer **extra_data, bool **nullFlags, int32 *searchMode)

Returns a palloc'd array of keys given a value to be queried; that is, query is the value on the right-hand side of an indexable operator whose left-hand side is the indexed column. n is the strategy number of the operator within the operator class (see ). Often, extractQuery will need to consult n to determine the data type of query and the method it should use to extract key values. The number of returned keys must be stored into *nkeys. If any of the keys can be null, also palloc an array of *nkeys bool fields, store its address at *nullFlags, and set these null flags as needed. *nullFlags can be left NULL (its initial value) if all keys are non-null. The return value can be NULL if the query contains no keys.

searchMode is an output argument that allows extractQuery to specify details about how the search will be done. If *searchMode is set to GIN_SEARCH_MODE_DEFAULT (which is the value it is initialized to before call), only items that match at least one of the returned keys are considered candidate matches. If *searchMode is set to GIN_SEARCH_MODE_INCLUDE_EMPTY, then in addition to items containing at least one matching key, items that contain no keys at all are considered candidate matches. (This mode is useful for implementing is-subset-of operators, for example.) If *searchMode is set to GIN_SEARCH_MODE_ALL, then all non-null items in the index are considered candidate matches, whether they match any of the returned keys or not. (This mode is much slower than the other two choices, since it requires scanning essentially the entire index, but it may be necessary to implement corner cases correctly. An operator that needs this mode in most cases is probably not a good candidate for a GIN operator class.) The symbols to use for setting this mode are defined in access/gin.h.

pmatch is an output argument for use when partial match is supported. To use it, extractQuery must allocate an array of *nkeys booleans and store its address at *pmatch. Each element of the array should be set to TRUE if the corresponding key requires partial match, FALSE if not. If *pmatch is set to NULL then GIN assumes partial match is not required. The variable is initialized to NULL before call, so this argument can simply be ignored by operator classes that do not support partial match.

extra_data is an output argument that allows extractQuery to pass additional data to the consistent and comparePartial methods. To use it, extractQuery must allocate an array of *nkeys pointers and store its address at *extra_data, then store whatever it wants to into the individual pointers. The variable is initialized to NULL before call, so this argument can simply be ignored by operator classes that do not require extra data. If *extra_data is set, the whole array is passed to the consistent method, and the appropriate element to the comparePartial method.

An operator class must also provide a function to check if an indexed item matches the query. It comes in two flavors, a boolean consistent function, and a ternary triConsistent function. triConsistent covers the functionality of both, so providing triConsistent alone is sufficient. However, if the boolean variant is significantly cheaper to calculate, it can be advantageous to provide both. If only the boolean variant is provided, some optimizations that depend on refuting index items before fetching all the keys are disabled.bool consistent(bool check[], StrategyNumber n, Datum query, int32 nkeys, Pointer extra_data[], bool *recheck, Datum queryKeys[], bool nullFlags[])

Returns TRUE if an indexed item satisfies the query operator with strategy number n (or might satisfy it, if the recheck indication is returned). This function does not have direct access to the indexed item's value, since GIN does not store items explicitly. Rather, what is available is knowledge about which key values extracted from the query appear in a given indexed item. The check array has length nkeys, which is the same as the number of keys previously returned by extractQuery for this query datum. Each element of the check array is TRUE if the indexed item contains the corresponding query key, i.e., if (check[i] == TRUE) the i-th key of the extractQuery result array is present in the indexed item. The original query datum is passed in case the consistent method needs to consult it, and so are the queryKeys[] and nullFlags[] arrays previously returned by extractQuery. extra_data is the extra-data array returned by extractQuery, or NULL if none.

When extractQuery returns a null key in queryKeys[], the corresponding check[] element is TRUE if the indexed item contains a null key; that is, the semantics of check[] are like IS NOT DISTINCT FROM. The consistent function can examine the corresponding nullFlags[] element if it needs to tell the difference between a regular value match and a null match.

On success, *recheck should be set to TRUE if the heap tuple needs to be rechecked against the query operator, or FALSE if the index test is exact. That is, a FALSE return value guarantees that the heap tuple does not match the query; a TRUE return value with *recheck set to FALSE guarantees that the heap tuple does match the query; and a TRUE return value with *recheck set to TRUE means that the heap tuple might match the query, so it needs to be fetched and rechecked by evaluating the query operator directly against the originally indexed item.GinTernaryValue triConsistent(GinTernaryValue check[], StrategyNumber n, Datum query, int32 nkeys, Pointer extra_data[], Datum queryKeys[], bool nullFlags[])

triConsistent is similar to consistent, but instead of booleans in the check vector, there are three possible values for each key: GIN_TRUE, GIN_FALSE and GIN_MAYBE. GIN_FALSE and GIN_TRUE have the same meaning as regular boolean values, while GIN_MAYBE means that the presence of that key is not known. When GIN_MAYBE values are present, the function should only return GIN_TRUE if the item certainly matches whether or not the index item contains the corresponding query keys. Likewise, the function must return GIN_FALSEonly if the item certainly does not match, whether or not it contains the GIN_MAYBE keys. If the result depends on the GIN_MAYBE entries, i.e., the match cannot be confirmed or refuted based on the known query keys, the function must return GIN_MAYBE.

When there are no GIN_MAYBE values in the check vector, a GIN_MAYBE return value is the equivalent of setting the recheck flag in the boolean consistent function.

In addition, GIN must have a way to sort the key values stored in the index. The operator class can define the sort ordering by specifying a comparison method:int compare(Datum a, Datum b)

Compares two keys (not indexed items!) and returns an integer less than zero, zero, or greater than zero, indicating whether the first key is less than, equal to, or greater than the second. Null keys are never passed to this function.

Alternatively, if the operator class does not provide a compare method, GIN will look up the default btree operator class for the index key data type, and use its comparison function. It is recommended to specify the comparison function in a GIN operator class that is meant for just one data type, as looking up the btree operator class costs a few cycles. However, polymorphic GIN operator classes (such as array_ops) typically cannot specify a single comparison function.

Optionally, an operator class for GIN can supply the following method:int comparePartial(Datum partial_key, Datum key, StrategyNumber n, Pointer extra_data)

Compare a partial-match query key to an index key. Returns an integer whose sign indicates the result: less than zero means the index key does not match the query, but the index scan should continue; zero means that the index key does match the query; greater than zero indicates that the index scan should stop because no more matches are possible. The strategy number n of the operator that generated the partial match query is provided, in case its semantics are needed to determine when to end the scan. Also, extra_data is the corresponding element of the extra-data array made by extractQuery, or NULL if none. Null keys are never passed to this function.

The actual data types of the various Datum values mentioned above vary depending on the operator class. The item values passed to extractValue are always of the operator class's input type, and all key values must be of the class's STORAGE type. The type of the queryargument passed to extractQuery, consistent and triConsistent is whatever is the right-hand input type of the class member operator identified by the strategy number. This need not be the same as the indexed type, so long as key values of the correct type can be extracted from it. However, it is recommended that the SQL declarations of these three support functions use the opclass's indexed data type for the query argument, even though the actual type might be something else depending on the operator.

The core PostgreSQL distribution includes the GIN operator classes previously shown in . The following contrib modules also contain GIN operator classes:

btree_gin

B-tree equivalent functionality for several data types

hstore

Module for storing (key, value) pairs

intarray

Enhanced support for int[]

pg_trgm

Text similarity using trigram matching

The core PostgreSQL distribution includes the BRIN operator classes shown in .

The minmax operator classes store the minimum and the maximum values appearing in the indexed column within the range. The inclusion operator classes store a value which includes the values in the indexed column within the range.

Table 65.1. Built-in BRIN Operator Classes

本節概述了 TOAST（The Oversized-Attribute Storage Technique，超大型屬性儲存技術）。

PostgreSQL 使用固定的頁面大小（通常為 8 kB），並且不允許 tuple 跨越多個頁面。 因此，不可能直接儲存非常大的字串。為了克服這個限制，將大字串壓縮和分解成多個實體資料列。這對使用者而言是無感的，對大多數後端程式碼的影響很小。該技術被親切地稱為 TOAST（或「切片麵包以來最好的東西」）。TOAST 基礎結構還用於改進記憶體中大資料值的處理。

只有某些資料型別支援 TOAST - 不需要對無法産生大字串的資料型別增加成本。為了支援 TOAST，資料型別必須具有可變長度（varlena）表示，其中，通常，任何儲存值的第一個 4 bytes 包含以 byte 為單位的總長度（包括其自身）。TOAST不會限制資料型別表示的其餘部分。統稱為 TOASTed 的特殊值表示透過修改或重新解釋此初始長度字來起作用。因此，支援 TOAST-able 資料型別的 C 語言函數必須注意它們如何處理可能的 TOASTed 輸入值：輸入實際上可能不包含 4 bytes 長度的字和內容，直到它被解除 TOAST。（這通常透過在對輸入值執行任何操作之前呼叫 PG_DETOAST_DATUM 來完成，但在某些情況下可以採用更有效的方法。有關更多詳細訊息，請參閱。）

TOAST 使用 varlena 長度的兩位元（big-endian 機器上的高位元，little-endian 機器上的低位元），從而將 TOAST-able 資料型別的任何值的邏輯大小限制為 1 GB。當兩個位元都為零時，該值是資料型別的普通值非 TOAST，長度位元組的其餘位以位元組為單位記錄總資料大小（包括長度位元組）。當設定最高位或最低位時，該值只有一個單位元組標頭而不是普通的四位元組標頭，該位元組的其餘位元表示以位元組為單位的總資料大小（包括長度位元組） 。此額外的方案支援空間高效率儲存短於 127 位元組的值，同時仍允許資料型別在需要時增長到 1 GB。具有單位元組標頭的值不在任何特定邊界上對齊，而具有四位元組標頭的值在至少四位元組邊界上對齊；與短值相比，這種省略對齊填充提供了額外的空間節省。作為特殊情況，如果單位元組標頭的剩餘位全部為零（對於自包含長度而言這是不可能的），則該值是指向外部資料的指標，具有如所描述的幾種可能的替代方案，如下所示。這種 TOAST 指標的型別和大小由儲存在資料的第二個位元組中的代碼決定。最後，當最高位元或最低位元清除為零但相鄰位置時，資料的內容已被壓縮，必須在使用前解壓縮。在這種情況下，四位元組長度字的剩餘位表示壓縮資料的總大小，而不是原始資料。請注意，對於外部資料也可以進行壓縮，但 varlena 標頭不會告訴它是否已經發生 - 而 TOAST 指標的內容則說明這件事。

如上所述，有多種類型的 TOAST 指標基準。最舊和最常見的類型是指向儲存在 TOAST 資料表中的外部資料的指標，該資料表與包含 TOAST 指標資料本身的資料表分開但與之相關聯。當要儲存在磁碟上的 tuple 太大而無法按原樣儲存時，這些磁碟指標基準由 TOAST 管理代碼（在 access/heap/tuptoaster.c 中）建立。更多細節見。或者，TOAST 指標資料可以包含指向出現在記憶體中其他位置外部資料的指標。這些資料必然是短暫的，並且永遠不會出現在磁碟上，但它們對於避免複製和冗餘處理大量資料值非常有用。更多細節見。

用於壓縮資料的壓縮技術是 LZ 系列壓縮技術中相當簡單且非常快速的方法。有關詳細訊息，請參閱 src/common/pg_lzcompress.c。

66.2.1. Out-of-line, on-disk TOAST storage

如果資料表的任何欄位都是可以 TOAST 的，則該資料表將擁有關連的 TOAST 資料表，其 OID 儲存在資料表的 pg_class.reltoastrelid 項目中。磁盤上 TOAST 後的值保留在 TOAST 資料表中，下面將有更詳細的描述。

將 out-of-line 的內容（在壓縮後使用）分割為最多 TOAST_MAX_CHUNK_SIZE 個字元的區塊（預設情況下，選擇此值使得四個區塊的資料列行剛好放進一個 page，大約為 2000 個字元）。每個區塊都屬於其所有資料表的 TOAST 資料表中單獨的資料列來儲存。每個 TOAST 資料表都有欄位的 chunk_id（識別特定有 TOAST 值的 OID），chunk_seq（其值中區塊的序列號）和 chunk_data（區塊的實際資料）。chunk_id 和 chunk_seq 上的唯一索引提供了對內容的快速檢索。表示線上磁碟 TOAST 值的指標資料需要儲存要查看的 TOAST 資料表 OID 以及特定值的 OID（其chunk_id）。為方便起見，指標 datum 還儲存邏輯上的 datum 大小（原始未壓縮字串長度）和實際上的儲存大小（如果套用了壓縮則會不同）。因此，允許 varlena 標頭字元，磁碟 TOAST 指標資料的總大小為 18 個位元組，不論其所表示字串大小。

僅當要儲存在資料表中的資料列內容大於 TOAST_TUPLE_THRESHOLD 字元（通常為2 kB）時，才會觸發 TOAST 機制。TOAST 程式將會壓縮或移動字串內容，直到資料列小於 TOAST_TUPLE_TARGET 個字元（通常也是 2 kB）或者不能再獲得更多的增益。在 UPDATE 操作期間，未變更字串的內容通常就保持原樣；因此，如果沒有任何 out-of-line 需要變更，則具有 out-of-line 的資料列更新就不會產生任何 TOAST 成本。

TOAST 機制識別用於在磁碟上儲存可 TOAST 欄位有四種不同策略：

• PLAIN 可防止壓縮或 out-of-line 儲存方式；此外，它禁止使用 varlena 類型的單字元標頭。對於非 TOAST-capable 資料型別欄位，這是唯一可行的策略。

• EXTENDED 允許壓縮和 out-of-line 儲存。這是大多數 TOAST-capable 資料型別的預設方式。首先嘗試壓縮，然後在資料列仍然太大的情況下進行 out-of-line 儲存。

• EXTERNAL 允許 out-of-line 儲存但不允許壓縮。使用 EXTERNAL 將使大量文字和 bytea 欄位上的子字串操作更快（以增加的儲存空間為代價），因為這些操作被最佳化為在未壓縮時僅獲取 out-of-line 內容所需的部分。

• MAIN 允許壓縮但不允許 out-of-line 儲存。（實際上，仍然會為這些欄位執行 out-of-line 儲存，但只有在沒有其他方法使資料列足夠小到適合頁面時才做的最後手段。）

每個 TOAST-able 資料型別會為該型別的欄位指定預設策略，但是可以使用 變更指定資料表欄位的策略。

與更直覺的方法（例如允許資料列內容跨越頁面）相比，此方案具有許多優點。假設查詢通常透過與相對較小的鍵值進行比較來過濾，執行程序的大部分工作將使用主要欄位完成。 TOASTed 屬性的大量內容只會在結果集發送到用戶端時被取出（如果選中的話）。因此，與沒有任何外部儲存的情況相比，主要資料表更小並且其更多資料列置於共享緩衝區高速處理。排序集合也會縮小，而排序通常完全在記憶體中完成。一個小小的測試顯示，包含典型 HTML 頁面及其 URL 的資料儲存在大約一半的原始資料大小（包括 TOAST 資料表）中，並且主要資料表僅包含大約 10％ 的內容（URL 和一些小的 HTML）。與未轉換的相比，並沒有執行時間差異，其中所有 HTML 頁面都被削減到 7 kB 以適應頁面。

66.2.2. Out-of-line, in-memory TOAST storage

TOAST pointers can point to data that is not on disk, but is elsewhere in the memory of the current server process. Such pointers obviously cannot be long-lived, but they are nonetheless useful. There are currently two sub-cases: pointers to indirect data and pointers to expanded data.

Indirect TOAST pointers simply point at a non-indirect varlena value stored somewhere in memory. This case was originally created merely as a proof of concept, but it is currently used during logical decoding to avoid possibly having to create physical tuples exceeding 1 GB (as pulling all out-of-line field values into the tuple might do). The case is of limited use since the creator of the pointer datum is entirely responsible that the referenced data survives for as long as the pointer could exist, and there is no infrastructure to help with this.

Expanded TOAST pointers are useful for complex data types whose on-disk representation is not especially suited for computational purposes. As an example, the standard varlena representation of a PostgreSQL array includes dimensionality information, a nulls bitmap if there are any null elements, then the values of all the elements in order. When the element type itself is variable-length, the only way to find the N'th element is to scan through all the preceding elements. This representation is appropriate for on-disk storage because of its compactness, but for computations with the array it's much nicer to have an “expanded” or “deconstructed” representation in which all the element starting locations have been identified. The TOAST pointer mechanism supports this need by allowing a pass-by-reference Datum to point to either a standard varlena value (the on-disk representation) or a TOAST pointer that points to an expanded representation somewhere in memory. The details of this expanded representation are up to the data type, though it must have a standard header and meet the other API requirements given in src/include/utils/expandeddatum.h. C-level functions working with the data type can choose to handle either representation. Functions that do not know about the expanded representation, but simply apply PG_DETOAST_DATUMto their inputs, will automatically receive the traditional varlena representation; so support for an expanded representation can be introduced incrementally, one function at a time.

TOAST pointers to expanded values are further broken down into read-write and read-only pointers. The pointed-to representation is the same either way, but a function that receives a read-write pointer is allowed to modify the referenced value in-place, whereas one that receives a read-only pointer must not; it must first create a copy if it wants to make a modified version of the value. This distinction and some associated conventions make it possible to avoid unnecessary copying of expanded values during query execution.

For all types of in-memory TOAST pointer, the TOAST management code ensures that no such pointer datum can accidentally get stored on disk. In-memory TOAST pointers are automatically expanded to normal in-line varlena values before storage — and then possibly converted to on-disk TOAST pointers, if the containing tuple would otherwise be too big.