<h3 dir="auto">Describe the bug</h3>
<p dir="auto">Since the switch from <code class="notranslate">LegacyRow</code> to <code class="notranslate">Row</code> in 2.0.0, deserialization is no longer possible between sessions (it IS possible if you don't restart the kernel).</p>
<h3 dir="auto">Optional link from <a href="https://docs.sqlalchemy.org" rel="nofollow">https://docs.sqlalchemy.org</a> which documents the behavior that is expected</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">SQLAlchemy Version in Use</h3>
<p dir="auto">1.4.42 and 2.0.4</p>
<h3 dir="auto">DBAPI (i.e. the database driver)</h3>
<p dir="auto">pg8000</p>
<h3 dir="auto">Database Vendor and Major Version</h3>
<p dir="auto">PostgreSQL 12</p>
<h3 dir="auto">Python Version</h3>
<p dir="auto">3.11.2</p>
<h3 dir="auto">Operating system</h3>
<p dir="auto">Ubuntu 20.04.5 LTS (Focal Fossa)</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="# mre.py

import os.path
import sqlalchemy
import pickle

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

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

    with open(data_path, &quot;wb&quot;) as f:
        pickle.dump(data, f)
    print(f&quot;Serialized {type(data)=} using {sqlalchemy.__version__=}.&quot;)"><pre class="notranslate"><span class="pl-c"># mre.py</span>

<span class="pl-k">import</span> <span class="pl-s1">os</span>.<span class="pl-s1">path</span>
<span class="pl-k">import</span> <span class="pl-s1">sqlalchemy</span>
<span class="pl-k">import</span> <span class="pl-s1">pickle</span>

<span class="pl-s1">data_path</span> <span class="pl-c1">=</span> <span class="pl-s">"data.pkl"</span>
<span class="pl-k">if</span> <span class="pl-s1">os</span>.<span class="pl-s1">path</span>.<span class="pl-en">exists</span>(<span class="pl-s1">data_path</span>):
    <span class="pl-k">with</span> <span class="pl-en">open</span>(<span class="pl-s1">data_path</span>, <span class="pl-s">"rb"</span>) <span class="pl-k">as</span> <span class="pl-s1">f</span>:
        <span class="pl-s1">data</span> <span class="pl-c1">=</span> <span class="pl-s1">pickle</span>.<span class="pl-en">load</span>(<span class="pl-s1">f</span>)
    <span class="pl-en">print</span>(<span class="pl-s">f"Deserialized <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-en">type</span>(<span class="pl-s1">data</span>)<span class="pl-c1">=</span><span class="pl-kos">}</span></span>."</span>)
<span class="pl-k">else</span>:
    <span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-en">create_engine</span>(<span class="pl-s">"postgresql+pg8000://postgres:Sofia123!@localhost:5002/sakila"</span>)

    <span class="pl-k">with</span> <span class="pl-s1">engine</span>.<span class="pl-en">connect</span>() <span class="pl-k">as</span> <span class="pl-s1">conn</span>:
        <span class="pl-s1">data</span> <span class="pl-c1">=</span> <span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">sqlalchemy</span>.<span class="pl-en">text</span>(<span class="pl-s">"SELECT store_id FROM store"</span>)).<span class="pl-en">fetchone</span>()

    <span class="pl-k">with</span> <span class="pl-en">open</span>(<span class="pl-s1">data_path</span>, <span class="pl-s">"wb"</span>) <span class="pl-k">as</span> <span class="pl-s1">f</span>:
        <span class="pl-s1">pickle</span>.<span class="pl-en">dump</span>(<span class="pl-s1">data</span>, <span class="pl-s1">f</span>)
    <span class="pl-en">print</span>(<span class="pl-s">f"Serialized <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-en">type</span>(<span class="pl-s1">data</span>)<span class="pl-c1">=</span><span class="pl-kos">}</span></span> using <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">__version__</span><span class="pl-c1">=</span><span class="pl-kos">}</span></span>."</span>)</pre></div>
