Issue 1:
Describe the bug

Since the switch from LegacyRow to Row in 2.0.0, deserialization is no longer possible between sessions (it IS possible if you don't restart the kernel).

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

1.4.42 and 2.0.4

DBAPI (i.e. the database driver)

pg8000

Database Vendor and Major Version

PostgreSQL 12

Python Version

3.11.2

Operating system

Ubuntu 20.04.5 LTS (Focal Fossa)

To Reproduce

# mre.py

import os.path
import sqlalchemy
import pickle

data_path = "data.pkl"
if os.path.exists(data_path):
with open(data_path, "rb") as f:
data = pickle.load(f)
print(f"Deserialized {type(data)=}.")
else:
engine = sqlalchemy.create_engine("postgresql+pg8000://postgres:Sofia123!@localhost:5002/sakila")

with engine.connect() as conn:
data = conn.execute(sqlalchemy.text("SELECT store_id FROM store")).fetchone()

with open(data_path, "wb") as f:
pickle.dump(data, f)
print(f"Serialized {type(data)=} using {sqlalchemy.__version__=}.")

Error

Traceback (most recent call last):
File "/home/dev/mre/mre.py", line 16, in <module>
data = pickle.load(f)
^^^^^^^^^^^^^^
File "/home/dev/mre/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 1636, in __new__
raise TypeError(
TypeError: Can't replace canonical symbol for unpickled with new int value 6015043212151762847

Additional context

The exact dialect + driver don't seem to matter as far as I can tell (I've tested more variants). The issue seems to be Row vs LegacyRow.

This does NOT appear if you do not allow the python process to exit before deserialization.

Exact steps that I used for the MRE:

pip install pg8000
docker run -p 5002:5432 -d rsundqvist/sakila-preload:postgres

rm -f data.pkl && pip install 'sqlalchemy==1.4.46' --quiet && python mre.py && python mre.py
# Serialized type(data)=<class 'sqlalchemy.engine.row.LegacyRow'> using sqlalchemy.__version__='1.4.46'.
# Deserialized type(data)=<class 'sqlalchemy.engine.row.LegacyRow'>.
rm -f data.pkl && pip install 'sqlalchemy==2.0.4' --quiet && python mre.py && python mre.py
# Serialized type(data)=<class 'sqlalchemy.engine.row.Row'> using sqlalchemy.__version__='2.0.4'.
# <Stack trace>
# TypeError: Can't replace canonical symbol for unpickled with new int value 1856181069431459150
rm -f data.pkl && pip install 'git+https://github.com/sqlalchemy/sqlalchemy.git' --quiet && python mre.py && python mre.py
# Serialized type(data)=<class 'sqlalchemy.engine.row.Row'> using sqlalchemy.__version__='2.0.5'.
# <Stack trace>
# TypeError: Can't replace canonical symbol for unpickled with new int value 6015043212151762847

Maybe I missed something and this isn't permitted anymore?

// RS

Issue 2:
Migrated issue, originally created by Anonymous

The (attached, also partially quoted below) test case results in:

RuntimeError: maximum recursion depth exceeded

This appears to have something to do with filtering a query using .in() when what is being tested is a mapped property. I'm not sure if the ability to filter in this particular way is meant to be supported at this time or not, but it seemed worth submitting since in general SA seems to work toward making mapped properties behave as much as possible like ordinary python properties.

Thanks!

Eric

nodes = Table('nodes', metadata,

Column('id', Integer, primary_key=True),

Column('parent_id', Integer, ForeignKey('nodes.id'))

)

fribbits = Table('fribbits', metadata,

Column('id', Integer, primary_key=True),

Column('node_id', Integer, ForeignKey('nodes.id'))

)

class Node(object):

def list_to_root(self):

if self.parent_id == None:

return [ else:

result =

result.append(self)

return result + self.parent.list_to_root()

mapper(Node, nodes, properties={

'parent': relation(Node, uselist=False, remote_side=nodes.c.id)

})

mapper(Fribbit, fribbits, properties={

'node': relation(Node, uselist=False)

})

f = session.query(Fribbit).get(4444)

n = f.node

l = n.list_to_root()

print "working case here are all Fribbits whose nodes are in that ancestry:",

y = map(lambda item:item.id, l)

t = session.query(Fribbit).filter(Fribbit.node_id.in_(y)).all()

print t

print "broken case here are all Fribbits whose nodes are in that ancestry:",

z = session.query(Fribbit).filter(Fribbit.node.in_(l)).all()

print z

Attachments: testcase.py