SQL query started mysteriously failing

💬 Queries and Resources
, , ,
1
  1. My goal: I need this UPDATE query to run consistently on my PostgreSQL DB.
  2. Issue: This UPDATE query used to run consistently but suddenly stopped working for some apps. The query is below.
  3. Steps I've taken to troubleshoot: Tried casting types and made sure every value was defined, etc. The exact same query on the same table/DB works for some modules but not others.
  4. Additional info: (Cloud or Self-hosted, Screenshots)
UPDATE main.task
  SET status = 'completed', 
  status_updated_at=NOW(),
  result = ((COALESCE(result::jsonb, '{}'::jsonb) || jsonb_build_object('response_creator', '{{current_user.email}}'))::json)
    WHERE id = '{{task_id.value}}'

task_id.value (UUID) and current_user.email (string) are defined. The error I get is:

"invalid input syntax for type uuid: "$2""

However, I get the same error if I try to cast it to UUID like so:

WHERE id = '{{task_id.value}}'::uuid

and I get a different error like so:

WHERE id = {{task_queue_item_id.value}}::uuid

the error is: "could not determine data type of parameter $1"

It’s very strange to me that this exact same query works on some modules and not others. It makes me think that there’s something wrong with the variable binding here. Would appreciate some help.

4

Hi @boston_rob,

Thanks for reaching out about this!

:thinking: I haven’t found a Retool bug with variable binding that would explain this behavior yet. The inconsistency makes this tricky, but in my testing, WHERE id = {{task_queue_item_id.value}}::uuid works as expected. I rarely add quotes to variables (like '{{task_id.value}}') since Retool already handles that with the variable.

I’m wondering if the cases where you’re running into errors have some different edge case that I’m not testing. What is task_id (transformer, component value, etc)? Are you writing the queries directly in the same app as task_id, or are you also using the Query Library, Modules, etc? Hopefully, with that information, we can narrow this down a bit further :crossed_fingers: Another option is to DM me an export of your app so that I can ensure I am testing the exact same configuration

Thanks!

Tess