Unable to use on-conflict-do-nothing flag due to other missing flags that cannot be supplied

[bjorngoossens]

Hi all, great tool you're working on!

Using Greenmask for dumping/restoring full databases works well for us. However, we'd really like to be able to restore multiple times and skip any data that causes conflicts. The underlying pg_dump command has a feature flag for this: on-conflict-do-nothing. This is also listed in the available flags in the documentation.

However, when I try to run a dump with this flag enabled, pg_dump gives me this output:
pg_dump: error: option --on-conflict-do-nothing requires option --inserts, --rows-per-insert, or --column-inserts

I cannot figure out how to supply any of these additional flags to Greenmask, and I also don't see them listed as possible options in the documentation linked above. I also don't see any reference to these flags in the file that is used to supply the on-conflict-do-nothing flag.

Am I missing something to make on-conflict-do-nothing work nonetheless? Or should these options/flags be added to Greenmask to make it work. Thanks in advance for your help!

[wwoytenko]

Hi! Thanks for sharing your feedback. I'm glad to hear that Greenmask is helping you.

By the way, I agree it's a bit controversial that not all pg_dump/pg_restore options are supported.

I believe it's necessary to implement such options, and here’s a list of tasks we need to address:

• Implement an INSERT-like dump when the --inserts option is provided.
• Implement --on-conflict-do-nothing for the --inserts option.
• Ensure that when restoring a dump in INSERT-like format, all constraints are deployed first to prevent unexpected errors when data violates constraints.
• Develop a strategy for restoring tables topologically. We need to carefully plan the deployment, as parallel jobs can only restore tables if their dependent tables have already been restored. Initially, this might require a single job.

[wwoytenko]

I will add it to the backlog. It might be done at the end of this quarter or in the beginning of the Q4 2024.

[bjorngoossens]

@wwoytenko thanks for your input, and great to hear that you're planning on picking this up. To give a bit more insight into our intended use-case: we mainly want to use Greenmask for our dev team to quickly get up-and running locally with an anonymized but true-to-life db. Greenmask fulfills this need really well. The feedback I get from our team is that it would be even better to allow them to re-run the restoration process periodically to get new data. Now they have to set up a new, empty database for this. It would be really nice if they could just run a restore operation again.

Happy to provide you with some input when needed. Thanks!

[wwoytenko]

@bjorngoossens thank you so much for the details.

There are a few points we need to clarify to determine the best implementation approach based on the requirements.

Description:

According to the PostgreSQL docs

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.

When using --on-conflict-do-nothing, it generates the following SQL statement (example from a test database):

INSERT INTO person.password VALUES ('2811', 'M/FE9Z+3Jj1EKOLYSlfuUEnLfMlfFt91yy6qLfGguTA=', 'Tu7nuZo=', '4d6140e8-33e6-494f-8ea6-c56afc5d50ec', '2014-01-29 00:00:00') ON CONFLICT DO NOTHING;

However, this approach only works for unique or exclusion constraints. If a foreign key is not present in the referenced table, the insertion will still fail. For example:

transformed=# INSERT INTO person.password VALUES ('2811', 'M/FE9Z+3Jj1EKOLYSlfuUEnLfMlfFt91yy6qLfGguTA=', 'Tu7nuZo=', '4d6140e8-33e6-494f-8ea6-c56afc5d50ec', '2014-01-29 00:00:00') ON CONFLICT DO NOTHING;
ERROR:  insert or update on table "password" violates foreign key constraint "FK_Password_Person_BusinessEntityID"
DETAIL:  Key (businessentityid)=(2811) is not present in table "person".

How critical is this issue? Do you want to rely on other constraints like check constraints or foreign keys?

In my opinion, it might be acceptable to ignore other constraints as well, at least as an option. In this particular case, we could resolve the issue by inserting data using a transaction per statement. If the driver returns an error indicating a constraint violation, we can choose to skip that insertion. The downside is that the target table may become significantly bloated.

[wwoytenko]

I've made MR #157 which has to close this feat request

[wwoytenko]

It is closed by #157