[DBAL-54] Incorrect sequence dropping in PostgreSQL Created: 21/Sep/10  Updated: 09/Oct/12  Resolved: 23/Sep/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.2.2
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Critical
Reporter: Tomasz Jędrzejewski Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

postgresql 8.4.3, Arch Linux 64-bit


 Description   

Currently, DBAL drops the PostgreSQL sequences using the query:

DROP SEQUENCE sequencename

While it is quite correct at the first look, it fails, if the sequence is actually used by a table. Because Doctrine 2 ORM tries to drop the sequences before the tables, it makes impossible to drop a database schema in PostgreSQL due to the following exception:

$ php53 doctrine.php orm:schema-tool:drop
Dropping database schema...
PDOException
SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence admins_id_seq because other objects depend on it
DETAIL: default for table admins column id depends on sequence admins_id_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.
/.../Libs/Doctrine/DBAL/Connection.php
Line 570
Trace:
0. PDO::query on line 570
1. Doctrine\DBAL\Connection::executeQuery on line 484
2. Doctrine\ORM\Tools\SchemaTool::dropSchema on line 78
3. Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand::executeSchemaCommand on line 59
4. Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand::execute on line 159
5. Symfony\Component\Console\Command\Command::run on line 205
6. Symfony\Component\Console\Application::doRun on line 117
7. Symfony\Component\Console\Application::run on line 7

A solution is simply to add the "CASCADE" keyword at the end of the query.

Although I encountered this problem on DBAL 2.0-beta4, I checked the most up-to-date code on Git, and the problem is still present there.

 Comments   
Comment by Benjamin Eberlei [ 21/Sep/10 ]

Would it help to drop sequences after tables? If then i would just move the code blocks.

Comment by Tomasz Jędrzejewski [ 21/Sep/10 ]

In this particular case - yes, it would help. But consider that different database engines may have different dependencies between schema elements and be more or less restrictive, so they may require different order of code blocks. I'd recommend to make a bit deeper investigation here in order not to cause potential problems with other database engines.

Comment by Benjamin Eberlei [ 23/Sep/10 ]

Fixed

Comment by Jon Wadsworth [ 20/Jun/12 ]

This issue is happening again with Doctrine 2.2.2 on Postgres 9.1.3. when trying to drio a database I get this message even with --full-database

> php doctrine.php orm:schema-tool:drop --force --full-database
Dropping database schema...

[PDOException]
SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence policycategory_id_seq because other objects depend on it
DETAIL: default for table policycategory column id depends on sequence policycategory_id_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.

I would love to help diagnose, just let me know what you need and I will be more than happy to help.

Comment by Jon Wadsworth [ 09/Oct/12 ]

I forgot to update this for anyone else who might have had my problem. The issue was solved for me when I was reviewing some models. The project was originally intended for MySQL and Generated Value Strategy was not set to Auto. Upon changing it to auto, everything worked correctly.

Generated at Wed Jun 19 20:16:34 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.