Re: [HACKERS] Permissions on copy

Jan Wieck wrote:
>
> > either-or or just a select, so it is forced to go through the
> > executor?  If we do that, what about reload the database..  it seems
> > like copy should just dump the table as-is and not do funny tricks
> > that make it impossible to COPY FROM...
>
>     Good point!
>
>     Currently  COPY FROM does fire insert triggers.  Pg_dump uses
>     that and if there are  triggers  a  restore  from  a  pg_dump
>     output  with  triggers active might not recreate the database
>     dumped. This does not only lead to triggers, constraints  are
>     also  object  to this because reference checks implemented by
>     triggers or constraints must fail if the data isn't dumped by
>     pg_dump  in the right order of tables. For constraints it may
>     be possible (if not yet done) for pg_dump,  to  analyze  them
>     and  dump  the  tables in the right order. But pg_dump cannot
>     know what a trigger checks or what it inserts/updates/deletes
>     if fired.
>
>     So  we need at least a switch for the COPY command restricted
>     to superusers or  the  DB  owner  telling  COPY  to  suppress
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Table owber ?

>     trigger  firing.   Then have a look at pg_dump if it analyzes
>     constraints.

1. usage:  pg_dump [options] [dbname]
         -a                      dump out only the data, no schema
         -d                      dump data as proper insert strings
                                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Not only COPY should be satisfied.

2. pg_dump put triggers at the end of output -> triggers will not be
   fired on restoring.

3. As for referential constraints, it's better for pg_dump to create
   all tables without any of these, save data and then just use ALTER TABLE to
   add constraints.

Vadim