Sequence with truncated name is not associated with a table in PostgreSQL

[Pawloland]

Bug Report

Here are my requirements in composer.json:

"require": {
  "php": ">=8.4",
  "ext-ctype": "*",
  "ext-iconv": "*",
  "doctrine/dbal": "^3.10.3",
  "doctrine/doctrine-bundle": "^2.18.1",
  "doctrine/doctrine-migrations-bundle": "^3.6",
  "doctrine/orm": "^3.5.3",
  "symfony/console": "7.3.*",
  "symfony/dotenv": "7.3.*",
  "symfony/flex": "^2.9",
  "symfony/framework-bundle": "7.3.*",
  "symfony/runtime": "7.3.*",
  "symfony/yaml": "7.3.*"
}

Summary

Command php bin/console doctrine:schema:validate -v does not correctly associate existing sequences with tables. It happens when a default generated sequence name was trimmed to 63 chars. Because of this the command proposes a migration that drops the unrecognized sequence.

Current behavior

There is a minimal reproducible example:
sqlinit.sql (I tried it in PostgreSQL 18 docker image)

CREATE TABLE student_subject_group_student (
    id_student_subject_group_student SERIAL PRIMARY KEY
);

Doctrine entity class:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'student_subject_group_student')]
class StudentSubjectGroupStudent
{
    #[ORM\Id]
    #[ORM\GeneratedValue(strategy: 'AUTO')]
    #[ORM\Column(name: 'id_student_subject_group_student', type: 'integer')]
    public private(set) ?int $idStudentSubjectGroupStudent = null;
}

Command php bin/console doctrine:schema:validate -v returns:

Mapping
-------

                                                                                                                        
 [OK] The mapping files are correct.                                                                                    
                                                                                                                        

Database
--------

                                                                                                                        
 [ERROR] The database schema is not in sync with the current mapping file.                                              
                                                                                                                        

 // 1 schema diff(s) detected:                                                                                          

     DROP SEQUENCE student_subject_group_student_id_student_subject_group_stud_seq CASCADE;

The problem here is that the default name that PostgreSQL generates for sequence is too long, so it is truncated to 59 chars, and then the default suffix _seq is added which in total gives 63 characters - maximum length of sequence name allowed in PostgreSQL. I assume doctrine searches for student_subject_group_student_id_student_subject_group_student_seq which is too long and invalid in PostgreSQL, or for student_subject_group_student_id_student_subject_group_student_ which is the previous name truncated to the first 63 chars, but such a sequence doesn't exist in the database.

For the time being I added the bellow in config/packages/doctrine.yaml to exclude my sequence from being suggested to remove:

doctrine:
    dbal:
        schema_filter: ~^(?!student_subject_group_student_id_student_subject_group_stud_seq)~

Expected behavior

Command should return no errors like bellow without the need of modifying schema_filter key in doctrine.yaml

Mapping
-------

                                                                                                                        
 [OK] The mapping files are correct.                                                                                    
                                                                                                                        

Database
--------

                                                                                                                        
 [OK] The database schema is in sync with the mapping files.                                                            
                                                                                                                        

[morozov]

Please see if this is reproducible on 4.3.x. As of DBAL 4.0 (#5396), the DBAL implements "autoincrement" using identity columns instead of sequences.