PostgreSQL內建支援智慧式日期時間輸入格式。日期及時間以字串格式輸入，以預先定義的方式，分隔為多個欄位。每一個欄位可能會被解譯為數字、忽視或拒絕。處理程式也內建常見的描述字，如月份、星期及時區名稱。

本文件說明日期及時間解譯的方法和步驟，也包含了常見描述字的列表。

• https://www.postgresql.org/docs/10/static/datetime-appendix.html

由於時區縮寫並沒有很好地標準化，PostgreSQL 提供了一種自訂的伺服器接受的縮寫集方法。timezone_abbreviations 運行時參數決定有效的縮寫集。雖然此參數可由任何資料庫使用者變更，但其可能的值受資料庫管理員控制 - 實際上它們是儲存在安裝目錄的 .../share/timezonesets/ 中的組態檔案的名稱。透過增加或變更該目錄中的檔案，管理員可以為時區縮寫設定本地策略。

timezone_abbreviations 可以設定為在 .../share/timezonesets/ 中找到的任何檔案名稱，檔案的名稱需要完全是英文字母的。（禁止 timezone_abbreviations 中的非英文字母字元可以防止讀取目標目錄之外的檔案，以及讀取編輯器備份檔案和其他無關檔案。）

時區縮寫檔案可以包含空白行和以 # 開頭的註釋。非註釋行必須具有以下格式之一：

zone_abbreviation offset
zone_abbreviation offset D
zone_abbreviation time_zone_name
@INCLUDE file_name
@OVERRIDE

zone_abbreviation 只是定義的縮寫。offset 是一個整數，定義與 UTC 相等的偏移量，以秒為單位，正向格林威治為東，負向為西。例如，-18000 將在格林威治以西 5 小時或北美東海岸標準時間。D 的區域名稱表示本地夏令時間而不是標準時間。

或者，可以定義 time_zone_name，引用 IANA 時區資料庫中定義的區域名稱。查詢區域的定義以查看該區域中是否正在使用縮寫，如果是，則使用適當的含義 - 即，目前正在確定其值的時間戳記中使用的含義，或者如果當時不是目前使用的話，則使用之前的含義，如果僅在該時間之後使用，則使用最舊的含義。這種行為對於處理其含義歷史變化的縮寫至關重要。還允許根據區域名稱定義縮寫，其中不出現該縮寫；使用縮寫就等於寫出區域名稱。

小技巧 在定義與 UTC 的偏移量從未改變的縮寫時，偏好使用簡單的整數偏移量，因為這些縮寫比需要查閱時區定義的縮寫要簡單得多。

@INCLUDE 語法允許在 .../share/timezonesets/ 目錄中包含另一個檔案。包含可以嵌套到某個有限的深度。

@OVERRIDE 語法指示檔案中的後續項目可以覆蓋先前的項目（通常是從包含的檔案中獲取的項目）。如果沒有這個，相同時區縮寫的衝突定義將被視為錯誤。

在未修改的安裝環境中，檔案 Default 包含了世界上大多數地區的所有非衝突時區縮寫。為這些區域提供了澳大利亞和印度的附加檔案：這些檔案先有預設設定，然後根據需要增加或修改縮寫。

出於參考目的，標準安裝還包含 Africa.txt，America.txt 等文件，其中包含有關根據 IANA 時區資料庫已知正在使用的每個時區縮寫的訊息。可以根據需要將這些檔案中找到的區域名稱定義複製並貼到自行定義配置檔案中。請注意，由於名稱中包含了點，因此無法將這些檔案直接引用為 timezone_abbreviations 設定。

注意

如果在讀取時區縮寫集時發生錯誤，則不會應用新值並保留舊值。如果在啟動資料庫時發生錯誤，則啟動失敗。

配置檔案中定義的時區縮寫會覆寫 PostgreSQL 中內建的非時區定義。例如，澳大利亞配置檔案定義了 SAT（南澳大利亞標準時間）。當此檔案有效時，SAT 將不會被識別為星期六的縮寫。

如果您修改 .../share/timezonesets/ 中的檔案，則由您來進行備份 - 正常的資料庫轉存將不包含此目錄。

日期時間資料將會以下列流程解譯：（分隔符號均為半型文字）

1. 以分隔符號將其分解為多個段落，如字串、時區或數字。

   1. 如果是以冒號（:）分隔的數字格式，那麼這是一個時間的字串，其所包括的內容都是時間資訊的一部份。

   2. 如果是以連字號（-）、斜線（/）、或兩個以上的間隔號（.）所分隔的數字格式，那麼這是一個日期的字串，它可能包含文字型式的月份名稱。但如果日期分隔符號已經先出現了，那麼它將被解釋為時區資訊（例如：Asia/Taipei）。

   3. 如果整個字串都是數字所組成，那麼它可能是符合ISO 8601格式的日期（例如：19990113，表示西元1999年1月3日），或時間（例如：141516，表示14:15:16）。

   4. 如果它是以加號（+）或減號（-）開頭的話，那麼它是一個數字型態的時區資訊或是特別的區間。

2. 如果是一個字串，進行下列比對規則：

   1. 以binary-search表格，尋找時區的表示字。

   2. 如果沒有找到的話，則搜尋慣用字（如：today），星期（如：Thursday），月份（如：January），或介系詞（如：at, on）。

   3. 如果都沒有找到，就回傳錯誤訊息。

3. 如果是一個由數字組成的字串，則進行下列判斷：

   1. 如果是8個或6個數字，而先前也沒有讀到其他日期的資訊，那麼它會被解譯為一個數字型態的日期（如：19990118或990118），對應年月日格式為YYYYMMDD或YYMMDD。

   2. 如果是3位數字，而且先前已處理到年份資訊的話，那麼它會被解譯為該年的第幾天。

   3. 如果是4位或6位數字，而且先前已處理到年份資訊的話，那麼它會被解譯為時間資訊，對應格式為HHMM或HHMMSS。

   4. 如果是3個或更多的數字，並且先前未處理到日期資訊的話，那麼它會是年份資訊。（這里將直接判斷為yy-mm-dd的日期格式。）

   5. 最後，日期格式將依DateStyle所定義的，設定為：mm-dd-yy，dd-mm-yy，或yy-mm-dd。其中如果月份或日子名稱無法找到合法字詞的話，那將會回傳錯誤的訊息。

4. 如果指定了BC（西元前）的話，那麼實際儲存值將是負的年份數再加1。（陽曆Gregorian year中並無西元0年，所以西元1年，以數值0作為年份的記錄值。）

5. 沒有指定BC的2位數年份，將自動被調整為4位數。其規則為：若小於70，則加2000作為其記錄值，否則就加1900作為記錄值。

小技巧

• 若需要描述西元（Gregorian years AD） 1-99年，請將年份數值以0補滿4位數（例如：0099表西元99年）。

• https://www.postgresql.org/docs/10/static/datetime-input-rules.html

The release notes contain the significant changes in each PostgreSQL release, with major features and migration issues listed at the top. The release notes do not contain changes that affect only a few users or changes that are internal and therefore not user-visible. For example, the optimizer is improved in almost every release, but the improvements are usually observed by users as simply faster queries.

A complete list of changes for each release can be obtained by viewing the Git logs for each release. The pgsql-committers email list records all source code changes as well. There is also a web interface that shows changes to specific files.

The name appearing next to each item represents the major developer for that item. Of course all changes involve community discussion and patch review, so each item is truly a community effort.

Release date: 2021-05-13

This release contains a variety of fixes from 13.2. For information about new features in major release 13, see Section E.4.

E.1.1. Migration to Version 13.3

A dump/restore is not required for those running 13.X.

However, if you are upgrading from a version earlier than 13.2, see Section E.2.

E.1.2. Changes

• Prevent integer overflows in array subscripting calculations (Tom Lane)

  The array code previously did not complain about cases where an array's lower bound plus length overflows an integer. This resulted in later entries in the array becoming inaccessible (since their subscripts could not be written as integers), but more importantly it confused subsequent assignment operations. This could lead to memory overwrites, with ensuing crashes or unwanted data modifications. (CVE-2021-32027)

• Fix mishandling of “junk” columns in INSERT ... ON CONFLICT ... UPDATE target lists (Tom Lane)

  If the UPDATE list contains any multi-column sub-selects (which give rise to junk columns in addition to the results proper), the UPDATE path would end up storing tuples that include the values of the extra junk columns. That's fairly harmless in the short run, but if new columns are added to the table then the values would become accessible, possibly leading to malfunctions if they don't match the datatypes of the added columns.

  In addition, in versions supporting cross-partition updates, a cross-partition update triggered by such a case had the reverse problem: the junk columns were removed from the target list, typically causing an immediate crash due to malfunction of the multi-column sub-select mechanism. (CVE-2021-32028)

