Re: Temporary Views Cleanup Issue

"=?UTF-8?B?6LW15a6H6bmPKOWuh+W9rSk=?=" <zhaoyupeng(dot)zyp(at)alibaba-inc(dot)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