Fix: Resolve PG17 incompatibility for ENUMS in CASE statements #6099
Fix: Resolve PG17 incompatibility for ENUMS in CASE statements #6099y0z merged 3 commits intooptuna:masterfrom
Conversation
|
@vcovo |
|
This pull request has not seen any recent activity. |
|
@gen740 in case it was missed I added the requested tests. Let me know if anything is still missing for this to be merged. |
| ( | ||
| TrialValueModel.value_type == TrialValueModel.TrialValueType.INF_NEG, | ||
| -1, | ||
| ), | ||
| ( | ||
| TrialValueModel.value_type == TrialValueModel.TrialValueType.FINITE, | ||
| 0, | ||
| ), | ||
| ( | ||
| TrialValueModel.value_type == TrialValueModel.TrialValueType.INF_POS, | ||
| 1, | ||
| ), |
There was a problem hiding this comment.
[question] I didn't tested yet, but based on the error message, can we simply write these lines like this?
| ( | |
| TrialValueModel.value_type == TrialValueModel.TrialValueType.INF_NEG, | |
| -1, | |
| ), | |
| ( | |
| TrialValueModel.value_type == TrialValueModel.TrialValueType.FINITE, | |
| 0, | |
| ), | |
| ( | |
| TrialValueModel.value_type == TrialValueModel.TrialValueType.INF_POS, | |
| 1, | |
| ), | |
| {"INF_NEG": -1, "FINITE": 0, "INF_POS": 1}, | |
| value=TrialValueModel.value_type.value, |
There was a problem hiding this comment.
I tried several alternatives, but they didn’t work as expected:
- Passing
.valueor.namecauses errors, likely becausevalue=expects a column expression rather than an attribute:
case({"INF_NEG": -1, "FINITE": 0, "INF_POS": 1},
value=TrialValueModel.value_type.value) # Fails
case({"INF_NEG": -1, "FINITE": 0, "INF_POS": 1},
value=TrialValueModel.value_type.name) # Also fails
These cases failed in:
run 15626347340/job 44021240261
run 15624687460/job 44016565482
- Using Enum members as dictionary keys also failed:
case(
{
TrialValueModel.TrialValueType.INF_NEG: -1,
TrialValueModel.TrialValueType.FINITE: 0,
TrialValueModel.TrialValueType.INF_POS: 1,
},
value=TrialValueModel.value_type,
)
This failed in:
run 15625311560/job 44018289101
Given these issues, I feel that the current comparison approach is more robust.
|
Let me change the reviewer since I'll be on leave until the week after next. @y0z Could you review this PR? |
|
Good day! FYI: In order to fix it, I used psycopg to connect to optuna's database after creating the storage object using optuna.storages.RDBStorage(), then I executed Hope this helps! |
Motivation
I have a postgresql 17.3 DB that I also wanted to use for the results of the trials ran through Optuna - potentially linking them with my own tables through a common id. Unfortunately this lead to an error:
operator does not exist: trialvaluetype = character varyingerror when using PostgreSQL 17.3 as explained in #6096.Description of the changes
This changes the SQLAlchemy case() structure in TrialModel.find_max/min_value_trial_id to directly compare the ENUM column with Python ENUM members, avoiding problematic VARCHAR casts on string literals.