Re: Suggestion for --truncate-tables to pg_restore

On 11/26/2012 08:45:08 PM, Josh Kupershmidt wrote:
> 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.

I'm thinking impossible because it's impossible to know
what the existing FKs are without a db connection. Impossible is
a problem. You may have another reason why it's impossible.

> It is a common administrative task to selectively restore some
> existing tables' contents from a backup, and IIRC was the impetus for
> this patch.

Yes. (And aside from listing tables individually it'd be nice
to restore tables per schema.)

It's also a bit surprising that restoring table content
is so hard/unsupported, given a db of more than minimal
complexity.

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

Per above, the tool would then either require a db connection
or at least a dump which contains the system catalogs.

I'm afraid I don't have a clear picture of what such a tool
would look like, if it does not look a lot like pg_restore.
I would like to have such a tool. I'm not certain how
much I'd be able to contribute toward making one.

Meanwhile it sounds like the --truncate-tables patch
is looking less and less desirable. I'm ready for
rejection, but will soldier on in the interest of
not wasting other people work on this, if given
direction to move forward.

Regards,

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