• https://www.postgresql.org/docs/10/static/appendixes.html

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 提供了一種自訂的伺服器接受的縮寫集方法。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/ 中的檔案，則由您來進行備份 - 正常的資料庫轉存將不包含此目錄。

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

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

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年）。

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

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

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

This section attempts to outline to what extent PostgreSQL conforms to the current SQL standard. The following information is not a full statement of conformance, but it presents the main topics in as much detail as is both reasonable and useful for users.

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 2011. The 2011 version is referred to as ISO/IEC 9075:2011, or simply as SQL:2011. The versions prior to that were SQL:2008, 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. Some optional features are grouped together to form “packages”, which SQL implementations can claim conformance to, thus claiming conformance to particular groups of features.

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)

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 and 10 for PostgreSQL.

PostgreSQL supports most of the major features of SQL:2011. Out of 179 mandatory features required for full Core conformance, PostgreSQL conforms to at least 160. 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:2011.

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:2011 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.

Table B.1. 月份名稱

Table B.2. 星期名稱

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

版本資訊不進行翻譯，請參閱官方手冊網頁： https://www.postgresql.org/docs/devel/static/release.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.

所有由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

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

The pg_visibility module provides a means for examining the visibility map (VM) and page-level visibility information of a table. It also provides functions to check the integrity of a visibility map and to force it to be rebuilt.

Three different bits are used to store information about page-level visibility. The all-visible bit in the visibility map indicates that every tuple in the corresponding page of the relation is visible to every current and future transaction. The all-frozen bit in the visibility map indicates that every tuple in the page is frozen; that is, no future vacuum will need to modify the page until such time as a tuple is inserted, updated, deleted, or locked on that page. The page header's PD_ALL_VISIBLE bit has the same meaning as the all-visible bit in the visibility map, but is stored within the data page itself rather than in a separate data structure. These two bits will normally agree, but the page's all-visible bit can sometimes be set while the visibility map bit is clear after a crash recovery. The reported values can also disagree because of a change that occurs after pg_visibility examines the visibility map and before it examines the data page. Any event that causes data corruption can also cause these bits to disagree.

Functions that display information about PD_ALL_VISIBLE bits are much more costly than those that only consult the visibility map, because they must read the relation's data blocks rather than only the (much smaller) visibility map. Functions that check the relation's data blocks are similarly expensive.

F.33.1. Functions

pg_visibility_map(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean) returns record

Returns the all-visible and all-frozen bits in the visibility map for the given block of the given relation.