• Fix possibly-incorrect computation of UPDATE ... RETURNING outputs for joined cross-partition updates (Amit Langote, Etsuro Fujita)

  If an UPDATE for a partitioned table caused a row to be moved to another partition with a physically different row type (for example, one with a different set of dropped columns), computation of RETURNING results for that row could produce errors or wrong answers. No error is observed unless the UPDATE involves other tables being joined to the target table. (CVE-2021-32029)

• Fix adjustment of constraint deferrability properties in partitioned tables (Álvaro Herrera)

  When applied to a foreign-key constraint of a partitioned table, ALTER TABLE ... ALTER CONSTRAINT failed to adjust the DEFERRABLE and/or INITIALLY DEFERRED markings of the constraints and triggers of leaf partitions. This led to unexpected behavior of such constraints. After updating to this version, any misbehaving partitioned tables can be fixed by executing a new ALTER command to set the desired properties.

  This change also disallows applying such an ALTER directly to the constraints of leaf partitions. The only supported case is for the whole partitioning hierarchy to have identical constraint properties, so such ALTERs must be applied at the partition root.

• When attaching a child table with ALTER TABLE ... INHERIT, insist that any generated columns in the parent be generated the same way in the child (Peter Eisentraut)

• Forbid marking an identity column as nullable (Vik Fearing)

  GENERATED ALWAYS AS IDENTITY implies NOT NULL, so don't allow it to be combined with an explicit NULL specification.

• Allow ALTER ROLE/DATABASE ... SET to set the role, session_authorization, and temp_buffers parameters (Tom Lane)

  Previously, over-eager validity checks might reject these commands, even if the values would have worked when used later. This created a command ordering hazard for dump/reload and upgrade scenarios.

• Ensure that REINDEX CONCURRENTLY preserves any statistics target that's been set for the index (Michael Paquier)

• Fix COMMIT AND CHAIN to work correctly when the current transaction has live savepoints (Fujii Masao)

• Fix list-manipulation bug in WITH RECURSIVE processing (Michael Paquier, Tom Lane)

  Sufficiently deep nesting of WITH constructs (at least seven levels) triggered core dumps or incorrect complaints of faulty WITH nesting.

• Fix bug with coercing the result of a COLLATE expression to a non-collatable type (Tom Lane)

  This led to a parse tree in which the COLLATE appears to be applied to a non-collatable value. While that normally has no real impact (since COLLATE has no effect at runtime), it was possible to construct views that would be rejected during dump/reload.

• Fix use-after-free bug in saving tuples for AFTER triggers (Amit Langote)

  This could cause crashes in some situations.

• Disallow calling window functions and procedures via the “fast path” wire protocol message (Tom Lane)

  Only plain functions are supported here. While trying to call an aggregate function failed already, calling a window function would crash, and calling a procedure would work only if the procedure did no transaction control.

• Extend pg_identify_object_as_address() to support event triggers (Joel Jacobson)

• Fix to_char()'s handling of Roman-numeral month format codes with negative intervals (Julien Rouhaud)

  Previously, such cases would usually cause a crash.

• Check that the argument of pg_import_system_collations() is a valid schema OID (Tom Lane)

• Fix use of uninitialized value while parsing an \{m,n\} quantifier in a BRE-mode regular expression (Tom Lane)

  This error could cause the quantifier to act non-greedy, that is behave like an {m,n}? quantifier would do in full regular expressions.

• Fix “could not find pathkey item to sort” planner errors in some situations where the sort key involves an aggregate or window function (James Coleman, Tom Lane)

• Don't ignore system columns when estimating the number of groups using extended statistics (Tomas Vondra)

  This led to strange estimates for queries such as SELECT ... GROUP BY a, b, ctid.

• Avoid divide-by-zero when estimating selectivity of a regular expression with a very long fixed prefix (Tom Lane)

  This typically led to a NaN selectivity value, causing assertion failures or strange planner behavior.

• Fix access-off-the-end-of-the-table error in BRIN index bitmap scans (Tomas Vondra)

  If the page range size used by a BRIN index isn't a power of two, there were corner cases in which a bitmap scan could try to fetch pages past the actual end of the table, leading to “could not open file” errors.

• Fix potentially wrong answers from GIN tsvector index searches, when there are many matching tuples (Tom Lane)

  If the number of index matches became large enough to make the bitmap holding them become lossy (a threshold that depends on work_mem), the code could get confused about whether rechecks are required, allowing rows to be returned that don't actually match the query.

• Fix concurrency issues with WAL segment recycling on Windows (Michael Paquier)

  This reverts a change that caused intermittent “could not rename file ...: Permission denied” log messages. While there were not serious consequences, the log spam was annoying.

• Avoid incorrect timeline change while recovering uncommitted two-phase transactions from WAL (Soumyadeep Chakraborty, Jimmy Yih, Kevin Yeap)

  This error could lead to subsequent WAL records being written under the wrong timeline ID, leading to consistency problems, or even complete failure to be able to restart the server, later on.

• Ensure that locks are released while shutting down a standby server's startup process (Fujii Masao)

  When a standby server is shut down while still in recovery, some locks might be left held. This causes assertion failures in debug builds; it's unclear whether any serious consequence could occur in production builds.

• Fix crash when a logical replication worker does ALTER SUBSCRIPTION REFRESH (Peter Smith)

  The core code won't do this, but a replica trigger could.

• Ensure we default to wal_sync_method = fdatasync on recent FreeBSD (Thomas Munro)

  FreeBSD 13 supports open_datasync, which would normally become the default choice. However, it's unclear whether that is actually an improvement for Postgres, so preserve the existing default for now.

• Disable the vacuum_cleanup_index_scale_factor parameter and storage option (Peter Geoghegan)

  The notion of tracking “stale” index statistics proved to interact badly with the autovacuum_vacuum_insert_threshold parameter, resulting in unnecessary full-index scans and consequent degradation of autovacuum performance. The latter mechanism seems superior, so remove the stale-statistics logic. The control parameter for that, vacuum_cleanup_index_scale_factor, will be removed entirely in v14. In v13, it remains present to avoid breaking existing configuration files, but it no longer does anything.

• Pass the correct trigger OID to object post-alter hooks during ALTER CONSTRAINT (Álvaro Herrera)

  When updating trigger properties during ALTER CONSTRAINT, the post-alter hook was told that we are updating a trigger, but the constraint's OID was passed instead of the trigger's.

• Ensure we finish cleaning up when interrupted while detaching a DSM segment (Thomas Munro)

  This error could result in temporary files not being cleaned up promptly after a parallel query.

• Fix assorted minor memory leaks in the server (Tom Lane, Andres Freund)

• Fix uninitialized variable in walreceiver's statistics in shared memory (Fujii Masao)

  This error was harmless on most platforms, but could cause issues on platforms lacking atomic variables and/or spinlock support.

• Reduce the overhead of dtrace probes for LWLock operations, when dtrace support is compiled in but not active (Peter Eisentraut)

• Fix failure when a PL/pgSQL DO block makes use of both composite-type variables and transaction control (Tom Lane)

  Previously, such cases led to errors about leaked tuple descriptors.

• Prevent infinite loop in libpq if a ParameterDescription message with a corrupt length is received (Tom Lane)

• When initdb prints instructions about how to start the server, make the path shown for pg_ctl use backslash separators on Windows (Nitin Jadhav)

• Fix psql to restore the previous behavior of \connect service=something (Tom Lane)

  A previous bug fix caused environment variables (such as PGPORT) to override entries in the service file in this context. Restore the previous behavior, in which the priority is the other way around.

• Fix psql's ON_ERROR_ROLLBACK feature to handle COMMIT AND CHAIN commands correctly (Arthur Nascimento)

  Previously, this case failed with “savepoint "pg_psql_temporary_savepoint" does not exist”.

• In psql, avoid repeated “could not print result table” failures after the first such error (Álvaro Herrera)

• Fix race condition in detection of file modification by psql's \e and related commands (Laurenz Albe)

  A very fast typist could fool the code's file-timestamp-based detection of whether the temporary edit file was changed.

• Fix pg_dump's dumping of generated columns in partitioned tables (Peter Eisentraut)

  A fix introduced in the previous minor release should not be applied to partitioned tables, only traditionally-inherited tables.

