{"Entry":{"collection":"sql","key":"wait-for","name":"WAIT","aliases":["wait","wait-for","waitfor"],"metadata":{"aliases":["wait","wait-for","waitfor"],"changed_in":[],"changes":[{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"19"}],"content_hash":"0e5046dc11b6540f45f9a832ad399194ed6a7c562cebd0b78a24072692c3846f","editorial":{},"first_version":"19","group":"transaction","imported_at":"2026-09-27T17:57:27.305859+08:00","last_version":"20","name":"WAIT","object":"","position":13006,"present_in":["19","20"],"purpose":"wait for WAL to reach a target LSN","purpose_zh":"","related":[],"slug":"wait-for","source_rev":"b7bd9cda","synopsis":"WAIT FOR LSN 'lsn'\n[ WITH ( option [, ...] ) ]\n\nwhere option can be:\n\nMODE 'mode'\nTIMEOUT 'timeout'\nNO_THROW\n\nand mode can be:\n\nstandby_replay | standby_write | standby_flush | primary_flush","verb":"WAIT"}},"Definition":{"Collection":"sql","Key":"wait-for","SourceDatabase":"center","Version":"20","SourceTable":"sqlcmd","SourceKey":"wait-for","SourceRevision":"b7bd9cda","Facts":{"anchor":"SQL-WAIT","file":"sql-wait.html","lang":"en","name":"WAIT","purpose":"wait for WAL to reach a target LSN","purpose_zh":"","related":[],"sections":[{"html":"\u003cp\u003eWaits until the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached according to the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e, which determines whether to wait for WAL to be written, flushed, or replayed. If no \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e is specified or it is set to zero, this command waits indefinitely for the \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003cp\u003eOn timeout, an error is emitted unless \u003ccode class=\"literal\"\u003eNO_THROW\u003c/code\u003e is specified in the WITH clause. For standby modes (\u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e, \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e, \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e), an error is also emitted if the server is promoted before the \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached. If \u003cem class=\"parameter\"\u003e\u003ccode\u003eNO_THROW\u003c/code\u003e\u003c/em\u003e is specified, the command reports these outcomes as a status string instead of raising an error.\u003c/p\u003e\u003cp\u003eThe possible return values are \u003ccode class=\"literal\"\u003esuccess\u003c/code\u003e, \u003ccode class=\"literal\"\u003etimeout\u003c/code\u003e, and \u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies the target \u003cacronym\u003eLSN\u003c/acronym\u003e to wait for.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause specifies optional parameters for the wait operation. The following parameters are supported:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eMODE\u003c/code\u003e '\u003cem class=\"replaceable\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e'\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies the type of LSN processing to wait for. If not specified, the default is \u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e. The valid modes are:\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e: Wait for the LSN to be replayed (applied to the database) on a standby server. After successful completion, \u003ccode class=\"function\"\u003epg_last_wal_replay_lsn()\u003c/code\u003e will return a value greater than or equal to the target LSN. This mode can only be used during recovery.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e: Wait for the WAL containing the LSN to be written to disk on a standby server, but not yet necessarily flushed. This is faster than \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e but provides weaker durability guarantees since the data may still be in operating system buffers. This is satisfied by WAL already present on the standby from a base backup, archive restore, or prior streaming, as well as WAL newly received from the primary. This mode can only be used during recovery.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e: Wait for the WAL containing the LSN to be flushed to disk on a standby server. This provides a durability guarantee without waiting for the WAL to be applied. This is satisfied by WAL already present on the standby from a base backup, archive restore, or prior streaming, as well as WAL newly received from the primary. This mode can only be used during recovery.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eprimary_flush\u003c/code\u003e: Wait for the WAL containing the LSN to be flushed to disk on a primary server. After successful completion, \u003ccode class=\"function\"\u003epg_current_wal_flush_lsn()\u003c/code\u003e will return a value greater than or equal to the target LSN. This mode can only be used on a primary server (not during recovery).\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTIMEOUT\u003c/code\u003e '\u003cem class=\"replaceable\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e'\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eWhen specified and \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e is greater than zero, the command waits until \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached or the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e has elapsed. A value of zero (the default) means the command waits indefinitely.\u003c/p\u003e\u003cp\u003eThe \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e is an amount of time in milliseconds. It may also be specified as a string containing the numerical value followed by a time unit (see \u003ca href=\"/docs/devel/config-setting.html#CONFIG-SETTING-NAMES-VALUES\" title=\"19.1.1. Parameter Names and Values\"\u003eSection 19.1.1\u003c/a\u003e). The maximum value is \u003ccode class=\"literal\"\u003e2147483647 ms\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eFractional values are rounded to the nearest millisecond. Note that a \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e of half a millisecond or less therefore rounds down to zero, which means waiting indefinitely.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO_THROW\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecify to not throw an error in the case of timeout or running on the primary. In this case the result status can be obtained from the return value.\u003c/p\u003e\u003cp\u003eUse this option when \u003ccode class=\"literal\"\u003etimeout\u003c/code\u003e or \u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e is an expected result that the application intends to handle, for example by retrying the wait, reporting replication delay, or choosing another server for a subsequent operation. The command then returns the result as a status, which the application must check before assuming that the target \u003cacronym\u003eLSN\u003c/acronym\u003e was reached. Omit the option when the application must not proceed unless the target \u003cacronym\u003eLSN\u003c/acronym\u003e is reached, so that an unsuccessful wait stops normal execution with an error.\u003c/p\u003e\u003cp\u003eReturning a status also leaves an explicit transaction usable; without this option, the corresponding error requires rolling back the transaction, or rolling back to a savepoint, before further commands can be issued.\u003c/p\u003e\u003cp\u003eThis option changes only how \u003ccode class=\"literal\"\u003etimeout\u003c/code\u003e and \u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e are reported. Other errors are still raised. That covers invalid input, such as a malformed \u003cacronym\u003eLSN\u003c/acronym\u003e or an unrecognized option value, and every condition that is checked before the wait begins, such as requesting \u003ccode class=\"literal\"\u003eprimary_flush\u003c/code\u003e during recovery or holding a lock while waiting for a standby \u003cacronym\u003eLSN\u003c/acronym\u003e. The option also does not limit the duration of the wait; specify \u003ccode class=\"literal\"\u003eTIMEOUT\u003c/code\u003e for that purpose.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003esuccess\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis return value denotes that we have successfully reached the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003etimeout\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis return value denotes that the timeout happened before reaching the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis return value denotes that the database server is not in a recovery state. This might mean either the database server was not in recovery at the moment of receiving the command (i.e., executed on a primary), or it was promoted before reaching the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e. In the promotion case, this status indicates a timeline change occurred, and the application should re-evaluate whether the target LSN is still relevant.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"outputs","title":"Outputs"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e must be executed as a top-level command. It cannot be executed from a function, procedure, or \u003ccode class=\"command\"\u003eDO\u003c/code\u003e block. It also cannot be executed while the current transaction holds a snapshot. \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e itself acquires none, so it can run before the first snapshot-taking statement of a \u003ccode class=\"literal\"\u003eREPEATABLE READ\u003c/code\u003e or \u003ccode class=\"literal\"\u003eSERIALIZABLE\u003c/code\u003e transaction, but not after it, and not while a cursor or an exported snapshot holds one at any isolation level. A snapshot held here could delay replay, which \u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e waits for and which other standby modes can end up waiting for too. That is also why \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e is a command rather than a function or a procedure, which execute with one held.\u003c/p\u003e\u003cp\u003eWhile recovery is in progress, a wait in \u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e (the default), \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e, or \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e mode is rejected when the session already holds a lock and the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e has not been reached yet. Such a lock can make the startup process wait for this session, either directly or through another session, while this session waits for the startup process to advance recovery. That cycle involves no lock wait on this side, so deadlock detection does not see it and nothing breaks it. A wait whose target has already been reached returns immediately and is therefore always allowed.\u003c/p\u003e\u003cp\u003eIssue \u003ccode class=\"command\"\u003eWAIT FOR\u003c/code\u003e outside a transaction block, or as the first statement of one, before running anything that takes locks. That is also the natural order for the read-your-writes pattern shown in the examples below: wait for the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e first, then run the queries that have to see it. Note that a lock taken by an earlier statement is still held at \u003ccode class=\"literal\"\u003eREAD COMMITTED\u003c/code\u003e, even though its snapshot is gone, so a wait placed after such a statement is rejected even when the isolation level permits it.\u003c/p\u003e\u003cp\u003eThe restriction covers \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e and \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e as well, even though streaming replication can advance those positions without the startup process. Both positions are at least the replay position, so without an active walreceiver replay can be their only source of progress. If a held lock blocks replay, the session waits for replay to advance while replay waits for the session to release the lock. Under streaming replication the positions advance independently only while WAL keeps arriving. If reception stops before the target is reached, a blocked startup process cannot restart the walreceiver. It also cannot replay newer checkpoint records needed to advance restartpoints and recycle WAL, so \u003ccode class=\"filename\"\u003epg_wal\u003c/code\u003e can fill up and reception can stop before the target is reached. The restriction therefore also applies when streaming is active at the start of the wait.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e waits until the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached according to the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e. The \u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e mode waits for the LSN to be replayed (applied to the database), which is useful to achieve read-your-writes consistency while using an async replica for reads and the primary for writes, provided that the target \u003cacronym\u003eLSN\u003c/acronym\u003e is at or after the end of the relevant write transaction's \u003ccode class=\"literal\"\u003eCOMMIT\u003c/code\u003e record on the primary. The \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e mode waits for the WAL to be flushed to durable storage on the replica, or to have already been replayed from WAL present on the standby. The \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e mode waits for the WAL to be written to the operating system, or to have already been replayed, which is faster than flush for newly received WAL but provides weaker durability guarantees. The \u003ccode class=\"literal\"\u003eprimary_flush\u003c/code\u003e mode waits for WAL to be flushed on a primary server. In all cases, the \u003cacronym\u003eLSN\u003c/acronym\u003e of the last modification should be stored on the client application side or the connection pooler side.\u003c/p\u003e\u003cp\u003eThe standby modes (\u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e, \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e, \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e) can only be used during recovery, and \u003ccode class=\"literal\"\u003eprimary_flush\u003c/code\u003e can only be used on a primary server. Using the wrong mode for the current server state will result in an error. If a standby is promoted while waiting with a standby mode, the command will return \u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e (or throw an error if \u003ccode class=\"literal\"\u003eNO_THROW\u003c/code\u003e is not specified). Promotion creates a new timeline, and the LSN being waited for may refer to WAL from the old timeline.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e compares only the numeric \u003cacronym\u003eLSN\u003c/acronym\u003e; it has no notion of which timeline a WAL record belongs to. This matters when a standby continues recovery across an upstream timeline switch — for example, a cascading standby whose upstream gets promoted. In that case \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e will return \u003ccode class=\"literal\"\u003esuccess\u003c/code\u003e as soon as the position used by the selected wait mode reaches or passes the numeric \u003cacronym\u003eLSN\u003c/acronym\u003e, regardless of which timeline that \u003cacronym\u003eLSN\u003c/acronym\u003e belongs to. Applications that need to confirm the target refers to the expected timeline must validate the timeline themselves.\u003c/p\u003e\u003cp\u003eOn a standby server, \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e sessions may be interrupted by recovery conflicts. Some recovery conflicts are unavoidable: for example, replaying a tablespace drop resolves conflicts by terminating all backends, regardless of what they are doing. Applications using \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e on a standby should be prepared to handle such interruptions, for example by retrying the command or falling back to an alternative mechanism.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eYou can use the \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e command to wait for the \u003ccode class=\"type\"\u003epg_lsn\u003c/code\u003e value. For example, an application could update the \u003ccode class=\"literal\"\u003emovie\u003c/code\u003e table and get an \u003cacronym\u003elsn\u003c/acronym\u003e that is at or after the end of the relevant write transaction's \u003ccode class=\"literal\"\u003eCOMMIT\u003c/code\u003e record. In the default autocommit mode shown here, the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e commits before the subsequent \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e. This example uses \u003ccode class=\"function\"\u003epg_current_wal_insert_lsn\u003c/code\u003e on primary server to get the \u003cacronym\u003elsn\u003c/acronym\u003e given that \u003ccode class=\"varname\"\u003esynchronous_commit\u003c/code\u003e could be set to \u003ccode class=\"literal\"\u003eoff\u003c/code\u003e.\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';\nUPDATE 100\npostgres=# SELECT pg_current_wal_insert_lsn();\n pg_current_wal_insert_lsn\n---------------------------\n 0/0306EE20\n(1 row)\n\u003c/pre\u003e\u003cp\u003eThen an application could run \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e with the \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e obtained from the primary after the commit. After that, the changes made on the primary should be guaranteed to be visible on the replica.\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20';\n status\n---------\n success\n(1 row)\npostgres=# SELECT * FROM movie WHERE genre = 'Drama';\n genre\n-------\n(0 rows)\n\u003c/pre\u003e\u003cp\u003eWait for flush (data durable on replica):\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (MODE 'standby_flush');\n status\n---------\n success\n(1 row)\n\u003c/pre\u003e\u003cp\u003eWait for write with timeout:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (MODE 'standby_write', TIMEOUT '100ms', NO_THROW);\n status\n---------\n success\n(1 row)\n\u003c/pre\u003e\u003cp\u003eWait for flush on primary:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (MODE 'primary_flush');\n status\n---------\n success\n(1 row)\n\u003c/pre\u003e\u003cp\u003eIf the target LSN is not reached before the timeout, an error is thrown:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (TIMEOUT '0.1s');\nERROR:  timed out while waiting for target LSN 0/0306EE20 to be replayed; current standby_replay LSN 0/0306EA60\n\u003c/pre\u003e\u003cp\u003eThe same example uses \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e with the \u003cem class=\"parameter\"\u003e\u003ccode\u003eNO_THROW\u003c/code\u003e\u003c/em\u003e option:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (TIMEOUT '100ms', NO_THROW);\n status\n---------\n timeout\n(1 row)\n\u003c/pre\u003e","key":"examples","title":"Examples"}],"sections_same_as":"","slug":"devel","synopsis_html":"WAIT FOR LSN '\u003cem class=\"replaceable\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e'\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e can be:\u003c/span\u003e\n\n    MODE '\u003cem class=\"replaceable\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e'\n    TIMEOUT '\u003cem class=\"replaceable\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e'\n    NO_THROW\n\n\u003cspan class=\"phrase\"\u003eand \u003cem class=\"replaceable\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e can be:\u003c/span\u003e\n\n    standby_replay | standby_write | standby_flush | primary_flush","synopsis_text":"WAIT FOR LSN 'lsn'\n[ WITH ( option [, ...] ) ]\n\nwhere option can be:\n\nMODE 'mode'\nTIMEOUT 'timeout'\nNO_THROW\n\nand mode can be:\n\nstandby_replay | standby_write | standby_flush | primary_flush"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"wait-for","SourceDatabase":"center","Version":"20","Locale":"en","Title":"WAIT","Summary":"wait for WAL to reach a target LSN","BodyHTML":"\u003cpre\u003eWAIT FOR LSN \u0026#39;lsn\u0026#39;\n[ WITH ( option [, ...] ) ]\n\nwhere option can be:\n\nMODE \u0026#39;mode\u0026#39;\nTIMEOUT \u0026#39;timeout\u0026#39;\nNO_THROW\n\nand mode can be:\n\nstandby_replay | standby_write | standby_flush | primary_flush\u003c/pre\u003e\u003csection\u003e\u003ch2\u003eDescription\u003c/h2\u003e\u003cp\u003eWaits until the specified \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached according to the specified \u003cem\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e, which determines whether to wait for WAL to be written, flushed, or replayed. If no \u003cem\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e is specified or it is set to zero, this command waits indefinitely for the \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003cp\u003eOn timeout, an error is emitted unless \u003ccode\u003eNO_THROW\u003c/code\u003e is specified in the WITH clause. For standby modes (\u003ccode\u003estandby_replay\u003c/code\u003e, \u003ccode\u003estandby_write\u003c/code\u003e, \u003ccode\u003estandby_flush\u003c/code\u003e), an error is also emitted if the server is promoted before the \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached. If \u003cem\u003e\u003ccode\u003eNO_THROW\u003c/code\u003e\u003c/em\u003e is specified, the command reports these outcomes as a status string instead of raising an error.\u003c/p\u003e\u003cp\u003eThe possible return values are \u003ccode\u003esuccess\u003c/code\u003e, \u003ccode\u003etimeout\u003c/code\u003e, and \u003ccode\u003enot in recovery\u003c/code\u003e.\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eParameters\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies the target \u003cacronym\u003eLSN\u003c/acronym\u003e to wait for.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITH ( \u003cem\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause specifies optional parameters for the wait operation. The following parameters are supported:\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eMODE\u003c/code\u003e \u0026#39;\u003cem\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e\u0026#39;\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies the type of LSN processing to wait for. If not specified, the default is \u003ccode\u003estandby_replay\u003c/code\u003e. The valid modes are:\u003c/p\u003e\u003cdiv\u003e\u003cul\u003e\u003cli\u003e\u003cp\u003e\u003ccode\u003estandby_replay\u003c/code\u003e: Wait for the LSN to be replayed (applied to the database) on a standby server. After successful completion, \u003ccode\u003epg_last_wal_replay_lsn()\u003c/code\u003e will return a value greater than or equal to the target LSN. This mode can only be used during recovery.\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e\u003ccode\u003estandby_write\u003c/code\u003e: Wait for the WAL containing the LSN to be written to disk on a standby server, but not yet necessarily flushed. This is faster than \u003ccode\u003estandby_flush\u003c/code\u003e but provides weaker durability guarantees since the data may still be in operating system buffers. This is satisfied by WAL already present on the standby from a base backup, archive restore, or prior streaming, as well as WAL newly received from the primary. This mode can only be used during recovery.\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e\u003ccode\u003estandby_flush\u003c/code\u003e: Wait for the WAL containing the LSN to be flushed to disk on a standby server. This provides a durability guarantee without waiting for the WAL to be applied. This is satisfied by WAL already present on the standby from a base backup, archive restore, or prior streaming, as well as WAL newly received from the primary. This mode can only be used during recovery.\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e\u003ccode\u003eprimary_flush\u003c/code\u003e: Wait for the WAL containing the LSN to be flushed to disk on a primary server. After successful completion, \u003ccode\u003epg_current_wal_flush_lsn()\u003c/code\u003e will return a value greater than or equal to the target LSN. This mode can only be used on a primary server (not during recovery).\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTIMEOUT\u003c/code\u003e \u0026#39;\u003cem\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e\u0026#39;\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eWhen specified and \u003cem\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e is greater than zero, the command waits until \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached or the specified \u003cem\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e has elapsed. A value of zero (the default) means the command waits indefinitely.\u003c/p\u003e\u003cp\u003eThe \u003cem\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e is an amount of time in milliseconds. It may also be specified as a string containing the numerical value followed by a time unit (see \u003ca href=\"/docs/devel/config-setting.html#CONFIG-SETTING-NAMES-VALUES\" rel=\"nofollow\"\u003eSection 19.1.1\u003c/a\u003e). The maximum value is \u003ccode\u003e2147483647 ms\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eFractional values are rounded to the nearest millisecond. Note that a \u003cem\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e of half a millisecond or less therefore rounds down to zero, which means waiting indefinitely.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eNO_THROW\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecify to not throw an error in the case of timeout or running on the primary. In this case the result status can be obtained from the return value.\u003c/p\u003e\u003cp\u003eUse this option when \u003ccode\u003etimeout\u003c/code\u003e or \u003ccode\u003enot in recovery\u003c/code\u003e is an expected result that the application intends to handle, for example by retrying the wait, reporting replication delay, or choosing another server for a subsequent operation. The command then returns the result as a status, which the application must check before assuming that the target \u003cacronym\u003eLSN\u003c/acronym\u003e was reached. Omit the option when the application must not proceed unless the target \u003cacronym\u003eLSN\u003c/acronym\u003e is reached, so that an unsuccessful wait stops normal execution with an error.\u003c/p\u003e\u003cp\u003eReturning a status also leaves an explicit transaction usable; without this option, the corresponding error requires rolling back the transaction, or rolling back to a savepoint, before further commands can be issued.\u003c/p\u003e\u003cp\u003eThis option changes only how \u003ccode\u003etimeout\u003c/code\u003e and \u003ccode\u003enot in recovery\u003c/code\u003e are reported. Other errors are still raised. That covers invalid input, such as a malformed \u003cacronym\u003eLSN\u003c/acronym\u003e or an unrecognized option value, and every condition that is checked before the wait begins, such as requesting \u003ccode\u003eprimary_flush\u003c/code\u003e during recovery or holding a lock while waiting for a standby \u003cacronym\u003eLSN\u003c/acronym\u003e. The option also does not limit the duration of the wait; specify \u003ccode\u003eTIMEOUT\u003c/code\u003e for that purpose.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eOutputs\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003esuccess\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis return value denotes that we have successfully reached the target \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis return value denotes that the timeout happened before reaching the target \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003enot in recovery\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis return value denotes that the database server is not in a recovery state. This might mean either the database server was not in recovery at the moment of receiving the command (i.e., executed on a primary), or it was promoted before reaching the target \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e. In the promotion case, this status indicates a timeline change occurred, and the application should re-evaluate whether the target LSN is still relevant.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eNotes\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eWAIT\u003c/code\u003e must be executed as a top-level command. It cannot be executed from a function, procedure, or \u003ccode\u003eDO\u003c/code\u003e block. It also cannot be executed while the current transaction holds a snapshot. \u003ccode\u003eWAIT\u003c/code\u003e itself acquires none, so it can run before the first snapshot-taking statement of a \u003ccode\u003eREPEATABLE READ\u003c/code\u003e or \u003ccode\u003eSERIALIZABLE\u003c/code\u003e transaction, but not after it, and not while a cursor or an exported snapshot holds one at any isolation level. A snapshot held here could delay replay, which \u003ccode\u003estandby_replay\u003c/code\u003e waits for and which other standby modes can end up waiting for too. That is also why \u003ccode\u003eWAIT\u003c/code\u003e is a command rather than a function or a procedure, which execute with one held.\u003c/p\u003e\u003cp\u003eWhile recovery is in progress, a wait in \u003ccode\u003estandby_replay\u003c/code\u003e (the default), \u003ccode\u003estandby_write\u003c/code\u003e, or \u003ccode\u003estandby_flush\u003c/code\u003e mode is rejected when the session already holds a lock and the target \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e has not been reached yet. Such a lock can make the startup process wait for this session, either directly or through another session, while this session waits for the startup process to advance recovery. That cycle involves no lock wait on this side, so deadlock detection does not see it and nothing breaks it. A wait whose target has already been reached returns immediately and is therefore always allowed.\u003c/p\u003e\u003cp\u003eIssue \u003ccode\u003eWAIT FOR\u003c/code\u003e outside a transaction block, or as the first statement of one, before running anything that takes locks. That is also the natural order for the read-your-writes pattern shown in the examples below: wait for the target \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e first, then run the queries that have to see it. Note that a lock taken by an earlier statement is still held at \u003ccode\u003eREAD COMMITTED\u003c/code\u003e, even though its snapshot is gone, so a wait placed after such a statement is rejected even when the isolation level permits it.\u003c/p\u003e\u003cp\u003eThe restriction covers \u003ccode\u003estandby_write\u003c/code\u003e and \u003ccode\u003estandby_flush\u003c/code\u003e as well, even though streaming replication can advance those positions without the startup process. Both positions are at least the replay position, so without an active walreceiver replay can be their only source of progress. If a held lock blocks replay, the session waits for replay to advance while replay waits for the session to release the lock. Under streaming replication the positions advance independently only while WAL keeps arriving. If reception stops before the target is reached, a blocked startup process cannot restart the walreceiver. It also cannot replay newer checkpoint records needed to advance restartpoints and recycle WAL, so \u003ccode\u003epg_wal\u003c/code\u003e can fill up and reception can stop before the target is reached. The restriction therefore also applies when streaming is active at the start of the wait.\u003c/p\u003e\u003cp\u003e\u003ccode\u003eWAIT\u003c/code\u003e waits until the specified \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached according to the specified \u003cem\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e. The \u003ccode\u003estandby_replay\u003c/code\u003e mode waits for the LSN to be replayed (applied to the database), which is useful to achieve read-your-writes consistency while using an async replica for reads and the primary for writes, provided that the target \u003cacronym\u003eLSN\u003c/acronym\u003e is at or after the end of the relevant write transaction\u0026#39;s \u003ccode\u003eCOMMIT\u003c/code\u003e record on the primary. The \u003ccode\u003estandby_flush\u003c/code\u003e mode waits for the WAL to be flushed to durable storage on the replica, or to have already been replayed from WAL present on the standby. The \u003ccode\u003estandby_write\u003c/code\u003e mode waits for the WAL to be written to the operating system, or to have already been replayed, which is faster than flush for newly received WAL but provides weaker durability guarantees. The \u003ccode\u003eprimary_flush\u003c/code\u003e mode waits for WAL to be flushed on a primary server. In all cases, the \u003cacronym\u003eLSN\u003c/acronym\u003e of the last modification should be stored on the client application side or the connection pooler side.\u003c/p\u003e\u003cp\u003eThe standby modes (\u003ccode\u003estandby_replay\u003c/code\u003e, \u003ccode\u003estandby_write\u003c/code\u003e, \u003ccode\u003estandby_flush\u003c/code\u003e) can only be used during recovery, and \u003ccode\u003eprimary_flush\u003c/code\u003e can only be used on a primary server. Using the wrong mode for the current server state will result in an error. If a standby is promoted while waiting with a standby mode, the command will return \u003ccode\u003enot in recovery\u003c/code\u003e (or throw an error if \u003ccode\u003eNO_THROW\u003c/code\u003e is not specified). Promotion creates a new timeline, and the LSN being waited for may refer to WAL from the old timeline.\u003c/p\u003e\u003cp\u003e\u003ccode\u003eWAIT\u003c/code\u003e compares only the numeric \u003cacronym\u003eLSN\u003c/acronym\u003e; it has no notion of which timeline a WAL record belongs to. This matters when a standby continues recovery across an upstream timeline switch — for example, a cascading standby whose upstream gets promoted. In that case \u003ccode\u003eWAIT\u003c/code\u003e will return \u003ccode\u003esuccess\u003c/code\u003e as soon as the position used by the selected wait mode reaches or passes the numeric \u003cacronym\u003eLSN\u003c/acronym\u003e, regardless of which timeline that \u003cacronym\u003eLSN\u003c/acronym\u003e belongs to. Applications that need to confirm the target refers to the expected timeline must validate the timeline themselves.\u003c/p\u003e\u003cp\u003eOn a standby server, \u003ccode\u003eWAIT\u003c/code\u003e sessions may be interrupted by recovery conflicts. Some recovery conflicts are unavoidable: for example, replaying a tablespace drop resolves conflicts by terminating all backends, regardless of what they are doing. Applications using \u003ccode\u003eWAIT\u003c/code\u003e on a standby should be prepared to handle such interruptions, for example by retrying the command or falling back to an alternative mechanism.\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eExamples\u003c/h2\u003e\u003cp\u003eYou can use the \u003ccode\u003eWAIT\u003c/code\u003e command to wait for the \u003ccode\u003epg_lsn\u003c/code\u003e value. For example, an application could update the \u003ccode\u003emovie\u003c/code\u003e table and get an \u003cacronym\u003elsn\u003c/acronym\u003e that is at or after the end of the relevant write transaction\u0026#39;s \u003ccode\u003eCOMMIT\u003c/code\u003e record. In the default autocommit mode shown here, the \u003ccode\u003eUPDATE\u003c/code\u003e commits before the subsequent \u003ccode\u003eSELECT\u003c/code\u003e. This example uses \u003ccode\u003epg_current_wal_insert_lsn\u003c/code\u003e on primary server to get the \u003cacronym\u003elsn\u003c/acronym\u003e given that \u003ccode\u003esynchronous_commit\u003c/code\u003e could be set to \u003ccode\u003eoff\u003c/code\u003e.\u003c/p\u003e\u003cpre\u003epostgres=# UPDATE movie SET genre = \u0026#39;Dramatic\u0026#39; WHERE genre = \u0026#39;Drama\u0026#39;;\nUPDATE 100\npostgres=# SELECT pg_current_wal_insert_lsn();\n pg_current_wal_insert_lsn\n---------------------------\n 0/0306EE20\n(1 row)\n\u003c/pre\u003e\u003cp\u003eThen an application could run \u003ccode\u003eWAIT\u003c/code\u003e with the \u003cem\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e obtained from the primary after the commit. After that, the changes made on the primary should be guaranteed to be visible on the replica.\u003c/p\u003e\u003cpre\u003epostgres=# WAIT FOR LSN \u0026#39;0/0306EE20\u0026#39;;\n status\n---------\n success\n(1 row)\npostgres=# SELECT * FROM movie WHERE genre = \u0026#39;Drama\u0026#39;;\n genre\n-------\n(0 rows)\n\u003c/pre\u003e\u003cp\u003eWait for flush (data durable on replica):\u003c/p\u003e\u003cpre\u003epostgres=# WAIT FOR LSN \u0026#39;0/0306EE20\u0026#39; WITH (MODE \u0026#39;standby_flush\u0026#39;);\n status\n---------\n success\n(1 row)\n\u003c/pre\u003e\u003cp\u003eWait for write with timeout:\u003c/p\u003e\u003cpre\u003epostgres=# WAIT FOR LSN \u0026#39;0/0306EE20\u0026#39; WITH (MODE \u0026#39;standby_write\u0026#39;, TIMEOUT \u0026#39;100ms\u0026#39;, NO_THROW);\n status\n---------\n success\n(1 row)\n\u003c/pre\u003e\u003cp\u003eWait for flush on primary:\u003c/p\u003e\u003cpre\u003epostgres=# WAIT FOR LSN \u0026#39;0/0306EE20\u0026#39; WITH (MODE \u0026#39;primary_flush\u0026#39;);\n status\n---------\n success\n(1 row)\n\u003c/pre\u003e\u003cp\u003eIf the target LSN is not reached before the timeout, an error is thrown:\u003c/p\u003e\u003cpre\u003epostgres=# WAIT FOR LSN \u0026#39;0/0306EE20\u0026#39; WITH (TIMEOUT \u0026#39;0.1s\u0026#39;);\nERROR:  timed out while waiting for target LSN 0/0306EE20 to be replayed; current standby_replay LSN 0/0306EA60\n\u003c/pre\u003e\u003cp\u003eThe same example uses \u003ccode\u003eWAIT\u003c/code\u003e with the \u003cem\u003e\u003ccode\u003eNO_THROW\u003c/code\u003e\u003c/em\u003e option:\u003c/p\u003e\u003cpre\u003epostgres=# WAIT FOR LSN \u0026#39;0/0306EE20\u0026#39; WITH (TIMEOUT \u0026#39;100ms\u0026#39;, NO_THROW);\n status\n---------\n timeout\n(1 row)\n\u003c/pre\u003e\u003c/section\u003e","SourceRevision":"b7bd9cda","ContentHash":"beab7a4fc32f89c8bc22a07b74163f95f0f619f14e9f73958bbe9e46d1be7e8d","Payload":{"related":[],"sections":[{"html":"\u003cp\u003eWaits until the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached according to the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e, which determines whether to wait for WAL to be written, flushed, or replayed. If no \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e is specified or it is set to zero, this command waits indefinitely for the \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003cp\u003eOn timeout, an error is emitted unless \u003ccode class=\"literal\"\u003eNO_THROW\u003c/code\u003e is specified in the WITH clause. For standby modes (\u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e, \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e, \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e), an error is also emitted if the server is promoted before the \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached. If \u003cem class=\"parameter\"\u003e\u003ccode\u003eNO_THROW\u003c/code\u003e\u003c/em\u003e is specified, the command reports these outcomes as a status string instead of raising an error.\u003c/p\u003e\u003cp\u003eThe possible return values are \u003ccode class=\"literal\"\u003esuccess\u003c/code\u003e, \u003ccode class=\"literal\"\u003etimeout\u003c/code\u003e, and \u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies the target \u003cacronym\u003eLSN\u003c/acronym\u003e to wait for.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause specifies optional parameters for the wait operation. The following parameters are supported:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eMODE\u003c/code\u003e '\u003cem class=\"replaceable\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e'\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies the type of LSN processing to wait for. If not specified, the default is \u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e. The valid modes are:\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e: Wait for the LSN to be replayed (applied to the database) on a standby server. After successful completion, \u003ccode class=\"function\"\u003epg_last_wal_replay_lsn()\u003c/code\u003e will return a value greater than or equal to the target LSN. This mode can only be used during recovery.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e: Wait for the WAL containing the LSN to be written to disk on a standby server, but not yet necessarily flushed. This is faster than \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e but provides weaker durability guarantees since the data may still be in operating system buffers. This is satisfied by WAL already present on the standby from a base backup, archive restore, or prior streaming, as well as WAL newly received from the primary. This mode can only be used during recovery.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e: Wait for the WAL containing the LSN to be flushed to disk on a standby server. This provides a durability guarantee without waiting for the WAL to be applied. This is satisfied by WAL already present on the standby from a base backup, archive restore, or prior streaming, as well as WAL newly received from the primary. This mode can only be used during recovery.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eprimary_flush\u003c/code\u003e: Wait for the WAL containing the LSN to be flushed to disk on a primary server. After successful completion, \u003ccode class=\"function\"\u003epg_current_wal_flush_lsn()\u003c/code\u003e will return a value greater than or equal to the target LSN. This mode can only be used on a primary server (not during recovery).\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTIMEOUT\u003c/code\u003e '\u003cem class=\"replaceable\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e'\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eWhen specified and \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e is greater than zero, the command waits until \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached or the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e has elapsed. A value of zero (the default) means the command waits indefinitely.\u003c/p\u003e\u003cp\u003eThe \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e is an amount of time in milliseconds. It may also be specified as a string containing the numerical value followed by a time unit (see \u003ca href=\"/docs/devel/config-setting.html#CONFIG-SETTING-NAMES-VALUES\" title=\"19.1.1. Parameter Names and Values\"\u003eSection 19.1.1\u003c/a\u003e). The maximum value is \u003ccode class=\"literal\"\u003e2147483647 ms\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eFractional values are rounded to the nearest millisecond. Note that a \u003cem class=\"parameter\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e of half a millisecond or less therefore rounds down to zero, which means waiting indefinitely.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO_THROW\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecify to not throw an error in the case of timeout or running on the primary. In this case the result status can be obtained from the return value.\u003c/p\u003e\u003cp\u003eUse this option when \u003ccode class=\"literal\"\u003etimeout\u003c/code\u003e or \u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e is an expected result that the application intends to handle, for example by retrying the wait, reporting replication delay, or choosing another server for a subsequent operation. The command then returns the result as a status, which the application must check before assuming that the target \u003cacronym\u003eLSN\u003c/acronym\u003e was reached. Omit the option when the application must not proceed unless the target \u003cacronym\u003eLSN\u003c/acronym\u003e is reached, so that an unsuccessful wait stops normal execution with an error.\u003c/p\u003e\u003cp\u003eReturning a status also leaves an explicit transaction usable; without this option, the corresponding error requires rolling back the transaction, or rolling back to a savepoint, before further commands can be issued.\u003c/p\u003e\u003cp\u003eThis option changes only how \u003ccode class=\"literal\"\u003etimeout\u003c/code\u003e and \u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e are reported. Other errors are still raised. That covers invalid input, such as a malformed \u003cacronym\u003eLSN\u003c/acronym\u003e or an unrecognized option value, and every condition that is checked before the wait begins, such as requesting \u003ccode class=\"literal\"\u003eprimary_flush\u003c/code\u003e during recovery or holding a lock while waiting for a standby \u003cacronym\u003eLSN\u003c/acronym\u003e. The option also does not limit the duration of the wait; specify \u003ccode class=\"literal\"\u003eTIMEOUT\u003c/code\u003e for that purpose.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003esuccess\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis return value denotes that we have successfully reached the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003etimeout\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis return value denotes that the timeout happened before reaching the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis return value denotes that the database server is not in a recovery state. This might mean either the database server was not in recovery at the moment of receiving the command (i.e., executed on a primary), or it was promoted before reaching the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e. In the promotion case, this status indicates a timeline change occurred, and the application should re-evaluate whether the target LSN is still relevant.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"outputs","title":"Outputs"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e must be executed as a top-level command. It cannot be executed from a function, procedure, or \u003ccode class=\"command\"\u003eDO\u003c/code\u003e block. It also cannot be executed while the current transaction holds a snapshot. \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e itself acquires none, so it can run before the first snapshot-taking statement of a \u003ccode class=\"literal\"\u003eREPEATABLE READ\u003c/code\u003e or \u003ccode class=\"literal\"\u003eSERIALIZABLE\u003c/code\u003e transaction, but not after it, and not while a cursor or an exported snapshot holds one at any isolation level. A snapshot held here could delay replay, which \u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e waits for and which other standby modes can end up waiting for too. That is also why \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e is a command rather than a function or a procedure, which execute with one held.\u003c/p\u003e\u003cp\u003eWhile recovery is in progress, a wait in \u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e (the default), \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e, or \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e mode is rejected when the session already holds a lock and the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e has not been reached yet. Such a lock can make the startup process wait for this session, either directly or through another session, while this session waits for the startup process to advance recovery. That cycle involves no lock wait on this side, so deadlock detection does not see it and nothing breaks it. A wait whose target has already been reached returns immediately and is therefore always allowed.\u003c/p\u003e\u003cp\u003eIssue \u003ccode class=\"command\"\u003eWAIT FOR\u003c/code\u003e outside a transaction block, or as the first statement of one, before running anything that takes locks. That is also the natural order for the read-your-writes pattern shown in the examples below: wait for the target \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e first, then run the queries that have to see it. Note that a lock taken by an earlier statement is still held at \u003ccode class=\"literal\"\u003eREAD COMMITTED\u003c/code\u003e, even though its snapshot is gone, so a wait placed after such a statement is rejected even when the isolation level permits it.\u003c/p\u003e\u003cp\u003eThe restriction covers \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e and \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e as well, even though streaming replication can advance those positions without the startup process. Both positions are at least the replay position, so without an active walreceiver replay can be their only source of progress. If a held lock blocks replay, the session waits for replay to advance while replay waits for the session to release the lock. Under streaming replication the positions advance independently only while WAL keeps arriving. If reception stops before the target is reached, a blocked startup process cannot restart the walreceiver. It also cannot replay newer checkpoint records needed to advance restartpoints and recycle WAL, so \u003ccode class=\"filename\"\u003epg_wal\u003c/code\u003e can fill up and reception can stop before the target is reached. The restriction therefore also applies when streaming is active at the start of the wait.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e waits until the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e is reached according to the specified \u003cem class=\"parameter\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e. The \u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e mode waits for the LSN to be replayed (applied to the database), which is useful to achieve read-your-writes consistency while using an async replica for reads and the primary for writes, provided that the target \u003cacronym\u003eLSN\u003c/acronym\u003e is at or after the end of the relevant write transaction's \u003ccode class=\"literal\"\u003eCOMMIT\u003c/code\u003e record on the primary. The \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e mode waits for the WAL to be flushed to durable storage on the replica, or to have already been replayed from WAL present on the standby. The \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e mode waits for the WAL to be written to the operating system, or to have already been replayed, which is faster than flush for newly received WAL but provides weaker durability guarantees. The \u003ccode class=\"literal\"\u003eprimary_flush\u003c/code\u003e mode waits for WAL to be flushed on a primary server. In all cases, the \u003cacronym\u003eLSN\u003c/acronym\u003e of the last modification should be stored on the client application side or the connection pooler side.\u003c/p\u003e\u003cp\u003eThe standby modes (\u003ccode class=\"literal\"\u003estandby_replay\u003c/code\u003e, \u003ccode class=\"literal\"\u003estandby_write\u003c/code\u003e, \u003ccode class=\"literal\"\u003estandby_flush\u003c/code\u003e) can only be used during recovery, and \u003ccode class=\"literal\"\u003eprimary_flush\u003c/code\u003e can only be used on a primary server. Using the wrong mode for the current server state will result in an error. If a standby is promoted while waiting with a standby mode, the command will return \u003ccode class=\"literal\"\u003enot in recovery\u003c/code\u003e (or throw an error if \u003ccode class=\"literal\"\u003eNO_THROW\u003c/code\u003e is not specified). Promotion creates a new timeline, and the LSN being waited for may refer to WAL from the old timeline.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e compares only the numeric \u003cacronym\u003eLSN\u003c/acronym\u003e; it has no notion of which timeline a WAL record belongs to. This matters when a standby continues recovery across an upstream timeline switch — for example, a cascading standby whose upstream gets promoted. In that case \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e will return \u003ccode class=\"literal\"\u003esuccess\u003c/code\u003e as soon as the position used by the selected wait mode reaches or passes the numeric \u003cacronym\u003eLSN\u003c/acronym\u003e, regardless of which timeline that \u003cacronym\u003eLSN\u003c/acronym\u003e belongs to. Applications that need to confirm the target refers to the expected timeline must validate the timeline themselves.\u003c/p\u003e\u003cp\u003eOn a standby server, \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e sessions may be interrupted by recovery conflicts. Some recovery conflicts are unavoidable: for example, replaying a tablespace drop resolves conflicts by terminating all backends, regardless of what they are doing. Applications using \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e on a standby should be prepared to handle such interruptions, for example by retrying the command or falling back to an alternative mechanism.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eYou can use the \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e command to wait for the \u003ccode class=\"type\"\u003epg_lsn\u003c/code\u003e value. For example, an application could update the \u003ccode class=\"literal\"\u003emovie\u003c/code\u003e table and get an \u003cacronym\u003elsn\u003c/acronym\u003e that is at or after the end of the relevant write transaction's \u003ccode class=\"literal\"\u003eCOMMIT\u003c/code\u003e record. In the default autocommit mode shown here, the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e commits before the subsequent \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e. This example uses \u003ccode class=\"function\"\u003epg_current_wal_insert_lsn\u003c/code\u003e on primary server to get the \u003cacronym\u003elsn\u003c/acronym\u003e given that \u003ccode class=\"varname\"\u003esynchronous_commit\u003c/code\u003e could be set to \u003ccode class=\"literal\"\u003eoff\u003c/code\u003e.\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';\nUPDATE 100\npostgres=# SELECT pg_current_wal_insert_lsn();\n pg_current_wal_insert_lsn\n---------------------------\n 0/0306EE20\n(1 row)\n\u003c/pre\u003e\u003cp\u003eThen an application could run \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e with the \u003cem class=\"parameter\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e obtained from the primary after the commit. After that, the changes made on the primary should be guaranteed to be visible on the replica.\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20';\n status\n---------\n success\n(1 row)\npostgres=# SELECT * FROM movie WHERE genre = 'Drama';\n genre\n-------\n(0 rows)\n\u003c/pre\u003e\u003cp\u003eWait for flush (data durable on replica):\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (MODE 'standby_flush');\n status\n---------\n success\n(1 row)\n\u003c/pre\u003e\u003cp\u003eWait for write with timeout:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (MODE 'standby_write', TIMEOUT '100ms', NO_THROW);\n status\n---------\n success\n(1 row)\n\u003c/pre\u003e\u003cp\u003eWait for flush on primary:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (MODE 'primary_flush');\n status\n---------\n success\n(1 row)\n\u003c/pre\u003e\u003cp\u003eIf the target LSN is not reached before the timeout, an error is thrown:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (TIMEOUT '0.1s');\nERROR:  timed out while waiting for target LSN 0/0306EE20 to be replayed; current standby_replay LSN 0/0306EA60\n\u003c/pre\u003e\u003cp\u003eThe same example uses \u003ccode class=\"command\"\u003eWAIT\u003c/code\u003e with the \u003cem class=\"parameter\"\u003e\u003ccode\u003eNO_THROW\u003c/code\u003e\u003c/em\u003e option:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# WAIT FOR LSN '0/0306EE20' WITH (TIMEOUT '100ms', NO_THROW);\n status\n---------\n timeout\n(1 row)\n\u003c/pre\u003e","key":"examples","title":"Examples"}],"sections_same_as":"","synopsis_html":"WAIT FOR LSN '\u003cem class=\"replaceable\"\u003e\u003ccode\u003elsn\u003c/code\u003e\u003c/em\u003e'\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e can be:\u003c/span\u003e\n\n    MODE '\u003cem class=\"replaceable\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e'\n    TIMEOUT '\u003cem class=\"replaceable\"\u003e\u003ccode\u003etimeout\u003c/code\u003e\u003c/em\u003e'\n    NO_THROW\n\n\u003cspan class=\"phrase\"\u003eand \u003cem class=\"replaceable\"\u003e\u003ccode\u003emode\u003c/code\u003e\u003c/em\u003e can be:\u003c/span\u003e\n\n    standby_replay | standby_write | standby_flush | primary_flush","synopsis_text":"WAIT FOR LSN 'lsn'\n[ WITH ( option [, ...] ) ]\n\nwhere option can be:\n\nMODE 'mode'\nTIMEOUT 'timeout'\nNO_THROW\n\nand mode can be:\n\nstandby_replay | standby_write | standby_flush | primary_flush"}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}