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
