[DBAL-420] Schema Drop SQL incorrect on PostgreSQL with entities with GeneratedValue(strategy="IDENTITY") Created: 23/Jan/13  Updated: 14/Apr/13  Resolved: 14/Apr/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.3.2
Fix Version/s: 2.3.4
Security Level: All

Type: Bug Priority: Major
Reporter: Adam Ashley Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: postgresql, schematool
Environment:

Symfony 2.1, PHP5.4, PostgreSQL 9.1 on Ubuntu 12.04



 Description   

This problem is probably related to #DBAL-54. However that was closed by the raiser as he changed his Entity model and it went away.

When schema drop is run the following error occurs:
Doctrine\DBAL\DBALException: An exception occurred while executing 'DROP SEQUENCE radacct_radacctid_seq':

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

The source of this problem is the difference between strategy="IDENTITY" and strategy="SEQUENCE"

With SEQUENCE doctrine creates the table schema with field type BIGINT and no specified. It then creates a seperate sequence and as far as I can tell takes care of getting and inserting the next id number itself.

With IDENTITY doctrine creates the table schema with field type BIGSERIAL and no specified default. Now postgres automatically creates a sequence and creates the column with type BIGINT and sets the DEFAULT to the pgpsql statement required to get the nextval from the sequence.

At this point the two differently configured tables will work successfully and identically, except SEQUENCE tables will only get a correct new ID when run through the doctrine code while IDENTITY tables will get the correct new ID whenever an insert is done to the table.

Because in the case of an IDENTITY field postgresql creates the field with a default value refering to the sequence the sequence can not be deleted before the table reference is removed.

For my case I need the IDENTITY fields to work as we have a RADIUS server that needs to insert into one table which is managed and mapped to an entity in Doctrine.

Swapping the order of DROP TABLE and DROP SEQUENCE commands in Doctrine/DBAL/Schema/Visitor/DropSchemaSqlCollector.php in getQueries() line 159. Does not work as a quick fix. The following error occurs as the sequence is quite correctly be dropped along with the table.

Doctrine\DBAL\DBALException: An exception occurred while executing 'DROP SEQUENCE radacct_radacctid_seq':

SQLSTATE[42P01]: Undefined table: 7 ERROR: sequence "radacct_radacctid_seq" does not exist



 Comments   
Comment by Adam Ashley [ 24/Jan/13 ]

This issue also appears to affect Doctrine_Migrations. Generated migrations try to drop and recreate automatically generated sequences associated with SERIAL fields making a mess of the database.

Comment by Benjamin Eberlei [ 22/Mar/13 ]

A related Github Pull-Request [GH-289] was opened
https://github.com/doctrine/dbal/pull/289

Comment by Adrien Crivelli [ 12/Apr/13 ]

@Adam Ashley, could you test whether https://github.com/doctrine/dbal/pull/289 solve your issue ?

Comment by Doctrine Bot [ 14/Apr/13 ]

A related Github Pull-Request [GH-289] was closed:
https://github.com/doctrine/dbal/pull/289

Generated at Thu Nov 27 22:17:43 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.