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@meme.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@gmail.com>
>> wrote:
>> > On Mon, Nov 26, 2012 at 4:51 PM, Karl O. Pinc <kop@meme.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 itb.) has no other tables with FK references to it, so that we
don'trun into:
 
ERROR:  cannot truncate a table referenced in a foreign key constraintc.) is not so large that it takes forever for
datato be restored
 
with indexes and constraints left intactd.) 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