Thread: [WIP] Implement "pg_restore --data-only --clean" as a way to skip WAL

Hello list,

I implemented --clean support for --data-only, in order to avoid logging
to the WAL while populating the database. The attached patch issues a
TRUNCATE before COPY on each worker process, and provides a significant
speed advantage if the cluster is configure with wal_level=minimal.

It also provides a safer way to load the database, as avoiding WAL logging
also avoids potential and painful ENOSPACE on the WAL partition as I
experienced in [1]. In other words it makes things much better for my use
case.

[1] https://www.postgresql.org/message-id/flat/076464ad-3d70-dd25-9e8f-e84f27decfba%40gmx.net

But it has some rough edges. I would appreciate guidance and feedback.

* When the table-to-be-TRUNCATEd is referenced as foreign key from other
   table, the whole transaction fails with:

     ERROR: cannot truncate a table referenced in a foreign key constraint

   1. As a first step, when TRUNCATE fails I want to try a DELETE FROM
      instead, which has more chances of succeeding, and continuing with
      the COPY. How to detect the failure of ahprintf("TRUNCATE") and do
      the alternative without failing the whole transaction?

   2. Why doesn't --disable-triggers help?
      To test this, I have manually issued

        ALTER TABLE x DISABLE TRIGGER ALL

      to every table and issued manual TRUNCATE still fails. Shouldn't
      postgres skip the referential integrity checks?

   3. In my tests, all my tables start empty since I have just created the
      schema. Then pg_restore --data-only --clean first populates
      the /referencing/ tables, which is allowed because of disabled
      triggers, and then it tries to load the /referenced/ table.

      At this point the referential integrity is already broken. Getting an
      error when TRUNCATing the empty /referenced/ table doesn't make
      sense.


What do you think?

Thank you in advance,
Dimitris