<h3 dir="auto">Describe the bug</h3>
<p dir="auto">We had an existing database that worked fine. However, when we copied all the data from this existing database into a new database, we have found that the ORM is trying to insert the primary key ID from one again. This is the model:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from datetime import datetime
from typing import Dict, Union

from sqlalchemy import Boolean, Column, Date, Integer, String

from database import dal


class ClassSession(dal.base):
    &quot;&quot;&quot;
    This class is responsible for managing a class session between game sessions and a teacher.
    &quot;&quot;&quot;
    __tablename__ = &quot;class_sessions&quot;

    id = Column(Integer, primary_key=True)
    name = Column(String(120), nullable=False)
    teacher_id = Column(Integer, nullable=True)
    auth_code = Column(String(120), nullable=False)
    date_created = Column(Date(), nullable=False, default=datetime.now())
    class_open = Column(Boolean(), nullable=True)
    institution = Column(String(130), nullable=True)
    lock = Column(Boolean(), default=False)"><pre class="notranslate"><code class="notranslate">from datetime import datetime
from typing import Dict, Union

from sqlalchemy import Boolean, Column, Date, Integer, String

from database import dal


class ClassSession(dal.base):
    """
    This class is responsible for managing a class session between game sessions and a teacher.
    """
    __tablename__ = "class_sessions"

    id = Column(Integer, primary_key=True)
    name = Column(String(120), nullable=False)
    teacher_id = Column(Integer, nullable=True)
    auth_code = Column(String(120), nullable=False)
    date_created = Column(Date(), nullable=False, default=datetime.now())
    class_open = Column(Boolean(), nullable=True)
    institution = Column(String(130), nullable=True)
    lock = Column(Boolean(), default=False)
</code></pre></div>
<p dir="auto">This has worked fine for over a year. However, with the new database that has entries with IDs up to 235 and in the datagrip viewer the ID column is in the primary key. When we try and make a new insert we get the following error:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint &quot;class_sessions_pkey&quot;
DETAIL:  Key (id)=(2) already exists.

[SQL: INSERT INTO class_sessions (name, teacher_id, auth_code, date_created, class_open, institution, lock) VALUES (%(name)s, %(teacher_id)s, %(auth_code)s, %(date_created)s, %(class_open)s, %(institution)s, %(lock)s) RETURNING class_sessions.id]
[parameters: {'name': 'test', 'teacher_id': 4, 'auth_code': 'UK268', 'date_created': datetime.datetime(2022, 7, 11, 12, 47, 12, 198753), 'class_open': None, 'institution': 'Test', 'lock': False}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)"><pre class="notranslate"><code class="notranslate">sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "class_sessions_pkey"
DETAIL:  Key (id)=(2) already exists.

[SQL: INSERT INTO class_sessions (name, teacher_id, auth_code, date_created, class_open, institution, lock) VALUES (%(name)s, %(teacher_id)s, %(auth_code)s, %(date_created)s, %(class_open)s, %(institution)s, %(lock)s) RETURNING class_sessions.id]
[parameters: {'name': 'test', 'teacher_id': 4, 'auth_code': 'UK268', 'date_created': datetime.datetime(2022, 7, 11, 12, 47, 12, 198753), 'class_open': None, 'institution': 'Test', 'lock': False}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
</code></pre></div>
<p dir="auto">The ID above trying to be inserted is two. This is because it was the second attempt so the ID is 2 so we can see that the primary key is increasing by one.</p>
<h3 dir="auto">To Reproduce</h3>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import Boolean, Column, Date, Integer, String

from database import dal


class ClassSession(dal.base):
    &quot;&quot;&quot;
    This class is responsible for managing a class session between game sessions and a teacher.
    &quot;&quot;&quot;
    __tablename__ = &quot;class_sessions&quot;

    id = Column(Integer, primary_key=True)
    name = Column(String(120), nullable=False)
    teacher_id = Column(Integer, nullable=True)
    auth_code = Column(String(120), nullable=False)
    date_created = Column(Date(), nullable=False, default=datetime.now())
    class_open = Column(Boolean(), nullable=True)
    institution = Column(String(130), nullable=True)
    lock = Column(Boolean(), default=False)

# below is the code that makes the insert in another file
    def create_class(self, name: str, institution: str) -&gt; ClassSession:
        &quot;&quot;&quot;
        Creates a class associated with the self._teacher_id.

        :param name: (str) name of the class being created
        :param institution: (str) the name of the institution assigned to the class
        :return: (ClassSession) the class that was created.
        &quot;&quot;&quot;
        auth_code: AuthCode = AuthCode()
        code_verified: bool = False

        InstitutionModel.check_institution(name=institution)

        while code_verified is False:
            code_exists: bool = self._check_code_exists(auth_code=auth_code.value)

            if code_exists is False:
                code_verified = True
            else:
                auth_code = AuthCode()

        new_class: ClassSession = ClassSession(
            name=name,
            teacher_id=self._teacher_id,
            auth_code=auth_code.value,
            institution=institution,
            date_created=datetime.now(),
        )
        dal.session.add(new_class)
        dal.session.commit()

        disease_permissions: DiseaseManager = DiseaseManager(class_id=new_class.id)

        for disease in self.disease_list:
            disease_permissions.add_disease_to_class(disease)

        return new_class"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Boolean</span>, <span class="pl-v">Column</span>, <span class="pl-v">Date</span>, <span class="pl-v">Integer</span>, <span class="pl-v">String</span>

<span class="pl-k">from</span> <span class="pl-s1">database</span> <span class="pl-k">import</span> <span class="pl-s1">dal</span>


<span class="pl-k">class</span> <span class="pl-v">ClassSession</span>(<span class="pl-s1">dal</span>.<span class="pl-s1">base</span>):
    <span class="pl-s">"""</span>
<span class="pl-s">    This class is responsible for managing a class session between game sessions and a teacher.</span>
<span class="pl-s">    """</span>
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"class_sessions"</span>

    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>(<span class="pl-c1">120</span>), <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)
    <span class="pl-s1">teacher_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">auth_code</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>(<span class="pl-c1">120</span>), <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)
    <span class="pl-s1">date_created</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Date</span>(), <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>, <span class="pl-s1">default</span><span class="pl-c1">=</span><span class="pl-s1">datetime</span>.<span class="pl-en">now</span>())
    <span class="pl-s1">class_open</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Boolean</span>(), <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">institution</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>(<span class="pl-c1">130</span>), <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">lock</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Boolean</span>(), <span class="pl-s1">default</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)

