I'm trying to set my sql scripts into a transaction to achieve atomicity with my database.
The table structure is (simplified):
CREATE TABLE foo (
id serial NOT NULL,
foo varchar(50) NOT NULL,
CONSTRAINT foo_pk PRIMARY KEY (id)
);
CREATE TABLE access (
id serial NOT NULL,
foo_id int NULL
CONSTRAINT access_pk PRIMARY KEY (id)
);
ALTER TABLE access ADD CONSTRAINT access_foo
FOREIGN KEY (foo_id)
REFERENCES foo (id)
ON DELETE CASCADE
ON UPDATE CASCADE
DEFERRABLE
INITIALLY DEFERRED;
In my code I first declare:
client.query('BEGIN'); (I'm using npm library 'pg') then
insert a row into a table 'foo', then another insert to 'access' with a foo_id from the first insert. After that there is client.query('COMMIT');
All of this is in a try catch, and in the catch is client.query('ROLLBACK'); and the rolling back seems to be working if there is an issue either of the inserts. When everything should be committed I still end up in the catch block for this:
message: "insert or update on table "access" violates foreign key constraint "access_foo""
detail: "Key (foo_id)=(20) is not present in table "foo"."
I thought that deferring constraint would be enough to do this, but I guess I'm wrong. Any help is welcome.
You probably have some issue with the transaction demarcation. I ran a simple test and works wells.
insert into foo (id, foo) values (1, 'Anne');
start transaction;
insert into access (id, foo_id) values (101, 1);
insert into access (id, foo_id) values (107, 7); -- 7 does not exist yet...
insert into foo (id, foo) values (7, 'Ivan'); -- 7 now exists!
commit; -- at this point all is good
See running example at DB Fiddle.