9.26.1. Configuration SettingsFunctions

Table9-59 shows the functions available to query and alter run-timeconfiguration parameters.

The functioncurrent_settingyields the current value of the settingsetting_name. It corresponds to theSQL commandSHOW. An example:

SELECT current_setting('datestyle'); current_setting----------------- ISO, MDY(1 row)

set_config sets the parametersetting_name tonew_value. Ifis_localistrue, the new value will only apply tothe current transaction. If you want the new value to apply for thecurrent session, usefalse instead. Thefunction corresponds to the SQL commandSET. An example:

SELECT set_config('log_statement_stats', 'off', false); set_config------------ off(1 row)

9.26.2. Server SignalingFunctions

The functions shown inTable 9-60send control signals to other server processes. Use of thesefunctions is usually restricted to superusers, with notedexceptions.

Each of these functions returnstrue ifsuccessful andfalse otherwise.

pg_cancel_backend andpg_terminate_backend send signals (SIGINT orSIGTERM respectively) to backend processesidentified by process ID. The process ID of an active backend canbe found from thepid column of thepg_stat_activity view, or by listingthepostgres processes on the server(usingps on Unix or theTask Manager onWindows). The role of an active backend can befound from theusename column of thepg_stat_activity view.

pg_reload_conf sends aSIGHUP signal to the server,causing configuration files to be reloaded by all serverprocesses.

pg_rotate_logfile signals thelog-file manager to switch to a new output file immediately. Thisworks only when the built-in log collector is running, sinceotherwise there is no log-file manager subprocess.

9.26.3. Backup Control Functions

The functions shown inTable 9-61assist in making on-line backups. These functions cannot beexecuted during recovery (exceptpg_is_in_backup,pg_backup_start_time andpg_xlog_location_diff).

pg_start_backup accepts anarbitrary user-defined label for the backup. (Typically this wouldbe the name under which the backup dump file will be stored.) Thefunction writes a backup label file (backup_label) into the database cluster's datadirectory, performs a checkpoint, and then returns the backup'sstarting transaction log location as text. The user can ignore thisresult value, but it is provided in case it is useful.

postgres=# select pg_start_backup('label_goes_here'); pg_start_backup----------------- 0/D4445B8(1 row)

There is an optional second parameter of typeboolean. Iftrue, it specifiesexecutingpg_start_backup as quicklyas possible. This forces an immediate checkpoint which will cause aspike in I/O operations, slowing any concurrently executingqueries.

pg_stop_backup removes the labelfile created bypg_start_backup, andcreates a backup history file in the transaction log archive area.The history file includes the label given topg_start_backup, the starting and endingtransaction log locations for the backup, and the starting andending times of the backup. The return value is the backup's endingtransaction log location (which again can be ignored). Afterrecording the ending location, the current transaction loginsertion point is automatically advanced to the next transactionlog file, so that the ending transaction log file can be archivedimmediately to complete the backup.

pg_switch_xlog moves to the nexttransaction log file, allowing the current file to be archived(assuming you are using continuous archiving). The return value isthe ending transaction log location + 1 within the just-completedtransaction log file. If there has been no transaction log activitysince the last transaction log switch,pg_switch_xlog does nothing and returns the startlocation of the transaction log file currently in use.

pg_create_restore_point creates anamed transaction log record that can be used as recovery target,and returns the corresponding transaction log location. The givenname can then be used withrecovery_target_nameto specify the point up to which recovery will proceed. Avoidcreating multiple restore points with the same name, since recoverywill stop at the first one whose name matches the recoverytarget.

pg_current_xlog_location displaysthe current transaction log write location in the same format usedby the above functions. Similarly,pg_current_xlog_insert_location displays thecurrent transaction log insertion point. The insertion point is the"logical" end of the transaction log atany instant, while the write location is the end of what hasactually been written out from the server's internal buffers. Thewrite location is the end of what can be examined from outside theserver, and is usually what you want if you are interested inarchiving partially-complete transaction log files. The insertionpoint is made available primarily for server debugging purposes.These are both read-only operations and do not require superuserpermissions.

You can usepg_xlogfile_name_offset to extract thecorresponding transaction log file name and byte offset from theresults of any of the above functions. For example:

postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());        file_name         | file_offset --------------------------+------------- 00000001000000000000000D |     4039624(1 row)

Similarly,pg_xlogfile_nameextracts just the transaction log file name. When the giventransaction log location is exactly at a transaction log fileboundary, both these functions return the name of the precedingtransaction log file. This is usually the desired behavior formanaging transaction log archiving behavior, since the precedingfile is the last one that currently needs to be archived.

