Ordinarily, if a date/time string is syntactically valid but contains out-of-range field values, an error will be thrown. For example, input specifying the 31st of February will be rejected.

During a daylight-savings-time transition, it is possible for a seemingly valid timestamp string to represent a nonexistent or ambiguous timestamp. Such cases are not rejected; the ambiguity is resolved by determining which UTC offset to apply. For example, supposing that the TimeZone parameter is set to America/New_York, consider

=> SELECT '2018-03-11 02:30'::timestamptz;
      timestamptz
------------------------
 2018-03-11 03:30:00-04
(1 row)

Because that day was a spring-forward transition date in that time zone, there was no civil time instant 2:30AM; clocks jumped forward from 2AM EST to 3AM EDT. PostgreSQL interprets the given time as if it were standard time (UTC-5), which then renders as 3:30AM EDT (UTC-4).

Conversely, consider the behavior during a fall-back transition:

=> SELECT '2018-11-04 01:30'::timestamptz;
      timestamptz
------------------------
 2018-11-04 01:30:00-05
(1 row)

On that date, there were two possible interpretations of 1:30AM; there was 1:30AM EDT, and then an hour later after clocks jumped back from 2AM EDT to 1AM EST, there was 1:30AM EST. Again, PostgreSQL interprets the given time as if it were standard time (UTC-5). We can force the other interpretation by specifying daylight-savings time:

=> SELECT '2018-11-04 01:30 EDT'::timestamptz;
      timestamptz
------------------------
 2018-11-04 01:30:00-04
(1 row)

The precise rule that is applied in such cases is that an invalid timestamp that appears to fall within a jump-forward daylight savings transition is assigned the UTC offset that prevailed in the time zone just before the transition, while an ambiguous timestamp that could fall on either side of a jump-back transition is assigned the UTC offset that prevailed just after the transition. In most time zones this is equivalent to saying that “the standard-time interpretation is preferred when in doubt”.

In all cases, the UTC offset associated with a timestamp can be specified explicitly, using either a numeric UTC offset or a time zone abbreviation that corresponds to a fixed UTC offset. The rule just given applies only when it is necessary to infer a UTC offset for a time zone in which the offset varies.

PostgreSQL can accept time zone specifications that are written according to the POSIX standard's rules for the TZ environment variable. POSIX time zone specifications are inadequate to deal with the complexity of real-world time zone history, but there are sometimes reasons to use them.

A POSIX time zone specification has the form

STD offset [ DST [ dstoffset ] [ , rule ] ]

(For readability, we show spaces between the fields, but spaces should not be used in practice.) The fields are:

• STD is the zone abbreviation to be used for standard time.

• offset is the zone's standard-time offset from UTC.

• DST is the zone abbreviation to be used for daylight-savings time. If this field and the following ones are omitted, the zone uses a fixed UTC offset with no daylight-savings rule.

• dstoffset is the daylight-savings offset from UTC. This field is typically omitted, since it defaults to one hour less than the standard-time offset, which is usually the right thing.

• rule defines the rule for when daylight savings is in effect, as described below.

In this syntax, a zone abbreviation can be a string of letters, such as EST, or an arbitrary string surrounded by angle brackets, such as <UTC-05>. Note that the zone abbreviations given here are only used for output, and even then only in some timestamp output formats. The zone abbreviations recognized in timestamp input are determined as explained in Section B.4.

The offset fields specify the hours, and optionally minutes and seconds, difference from UTC. They have the format hh[:mm[:ss]] optionally with a leading sign (+ or -). The positive sign is used for zones west of Greenwich. (Note that this is the opposite of the ISO-8601 sign convention used elsewhere in PostgreSQL.) hh can have one or two digits; mm and ss (if used) must have two.

The daylight-savings transition rule has the format

dstdate [ / dsttime ] , stddate [ / stdtime ]

(As before, spaces should not be included in practice.) The dstdate and dsttime fields define when daylight-savings time starts, while stddate and stdtime define when standard time starts. (In some cases, notably in zones south of the equator, the former might be later in the year than the latter.) The date fields have one of these formats:

n

A plain integer denotes a day of the year, counting from zero to 364, or to 365 in leap years.

Jn

