[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
|Reporter:||Adam Ashley||Assignee:||Benjamin Eberlei|
Symfony 2.1, PHP5.4, PostgreSQL 9.1 on Ubuntu 12.04
This problem is probably related to #
When schema drop is run the following error occurs:
SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence radacct_radacctid_seq because other objects depend on it
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
|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
|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: