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