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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
