Re: Suggestion for --truncate-tables to pg_restore - Mailing list pgsql-hackers

On 11/26/2012 12:06:56 PM, Robert Haas wrote:
> On Wed, Nov 21, 2012 at 12:53 AM, Josh Kupershmidt
> <schmiddy@gmail.com> wrote:
> > TBH, I didn't find the example above particularly compelling for
> > demonstrating the need for this feature. If you've just got one
> table
> > with dependent views which needs to be restored, it's pretty easy
> to
> > manually TRUNCATE and have pg_restore --data-only reload the table.
> > (And easy enough to combine the truncate and restore into a single
> > transaction in case anything goes wrong, if need be.)
> >
> > But I'm willing to grant that this proposed feature is potentially
> as
> > useful as existing restore-jiggering options like
> --disable-triggers.
> > And I guess I could see that if you're really stuck having to
> perform
> > a --data-only restore of many tables, this feature could come in
> > handy.
>
> I think I would come down on the other side of this.  We've never
> really been able to get --clean work properly in all scenarios, and
> it
> seems likely that a similar fate will befall this option.


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 would mark the constraint NOT VALID, as if the
constraint were created with the NOT VALID option.
After a constraint is UNVALIDATEd the existing
 ALTER TABLE foo VALIDATE CONSTRAINT "constraintname";

feature would turn the constraint on and check the data.

With UNVALIDATE CONSTRAINT, pg_restore could first turn
off all the constraints concerning tables to be restored,
truncate the tables, restore the data, turn the
constraints back on and re-validate the constraints.
No need to worry about ordering based on a FK referential
dependency graph or loops in such a graph (due to
DEFERRABLE INITIALLY DEFERRED).

This approach would allow the content of a table or
tables to be restored regardless of dependent objects
or FK references and preserve FK referential integrity.
Right?  I need some guidance here from someone who
knows more than I do.

There would likely need to be a pg_restore option like
--disable-constraints to invoke this functionality,
but that can be worked out later.
Likewise, I see an update and a delete trigger in
pg_triggers associated with the referenced table
in REFERENCES constraints, but no trigger for
truncate.  So making a constraint NOT VALID may
not be as easy as it seems.

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

Regards,

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."                -- Robert A. Heinlein

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.