Issue 1:
Migrated issue, originally created by vijchti

Oracle database via cx_Oracle on SQLAlchemy 1.0.6 (also tested on some older versions, got same result).

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.

Here is the code I used to generate this error:

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

Issue 2:
Describe the bug

I am not able to specify language configuration when using PostgreSQL full-text search functions (to_tsvector, websearch_to_tsvector, plainto_tsvector) with psycopg3. The cause appears to be sqlalchemy casting the configuration string to VARCHAR.

The example is adapted from the tutorial and works as intended with psycopg2 (so does omitting language configuration in psycopg3).

To Reproduce

import sqlalchemy as sa

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

metadata = sa.MetaData()

t = sa.Table(
"BakedGoods", metadata,
sa.Column("name", sa.Text),
)

t.create(engine, checkfirst=True)

with engine.begin() as con:
con.execute(t.insert().values(
[{"name": "Apple pie"}, {"name": "Banana bread"}]))
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"Found some apples in {row[0]}")

t.drop(engine, checkfirst=True)

Error

❯ /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 <module>
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)

Versions

OS: Ubuntu 22.04

Python: 3.11

SQLAlchemy: 2.0.0b4

Database: PostgreSQL 14

DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg-3.1.4 (works with psycopg2)

Additional context

Similar issues:

#6833 is about asyncpg support

#6842 (comment) discusses the problem but it sounds like the conclusion is that the issue would not come up, and it does.

If you consider this a duplicate, apologies & feel free to close. I thought it was warranted to report seeing as psycopg3 support was just introduced in the 2.0 beta.