Blogmark

Odd PostgreSQL thing related to Prepared Statement / Parameter Type Inference I'm still trying to unravel.

I had the following bit of ActiveRecord query:

@tags =
  Tag.where("? is null or normalized_value ilike ?", normalized_query, "%#{normalized_query}%")
     .order(:normalized_value)
     .limit(10)

which short-circuits the filter (where) if normalized_query is nil. This worked in development when the normalized_query value was and wasn't present.

However, as soon as I shipped this to production, it was failing. I found the following error in the logs:

Caused by: PG::IndeterminateDatatype (ERROR:  could not determine data type of parameter $1)

I fixed it by rewriting the query to type cast to text which made postgres no longer unsure in production what the type of the parameter would be:

@tags =
  Tag.where("cast(? as text) is null or normalized_value ilike ?", normalized_query, "%#{normalized_query}%")
     .order(:normalized_value)
     .limit(10)

Yay, fixed. Buuut, I don't get why this worked in dev, but not production. My best guesses are either that there is some different level of type inference that production is configured for (seems unlikely) or that the prepared statement in production gets prepared with different type info. Perhaps different connections are getting different prepared statement versions which might lead to it being flaky?

This is weird. Any idea what could be going on here?

Interestingly, I found a typescript project that was reporting the EXACT same issue for the EXACT same type of query -- https://github.com/adelsz/pgtyped/issues/354