Re: Suggestion for --truncate-tables to pg_restore

On Mon, Nov 26, 2012 at 3:42 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Nov 26, 2012 at 4:51 PM, Karl O. Pinc <kop(at)meme(dot)com> wrote:
>> 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.

Another problem: attempting to drop a unique constraint or primary key
(if we're counting these as indexes to be dropped and recreated, which
they should be if the goal is reasonable restore performance) which is
referenced by another table's foreign key will cause:
ERROR: cannot drop constraint xxx on table yyy
because other objects depend on it

and as discussed upthread, it would be impolite for pg_restore to
presume it should monkey with dropping+recreating other tables'
constraints to work around this problem, not to mention impossible
when pg_restore is not connected to the target database.

It is a common administrative task to selectively restore some
existing tables' contents from a backup, and IIRC was the impetus for
this patch. Instead of adding a bunch of options to pg_restore,
perhaps a separate tool specific to this task would be the way to go.
It could handle the minutiae of truncating, dropping and recreating
constraints and indexes of the target tables, and dealing with FKs
sensibly, without worrying about conflicts with existing pg_restore
options and behavior.

Josh