Postgres Fixtures Bug (RI_ConstraintTrigger)

Apologies if this is a duplicate thread. I couldn’t find anything in the forums, but I’ll be amazed if no-one else has come across this issue…

I have just managed to get unit testing working in my first Yii app, but I cannot get fixtures to work with my postgres database. I am using the standard system.test.CDbFixtureManager component, and a very basic model class (called Cheese) generated by yiic.

The error message I get is this:


1) CheeseTest::testCreateCDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied: "RI_ConstraintTrigger_70860" is a system trigger

Some preliminary web-research led me to a discussion of what seems to be the identical problem in RubyOnRails.

This blog post explains the problem pretty well.

Basically, the Fixture manager tries to temporarily disable referential integrity checks to allow it to insert fixture data in any order. But postgres refuses to allow this to anyone except the superuser. Clearly, running your tests as a superuser is a dangerous thing to do.

The post describes a workaround. Postgres allows you to defer constraint checking for a transaction until all of the statements have been executed (postgres docs). That way there is no need to try and do anything which only a superuser can do.

I’m going to try and adapt the FixtureManager code to do this. Will post here if I’m successful.

If anyone has any further info on this issue, please let me know. Thanks.

OK, I have a first (rubbish, but seems to be working) solution:

Override the following 2 fixture manager functions:





	/**

	 * Prepares the fixtures for the whole test.

	 * This method is invoked in {@link init}. It executes the database init script

	 * if it exists. Otherwise, it will load all available fixtures.

	 */

	public function prepare()

	{

		$initFile=$this->basePath . DIRECTORY_SEPARATOR . $this->initScript;


		$dbconn=$this->getDbConnection();

		// Begin transaction and only check constraints at the end

		$transaction = $dbconn->beginTransaction();

		$dbconn->createCommand("SET CONSTRAINTS ALL DEFERRED")->execute();

		//$schema->checkIntegrity(false);

		

		

		if(is_file($initFile))

			require($initFile);

		else

		{

			foreach($this->getFixtures() as $tableName=>$fixturePath)

			{

				$this->resetTable($tableName);

				$this->loadFixture($tableName);

			}

		}


		// Commit transaction

		$transaction->commit();

		//$schema->checkIntegrity(true);


	}




	/**

	 * Loads the specified fixtures.

	 * For each fixture, the corresponding table will be reset first by calling

	 * {@link resetTable} and then be populated with the fixture data.

	 * The loaded fixture data may be later retrieved using {@link getRows}

	 * and {@link getRecord}.

	 * Note, if a table does not have fixture data, {@link resetTable} will still

	 * be called to reset the table.

	 * @param array fixtures to be loaded. The array keys are fixture names,

	 * and the array values are either AR class names or table names.

	 * If table names, they must begin with a colon character (e.g. 'Post'

	 * means an AR class, while ':Post' means a table name).

	 */

	public function load($fixtures)

	{

		$dbconn=$this->getDbConnection();

		$schema=$dbconn->getSchema();

		

		// Begin transaction and only check constraints at the end

		$transaction = $dbconn->beginTransaction();

		$dbconn->createCommand("SET CONSTRAINTS ALL DEFERRED")->execute();

		//$schema->checkIntegrity(false);


		$this->_rows=array();

		$this->_records=array();

		foreach($fixtures as $fixtureName=>$tableName)

		{

			if($tableName[0]===':')

			{

				$tableName=substr($tableName,1);

				unset($modelClass);

			}

			else

			{

				$modelClass=Yii::import($tableName,true);

				$tableName=CActiveRecord::model($modelClass)->tableName();

				if(($prefix=$this->getDbConnection()->tablePrefix)!='')

					$tableName=preg_replace('/{{(.*?)}}/',$prefix.'\1',$tableName);

			}

			$this->resetTable($tableName);

			$rows=$this->loadFixture($tableName);

			if(is_array($rows) && is_string($fixtureName))

			{

				$this->_rows[$fixtureName]=$rows;

				if(isset($modelClass))

				{

					foreach(array_keys($rows) as $alias)

						$this->_records[$fixtureName][$alias]=$modelClass;

				}

			}

		}


		// Commit transaction

		$transaction->commit();

		//$schema->checkIntegrity(true);

	}






