codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.27. System Administration Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-info.html" title="9.26. System Information Functions and Operators" /><link rel="next" href="functions-trigger.html" title="9.28. Trigger Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.27. System Administration Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-info.html" title="9.26. System Information Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-trigger.html" title="9.28. Trigger Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-ADMIN"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.27. System Administration Functions <a href="#FUNCTIONS-ADMIN" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-SET">9.27.1. Configuration Settings Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-SIGNAL">9.27.2. Server Signaling Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-BACKUP">9.27.3. Backup Control Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL">9.27.4. Recovery Control Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION">9.27.5. Snapshot Synchronization Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-REPLICATION">9.27.6. Replication Management Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT">9.27.7. Database Object Management Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-INDEX">9.27.8. Index Maintenance Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-GENFILE">9.27.9. Generic File Access Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADVISORY-LOCKS">9.27.10. Advisory Lock Functions</a></span></dt></dl></div><p>3 The functions described in this section are used to control and4 monitor a <span class="productname">PostgreSQL</span> installation.5 </p><div class="sect2" id="FUNCTIONS-ADMIN-SET"><div class="titlepage"><div><div><h3 class="title">9.27.1. Configuration Settings Functions <a href="#FUNCTIONS-ADMIN-SET" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.33.3.2" class="indexterm"></a><a id="id-1.5.8.33.3.3" class="indexterm"></a><a id="id-1.5.8.33.3.4" class="indexterm"></a><p>6 <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE" title="Table 9.89. Configuration Settings Functions">Table 9.89</a> shows the functions7 available to query and alter run-time configuration parameters.8 </p><div class="table" id="FUNCTIONS-ADMIN-SET-TABLE"><p class="title"><strong>Table 9.89. Configuration Settings Functions</strong></p><div class="table-contents"><table class="table" summary="Configuration Settings Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">9 Function10 </p>11 <p>12 Description13 </p>14 <p>15 Example(s)16 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">17 <a id="id-1.5.8.33.3.6.2.2.1.1.1.1" class="indexterm"></a>18 <code class="function">current_setting</code> ( <em class="parameter"><code>setting_name</code></em> <code class="type">text</code> [<span class="optional">, <em class="parameter"><code>missing_ok</code></em> <code class="type">boolean</code> </span>] )19 → <code class="returnvalue">text</code>20 </p>21 <p>22 Returns the current value of the23 setting <em class="parameter"><code>setting_name</code></em>. If there is no such24 setting, <code class="function">current_setting</code> throws an error25 unless <em class="parameter"><code>missing_ok</code></em> is supplied and26 is <code class="literal">true</code> (in which case NULL is returned).27 This function corresponds to28 the <acronym class="acronym">SQL</acronym> command <a class="xref" href="sql-show.html" title="SHOW"><span class="refentrytitle">SHOW</span></a>.29 </p>30 <p>31 <code class="literal">current_setting('datestyle')</code>32 → <code class="returnvalue">ISO, MDY</code>33 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">34 <a id="id-1.5.8.33.3.6.2.2.2.1.1.1" class="indexterm"></a>35 <code class="function">set_config</code> (36 <em class="parameter"><code>setting_name</code></em> <code class="type">text</code>,37 <em class="parameter"><code>new_value</code></em> <code class="type">text</code>,38 <em class="parameter"><code>is_local</code></em> <code class="type">boolean</code> )39 → <code class="returnvalue">text</code>40 </p>41 <p>42 Sets the parameter <em class="parameter"><code>setting_name</code></em>43 to <em class="parameter"><code>new_value</code></em>, and returns that value.44 If <em class="parameter"><code>is_local</code></em> is <code class="literal">true</code>, the new45 value will only apply during the current transaction. If you want the46 new value to apply for the rest of the current session,47 use <code class="literal">false</code> instead. This function corresponds to48 the SQL command <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a>.49 </p>50 <p>51 <code class="literal">set_config('log_statement_stats', 'off', false)</code>52 → <code class="returnvalue">off</code>53 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-ADMIN-SIGNAL"><div class="titlepage"><div><div><h3 class="title">9.27.2. Server Signaling Functions <a href="#FUNCTIONS-ADMIN-SIGNAL" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.33.4.2" class="indexterm"></a><p>54 The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE" title="Table 9.90. Server Signaling Functions">Table 9.90</a> send control signals to55 other server processes. Use of these functions is restricted to56 superusers by default but access may be granted to others using57 <code class="command">GRANT</code>, with noted exceptions.58 </p><p>59 Each of these functions returns <code class="literal">true</code> if60 the signal was successfully sent and <code class="literal">false</code>61 if sending the signal failed.62 </p><div class="table" id="FUNCTIONS-ADMIN-SIGNAL-TABLE"><p class="title"><strong>Table 9.90. Server Signaling Functions</strong></p><div class="table-contents"><table class="table" summary="Server Signaling Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">63 Function64 </p>65 <p>66 Description67 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">68 <a id="id-1.5.8.33.4.5.2.2.1.1.1.1" class="indexterm"></a>69 <code class="function">pg_cancel_backend</code> ( <em class="parameter"><code>pid</code></em> <code class="type">integer</code> )70 → <code class="returnvalue">boolean</code>71 </p>72 <p>73 Cancels the current query of the session whose backend process has the74 specified process ID. This is also allowed if the75 calling role is a member of the role whose backend is being canceled or76 the calling role has privileges of <code class="literal">pg_signal_backend</code>,77 however only superusers can cancel superuser backends.78 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">79 <a id="id-1.5.8.33.4.5.2.2.2.1.1.1" class="indexterm"></a>80 <code class="function">pg_log_backend_memory_contexts</code> ( <em class="parameter"><code>pid</code></em> <code class="type">integer</code> )81 → <code class="returnvalue">boolean</code>82 </p>83 <p>84 Requests to log the memory contexts of the backend with the85 specified process ID. This function can send the request to86 backends and auxiliary processes except logger. These memory contexts87 will be logged at88 <code class="literal">LOG</code> message level. They will appear in89 the server log based on the log configuration set90 (see <a class="xref" href="runtime-config-logging.html" title="20.8. Error Reporting and Logging">Section 20.8</a> for more information),91 but will not be sent to the client regardless of92 <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a>.93 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">94 <a id="id-1.5.8.33.4.5.2.2.3.1.1.1" class="indexterm"></a>95 <code class="function">pg_reload_conf</code> ()96 → <code class="returnvalue">boolean</code>97 </p>98 <p>99 Causes all processes of the <span class="productname">PostgreSQL</span>100 server to reload their configuration files. (This is initiated by101 sending a <span class="systemitem">SIGHUP</span> signal to the postmaster102 process, which in turn sends <span class="systemitem">SIGHUP</span> to each103 of its children.) You can use the104 <a class="link" href="view-pg-file-settings.html" title="54.7. pg_file_settings"><code class="structname">pg_file_settings</code></a>,105 <a class="link" href="view-pg-hba-file-rules.html" title="54.9. pg_hba_file_rules"><code class="structname">pg_hba_file_rules</code></a> and106 <a class="link" href="view-pg-ident-file-mappings.html" title="54.10. pg_ident_file_mappings"><code class="structname">pg_ident_file_mappings</code></a> views107 to check the configuration files for possible errors, before reloading.108 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">109 <a id="id-1.5.8.33.4.5.2.2.4.1.1.1" class="indexterm"></a>110 <code class="function">pg_rotate_logfile</code> ()111 → <code class="returnvalue">boolean</code>112 </p>113 <p>114 Signals the log-file manager to switch to a new output file115 immediately. This works only when the built-in log collector is116 running, since otherwise there is no log-file manager subprocess.117 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">118 <a id="id-1.5.8.33.4.5.2.2.5.1.1.1" class="indexterm"></a>119 <code class="function">pg_terminate_backend</code> ( <em class="parameter"><code>pid</code></em> <code class="type">integer</code>, <em class="parameter"><code>timeout</code></em> <code class="type">bigint</code> <code class="literal">DEFAULT</code> <code class="literal">0</code> )120 → <code class="returnvalue">boolean</code>121 </p>122 <p>123 Terminates the session whose backend process has the124 specified process ID. This is also allowed if the calling role125 is a member of the role whose backend is being terminated or the126 calling role has privileges of <code class="literal">pg_signal_backend</code>,127 however only superusers can terminate superuser backends.128 </p>129 <p>130 If <em class="parameter"><code>timeout</code></em> is not specified or zero, this131 function returns <code class="literal">true</code> whether the process actually132 terminates or not, indicating only that the sending of the signal was133 successful. If the <em class="parameter"><code>timeout</code></em> is specified (in134 milliseconds) and greater than zero, the function waits until the135 process is actually terminated or until the given time has passed. If136 the process is terminated, the function137 returns <code class="literal">true</code>. On timeout, a warning is emitted and138 <code class="literal">false</code> is returned.139 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>140 <code class="function">pg_cancel_backend</code> and <code class="function">pg_terminate_backend</code>141 send signals (<span class="systemitem">SIGINT</span> or <span class="systemitem">SIGTERM</span>142 respectively) to backend processes identified by process ID.143 The process ID of an active backend can be found from144 the <code class="structfield">pid</code> column of the145 <code class="structname">pg_stat_activity</code> view, or by listing the146 <code class="command">postgres</code> processes on the server (using147 <span class="application">ps</span> on Unix or the <span class="application">Task148 Manager</span> on <span class="productname">Windows</span>).149 The role of an active backend can be found from the150 <code class="structfield">usename</code> column of the151 <code class="structname">pg_stat_activity</code> view.152 </p><p>153 <code class="function">pg_log_backend_memory_contexts</code> can be used154 to log the memory contexts of a backend process. For example:155</p><pre class="programlisting">156postgres=# SELECT pg_log_backend_memory_contexts(pg_backend_pid());157 pg_log_backend_memory_contexts158--------------------------------159 t160(1 row)161</pre><p>162One message for each memory context will be logged. For example:163</p><pre class="screen">164LOG: logging memory contexts of PID 10377165STATEMENT: SELECT pg_log_backend_memory_contexts(pg_backend_pid());166LOG: level: 0; TopMemoryContext: 80800 total in 6 blocks; 14432 free (5 chunks); 66368 used167LOG: level: 1; pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 1408 free (0 chunks); 6784 used168LOG: level: 1; TopTransactionContext: 8192 total in 1 blocks; 7720 free (1 chunks); 472 used169LOG: level: 1; RowDescriptionContext: 8192 total in 1 blocks; 6880 free (0 chunks); 1312 used170LOG: level: 1; MessageContext: 16384 total in 2 blocks; 5152 free (0 chunks); 11232 used171LOG: level: 1; Operator class cache: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used172LOG: level: 1; smgr relation table: 16384 total in 2 blocks; 4544 free (3 chunks); 11840 used173LOG: level: 1; TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used174...175LOG: level: 1; ErrorContext: 8192 total in 1 blocks; 7928 free (3 chunks); 264 used176LOG: Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560 used177</pre><p>178 If there are more than 100 child contexts under the same parent, the first179 100 child contexts are logged, along with a summary of the remaining contexts.180 Note that frequent calls to this function could incur significant overhead,181 because it may generate a large number of log messages.182 </p></div><div class="sect2" id="FUNCTIONS-ADMIN-BACKUP"><div class="titlepage"><div><div><h3 class="title">9.27.3. Backup Control Functions <a href="#FUNCTIONS-ADMIN-BACKUP" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.33.5.2" class="indexterm"></a><p>183 The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE" title="Table 9.91. Backup Control Functions">Table 9.91</a> assist in making on-line backups.184 These functions cannot be executed during recovery (except185 <code class="function">pg_backup_start</code>,186 <code class="function">pg_backup_stop</code>,187 and <code class="function">pg_wal_lsn_diff</code>).188 </p><p>189 For details about proper usage of these functions, see190 <a class="xref" href="continuous-archiving.html" title="26.3. Continuous Archiving and Point-in-Time Recovery (PITR)">Section 26.3</a>.191 </p><div class="table" id="FUNCTIONS-ADMIN-BACKUP-TABLE"><p class="title"><strong>Table 9.91. Backup Control Functions</strong></p><div class="table-contents"><table class="table" summary="Backup Control Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">192 Function193 </p>194 <p>195 Description196 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">197 <a id="id-1.5.8.33.5.5.2.2.1.1.1.1" class="indexterm"></a>198 <code class="function">pg_create_restore_point</code> ( <em class="parameter"><code>name</code></em> <code class="type">text</code> )199 → <code class="returnvalue">pg_lsn</code>200 </p>201 <p>202 Creates a named marker record in the write-ahead log that can later be203 used as a recovery target, and returns the corresponding write-ahead204 log location. The given name can then be used with205 <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-NAME">recovery_target_name</a> to specify the point up to206 which recovery will proceed. Avoid creating multiple restore points207 with the same name, since recovery will stop at the first one whose208 name matches the recovery target.209 </p>210 <p>211 This function is restricted to superusers by default, but other users212 can be granted EXECUTE to run the function.213 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">214 <a id="id-1.5.8.33.5.5.2.2.2.1.1.1" class="indexterm"></a>215 <code class="function">pg_current_wal_flush_lsn</code> ()216 → <code class="returnvalue">pg_lsn</code>217 </p>218 <p>219 Returns the current write-ahead log flush location (see notes below).220 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">221 <a id="id-1.5.8.33.5.5.2.2.3.1.1.1" class="indexterm"></a>222 <code class="function">pg_current_wal_insert_lsn</code> ()223 → <code class="returnvalue">pg_lsn</code>224 </p>225 <p>226 Returns the current write-ahead log insert location (see notes below).227 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">228 <a id="id-1.5.8.33.5.5.2.2.4.1.1.1" class="indexterm"></a>229 <code class="function">pg_current_wal_lsn</code> ()230 → <code class="returnvalue">pg_lsn</code>231 </p>232 <p>233 Returns the current write-ahead log write location (see notes below).234 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">235 <a id="id-1.5.8.33.5.5.2.2.5.1.1.1" class="indexterm"></a>236 <code class="function">pg_backup_start</code> (237 <em class="parameter"><code>label</code></em> <code class="type">text</code>238 [<span class="optional">, <em class="parameter"><code>fast</code></em> <code class="type">boolean</code>239 </span>] )240 → <code class="returnvalue">pg_lsn</code>241 </p>242 <p>243 Prepares the server to begin an on-line backup. The only required244 parameter is an arbitrary user-defined label for the backup.245 (Typically this would be the name under which the backup dump file246 will be stored.)247 If the optional second parameter is given as <code class="literal">true</code>,248 it specifies executing <code class="function">pg_backup_start</code> as quickly249 as possible. This forces an immediate checkpoint which will cause a250 spike in I/O operations, slowing any concurrently executing queries.251 </p>252 <p>253 This function is restricted to superusers by default, but other users254 can be granted EXECUTE to run the function.255 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">256 <a id="id-1.5.8.33.5.5.2.2.6.1.1.1" class="indexterm"></a>257 <code class="function">pg_backup_stop</code> (258 [<span class="optional"><em class="parameter"><code>wait_for_archive</code></em> <code class="type">boolean</code>259 </span>] )260 → <code class="returnvalue">record</code>261 ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>,262 <em class="parameter"><code>labelfile</code></em> <code class="type">text</code>,263 <em class="parameter"><code>spcmapfile</code></em> <code class="type">text</code> )264 </p>265 <p>266 Finishes performing an on-line backup. The desired contents of the267 backup label file and the tablespace map file are returned as part of268 the result of the function and must be written to files in the269 backup area. These files must not be written to the live data directory270 (doing so will cause PostgreSQL to fail to restart in the event of a271 crash).272 </p>273 <p>274 There is an optional parameter of type <code class="type">boolean</code>.275 If false, the function will return immediately after the backup is276 completed, without waiting for WAL to be archived. This behavior is277 only useful with backup software that independently monitors WAL278 archiving. Otherwise, WAL required to make the backup consistent might279 be missing and make the backup useless. By default or when this280 parameter is true, <code class="function">pg_backup_stop</code> will wait for281 WAL to be archived when archiving is enabled. (On a standby, this282 means that it will wait only when <code class="varname">archive_mode</code> =283 <code class="literal">always</code>. If write activity on the primary is low,284 it may be useful to run <code class="function">pg_switch_wal</code> on the285 primary in order to trigger an immediate segment switch.)286 </p>287 <p>288 When executed on a primary, this function also creates a backup289 history file in the write-ahead log archive area. The history file290 includes the label given to <code class="function">pg_backup_start</code>, the291 starting and ending write-ahead log locations for the backup, and the292 starting and ending times of the backup. After recording the ending293 location, the current write-ahead log insertion point is automatically294 advanced to the next write-ahead log file, so that the ending295 write-ahead log file can be archived immediately to complete the296 backup.297 </p>298 <p>299 The result of the function is a single record.300 The <em class="parameter"><code>lsn</code></em> column holds the backup's ending301 write-ahead log location (which again can be ignored). The second302 column returns the contents of the backup label file, and the third303 column returns the contents of the tablespace map file. These must be304 stored as part of the backup and are required as part of the restore305 process.306 </p>307 <p>308 This function is restricted to superusers by default, but other users309 can be granted EXECUTE to run the function.310 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">311 <a id="id-1.5.8.33.5.5.2.2.7.1.1.1" class="indexterm"></a>312 <code class="function">pg_switch_wal</code> ()313 → <code class="returnvalue">pg_lsn</code>314 </p>315 <p>316 Forces the server to switch to a new write-ahead log file, which317 allows the current file to be archived (assuming you are using318 continuous archiving). The result is the ending write-ahead log319 location plus 1 within the just-completed write-ahead log file. If320 there has been no write-ahead log activity since the last write-ahead321 log switch, <code class="function">pg_switch_wal</code> does nothing and322 returns the start location of the write-ahead log file currently in323 use.324 </p>325 <p>326 This function is restricted to superusers by default, but other users327 can be granted EXECUTE to run the function.328 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">329 <a id="id-1.5.8.33.5.5.2.2.8.1.1.1" class="indexterm"></a>330 <code class="function">pg_walfile_name</code> ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> )331 → <code class="returnvalue">text</code>332 </p>333 <p>334 Converts a write-ahead log location to the name of the WAL file335 holding that location.336 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">337 <a id="id-1.5.8.33.5.5.2.2.9.1.1.1" class="indexterm"></a>338 <code class="function">pg_walfile_name_offset</code> ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> )339 → <code class="returnvalue">record</code>340 ( <em class="parameter"><code>file_name</code></em> <code class="type">text</code>,341 <em class="parameter"><code>file_offset</code></em> <code class="type">integer</code> )342 </p>343 <p>344 Converts a write-ahead log location to a WAL file name and byte offset345 within that file.346 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">347 <a id="id-1.5.8.33.5.5.2.2.10.1.1.1" class="indexterm"></a>348 <code class="function">pg_split_walfile_name</code> ( <em class="parameter"><code>file_name</code></em> <code class="type">text</code> )349 → <code class="returnvalue">record</code>350 ( <em class="parameter"><code>segment_number</code></em> <code class="type">numeric</code>,351 <em class="parameter"><code>timeline_id</code></em> <code class="type">bigint</code> )352 </p>353 <p>354 Extracts the sequence number and timeline ID from a WAL file355 name.356 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">357 <a id="id-1.5.8.33.5.5.2.2.11.1.1.1" class="indexterm"></a>358 <code class="function">pg_wal_lsn_diff</code> ( <em class="parameter"><code>lsn1</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>lsn2</code></em> <code class="type">pg_lsn</code> )359 → <code class="returnvalue">numeric</code>360 </p>361 <p>362 Calculates the difference in bytes (<em class="parameter"><code>lsn1</code></em> - <em class="parameter"><code>lsn2</code></em>) between two write-ahead log363 locations. This can be used364 with <code class="structname">pg_stat_replication</code> or some of the365 functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE" title="Table 9.91. Backup Control Functions">Table 9.91</a> to366 get the replication lag.367 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>368 <code class="function">pg_current_wal_lsn</code> displays the current write-ahead369 log write location in the same format used by the above functions.370 Similarly, <code class="function">pg_current_wal_insert_lsn</code> displays the371 current write-ahead log insertion location372 and <code class="function">pg_current_wal_flush_lsn</code> displays the current373 write-ahead log flush location. The insertion location is374 the <span class="quote">“<span class="quote">logical</span>”</span> end of the write-ahead log at any instant,375 while the write location is the end of what has actually been written out376 from the server's internal buffers, and the flush location is the last377 location known to be written to durable storage. The write location is the378 end of what can be examined from outside the server, and is usually what379 you want if you are interested in archiving partially-complete write-ahead380 log files. The insertion and flush locations are made available primarily381 for server debugging purposes. These are all read-only operations and do382 not require superuser permissions.383 </p><p>384 You can use <code class="function">pg_walfile_name_offset</code> to extract the385 corresponding write-ahead log file name and byte offset from386 a <code class="type">pg_lsn</code> value. For example:387</p><pre class="programlisting">388postgres=# SELECT * FROM pg_walfile_name_offset((pg_backup_stop()).lsn);389 file_name | file_offset390--------------------------+-------------391 00000001000000000000000D | 4039624392(1 row)393</pre><p>394 Similarly, <code class="function">pg_walfile_name</code> extracts just the write-ahead log file name.395 When the given write-ahead log location is exactly at a write-ahead log file boundary, both396 these functions return the name of the preceding write-ahead log file.397 This is usually the desired behavior for managing write-ahead log archiving398 behavior, since the preceding file is the last one that currently399 needs to be archived.400 </p><p>401 <code class="function">pg_split_walfile_name</code> is useful to compute a402 <acronym class="acronym">LSN</acronym> from a file offset and WAL file name, for example:403</p><pre class="programlisting">404postgres=# \set file_name '000000010000000100C000AB'405postgres=# \set offset 256406postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset AS lsn407 FROM pg_split_walfile_name(:'file_name') pd,408 pg_show_all_settings() ps409 WHERE ps.name = 'wal_segment_size';410 lsn411---------------412 C001/AB000100413(1 row)414</pre><p>415 </p></div><div class="sect2" id="FUNCTIONS-RECOVERY-CONTROL"><div class="titlepage"><div><div><h3 class="title">9.27.4. Recovery Control Functions <a href="#FUNCTIONS-RECOVERY-CONTROL" class="id_link">#</a></h3></div></div></div><p>416 The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE" title="Table 9.92. Recovery Information Functions">Table 9.92</a> provide information417 about the current status of a standby server.418 These functions may be executed both during recovery and in normal running.419 </p><div class="table" id="FUNCTIONS-RECOVERY-INFO-TABLE"><p class="title"><strong>Table 9.92. Recovery Information Functions</strong></p><div class="table-contents"><table class="table" summary="Recovery Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">420 Function421 </p>422 <p>423 Description424 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">425 <a id="id-1.5.8.33.6.3.2.2.1.1.1.1" class="indexterm"></a>426 <code class="function">pg_is_in_recovery</code> ()427 → <code class="returnvalue">boolean</code>428 </p>429 <p>430 Returns true if recovery is still in progress.431 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">432 <a id="id-1.5.8.33.6.3.2.2.2.1.1.1" class="indexterm"></a>433 <code class="function">pg_last_wal_receive_lsn</code> ()434 → <code class="returnvalue">pg_lsn</code>435 </p>436 <p>437 Returns the last write-ahead log location that has been received and438 synced to disk by streaming replication. While streaming replication439 is in progress this will increase monotonically. If recovery has440 completed then this will remain static at the location of the last WAL441 record received and synced to disk during recovery. If streaming442 replication is disabled, or if it has not yet started, the function443 returns <code class="literal">NULL</code>.444 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">445 <a id="id-1.5.8.33.6.3.2.2.3.1.1.1" class="indexterm"></a>446 <code class="function">pg_last_wal_replay_lsn</code> ()447 → <code class="returnvalue">pg_lsn</code>448 </p>449 <p>450 Returns the last write-ahead log location that has been replayed451 during recovery. If recovery is still in progress this will increase452 monotonically. If recovery has completed then this will remain453 static at the location of the last WAL record applied during recovery.454 When the server has been started normally without recovery, the455 function returns <code class="literal">NULL</code>.456 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">457 <a id="id-1.5.8.33.6.3.2.2.4.1.1.1" class="indexterm"></a>458 <code class="function">pg_last_xact_replay_timestamp</code> ()459 → <code class="returnvalue">timestamp with time zone</code>460 </p>461 <p>462 Returns the time stamp of the last transaction replayed during463 recovery. This is the time at which the commit or abort WAL record464 for that transaction was generated on the primary. If no transactions465 have been replayed during recovery, the function466 returns <code class="literal">NULL</code>. Otherwise, if recovery is still in467 progress this will increase monotonically. If recovery has completed468 then this will remain static at the time of the last transaction469 applied during recovery. When the server has been started normally470 without recovery, the function returns <code class="literal">NULL</code>.471 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">472 <a id="id-1.5.8.33.6.3.2.2.5.1.1.1" class="indexterm"></a>473 <code class="function">pg_get_wal_resource_managers</code> ()474 → <code class="returnvalue">setof record</code>475 ( <em class="parameter"><code>rm_id</code></em> <code class="type">integer</code>,476 <em class="parameter"><code>rm_name</code></em> <code class="type">text</code>,477 <em class="parameter"><code>rm_builtin</code></em> <code class="type">boolean</code> )478 </p>479 <p>480 Returns the currently-loaded WAL resource managers in the system. The481 column <em class="parameter"><code>rm_builtin</code></em> indicates whether it's a482 built-in resource manager, or a custom resource manager loaded by an483 extension.484 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>485 The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE" title="Table 9.93. Recovery Control Functions">Table 9.93</a> control the progress of recovery.486 These functions may be executed only during recovery.487 </p><div class="table" id="FUNCTIONS-RECOVERY-CONTROL-TABLE"><p class="title"><strong>Table 9.93. Recovery Control Functions</strong></p><div class="table-contents"><table class="table" summary="Recovery Control Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">488 Function489 </p>490 <p>491 Description492 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">493 <a id="id-1.5.8.33.6.5.2.2.1.1.1.1" class="indexterm"></a>494 <code class="function">pg_is_wal_replay_paused</code> ()495 → <code class="returnvalue">boolean</code>496 </p>497 <p>498 Returns true if recovery pause is requested.499 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">500 <a id="id-1.5.8.33.6.5.2.2.2.1.1.1" class="indexterm"></a>501 <code class="function">pg_get_wal_replay_pause_state</code> ()502 → <code class="returnvalue">text</code>503 </p>504 <p>505 Returns recovery pause state. The return values are <code class="literal">506 not paused</code> if pause is not requested, <code class="literal">507 pause requested</code> if pause is requested but recovery is508 not yet paused, and <code class="literal">paused</code> if the recovery is509 actually paused.510 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">511 <a id="id-1.5.8.33.6.5.2.2.3.1.1.1" class="indexterm"></a>512 <code class="function">pg_promote</code> ( <em class="parameter"><code>wait</code></em> <code class="type">boolean</code> <code class="literal">DEFAULT</code> <code class="literal">true</code>, <em class="parameter"><code>wait_seconds</code></em> <code class="type">integer</code> <code class="literal">DEFAULT</code> <code class="literal">60</code> )513 → <code class="returnvalue">boolean</code>514 </p>515 <p>516 Promotes a standby server to primary status.517 With <em class="parameter"><code>wait</code></em> set to <code class="literal">true</code> (the518 default), the function waits until promotion is completed519 or <em class="parameter"><code>wait_seconds</code></em> seconds have passed, and520 returns <code class="literal">true</code> if promotion is successful521 and <code class="literal">false</code> otherwise.522 If <em class="parameter"><code>wait</code></em> is set to <code class="literal">false</code>, the523 function returns <code class="literal">true</code> immediately after sending a524 <code class="literal">SIGUSR1</code> signal to the postmaster to trigger525 promotion.526 </p>527 <p>528 This function is restricted to superusers by default, but other users529 can be granted EXECUTE to run the function.530 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">531 <a id="id-1.5.8.33.6.5.2.2.4.1.1.1" class="indexterm"></a>532 <code class="function">pg_wal_replay_pause</code> ()533 → <code class="returnvalue">void</code>534 </p>535 <p>536 Request to pause recovery. A request doesn't mean that recovery stops537 right away. If you want a guarantee that recovery is actually paused,538 you need to check for the recovery pause state returned by539 <code class="function">pg_get_wal_replay_pause_state()</code>. Note that540 <code class="function">pg_is_wal_replay_paused()</code> returns whether a request541 is made. While recovery is paused, no further database changes are applied.542 If hot standby is active, all new queries will see the same consistent543 snapshot of the database, and no further query conflicts will be generated544 until recovery is resumed.545 </p>546 <p>547 This function is restricted to superusers by default, but other users548 can be granted EXECUTE to run the function.549 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">550 <a id="id-1.5.8.33.6.5.2.2.5.1.1.1" class="indexterm"></a>551 <code class="function">pg_wal_replay_resume</code> ()552 → <code class="returnvalue">void</code>553 </p>554 <p>555 Restarts recovery if it was paused.556 </p>557 <p>558 This function is restricted to superusers by default, but other users559 can be granted EXECUTE to run the function.560 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>561 <code class="function">pg_wal_replay_pause</code> and562 <code class="function">pg_wal_replay_resume</code> cannot be executed while563 a promotion is ongoing. If a promotion is triggered while recovery564 is paused, the paused state ends and promotion continues.565 </p><p>566 If streaming replication is disabled, the paused state may continue567 indefinitely without a problem. If streaming replication is in568 progress then WAL records will continue to be received, which will569 eventually fill available disk space, depending upon the duration of570 the pause, the rate of WAL generation and available disk space.571 </p></div><div class="sect2" id="FUNCTIONS-SNAPSHOT-SYNCHRONIZATION"><div class="titlepage"><div><div><h3 class="title">9.27.5. Snapshot Synchronization Functions <a href="#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION" class="id_link">#</a></h3></div></div></div><p>572 <span class="productname">PostgreSQL</span> allows database sessions to synchronize their573 snapshots. A <em class="firstterm">snapshot</em> determines which data is visible to the574 transaction that is using the snapshot. Synchronized snapshots are575 necessary when two or more sessions need to see identical content in the576 database. If two sessions just start their transactions independently,577 there is always a possibility that some third transaction commits578 between the executions of the two <code class="command">START TRANSACTION</code> commands,579 so that one session sees the effects of that transaction and the other580 does not.581 </p><p>582 To solve this problem, <span class="productname">PostgreSQL</span> allows a transaction to583 <em class="firstterm">export</em> the snapshot it is using. As long as the exporting584 transaction remains open, other transactions can <em class="firstterm">import</em> its585 snapshot, and thereby be guaranteed that they see exactly the same view586 of the database that the first transaction sees. But note that any587 database changes made by any one of these transactions remain invisible588 to the other transactions, as is usual for changes made by uncommitted589 transactions. So the transactions are synchronized with respect to590 pre-existing data, but act normally for changes they make themselves.591 </p><p>592 Snapshots are exported with the <code class="function">pg_export_snapshot</code> function,593 shown in <a class="xref" href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION-TABLE" title="Table 9.94. Snapshot Synchronization Functions">Table 9.94</a>, and594 imported with the <a class="xref" href="sql-set-transaction.html" title="SET TRANSACTION"><span class="refentrytitle">SET TRANSACTION</span></a> command.595 </p><div class="table" id="FUNCTIONS-SNAPSHOT-SYNCHRONIZATION-TABLE"><p class="title"><strong>Table 9.94. Snapshot Synchronization Functions</strong></p><div class="table-contents"><table class="table" summary="Snapshot Synchronization Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">596 Function597 </p>598 <p>599 Description600 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">601 <a id="id-1.5.8.33.7.5.2.2.1.1.1.1" class="indexterm"></a>602 <code class="function">pg_export_snapshot</code> ()603 → <code class="returnvalue">text</code>604 </p>605 <p>606 Saves the transaction's current snapshot and returns607 a <code class="type">text</code> string identifying the snapshot. This string must608 be passed (outside the database) to clients that want to import the609 snapshot. The snapshot is available for import only until the end of610 the transaction that exported it.611 </p>612 <p>613 A transaction can export more than one snapshot, if needed. Note that614 doing so is only useful in <code class="literal">READ COMMITTED</code>615 transactions, since in <code class="literal">REPEATABLE READ</code> and higher616 isolation levels, transactions use the same snapshot throughout their617 lifetime. Once a transaction has exported any snapshots, it cannot be618 prepared with <a class="xref" href="sql-prepare-transaction.html" title="PREPARE TRANSACTION"><span class="refentrytitle">PREPARE TRANSACTION</span></a>.619 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">620 <a id="id-1.5.8.33.7.5.2.2.2.1.1.1" class="indexterm"></a>621 <code class="function">pg_log_standby_snapshot</code> ()622 → <code class="returnvalue">pg_lsn</code>623 </p>624 <p>625 Take a snapshot of running transactions and write it to WAL, without626 having to wait for bgwriter or checkpointer to log one. This is useful627 for logical decoding on standby, as logical slot creation has to wait628 until such a record is replayed on the standby.629 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-REPLICATION"><div class="titlepage"><div><div><h3 class="title">9.27.6. Replication Management Functions <a href="#FUNCTIONS-REPLICATION" class="id_link">#</a></h3></div></div></div><p>630 The functions shown631 in <a class="xref" href="functions-admin.html#FUNCTIONS-REPLICATION-TABLE" title="Table 9.95. Replication Management Functions">Table 9.95</a> are for632 controlling and interacting with replication features.633 See <a class="xref" href="warm-standby.html#STREAMING-REPLICATION" title="27.2.5. Streaming Replication">Section 27.2.5</a>,634 <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="27.2.6. Replication Slots">Section 27.2.6</a>, and635 <a class="xref" href="replication-origins.html" title="Chapter 50. Replication Progress Tracking">Chapter 50</a>636 for information about the underlying features.637 Use of functions for replication origin is only allowed to the638 superuser by default, but may be allowed to other users by using the639 <code class="literal">GRANT</code> command.640 Use of functions for replication slots is restricted to superusers641 and users having <code class="literal">REPLICATION</code> privilege.642 </p><p>643 Many of these functions have equivalent commands in the replication644 protocol; see <a class="xref" href="protocol-replication.html" title="55.4. Streaming Replication Protocol">Section 55.4</a>.645 </p><p>646 The functions described in647 <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-BACKUP" title="9.27.3. Backup Control Functions">Section 9.27.3</a>,648 <a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL" title="9.27.4. Recovery Control Functions">Section 9.27.4</a>, and649 <a class="xref" href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION" title="9.27.5. Snapshot Synchronization Functions">Section 9.27.5</a>650 are also relevant for replication.651 </p><div class="table" id="FUNCTIONS-REPLICATION-TABLE"><p class="title"><strong>Table 9.95. Replication Management Functions</strong></p><div class="table-contents"><table class="table" summary="Replication Management Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">652 Function653 </p>654 <p>655 Description656 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">657 <a id="id-1.5.8.33.8.5.2.2.1.1.1.1" class="indexterm"></a>658 <code class="function">pg_create_physical_replication_slot</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code> [<span class="optional">, <em class="parameter"><code>immediately_reserve</code></em> <code class="type">boolean</code>, <em class="parameter"><code>temporary</code></em> <code class="type">boolean</code> </span>] )659 → <code class="returnvalue">record</code>660 ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>,661 <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> )662 </p>663 <p>664 Creates a new physical replication slot named665 <em class="parameter"><code>slot_name</code></em>. The optional second parameter,666 when <code class="literal">true</code>, specifies that the <acronym class="acronym">LSN</acronym> for this667 replication slot be reserved immediately; otherwise668 the <acronym class="acronym">LSN</acronym> is reserved on first connection from a streaming669 replication client. Streaming changes from a physical slot is only670 possible with the streaming-replication protocol —671 see <a class="xref" href="protocol-replication.html" title="55.4. Streaming Replication Protocol">Section 55.4</a>. The optional third672 parameter, <em class="parameter"><code>temporary</code></em>, when set to true, specifies that673 the slot should not be permanently stored to disk and is only meant674 for use by the current session. Temporary slots are also675 released upon any error. This function corresponds676 to the replication protocol command <code class="literal">CREATE_REPLICATION_SLOT677 ... PHYSICAL</code>.678 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">679 <a id="id-1.5.8.33.8.5.2.2.2.1.1.1" class="indexterm"></a>680 <code class="function">pg_drop_replication_slot</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code> )681 → <code class="returnvalue">void</code>682 </p>683 <p>684 Drops the physical or logical replication slot685 named <em class="parameter"><code>slot_name</code></em>. Same as replication protocol686 command <code class="literal">DROP_REPLICATION_SLOT</code>. For logical slots, this must687 be called while connected to the same database the slot was created on.688 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">689 <a id="id-1.5.8.33.8.5.2.2.3.1.1.1" class="indexterm"></a>690 <code class="function">pg_create_logical_replication_slot</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>plugin</code></em> <code class="type">name</code> [<span class="optional">, <em class="parameter"><code>temporary</code></em> <code class="type">boolean</code>, <em class="parameter"><code>twophase</code></em> <code class="type">boolean</code> </span>] )691 → <code class="returnvalue">record</code>692 ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>,693 <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> )694 </p>695 <p>696 Creates a new logical (decoding) replication slot named697 <em class="parameter"><code>slot_name</code></em> using the output plugin698 <em class="parameter"><code>plugin</code></em>. The optional third699 parameter, <em class="parameter"><code>temporary</code></em>, when set to true, specifies that700 the slot should not be permanently stored to disk and is only meant701 for use by the current session. Temporary slots are also702 released upon any error. The optional fourth parameter,703 <em class="parameter"><code>twophase</code></em>, when set to true, specifies704 that the decoding of prepared transactions is enabled for this705 slot. A call to this function has the same effect as the replication706 protocol command <code class="literal">CREATE_REPLICATION_SLOT ... LOGICAL</code>.707 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">708 <a id="id-1.5.8.33.8.5.2.2.4.1.1.1" class="indexterm"></a>709 <code class="function">pg_copy_physical_replication_slot</code> ( <em class="parameter"><code>src_slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>dst_slot_name</code></em> <code class="type">name</code> [<span class="optional">, <em class="parameter"><code>temporary</code></em> <code class="type">boolean</code> </span>] )710 → <code class="returnvalue">record</code>711 ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>,712 <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> )713 </p>714 <p>715 Copies an existing physical replication slot named <em class="parameter"><code>src_slot_name</code></em>716 to a physical replication slot named <em class="parameter"><code>dst_slot_name</code></em>.717 The copied physical slot starts to reserve WAL from the same <acronym class="acronym">LSN</acronym> as the718 source slot.719 <em class="parameter"><code>temporary</code></em> is optional. If <em class="parameter"><code>temporary</code></em>720 is omitted, the same value as the source slot is used.721 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">722 <a id="id-1.5.8.33.8.5.2.2.5.1.1.1" class="indexterm"></a>723 <code class="function">pg_copy_logical_replication_slot</code> ( <em class="parameter"><code>src_slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>dst_slot_name</code></em> <code class="type">name</code> [<span class="optional">, <em class="parameter"><code>temporary</code></em> <code class="type">boolean</code> [<span class="optional">, <em class="parameter"><code>plugin</code></em> <code class="type">name</code> </span>]</span>] )724 → <code class="returnvalue">record</code>725 ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>,726 <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> )727 </p>728 <p>729 Copies an existing logical replication slot730 named <em class="parameter"><code>src_slot_name</code></em> to a logical replication731 slot named <em class="parameter"><code>dst_slot_name</code></em>, optionally changing732 the output plugin and persistence. The copied logical slot starts733 from the same <acronym class="acronym">LSN</acronym> as the source logical slot. Both734 <em class="parameter"><code>temporary</code></em> and <em class="parameter"><code>plugin</code></em> are735 optional; if they are omitted, the values of the source slot are used.736 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">737 <a id="id-1.5.8.33.8.5.2.2.6.1.1.1" class="indexterm"></a>738 <code class="function">pg_logical_slot_get_changes</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>upto_nchanges</code></em> <code class="type">integer</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>options</code></em> <code class="type">text[]</code> )739 → <code class="returnvalue">setof record</code>740 ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>,741 <em class="parameter"><code>xid</code></em> <code class="type">xid</code>,742 <em class="parameter"><code>data</code></em> <code class="type">text</code> )743 </p>744 <p>745 Returns changes in the slot <em class="parameter"><code>slot_name</code></em>, starting746 from the point from which changes have been consumed last. If747 <em class="parameter"><code>upto_lsn</code></em>748 and <em class="parameter"><code>upto_nchanges</code></em> are NULL,749 logical decoding will continue until end of WAL. If750 <em class="parameter"><code>upto_lsn</code></em> is non-NULL, decoding will include only751 those transactions which commit prior to the specified LSN. If752 <em class="parameter"><code>upto_nchanges</code></em> is non-NULL, decoding will753 stop when the number of rows produced by decoding exceeds754 the specified value. Note, however, that the actual number of755 rows returned may be larger, since this limit is only checked after756 adding the rows produced when decoding each new transaction commit.757 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">758 <a id="id-1.5.8.33.8.5.2.2.7.1.1.1" class="indexterm"></a>759 <code class="function">pg_logical_slot_peek_changes</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>upto_nchanges</code></em> <code class="type">integer</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>options</code></em> <code class="type">text[]</code> )760 → <code class="returnvalue">setof record</code>761 ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>,762 <em class="parameter"><code>xid</code></em> <code class="type">xid</code>,763 <em class="parameter"><code>data</code></em> <code class="type">text</code> )764 </p>765 <p>766 Behaves just like767 the <code class="function">pg_logical_slot_get_changes()</code> function,768 except that changes are not consumed; that is, they will be returned769 again on future calls.770 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">771 <a id="id-1.5.8.33.8.5.2.2.8.1.1.1" class="indexterm"></a>772 <code class="function">pg_logical_slot_get_binary_changes</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>upto_nchanges</code></em> <code class="type">integer</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>options</code></em> <code class="type">text[]</code> )773 → <code class="returnvalue">setof record</code>774 ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>,775 <em class="parameter"><code>xid</code></em> <code class="type">xid</code>,776 <em class="parameter"><code>data</code></em> <code class="type">bytea</code> )777 </p>778 <p>779 Behaves just like780 the <code class="function">pg_logical_slot_get_changes()</code> function,781 except that changes are returned as <code class="type">bytea</code>.782 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">783 <a id="id-1.5.8.33.8.5.2.2.9.1.1.1" class="indexterm"></a>784 <code class="function">pg_logical_slot_peek_binary_changes</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>upto_nchanges</code></em> <code class="type">integer</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>options</code></em> <code class="type">text[]</code> )785 → <code class="returnvalue">setof record</code>786 ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>,787 <em class="parameter"><code>xid</code></em> <code class="type">xid</code>,788 <em class="parameter"><code>data</code></em> <code class="type">bytea</code> )789 </p>790 <p>791 Behaves just like792 the <code class="function">pg_logical_slot_peek_changes()</code> function,793 except that changes are returned as <code class="type">bytea</code>.794 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">795 <a id="id-1.5.8.33.8.5.2.2.10.1.1.1" class="indexterm"></a>796 <code class="function">pg_replication_slot_advance</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code> )797 → <code class="returnvalue">record</code>798 ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>,799 <em class="parameter"><code>end_lsn</code></em> <code class="type">pg_lsn</code> )800 </p>801 <p>802 Advances the current confirmed position of a replication slot named803 <em class="parameter"><code>slot_name</code></em>. The slot will not be moved backwards,804 and it will not be moved beyond the current insert location. Returns805 the name of the slot and the actual position that it was advanced to.806 The updated slot position information is written out at the next807 checkpoint if any advancing is done. So in the event of a crash, the808 slot may return to an earlier position.809 </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-CREATE" class="func_table_entry"><p class="func_signature">810 <a id="id-1.5.8.33.8.5.2.2.11.1.1.1" class="indexterm"></a>811 <code class="function">pg_replication_origin_create</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code> )812 → <code class="returnvalue">oid</code>813 </p>814 <p>815 Creates a replication origin with the given external816 name, and returns the internal ID assigned to it.817 </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-DROP" class="func_table_entry"><p class="func_signature">818 <a id="id-1.5.8.33.8.5.2.2.12.1.1.1" class="indexterm"></a>819 <code class="function">pg_replication_origin_drop</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code> )820 → <code class="returnvalue">void</code>821 </p>822 <p>823 Deletes a previously-created replication origin, including any824 associated replay progress.825 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">826 <a id="id-1.5.8.33.8.5.2.2.13.1.1.1" class="indexterm"></a>827 <code class="function">pg_replication_origin_oid</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code> )828 → <code class="returnvalue">oid</code>829 </p>830 <p>831 Looks up a replication origin by name and returns the internal ID. If832 no such replication origin is found, <code class="literal">NULL</code> is833 returned.834 </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-SESSION-SETUP" class="func_table_entry"><p class="func_signature">835 <a id="id-1.5.8.33.8.5.2.2.14.1.1.1" class="indexterm"></a>836 <code class="function">pg_replication_origin_session_setup</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code> )837 → <code class="returnvalue">void</code>838 </p>839 <p>840 Marks the current session as replaying from the given841 origin, allowing replay progress to be tracked.842 Can only be used if no origin is currently selected.843 Use <code class="function">pg_replication_origin_session_reset</code> to undo.844 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">845 <a id="id-1.5.8.33.8.5.2.2.15.1.1.1" class="indexterm"></a>846 <code class="function">pg_replication_origin_session_reset</code> ()847 → <code class="returnvalue">void</code>848 </p>849 <p>850 Cancels the effects851 of <code class="function">pg_replication_origin_session_setup()</code>.852 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">853 <a id="id-1.5.8.33.8.5.2.2.16.1.1.1" class="indexterm"></a>854 <code class="function">pg_replication_origin_session_is_setup</code> ()855 → <code class="returnvalue">boolean</code>856 </p>857 <p>858 Returns true if a replication origin has been selected in the859 current session.860 </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-SESSION-PROGRESS" class="func_table_entry"><p class="func_signature">861 <a id="id-1.5.8.33.8.5.2.2.17.1.1.1" class="indexterm"></a>862 <code class="function">pg_replication_origin_session_progress</code> ( <em class="parameter"><code>flush</code></em> <code class="type">boolean</code> )863 → <code class="returnvalue">pg_lsn</code>864 </p>865 <p>866 Returns the replay location for the replication origin selected in867 the current session. The parameter <em class="parameter"><code>flush</code></em>868 determines whether the corresponding local transaction will be869 guaranteed to have been flushed to disk or not.870 </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-XACT-SETUP" class="func_table_entry"><p class="func_signature">871 <a id="id-1.5.8.33.8.5.2.2.18.1.1.1" class="indexterm"></a>872 <code class="function">pg_replication_origin_xact_setup</code> ( <em class="parameter"><code>origin_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>origin_timestamp</code></em> <code class="type">timestamp with time zone</code> )873 → <code class="returnvalue">void</code>874 </p>875 <p>876 Marks the current transaction as replaying a transaction that has877 committed at the given <acronym class="acronym">LSN</acronym> and timestamp. Can878 only be called when a replication origin has been selected879 using <code class="function">pg_replication_origin_session_setup</code>.880 </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-XACT-RESET" class="func_table_entry"><p class="func_signature">881 <a id="id-1.5.8.33.8.5.2.2.19.1.1.1" class="indexterm"></a>882 <code class="function">pg_replication_origin_xact_reset</code> ()883 → <code class="returnvalue">void</code>884 </p>885 <p>886 Cancels the effects of887 <code class="function">pg_replication_origin_xact_setup()</code>.888 </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-ADVANCE" class="func_table_entry"><p class="func_signature">889 <a id="id-1.5.8.33.8.5.2.2.20.1.1.1" class="indexterm"></a>890 <code class="function">pg_replication_origin_advance</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code>, <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> )891 → <code class="returnvalue">void</code>892 </p>893 <p>894 Sets replication progress for the given node to the given895 location. This is primarily useful for setting up the initial896 location, or setting a new location after configuration changes and897 similar. Be aware that careless use of this function can lead to898 inconsistently replicated data.899 </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-PROGRESS" class="func_table_entry"><p class="func_signature">900 <a id="id-1.5.8.33.8.5.2.2.21.1.1.1" class="indexterm"></a>901 <code class="function">pg_replication_origin_progress</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code>, <em class="parameter"><code>flush</code></em> <code class="type">boolean</code> )902 → <code class="returnvalue">pg_lsn</code>903 </p>904 <p>905 Returns the replay location for the given replication origin. The906 parameter <em class="parameter"><code>flush</code></em> determines whether the907 corresponding local transaction will be guaranteed to have been908 flushed to disk or not.909 </p></td></tr><tr><td id="PG-LOGICAL-EMIT-MESSAGE" class="func_table_entry"><p class="func_signature">910 <a id="id-1.5.8.33.8.5.2.2.22.1.1.1" class="indexterm"></a>911 <code class="function">pg_logical_emit_message</code> ( <em class="parameter"><code>transactional</code></em> <code class="type">boolean</code>, <em class="parameter"><code>prefix</code></em> <code class="type">text</code>, <em class="parameter"><code>content</code></em> <code class="type">text</code> )912 → <code class="returnvalue">pg_lsn</code>913 </p>914 <p class="func_signature">915 <code class="function">pg_logical_emit_message</code> ( <em class="parameter"><code>transactional</code></em> <code class="type">boolean</code>, <em class="parameter"><code>prefix</code></em> <code class="type">text</code>, <em class="parameter"><code>content</code></em> <code class="type">bytea</code> )916 → <code class="returnvalue">pg_lsn</code>917 </p>918 <p>919 Emits a logical decoding message. This can be used to pass generic920 messages to logical decoding plugins through921 WAL. The <em class="parameter"><code>transactional</code></em> parameter specifies if922 the message should be part of the current transaction, or if it should923 be written immediately and decoded as soon as the logical decoder924 reads the record. The <em class="parameter"><code>prefix</code></em> parameter is a925 textual prefix that can be used by logical decoding plugins to easily926 recognize messages that are interesting for them.927 The <em class="parameter"><code>content</code></em> parameter is the content of the928 message, given either in text or binary form.929 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-ADMIN-DBOBJECT"><div class="titlepage"><div><div><h3 class="title">9.27.7. Database Object Management Functions <a href="#FUNCTIONS-ADMIN-DBOBJECT" class="id_link">#</a></h3></div></div></div><p>930 The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-DBSIZE" title="Table 9.96. Database Object Size Functions">Table 9.96</a> calculate931 the disk space usage of database objects, or assist in presentation932 or understanding of usage results. <code class="literal">bigint</code> results933 are measured in bytes. If an OID that does934 not represent an existing object is passed to one of these935 functions, <code class="literal">NULL</code> is returned.936 </p><div class="table" id="FUNCTIONS-ADMIN-DBSIZE"><p class="title"><strong>Table 9.96. Database Object Size Functions</strong></p><div class="table-contents"><table class="table" summary="Database Object Size Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">937 Function938 </p>939 <p>940 Description941 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">942 <a id="id-1.5.8.33.9.3.2.2.1.1.1.1" class="indexterm"></a>943 <code class="function">pg_column_size</code> ( <code class="type">"any"</code> )944 → <code class="returnvalue">integer</code>945 </p>946 <p>947 Shows the number of bytes used to store any individual data value. If948 applied directly to a table column value, this reflects any949 compression that was done.950 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">951 <a id="id-1.5.8.33.9.3.2.2.2.1.1.1" class="indexterm"></a>952 <code class="function">pg_column_compression</code> ( <code class="type">"any"</code> )953 → <code class="returnvalue">text</code>954 </p>955 <p>956 Shows the compression algorithm that was used to compress957 an individual variable-length value. Returns <code class="literal">NULL</code>958 if the value is not compressed.959 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">960 <a id="id-1.5.8.33.9.3.2.2.3.1.1.1" class="indexterm"></a>961 <code class="function">pg_database_size</code> ( <code class="type">name</code> )962 → <code class="returnvalue">bigint</code>963 </p>964 <p class="func_signature">965 <code class="function">pg_database_size</code> ( <code class="type">oid</code> )966 → <code class="returnvalue">bigint</code>967 </p>968 <p>969 Computes the total disk space used by the database with the specified970 name or OID. To use this function, you must971 have <code class="literal">CONNECT</code> privilege on the specified database972 (which is granted by default) or have privileges of973 the <code class="literal">pg_read_all_stats</code> role.974 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">975 <a id="id-1.5.8.33.9.3.2.2.4.1.1.1" class="indexterm"></a>976 <code class="function">pg_indexes_size</code> ( <code class="type">regclass</code> )977 → <code class="returnvalue">bigint</code>978 </p>979 <p>980 Computes the total disk space used by indexes attached to the981 specified table.982 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">983 <a id="id-1.5.8.33.9.3.2.2.5.1.1.1" class="indexterm"></a>984 <code class="function">pg_relation_size</code> ( <em class="parameter"><code>relation</code></em> <code class="type">regclass</code> [<span class="optional">, <em class="parameter"><code>fork</code></em> <code class="type">text</code> </span>] )985 → <code class="returnvalue">bigint</code>986 </p>987 <p>988 Computes the disk space used by one <span class="quote">“<span class="quote">fork</span>”</span> of the989 specified relation. (Note that for most purposes it is more990 convenient to use the higher-level991 functions <code class="function">pg_total_relation_size</code>992 or <code class="function">pg_table_size</code>, which sum the sizes of all993 forks.) With one argument, this returns the size of the main data994 fork of the relation. The second argument can be provided to specify995 which fork to examine:996 </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>997 <code class="literal">main</code> returns the size of the main998 data fork of the relation.999 </p></li><li class="listitem"><p>1000 <code class="literal">fsm</code> returns the size of the Free Space Map1001 (see <a class="xref" href="storage-fsm.html" title="73.3. Free Space Map">Section 73.3</a>) associated with the relation.1002 </p></li><li class="listitem"><p>1003 <code class="literal">vm</code> returns the size of the Visibility Map1004 (see <a class="xref" href="storage-vm.html" title="73.4. Visibility Map">Section 73.4</a>) associated with the relation.1005 </p></li><li class="listitem"><p>1006 <code class="literal">init</code> returns the size of the initialization1007 fork, if any, associated with the relation.1008 </p></li></ul></div><p>1009 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1010 <a id="id-1.5.8.33.9.3.2.2.6.1.1.1" class="indexterm"></a>1011 <code class="function">pg_size_bytes</code> ( <code class="type">text</code> )1012 → <code class="returnvalue">bigint</code>1013 </p>1014 <p>1015 Converts a size in human-readable format (as returned1016 by <code class="function">pg_size_pretty</code>) into bytes. Valid units are1017 <code class="literal">bytes</code>, <code class="literal">B</code>, <code class="literal">kB</code>,1018 <code class="literal">MB</code>, <code class="literal">GB</code>, <code class="literal">TB</code>,1019 and <code class="literal">PB</code>.1020 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1021 <a id="id-1.5.8.33.9.3.2.2.7.1.1.1" class="indexterm"></a>1022 <code class="function">pg_size_pretty</code> ( <code class="type">bigint</code> )1023 → <code class="returnvalue">text</code>1024 </p>1025 <p class="func_signature">1026 <code class="function">pg_size_pretty</code> ( <code class="type">numeric</code> )1027 → <code class="returnvalue">text</code>1028 </p>1029 <p>1030 Converts a size in bytes into a more easily human-readable format with1031 size units (bytes, kB, MB, GB, TB, or PB as appropriate). Note that the1032 units are powers of 2 rather than powers of 10, so 1kB is 1024 bytes,1033 1MB is 1024<sup>2</sup> = 1048576 bytes, and so on.1034 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1035 <a id="id-1.5.8.33.9.3.2.2.8.1.1.1" class="indexterm"></a>1036 <code class="function">pg_table_size</code> ( <code class="type">regclass</code> )1037 → <code class="returnvalue">bigint</code>1038 </p>1039 <p>1040 Computes the disk space used by the specified table, excluding indexes1041 (but including its TOAST table if any, free space map, and visibility1042 map).1043 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1044 <a id="id-1.5.8.33.9.3.2.2.9.1.1.1" class="indexterm"></a>1045 <code class="function">pg_tablespace_size</code> ( <code class="type">name</code> )1046 → <code class="returnvalue">bigint</code>1047 </p>1048 <p class="func_signature">1049 <code class="function">pg_tablespace_size</code> ( <code class="type">oid</code> )1050 → <code class="returnvalue">bigint</code>1051 </p>1052 <p>1053 Computes the total disk space used in the tablespace with the1054 specified name or OID. To use this function, you must1055 have <code class="literal">CREATE</code> privilege on the specified tablespace1056 or have privileges of the <code class="literal">pg_read_all_stats</code> role,1057 unless it is the default tablespace for the current database.1058 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1059 <a id="id-1.5.8.33.9.3.2.2.10.1.1.1" class="indexterm"></a>1060 <code class="function">pg_total_relation_size</code> ( <code class="type">regclass</code> )1061 → <code class="returnvalue">bigint</code>1062 </p>1063 <p>1064 Computes the total disk space used by the specified table, including1065 all indexes and <acronym class="acronym">TOAST</acronym> data. The result is1066 equivalent to <code class="function">pg_table_size</code>1067 <code class="literal">+</code> <code class="function">pg_indexes_size</code>.1068 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>1069 The functions above that operate on tables or indexes accept a1070 <code class="type">regclass</code> argument, which is simply the OID of the table or index1071 in the <code class="structname">pg_class</code> system catalog. You do not have to look up1072 the OID by hand, however, since the <code class="type">regclass</code> data type's input1073 converter will do the work for you. See <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>1074 for details.1075 </p><p>1076 The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-DBLOCATION" title="Table 9.97. Database Object Location Functions">Table 9.97</a> assist1077 in identifying the specific disk files associated with database objects.1078 </p><div class="table" id="FUNCTIONS-ADMIN-DBLOCATION"><p class="title"><strong>Table 9.97. Database Object Location Functions</strong></p><div class="table-contents"><table class="table" summary="Database Object Location Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">1079 Function1080 </p>1081 <p>1082 Description1083 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">1084 <a id="id-1.5.8.33.9.6.2.2.1.1.1.1" class="indexterm"></a>1085 <code class="function">pg_relation_filenode</code> ( <em class="parameter"><code>relation</code></em> <code class="type">regclass</code> )1086 → <code class="returnvalue">oid</code>1087 </p>1088 <p>1089 Returns the <span class="quote">“<span class="quote">filenode</span>”</span> number currently assigned to the1090 specified relation. The filenode is the base component of the file1091 name(s) used for the relation (see1092 <a class="xref" href="storage-file-layout.html" title="73.1. Database File Layout">Section 73.1</a> for more information).1093 For most relations the result is the same as1094 <code class="structname">pg_class</code>.<code class="structfield">relfilenode</code>,1095 but for certain system catalogs <code class="structfield">relfilenode</code>1096 is zero and this function must be used to get the correct value. The1097 function returns NULL if passed a relation that does not have storage,1098 such as a view.1099 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1100 <a id="id-1.5.8.33.9.6.2.2.2.1.1.1" class="indexterm"></a>1101 <code class="function">pg_relation_filepath</code> ( <em class="parameter"><code>relation</code></em> <code class="type">regclass</code> )1102 → <code class="returnvalue">text</code>1103 </p>1104 <p>1105 Returns the entire file path name (relative to the database cluster's1106 data directory, <code class="varname">PGDATA</code>) of the relation.1107 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1108 <a id="id-1.5.8.33.9.6.2.2.3.1.1.1" class="indexterm"></a>1109 <code class="function">pg_filenode_relation</code> ( <em class="parameter"><code>tablespace</code></em> <code class="type">oid</code>, <em class="parameter"><code>filenode</code></em> <code class="type">oid</code> )1110 → <code class="returnvalue">regclass</code>1111 </p>1112 <p>1113 Returns a relation's OID given the tablespace OID and filenode it is1114 stored under. This is essentially the inverse mapping of1115 <code class="function">pg_relation_filepath</code>. For a relation in the1116 database's default tablespace, the tablespace can be specified as zero.1117 Returns <code class="literal">NULL</code> if no relation in the current database1118 is associated with the given values.1119 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>1120 <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-COLLATION" title="Table 9.98. Collation Management Functions">Table 9.98</a> lists functions used to manage1121 collations.1122 </p><div class="table" id="FUNCTIONS-ADMIN-COLLATION"><p class="title"><strong>Table 9.98. Collation Management Functions</strong></p><div class="table-contents"><table class="table" summary="Collation Management Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">1123 Function1124 </p>1125 <p>1126 Description1127 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">1128 <a id="id-1.5.8.33.9.8.2.2.1.1.1.1" class="indexterm"></a>1129 <code class="function">pg_collation_actual_version</code> ( <code class="type">oid</code> )1130 → <code class="returnvalue">text</code>1131 </p>1132 <p>1133 Returns the actual version of the collation object as it is currently1134 installed in the operating system. If this is different from the1135 value in1136 <code class="structname">pg_collation</code>.<code class="structfield">collversion</code>,1137 then objects depending on the collation might need to be rebuilt. See1138 also <a class="xref" href="sql-altercollation.html" title="ALTER COLLATION"><span class="refentrytitle">ALTER COLLATION</span></a>.1139 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1140 <a id="id-1.5.8.33.9.8.2.2.2.1.1.1" class="indexterm"></a>1141 <code class="function">pg_database_collation_actual_version</code> ( <code class="type">oid</code> )1142 → <code class="returnvalue">text</code>1143 </p>1144 <p>1145 Returns the actual version of the database's collation as it is currently1146 installed in the operating system. If this is different from the1147 value in1148 <code class="structname">pg_database</code>.<code class="structfield">datcollversion</code>,1149 then objects depending on the collation might need to be rebuilt. See1150 also <a class="xref" href="sql-alterdatabase.html" title="ALTER DATABASE"><span class="refentrytitle">ALTER DATABASE</span></a>.1151 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1152 <a id="id-1.5.8.33.9.8.2.2.3.1.1.1" class="indexterm"></a>1153 <code class="function">pg_import_system_collations</code> ( <em class="parameter"><code>schema</code></em> <code class="type">regnamespace</code> )1154 → <code class="returnvalue">integer</code>1155 </p>1156 <p>1157 Adds collations to the system1158 catalog <code class="structname">pg_collation</code> based on all the locales1159 it finds in the operating system. This is1160 what <code class="command">initdb</code> uses; see1161 <a class="xref" href="collation.html#COLLATION-MANAGING" title="24.2.2. Managing Collations">Section 24.2.2</a> for more details. If additional1162 locales are installed into the operating system later on, this1163 function can be run again to add collations for the new locales.1164 Locales that match existing entries1165 in <code class="structname">pg_collation</code> will be skipped. (But1166 collation objects based on locales that are no longer present in the1167 operating system are not removed by this function.)1168 The <em class="parameter"><code>schema</code></em> parameter would typically1169 be <code class="literal">pg_catalog</code>, but that is not a requirement; the1170 collations could be installed into some other schema as well. The1171 function returns the number of new collation objects it created.1172 Use of this function is restricted to superusers.1173 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>1174 <a class="xref" href="functions-admin.html#FUNCTIONS-INFO-PARTITION" title="Table 9.99. Partitioning Information Functions">Table 9.99</a> lists functions that provide1175 information about the structure of partitioned tables.1176 </p><div class="table" id="FUNCTIONS-INFO-PARTITION"><p class="title"><strong>Table 9.99. Partitioning Information Functions</strong></p><div class="table-contents"><table class="table" summary="Partitioning Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">1177 Function1178 </p>1179 <p>1180 Description1181 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">1182 <a id="id-1.5.8.33.9.10.2.2.1.1.1.1" class="indexterm"></a>1183 <code class="function">pg_partition_tree</code> ( <code class="type">regclass</code> )1184 → <code class="returnvalue">setof record</code>1185 ( <em class="parameter"><code>relid</code></em> <code class="type">regclass</code>,1186 <em class="parameter"><code>parentrelid</code></em> <code class="type">regclass</code>,1187 <em class="parameter"><code>isleaf</code></em> <code class="type">boolean</code>,1188 <em class="parameter"><code>level</code></em> <code class="type">integer</code> )1189 </p>1190 <p>1191 Lists the tables or indexes in the partition tree of the1192 given partitioned table or partitioned index, with one row for each1193 partition. Information provided includes the OID of the partition,1194 the OID of its immediate parent, a boolean value telling if the1195 partition is a leaf, and an integer telling its level in the hierarchy.1196 The level value is 0 for the input table or index, 1 for its1197 immediate child partitions, 2 for their partitions, and so on.1198 Returns no rows if the relation does not exist or is not a partition1199 or partitioned table.1200 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">