pg_xlog_location_diff calculatesthe difference in bytes between two transaction log locations. Itcan be used withpg_stat_replication orsome functions shown inTable 9-61to get the replication lag.

For details about proper usage of these functions, seeSection 24.3.

9.26.4. Recovery ControlFunctions

The functions shown inTable 9-62provide information about the current status of the standby. Thesefunctions may be executed both during recovery and in normalrunning.

The functions shown inTable9-63 control the progress of recovery. These functions may beexecuted only during recovery.

While recovery is paused no further database changes areapplied. If in hot standby, all new queries will see the sameconsistent snapshot of the database, and no further query conflictswill be generated until recovery is resumed.

If streaming replication is disabled, the paused state maycontinue indefinitely without problem. While streaming replicationis in progress WAL records will continue to be received, which willeventually fill available disk space, depending upon the durationof the pause, the rate of WAL generation and available diskspace.

9.26.5. SnapshotSynchronization Functions

PostgreSQL allows databasesessions to synchronize their snapshots. Asnapshot determines which data is visible to thetransaction that is using the snapshot. Synchronized snapshots arenecessary when two or more sessions need to see identical contentin the database. If two sessions just start their transactionsindependently, there is always a possibility that some thirdtransaction commits between the executions of the twoSTART TRANSACTION commands, so that one session seesthe effects of that transaction and the other does not.

To solve this problem,PostgreSQL allows a transaction toexport the snapshot it is using. As long as theexporting transaction remains open, other transactions canimport its snapshot, and thereby beguaranteed that they see exactly the same view of the database thatthe first transaction sees. But note that any database changes madeby any one of these transactions remain invisible to the othertransactions, as is usual for changes made by uncommittedtransactions. So the transactions are synchronized with respect topre-existing data, but act normally for changes they makethemselves.

Snapshots are exported with thepg_export_snapshot function, shown inTable9-64, and imported with theSET TRANSACTION command.

The functionpg_export_snapshotsaves the current snapshot and returns atextstring identifying the snapshot. This string must be passed(outside the database) to clients that want to import the snapshot.The snapshot is available for import only until the end of thetransaction that exported it. A transaction can export more thanone snapshot, if needed. Note that doing so is only useful inREAD COMMITTED transactions, since inREPEATABLE READ and higher isolationlevels, transactions use the same snapshot throughout theirlifetime. Once a transaction has exported any snapshots, it cannotbe prepared withPREPARETRANSACTION.

SeeSET TRANSACTION fordetails of how to use an exported snapshot.

9.26.6. Database Object ManagementFunctions

The functions shown inTable 9-65calculate the disk space usage of database objects.

pg_column_size shows the spaceused to store any individual data value.

pg_total_relation_size accepts theOID or name of a table or toast table, and returns the totalon-disk space used for that table, including all associatedindexes. This function is equivalent topg_table_size+pg_indexes_size.

pg_table_size accepts the OID orname of a table and returns the disk space needed for that table,exclusive of indexes. (TOAST space, free space map, and visibilitymap are included.)

pg_indexes_size accepts the OID orname of a table and returns the total disk space used by all theindexes attached to that table.

pg_database_size andpg_tablespace_size accept the OID or name of adatabase or tablespace, and return the total disk space usedtherein. To usepg_database_size, youmust haveCONNECT permission on thespecified database (which is granted by default). To usepg_tablespace_size, you must haveCREATE permission on the specifiedtablespace, unless it is the default tablespace for the currentdatabase.

pg_relation_size accepts the OIDor name of a table, index or toast table, and returns the on-disksize in bytes of one fork of that relation. (Note that for mostpurposes it is more convenient to use the higher-level functionspg_total_relation_size orpg_table_size, which sum the sizes ofall forks.) With one argument, it returns the size of the main datafork of the relation. The second argument can be provided tospecify which fork to examine:

• 'main' returns the size of the maindata fork of the relation.

• 'fsm' returns the size of the FreeSpace Map (seeSection 58.3)associated with the relation.

• 'vm' returns the size of the VisibilityMap (seeSection 58.4) associatedwith the relation.

• 'init' returns the size of theinitialization fork, if any, (seeSection 58.5) associated with therelation.

pg_size_pretty can be used toformat the result of one of the other functions in a human-readableway, using kB, MB, GB or TB as appropriate.

