Обсуждение: Orphaned relation files after crash recovery
Hi all, During testing recovery, I realized that it's possible that a relation file is orphaned (probably) forever if postgres crashes during executing a transaction that creates a new relation and loads data. After postgres crashing, the relation is recovered during crash recovery and the transaction is regards as aborted, but that relation file exists in database cluster. There seems no way for postgres to know that that relation is now garbage and can be deleted. The entry of that relation in pg_class exists but it is never detected even by autovacuum because autovacuum's snapshot doesn't include that. In addition, that relfilenode number is never be reused because the file already exists. Therefore I think that such orphaned relation file is left forever. The orphaned relation can be large if we are loading large data or creating a large materialized view. Is this a bug? Has this ever been discussed? Regards, -- Masahiko Sawada http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Feb 17, 2020 at 11:20 PM Masahiko Sawada <masahiko.sawada@2ndquadrant.com> wrote: > Is this a bug? Has this ever been discussed? Hi Sawada-san, There was this thread: https://www.postgresql.org/message-id/flat/CAEepm%3D0ULqYgM2aFeOnrx6YrtBg3xUdxALoyCG%2BXpssKqmezug%40mail.gmail.com We chose that as a sort of simple test case to demonstrate the machinery for performing any kind of clean-up work when when a transaction aborts, even if there is a crash restart in between. It's going to take a little while to come back to that due to some on-going redesign work on the undo proposal.
On Mon, 17 Feb 2020 at 20:05, Thomas Munro <thomas.munro@gmail.com> wrote: > > On Mon, Feb 17, 2020 at 11:20 PM Masahiko Sawada > <masahiko.sawada@2ndquadrant.com> wrote: > > Is this a bug? Has this ever been discussed? > > Hi Sawada-san, > > There was this thread: > > https://www.postgresql.org/message-id/flat/CAEepm%3D0ULqYgM2aFeOnrx6YrtBg3xUdxALoyCG%2BXpssKqmezug%40mail.gmail.com > > We chose that as a sort of simple test case to demonstrate the > machinery for performing any kind of clean-up work when when a > transaction aborts, even if there is a crash restart in between. It's > going to take a little while to come back to that due to some on-going > redesign work on the undo proposal. Thank you! That's a good use case of undo logging. I'm looking forward to the new patch. Regards, -- Masahiko Sawada http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services