The basic difference is that I don’t turn on or off integrity checking, instead I wrap the whole thing in a transaction. Immediately after starting the transaction I tell it to defer integrity checking.

I did this by defining a new FixtureManager class in my app/components directory, and telling the test suite to use it instead of CDbFixtureManager.

Problem is, I think this is postgres specific, so by doing this, I have broken the database abstraction layer. Would be good if I could do this in CPgsqlSchema.php instead so that it would continue to work with other databases. It would then be a genuine bug fix rather than a workaround.

Trouble is, using a transaction instead of disabling integrity checking is a completely different approach. Anyone got any ideas?

I have a problem with testing and PostgreSQL too. In my case I get a error when the test tries to insert new record when fixtures has been loaded in the same table. A sequence is still set to "1" and I get "integrity constraint violation", because DB tries to duplicate a primary key.

I’ll check this solution by “cheeserolls” and report. Thanks.

Thanks for this great post, cheeserolls! Saved me a lot of time.

The more compact solution:

extend class CPgsqlSchema and override method checkIntegrity




	public function checkIntegrity($check=true,$schema='')

	{

		$db = $this->getDbConnection();

	        if ($check) {

        	    $db->createCommand("SET CONSTRAINTS ALL DEFERRED")->execute();

	        } else {

        	    $db->createCommand("SET CONSTRAINTS ALL IMMEDIATE")->execute();

	        }

	}



then in config-file for unittests type something like this




...

        'components'=>array(

                'fixture'=>array(

                    'class'=>'system.test.CDbFixtureManager'

                ),

                'db' => array( // for fixtures

                    'class' => 'system.db.CDbConnection',

                    'driverMap' => array('pgsql' => 'CUnitTestPgsqlSchema'),

..

                ),

...

        ),

...



So, driverMap param allow us to include our extended scheme class.

Thanks to @Dionysius for that really helps, save me a lot of time. :)

I am not sure, if that works as expected. I can connect and create the tables if I use that extended schema, but have problems with update/delete because of foreign key violations. When I use the superuser it seems to work. Maybe I am doing something wrong? I dont want to use a superuser for testing.

did you create foreign keys as deferrable?

I didn’t succeed to manage it… I try all solution suggested but I still have problem of foreign key violation… depending the order of how the fixtures are loaded… I’m giving up =( I set in my database all constraint as deferred in stransaction though doing it with owner user…

When I look at my database logs, it stops and rollback even before the commit giving me a foreign violation key… shouldn’t the point of deferred constraints resolve that ?

EDIT:

I re-created all my tables with the parameter "DIFERRABLE" for all my constraints. Then I used Dionysus suggestion and it seems to work. I had to change my setUp() function aswell in my WebTestCase for functional tests like :




	protected function setUp()

	{

    $transaction = Yii::app()->db->beginTransaction();

    try {

      parent::setUp();

      $transaction->commit();

    }

    catch (Exception $e) {

      $transaction->rollBack();

    }

    //$this->setBrowser('*firefox');

    $this->setBrowserUrl(TEST_BASE_URL);

	}



If you want to just reset tables, for a specific test, giving in your $fixtures parameter set in your test, rewrite your prepare() function of CDbFixtureManager as :




public function prepare()

	{

    $initFile=$this->basePath . DIRECTORY_SEPARATOR . $this->initScript;

    $this->checkIntegrity(false);

    if(is_file($initFile))

      require($initFile);

    /*else

    {

      foreach($this->getFixtures() as $tableName=>$fixturePath)

      {

        $this->resetTable($tableName);

        $this->loadFixture($tableName);

      }

    }*/

    $this->checkIntegrity(true);

	}