Postgres err: insufficient data left in message

I'm not sure how to approach debugging this issue For this query and inputs (in screenshot) I'm getting the error:

ExecutionErr: error during execution of the script:
db error: ERROR: insufficient data left in message

-- $1 user_guid
-- $2 network
-- $3 asset
-- $4 direction
-- $5 count_limit
-- $6 amount_limit
-- $7 amount_minimum
INSERT INTO stably.limit_user (
    user_guid,
    network,
    asset,
    direction,
    count_limit,
    amount_limit,
    amount_minimum
)
VALUES (
    $1::UUID,
    $2::VARCHAR,
    $3::VARCHAR,
    $4::VARCHAR,
    $5::INT,
    $6::NUMERIC,
    $7::NUMERIC
)
ON CONFLICT (user_guid, network, direction) 
DO UPDATE SET
    asset = EXCLUDED.asset,
    direction = EXCLUDED.direction,
    count_limit = EXCLUDED.count_limit,
    amount_limit = EXCLUDED.amount_limit,
    amount_minimum = EXCLUDED.amount_minimum;

-- $1 user_guid
-- $2 network
-- $3 asset
-- $4 direction
-- $5 count_limit
-- $6 amount_limit
-- $7 amount_minimum
INSERT INTO stably.limit_user (
    user_guid,
    network,
    asset,
    direction,
    count_limit,
    amount_limit,
    amount_minimum
)
VALUES (
    $1::UUID,
    $2::VARCHAR,
    $3::VARCHAR,
    $4::VARCHAR,
    $5::INT,
    $6::NUMERIC,
    $7::NUMERIC
)
ON CONFLICT (user_guid, network, direction) 
DO UPDATE SET
    asset = EXCLUDED.asset,
    direction = EXCLUDED.direction,
    count_limit = EXCLUDED.count_limit,
    amount_limit = EXCLUDED.amount_limit,
    amount_minimum = EXCLUDED.amount_minimum;

I've tried running this exact same query with the same arguments in another Postgres client (screenshot) but the query runs successfully. I've tried changing the casting syntax but there's too many permutations to try. Is there a way to get more context on the error somehow? There's nothing useful in the logs either

8 Replies

David ZhangOP•10mo ago

Here's a CREATE statement for anyone that wants to try to reproduce

CREATE TABLE
  stably.limit_user (
    user_guid uuid NOT NULL,
    network character varying(50) NOT NULL,
    asset character varying(50) NULL,
    direction character varying(10) NOT NULL,
    count_limit integer NULL,
    amount_limit numeric(36, 18) NULL,
    amount_minimum numeric(36, 18) NULL
  );

ALTER TABLE
  stably.limit_user
ADD
  CONSTRAINT limit_user_pkey PRIMARY KEY (direction)

CREATE TABLE
  stably.limit_user (
    user_guid uuid NOT NULL,
    network character varying(50) NOT NULL,
    asset character varying(50) NULL,
    direction character varying(10) NOT NULL,
    count_limit integer NULL,
    amount_limit numeric(36, 18) NULL,
    amount_minimum numeric(36, 18) NULL
  );

ALTER TABLE
  stably.limit_user
ADD
  CONSTRAINT limit_user_pkey PRIMARY KEY (direction)

rubenf•10mo ago

thanks, we will take a look @Hugo

Hugo•10mo ago

Hi @David Zhang i've tried just now and it works as expected so i can't reproduce. The error comes from the db and it could indicate that invalid data was sent What postgres version are you using?

David ZhangOP•10mo ago

15.3 using AWS Aurora I tried running the query using a DB client against the exact same DB and it worked, so I'm not sure if it's related to the DB, is there any other info I can help provide?

David ZhangOP•10mo ago

Actually I can no longer reproduce, when I run it with the same args it succeeds now, maybe there's state in the frontend that was being kept?

Hugo•10mo ago

my guess is that an unsupported invisible character has been passed in the parameters (see https://stackoverflow.com/questions/28308418/postgres-org-postgresql-util-psqlexception-error-insufficient-data-left-in-me) anyway let us know if it happens again

Stack Overflow

Postgres: org.postgresql.util.PSQLException: ERROR: insufficient da...

I read enough to know that this occurs when a string contains some characters that Postgres doesn't like. However, I cannot figure out if there is a way to validate strings before writing them. In

David ZhangOP•10mo ago

Interesting, I used the same method of inputing the values, the only thing I can think of is that I modified the input GUI to use enums for some of the strings, maybe some behavior related to when the enums are created initially vs when they're loaded later? But I'm not sure, just a guess, will update here if I ever run into this again

Hugo•10mo ago

Oh maybe, 👍 thanks