The functions above that operate on tables or indexes accept aregclass argument, which is simply the OID ofthe table or index in thepg_classsystem catalog. You do not have to look up the OID by hand,however, since theregclass data type's inputconverter will do the work for you. Just write the table nameenclosed in single quotes so that it looks like a literal constant.For compatibility with the handling of ordinarySQL names, the string will be converted tolower case unless it contains double quotes around the tablename.

If an OID that does not represent an existing object is passedas argument to one of the above functions, NULL is returned.

The functions shown inTable 9-66assist in identifying the specific disk files associated withdatabase objects.

pg_relation_filenode accepts theOID or name of a table, index, sequence, or toast table, andreturns the"filenode" number currentlyassigned to it. The filenode is the base component of the filename(s) used for the relation (seeSection 58.1 for more information).For most tables the result is the same aspg_class.relfilenode, but for certain system catalogsrelfilenode is zero and this functionmust be used to get the correct value. The function returns NULL ifpassed a relation that does not have storage, such as a view.

pg_relation_filepath is similar topg_relation_filenode, but it returnsthe entire file path name (relative to the database cluster's datadirectoryPGDATA) of the relation.

9.26.7. Generic File AccessFunctions

The functions shown inTable 9-67provide native access to files on the machine hosting the server.Only files within the database cluster directory and thelog_directory can be accessed. Use a relative pathfor files in the cluster directory, and a path matching thelog_directory configuration setting forlog files. Use of these functions is restricted to superusers.

pg_ls_dir returns all the names inthe specified directory, except the special entries"." and"..".

pg_read_file returns part of atext file, starting at the givenoffset,returning at mostlength bytes (less ifthe end of file is reached first). Ifoffset is negative, it is relative to the end ofthe file. Ifoffset andlength are omitted, the entire file is returned.The bytes read from the file are interpreted as a string in theserver encoding; an error is thrown if they are not valid in thatencoding.

pg_read_binary_file is similar topg_read_file, except that the resultis abytea value; accordingly, no encodingchecks are performed. In combination with theconvert_from function, this function can be usedto read a file in a specified encoding:

SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');

pg_stat_file returns a recordcontaining the file size, last accessed time stamp, last modifiedtime stamp, last file status change time stamp (Unix platformsonly), file creation time stamp (Windows only), and aboolean indicating if it is a directory. Typical usagesinclude:

SELECT * FROM pg_stat_file('filename');SELECT (pg_stat_file('filename')).modification;

9.26.8. Advisory Lock Functions

The functions shown inTable9-68 manage advisory locks. For details about proper use ofthese functions, seeSection 13.3.4.

pg_advisory_lock locks anapplication-defined resource, which can be identified either by asingle 64-bit key value or two 32-bit key values (note that thesetwo key spaces do not overlap). If another session already holds alock on the same resource identifier, this function will wait untilthe resource becomes available. The lock is exclusive. Multiplelock requests stack, so that if the same resource is locked threetimes it must then be unlocked three times to be released for othersessions' use.

pg_advisory_lock_shared works thesame aspg_advisory_lock, except thelock can be shared with other sessions requesting shared locks.Only would-be exclusive lockers are locked out.

pg_try_advisory_lock is similar topg_advisory_lock, except the functionwill not wait for the lock to become available. It will eitherobtain the lock immediately and returntrue, or returnfalse ifthe lock cannot be acquired immediately.

pg_try_advisory_lock_shared worksthe same aspg_try_advisory_lock,except it attempts to acquire a shared rather than an exclusivelock.

pg_advisory_unlock will release apreviously-acquired exclusive session level advisory lock. Itreturnstrue if the lock is successfullyreleased. If the lock was not held, it will returnfalse, and in addition, an SQL warning will bereported by the server.

pg_advisory_unlock_shared worksthe same aspg_advisory_unlock,except it releases a shared session level advisory lock.

pg_advisory_unlock_all willrelease all session level advisory locks held by the currentsession. (This function is implicitly invoked at session end, evenif the client disconnects ungracefully.)

pg_advisory_xact_lock works thesame aspg_advisory_lock, except thelock is automatically released at the end of the currenttransaction and cannot be released explicitly.

pg_advisory_xact_lock_shared worksthe same aspg_advisory_lock_shared,except the lock is automatically released at the end of the currenttransaction and cannot be released explicitly.

pg_try_advisory_xact_lock worksthe same aspg_try_advisory_lock,except the lock, if acquired, is automatically released at the endof the current transaction and cannot be released explicitly.

pg_try_advisory_xact_lock_sharedworks the same aspg_try_advisory_lock_shared, except the lock, ifacquired, is automatically released at the end of the currenttransaction and cannot be released explicitly.