[PostgreSQL]ALTER CONSTRAINT problem about testfixtures HOT 8 CLOSED

Thank you for your infromation.

The cause of this issue is referential actions.

Stack Overflow
PostgreSQL doc

Referential actions other than the NO ACTION check cannot be deferred, even if the constraint is declared deferrable.

In the NO ACTION case, I've confirmed to be deferred whithout DEFERRABLE.

from testfixtures.

Maybe it's an issue with PostgreSQL 10? Until now, I only tried with PosgreSQL 9.x and it worked.

from testfixtures.

Also, you can try checking the permissions of this user. Does this user has permission to alter constraints?

from testfixtures.

I can execute ALTER CONSTRAINT by non-superuser.
I'll try PostgreSQL 9.

USER=> ALTER TABLE "public"."environments" ALTER CONSTRAINT "environments_created_by_users_id_foreign" DEFERRABLE;
ALTER TABLE
USER=> BEGIN;
BEGIN
USER=> SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
USER=> DELETE FROM "public"."users";
ERROR:  update or delete on table "users" violates foreign key constraint "environments_created_by_users_id_foreign" on table "environments"
DETAIL:  Key (id)=(1) is still referenced from table "environments".
USER=> COMMIT;
ROLLBACK
USER=> ALTER TABLE "public"."environments" ALTER CONSTRAINT "environments_created_by_users_id_foreign" NOT DEFERRABLE;
ALTER TABLE

from testfixtures.

I've tried PostgreSQL 9.6.6. The result is the same.

Does ALTER CONSTRAINT approach require to create CONSTRAINT with DEFERRABLE ?
It worked fine.

    CONSTRAINT aaaaa FOREIGN KEY ("user")
        REFERENCES public.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE

from testfixtures.

Does ALTER CONSTRAINT approach require to create CONSTRAINT with DEFERRABLE?

Yes, it does. We just found a missing information on README.

from testfixtures.

I see. Thank you.

from testfixtures.

Does ALTER CONSTRAINT approach require to create CONSTRAINT with DEFERRABLE?

Yes, it does. We just found a missing information on README.

Sorry, I was wrong. The package should temporarely make NON DEFERRABLE constraints DEFERRABLE to make it work. So this should not be the issue.

from testfixtures.