Yii Framework Forum: Postgres Fixtures Bug (RI_ConstraintTrigger) - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Postgres Fixtures Bug (RI_ConstraintTrigger) Rate Topic: ***** 3 Votes

#1 User is offline   cheeserolls 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 12-August 10

Posted 17 August 2010 - 08:28 PM

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.
1

#2 User is offline   cheeserolls 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 12-August 10

Posted 17 August 2010 - 09:22 PM

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?
0

#3 User is offline   gadd33 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 15-April 09
  • Location:Opole / Poland

Posted 13 November 2010 - 02:26 PM

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.
0

#4 User is offline   Lupin Sansei 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 30-December 10

Posted 30 December 2010 - 02:38 PM

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

#5 User is offline   Dionysius 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 16-May 11

Posted 16 May 2011 - 10:56 PM

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.
2

#6 User is offline   Jethro Lee 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 24-November 11

Posted 24 November 2011 - 10:20 PM

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

#7 User is offline   Sisko 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 27-September 10

Posted 12 April 2012 - 10:40 AM

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.
0

#8 User is offline   uvegpohar 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 04-October 10

Posted 22 November 2012 - 11:49 AM

did you create foreign keys as deferrable?


View PostSisko, on 12 April 2012 - 10:40 AM, said:

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.

0

#9 User is offline   Zugluk 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 205
  • Joined: 08-August 12

Posted 28 August 2013 - 05:50 AM

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);
	}

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users