pg_visibility(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns record

Returns the all-visible and all-frozen bits in the visibility map for the given block of the given relation, plus the PD_ALL_VISIBLE bit of that block.

pg_visibility_map(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean) returns setof record

Returns the all-visible and all-frozen bits in the visibility map for each block of the given relation.

pg_visibility(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns setof record

Returns the all-visible and all-frozen bits in the visibility map for each block of the given relation, plus the PD_ALL_VISIBLE bit of each block.

pg_visibility_map_summary(relation regclass, all_visible OUT bigint, all_frozen OUT bigint) returns record

Returns the number of all-visible pages and the number of all-frozen pages in the relation according to the visibility map.

pg_check_frozen(relation regclass, t_ctid OUT tid) returns setof tid

Returns the TIDs of non-frozen tuples stored in pages marked all-frozen in the visibility map. If this function returns a non-empty set of TIDs, the visibility map is corrupt.

pg_check_visible(relation regclass, t_ctid OUT tid) returns setof tid

Returns the TIDs of non-all-visible tuples stored in pages marked all-visible in the visibility map. If this function returns a non-empty set of TIDs, the visibility map is corrupt.

pg_truncate_visibility_map(relation regclass) returns void

Truncates the visibility map for the given relation. This function is useful if you believe that the visibility map for the relation is corrupt and wish to force rebuilding it. The firstVACUUM executed on the given relation after this function is executed will scan every page in the relation and rebuild the visibility map. (Until that is done, queries will treat the visibility map as containing all zeroes.)

By default, these functions are executable only by superusers and members of the pg_stat_scan_tables role, with the exception of pg_truncate_visibility_map(relation regclass) which can only be executed by superusers.

F.33.2. Author

This appendix and the previous one contain information regarding the modules that can be found in the contrib directory of the PostgreSQL distribution. See for more information about the contrib section in general and server extensions and plug-ins found incontrib specifically.

This appendix covers utility programs found in contrib. Once installed, either from source or a packaging system, they are found in the bin directory of the PostgreSQL installation and can be used like any other program.

— opens a persistent connection to a remote database

— opens a persistent connection to a remote database, insecurely

— closes a persistent connection to a remote database

— executes a query in a remote database

— executes a command in a remote database

— opens a cursor in a remote database

— returns rows from an open cursor in a remote database

— closes a cursor in a remote database

— returns the names of all open named dblink connections

— gets last error message on the named connection

— sends an async query to a remote database

— checks if connection is busy with an async query

— retrieve async notifications on a connection

— gets an async query result

— cancels any active query on the named connection

— returns the positions and field names of a relation's primary key fields

— builds an INSERT statement using a local tuple, replacing the primary key field values with alternative supplied values

— builds a DELETE statement using supplied values for primary key field values

— builds an UPDATE statement using a local tuple, replacing the primary key field values with alternative supplied values

dblinkis a module that supports connections to otherPostgreSQLdatabases from within a database session.

dblink — executes a query in a remote database

Synopsis

Description

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

When twotextarguments 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 fordblink_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 fordblink_connect.

sql

The SQL query that you wish to execute in the remote database, for exampleselect * 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. Sincedblinkcan be used with any query, it is declared to returnrecord, rather than specifying any particular set of columns. This means that you must specify the expected set of columns in the calling query — otherwisePostgreSQLwould not know what to expect. Here is an example:

The“alias”part of theFROMclause 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 aPostgreSQLextension.) This allows the system to understand what*should expand to, and whatpronamein theWHEREclause 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 theFROMclause. The column names need not match, however, anddblinkdoes 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 theFROMclause.

Notes

A convenient way to usedblinkwith 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,

Examples

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

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

（您必須是超級使用者才能這樣做。）更典型的用法是透過在 postgresql.conf 中的 或 中包含 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_timing 可以獲得較少的訊息，從而改善效能成本。

auto_explain.log_buffers (boolean)

auto_explain.log_buffers 控制是否在記錄執行計劃時輸出緩衝區使用情況統計訊息；它相當於 EXPLAIN 的 BUFFERS 選項。除非啟用了 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_format (enum)

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

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 中設定，儘管超級使用者可以在他們自己的連線中即時更改它們。典型用法可能是：

F.4.2. 範例

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

F.4.3. 作者

Table of Contents

— opens a persistent connection to a remote database

— opens a persistent connection to a remote database, insecurely

— closes a persistent connection to a remote database

— executes a query in a remote database

— executes a command in a remote database

— opens a cursor in a remote database

— returns rows from an open cursor in a remote database

— closes a cursor in a remote database

— returns the names of all open named dblink connections

— gets last error message on the named connection

— sends an async query to a remote database

— checks if connection is busy with an async query

— retrieve async notifications on a connection

— gets an async query result

— cancels any active query on the named connection

— returns the positions and field names of a relation's primary key fields

— builds an INSERT statement using a local tuple, replacing the primary key field values with alternative supplied values

— builds a DELETE statement using supplied values for primary key field values

— builds an UPDATE statement using a local tuple, replacing the primary key field values with alternative supplied values

This appendix and the next one contain information regarding the modules that can be found in thecontribdirectory of thePostgreSQLdistribution. These include porting tools, analysis utilities, and plug-in features that are not part of the core PostgreSQL system, mainly because they address a limited audience or are too experimental to be part of the main source tree. This does not preclude their usefulness.

in thecontribdirectory of a configured source tree; or to build and install just one selected module, do the same in that module's subdirectory. Many of the modules have regression tests, which can be executed by running:

before installation or

once you have aPostgreSQLserver running.

If you are using a pre-packaged version ofPostgreSQL, these modules are typically made available as a separate subpackage, such aspostgresql-contrib.

This command must be run by a database superuser. This registers the new SQL objects in the current database only, so you need to run this command in each database that you want the module's facilities to be available in. Alternatively, run it in databasetemplate1so that the extension will be copied into subsequently-created databases by default.

Many modules allow you to install their objects in a schema of your choice. To do that, addSCHEMA_schema_name_to theCREATE EXTENSIONcommand. By default, the objects will be placed in your current creation target schema, typicallypublic.

If your database was brought forward by dump and reload from a pre-9.1 version ofPostgreSQL, and you had been using the pre-9.1 version of the module in it, you should instead do