PostgreSQL: Unclear upgrade instructions regarding identity generation (AUTO vs IDENTITY vs SEQUENCE) leads to unusable database

[ThomasLandauer]

BC Break Report

Q	A
BC Break	yes
Version	3.0.0

Summary

I have this mapping:

#[ORM\Id, ORM\Column(), ORM\GeneratedValue()]
private ?int $id = null;

After upgrading to DBAL 4 and ORM 3, running php bin/console make:migration generates:

$this->addSql('ALTER TABLE foo ALTER id ADD GENERATED BY DEFAULT AS IDENTITY');

When running this migration, I get:

• A new sequence foo_id_seq1, additional to foo_id_seq (with "Current Value": 31) I already have.
• The ID column changes from Constraint Type "None" to "Identity", with "Start": 1

Then, obviously, when persisting a new entity, I'm getting:

An exception occurred while executing a query: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "foo_pkey"
DETAIL: Key (id)=(1) already exists.

Unclear Recommendations

Before upgrading, I got this deprecation:

User Deprecated: Relying on non-optimal defaults for ID generation is deprecated, and IDENTITY results in SERIAL, which is not recommended. Instead, configure identifier generation strategies explicitly through configuration. We currently recommend "SEQUENCE" for "Doctrine\DBAL\Platforms\PostgreSqlPlatform", so you should use $configuration->setIdentityGenerationPreferences([ "Doctrine\DBAL\Platforms\PostgreSqlPlatform" => ClassMetadata::GENERATOR_TYPE_SEQUENCE, ]); (ClassMetadataFactory.php:755 called by ClassMetadataFactory.php:629, #8893, package doctrine/orm)

Which (basically) says: Switch to SEQUENCE!

On the other hand, https://github.com/doctrine/orm/blob/3.0.x/UPGRADE.md#bc-break-auto-keyword-for-identity-generation-defaults-to-identity-for-postgresql-when-using-doctrinedbal-4 says:

When using the AUTO strategy to let Doctrine determine the identity generation mechanism for an entity [...] PostgreSQL now uses IDENTITY instead of SEQUENCE.

Which (basically) says: Doctrine recommends IDENTITY.

Solution?

So the questions are:

• Which one is the recommended strategy now? SEQUENCE or IDENTITY?
• Since (more or less) everybody will run into the above migration problem sooner or later: Is there a chance that the migration generator can be improved to take care of this? If not, the upgrade instructions need to be improved to tell people how to proceed (I could take care of this).

[greg0ire]

If you start a new project with DBAL 4 and ORM 3, use IDENTITY. It will map to GENERATED BY DEFAULT AS IDENTITY. Otherwise, it will map to SERIAL, which is basically an alias for SEQUENCE.

We recommend to do the upgrade TO DBAL 4 and ORM 3 while making sure you stay with SEQUENCE, then deploy, fix any issues you have with DBAL 4 and ORM 3. Then, if that works fine, you should look into switching from SEQUENCE to IDENTITY.

Cc @jwage who is also discovering the upgrade path these days.

[jwage]

I decided to leave my id mapped with the IDENTITY strategy instead of SEQUENCE until I can get upgraded. I'll ignore the deprecation until then.

When I switch to ORM 3.0 and DBAL 4.0 I will run this migration at the same time https://www.doctrine-project.org/projects/doctrine-dbal/en/4.0/how-to/postgresql-identity-migration.html

[ThomasLandauer]

Great, thanks, that article was exactly what I was missing! I added a link to it in #11257
So I'm closing here.

[bendavies]

I decided to leave my id mapped with the IDENTITY strategy instead of SEQUENCE until I can get upgraded. I'll ignore the deprecation until then.

When I switch to ORM 3.0 and DBAL 4.0 I will run this migration at the same time doctrine-project.org/projects/doctrine-dbal/en/4.0/how-to/postgresql-identity-migration.html

@jwage did you write this backwards?

I decided to leave my id mapped with the IDENTITY strategy instead of SEQUENCE until I can get upgraded.

the eventual strategy doctine wants to use in v4 is IDENTITY, so did you mean

I decided to leave my id mapped with the SEQUENCE strategy instead of IDENTITY until I can get upgraded

thanks

[bendavies]

hmm maybe not, I think I understand now.

All my entities are currently defined as strategy=IDENTITY

despite reading all the docs and upgrade guides, i'm still a little confused on the path forward.

using orm 2.19.5, dbal 3.8.4, and defining 3 entities, with AUTO, SEQUENCE, IDENTITY strategeies,

<?php

declare(strict_types=1);

use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Table(name: 'test_auto')]
#[ORM\Entity()]
class TestAuto
{
    #[ORM\Column(type: Types::INTEGER)]
    #[ORM\Id()]
    #[ORM\GeneratedValue(strategy: 'AUTO')]
    private $id;
}

