Re: ALTER TABLE ... IF EXISTS feature?

On Wed, Nov 24, 2010 at 7:21 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> What are we adding a pl/pgsql dependency for?  What is the benefit that
> will warrant requiring people who disable plpgsql to enable it for
> restores?

There are two use cases I want to cover:

1) It should be possible to restore a dump made with --clean on an
empty database without error, so it can be run in a transaction and
the error code can be usefully monitored.

2) It should be possible a database be dumped and restored by a
non-superuser, again, cleanly, as per 1.

It was easy enough to change all the "DROP ..." statements to "DROP
... IF EXISTS", but the ALTER statements have no equivalent, and thus
the only way for a dump created with --clean to run without error is
to ensure that all table and domain constraints exist prior to
restore.

The obvious mechanisms that have come to mind in this thread are:

* An IF EXISTS variant of ALTER, at least for TABLE and DOMAIN
(although it may be strange to only support it on a couple of types)

* Use of anonymous-DO code blocks (the prototype uses this, and this
depends on plpgsql)

* Bizarre things I can imagine doing that involve creative queries
that, as a side effect, might drop objects that I have not mentioned
because I thought they were too gross to be given serious
consideration. But it might be plpgsql-less, which would be nice.

Note that in the case where one wants to dump/restore as a
non-superuser that one may not be in a position to conveniently do a
(DROP|CREATE) DATABASE statement to work around the problem.

--
fdr