• Fix missed file version check in pg_restore (Tom Lane)

  When reading a custom-format archive from a non-seekable source, pg_restore neglected to check the archive version. If it was fed a newer archive version than it can support, it would fail messily later on.

• Add some more checks to pg_upgrade for user tables containing non-upgradable data types (Tom Lane)

  Fix detection of some cases where a non-upgradable data type is embedded within a container type (such as an array or range). Also disallow upgrading when user tables contain columns of system-defined composite types, since those types' OIDs are not stable across versions.

• Fix incorrect progress-reporting calculation in pg_checksums (Shinya Kato)

• Fix pg_waldump to count XACT records correctly when generating per-record statistics (Kyotaro Horiguchi)

• Fix contrib/amcheck to not complain about the tuple flags HEAP_XMAX_LOCK_ONLY and HEAP_KEYS_UPDATED both being set (Julien Rouhaud)

  This is a valid state after SELECT FOR UPDATE.

• Adjust VPATH build rules to support recent Oracle Developer Studio compiler versions (Noah Misch)

• Fix testing of PL/Python for Python 3 on Solaris (Noah Misch)

Table C.1 lists all tokens that are key words in the SQL standard and in PostgreSQL 11devel. Background information can be found in Section 4.1.1. (For space reasons, only the latest two versions of the SQL standard, and SQL-92 for historical comparison, are included. The differences between those and the other intermediate standard versions are small.)

SQL distinguishes between reserved and non-reserved key words. According to the standard, reserved key words are the only real key words; they are never allowed as identifiers. Non-reserved key words only have a special meaning in particular contexts and can be used as identifiers in other contexts. Most non-reserved key words are actually the names of built-in tables and functions specified by SQL. The concept of non-reserved key words essentially only exists to declare that some predefined meaning is attached to a word in some contexts.

In the PostgreSQL parser life is a bit more complicated. There are several different classes of tokens ranging from those that can never be used as an identifier to those that have absolutely no special status in the parser as compared to an ordinary identifier. (The latter is usually the case for functions specified by SQL.) Even reserved key words are not completely reserved in PostgreSQL, but can be used as column labels (for example, SELECT 55 AS CHECK, even though CHECK is a reserved key word).

In Table C.1 in the column for PostgreSQL we classify as “non-reserved” those key words that are explicitly known to the parser but are allowed as column or table names. Some key words that are otherwise non-reserved cannot be used as function or data type names and are marked accordingly. (Most of these words represent built-in functions or data types with special syntax. The function or type is still available but it cannot be redefined by the user.) Labeled “reserved” are those tokens that are not allowed as column or table names. Some reserved key words are allowable as names for functions or data types; this is also shown in the table. If not so marked, a reserved key word is only allowed as an “AS” column label name.

As a general rule, if you get spurious parser errors for commands that contain any of the listed key words as an identifier you should try to quote the identifier to see if the problem goes away.

It is important to understand before studying Table C.1 that the fact that a key word is not reserved in PostgreSQL does not mean that the feature related to the word is not implemented. Conversely, the presence of a key word does not indicate the existence of a feature.

Table C.1. SQL Key Words

本節試圖描述 PostgreSQL 在相當程度上符合目前的 SQL 標準。以下內容可能並非是完整且一致性聲明，但它以合理且對使用者有意義的方式詳細介紹了相關的主題。

The formal name of the SQL standard is ISO/IEC 9075 “Database Language SQL”. A revised version of the standard is released from time to time; the most recent update appearing in 2016. The 2016 version is referred to as ISO/IEC 9075:2016, or simply as SQL:2016. The versions prior to that were SQL:2011, SQL:2008, SQL:2006, SQL:2003, SQL:1999, and SQL-92. Each version replaces the previous one, so claims of conformance to earlier versions have no official merit. PostgreSQL development aims for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense. Many of the features required by the SQL standard are supported, though sometimes with slightly differing syntax or function. Further moves towards conformance can be expected over time.

SQL-92 defined three feature sets for conformance: Entry, Intermediate, and Full. Most database management systems claiming SQL standard conformance were conforming at only the Entry level, since the entire set of features in the Intermediate and Full levels was either too voluminous or in conflict with legacy behaviors.

Starting with SQL:1999, the SQL standard defines a large set of individual features rather than the ineffectively broad three levels found in SQL-92. A large subset of these features represents the “Core” features, which every conforming SQL implementation must supply. The rest of the features are purely optional.

The standard versions beginning with SQL:2003 are also split into a number of parts. Each is known by a shorthand name. Note that these parts are not consecutively numbered.

• ISO/IEC 9075-1 Framework (SQL/Framework)

• ISO/IEC 9075-2 Foundation (SQL/Foundation)

• ISO/IEC 9075-3 Call Level Interface (SQL/CLI)

• ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM)

• ISO/IEC 9075-9 Management of External Data (SQL/MED)

• ISO/IEC 9075-10 Object Language Bindings (SQL/OLB)

• ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata)

• ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT)

• ISO/IEC 9075-14 XML-related specifications (SQL/XML)

• ISO/IEC 9075-15 Multi-dimensional arrays (SQL/MDA)

The PostgreSQL core covers parts 1, 2, 9, 11, and 14. Part 3 is covered by the ODBC driver, and part 13 is covered by the PL/Java plug-in, but exact conformance is currently not being verified for these components. There are currently no implementations of parts 4, 10, and 15 for PostgreSQL.

PostgreSQL supports most of the major features of SQL:2016. Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features. It might be worth noting that at the time of writing, no current version of any database management system claims full conformance to Core SQL:2016.

In the following two sections, we provide a list of those features that PostgreSQL supports, followed by a list of the features defined in SQL:2016 which are not yet supported in PostgreSQL. Both of these lists are approximate: There might be minor details that are nonconforming for a feature that is listed as supported, and large parts of an unsupported feature might in fact be implemented. The main body of the documentation always contains the most accurate information about what does and does not work.

Note

Feature codes containing a hyphen are subfeatures. Therefore, if a particular subfeature is not supported, the main feature is listed as unsupported even if some other subfeatures are supported.

Significant revisions to the XML-related specifications in ISO/IEC 9075-14 (SQL/XML) were introduced with SQL:2006. PostgreSQL's implementation of the XML data type and related functions largely follows the earlier 2003 edition, with some borrowing from later editions. In particular:

• Where the current standard provides a family of XML data types to hold “document” or “content” in untyped or XML Schema-typed variants, and a type XML(SEQUENCE) to hold arbitrary pieces of XML content, PostgreSQL provides the single xml type, which can hold “document” or “content”. There is no equivalent of the standard's “sequence” type.

• PostgreSQL provides two functions introduced in SQL:2006, but in variants that use the XPath 1.0 language, rather than XML Query as specified for them in the standard.

This section presents some of the resulting differences you may encounter.

D.3.1. Queries Are Restricted to XPath 1.0

The PostgreSQL-specific functions xpath() and xpath_exists() query XML documents using the XPath language. PostgreSQL also provides XPath-only variants of the standard functions XMLEXISTS and XMLTABLE, which officially use the XQuery language. For all of these functions, PostgreSQL relies on the libxml2 library, which provides only XPath 1.0.

There is a strong connection between the XQuery language and XPath versions 2.0 and later: any expression that is syntactically valid and executes successfully in both produces the same result (with a minor exception for expressions containing numeric character references or predefined entity references, which XQuery replaces with the corresponding character while XPath leaves them alone). But there is no such connection between these languages and XPath 1.0; it was an earlier language and differs in many respects.

There are two categories of limitation to keep in mind: the restriction from XQuery to XPath for the functions specified in the SQL standard, and the restriction of XPath to version 1.0 for both the standard and the PostgreSQL-specific functions.

D.3.1.1. Restriction Of XQuery To XPath

Features of XQuery beyond those of XPath include:

• XQuery expressions can construct and return new XML nodes, in addition to all possible XPath values. XPath can create and return values of the atomic types (numbers, strings, and so on) but can only return XML nodes that were already present in documents supplied as input to the expression.

• XQuery has control constructs for iteration, sorting, and grouping.

• XQuery allows declaration and use of local functions.

Recent XPath versions begin to offer capabilities overlapping with these (such as functional-style for-each and sort, anonymous functions, and parse-xml to create a node from a string), but such features were not available before XPath 3.0.

D.3.1.2. Restriction Of XPath To 1.0

For developers familiar with XQuery and XPath 2.0 or later, XPath 1.0 presents a number of differences to contend with:

