Thread: Re: Temporary Views Cleanup Issue
"=?UTF-8?B?6LW15a6H6bmPKOWuh+W9rSk=?=" <zhaoyupeng.zyp@alibaba-inc.com> writes:
> I have discovered a peculiar issue: after creating a temporary view, if the
> backend process exits abnormally, the temporary view is not cleaned up. However,
> if a temporary table is created and the backend process exits abnormally, the
> temporary table is cleaned up.
I think this is intentional. A temp table may consume enough disk
space that it's worth hacking up autovacuum to remove the space.
The same cannot be said of other kinds of objects. (If we had
temp materialized views, they might be worth cleaning up ... but
we don't.) So we might as well leave the rest for the normal
RemoveTempRelations call the next time somebody uses the temp
namespace.
Also, I don't buy the argument that it's better for autovacuum to be
aggressive here. RemoveTempRelations will cope if, say, a temp view
has a dependency on another one. autovacuum might fail entirely
(blocking additional cleanup), if it happens to try to delete the
wrong one first. So we should minimize the amount of stuff we expect
autovacuum to accomplish.
regards, tom lane
Hello,
Thank you for your response.
We encountered a scenario where orphaned temporary views are preventing DDL
operations on a table, such as "ALTER TABLE xxx ALTER COLUMN xxx TYPE xxx." The
corresponding error message is "ERROR: cannot alter type of a column used by a view or rule."
In this case, the only way to resolve this is to manually delete the temporary
views. However, ordinary users do not have the permission to do so, resulting in
the error "ERROR: permission denied for schema pg_temp_xxx." Does this mean that
a superuser is the only one who can delete them? This is somewhat inconvenient.
The reuse of the temp namespace requires the same "ProcNumber"; however,
max_connections may be quite large, and the application may not frequently
create new connections. Even if the same "ProcNumber" is encountered, it does
not guarantee that the temp namespace will be used. Therefore, overall, the
reuse triggering cleanup might be very timely.
Additionally, I tested a scenario where temporary view B depends on temporary
view A. By observing the debug logs, I found that the current implementation can
handle such dependency scenarios without errors. If temporary view B depends on
temporary view A, then deleting temporary view A in the first round of the loop
will also delete B. In the second round of the loop, it will exit early due to
the check if (!HeapTupleIsValid(tuple)). Both RemoveTempRelations() and
do_autovacuum() call performDeletion() with the DROP_CASCADE option, and the
other parameters are generally consistent, so the automatic cleanup also seems
to be safe, right? There may be aspects I haven't considered…
Best regards,
Yupeng Zhao"=?UTF-8?B?6LW15a6H6bmPKOWuh+W9rSk=?=" <zhaoyupeng.zyp@alibaba-inc.com> writes:
> We encountered a scenario where orphaned temporary views are preventing DDL
> operations on a table, such as "ALTER TABLE xxx ALTER COLUMN xxx TYPE xxx." The
> corresponding error message is "ERROR: cannot alter type of a column used by a view or rule."
> In this case, the only way to resolve this is to manually delete the temporary
> views. However, ordinary users do not have the permission to do so, resulting in
> the error "ERROR: permission denied for schema pg_temp_xxx." Does this mean that
> a superuser is the only one who can delete them? This is somewhat inconvenient.
I can see that it's possible to get into that situation after a
backend crash. However, I don't find this to be a compelling
argument for expanding autovacuum's cleanup responsibilities.
Nobody has ever promised that PG will automatically recover from
every possible crash scenario. We generally limit our ambition
to not losing/corrupting user data. In that light, the more stuff
that autovacuum does automatically, the greater the chance that
it will automatically do something you didn't want. Who's to
say that the contents of pg_depend are fully trustworthy after
such a crash?
So I'm content with the answer "yes, you'd need superuser privileges
to clean up in such a case". If you find this operationally
inconvenient, it'd be better to focus your energy on finding and
fixing the bug that caused the original crash.
> Additionally, I tested a scenario where temporary view B depends on temporary
> view A. By observing the debug logs, I found that the current implementation can
> handle such dependency scenarios without errors.
I did not say that every possible case will trigger problems.
But I do fear that this patch will create scenarios where
autovacuum is blocked from making progress, which would soon
cause problems much larger than the one you sought to fix.
The situation I'm worried about is one where the recursive
DROP attempt fails for whatever reason (permissions, corrupted
catalogs, etc), causing the autovacuum session to error out
completely. That would repeat on the next attempt. Yes,
that's possible today --- but expanding the set of things
autovacuum will attempt to drop expands the risk of trouble.
regards, tom lane