Re: Suggestion for --truncate-tables to pg_restore

Sorry for the delay in following up here.

On Mon, Nov 26, 2012 at 8:30 PM, Karl O. Pinc <kop(at)meme(dot)com> wrote:
> 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.

Yes, that's what I meant.

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

Well, as far as I was able to tell, the use-case where this patch
worked without trouble was limited to restoring a table, or schema
with table(s), that:
a.) has some view(s) dependent on it
b.) has no other tables with FK references to it, so that we don't run into:
ERROR: cannot truncate a table referenced in a foreign key constraint
c.) is not so large that it takes forever for data to be restored
with indexes and constraints left intact
d.) and whose admin does not want to use --clean plus a list-file
which limits pg_restore to the table and its views

I was initially hoping that the patch would be more useful for
restoring a table with FKs pointing to it, but it seems the only
reliable way to do this kind of selective restore with pg_restore is
with --clean and editing the list-file. Editing the list-file is
certainly tedious and prone to manual error, but I'm not sure this
particular patch has a wide enough use-case to alleviate that pain
significantly.

Josh