• The fundamental type of an XQuery/XPath expression, the sequence, which can contain XML nodes, atomic values, or both, does not exist in XPath 1.0. A 1.0 expression can only produce a node-set (containing zero or more XML nodes), or a single atomic value.

• Unlike an XQuery/XPath sequence, which can contain any desired items in any desired order, an XPath 1.0 node-set has no guaranteed order and, like any set, does not allow multiple appearances of the same item.

  Note

  The libxml2 library does seem to always return node-sets to PostgreSQL with their members in the same relative order they had in the input document. Its documentation does not commit to this behavior, and an XPath 1.0 expression cannot control it.

• While XQuery/XPath provides all of the types defined in XML Schema and many operators and functions over those types, XPath 1.0 has only node-sets and the three atomic types boolean, double, and string.

• XPath 1.0 has no conditional operator. An XQuery/XPath expression such as if ( hat ) then hat/@size else "no hat" has no XPath 1.0 equivalent.

• XPath 1.0 has no ordering comparison operator for strings. Both "cat" < "dog" and "cat" > "dog" are false, because each is a numeric comparison of two NaNs. In contrast, = and != do compare the strings as strings.

• XPath 1.0 blurs the distinction between value comparisons and general comparisons as XQuery/XPath define them. Both sale/@hatsize = 7 and sale/@customer = "alice" are existentially quantified comparisons, true if there is any sale with the given value for the attribute, but sale/@taxable = false() is a value comparison to the effective boolean value of a whole node-set. It is true only if no sale has a taxable attribute at all.

• In the XQuery/XPath data model, a document node can have either document form (i.e., exactly one top-level element, with only comments and processing instructions outside of it) or content form (with those constraints relaxed). Its equivalent in XPath 1.0, the root node, can only be in document form. This is part of the reason an xml value passed as the context item to any PostgreSQL XPath-based function must be in document form.

The differences highlighted here are not all of them. In XQuery and the 2.0 and later versions of XPath, there is an XPath 1.0 compatibility mode, and the W3C lists of function library changes and language changes applied in that mode offer a more complete (but still not exhaustive) account of the differences. The compatibility mode cannot make the later languages exactly equivalent to XPath 1.0.

D.3.1.3. Mappings Between SQL And XML Data Types And Values

In SQL:2006 and later, both directions of conversion between standard SQL data types and the XML Schema types are specified precisely. However, the rules are expressed using the types and semantics of XQuery/XPath, and have no direct application to the different data model of XPath 1.0.

When PostgreSQL maps SQL data values to XML (as in xmlelement), or XML to SQL (as in the output columns of xmltable), except for a few cases treated specially, PostgreSQL simply assumes that the XML data type's XPath 1.0 string form will be valid as the text-input form of the SQL datatype, and conversely. This rule has the virtue of simplicity while producing, for many data types, results similar to the mappings specified in the standard.

Where interoperability with other systems is a concern, for some data types, it may be necessary to use data type formatting functions (such as those in Section 9.8) explicitly to produce the standard mappings.

D.3.2. Incidental Limits of the Implementation

This section concerns limits that are not inherent in the libxml2 library, but apply to the current implementation in PostgreSQL.

D.3.2.1. Only BY VALUE Passing Mechanism Is Supported

The SQL standard defines two passing mechanisms that apply when passing an XML argument from SQL to an XML function or receiving a result: BY REF, in which a particular XML value retains its node identity, and BY VALUE, in which the content of the XML is passed but node identity is not preserved. A mechanism can be specified before a list of parameters, as the default mechanism for all of them, or after any parameter, to override the default.

To illustrate the difference, if x is an XML value, these two queries in an SQL:2006 environment would produce true and false, respectively:

SELECT XMLQUERY('$a is $b' PASSING BY REF x AS a, x AS b NULL ON EMPTY);
SELECT XMLQUERY('$a is $b' PASSING BY VALUE x AS a, x AS b NULL ON EMPTY);

PostgreSQL will accept BY VALUE or BY REF in an XMLEXISTS or XMLTABLE construct, but it ignores them. The xml data type holds a character-string serialized representation, so there is no node identity to preserve, and passing is always effectively BY VALUE.

D.3.2.2. Cannot Pass Named Parameters To Queries

The XPath-based functions support passing one parameter to serve as the XPath expression's context item, but do not support passing additional values to be available to the expression as named parameters.

D.3.2.3. No XML(SEQUENCE) Type

The PostgreSQL xml data type can only hold a value in DOCUMENT or CONTENT form. An XQuery/XPath expression context item must be a single XML node or atomic value, but XPath 1.0 further restricts it to be only an XML node, and has no node type allowing CONTENT. The upshot is that a well-formed DOCUMENT is the only form of XML value that PostgreSQL can supply as an XPath context item.

auth_delay 讓伺服器在回報身份驗證失敗之前會短暫暫停，從而使對資料庫密碼的暴力攻擊更加困難。請注意，它不會阻止拒絕服務攻擊，甚至可能加劇攻擊，因為在回報身份驗證失敗之前等待的程序仍然會佔用連線。

要使用這個功能，必須透過 postgresql.conf 中的 shared_preload_libraries 載入此模組。

F.3.1. Configuration Parameters

auth_delay.milliseconds (int)

The number of milliseconds to wait before reporting an authentication failure. The default is 0.

These parameters must be set in postgresql.conf. Typical usage might be:

# postgresql.conf
shared_preload_libraries = 'auth_delay'

auth_delay.milliseconds = '500'

F.3.2. Author

KaiGai Kohei <kaigai@ak.jp.nec.com>

auto_explain 模組提供了一種自動記錄慢速語句執行計劃的方法，毌須手動執行 EXPLAIN。這對於在大型應用程序中追踪未最佳化的查詢特別有用。

該模組不提供 SQL 可存取的功能。要使用它，只需將其載入到伺服器中即可。您也可以將其載入到單個連線之中：

LOAD 'auto_explain';

（您必須是超級使用者才能這樣做。）更典型的用法是透過在 postgresql.conf 中的 session_preload_libraries 或 shared_preload_libraries 中包含 auto_explain 將其預先載入到部分或全部連線中。然後，無論何時發生，您都可以追踪意外緩慢的查詢。當然，會有一些系統代價。

F.4.1. 組態參數

有幾個組態參數可以控制 auto_explain 的行為。請注意，預設行為是什麼都不做，因此如果需要任何結果，必須至少設定 auto_explain.log_min_duration。

auto_explain.log_min_duration (integer)

auto_explain.log_min_duration 是記錄語句計劃的最小語句執行時間（以毫秒為單位）。將此設定為零會記錄所有計劃。減號（預設值）停用計劃的記錄。例如，如果將其設定為 250ms，則將記錄執行 250ms 或更長時間的所有語句。只有超級使用者才能變更此設定。

auto_explain.log_analyze (boolean)

auto_explain.log_analyze 會在記錄執行計劃時列印 EXPLAIN ANALYZE 輸出，而不僅僅是 EXPLAIN 輸出。預設情況下，此參數處於停用狀態。只有超級使用者才能變更此設定。

auto_explain.log_buffers (boolean)

auto_explain.log_buffers 控制是否在記錄執行計劃時輸出緩衝區使用情況統計訊息；它相當於 EXPLAIN 的 BUFFERS 選項。除非啟用了 auto_explain.log_analyze，否則此參數無效。預鉆水情況下，此參數處於停用狀態。只有超級使用者才能變更改此設定。

auto_explain.log_wal (boolean)

auto_explain.log_wal 控制在記錄執行計劃時是否輸出 WAL 使用情況統計資訊。它等同於 EXPLAIN 的 WAL 選項。除非啟用了 auto_explain.log_analyze，否則此參數無效。預設情況下，此參數是停用的。只有超級使用者可以變更此設定。

auto_explain.log_timing (boolean)

auto_explain.log_timing 控制在記錄執行計劃時是否輸出每個節點的計時訊息；它相當於 EXPLAIN 的 TIMING 選項。重複讀取系統時鐘的成本會在某些系統上明顯減慢查詢速度，因此當只需要實際資料列計數而非精確時間計時，將此參數設定為關閉可能很有用。除非啟用了 auto_explain.log_analyze，否則此參數無效。預設情況下，此參數處於啟用狀態。只有超級使用者才能變更此設定。

auto_explain.log_triggers (boolean)

auto_explain.log_triggers 會在記錄執行計劃時包含觸發器執行統計訊息。除非啟用了 auto_explain.log_analyze，否則此參數無效。預設情況下，此參數處於停用狀態。只有超級使用者才能變更此設定。

