Doctrine 1

New sequence generation for PostgreSQL SQL code results in errors

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 1.2.2
  • Fix Version/s: None
  • Component/s: Import/Export
  • Labels:
    None

Description

With 1.2.2 we get errors for our generated PostgreSQL code: "sequence <sequence name> already exists". I tracked down the issue to the following:

Doctrine_Export_Pgsql::createTableSql() has the following new code:

        if (isset($options['sequenceName'])) {
            $sql[] = $this->createSequenceSql($options['sequenceName']);
        }

This is fine, but this CREATE SEQUENCE code is now added after the CREATE TABLE code. This does not work okay for PostgreSQL, since PostgreSQL will automatically create the index for you if it does not yet exist. If CREATE SEQUENCE is required for some reason, then it should ge before the CREATE TABLE to be useful (it could for example be useful if different increment intervals or starting numbers should be set for the sequence).

I tried moving the new code up, but that did not yet give me the desired results. The issue is that Doctrine_Export::exportClassesSql() collects all the SQL in an array and finally does:

rsort($sql);
return $sql;

This can break any statement set in there that should be run in a specific order. For the PostgreSQL code, this will put all the CREATE TABLE calls in font of the CREATE SEQUENCE calls, resulting in the errors that we see.

Suggested fix options:

1) Do not sort the SQL queries, but accept them as generated by the db specific Export class (but I'm only suggesting this, because I am not aware of the reason to do an rsort() in the first place).

2) Change the PostgreSQL export code to make it immune to the sorting.

We did the latter in our code. The CREATE SEQUENCE and CREATE TABLE are concatenated into one statement string ("<squence>;<table>") that is added to the list of SQL queries. This provides us a work-around for the problem. The first solution is more clean IMO, but I lack knowledge to tell if the rsort() is here for technical or aesthetic reasons.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Maurice Makaay added a comment -

An additional issue for the PostgreSQL sequence handling: the Doctrine code does create a sequence field in the tables. However, the sequence system seems not to be in use at all for generating new id fields. As a result, the sequences remain at their initial value of 1.

We noticed this when we tried to manually insert a record in the database. Five records were already available. The manual INSERT failed, because of a duplicate index error on the id field. After a few attempts, it did work. That was because the PostgreSQL sequence had reached a value of 6, which was a non-existing id for the table at that point.

I think that a choice has to be made here: either use sequences or don't:

  • When id's are always generated without using the sequence features of PostgreSQL, then do not create the id fields as sequences, but make them standard primary key integer fields instead, so no unused sequence objects will be created in the database.
  • When id generation makes use of sequence features, then it's okay to use sequences for id fields. When doing this, loading a new dataset into the database might require some additional attention for the sequences. After import, the sequences should be updated to represent the first free id value for the table that it is providing id values for. When data import makes use of the sequences directly for generating id's, then this would not be necessary of course.

I hope this helps in improving Doctrine.

Show
Maurice Makaay added a comment - An additional issue for the PostgreSQL sequence handling: the Doctrine code does create a sequence field in the tables. However, the sequence system seems not to be in use at all for generating new id fields. As a result, the sequences remain at their initial value of 1. We noticed this when we tried to manually insert a record in the database. Five records were already available. The manual INSERT failed, because of a duplicate index error on the id field. After a few attempts, it did work. That was because the PostgreSQL sequence had reached a value of 6, which was a non-existing id for the table at that point. I think that a choice has to be made here: either use sequences or don't: When id's are always generated without using the sequence features of PostgreSQL, then do not create the id fields as sequences, but make them standard primary key integer fields instead, so no unused sequence objects will be created in the database. When id generation makes use of sequence features, then it's okay to use sequences for id fields. When doing this, loading a new dataset into the database might require some additional attention for the sequences. After import, the sequences should be updated to represent the first free id value for the table that it is providing id values for. When data import makes use of the sequences directly for generating id's, then this would not be necessary of course. I hope this helps in improving Doctrine.
Hide
Permalink
Maurice Makaay added a comment -

The issue with the duplicate id's is fixed on our system.

When defining "sequence: <something>" in a YAML file for a field, Doctrine will generate two sequences: one as specified in the YAML data and one as autogenerated by PostgreSQL. Doctrine inserts will use one, direct inserts via SQL will use the other. By specifying an field as primary and autoincrement, a correct and single sequence will be generated.

This work-around works for us. The Doctrine team will have to decide on what the expected behavior for "sequence" is. The current workings seem a bit off IMO.

I hope this helps. Thanks.

Show
Maurice Makaay added a comment - The issue with the duplicate id's is fixed on our system. When defining "sequence: <something>" in a YAML file for a field, Doctrine will generate two sequences: one as specified in the YAML data and one as autogenerated by PostgreSQL. Doctrine inserts will use one, direct inserts via SQL will use the other. By specifying an field as primary and autoincrement, a correct and single sequence will be generated. This work-around works for us. The Doctrine team will have to decide on what the expected behavior for "sequence" is. The current workings seem a bit off IMO. I hope this helps. Thanks.

People

  • Assignee:
    Jonathan H. Wage
    Reporter:
    Maurice Makaay
Vote (0)
Watch (0)

Dates

  • Created:
    Updated: