pg_ls_tmpdir()

A system function for examining the contents of a tablespace's temporary directory

pg_ls_tmpdir() is a system function for examining the contents of a tablespace's temporary directory.

pg_ls_tmpdir() was added in PostgreSQL 12.

Usage

pg_ls_tmpdir ( [ tablespace oid ] ) → setof record ( name text, size bigint, modification timestamp with time zone )

pg_ls_tmpdir() returns a set of records containing the name, size and last modification timestamp of files in the tablespace's temporary directory. If no tablespace is specified, it defaults to the pg_default tablespace.

pg_ls_tmpdir() returns the file records unsorted, so is usually combined with ORDER BY.

Note that in contrast to pg_ls_dir(), it does not return filenames beginning with a dot, directories, and other special files.

Permissions

By default pg_ls_tmpdir() is restricted to superusers and members of the pg_monitor default role, but other users can be granted EXECUTE permission to run this function.

Source code

pg_ls_tmpdir() is implemented in src/backend/utils/adt/genfile.c.

Change history

Examples

Display files currently in the default tablespace's temporary directory:

postgres=# SELECT * FROM pg_ls_tmpdir() ORDER BY modification;
        name        |    size    |      modification      
--------------------+------------+------------------------
 pgsql_tmp3567578.0 | 1073741824 | 2020-12-24 11:09:57+01
 pgsql_tmp3567578.1 | 1073741824 | 2020-12-24 11:10:01+01
 pgsql_tmp3567578.2 | 1073741824 | 2020-12-24 11:10:05+01
 pgsql_tmp3567578.3 | 1073741824 | 2020-12-24 11:10:09+01
 pgsql_tmp3567578.4 | 1073741824 | 2020-12-24 11:10:13+01
 pgsql_tmp3567578.5 |  716013568 | 2020-12-24 11:10:16+01
(6 rows)

Display files currently in a specified tablespace's temporary directory:

postgres=# SELECT * FROM pg_ls_tmpdir((SELECT oid FROM pg_tablespace WHERE spcname='tblspc_1')) ORDER BY modification;
        name         |    size    |      modification      
---------------------+------------+------------------------
 pgsql_tmp3568453.0  | 1073741824 | 2020-12-24 11:16:54+01
 pgsql_tmp3568453.1  | 1073741824 | 2020-12-24 11:16:57+01
 pgsql_tmp3568453.2  | 1073741824 | 2020-12-24 11:17:01+01
 pgsql_tmp3568453.3  | 1073741824 | 2020-12-24 11:17:05+01
 pgsql_tmp3568453.4  | 1073741824 | 2020-12-24 11:17:09+01
 pgsql_tmp3568453.5  | 1073741824 | 2020-12-24 11:17:13+01
 pgsql_tmp3568453.6  | 1073741824 | 2020-12-24 11:17:17+01
 pgsql_tmp3568453.7  |   69771264 | 2020-12-24 11:17:21+01
(17 rows)

Categories

System function

See also

pg_ls_dir(), pg_ls_logdir(), pg_ls_waldir(), pg_ls_archive_statusdir() , temp_tablespaces