0

I encountered an issue with casting in my application while using PostgreSQL. Let me provide a specific example to illustrate the problem.

When I execute the following query:

UPDATE intent SET status = ? WHERE id = ?;

It works without any problems on my AWS server. However, when I attempt to run it on my local machine, I encounter a casting error:

nested exception is org.postgresql.util.PSQLException: ERROR: column "status" is of type intent_status but expression is of type character varying Hint: You will need to rewrite or cast the expression

My main question is why does the query require casting only on my local machine, considering that both environments have the same PostgreSQL version?

Current PostgreSQL version:

PostgreSQL 12.14 (Ubuntu 12.14-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

Table "platform.intent"

----------------------------+-------------------------------+-----------+----------+---------------------------------------------
 id                         | integer                       |           | not null | nextval('platform.intent_id_seq'::regclass)
 org_id                     | integer                       |           | not null | 
 bot_id                     | integer                       |           | not null | 
 added_by                   | integer                       |           | not null | 
 added_date                 | timestamp without time zone   |           | not null | now()
 status                     | platform.intent_status        |           | not null | 'ACTIVE'::platform.intent_status
 flow                       | jsonb                         |           | not null | '{}'::jsonb
 nlu_label_id               | integer                       |           |          | 
 webhook_settings           | jsonb                         |           | not null | '{}'::jsonb
 matrix_settings            | jsonb                         |           | not null | '[]'::jsonb
 type                       | platform.intent_type          |           | not null | 'FLOW'::platform.intent_type
 expiry_settings            | jsonb                         |           | not null | '{}'::jsonb
 closed_cycle_settings      | jsonb                         |           | not null | '{}'::jsonb
 event_logging_level        | platform.intent_logging_level |           | not null | 'ENTER_EXIT'::platform.intent_logging_level
 failure_action             | jsonb                         |           |          | '{}'::jsonb
 has_smart_search           | boolean                       |           | not null | false
 switching_settings         | jsonb                         |           | not null | '{}'::jsonb
 queuing_settings           | jsonb                         |           | not null | '{}'::jsonb
 trigger_condition_settings | jsonb                         |           | not null | '{}'::jsonb
Indexes:
    "intent_pkey" PRIMARY KEY, btree (id)
    "id_bot_id" btree (bot_id)
    "idx_bot_org_id" btree (org_id, bot_id)
    "idx_intent_type" btree (type)
    "idx_org_id" btree (org_id)
    "intent_expr_idx" btree ((((flow -> 'arabot_flow'::text) -> 'data'::text) ->> 'name'::text))
Foreign-key constraints:
    "fk_botid" FOREIGN KEY (bot_id) REFERENCES platform.bot(id) ON DELETE CASCADE NOT VALID
    "fk_orgid" FOREIGN KEY (org_id) REFERENCES platform.organization(id) ON DELETE CASCADE

Java Code (JdbcTemplate)

   public void updateEntity(Intent intent) {

        try {
            jdbcTemplate.update("update intent set status = ?, flow = to_json(?::json), webhook_settings = to_json(?::json) , expiry_settings = to_json(?::json)," +
                            " closed_cycle_settings = to_json(?::json) , event_logging_level = ?::intent_logging_level ,  failure_action = to_json(?::json), switching_settings = to_json(?::json), queuing_settings = to_json(?::json), trigger_condition_settings = ?::jsonb " +
                            "  where id = ?",
                    intent.getStatus().name(),
                    intent.getFlow().toString(),
                    new FlowReader().writeNode(intent.getWebhookSettings()),
                    new FlowReader().writeNode(intent.getExpirySettings()),
                    new FlowReader().writeNode(intent.getClosedCycleSettings()),
                    intent.getEventLoggingLevel().name(),
                    new FlowReader().writeNode(intent.getFailureAction()),
                    new FlowReader().writeNode(intent.getSwitchingSettings()),
                    new FlowReader().writeNode(intent.getQueuingSettings()),
                    new FlowReader().writeNode(intent.getTriggerConditionSettings()),
                    intent.getId());
        } catch (JsonProcessingException e) {
            throw new ServerErrorException(String.format("Failed to update intent due to error while parsing webhook settings: %s ", e.getMessage()), e);
        }
    }
DanTe
  • 115
  • 6
  • Is the DDL for the table `intent` the same ? (When you do not know how to create that, see: [How to generate the "create table" sql statement for an existing table in postgreSQL](https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr)) – Luuk May 16 '23 at 13:07
  • @Luuk Yes, they are identical! – DanTe May 16 '23 at 13:12
  • Can you create this in a [DBFIDDLE](https://dbfiddle.uk/btGcOH30)? Or will it work OK when you try that? If it's working OK in a DBFIDDLE, then the data might be the cause of this issue (but not enough info in the question to be sure .... ) – Luuk May 16 '23 at 13:18
  • **To your question add**: 1) The table definition. 2) The Java code you are running that causes the error. 3) Also are you running the query the same way locally as remotely? – Adrian Klaver May 16 '23 at 14:48
  • I would think this is a client issue. What is the client? Are you running the same code on the same machine in the same manner each time, changing only the host value in the connection string? – jjanes May 16 '23 at 15:39
  • @AdrianKlaver, I have updated it. And yes, I am using API requests to trigger all the DAO methods, so it will trigger the same query in both environments. – DanTe May 17 '23 at 07:26
  • I am not a Java programmer but I would say this `intent.getStatus().name()` is returning something different locally(a string) versus remotely(the type). Can you trace/see what that value is in each case before it is submitted to the query? – Adrian Klaver May 17 '23 at 15:10
  • You do cast the event_logging_level (in your update: `..., event_logging_level = ?::intent_logging_level, ...`), but like the Hind in the error says: "You will need to rewrite or cast the expression", you might need to so `SET status = ?::intent_status, ...` – Luuk May 18 '23 at 08:29
  • @Luuk Yeah, this will definitely solve the problem. However, I'm just trying to figure out why it's already working fine on AWS. I thought it might be related to the Postgres configuration or something like that. – DanTe May 18 '23 at 09:18

0 Answers0