Circular foreign keys between the auth profile tables break data-only restores

[Zfinix]

serverpod_auth_core_profile and serverpod_auth_core_profile_image reference each other, so a data-only restore fails as soon as one user has a profile image. That's the normal way to move data into a database whose tables were created by Serverpod migrations, such as a Serverpod Cloud database.

Constraint	From	To
serverpod_auth_core_profile_fk_1	serverpod_auth_core_profile.imageId (UserProfile.image)	serverpod_auth_core_profile_image.id
serverpod_auth_core_profile_image_fk_0	serverpod_auth_core_profile_image.userProfileId (UserProfileImage.userProfile)	serverpod_auth_core_profile.id

A full dump restores fine, because PostgreSQL adds the constraints after loading the data.

Reproduction

1. In a 4.0.0 project, sign up a user and set a profile image.
2. pg_dump --data-only --format=custom. PostgreSQL already warns about it: there are circular foreign-key constraints among these tables: serverpod_auth_core_profile, serverpod_auth_core_profile_image.
3. Apply the same migrations to an empty database, then pg_restore --data-only --single-transaction as a user that can write rows but doesn't own the tables.

Restore	Result
--data-only	violates foreign key constraint "serverpod_auth_core_profile_fk_1" on Key (imageId)=(...)
--data-only --disable-triggers	must be owner of table author

The same happens on Serverpod Cloud, where the db user role can't disable triggers or set session_replication_role.

Suggested fix

Mark the relation as deferred, which relations support since #5605:

image: UserProfileImage?, relation(optional, deferred)

serverpod_auth_core_profile_fk_1 then becomes DEFERRABLE INITIALLY DEFERRED, and PostgreSQL checks it at commit. I tested this on the same dump: with the constraint deferred, pg_restore --data-only --single-transaction restores users with profile images and needs no workaround. The existing serverpod_auth_core tests still pass with the change.

Today the workaround takes four extra steps: save the links, clear imageId in a copy of the database, dump the copy, and add the links back after the restore.

Related: serverpod/serverpod_docs#853