lundi 30 mai 2016

How can I use rollback with PostgreSQL while also checking all foreign key constraints?

I am writing an integration test for a web app using PostgreSQL. I want to run my test code and web server inside a single transaction, so that I can efficiently roll back the transaction after the test to save time on the test run. That is, I am sharing the DB connection between the test code and the web server that is under test, so that both are able to see uncommitted results inside the transaction. However, I also need to insert a lot of data as test fixtures into the database, and the schema has foreign key constraints on this data.

Because my data has these foreign key constraints, I receive foreign key errors when I try to insert objects. It seems that I have to COMMIT in order to get prerequisite data to be recognized. I notice that PostgreSQL has a feature SET CONSTRAINTS DEFERRABLE. However, this isn't really what I want I believe, because this will just make it so that the constraints are not checked until the transaction is committed -- which will never happen, as the transaction will always be rolled back after the test run. i.e. this would be functionally the same as disabling foreign key constraints in the system under test.

It seems that I want is some kind of ability to nest transactions, so that I can COMMIT inside the sub-transaction and have the constraints checked there, but still roll back to the previous point and discard that commit. I investigated ROLLBACK TO SAVEPOINT, but the problem with this is that my code doesn't contain any explicit commits. Normally, I just issue statements and they work -- and I suppose they just autocommit. So it's not really clear how I would modify the application code to do an explicit commit. Also, the usage example doesn't show that it can actually be used to revert to a point after COMMIT was used. The example just shows a use of INSERT without a COMMIT, so I'm not sure if this actually works for my purposes.

Aucun commentaire:

Enregistrer un commentaire