<p dir="auto"><strong>Migrated issue, originally created by vijchti</strong></p>
<p dir="auto">Oracle database via cx_Oracle on SQLAlchemy 1.0.6 (also tested on some older versions, got same result).</p>
<p dir="auto">About 50% of the time, using Query.first() on a query will return None. The rest of the time the query returns results as expected. The same behavior is not seen when using the expression language by itself or when using any other client on this or any other computer to retrieve the data. This issue can be duplicated on other machines.</p>
<p dir="auto">Here is the code I used to generate this error:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Fix environment variables
import os
try:
	del os.environ['ORACLE_HOME']
except KeyError:
	pass
os.environ['TNS_ADMIN'] = r'C:\product\11.1.0\client_1\network\admin'
os.environ['PATH'] = r'C:\product\11.1.0\client_1\BIN;' + os.environ['PATH']

engine = create_engine(r'oracle+cx_oracle://{username}:{password}@{service_name}'.format(username='USER', password='PASSWORD', service_name='SERVICE'))
session_maker = sessionmaker(bind=engine)
base_class = declarative_base()

class Lot(base_class):
	__tablename__ = 'lot'
	__table_args__ = {'schema': 'SCHEMA_NAME'}
	lot_key = Column(Integer, primary_key=True)
	lot_id = Column(String)

session = session_maker()
cursor = session.query(Lot)
result = cursor.first()
if result is None:
	raise Exception"><pre class="notranslate"><code class="notranslate">from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Fix environment variables
import os
try:
	del os.environ['ORACLE_HOME']
except KeyError:
	pass
os.environ['TNS_ADMIN'] = r'C:\product\11.1.0\client_1\network\admin'
os.environ['PATH'] = r'C:\product\11.1.0\client_1\BIN;' + os.environ['PATH']

engine = create_engine(r'oracle+cx_oracle://{username}:{password}@{service_name}'.format(username='USER', password='PASSWORD', service_name='SERVICE'))
session_maker = sessionmaker(bind=engine)
base_class = declarative_base()

class Lot(base_class):
	__tablename__ = 'lot'
	__table_args__ = {'schema': 'SCHEMA_NAME'}
	lot_key = Column(Integer, primary_key=True)
	lot_id = Column(String)

session = session_maker()
cursor = session.query(Lot)
result = cursor.first()
if result is None:
	raise Exception
</code></pre></div>
<h3 dir="auto">Describe the bug</h3>
<p dir="auto">I am not able to specify language configuration when using PostgreSQL full-text search functions (<code class="notranslate">to_tsvector</code>, <code class="notranslate">websearch_to_tsvector</code>, <code class="notranslate">plainto_tsvector</code>) with psycopg3. The cause appears to be sqlalchemy casting the configuration string to <code class="notranslate">VARCHAR</code>.</p>
<p dir="auto">The example is adapted from the tutorial and works as intended with psycopg2 (so does omitting language configuration in psycopg3).</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="import sqlalchemy as sa

url = &quot;postgresql+psycopg://postgres:pgpass@localhost:5432/mydb?sslmode=require&quot;
engine = sa.create_engine(url, echo=True)

metadata = sa.MetaData()

t = sa.Table(
    &quot;BakedGoods&quot;, metadata,
    sa.Column(&quot;name&quot;, sa.Text),
)

t.create(engine, checkfirst=True)

with engine.begin() as con:
    con.execute(t.insert().values(
        [{&quot;name&quot;: &quot;Apple pie&quot;}, {&quot;name&quot;: &quot;Banana bread&quot;}]))
    goods = con.execute(
        sa.select(t.c.name).where(
            sa.func.to_tsvector('english', t.c.name)\
            .match('apples', postgresql_regconfig='english')
        )
    )

for row in goods.all():
    print(f&quot;Found some apples in {row[0]}&quot;)

t.drop(engine, checkfirst=True)"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">as</span> <span class="pl-s1">sa</span>

<span class="pl-s1">url</span> <span class="pl-c1">=</span> <span class="pl-s">"postgresql+psycopg://postgres:pgpass@localhost:5432/mydb?sslmode=require"</span>
<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-en">create_engine</span>(<span class="pl-s1">url</span>, <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-s1">metadata</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">MetaData</span>()

<span class="pl-s1">t</span> <span class="pl-c1">=</span> <span class="pl-s1">sa</span>.<span class="pl-v">Table</span>(
    <span class="pl-s">"BakedGoods"</span>, <span class="pl-s1">metadata</span>,
    <span class="pl-s1">sa</span>.<span class="pl-v">Column</span>(<span class="pl-s">"name"</span>, <span class="pl-s1">sa</span>.<span class="pl-v">Text</span>),
)

