Re: Suggestion for --truncate-tables to pg_restore

On Mon, Nov 26, 2012 at 4:51 PM, Karl O. Pinc <kop(at)meme(dot)com> wrote:
> Where I would like to go with this is to first introduce,
> as a new patch, an ALTER TABLE option to disable a
> constraint. Something like
>
> ALTER TABLE foo UNVALIDATE CONSTRAINT "constraintname";

This doesn't really make sense, because constraints that are not
validated are still enforced for new rows. This thus wouldn't save
anything performance-wise. We should perhaps have two more states:
not enforced but blindly assumed true, and not enforced and not
assumed true either. But currently, we don't.

> I don't know what the problems are with --clean
> but I would like to know if this appears
> a promising approach. If so I can pursue it,
> although I make no promises. (I sent in
> the --disable-triggers patch because it seemed
> easy and I'm not sure where a larger project fits
> into my life.)

I'm not really sure what the issues were any more; but I think they
may have had to do with dependencies between different objects messing
things up, which I think is likely to be a problem for this proposal
as well.

> P.S. An outstanding question regards --truncate-tables
> is whether it should drop indexes before truncate
> and re-create them after restore. Sounds like it should.

Well, that would improve performance, but it also makes the behavior
of object significantly different from what one might expect from the
name. One of the problems here is that there seem to be a number of
slightly-different things that one might want to do, and it's not
exactly clear what all of them are, or whether a reasonable number of
options can cater to all of them.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company