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

On Friday 2025-06-13 03:42, Greg Sabino Mullane wrote:

>
>I think the overall idea is sound. But we need a better solution for the truncate fk failure. Can we introspect somehow and do a truncate or do a delete as necessary? I don't like the idea of simply ignoring the constraint, or of throwing an error.

Sorry I haven't answered, but I don't really know how to catch the error
of TRUNCATE in the C code and do a DELETE instead. I was hoping someone
would chime in and suggest an approach.

Furthermore, silently replacing the TRUNCATE with DELETE will negate all
the performance benefits we are aiming for with this patch (avoiding WAL
logging).

In pg_restore AFAICT the suggested way to handle similar FK errors is to
add another option: --disable-triggers. This works for other operations
besides TRUNCATE, and one can break referential integrity in order to
speed up loading of data.

As such, I think the proper solution would be to change TRUNCATE
implementation in the backend, in order to ignore FK constraints when
the triggers are off. How correct do you think this is? And any ideas on
how to approach the implementation?

Thanks,
Dimitris