operator does not exist: trialvaluetype = character varying in CASE statement on PostgreSQL 17.3 with Optuna 4.3.0 #6096
Description
Expected behavior
Expected:
Able to run a study with numerous trials and be able to access the best trial(s) so far without any database errors. PostgreSQL should be able to compare the trialvaluetype ENUM with string literals in the CASE statement generated by Optuna/SQLAlchemy for finding the best trial.
Actual:
The script fails when study.best_trial (or any operation that calls _storage.get_best_trial()) is accessed. Able to run some trials until this occurs. The root cause is a psycopg.errors.UndefinedFunction error from PostgreSQL, indicating it cannot compare the ENUM type trialvaluetype (used for trial_values.value_type) with a character varying type within a CASE statement.
Environment
* Optuna version: 4.3.0
* Python version: 3.13.x
* SQLAlchemy version: ~2.0.35 (e.g., 2.0.35)
* Psycopg version: 3.2.4 (psycopg3)
* PostgreSQL version: 17.3 (Homebrew)
* OS: macOS
Error messages, stack traces, or logs
[I 2025-05-27 10:23:14,481] A new study created in RDB with name: study
Study created. ID: 4. Exiting to check schema.
Traceback (most recent call last):
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
~~~~~~~~~~~~~~~~~~~~~~~^
cursor, str_statement, effective_parameters, context
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
cursor.execute(statement, parameters)
~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/psycopg/cursor.py", line 97, in execute
raise ex.with_traceback(None)
psycopg.errors.UndefinedFunction: operator does not exist: trialvaluetype = character varying
LINE 3: ...$3::INTEGER ORDER BY CASE trial_values.value_type WHEN $4::V...
^
HINT: No operator 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 "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/storages/_rdb/storage.py", line 76, in _create_scoped_session
yield session
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/storages/_rdb/storage.py", line 907, in get_best_trial
trial_id = models.TrialModel.find_max_value_trial_id(study_id, 0, session)
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/storages/_rdb/models.py", line 208, in find_max_value_trial_id
.one_or_none()
~~~~~~~~~~~^^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/orm/query.py", line 2754, in one_or_none
return self._iter().one_or_none() # type: ignore
~~~~~~~~~~^^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/orm/query.py", line 2827, in _iter
result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
~~~~~~~~~~~~~~~~~~~~^
statement,
^^^^^^^^^^
params,
^^^^^^^
execution_options={"_sa_orm_load_options": self.load_options},
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 2362, in execute
return self._execute_internal(
~~~~~~~~~~~~~~~~~~~~~~^
statement,
^^^^^^^^^^
...<4 lines>...
_add_event=_add_event,
^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 2247, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
self,
^^^^^
...<4 lines>...
conn,
^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/orm/context.py", line 305, in orm_execute_statement
result = conn.execute(
statement, params or {}, execution_options=execution_options
)
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
self,
distilled_parameters,
execution_options or NO_OPTIONS,
)
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
return connection._execute_clauseelement(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
self, distilled_params, execution_options
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
ret = self._execute_context(
dialect,
...<8 lines>...
cache_hit=cache_hit,
)
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
return self._exec_single_context(
~~~~~~~~~~~~~~~~~~~~~~~~~^
dialect, context, statement, parameters
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
self._handle_dbapi_exception(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
e, str_statement, effective_parameters, cursor, context
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
~~~~~~~~~~~~~~~~~~~~~~~^
cursor, str_statement, effective_parameters, context
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
cursor.execute(statement, parameters)
~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/psycopg/cursor.py", line 97, in execute
raise ex.with_traceback(None)
sqlalchemy.exc.ProgrammingError: (psycopg.errors.UndefinedFunction) operator does not exist: trialvaluetype = character varying
LINE 3: ...$3::INTEGER ORDER BY CASE trial_values.value_type WHEN $4::V...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
[SQL: SELECT trials.trial_id AS trials_trial_id
FROM trials JOIN trial_values ON trials.trial_id = trial_values.trial_id
WHERE trials.study_id = %(study_id_1)s::INTEGER AND trials.state = %(state_1)s AND trial_values.objective = %(objective_1)s::INTEGER ORDER BY CASE trial_values.value_type WHEN %(param_1)s::VARCHAR THEN %(param_2)s::INTEGER WHEN %(param_3)s::VARCHAR THEN %(param_4)s::INTEGER WHEN %(param_5)s::VARCHAR THEN %(param_6)s::INTEGER END DESC, trial_values.value DESC
LIMIT %(param_7)s::INTEGER]
[parameters: {'study_id_1': 4, 'state_1': 'COMPLETE', 'objective_1': 0, 'param_1': 'INF_NEG', 'param_2': -1, 'param_3': 'FINITE', 'param_4': 0, 'param_5': 'INF_POS', 'param_6': 1, 'param_7': 1}]
(Background on this error at: https://sqlalche.me/e/20/f405)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/Users/vcovo/code/coins/src/backtesting/optuna_tester.py", line 31, in <module>
main()
~~~~^^
File "/Users/vcovo/code/coins/src/backtesting/optuna_tester.py", line 26, in main
study.optimize(objective, n_trials=1)
~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/study/study.py", line 475, in optimize
_optimize(
~~~~~~~~~^
study=self,
^^^^^^^^^^^
...<7 lines>...
show_progress_bar=show_progress_bar,
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/study/_optimize.py", line 63, in _optimize
_optimize_sequential(
~~~~~~~~~~~~~~~~~~~~^
study,
^^^^^^
...<8 lines>...
progress_bar=progress_bar,
^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/study/_optimize.py", line 160, in _optimize_sequential
frozen_trial = _run_trial(study, func, catch)
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/study/_optimize.py", line 221, in _run_trial
study._log_completed_trial(frozen_trial)
~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/study/study.py", line 1141, in _log_completed_trial
best_trial = self.best_trial
^^^^^^^^^^^^^^^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/study/study.py", line 162, in best_trial
best_trial = self._storage.get_best_trial(self._study_id)
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/storages/_cached_storage.py", line 180, in get_best_trial
return self._backend.get_best_trial(study_id)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/storages/_rdb/storage.py", line 898, in get_best_trial
with _create_scoped_session(self.scoped_session) as session:
~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^
File "/opt/homebrew/Cellar/python@3.13/3.13.2/Frameworks/Python.framework/Versions/3.13/lib/python3.13/contextlib.py", line 162, in __exit__
self.gen.throw(value)
~~~~~~~~~~~~~~^^^^^^^
File "/Users/vcovo/code/venvs/coins/lib/python3.13/site-packages/optuna/storages/_rdb/storage.py", line 94, in _create_scoped_session
raise optuna.exceptions.StorageInternalError(message) from e
optuna.exceptions.StorageInternalError: An exception is raised during the commit. This typically happens due to invalid data in the commit, e.g. exceeding max length.Steps to reproduce
- Set up environment with requirements listed above
dropdb optuna_db`createdb optuna_db- Run the following python code:
import optuna
from optuna.storages import RDBStorage
from sqlalchemy import NullPool
def main():
study_name = "study"
optuna_database_url = "postgresql+psycopg://user:password@localhost:5432/optuna_db"
print(f"Attempting to create study: {study_name} in {optuna_database_url}")
storage = RDBStorage(
url=optuna_database_url,
engine_kwargs={"poolclass": NullPool},
)
study = optuna.create_study(study_name=study_name, storage=storage, load_if_exists=True, direction="maximize")
print(f"Study created. ID: {study._study_id}. Exiting to check schema.")
# Add a dummy trial to trigger the error later
def objective(trial):
return trial.suggest_float("x", 0, 1)
study.optimize(objective, n_trials=1)
print(f"Best trial: {study.best_trial.value}") # This will trigger the error
if __name__ == "__main__":
main()Additional context (optional)
It appears SQLAlchemy (when used by Optuna 4.3.0) generates a CASE statement where string literals are explicitly cast to VARCHAR before comparison with the trialvaluetype ENUM. PostgreSQL 17.3 seems to not support this comparison directly.
Consider revising the SQL generation for CASE statements involving ENUMs in the PostgreSQL dialect, perhaps by:
a. Omitting the ::VARCHAR cast on the string literals.
b. Casting the string literals directly to the target ENUM type (e.g., 'FINITE'::trialvaluetype).