<span class="pl-c"># below is the code that makes the insert in another file</span>
    <span class="pl-k">def</span> <span class="pl-en">create_class</span>(<span class="pl-s1">self</span>, <span class="pl-s1">name</span>: <span class="pl-s1">str</span>, <span class="pl-s1">institution</span>: <span class="pl-s1">str</span>) <span class="pl-c1">-&gt;</span> <span class="pl-v">ClassSession</span>:
        <span class="pl-s">"""</span>
<span class="pl-s">        Creates a class associated with the self._teacher_id.</span>
<span class="pl-s"></span>
<span class="pl-s">        :param name: (str) name of the class being created</span>
<span class="pl-s">        :param institution: (str) the name of the institution assigned to the class</span>
<span class="pl-s">        :return: (ClassSession) the class that was created.</span>
<span class="pl-s">        """</span>
        <span class="pl-s1">auth_code</span>: <span class="pl-v">AuthCode</span> <span class="pl-c1">=</span> <span class="pl-v">AuthCode</span>()
        <span class="pl-s1">code_verified</span>: <span class="pl-s1">bool</span> <span class="pl-c1">=</span> <span class="pl-c1">False</span>

        <span class="pl-v">InstitutionModel</span>.<span class="pl-en">check_institution</span>(<span class="pl-s1">name</span><span class="pl-c1">=</span><span class="pl-s1">institution</span>)

        <span class="pl-k">while</span> <span class="pl-s1">code_verified</span> <span class="pl-c1">is</span> <span class="pl-c1">False</span>:
            <span class="pl-s1">code_exists</span>: <span class="pl-s1">bool</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-en">_check_code_exists</span>(<span class="pl-s1">auth_code</span><span class="pl-c1">=</span><span class="pl-s1">auth_code</span>.<span class="pl-s1">value</span>)

            <span class="pl-k">if</span> <span class="pl-s1">code_exists</span> <span class="pl-c1">is</span> <span class="pl-c1">False</span>:
                <span class="pl-s1">code_verified</span> <span class="pl-c1">=</span> <span class="pl-c1">True</span>
            <span class="pl-k">else</span>:
                <span class="pl-s1">auth_code</span> <span class="pl-c1">=</span> <span class="pl-v">AuthCode</span>()

        <span class="pl-s1">new_class</span>: <span class="pl-v">ClassSession</span> <span class="pl-c1">=</span> <span class="pl-v">ClassSession</span>(
            <span class="pl-s1">name</span><span class="pl-c1">=</span><span class="pl-s1">name</span>,
            <span class="pl-s1">teacher_id</span><span class="pl-c1">=</span><span class="pl-s1">self</span>.<span class="pl-s1">_teacher_id</span>,
            <span class="pl-s1">auth_code</span><span class="pl-c1">=</span><span class="pl-s1">auth_code</span>.<span class="pl-s1">value</span>,
            <span class="pl-s1">institution</span><span class="pl-c1">=</span><span class="pl-s1">institution</span>,
            <span class="pl-s1">date_created</span><span class="pl-c1">=</span><span class="pl-s1">datetime</span>.<span class="pl-en">now</span>(),
        )
        <span class="pl-s1">dal</span>.<span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">new_class</span>)
        <span class="pl-s1">dal</span>.<span class="pl-s1">session</span>.<span class="pl-en">commit</span>()

        <span class="pl-s1">disease_permissions</span>: <span class="pl-v">DiseaseManager</span> <span class="pl-c1">=</span> <span class="pl-v">DiseaseManager</span>(<span class="pl-s1">class_id</span><span class="pl-c1">=</span><span class="pl-s1">new_class</span>.<span class="pl-s1">id</span>)

        <span class="pl-k">for</span> <span class="pl-s1">disease</span> <span class="pl-c1">in</span> <span class="pl-s1">self</span>.<span class="pl-s1">disease_list</span>:
            <span class="pl-s1">disease_permissions</span>.<span class="pl-en">add_disease_to_class</span>(<span class="pl-s1">disease</span>)

        <span class="pl-k">return</span> <span class="pl-s1">new_class</span></pre></div>
