Skip to content

Loading…

DBAL-420: Schema Drop SQL incorrect on PostgreSQL with entities with GeneratedValue(strategy="IDENTITY") #1615

Closed
doctrinebot opened this Issue · 5 comments

2 participants

@doctrinebot

Jira issue originally created by user adamashley:

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 radacctradacctidseq':

SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence radacctradacctidseq because other objects depend on it
DETAIL: default for table radacct column radacctid depends on sequence radacctradacctidseq
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 radacctradacctidseq':

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

@doctrinebot

Comment created by adamashley:

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.

@doctrinebot

Comment created by @beberlei:

A related Github Pull-Request [GH-289] was opened
#289

@doctrinebot

Comment created by powerkiki:

@Adam Ashley, could you test whether #289 solve your issue ?

@doctrinebot

Comment created by @doctrinebot:

A related Github Pull-Request [GH-289] was closed:
#289

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label
@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.3.4 milestone
@doctrinebot doctrinebot closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.