Обсуждение: table dump

On Tue, 9 Apr 2002, Jodi Kanter wrote:

> I just completed a data only dump of a table by using the following command:
>
> pg_dump genex -Rau -t species > species.sql
>
> I noticed that the first line of the file seems to be disabling some
> postgres trigger. It reads:
>
> UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'species';
>
> Then all the data is listed.
>
> At the end of the file the triggers are enabled again with the
> following command:
>
> UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger
> where pg_class.oid = tgrelid) WHERE relname = 'species';
>
> Can someone explain why this is happening? The problem is that I am
> coping this dump into another schema creation file that I have. I want
> the data from this particular file included in with my new database.
> However, I am creating this database with a user account so that all
> tables will be owned by that user. I think the problem here is that
> this trigger stuff requires that you be signed in as postgres in order
> to do anything with the pg_class table. correct?

You'd need to be a superuser I'd guess.  The reason for those statmenets
is to turn off foreign key constraints during the load of what's assumed
to be correct data since it came from a dump.  You can ignore them if you
don't have any or don't mind having the constraints run.

Hi Jodi,

    Its to stop the triggers firing when you restore the new database from
the dumped file.

     Imagine that the triggers have already done their work on other tables
when the data was first inserted or
    later changed.

     With pg_dump you are taking a snap shot that you would later wish
perhaps to restore.

    The foreign key, table and column constraints are not turned off because
their enforcement is still valid at         restore time. For example the
data may have mysteriously been damaged in the database or in the dump file
and you would thus need to know about this situation at restore time.

    The dumped file is thus copied back at restore time not inserted.

    Regards Mark Carew
    Brisbane Australia.