Обсуждение: "Suspending" indexes and constraint updates

Hi all.

I'd need to do large updates into already populated tables as
a maintenance activity.
The updates are generated by software and are consistent with
the constraints defined in the tables.

As of now I've been looking at the index and constraint definitions,
dropping them, doing the inserts and then re-creating both indexes and
constraints.
Is there a way to "suspend" the index updates and the constraint checks
before the inserts in order to later re-enable them and do a reindex?

TIA.

--
Reg me, please!

Am Dienstag, 4. Dezember 2007 schrieb Reg Me Please:
> Is there a way to "suspend" the index updates and the constraint checks
> before the inserts in order to later re-enable them and do a reindex?

You can disable foreign-key constraints (see ALTER TABLE ... DISABLE TRIGGER;
not quite obvious, but there is a trigger beneath each foreign key
constraint).  But there is no general solution for all constraints and
indexes.

It might also be helpful to investigate the order in which pg_dump puts out
things, since it faces mostly the same issues.  It puts index and constraint
creation at the end after the data load.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Tue, Dec 04, 2007 at 10:09:06AM +0100, Reg Me Please wrote:
> Is there a way to "suspend" the index updates and the constraint checks
> before the inserts in order to later re-enable them and do a reindex?

You can defer foreign key checks and possibly constraints, but unique
index checks can't be done. For really big loads it's not uncommon to
remove the constraints and indexes prior to te load.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

Il Tuesday 04 December 2007 11:50:21 Peter Eisentraut ha scritto:
> Am Dienstag, 4. Dezember 2007 schrieb Reg Me Please:
> > Is there a way to "suspend" the index updates and the constraint checks
> > before the inserts in order to later re-enable them and do a reindex?
>
> You can disable foreign-key constraints (see ALTER TABLE ... DISABLE
> TRIGGER; not quite obvious, but there is a trigger beneath each foreign key
> constraint).  But there is no general solution for all constraints and
> indexes.
>
> It might also be helpful to investigate the order in which pg_dump puts out
> things, since it faces mostly the same issues.  It puts index and
> constraint creation at the end after the data load.

As far as I know, pg_dump usually starts with an empty DB.
Then it creates plain table to be filled with COPY.
And at last it creates indexes and constraints.
Which is not what I nedd.

In any case thanks for the hint.

--
Reg me, please!