Thread
Commits
-
Fix failure to remove non-first segments of temporary tables.
- 5fe0ab42015a 15.1 landed
- 0e758ae89a20 16.0 landed
-
BUG #17679: REFRESH MATERIALIZED VIEW CONCURRENTLY leaves temporary files?
The Post Office <noreply@postgresql.org> — 2022-11-06T23:09:56Z
The following bug has been logged on the website: Bug reference: 17679 Logged by: Balazs Szilfai Email address: balazs@obiserver.hu PostgreSQL version: 15.0 Operating system: Debian Linux Description: After we upgraded from 14 to 15, the occupied space keeps decreasing. The following files can be found in the subdirectory of the database: ( /var/lib/postgresql/15/main/base/16457 ) ... -rw-------. 1 postgres postgres 210903040 Nov 6 10:37 t8_4372109.1 -rw-------. 1 postgres postgres 1073741824 Nov 6 14:43 t8_4375122.1 -rw-------. 1 postgres postgres 1073741824 Nov 6 14:43 t8_4375122.2 -rw-------. 1 postgres postgres 434585600 Nov 6 14:43 t8_4375122.3 -rw-------. 1 postgres postgres 210919424 Nov 6 14:44 t8_4375147.1 -rw-------. 1 postgres postgres 210919424 Nov 6 15:22 t8_4375598.1 -rw-------. 1 postgres postgres 1073741824 Nov 6 15:22 t8_4375627.1 -rw-------. 1 postgres postgres 1073741824 Nov 6 15:22 t8_4375627.2 -rw-------. 1 postgres postgres 434585600 Nov 6 15:22 t8_4375627.3 -rw-------. 1 postgres postgres 210960384 Nov 6 18:58 t8_4388673.1 -rw-------. 1 postgres postgres 210960384 Nov 6 18:59 t8_4388712.1 -rw-------. 1 postgres postgres 210960384 Nov 6 19:00 t8_4392884.1 -rw-------. 1 postgres postgres 210968576 Nov 6 20:18 t8_4400972.1 -rw-------. 1 postgres postgres 210976768 Nov 6 23:15 t8_4415109.1 -rw-------. 1 postgres postgres 1073741824 Nov 6 23:16 t8_4415139.1 -rw-------. 1 postgres postgres 1073741824 Nov 6 23:16 t8_4415139.2 -rw-------. 1 postgres postgres 434634752 Nov 6 23:16 t8_4415139.3 -rw-------. 1 postgres postgres 210878464 Nov 6 09:08 t9_4370351.1 -rw-------. 1 postgres postgres 1073741824 Nov 6 09:09 t9_4370380.1 ... 200 GB of free storage space disappeared in 24 hours! Regards, Balazs
-
Re: BUG #17679: REFRESH MATERIALIZED VIEW CONCURRENTLY leaves temporary files?
Tom Lane <tgl@sss.pgh.pa.us> — 2022-11-07T16:41:35Z
PG Bug reporting form <noreply@postgresql.org> writes: > After we upgraded from 14 to 15, the occupied space keeps decreasing. > The following files can be found in the subdirectory of the database: ( > /var/lib/postgresql/15/main/base/16457 ) > ... > -rw-------. 1 postgres postgres 210903040 Nov 6 10:37 t8_4372109.1 > -rw-------. 1 postgres postgres 1073741824 Nov 6 14:43 t8_4375122.1 > -rw-------. 1 postgres postgres 1073741824 Nov 6 14:43 t8_4375122.2 > -rw-------. 1 postgres postgres 434585600 Nov 6 14:43 t8_4375122.3 Thanks for the report! Actually it's even worse: v15 will leak non-first segments of any temporary tables, not just those made during REFRESH MATERIALIZED VIEW CONCURRENTLY. I've pushed a fix that will appear in 15.1. In the meantime, you should manually delete any files named according to the tNN_NNNN.N pattern that are clearly too old to belong to any live session. regards, tom lane