I said:
> 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.
I just had an idea about this, based on the hackery that pg_dump
currently does with triggers: what if there were an ALTER command that
allows disabling and re-enabling constraint checking and index building?
Then the dump script could look like
full CREATE TABLE with all constraints shown
ALTER TABLE DISABLE CONSTRAINTS
COPY data in
ALTER TABLE ENABLE CONSTRAINTS
and there wouldn't have to be any difference between schema and full
dump output for CREATE TABLE. If we were really brave (foolish?)
the last step could be something like
ALTER TABLE ENABLE CONSTRAINTS NOCHECK
which'd suppress the scan for constraint violations that a normal
ALTER ADD CONSTRAINT would want to do.
It also occurs to me that we should not consider pg_dump as the only
area that needs work to fix this. Why shouldn't pg_dump simply do
full CREATE TABLE with all constraints shownCREATE all indexes too
-- if not schema dump then:COPY data in
The answer to that of course is that cross-table constraints (like
REFERENCES clauses) must be disabled while loading the data, or the
intermediate states where only some tables have been loaded are likely
to fail. So we do need some kind of DISABLE CONSTRAINT mode to make
this work. But it's silly that pg_dump has to go out of its way to
create the indexes last --- if COPY has a performance problem there,
we should be fixing COPY, not requiring pg_dump to contort itself.
Why can't COPY recognize for itself that rebuilding the indexes after
loading data is a better strategy than incremental index update?
(The simplest implementation would restrict this to happen only if the
table is empty when COPY starts, which'd be sufficient for pg_dump.)
regards, tom lane
