Zend Framework

Zend_Test_PHPUnit_Db_Operation_Truncate fails on Postgres tables with foreign keys

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 1.9.5
  • Fix Version/s: 1.9.6
  • Component/s: Zend_Test_PHPUnit
  • Labels:
    None

Description

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.

See:
http://www.postgresql.org/docs/8.2/interactive/sql-truncate.html
http://www.postgresql.org/docs/8.1/interactive/sql-truncate.html

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Jonathan Tai added a comment -

Trivial patch that fixes the issue for Postgres 8.3

Show
Jonathan Tai added a comment - Trivial patch that fixes the issue for Postgres 8.3
Hide
Permalink
Jonathan Tai added a comment -

Remove patch from the bug description

Show
Jonathan Tai added a comment - Remove patch from the bug description
Hide
Permalink
Matthew Weier O'Phinney added a comment -

Assigning to Ben.

Show
Matthew Weier O'Phinney added a comment - Assigning to Ben.
Hide
Permalink
Benjamin Eberlei added a comment -

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.

Show
Benjamin Eberlei added a comment - 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.
Hide
Permalink
Jonathan Tai added a comment -

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.

Show
Jonathan Tai added a comment - 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.
Hide
Permalink
Benjamin Eberlei added a comment -

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

Show
Benjamin Eberlei added a comment - Hm ok that i didnt know about postgres truncate. Will fix it.
Hide
Permalink
Benjamin Eberlei added a comment -

Fixed and merged into 1.9 release branch

Show
Benjamin Eberlei added a comment - Fixed and merged into 1.9 release branch

People

Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Zend Framework. Try JIRA - bug tracking software for your team.