pg_ls_tmpdir()
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 ( [tablespaceoid] ) → setof record (nametext,sizebigint,modificationtimestamp 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
- PostgreSQL 12
- added (commit 9cd92d1a)
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)
References
- PostgreSQL documentation: Generic File Access Functions
