Re: pg_dump / Unique constraints

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I have a good reason not to use UNIQUE. As I remember, pg_dump creates
> the tables, copies in the data, then creates the indexes. This is much
> faster than doing the copy with the indexes already created.

Right, that's the real implementation reason for doing it in two steps.

There's also a more abstract concern: ideally, pg_dump's schema output
should be the same as what the user originally entered. Converting a
table and separate index declaration into one statement is not any more
correct than doing the reverse. Thus the real problem here is to know
which way the index got created to begin with. Currently we do not
know that, because (you guessed it) we have not got a declarative
representation for the UNIQUE constraint, only the execution-oriented
fact that the unique index exists.

My feeling is that there should be a stored indication someplace
allowing us to deduce exactly what caused the index to be created.
An ad-hoc way is to add another field to pg_index, but it might be
cleaner to create a new system catalog that covers all types of
constraint.

The next question is what pg_dump should emit, considering that it has
two conflicting goals: it wants to restore the original state of the
constraint catalog *but* also be efficient about loading data. ALTER
TABLE ADD CONSTRAINT seems to be an essential requirement there.
But it seems to me that it'd be really whizzy if there were two
different styles of output, one for a full dump (CREATE, load data,
add constraints) and one for schema-only dumps that tries to reproduce
the original table declaration with embedded constraint specs. That
would be nicer for documentation and editing purposes.

regards, tom lane