auto_explain.log_verbose (boolean)

auto_explain.log_verbose 控制是否在記錄執行計劃時輸出詳細訊息；它相當於 EXPLAIN 的 VERBOSE 選項。預設情況下，此參數處於停用狀態。只有超級使用者才能變更此設定。

auto_explain.log_settings (boolean)

auto_explain.log_settings 控制記錄執行計劃時是否輸出有關被修改的組態選項資訊。輸出中僅包含影響其值與內建預設值不同的查詢計劃的選項。預設情況下，此參數是停用的。 只有超級使用者可以變更此設定。

auto_explain.log_format (enum)

auto_explain.log_format 選擇要使用的 EXPLAIN 輸出格式。允許的值為 text、xml、json 和 yaml。預設為 text。只有超級使用者才能變更此設定。

auto_explain.log_level (enum)

auto_explain.log_level 選擇 auto_explain 將記錄查詢計劃的日誌等級。 有效值為 DEBUG5，DEBUG4，DEBUG3，DEBUG2，DEBUG1，INFO，NOTICE，WARNING 和 LOG。預設值為 LOG。只有超級使用者可以變更此設定。

auto_explain.log_nested_statements (boolean)

auto_explain.log_nested_statements 會讓巢狀語句（在函數內執行的語句）記錄下來。關閉時，僅記錄最上層查詢計劃。預設情況下，此參數處於停用狀態。只有超級使用者才能變更此設定。

auto_explain.sample_rate (real)

auto_explain.sample_rate 使 auto_explain 僅解釋每個連線中的一小部分語句。預設值為 1，表示 EXPLAIN 所有查詢。在巢狀語句的情況下，要就全部都要解釋，要就都不解釋。只有超級使用者才能變更此設定。

在一般的用法中，這些參數在 postgresql.conf 中設定，儘管超級使用者可以在他們自己的連線中即時更改它們。典型用法可能是：

# postgresql.conf
session_preload_libraries = 'auto_explain'

auto_explain.log_min_duration = '3s'

F.4.2. 範例

postgres=# LOAD 'auto_explain';
postgres=# SET auto_explain.log_min_duration = 0;
postgres=# SET auto_explain.log_analyze = true;
postgres=# SELECT count(*)
           FROM pg_class, pg_index
           WHERE oid = indrelid AND indisunique;

這可能會產生如下的日誌輸出：

