Issue 1:
Describe the bug

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:

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)

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:

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)

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.

To Reproduce

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)

# below is the code that makes the insert in another file
def create_class(self, name: str, institution: str) -> ClassSession:
"""
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.
"""
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

Error

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)

Versions

OS:

Python:

SQLAlchemy:

Database:

DBAPI (eg: psycopg, cx_oracle, mysqlclient):

Additional context

No response

Issue 2:
Question

Given a composite type that is defined by a user following these instructions:

https://docs.sqlalchemy.org/en/13/orm/composites.html

Is there a way to register that type with PGInspector or the dialect so that it is available/used during table reflection?

Additional Context

The closest thing to a solution I've found is the DDL for column reflection:

https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.DDLEvents.column_reflect

But since unknown column types get reflected as NullType and there might be more than one composite type, it isn't quite enough.

I could:

Intercept NullType on column_reflect

Use the column info to query postgres for the correct composite type name

Lookup that composite type from a registry of locally defined composites

Replace NullType with the correct composite type

But I don't see a way to access the connection in an "official feeling way" inside that DDL event. is there one?

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!

Related Issues

This question is related to #2450 but doesn't require that the composite type definition is also reflected

Thanks!