postgresql and foreign key constraints

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

postgresql and foreign key constraints

Levent Aksu
Hi,
I have my application running against a postgresql database. While
preparing tests for it, I have run into the foreign key problem during
CLEAN_INSERT. While Googling around, I have seen that for mysql and hsql
there are certain options to turn RI constraints off. For postgresql it
is advised to use

set constraints all deferred

but it did not help, though I have enclosed the CLEAN_INSERT operation
between "start transaction" and "commit". So I would like to learn if
you guys have a method for not hitting into "foreign key violation
exception"

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: postgresql and foreign key constraints

MATHUS Baptiste
You have to order the deletes so that DB accepts it. We never disable our constraints.
That's how we do it. When deleting, you can use filters to order the deletes correctly between the different tables (that's what CLEAN_INSERT does, it uses the DeleteAllOperation class).

That works very well.

The bad point is this: you can't use this method to deletes rows on tables that might have reflexive constraints (say TABLEA.ID has a fk constraint on TABLE.PARENT_ID).
DeleteAllOperation orders deletes reading the db metadata, but it's not sufficient for reflexivity.
If you have such constraints, let me know. If not, DeleteAllOperation should work.

I wrote a class like the DeleteAllOperation of the DBUnit, but this one is able to handle reflexive constraints. If it finds such constraints, it stops sending "delete from giventable", but instead orders ids to let the deletes pass (currently it's only tested with a single reflexive constraints, I didn't test with more than one). The code's quite ugly, but has already been used here with success for some months. Initially, I wanted to propose this class as a patch, but I guess I let the time go on and didn't do it... :-)

Cheers.

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de Levent Aksu
Envoyé : jeudi 31 janvier 2008 13:33
À : [hidden email]
Objet : [dbunit-user] postgresql and foreign key constraints

Hi,
I have my application running against a postgresql database. While preparing tests for it, I have run into the foreign key problem during CLEAN_INSERT. While Googling around, I have seen that for mysql and hsql there are certain options to turn RI constraints off. For postgresql it is advised to use

set constraints all deferred

but it did not help, though I have enclosed the CLEAN_INSERT operation between "start transaction" and "commit". So I would like to learn if you guys have a method for not hitting into "foreign key violation exception"

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: postgresql and foreign key constraints

Levent Aksu
Hi,
Thank you for the reply. I cannot seem to find enough of documentation
so I cannot find my way through.
I use dbUnit with TestNG so I am not extending DBTestCase etc.
I get the snapshot by:
    dataSet = openDB().createDataSet();
and I dump it to the XML file by
    FlatXmlDataSet.write(dataSet, new FileOutputStream(...));

Afterwards, I retrieve the dumped dataSet from XML by
    dataSet = new FlatXmlDataSet(new File(...));
and restore it into the database by
    TransactionOperation.CLEAN_INSERT.execute(connection, dataSet);

I guess you are suggesting me to use the FilteredDataSet to order the
tables but I cannot tell at which stage should I introduce it. Also how
should the ordering be? From most referenced tables to most referencing
tables or what?
Self referencing tables and/or circular referencing tables is not a
problem yet but next.
Cheers,
Levent



MATHUS Baptiste wrote:

> You have to order the deletes so that DB accepts it. We never disable our constraints.
> That's how we do it. When deleting, you can use filters to order the deletes correctly between the different tables (that's what CLEAN_INSERT does, it uses the DeleteAllOperation class).
>
> That works very well.
>
> The bad point is this: you can't use this method to deletes rows on tables that might have reflexive constraints (say TABLEA.ID has a fk constraint on TABLE.PARENT_ID).
> DeleteAllOperation orders deletes reading the db metadata, but it's not sufficient for reflexivity.
> If you have such constraints, let me know. If not, DeleteAllOperation should work.
>
> I wrote a class like the DeleteAllOperation of the DBUnit, but this one is able to handle reflexive constraints. If it finds such constraints, it stops sending "delete from giventable", but instead orders ids to let the deletes pass (currently it's only tested with a single reflexive constraints, I didn't test with more than one). The code's quite ugly, but has already been used here with success for some months. Initially, I wanted to propose this class as a patch, but I guess I let the time go on and didn't do it... :-)
>
> Cheers.
>
> -----Message d'origine-----
> De : [hidden email] [mailto:[hidden email]] De la part de Levent Aksu
> Envoyé : jeudi 31 janvier 2008 13:33
> À : [hidden email]
> Objet : [dbunit-user] postgresql and foreign key constraints
>
> Hi,
> I have my application running against a postgresql database. While preparing tests for it, I have run into the foreign key problem during CLEAN_INSERT. While Googling around, I have seen that for mysql and hsql there are certain options to turn RI constraints off. For postgresql it is advised to use
>
> set constraints all deferred
>
> but it did not help, though I have enclosed the CLEAN_INSERT operation between "start transaction" and "commit". So I would like to learn if you guys have a method for not hitting into "foreign key violation exception"
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>  


-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user