<?php

declare(strict_types=1);

use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Table(name: 'test_identity')]
#[ORM\Entity()]
class TestIdentity
{
    #[ORM\Column(type: Types::INTEGER)]
    #[ORM\Id()]
    #[ORM\GeneratedValue(strategy: 'IDENTITY')]
    private $id;
}

<?php

declare(strict_types=1);

use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Table(name: 'test_sequence')]
#[ORM\Entity()]
class TestSequence
{
    #[ORM\Column(type: Types::INTEGER)]
    #[ORM\Id()]
    #[ORM\GeneratedValue(strategy: 'SEQUENCE')]
    private $id;
}

we get a diff of:

CREATE SEQUENCE test_sequence_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE test_auto_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE TABLE test_sequence (id INT NOT NULL, PRIMARY KEY(id));
CREATE TABLE test_identity (id SERIAL NOT NULL, PRIMARY KEY(id));
CREATE TABLE test_auto (id INT NOT NULL, PRIMARY KEY(id));

so currently

• strategy AUTO/SEQUENCE uses SEQUENCE
• strategy IDENTITY uses SERIAL

I think we are saying that, in dbal 4, strategy IDENTITY will switch from serial to identity so we need to run https://www.doctrine-project.org/projects/doctrine-dbal/en/4.0/how-to/postgresql-identity-migration.html to upgrade all serial to identity

[stof]

To get the full picture, you actually need to consider the full picture, which involves 3 strategies:

• in DBAL 3 and older, the IDENTITY strategy is implemented using SERIAL in Postgresql
• in DBAL 4, the IDENTITY strategy is implemented using GENERATED BY DEFAULT AS IDENTITY
• we have the SEQUENCE strategy which is unchanged

In DBAL 3 and older, the AUTO strategy defaults to SEQUENCE for Postgresql because it was considered better than the SERIAL implementation of IDENTITY.

To me, the unclear part about the deprecation is related to the fact that we have multiple cases to consider:

• existing projects using AUTO should configure a preferred strategy of SEQUENCE to keep their behavior (they can decide to do a separate migration if they want, but that's a dedicated topic)
• existing projects not using AUTO should not need to care about the deprecation (I hope DBAL does not trigger it when the mapping does not using AUTO). If they use IDENTITY, they will need to take care of the migration when upgrading to DBAL 4, following https://www.doctrine-project.org/projects/doctrine-dbal/en/4.0/how-to/postgresql-identity-migration.html
• new projects (created on DBAL 3) should probably configure the preferred strategy to IDENTITY (even though this is generated as a SERIAL in the database) to be ready to upgrade to DBAL 4 with the preferred strategy of DBAL 4. When upgrading to DBAL 4, those projects will need to migrate with https://www.doctrine-project.org/projects/doctrine-dbal/en/4.0/how-to/postgresql-identity-migration.html

@greg0ire in DBAL 3, SERIAL is not an alias of SEQUENCE. The SERIAL implementation of IDENTITY indeed relies on a sequence (which is not hidden in the database schema) but the way it is used is different. The SEQUENCE strategy is using a sequence managed by the ORM where the id is generated on ->persist(). The IDENTITY strategy (be it based on SERIAL or GENERATED BY DEFAULT AS IDENTITY) is delegating the id generation to the database and the id value will only be set in your entity after ->flush().

[greg0ire]

Sorry, when I wrote that SERIAL is an alias for SEQUENCE, I meant SEQUENCE as in CREATE SEQUENCE, and not as in strategy: 'SEQUENCE'. The docs I linked say that

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

I called that an alias, which is by no means an official term and might be clumsy, sorry if it caused more confusion on an already confusing topic.