Re: Re: Problem with disabling triggers in pg_dump

Tom Lane wrote:
> >> 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?

It was for data-only dumps. Someone might have a schema, that
doesn't dump well. So he keeps his schema creation in
separate, hand maintained SQL scripts and takes data-only
dumps.

On a restore, his schema scripts would already create all the
constraints, triggers and the like. Now the restore loads the
data, table by table. But circular dependencies will fail
all the way. And triggers might modify other tables (COPY
invokes triggers), but these derived actions are already part
of the dumped data. So it'll not do any good. At least it'll
not restore the database to the same content it had at dump
time.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #