Hello List,
Is there any reason why Postgres should not support an "ALTER TABLE
tablename [IF EXISTS]" feature? (And similar for other ALTER
OBJECTTYPE)
For example, a hypothetical statement that attempts to drop a
constraint in a *completely* optional manner would look like the
following:
ALTER TABLE IF EXISTS foo DROP CONSTRAINT bar IF EXISTS;
If you think this is already a reasonable idea, you can stop reading
now and express approval at the general idea. If you are curious as to
why *I* encountered it, continue reading.
It would be very useful if "pg_dump --clean" produced output that
could be run on an empty database without errors so that it is easier
to monitor the process exit status of pg_restore to check for problems
as well as enabling the use with one-transaction restore. The former
is a vital feature for organizations that manage the creation and
monitoring of many backups.
In addition, these semantics are pretty familiar: they are not
dissimilar from "make clean".
As-is, you are very likely to hit errors upon restoring a dump with
--clean in most schemas. The degree of "most" here includes "schemas
that use any form of foreign key" as a subset, so this is a rather
large set. I encountered this problem through first hoping that
meeting this requirement was entirely possible by changing pg_dump
alone. The first hurdle was:
DROP TABLE foo;
A straightforward pg_dump change can change this to:
DROP TABLE foo IF EXISTS;
Things then got tricky when dealing with the constraints on domains
and tables. In order to clean tables, for example, pg_dump will
reverse its topological sort on the dependencies and emit drop
statements for the dumpable objects individually. This ensures that
within the scope of objects to be dumped that all dependent objects
(the constraints, in this case) are dropped first, then finally the
table. Thus, one will see the following prior to dropping the table:
ALTER TABLE foo DROP CONSTRAINT bar;
While one can easily fix part of a problem by emitting the following instead:
ALTER TABLE foo DROP CONSTRAINT bar IF EXISTS;
One is still stuck on not being able to not-error when there's a
missing "foo" table. And so there is an impasse that requires some
grammar and DDL machinery changes on the server side.
fdr