LOG:  duration: 3.651 ms  plan:
  Query Text: SELECT count(*)
              FROM pg_class, pg_index
              WHERE oid = indrelid AND indisunique;
  Aggregate  (cost=16.79..16.80 rows=1 width=0) (actual time=3.626..3.627 rows=1 loops=1)
    ->  Hash Join  (cost=4.17..16.55 rows=92 width=0) (actual time=3.349..3.594 rows=92 loops=1)
          Hash Cond: (pg_class.oid = pg_index.indrelid)
          ->  Seq Scan on pg_class  (cost=0.00..9.55 rows=255 width=4) (actual time=0.016..0.140 rows=255 loops=1)
          ->  Hash  (cost=3.02..3.02 rows=92 width=4) (actual time=3.238..3.238 rows=92 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 4kB
                ->  Seq Scan on pg_index  (cost=0.00..3.02 rows=92 width=4) (actual time=0.008..3.187 rows=92 loops=1)
                      Filter: indisunique

F.4.3. 作者

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>

amcheck 模塊提供的功能是讓你可以驗證關連結構邏輯的一致性。如果該結構看起來有效，就不會引發任何錯誤。

The functions verify various invariants in the structure of the representation of particular relations. The correctness of the access method functions behind index scans and other important operations relies on these invariants always holding. For example, certain functions verify, among other things, that all B-Tree pages have items in “logical” order (e.g., for B-Tree indexes on text, index tuples should be in collated lexical order). If that particular invariant somehow fails to hold, we can expect binary searches on the affected page to incorrectly guide index scans, resulting in wrong answers to SQL queries.

Verification is performed using the same procedures as those used by index scans themselves, which may be user-defined operator class code. For example, B-Tree index verification relies on comparisons made with one or more B-Tree support function 1 routines. See Section 37.16.3 for details of operator class support functions.

amcheck functions may only be used by superusers.

F.2.1. Functions

bt_index_check(index regclass, heapallindexed boolean) returns void

bt_index_check tests that its target, a B-Tree index, respects a variety of invariants. Example usage:

test=# SELECT bt_index_check(index => c.oid, heapallindexed => i.indisunique),
               c.relname,
               c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog'
-- Don't check temp tables, which may be from another session:
AND c.relpersistence != 't'
-- Function may throw an error when this is omitted:
AND c.relkind = 'i' AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC LIMIT 10;
 bt_index_check |             relname             | relpages 
----------------+---------------------------------+----------
                | pg_depend_reference_index       |       43
                | pg_depend_depender_index        |       40
                | pg_proc_proname_args_nsp_index  |       31
                | pg_description_o_c_o_index      |       21
                | pg_attribute_relid_attnam_index |       14
                | pg_proc_oid_index               |       10
                | pg_attribute_relid_attnum_index |        9
                | pg_amproc_fam_proc_index        |        5
                | pg_amop_opr_fam_index           |        5
                | pg_amop_fam_strat_index         |        5
(10 rows)

This example shows a session that performs verification of the 10 largest catalog indexes in the database “test”. Verification of the presence of heap tuples as index tuples is requested for the subset that are unique indexes. Since no error is raised, all indexes tested appear to be logically consistent. Naturally, this query could easily be changed to call bt_index_check for every index in the database where verification is supported.

bt_index_check acquires an AccessShareLock on the target index and the heap relation it belongs to. This lock mode is the same lock mode acquired on relations by simple SELECT statements. bt_index_check does not verify invariants that span child/parent relationships, but will verify the presence of all heap tuples as index tuples within the index when heapallindexed is true. When a routine, lightweight test for corruption is required in a live production environment, using bt_index_check often provides the best trade-off between thoroughness of verification and limiting the impact on application performance and availability.

bt_index_parent_check(index regclass, heapallindexed boolean, rootdescend boolean) returns void

bt_index_parent_check tests that its target, a B-Tree index, respects a variety of invariants. Optionally, when the heapallindexed argument is true, the function verifies the presence of all heap tuples that should be found within the index. When the optional rootdescend argument is true, verification re-finds tuples on the leaf level by performing a new search from the root page for each tuple. The checks that can be performed by bt_index_parent_check are a superset of the checks that can be performed by bt_index_check. bt_index_parent_check can be thought of as a more thorough variant of bt_index_check: unlike bt_index_check, bt_index_parent_check also checks invariants that span parent/child relationships, including checking that there are no missing downlinks in the index structure. bt_index_parent_check follows the general convention of raising an error if it finds a logical inconsistency or other problem.

A ShareLock is required on the target index by bt_index_parent_check (a ShareLock is also acquired on the heap relation). These locks prevent concurrent data modification from INSERT, UPDATE, and DELETE commands. The locks also prevent the underlying relation from being concurrently processed by VACUUM, as well as all other utility commands. Note that the function holds locks only while running, not for the entire transaction.

bt_index_parent_check's additional verification is more likely to detect various pathological cases. These cases may involve an incorrectly implemented B-Tree operator class used by the index that is checked, or, hypothetically, undiscovered bugs in the underlying B-Tree index access method code. Note that bt_index_parent_check cannot be used when Hot Standby mode is enabled (i.e., on read-only physical replicas), unlike bt_index_check.

Tip

bt_index_check and bt_index_parent_check both output log messages about the verification process at DEBUG1 and DEBUG2 severity levels. These messages provide detailed information about the verification process that may be of interest to PostgreSQL developers. Advanced users may also find this information helpful, since it provides additional context should verification actually detect an inconsistency. Running:

SET client_min_messages = DEBUG1;

in an interactive psql session before running a verification query will display messages about the progress of verification with a manageable level of detail.

F.2.2. Optional heapallindexed Verification

When the heapallindexed argument to verification functions is true, an additional phase of verification is performed against the table associated with the target index relation. This consists of a “dummy” CREATE INDEX operation, which checks for the presence of all hypothetical new index tuples against a temporary, in-memory summarizing structure (this is built when needed during the basic first phase of verification). The summarizing structure “fingerprints” every tuple found within the target index. The high level principle behind heapallindexed verification is that a new index that is equivalent to the existing, target index must only have entries that can be found in the existing structure.

The additional heapallindexed phase adds significant overhead: verification will typically take several times longer. However, there is no change to the relation-level locks acquired when heapallindexed verification is performed.

The summarizing structure is bound in size by maintenance_work_mem. In order to ensure that there is no more than a 2% probability of failure to detect an inconsistency for each heap tuple that should be represented in the index, approximately 2 bytes of memory are needed per tuple. As less memory is made available per tuple, the probability of missing an inconsistency slowly increases. This approach limits the overhead of verification significantly, while only slightly reducing the probability of detecting a problem, especially for installations where verification is treated as a routine maintenance task. Any single absent or malformed tuple has a new opportunity to be detected with each new verification attempt.

F.2.3. Using amcheck Effectively

amcheck can be effective at detecting various types of failure modes that data page checksums will always fail to catch. These include:

• Structural inconsistencies caused by incorrect operator class implementations.

  This includes issues caused by the comparison rules of operating system collations changing. Comparisons of datums of a collatable type like text must be immutable (just as all comparisons used for B-Tree index scans must be immutable), which implies that operating system collation rules must never change. Though rare, updates to operating system collation rules can cause these issues. More commonly, an inconsistency in the collation order between a master server and a standby server is implicated, possibly because the major operating system version in use is inconsistent. Such inconsistencies will generally only arise on standby servers, and so can generally only be detected on standby servers.

  If a problem like this arises, it may not affect each individual index that is ordered using an affected collation, simply because indexed values might happen to have the same absolute ordering regardless of the behavioral inconsistency. See Section 23.1 and Section 23.2 for further details about how PostgreSQL uses operating system locales and collations.

• Structural inconsistencies between indexes and the heap relations that are indexed (when heapallindexed verification is performed).

  There is no cross-checking of indexes against their heap relation during normal operation. Symptoms of heap corruption can be subtle.

• Corruption caused by hypothetical undiscovered bugs in the underlying PostgreSQL access method code, sort code, or transaction management code.

  Automatic verification of the structural integrity of indexes plays a role in the general testing of new or proposed PostgreSQL features that could plausibly allow a logical inconsistency to be introduced. Verification of table structure and associated visibility and transaction status information plays a similar role. One obvious testing strategy is to call amcheck functions continuously when running the standard regression tests. See Section 32.1 for details on running the tests.

• File system or storage subsystem faults where checksums happen to simply not be enabled.

  Note that amcheck examines a page as represented in some shared memory buffer at the time of verification if there is only a shared buffer hit when accessing the block. Consequently, amcheck does not necessarily examine data read from the file system at the time of verification. Note that when checksums are enabled, amcheck may raise an error due to a checksum failure when a corrupt block is read into a buffer.

• Corruption caused by faulty RAM, or the broader memory subsystem.

  PostgreSQL does not protect against correctable memory errors and it is assumed you will operate using RAM that uses industry standard Error Correcting Codes (ECC) or better protection. However, ECC memory is typically only immune to single-bit errors, and should not be assumed to provide absolute protection against failures that result in memory corruption.

  When heapallindexed verification is performed, there is generally a greatly increased chance of detecting single-bit errors, since strict binary equality is tested, and the indexed attributes within the heap are tested.

In general, amcheck can only prove the presence of corruption; it cannot prove its absence.

F.2.4. Repairing Corruption

No error concerning corruption raised by amcheck should ever be a false positive. amcheck raises errors in the event of conditions that, by definition, should never happen, and so careful analysis of amcheck errors is often required.

There is no general method of repairing problems that amcheck detects. An explanation for the root cause of an invariant violation should be sought. pageinspect may play a useful role in diagnosing corruption that amcheck detects. A REINDEX may not be effective in repairing corruption.

Bloom 提供了一種基於 Bloom fliters 索引方法。

Bloom filter 是一種節省空間的資料結構，用於測試元素是否為集合的成員。在使用索引方法時，它透過 signatures（其大小是在建立索引時確定的）快速排除不相符的內容。

Signature 是索引屬性的失真表示，因此很容易誤報。也就是說，可能會回報某個元素在集合中（實際上沒有）。因此，必須使用資料中的實際屬性值來重新檢查索引並搜尋結果。較大的 signature 會減少了誤報的機率，也減少了無效的存取次數，但當然也會使索引變大，造成掃描速度變慢。

當資料表具有許多屬性並且查詢測試它們的任意組合時，這種類型的索引最有用。傳統的 btree 索引會比 Bloom 索引快，但是它可能需要許多 btree 索引來支援所有可能的查詢，而其中一個查詢只需要一個 Bloom 索引。但是請注意，bloom 索引僅支援相等查詢，而 btree 索引也可以用於不相等和範圍查詢。

F.5.1. 參數

Bloom 索引的 WITH 子句接受以下參數：

length

每個 signature（索引項目）的長度（以位元為單位）。四捨五入到最接近的 16 的倍數。預設值為 80 位元，最大值為 4096。

col1 — col32

每個索引欄位產成的位元數。每個參數的名稱指的是它控制的索引欄位的編號。預設值為 2 位元，最大值為 4095。實際未使用的索引欄位的參數將被忽略。

F.5.2. Examples

This is an example of creating a bloom index:

CREATE INDEX bloomidx ON tbloom USING bloom (i1,i2,i3)
       WITH (length=80, col1=2, col2=2, col3=4);

The index is created with a signature length of 80 bits, with attributes i1 and i2 mapped to 2 bits, and attribute i3 mapped to 4 bits. We could have omitted the length, col1, and col2 specifications since those have the default values.

Here is a more complete example of bloom index definition and usage, as well as a comparison with equivalent btree indexes. The bloom index is considerably smaller than the btree index, and can perform better.

=# CREATE TABLE tbloom AS
   SELECT
     (random() * 1000000)::int as i1,
     (random() * 1000000)::int as i2,
     (random() * 1000000)::int as i3,
     (random() * 1000000)::int as i4,
     (random() * 1000000)::int as i5,
     (random() * 1000000)::int as i6
   FROM
  generate_series(1,10000000);
SELECT 10000000
=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# SELECT pg_size_pretty(pg_relation_size('bloomidx'));
 pg_size_pretty
----------------
 153 MB
(1 row)
=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
=# SELECT pg_size_pretty(pg_relation_size('btreeidx'));
 pg_size_pretty
----------------
 387 MB
(1 row)

A sequential scan over this large table takes a long time:

=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                 QUERY PLAN
-------------------------------------------------------------------​-----------------------------------------
 Seq Scan on tbloom  (cost=0.00..213694.08 rows=1 width=24) (actual time=1445.438..1445.438 rows=0 loops=1)
   Filter: ((i2 = 898732) AND (i5 = 123451))
   Rows Removed by Filter: 10000000
 Planning time: 0.177 ms
 Execution time: 1445.473 ms
(5 rows)

So the planner will usually select an index scan if possible. With a btree index, we get results like this:

=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                           QUERY PLAN
-------------------------------------------------------------------​-------------------------------------------------------------
 Index Only Scan using btreeidx on tbloom  (cost=0.56..298311.96 rows=1 width=24) (actual time=445.709..445.709 rows=0 loops=1)
   Index Cond: ((i2 = 898732) AND (i5 = 123451))
   Heap Fetches: 0
 Planning time: 0.193 ms
 Execution time: 445.770 ms
(5 rows)

Bloom is better than btree in handling this type of search:

=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                        QUERY PLAN
-------------------------------------------------------------------​--------------------------------------------------------
 Bitmap Heap Scan on tbloom  (cost=178435.39..178439.41 rows=1 width=24) (actual time=76.698..76.698 rows=0 loops=1)
   Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
   Rows Removed by Index Recheck: 2439
   Heap Blocks: exact=2408
   ->  Bitmap Index Scan on bloomidx  (cost=0.00..178435.39 rows=1 width=0) (actual time=72.455..72.455 rows=2439 loops=1)
         Index Cond: ((i2 = 898732) AND (i5 = 123451))
 Planning time: 0.475 ms
 Execution time: 76.778 ms
(8 rows)

請注意，這裡的誤報的數量相對較多：被選擇 2439 筆資料要進行確認，但實際上沒有與查詢相符的資料。我們可以透過指定更大的 signature 長度來減少這種情況。在此範例中，建立長度為 200 的索引可將誤報數量減少到 55；但它也使索引大小增加了一倍（至 306 MB），並最終使該查詢的速度變慢（總計 125 毫秒）。

現在，btree 搜搜的主要問題在於，當搜搜條件不限制前導索引欄時，btree 的效率低下。btree 的更好策略是在每欄位上建立一個單獨的索引。然後計劃程序將會選擇規劃以下內容：

=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                          QUERY PLAN
-------------------------------------------------------------------​-----------------------------------------------------------
 Bitmap Heap Scan on tbloom  (cost=9.29..13.30 rows=1 width=24) (actual time=0.148..0.148 rows=0 loops=1)
   Recheck Cond: ((i5 = 123451) AND (i2 = 898732))
   ->  BitmapAnd  (cost=9.29..9.29 rows=1 width=0) (actual time=0.145..0.145 rows=0 loops=1)
         ->  Bitmap Index Scan on tbloom_i5_idx  (cost=0.00..4.52 rows=11 width=0) (actual time=0.089..0.089 rows=10 loops=1)
               Index Cond: (i5 = 123451)
         ->  Bitmap Index Scan on tbloom_i2_idx  (cost=0.00..4.52 rows=11 width=0) (actual time=0.048..0.048 rows=8 loops=1)
               Index Cond: (i2 = 898732)
 Planning time: 2.049 ms
 Execution time: 0.280 ms
(9 rows)

儘管此查詢的執行速度比使用單個索引的查詢快得多，但我們在索引大小上付出了很大的代價。每個單欄位 btree 索引佔用 214 MB，因此所需的總空間超過 1.2GB，是 Bloom 索引使用的空間 8 倍以上。

F.5.3. Operator Class Interface

An operator class for bloom indexes requires only a hash function for the indexed data type and an equality operator for searching. This example shows the operator class definition for the text data type:

CREATE OPERATOR CLASS text_ops
DEFAULT FOR TYPE text USING bloom AS
    OPERATOR    1   =(text, text),
    FUNCTION    1   hashtext(text);

F.5.4. Limitations

• Only operator classes for int4 and text are included with the module.

• Only the = operator is supported for search. But it is possible to add support for arrays with union and intersection operations in the future.

• bloom access method doesn't support UNIQUE indexes.

• bloom access method doesn't support searching for NULL values.

F.5.5. Authors

Teodor Sigaev <teodor@postgrespro.ru>, Postgres Professional, Moscow, Russia

Alexander Korotkov <a.korotkov@postgrespro.ru>, Postgres Professional, Moscow, Russia

Oleg Bartunov <obartunov@postgrespro.ru>, Postgres Professional, Moscow, Russia

dblink_disconnect — closes a persistent connection to a remote database

Synopsis

dblink_disconnect() returns text
dblink_disconnect(text connname) returns text

Description

dblink_disconnect() closes a connection previously opened by dblink_connect(). The form with no arguments closes an unnamed connection.

Arguments

connname

The name of a named connection to be closed.

Return Value

Returns status, which is always OK (since any error causes the function to throw an error instead of returning).

Examples

SELECT dblink_disconnect();
 dblink_disconnect
-------------------
 OK
(1 row)

SELECT dblink_disconnect('myconn');
 dblink_disconnect
-------------------
 OK
(1 row)

dblink — executes a query in a remote database

Synopsis

dblink(text connname, text sql [, bool fail_on_error]) returns setof record
dblink(text connstr, text sql [, bool fail_on_error]) returns setof record
dblink(text sql [, bool fail_on_error]) returns setof record

Description

dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.

When two text arguments are given, the first one is first looked up as a persistent connection's name; if found, the command is executed on that connection. If not found, the first argument is treated as a connection info string as for dblink_connect, and the indicated connection is made just for the duration of this command.

Arguments

connname

Name of the connection to use; omit this parameter to use the unnamed connection.connstr

A connection info string, as previously described for dblink_connect.

sql

The SQL query that you wish to execute in the remote database, for example select * from foo.

fail_on_error

If true (the default when omitted) then an error thrown on the remote side of the connection causes an error to also be thrown locally. If false, the remote error is locally reported as a NOTICE, and the function returns no rows.

Return Value

The function returns the row(s) produced by the query. Since dblink can be used with any query, it is declared to return record, rather than specifying any particular set of columns. This means that you must specify the expected set of columns in the calling query — otherwise PostgreSQL would not know what to expect. Here is an example:

SELECT *
    FROM dblink('dbname=mydb options=-csearch_path=',
                'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

The “alias” part of the FROM clause must specify the column names and types that the function will return. (Specifying column names in an alias is actually standard SQL syntax, but specifying column types is a PostgreSQL extension.) This allows the system to understand what * should expand to, and what proname in the WHERE clause refers to, in advance of trying to execute the function. At run time, an error will be thrown if the actual query result from the remote database does not have the same number of columns shown in the FROM clause. The column names need not match, however, and dblink does not insist on exact type matches either. It will succeed so long as the returned data strings are valid input for the column type declared in the FROM clause.

Notes

A convenient way to use dblink with predetermined queries is to create a view. This allows the column type information to be buried in the view, instead of having to spell it out in every query. For example,

CREATE VIEW myremote_pg_proc AS
  SELECT *
    FROM dblink('dbname=postgres options=-csearch_path=',
                'select proname, prosrc from pg_proc')
    AS t1(proname name, prosrc text);

SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';

Examples

SELECT * FROM dblink('dbname=postgres options=-csearch_path=',
                     'select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT * FROM dblink('select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 bytearecv  | bytearecv
 byteasend  | byteasend
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteain    | byteain
 byteaout   | byteaout
(14 rows)

dblink 模組可以讓你從資料庫連線中再連線到其他 PostgreSQL 資料庫。

另請參閱 postgres_fdw，它使用更現代且符合標準的基礎架構提供大致相同的功能。

dblink_connect — opens a persistent connection to a remote database

Synopsis

dblink_connect(text connstr) returns text
dblink_connect(text connname, text connstr) returns text

Description

dblink_connect() establishes a connection to a remote PostgreSQL database. The server and database to be contacted are identified through a standard libpq connection string. Optionally, a name can be assigned to the connection. Multiple named connections can be open at once, but only one unnamed connection is permitted at a time. The connection will persist until closed or until the database session is ended.

The connection string may also be the name of an existing foreign server. It is recommended to use the foreign-data wrapper dblink_fdw when defining the foreign server. See the example below, as well as CREATE SERVER and CREATE USER MAPPING.

Arguments

connname

The name to use for this connection; if omitted, an unnamed connection is opened, replacing any existing unnamed connection.connstr

libpq-style connection info string, for example hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd options=-csearch_path=. For details see Section 33.1.1. Alternatively, the name of a foreign server.

Return Value

Returns status, which is always OK (since any error causes the function to throw an error instead of returning).

Notes

If untrusted users have access to a database that has not adopted a secure schema usage pattern, begin each session by removing publicly-writable schemas from search_path. One could, for example, add options=-csearch_path= to connstr. This consideration is not specific to dblink; it applies to every interface for executing arbitrary SQL commands.

Only superusers may use dblink_connect to create non-password-authenticated connections. If non-superusers need this capability, use dblink_connect_u instead.

It is unwise to choose connection names that contain equal signs, as this opens a risk of confusion with connection info strings in other dblink functions.

Examples

SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

-- FOREIGN DATA WRAPPER functionality
-- Note: local connection must require password authentication for this to work properly
--       Otherwise, you will receive the following error from dblink_connect():
--       ----------------------------------------------------------------------
--       ERROR:  password is required
--       DETAIL:  Non-superuser cannot connect if the server does not request a password.
--       HINT:  Target server's authentication method must be changed.

CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');

CREATE USER regress_dblink_user WITH PASSWORD 'secret';
CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
GRANT SELECT ON TABLE foo TO regress_dblink_user;

\set ORIGINAL_USER :USER
\c - regress_dblink_user
SELECT dblink_connect('myconn', 'fdtest');
 dblink_connect 
----------------
 OK
(1 row)

SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
 a  | b |       c       
----+---+---------------
  0 | a | {a0,b0,c0}
  1 | b | {a1,b1,c1}
  2 | c | {a2,b2,c2}
  3 | d | {a3,b3,c3}
  4 | e | {a4,b4,c4}
  5 | f | {a5,b5,c5}
  6 | g | {a6,b6,c6}
  7 | h | {a7,b7,c7}
  8 | i | {a8,b8,c8}
  9 | j | {a9,b9,c9}
 10 | k | {a10,b10,c10}
(11 rows)

\c - :ORIGINAL_USER
REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user;
REVOKE SELECT ON TABLE foo FROM regress_dblink_user;
DROP USER MAPPING FOR regress_dblink_user SERVER fdtest;
DROP USER regress_dblink_user;
DROP SERVER fdtest;

btree_gin provides sample GIN operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, "char", varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid, name, bool, bpchar, and all enum types.

通常，這些運算子類不會優於等效的標準 B-tree 索引方法，並且它們缺少標準 B-tree 的一個主要功能：強制執行唯一性。但是，它們對於 GIN 測試很有用，並且可以作為開發其他 GIN 運算子類的基礎。同樣地，對於同時測試可索引 GIN 欄位和 B-tree 可索引列的查詢，建立使用這些運算子之一的多欄位 GIN 索引可能比建立兩個必須獨立的索引更有效，以 bitmap ANDing 的方式。

F.6.1. 使用範例

CREATE TABLE test (a int4);
-- create index
CREATE INDEX testidx ON test USING GIN (a);
-- query
SELECT * FROM test WHERE a < 10;

F.6.2. 作者們

Teodor Sigaev (<teodor@stack.net>) and Oleg Bartunov (<oleg@sai.msu.su>). See http://www.sai.msu.su/~megera/oddmuse/index.cgi/Gin for additional information.

Table B.1. 月份名稱

Table B.2. 星期名稱

Table B.3. 日期/時間修飾字

adminpack 提供了許多支援性質的函數，pgAdmin 和其他管理工具可以用來提供其他功能，例如伺服器日誌檔案的遠程管理。預設情況下，僅超級使用者可以使用所有的這些函數，但使用 GRANT 指令可以允許其他使用者使用它們。

Table F.1 中列出的函數提供了對伺服器的主機上檔案的寫入功能。 （另請參見 Table 9.95 中提供唯讀的功能。）只能存取資料庫叢集目錄中的檔案，除非使用者是超級使用者或具有 pg_read_server_files 或 pg_write_server_files 個角色（視情況而定） ，而相對路徑及絕對路徑都是允許的。

Table F.1. adminpack Functions

pg_file_write writes the specified data into the file named by filename. If append is false, the file must not already exist. If append is true, the file can already exist, and will be appended to if so. Returns the number of bytes written.

pg_file_sync fsyncs the specified file or directory named by filename. An error is thrown on failure (e.g., the specified file is not present). Note that data_sync_retry has no effect on this function, and therefore a PANIC-level error will not be raised even on failure to flush database files.

pg_file_rename renames a file. If archivename is omitted or NULL, it simply renames oldname to newname (which must not already exist). If archivename is provided, it first renames newname to archivename (which must not already exist), and then renames oldname to newname. In event of failure of the second rename step, it will try to rename archivename back to newname before reporting the error. Returns true on success, false if the source file(s) are not present or not writable; other cases throw errors.

pg_file_unlink removes the specified file. Returns true on success, false if the specified file is not present or the unlink() call fails; other cases throw errors.

pg_logdir_ls returns the start timestamps and path names of all the log files in the log_directory directory. The log_filename parameter must have its default setting (postgresql-%Y-%m-%d_%H%M%S.log) to use this function.

The SQL standard states that“Within the definition of a‘datetime literal’, the‘datetime values’are constrained by the natural rules for dates and times according to the Gregorian calendar”.PostgreSQLfollows the SQL standard's lead by counting dates exclusively in the Gregorian calendar, even for years before that calendar was in use. This rule is known as theproleptic Gregorian calendar.

The Julian calendar was introduced by Julius Caesar in 45 BC. It was in common use in the Western world until the year 1582, when countries started changing to the Gregorian calendar. In the Julian calendar, the tropical year is approximated as 365 1/4 days = 365.25 days. This gives an error of about 1 day in 128 years.

The accumulating calendar error prompted Pope Gregory XIII to reform the calendar in accordance with instructions from the Council of Trent. In the Gregorian calendar, the tropical year is approximated as 365 + 97 / 400 days = 365.2425 days. Thus it takes approximately 3300 years for the tropical year to shift one day with respect to the Gregorian calendar.

The approximation 365+97/400 is achieved by having 97 leap years every 400 years, using the following rules:

So, 1700, 1800, 1900, 2100, and 2200 are not leap years. But 1600, 2000, and 2400 are leap years. By contrast, in the older Julian calendar all years divisible by 4 are leap years.

The papal bull of February 1582 decreed that 10 days should be dropped from October 1582 so that 15 October should follow immediately after 4 October. This was observed in Italy, Poland, Portugal, and Spain. Other Catholic countries followed shortly after, but Protestant countries were reluctant to change, and the Greek Orthodox countries didn't change until the start of the 20th century. The reform was observed by Great Britain and its dominions (including what is now the USA) in 1752. Thus 2 September 1752 was followed by 14 September 1752. This is why Unix systems have thecalprogram produce the following:

$ cal 9 1752

   September 1752
 S  M Tu  W Th  F  S
       1  2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

But, of course, this calendar is only valid for Great Britain and dominions, not other places. Since it would be difficult and confusing to try to track the actual calendars that were in use in various places at various times,PostgreSQLdoes not try, but rather follows the Gregorian calendar rules for all dates, even though this method is not historically accurate.

Different calendars have been developed in various parts of the world, many predating the Gregorian system. For example, the beginnings of the Chinese calendar can be traced back to the 14th century BC. Legend has it that the Emperor Huangdi invented that calendar in 2637 BC. The People's Republic of China uses the Gregorian calendar for civil purposes. The Chinese calendar is used for determining festivals.

The_Julian Date_system is another type of calendar, unrelated to the Julian calendar though it is confusingly named similarly to that calendar. The Julian Date system was invented by the French scholar Joseph Justus Scaliger (1540-1609) and probably takes its name from Scaliger's father, the Italian scholar Julius Caesar Scaliger (1484-1558). In the Julian Date system, each day has a sequential number, starting from JD 0 (which is sometimes called_the_Julian Date). JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or 24 November 4714 BC in the Gregorian calendar. Julian Date counting is most often used by astronomers for labeling their nightly observations, and therefore a date runs from noon UTC to the next noon UTC, rather than from midnight to midnight: JD 0 designates the 24 hours from noon UTC on 24 November 4714 BC to noon UTC on 25 November 4714 BC.

AlthoughPostgreSQLsupports Julian Date notation for input and output of dates (and also uses Julian dates for some internal datetime calculations), it does not observe the nicety of having dates run from noon to noon.PostgreSQLtreats a Julian Date as running from midnight to midnight.

• https://www.postgresql.org/docs/10/static/datetime-units-history.html

所有由PostgreSQL伺服器回傳的訊息，都會搭配一個 5 個字元的錯誤代碼，這些代碼均以 SQL 標準的 SQLSTATE 代碼所定義。應用程式應該以錯誤代碼作為程式行為，而非處理文字的錯誤訊息。這些錯誤代碼會隨 PostgreSQL 版本發佈而微幅更新，但不會因為不同語言的文字訊息影響其定義。特別注意，部份錯誤代碼是以 SQL 標準的定義所制定，而有些錯誤代碼是額外制定的，以對應其他 SQL 標準未定義的使用情況。

標準來看，錯誤代碼的前 2 個字元代表著這個錯誤的類別，而後面 3 個字元則指出在此類別中更詳細的發生情況。所以，應用程式即使未完整支援特定的錯誤代碼，仍然可以依類別代碼瞭解大概發生了什麼事。

下方的 Table A.1 列出了所有在 PostgreSQL 10 中所定義的錯誤代碼。（部份錯誤代碼可能實際上沒有使用，但仍然依 SQL 標準所制定）每一個錯誤的類別都有一個＂標準＂的錯誤情況，其最後 3 個字元為 000，只用於在該類別未特別詳細定義的錯誤情況。

在＂Condition Name＂欄位中的內容將能在 PL/pgSQL 中被使用。Condition Name 不論大小寫字母均可合法使用。（特別注意，PL/pgSQL 並不支援 00、01 及 02 的錯誤類別。）

有一些錯誤訊息，其回傳內容是發生錯誤的資料庫物件名稱（表格 Table、表格欄位 Table column、資料型別 data type、限制條件 constraint）。舉例來說，如果產生了 unique_violation 錯誤，則會回傳某個限制條件名稱。這些訊息將會分別額外的欄位回傳，所以應用程式不需要特別解譯描述性的訊息內容。在 PostgreSQL 9.3之前，這種回傳方式僅限於 SQLSTATE 類別 23（違反限制條件的一致性 integrity constraint violation），日後將儘可能延伸支援到所有類別。

Table A.1. PostgreSQL Error Codes