Re: pg_dump / Unique constraints

At 11:34 22/11/00 -0500, Tom Lane wrote:
>
> full CREATE TABLE with all constraints shown
>
> ALTER TABLE DISABLE CONSTRAINTS

I think you need something more like:

SET ALL CONSTRAINTS DISABLED/OFF

since disabling one tables constraints won't work when we have
subselect-in-check (or if it does, then ALTER TABLE <table-name> DISABLE
CONSTRAINTS will be a misleading name). Also, I think FK constraints on
another table that is already loaded will fail until the primary table is
loaded.

>
>and there wouldn't have to be any difference between schema and full
>dump output for CREATE TABLE.

I still see a great deal of value in being able to get a list of 'ALTER
TABLE ADD CONSTRAINT...' statements from pg_dump/restore.

>If we were really brave (foolish?)
>the last step could be something like
>
> ALTER TABLE ENABLE CONSTRAINTS NOCHECK

Eek. Won't work for index-based constraints, since they are created anyway.
It *might* be a good idea for huge DBs.

>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.

This is fine for COPY, but doesn't work for data-as-INSERTS.

>Why can't COPY recognize for itself that rebuilding the indexes after
>loading data is a better strategy than incremental index update?

The other aspect of COPY that needs fixing is the ability to specify column
order (I think); from memory that's the reason the regression DB can't be
dumped & loaded. It's also be nice to be able to specify a subset of columns.

>(The simplest implementation would restrict this to happen only if the
>table is empty when COPY starts, which'd be sufficient for pg_dump.)

Does this approach have any implications for recovery/reliability; adding a
row but not updating indexes seems a little dangerous. Or is the plan to
drop the indexes, add the data, and create the indexes?

Stepping back from the discussion for a moment, I am beginning to have
doubts about the approach: having pg_dump put the indexes (and constraints)
at the end of the dump is simple and works in all cases. The only issue,
AFAICT, is generating a single complete table defn for easy-reading. The
suggested solution seems a little extreme (a pg_dump specific hack to COPY,
when there are other more general problems with COPY that more urgently
require attention).

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/