<span class="pl-s1">t</span>.<span class="pl-en">create</span>(<span class="pl-s1">engine</span>, <span class="pl-s1">checkfirst</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-k">with</span> <span class="pl-s1">engine</span>.<span class="pl-en">begin</span>() <span class="pl-k">as</span> <span class="pl-s1">con</span>:
    <span class="pl-s1">con</span>.<span class="pl-en">execute</span>(<span class="pl-s1">t</span>.<span class="pl-en">insert</span>().<span class="pl-en">values</span>(
        [{<span class="pl-s">"name"</span>: <span class="pl-s">"Apple pie"</span>}, {<span class="pl-s">"name"</span>: <span class="pl-s">"Banana bread"</span>}]))
    <span class="pl-s1">goods</span> <span class="pl-c1">=</span> <span class="pl-s1">con</span>.<span class="pl-en">execute</span>(
        <span class="pl-s1">sa</span>.<span class="pl-en">select</span>(<span class="pl-s1">t</span>.<span class="pl-s1">c</span>.<span class="pl-s1">name</span>).<span class="pl-en">where</span>(
            <span class="pl-s1">sa</span>.<span class="pl-s1">func</span>.<span class="pl-en">to_tsvector</span>(<span class="pl-s">'english'</span>, <span class="pl-s1">t</span>.<span class="pl-s1">c</span>.<span class="pl-s1">name</span>)\
            .<span class="pl-en">match</span>(<span class="pl-s">'apples'</span>, <span class="pl-s1">postgresql_regconfig</span><span class="pl-c1">=</span><span class="pl-s">'english'</span>)
        )
    )

<span class="pl-k">for</span> <span class="pl-s1">row</span> <span class="pl-c1">in</span> <span class="pl-s1">goods</span>.<span class="pl-en">all</span>():
    <span class="pl-en">print</span>(<span class="pl-s">f"Found some apples in <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">row</span>[<span class="pl-c1">0</span>]<span class="pl-kos">}</span></span>"</span>)

<span class="pl-s1">t</span>.<span class="pl-en">drop</span>(<span class="pl-s1">engine</span>, <span class="pl-s1">checkfirst</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)</pre></div>
<h3 dir="auto">Error</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="❯ /home/kozak/.virtualenvs/psycopg-test/bin/python /home/kozak/Projects/tests/sqlalchemy_psycopg/test.py
2022-12-13 09:40:40,061 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2022-12-13 09:40:40,061 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-12-13 09:40:40,063 INFO sqlalchemy.engine.Engine select current_schema()
2022-12-13 09:40:40,063 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-12-13 09:40:40,064 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2022-12-13 09:40:40,064 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-12-13 09:40:40,068 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-12-13 09:40:40,072 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s::VARCHAR(64) COLLATE &quot;C&quot; AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR, %(param_3)s::VARCHAR, %(param_4)s::VARCHAR, %(param_5)s::VARCHAR]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s::VARCHAR(64) COLLATE &quot;C&quot;
2022-12-13 09:40:40,072 INFO sqlalchemy.engine.Engine [generated in 0.00024s] {'table_name': 'BakedGoods', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2022-12-13 09:40:40,074 INFO sqlalchemy.engine.Engine COMMIT
2022-12-13 09:40:40,075 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-12-13 09:40:40,076 INFO sqlalchemy.engine.Engine INSERT INTO &quot;BakedGoods&quot; (name) VALUES (%(name_m0)s::VARCHAR), (%(name_m1)s::VARCHAR)
2022-12-13 09:40:40,076 INFO sqlalchemy.engine.Engine [no key 0.00012s] {'name_m0': 'Apple pie', 'name_m1': 'Banana bread'}
2022-12-13 09:40:40,078 INFO sqlalchemy.engine.Engine SELECT &quot;BakedGoods&quot;.name 
FROM &quot;BakedGoods&quot; 
WHERE to_tsvector(%(to_tsvector_1)s::VARCHAR, &quot;BakedGoods&quot;.name) @@ plainto_tsquery('english', %(to_tsvector_2)s::VARCHAR)
2022-12-13 09:40:40,078 INFO sqlalchemy.engine.Engine [generated in 0.00025s] {'to_tsvector_1': 'english', 'to_tsvector_2': 'apples'}
2022-12-13 09:40:40,079 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py&quot;, line 1964, in _exec_single_context
    self.dialect.do_execute(
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/default.py&quot;, line 746, in do_execute
    cursor.execute(statement, parameters)
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/psycopg/cursor.py&quot;, line 725, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedFunction: function to_tsvector(character varying, text) does not exist
LINE 3: WHERE to_tsvector($1::VARCHAR, &quot;BakedGoods&quot;.name) @@ plainto...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File &quot;/home/kozak/Projects/tests/sqlalchemy_psycopg/test.py&quot;, line 20, in &lt;module&gt;
    goods = con.execute(
            ^^^^^^^^^^^^
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py&quot;, line 1414, in execute
    return meth(
           ^^^^^
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/sql/elements.py&quot;, line 489, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py&quot;, line 1638, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py&quot;, line 1842, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py&quot;, line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py&quot;, line 2325, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py&quot;, line 1964, in _exec_single_context
    self.dialect.do_execute(
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/default.py&quot;, line 746, in do_execute
    cursor.execute(statement, parameters)
  File &quot;/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/psycopg/cursor.py&quot;, line 725, in execute
    raise ex.with_traceback(None)
sqlalchemy.exc.ProgrammingError: (psycopg.errors.UndefinedFunction) function to_tsvector(character varying, text) does not exist
LINE 3: WHERE to_tsvector($1::VARCHAR, &quot;BakedGoods&quot;.name) @@ plainto...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
[SQL: SELECT &quot;BakedGoods&quot;.name 
FROM &quot;BakedGoods&quot; 
WHERE to_tsvector(%(to_tsvector_1)s::VARCHAR, &quot;BakedGoods&quot;.name) @@ plainto_tsquery('english', %(to_tsvector_2)s::VARCHAR)]
[parameters: {'to_tsvector_1': 'english', 'to_tsvector_2': 'apples'}]
(Background on this error at: https://sqlalche.me/e/20/f405)"><pre class="notranslate"><code class="notranslate">❯ /home/kozak/.virtualenvs/psycopg-test/bin/python /home/kozak/Projects/tests/sqlalchemy_psycopg/test.py
2022-12-13 09:40:40,061 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2022-12-13 09:40:40,061 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-12-13 09:40:40,063 INFO sqlalchemy.engine.Engine select current_schema()
2022-12-13 09:40:40,063 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-12-13 09:40:40,064 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2022-12-13 09:40:40,064 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-12-13 09:40:40,068 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-12-13 09:40:40,072 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s::VARCHAR(64) COLLATE "C" AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR, %(param_3)s::VARCHAR, %(param_4)s::VARCHAR, %(param_5)s::VARCHAR]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s::VARCHAR(64) COLLATE "C"
2022-12-13 09:40:40,072 INFO sqlalchemy.engine.Engine [generated in 0.00024s] {'table_name': 'BakedGoods', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
2022-12-13 09:40:40,074 INFO sqlalchemy.engine.Engine COMMIT
2022-12-13 09:40:40,075 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-12-13 09:40:40,076 INFO sqlalchemy.engine.Engine INSERT INTO "BakedGoods" (name) VALUES (%(name_m0)s::VARCHAR), (%(name_m1)s::VARCHAR)
2022-12-13 09:40:40,076 INFO sqlalchemy.engine.Engine [no key 0.00012s] {'name_m0': 'Apple pie', 'name_m1': 'Banana bread'}
2022-12-13 09:40:40,078 INFO sqlalchemy.engine.Engine SELECT "BakedGoods".name 
FROM "BakedGoods" 
WHERE to_tsvector(%(to_tsvector_1)s::VARCHAR, "BakedGoods".name) @@ plainto_tsquery('english', %(to_tsvector_2)s::VARCHAR)
2022-12-13 09:40:40,078 INFO sqlalchemy.engine.Engine [generated in 0.00025s] {'to_tsvector_1': 'english', 'to_tsvector_2': 'apples'}
2022-12-13 09:40:40,079 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 746, in do_execute
    cursor.execute(statement, parameters)
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/psycopg/cursor.py", line 725, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedFunction: function to_tsvector(character varying, text) does not exist
LINE 3: WHERE to_tsvector($1::VARCHAR, "BakedGoods".name) @@ plainto...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/kozak/Projects/tests/sqlalchemy_psycopg/test.py", line 20, in &lt;module&gt;
    goods = con.execute(
            ^^^^^^^^^^^^
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
    return meth(
           ^^^^^
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 489, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2325, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 746, in do_execute
    cursor.execute(statement, parameters)
  File "/home/kozak/.virtualenvs/psycopg-test/lib/python3.11/site-packages/psycopg/cursor.py", line 725, in execute
    raise ex.with_traceback(None)
sqlalchemy.exc.ProgrammingError: (psycopg.errors.UndefinedFunction) function to_tsvector(character varying, text) does not exist
LINE 3: WHERE to_tsvector($1::VARCHAR, "BakedGoods".name) @@ plainto...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
[SQL: SELECT "BakedGoods".name 
FROM "BakedGoods" 
WHERE to_tsvector(%(to_tsvector_1)s::VARCHAR, "BakedGoods".name) @@ plainto_tsquery('english', %(to_tsvector_2)s::VARCHAR)]
[parameters: {'to_tsvector_1': 'english', 'to_tsvector_2': 'apples'}]
(Background on this error at: https://sqlalche.me/e/20/f405)
</code></pre></div>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS: Ubuntu 22.04</li>
<li>Python: 3.11</li>
<li>SQLAlchemy: 2.0.0b4</li>
<li>Database: PostgreSQL 14</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg-3.1.4 (works with psycopg2)</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto">Similar issues:</p>
<ul dir="auto">
<li><a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="956609390" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/6833" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/6833/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/6833">#6833</a> is about <code class="notranslate">asyncpg</code> support</li>
<li><a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="958527841" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/6842" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/6842/hovercard?comment_id=908563170&amp;comment_type=issue_comment" href="https://github.com/sqlalchemy/sqlalchemy/issues/6842#issuecomment-908563170">#6842 (comment)</a> discusses the problem but it sounds like the conclusion is that the issue would not come up, and it does.</li>
</ul>
<p dir="auto">If you consider this a duplicate, apologies &amp; feel free to close. I thought it was warranted to report seeing as psycopg3 support was just introduced in the 2.0 beta.</p>