How can a full sync with `--defer-constraints` lead to foreign key constraint violations?

[yannxaver]

I've got two databases (A and B) with the same schema (managed and kept in sync with https://github.com/ariga/atlas)

When I run a a full sync from database A to database B with --defer-constraints I get this error:

ERROR:  update or delete on table "foo" violates foreign key constraint "xyz" on table "bar"

This is the command I run:

pgsync 
--from $DATABASE_URL_PRODUCTION 
--to $DATABASE_URL_STAGING 
--to-safe 
--defer-constraints 
--truncate 

The schema is the same, the constraints are the same, the data is the same. How does this happen?

[ankane]

Hi @yannxaver, is the constraint that causes the error marked as deferrable? If not, does pgsync mark it as deferrable in the --debug output? (if not, it may be a permissions issue)