<h3 dir="auto">Error</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;/home/dev/mre/mre.py&quot;, line 16, in &lt;module&gt;
    data = pickle.load(f)
           ^^^^^^^^^^^^^^
  File &quot;/home/dev/mre/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py&quot;, line 1636, in __new__
    raise TypeError(
TypeError: Can't replace canonical symbol for unpickled with new int value 6015043212151762847"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "/home/dev/mre/mre.py", line 16, in &lt;module&gt;
    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
</code></pre></div>
<h3 dir="auto">Additional context</h3>
<p dir="auto">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 <code class="notranslate">Row</code> vs <code class="notranslate">LegacyRow</code>.</p>
<p dir="auto">This does NOT appear if you do not allow the python process to exit before deserialization.</p>
<p dir="auto">Exact steps that I used for the MRE:</p>
<div class="highlight highlight-source-shell notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="pip install pg8000
docker run -p 5002:5432 -d rsundqvist/sakila-preload:postgres

rm -f data.pkl &amp;&amp; pip install 'sqlalchemy==1.4.46' --quiet &amp;&amp; python mre.py &amp;&amp; python mre.py
# Serialized type(data)=&lt;class 'sqlalchemy.engine.row.LegacyRow'&gt; using sqlalchemy.__version__='1.4.46'.
# Deserialized type(data)=&lt;class 'sqlalchemy.engine.row.LegacyRow'&gt;.
rm -f data.pkl &amp;&amp; pip install 'sqlalchemy==2.0.4' --quiet &amp;&amp; python mre.py &amp;&amp; python mre.py
# Serialized type(data)=&lt;class 'sqlalchemy.engine.row.Row'&gt; using sqlalchemy.__version__='2.0.4'.
# &lt;Stack trace&gt;
# TypeError: Can't replace canonical symbol for unpickled with new int value 1856181069431459150
rm -f data.pkl &amp;&amp; pip install 'git+https://github.com/sqlalchemy/sqlalchemy.git' --quiet &amp;&amp; python mre.py &amp;&amp; python mre.py
# Serialized type(data)=&lt;class 'sqlalchemy.engine.row.Row'&gt; using sqlalchemy.__version__='2.0.5'.
# &lt;Stack trace&gt;
# TypeError: Can't replace canonical symbol for unpickled with new int value 6015043212151762847"><pre class="notranslate">pip install pg8000
docker run -p 5002:5432 -d rsundqvist/sakila-preload:postgres

rm -f data.pkl <span class="pl-k">&amp;&amp;</span> pip install <span class="pl-s"><span class="pl-pds">'</span>sqlalchemy==1.4.46<span class="pl-pds">'</span></span> --quiet <span class="pl-k">&amp;&amp;</span> python mre.py <span class="pl-k">&amp;&amp;</span> python mre.py
<span class="pl-c"><span class="pl-c">#</span> Serialized type(data)=&lt;class 'sqlalchemy.engine.row.LegacyRow'&gt; using sqlalchemy.__version__='1.4.46'.</span>
<span class="pl-c"><span class="pl-c">#</span> Deserialized type(data)=&lt;class 'sqlalchemy.engine.row.LegacyRow'&gt;.</span>
rm -f data.pkl <span class="pl-k">&amp;&amp;</span> pip install <span class="pl-s"><span class="pl-pds">'</span>sqlalchemy==2.0.4<span class="pl-pds">'</span></span> --quiet <span class="pl-k">&amp;&amp;</span> python mre.py <span class="pl-k">&amp;&amp;</span> python mre.py
<span class="pl-c"><span class="pl-c">#</span> Serialized type(data)=&lt;class 'sqlalchemy.engine.row.Row'&gt; using sqlalchemy.__version__='2.0.4'.</span>
<span class="pl-c"><span class="pl-c">#</span> &lt;Stack trace&gt;</span>
<span class="pl-c"><span class="pl-c">#</span> TypeError: Can't replace canonical symbol for unpickled with new int value 1856181069431459150</span>
rm -f data.pkl <span class="pl-k">&amp;&amp;</span> pip install <span class="pl-s"><span class="pl-pds">'</span>git+https://github.com/sqlalchemy/sqlalchemy.git<span class="pl-pds">'</span></span> --quiet <span class="pl-k">&amp;&amp;</span> python mre.py <span class="pl-k">&amp;&amp;</span> python mre.py
<span class="pl-c"><span class="pl-c">#</span> Serialized type(data)=&lt;class 'sqlalchemy.engine.row.Row'&gt; using sqlalchemy.__version__='2.0.5'.</span>
<span class="pl-c"><span class="pl-c">#</span> &lt;Stack trace&gt;</span>
<span class="pl-c"><span class="pl-c">#</span> TypeError: Can't replace canonical symbol for unpickled with new int value 6015043212151762847</span></pre></div>
<p dir="auto">Maybe I missed something and this isn't permitted anymore?</p>
<p dir="auto">// RS</p>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">The (attached, also partially quoted below) test case results in:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="RuntimeError: maximum recursion depth exceeded"><pre class="notranslate"><code class="notranslate">RuntimeError: maximum recursion depth exceeded
</code></pre></div>
<p dir="auto">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.</p>
<p dir="auto">Thanks!</p>
<ul dir="auto">
<li>
<p dir="auto">Eric</p>
<p dir="auto">nodes = Table('nodes', metadata,<br>
Column('id', Integer, primary_key=True),<br>
Column('parent_id', Integer, ForeignKey('nodes.id'))<br>
)<br>
fribbits = Table('fribbits', metadata,<br>
Column('id', Integer, primary_key=True),<br>
Column('node_id', Integer, ForeignKey('nodes.id'))<br>
)<br>
class Node(object):<br>
def list_to_root(self):<br>
if self.parent_id == None:<br>
return [       else:<br>
result = <a href="%5D"></a><br>
result.append(self)<br>
return result + self.parent.list_to_root()</p>
<p dir="auto">mapper(Node, nodes, properties={<br>
'parent': relation(Node, uselist=False, remote_side=<a href="nodes.c.id">nodes.c.id</a>)<br>
})<br>
mapper(Fribbit, fribbits, properties={<br>
'node': relation(Node, uselist=False)<br>
})</p>
<p dir="auto">f = session.query(Fribbit).get(4444)<br>
n = f.node<br>
l = n.list_to_root()</p>
<p dir="auto">print "<em>working case</em> here are all Fribbits whose nodes are in that ancestry:",<br>
y = map(lambda item:item.id, l)<br>
t = session.query(Fribbit).filter(Fribbit.node_id.in_(y)).all()<br>
print t</p>
<p dir="auto">print "<strong>broken case</strong> here are all Fribbits whose nodes are in that ancestry:",<br>
z = session.query(Fribbit).filter(Fribbit.node.in_(l)).all()<br>
print z</p>
</li>
</ul>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/1221/testcase.py">testcase.py</a></p>