Re: Re: Problem with disabling triggers in pg_dump

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

For constraints it's a circular dependency issue. Unless all of the restore
is being done in a single transaction, you can't put data in a table set
that
is something like table1 references table2 references table1. With
deferred triggers you can get data sets that cannot be created outside
of a transaction.
If we had drop constraint, we could drop and re-add the constraints
which would at least give an error message if the data failed the
constraint, but is still not a real solution.

In addition, if you're loading a data only dump into a created schema,
you're
asking for trouble if you have triggers anyway, since how does the system
know whether or not a particular trigger's effects are already included in
the
data only dump or not (say, a logging trigger). Some triggers you want to
run theoretically because they aren't circular dependencies and do
validation
of data. Other triggers have effects like creating rows that are already in
the data dump and you wouldn't want to run again.