Re: Re: Problem with disabling triggers in pg_dump

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>> (Compare the behavior of "tar x" when run as superuser or not.)
>> This has not worked in the past (because those \connect commands can't
>> be ignored),

> I don't follow...do you mean that psql barfs, or do you mean that there has
> been no way to prevent them being output?

No, I mean that if they're in the script there is no way to have them
be ignored. There is a way to prevent them from being output (-z switch
to pg_dump, or some such) but *that puts control at the wrong end of the
process*. When you make a tarfile you don't have to specify whether it
will be restored with the same file ownerships or not; you determine
that when you do the restore. pg_dump scripts need the same flexibility.

An additional reason for getting rid of the \connect's is performance;
each one causes a fresh backend startup. You might also care to
contemplate the implications for per-session variables like the "disable
triggers" switch you were suggesting.

>> The thing that really bothers me about this reltriggers hack is that
>> it doesn't work if the script is being run as non-superuser. I don't
>> see why it's necessary anyway; shouldn't the order of operations be
>> create table;
>> load data;
>> create triggers and indexes;

> Yes, for a full restore that is true. But for a partial restore (where I
> have assumed triggers may exist), it's nice to disable the triggers...

Why is that nice? If you are loading more data into an existing table
structure, seems to me the *last* thing you'd want is to disable your
consistency checks. Too risky --- certainly not something I want
pg_dump doing automatically without my knowledge or consent.

I've forgotten the exact details of the scenario that forced us to put
in the reltriggers hack in the first place, but it may well be that
the problem has a better solution now that pg_dump is smart enough to
reorder its output. I can see from the CVS logs that Jan put the
trigger change into pg_dump on 7-Feb-2000, but I didn't have much luck
finding any related discussion in the mail archives. Jan, do you recall
the reasoning for it?

regards, tom lane