Nathan Bossart <nathandbossart@gmail.com> writes:
> After some further investigation, I found this discussion from 2022 that
> mentions the same problem:
> https://postgr.es/m/flat/b43e03eb-3997-700e-c2d4-92c31e4e31ee%40dalibo.com
> This led to commit 75af0f401f, which added some clarity to the
> documentation for pg_dump's --clean and --if-exists options, but AFAICT we
> don't actually say that using --if-exists with --clean is _not_ guaranteed
> to prevent all errors due to nonexistent objects. Perhaps we should add
> that information. I haven't looked into what it would take to actually fix
> this issue, but from reading around I get the idea that it might not be
> straightforward.
Actually, AFAICS that 2022 thread didn't lead to any changes.
75af0f401f was in response to a pgsql-docs thread in late 2023,
and I guess by that time I'd forgotten the 2022 thread. But
I still stand by what I said in 2022:
>>> So I think mainly what we ought to do here is to adjust the
>>> documentation to make it clearer that --clean is not guaranteed
>>> to work without errors unless the target database has the same
>>> set of objects as the source. --if-exists can reduce the set
>>> of error cases, but not eliminate it. Possibly we should be
>>> more enthusiastic about recommending --create --clean (ie,
>>> drop and recreate the whole database) instead.
I don't think that actually fixing --clean to handle cases like this
without any errors is feasible, at least not without rethinking the
technology pg_dump uses significantly. For example, we couldn't
just issue "CREATE TYPE t ..." ahead of the CREATE OR REPLACE VIEW.
It'd have to be something like "CREATE TYPE IF NOT EXISTS t ..."
(which we don't have, for what are IMO very good reasons).
We could perhaps make it work by replacing --if-exists with
snippets of plpgsql code to test for existence of target objects,
but that would result in a dump that's completely unportable
instead of only somewhat unportable. And it'd be a huge amount
of work considering that "--clean --create" already does the job.
regards, tom lane