<h3 dir="auto">Error</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint &quot;class_sessions_pkey&quot;
DETAIL:  Key (id)=(2) already exists.

[SQL: INSERT INTO class_sessions (name, teacher_id, auth_code, date_created, class_open, institution, lock) VALUES (%(name)s, %(teacher_id)s, %(auth_code)s, %(date_created)s, %(class_open)s, %(institution)s, %(lock)s) RETURNING class_sessions.id]
[parameters: {'name': 'test', 'teacher_id': 4, 'auth_code': 'UK268', 'date_created': datetime.datetime(2022, 7, 11, 12, 47, 12, 198753), 'class_open': None, 'institution': 'Test', 'lock': False}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)"><pre class="notranslate"><code class="notranslate">sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "class_sessions_pkey"
DETAIL:  Key (id)=(2) already exists.

[SQL: INSERT INTO class_sessions (name, teacher_id, auth_code, date_created, class_open, institution, lock) VALUES (%(name)s, %(teacher_id)s, %(auth_code)s, %(date_created)s, %(class_open)s, %(institution)s, %(lock)s) RETURNING class_sessions.id]
[parameters: {'name': 'test', 'teacher_id': 4, 'auth_code': 'UK268', 'date_created': datetime.datetime(2022, 7, 11, 12, 47, 12, 198753), 'class_open': None, 'institution': 'Test', 'lock': False}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
</code></pre></div>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS:</li>
<li>Python:</li>
<li>SQLAlchemy:</li>
<li>Database:</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient):</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto"><em>No response</em></p>
<p dir="auto"><strong>Question</strong><br>
Given a composite type that is defined by a user following these instructions:<br>
<a href="https://docs.sqlalchemy.org/en/13/orm/composites.html" rel="nofollow">https://docs.sqlalchemy.org/en/13/orm/composites.html</a></p>
<p dir="auto">Is there a way to register that type with <code class="notranslate">PGInspector</code> or the dialect so that it is available/used during table reflection?</p>
<p dir="auto"><strong>Additional Context</strong><br>
The closest thing to a solution I've found is the DDL for column reflection:<br>
<a href="https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.DDLEvents.column_reflect" rel="nofollow">https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.DDLEvents.column_reflect</a></p>
<p dir="auto">But since unknown column types get reflected as <code class="notranslate">NullType</code> and there might be more than one composite type, it isn't quite enough.</p>
<p dir="auto">I could:</p>
<ul dir="auto">
<li>Intercept <code class="notranslate">NullType</code> on <code class="notranslate">column_reflect</code></li>
<li>Use the column info to query postgres for the correct composite type name</li>
<li>Lookup that composite type from a registry of locally defined composites</li>
<li>Replace <code class="notranslate">NullType</code> with the correct composite type</li>
</ul>
<p dir="auto">But I don't see a way to access the connection in an "official feeling way" inside that DDL event. is there one?</p>
<p dir="auto">Worst case scenario I'll stash the connection somewhere and retrieve it inside the event, but that's pretty gross so I'd like to avoid it if possible!</p>
<p dir="auto"><strong>Related Issues</strong><br>
This question is related to <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384625505" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/2450" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/2450/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/2450">#2450</a> but doesn't require that the composite type definition is also reflected</p>
<p dir="auto">Thanks!</p>