Skip site navigation (1) Skip section navigation (2)

Problem with temporary disbaling foreign keys.

From: "Robert Landsmeer" <r(dot)landsmeer(at)wis(dot)nl>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Problem with temporary disbaling foreign keys.
Date: 2006-05-05 14:41:29
Message-ID: 50CA25BD6EEA954FA592C097399942E30E463F7F@CM1.wis.local (view raw or flat)
Thread:
Lists: pgsql-novice
Hello all,

 

I am in a project that uses DBUnit(in combination with JUnit) to run
some tests on our sourcecode.

For those of you who do not know DBUnit it works: 

You define a xml file with the tables and data you want to insert and
every test case the database-data

In the xml file gets inserted into the database. Since DBUnit has now
knowledge of foreign keys it doesn't know what data it should insert or
delete before other data to not get a Foreign key violation. For this
reason I want to temporary disable the foreign keys when DBUnit updates
the test-database.

 

To do this I used some code I found on a  postgres mailing list (don't
know witch one from memory tough).

 The code is as follows:

 

for (int i = 0; i < tables.length; i++) {

if (enabled) {

statement.addBatch("UPDATE pg_class SET reltriggers = COUNT(*) FROM
pg_trigger WHERE pg_class.oid = tgrelid AND relname = '" + tables[i] +
"';");

      } else {

            statement.addBatch("UPDATE pg_class SET reltriggers=0 WHERE
relname = '" + tables[i] + "';");

      }

}

 

Where the tables array is filled with the tables DBUnit updates from the
xml files, and enables is a Boolean witch tells the function if it
should enable or disable the foreign keys.

 

Tough when I run this query on a table that has no foreign keys I get a
"ctid is NULL" error.

 

From browsing the bugs mailing list I found this is caused when you do a
UPDATE statement with a aggregate function on the top level that has no
results, and that those functions should be considered illegal as it is
against the sql spec.

 

So I am looking for a way to get the keys diabled and enabled without
violating the sql spec or atleast not giving an error if there is no
foreign key on the table.

 

Can anyone help me with this?

 

Thanks in advance,

 

Robert

 

 

 

pgsql-novice by date

Next:From: Franck RoutierDate: 2006-05-05 14:52:29
Subject: Memory consumption problem
Previous:From: Bruno Wolff IIIDate: 2006-05-05 14:03:04
Subject: Re: SELECT FOR UPDATE - release lock?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group