Error Message not compatible with Postgres

[sanyamsinghal]

Jira Link: DB-1905 This bug is found while running the Sequelize(ORM tool) test suite.

In case of UniqueConstraintError, the error message returned to Sequelize by the yugabytedb is different than that of postgres.

Error Message returned by Yugabyte:

{
  length: 143,
  name: "error",
  severity: "ERROR",
  code: "23505",
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: "pg_yb_utils.c",
  line: "350",
  routine: "HandleYBStatusAtErrorLevel",
  sql: "CREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response \"users\", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_9cb7f7c7c5ae4302942ea1be1af0d583$ BEGIN INSERT INTO \"users\" (\"id\",\"objectId\",\"username\",\"createdAt\",\"updatedAt\") VALUES (DEFAULT,'asdasdasd','gottlieb','2021-07-14 13:56:26.837 +00:00','2021-07-14 13:56:26.837 +00:00') RETURNING * INTO response; EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_9cb7f7c7c5ae4302942ea1be1af0d583$ LANGUAGE plpgsql; SELECT (testfunc.response).\"id\", (testfunc.response).\"objectId\", (testfunc.response).\"username\", (testfunc.response).\"createdAt\", (testfunc.response).\"updatedAt\", testfunc.sequelize_caught_exception FROM pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();",
  parameters: undefined,
}

Error Message returned by the Postgres:

{
  sql: "CREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response \"users\", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_7ff2bca5c2b445dc85f671a09286e189$ BEGIN INSERT INTO \"users\" (\"id\",\"objectId\",\"username\",\"createdAt\",\"updatedAt\") VALUES (DEFAULT,'asdasdasd','gottlieb','2021-07-14 14:01:08.262 +00:00','2021-07-14 14:01:08.262 +00:00') RETURNING * INTO response; EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_7ff2bca5c2b445dc85f671a09286e189$ LANGUAGE plpgsql; SELECT (testfunc.response).\"id\", (testfunc.response).\"objectId\", (testfunc.response).\"username\", (testfunc.response).\"createdAt\", (testfunc.response).\"updatedAt\", testfunc.sequelize_caught_exception FROM pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();",
  parameters: undefined,
  code: "23505",
  detail: "Key (username)=(gottlieb) already exists.",
}

The difference in: detail: undefined and detail: "Key (username)=(gottlieb) already exists." between the yugabytedb and postgres is causing the problem for Sequelize since it expects that to be defined and processing it further in its source code.

[ddorian]

@sanyamsinghal can show link to steps on how to reproduce this ? What version of PostgreSQL did you compare it against ?

[sanyamsinghal]

@ddorian I am using PostgreSQL version 11.2 for this The SQLs corresponding to this test:

DROP TABLE IF EXISTS "users" CASCADE;
CREATE TABLE IF NOT EXISTS "users" ("id"   SERIAL , "objectId" VARCHAR(255) UNIQUE, "username" VARCHAR(255) UNIQUE, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
INSERT INTO "users" ("id","username","createdAt","updatedAt") VALUES (DEFAULT,'gottlieb','2021-07-14 17:30:35.63+00','2021-07-14 17:30:35.63+00') RETURNING "id","objectId","username","createdAt","updatedAt";
START TRANSACTION;
SELECT "id", "objectId", "username", "createdAt", "updatedAt" FROM "users" AS "user" WHERE "user"."objectId" = 'asdasdasd';
CREATE OR REPLACE FUNCTION pg_temp.testfunc(OUT response "users", OUT sequelize_caught_exception text) RETURNS RECORD AS $func_1a5ff1c00a6143298db286c7f84813ef$ BEGIN INSERT INTO "users" ("id","objectId","username","createdAt","updatedAt") VALUES (DEFAULT,'asdasdasd','gottlieb','2021-07-14 17:30:41.302 +00:00','2021-07-14 17:30:41.302 +00:00') RETURNING * INTO response; EXCEPTION WHEN unique_violation THEN GET STACKED DIAGNOSTICS sequelize_caught_exception = PG_EXCEPTION_DETAIL; END $func_1a5ff1c00a6143298db286c7f84813ef$ LANGUAGE plpgsql; SELECT (testfunc.response)."id", (testfunc.response)."objectId", (testfunc.response)."username", (testfunc.response)."createdAt", (testfunc.response)."updatedAt", testfunc.sequelize_caught_exception FROM pg_temp.testfunc(); DROP FUNCTION IF EXISTS pg_temp.testfunc();
COMMIT;

(sequelize test link: https://github.com/sequelize/sequelize/blob/56bb1d6ef9827f604d7bcef945abb7e213f2322d/test/integration/model/create.test.js#L72)