In this form, n counts from 1 to 365, and February 29 is not counted even if it is present. (Thus, a transition occurring on February 29 could not be specified this way. However, days after February have the same numbers whether it's a leap year or not, so that this form is usually more useful than the plain-integer form for transitions on fixed dates.)

Mm.n.d

This form specifies a transition that always happens during the same month and on the same day of the week. m identifies the month, from 1 to 12. n specifies the n'th occurrence of the weekday identified by d. n is a number between 1 and 4, or 5 meaning the last occurrence of that weekday in the month (which could be the fourth or the fifth). d is a number between 0 and 6, with 0 indicating Sunday. For example, M3.2.0 means “the second Sunday in March”.

Note

The M format is sufficient to describe many common daylight-savings transition laws. But note that none of these variants can deal with daylight-savings law changes, so in practice the historical data stored for named time zones (in the IANA time zone database) is necessary to interpret past time stamps correctly.

The time fields in a transition rule have the same format as the offset fields described previously, except that they cannot contain signs. They define the current local time at which the change to the other time occurs. If omitted, they default to 02:00:00.

If a daylight-savings abbreviation is given but the transition rule field is omitted, the fallback behavior is to use the rule M3.2.0,M11.1.0, which corresponds to USA practice as of 2020 (that is, spring forward on the second Sunday of March, fall back on the first Sunday of November, both transitions occurring at 2AM prevailing time). Note that this rule does not give correct USA transition dates for years before 2007.

As an example, CET-1CEST,M3.5.0,M10.5.0/3 describes current (as of 2020) timekeeping practice in Paris. This specification says that standard time has the abbreviation CET and is one hour ahead (east) of UTC; daylight savings time has the abbreviation CEST and is implicitly two hours ahead of UTC; daylight savings time begins on the last Sunday in March at 2AM CET and ends on the last Sunday in October at 3AM CEST.

The four timezone names EST5EDT, CST6CDT, MST7MDT, and PST8PDT look like they are POSIX zone specifications. However, they actually are treated as named time zones because (for historical reasons) there are files by those names in the IANA time zone database. The practical implication of this is that these zone names will produce valid historical USA daylight-savings transitions, even when a plain POSIX specification would not.

One should be wary that it is easy to misspell a POSIX-style time zone specification, since there is no check on the reasonableness of the zone abbreviation(s). For example, SET TIMEZONE TO FOOBAR0 will work, leaving the system effectively using a rather peculiar abbreviation for UTC.

由於時區縮寫並沒有很好地標準化，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 時區資料庫中定義的區域名稱。查詢區域的定義以查看該區域中是否正在使用縮寫，如果是，則使用適當的含義 - 即，目前正在確定其值的時間戳記中使用的含義，或者如果當時不是目前使用的話，則使用之前的含義，如果僅在該時間之後使用，則使用最舊的含義。這種行為對於處理其含義歷史變化的縮寫至關重要。還允許根據區域名稱定義縮寫，其中不出現該縮寫；使用縮寫就等於寫出區域名稱。

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

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

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

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

Release date: 2023-02-09

This release contains a variety of fixes from 15.1. For information about new features in major release 15, see Section E.3.

E.1.1. Migration to Version 15.2

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

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

E.1.2. Changes

• libpq can leak memory contents after GSSAPI transport encryption initiation fails (Jacob Champion)

  A modified server, or an unauthenticated man-in-the-middle, can send a not-zero-terminated error message during setup of GSSAPI (Kerberos) transport encryption. libpq will then copy that string, as well as following bytes in application memory up to the next zero byte, to its error report. Depending on what the calling application does with the error report, this could result in disclosure of application memory contents. There is also a small probability of a crash due to reading beyond the end of memory. Fix by properly zero-terminating the server message. (CVE-2022-41862)

• Fix calculation of which GENERATED columns need to be updated in child tables during an UPDATE on a partitioned table or inheritance tree (Amit Langote, Tom Lane)

  This fixes failure to update GENERATED columns that do not exist in the parent table, or that have different dependencies than are in the parent column's generation expression.

• Fix possible failure of MERGE to compute GENERATED columns (Dean Rasheed)

  When the first row-level action of the MERGE was an UPDATE, any subsequent INSERT actions would fail to compute GENERATED columns that were deemed unnecessary to compute for the UPDATE action (due to not depending on any of the UPDATE target columns).

• Fix MERGE's check for unreachable WHEN clauses (Dean Rasheed)

  A WHEN clause following an unconditional WHEN clause should be rejected as unreachable, but this case was not always detected.

• Fix MERGE's rule-detection test (Dean Rasheed)

  MERGE is not supported on tables with rules; but it also failed on tables that once had rules but no longer do.

• In MERGE, don't count a DO NOTHING action as a processed tuple (Álvaro Herrera)

  This makes the code's behavior match the documentation.

• Allow a WITH RECURSIVE ... CYCLE CTE to access its output column (Tom Lane)

  A reference to the SET column from within the CTE would fail with “cache lookup failed for type 0”.

• Fix handling of pending inserts when doing a bulk insertion to a foreign table (Etsuro Fujita)

  In some cases pending insertions were not flushed to the FDW soon enough, leading to logical inconsistencies, for example BEFORE ROW triggers not seeing rows they should be able to see.

• Allow REPLICA IDENTITY to be set on an index that's not (yet) valid (Tom Lane)

  When pg_dump dumps a partitioned index that's marked REPLICA IDENTITY, it generates a command sequence that applies REPLICA IDENTITY before the partitioned index has been marked valid, causing restore to fail. There seems no very good reason to prohibit doing it in that order, so allow it. The marking will have no effect anyway until the index becomes valid.

• Fix handling of DEFAULT markers in rules that perform an INSERT from a multi-row VALUES list (Dean Rasheed)

  In some cases a DEFAULT marker would not get replaced with the proper default-value expression, leading to an “unrecognized node type” error.

• Reject uses of undefined variables in jsonpath existence checks (Alexander Korotkov, David G. Johnston)

  While jsonpath match operators threw an error for an undefined variable in the path pattern, the existence operators silently treated it as a match.

• Fix jsonb subscripting to cope with toasted subscript values (Tom Lane, David G. Johnston)

  Using a text value fetched directly from a table as a jsonb subscript was likely to fail. Fetches would usually not find any matching element. Assignments could store the value with a garbage key, although keys long enough to cause that problem are probably rare in the field.

• Fix edge-case data corruption in parallel hash joins (Dmitry Astapov)

  If the final chunk of a large tuple being written out to a temporary file was exactly 32760 bytes, it would be corrupted due to a fencepost bug. The query would typically fail later with corrupted-data symptoms.

• Honor non-default settings of checkpoint_completion_target (Bharath Rupireddy)

  Internal state was not updated after a change in checkpoint_completion_target, possibly resulting in performing checkpoint I/O faster or slower than desired, especially if that setting was changed on-the-fly.

• Log the correct ending timestamp in recovery_target_xid mode (Tom Lane)

  When ending recovery based on the recovery_target_xid setting with recovery_target_inclusive = off, we printed an incorrect timestamp (always 2000-01-01) in the “recovery stopping before ... transaction” log message.

• Improve error reporting for some buffered file read failures (Peter Eisentraut)

  Correctly report a short read, giving the numbers of bytes desired and actually read, instead of reporting an irrelevant error code. Most places got this right already, but some recently-written replication logic did not.

• Remove arbitrary limit on number of elements in int2vector and oidvector (Tom Lane)

  The input functions for these types previously rejected more than 100 elements. With the introduction of the logical replication column list feature, it's necessary to accept int2vectors having up to 1600 columns, otherwise long column lists cause logical-replication failures.

• In extended query protocol, avoid an immediate commit after ANALYZE if we're running a pipeline (Tom Lane)

  If there's not been an explicit BEGIN TRANSACTION, ANALYZE would take it on itself to commit, which should not happen within a pipelined series of commands.

• Reject cancel request packets having the wrong length (Andrey Borodin)

  The server would process a cancel request even if its length word was too small. This led to reading beyond the end of the allocated buffer. In theory that could cause a segfault, but it seems quite unlikely to happen in practice, since the buffer would have to be very close to the end of memory. The more likely outcome was a bogus log message about wrong backend PID or cancel code. Complain about the wrong length, instead.

• Fix planner preprocessing oversights for window function run-condition expressions (Richard Guo, David Rowley)

  This could lead to planner errors such as “WindowFunc not found in subplan target lists”.

• Fix possible dangling-pointer access during execution of window function run-condition expressions (David Rowley)

  In practice, because the run-condition optimization is only applied to certain window functions that happen to all return int8, this only manifested as a problem on 32-bit builds.

• Add recursion and looping defenses in subquery pullup (Tom Lane)

  A contrived query can result in deep recursion and unreasonable amounts of time spent trying to flatten subqueries. A proper fix for that seems unduly invasive for a back-patch, but we can at least add stack depth checks and an interrupt check to allow the query to be cancelled.

• Fix planner issues when combining Memoize nodes with partitionwise joins or parameterized nestloops (Richard Guo)

  These errors could lead to not using Memoize in contexts where it would be useful, or possibly to wrong query plans.

• Fix partitionwise-join code to tolerate failure to produce a plan for each partition (Tom Lane)

  This could result in “could not devise a query plan for the given query” errors.

• Limit the amount of cleanup work done by get_actual_variable_range (Simon Riggs)

  Planner runs occurring just after deletion of a large number of tuples appearing at the end of an index could expend significant amounts of work setting the “killed” bits for those index entries. Limit the amount of work done in any one query by giving up on this process after examining 100 heap pages. All the cleanup will still happen eventually, but without so large a performance hiccup.

• Prevent the statistics machinery from getting confused when a relation's relkind changes (Andres Freund)

  Converting a table to a view could lead to crashes or assertion failures.

• Fix under-parenthesized display of AT TIME ZONE constructs (Tom Lane)

  This could result in dump/restore failures for rules or views in which an argument of AT TIME ZONE is itself an expression.

• Prevent clobbering of cached parsetrees for utility statements in SQL functions (Tom Lane, Daniel Gustafsson)

  If a SQL-language function executes the same utility command more than once within a single calling query, it could crash or report strange errors such as “unrecognized node type”.

• Ensure that execution of full-text-search queries can be cancelled while they are performing phrase matches (Tom Lane)

• Fix memory leak in hashing strings with nondeterministic collations (Jeff Davis)

• Fix deadlock between DROP DATABASE and logical replication worker process (Hou Zhijie)

  This was caused by an ill-advised choice to block interrupts while creating a logical replication slot in the worker. In version 15 that could lead to an undetected deadlock. In version 14, no deadlock has been observed, but it's still a bad idea to block interrupts while waiting for network I/O.

• Clean up the libpq connection object after a failed replication connection attempt (Andres Freund)

  The previous coding leaked the connection object. In background code paths that's pretty harmless because the calling process will give up and exit. But in commands such as CREATE SUBSCRIPTION, such a failure resulted in a small session-lifespan memory leak.

• In hot-standby servers, reduce processing effort for tracking XIDs known to be active on the primary (Simon Riggs, Michail Nikolaev)

  Insufficiently-aggressive cleanup of the KnownAssignedXids array could lead to poor performance, particularly when max_connections is set to a large value on the standby.

• Ignore invalidated logical-replication slots while determining oldest catalog xmin (Sirisha Chamarthi)

  A replication slot could prevent cleanup of dead tuples in the system catalogs even after it becomes invalidated due to exceeding max_slot_wal_keep_size. Thus, failure of a replication consumer could lead to indefinitely-large catalog bloat.

• In logical decoding, notify the remote node when a transaction is detected to have crashed (Hou Zhijie)

  After a server restart, we'll re-stream the changes for transactions occurring shortly before the restart. Some of these transactions probably never completed; when we realize that one didn't we throw away the relevant decoding state locally, but we neglected to tell the subscriber about it. That led to the subscriber keeping useless streaming files until it's next restarted.

• Fix uninitialized-memory usage in logical decoding (Masahiko Sawada)

  In certain cases, resumption of logical decoding could try to re-use XID data that had already been freed, leading to unpredictable behavior.

• Acquire spinlock while updating shared state during logical decoding context creation (Masahiko Sawada)

  We neglected to acquire the appropriate lock while updating data about two-phase transactions, potentially allowing other processes to see inconsistent data.

• Fix pgoutput replication plug-in to not send columns not listed in a table's replication column list (Hou Zhijie)

  UPDATE and DELETE events did not pay attention to the configured column list, thus sending more data than expected. This did not cause a problem when the receiver is our built-in logical replication code, but it might confuse other receivers, and in any case it wasted network bandwidth.

• Avoid rare “failed to acquire cleanup lock” panic during WAL replay of hash-index page split operations (Robert Haas)

• Advance a heap page's LSN when setting its all-visible bit during WAL replay (Jeff Davis)

  Failure to do this left the page possibly different on standby servers than the primary, and violated some other expectations about when the LSN changes. This seems only a theoretical hazard so far as PostgreSQL itself is concerned, but it could upset third-party tools.

• Fix int64_div_fast_to_numeric() to work for a wider range of inputs (Dean Rasheed)

  This function misbehaved with some values of its second argument. No such usages exist in core PostgreSQL, but it's clearly a hazard for external modules, so repair.

• Fix latent buffer-overrun problem in WaitEventSet logic (Thomas Munro)

  The epoll-based and kqueue-based implementations could ask the kernel for too many events if the size of their internal buffer was different from the size of the caller's output buffer. That case is not known to occur in released PostgreSQL versions, but this error is a hazard for external modules and future bug fixes.

• Avoid nominally-undefined behavior when accessing shared memory in 32-bit builds (Andres Freund)

  clang's undefined-behavior sanitizer complained about use of a pointer that was less aligned than it should be. It's very unlikely that this would cause a problem in non-debug builds, but it's worth fixing for testing purposes.

• Fix assertion failure in BRIN minmax-multi opclasses (Tomas Vondra)

  The assertion was overly strict, so this mistake was harmless in non-assert builds.

• Remove faulty assertion in useless-RESULT-RTE optimization logic (Tom Lane)

• Fix copy-and-paste errors in cache-lookup-failure messages for ACL checks (Justin Pryzby)

  In principle these errors should never be reached. But if they are, some of them reported the wrong type of object.

• Fix possible corruption of very large tablespace map files in pg_basebackup (Antonin Houska)

• Avoid harmless warning from pg_dump in --if-exists mode (Tom Lane)

  If the public schema has a non-default owner then use of pg_dump's --if-exists option resulted in a warning message “warning: could not find where to insert IF EXISTS in statement "-- *not* dropping schema, since initdb creates it"”. The dump output was okay, though.

• Fix psql's \sf and \ef commands to handle SQL-language functions that have SQL-standard function bodies (Tom Lane)

  These commands misidentified the start of the function body when it used new-style syntax.

• Fix tab completion of ALTER FUNCTION/PROCEDURE/ROUTINE ... SET SCHEMA (Dean Rasheed)

• Update contrib/pageinspect to mark its disk-accessing functions as PARALLEL RESTRICTED (Tom Lane)

  This avoids possible failure if one of these functions is used to examine a temporary table, since a session's temporary tables are not accessible from parallel workers.

• Fix contrib/seg to not crash or print garbage if an input number has more than 127 digits (Tom Lane)

• Fix build on Microsoft Visual Studio 2013 (Tom Lane)

  A previous patch supposed that all platforms of interest have snprintf(), but MSVC 2013 isn't quite there yet. Revert to using sprintf() on that platform.

• Fix compile failure in building PL/Perl with MSVC when using Strawberry Perl (Andrew Dunstan)

• Fix mismatch of PL/Perl built with MSVC versus a Perl library built with gcc (Andrew Dunstan)

  Such combinations could previously fail with “loadable library and perl binaries are mismatched” errors.

• Suppress compiler warnings from Perl's header files (Andres Freund)

  Our preferred compiler options provoke warnings about constructs appearing in recent versions of Perl's header files. When using gcc, we can suppress these warnings with a pragma.

• Fix pg_waldump to build on compilers that don't discard unused static-inline functions (Tom Lane)

• Update time zone data files to tzdata release 2022g for DST law changes in Greenland and Mexico, plus historical corrections for northern Canada, Colombia, and Singapore.

  Notably, a new timezone America/Ciudad_Juarez has been split off from America/Ojinaga.

This appendix and the next one contain information regarding the modules that can be found in the contrib directory of the PostgreSQL distribution. 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.

本附錄涵蓋了 contrib 中的延伸功能和其他伺服器外掛模組，附錄 G 為工具程式的說明。

從原始碼編譯建置時，這些模組並不會自動編譯，除非您有編譯 “world” （參閱第 2 步）。 您可以透過執行以下命令來編譯和安裝這些模組：

make
make install

in the contrib directory 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:

make check

before installation or

make installcheck

once you have a PostgreSQL server running.

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

許多模組提供新的使用者定義函數、運算子或型別。 要使用這些模組之一，在安裝程式後，您需要在資料庫系統中註冊新的 SQL 物件。 這是透過執行 CREATE EXTENSION 指令來完成的。 在一個新的資料庫中，你可以簡單地執行：

CREATE EXTENSION module_name;

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

For all these modules, CREATE EXTENSION must be run by a database superuser, unless the module is considered “trusted”, in which case it can be run by any user who has CREATE privilege on the current database. Modules that are trusted are identified as such in the sections that follow. Generally, trusted modules are ones that cannot provide access to outside-the-database functionality.

Many modules allow you to install their objects in a schema of your choice. To do that, add SCHEMA schema_name to the CREATE EXTENSION command. By default, the objects will be placed in your current creation target schema, which in turn defaults to public.

Note, however, that some of these modules are not “extensions” in this sense, but are loaded into the server in some other way, for instance by way of shared_preload_libraries. See the documentation of each module for details.

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

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

SQL:2016 中所定義的以下功能並未在此版本的 PostgreSQL 中實作。在某些情況下，有一些等效的功能可以使用。

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

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:

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

• 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.
• 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.
• 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.

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

F.4.2. 範例

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

F.4.3. 作者

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

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 作為記錄值。