ZF-8292: Zend_Test_PHPUnit_Db_Operation_Truncate fails on Postgres tables with foreign keys

Issue Type: Bug Created: 2009-11-11T17:06:53.000+0000 Last Updated: 2009-11-20T09:16:15.000+0000 Status: Resolved Fix version(s): - 1.9.6 (24/Nov/09)

Reporter: Jonathan Tai (jtai) Assignee: Benjamin Eberlei (beberlei) Tags: - Zend_Test_PHPUnit

Related issues: Attachments: - diff


If a Postgres database has tables with foreign keys, Zend_Test_PHPUnit_Db_Operation_Truncate fails with:

[SQLSTATE[0A000]: Feature not supported: 7 ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "foo" references "bar". HINT: Truncate table "foo" at the same time, or use TRUNCATE ... CASCADE.]

The attached trivial patch fixes the problem on Postgres 8.3, but I don't know if it will work for older versions of Postgres. From what I can tell, the CASCADE parameter was added in 8.2. In 8.1, the documentation just says to TRUNCATE all the tables at once.



Posted by Jonathan Tai (jtai) on 2009-11-11T17:08:28.000+0000

Trivial patch that fixes the issue for Postgres 8.3

Posted by Jonathan Tai (jtai) on 2009-11-11T17:09:21.000+0000

Remove patch from the bug description

Posted by Matthew Weier O'Phinney (matthew) on 2009-11-12T05:34:26.000+0000

Assigning to Ben.

Posted by Benjamin Eberlei (beberlei) on 2009-11-20T04:01:36.000+0000

Can you post an example of your XML fixture plus a rough description how your foreign keys are set?

The Truncate Operation uses a reverse iterator, so it should truncate the tables in the correct order, when you specify them in a correct order.

Maybe this is an documentation issue in regards to foreign keys.

Posted by Jonathan Tai (jtai) on 2009-11-20T08:12:57.000+0000

I saw the previously-fixed bug about truncating tables in reverse order, but my read of the Postgres documentation was that it just flat-out rejects the bare truncate command, even if it wouldn't cause any cascading deletes. "TRUNCATE cannot be used on a table that has foreign-key references from other tables, unless all such tables are also truncated in the same command. Checking validity in such cases would require table scans, and the whole point is not to do one. The CASCADE option can be used to automatically include all dependent table..."

In other words, unless you have CASCADE in there, it will just look at the table metadata, see that there are foreign-key references, and stop.

If you still think it's because of my specific setup, I can attach a sample XML fixture.

Posted by Benjamin Eberlei (beberlei) on 2009-11-20T08:30:35.000+0000

Hm ok that i didnt know about postgres truncate. Will fix it.

Posted by Benjamin Eberlei (beberlei) on 2009-11-20T09:16:15.000+0000

Fixed and merged into 1.9 release branch

Have you found an issue?

See the Overview section for more details.


© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.