Exclusion Constraint in Postgres doesn't display a custom message

Support
#1

Hello,

I was trying to apply a date range uniqueness constraint, I am using Postgres so i used a postgres exclsuion constraint, and i defined a custom message for the index in the messages.properties under the Web module -like other unique indexes i have custom messages for-, but unfortunately it’s not showing in the error that gets displayed, the constraint is defined as:
ALTER TABLE VP_WORK_SCHEDULE
ADD CONSTRAINT IDX_VP_WORK_SCHEDULE_UNQ
EXCLUDE USING gist
( COUNTRY_ID WITH =,
COALESCE(SECTOR_ID, ‘00000000-0000-0000-0000-000000000000’) WITH =,
daterange(START_DATE, COALESCE(END_DATE, ‘infinity’), ‘[]’) WITH &&
);

and the error message is:

PSQLException: ERROR: conflicting key value violates exclusion constraint “idx_vp_work_schedule_unq”
Detail: Key (country_id, COALESCE(sector_id, ‘00000000-0000-0000-0000-000000000000’::uuid), daterange(start_date, COALESCE(end_date, ‘infinity’::date), ‘[]’::text))=(f7ad4216-480b-5261-c1c2-a5cd97166a4a, 00000000-0000-0000-0000-000000000000, [1990-01-01,infinity)) conflicts with existing key (country_id, COALESCE(sector_id, ‘00000000-0000-0000-0000-000000000000’::uuid), daterange(start_date, COALESCE(end_date, ‘infinity’::date), ‘[]’::text))=(f7ad4216-480b-5261-c1c2-a5cd97166a4a, 00000000-0000-0000-0000-000000000000, [2000-01-01,infinity)).

the message defined is:
IDX_VP_WORK_SCHEDULE_UNQ = Work Schedule is Overlapping with another Work Schedule

#3

Hi Tarek,

Have you defined the cuba.uniqueConstraintViolationPattern property in your application?

By the way, could you please use triple back quotes for code blocks and error messages.

#4

Hi Olga,

Thanks for your reply, I have defined the cuba.uniqueConstraintViolationPattern property. but I am not sure that’s the problem as the other normal unique constraints have custom messages defined for them in the main message pack and displayed correctly. I think the problem lies in that the framework doesn’t recognize the constraint created as

ALTER TABLE VP_WORK_SCHEDULE
ADD CONSTRAINT IDX_VP_WORK_SCHEDULE_UNQ
EXCLUDE USING gist
( COUNTRY_ID WITH =,
COALESCE(SECTOR_ID, ‘00000000-0000-0000-0000-000000000000’) WITH =,
daterange(START_DATE, COALESCE(END_DATE, ‘infinity’), ‘[]’) WITH &&
);

as a unique constraint. as technically it’s not a unique constraint but an exclusion constraint.

#5

Hi Olga,

Any reply please. Thanks in advance. :slight_smile:

#6

What message have you defined? The standard one considers only unique constraints:
ERROR: duplicate key value violates unique constraint "(.+)"
Modify the pattern to cover exclude constraints, too.

#7

I already defined the following entry in both app.properties and web-app.properties:
cuba.uniqueConstraintViolationPattern = ERROR: duplicate key value violates unique constraint “(.+)”

but the exclusion constraint is not showing this error message, although other normal unique constraints are displaying the custom error message when violated.

#8

That’s what I’m talking about: you’ve defined the custom message for the unique constraint only. Modify it to add the exclusion constraint, too. (By the way, the standard PostreSQL message for it is ERROR: conflicting key value violates exclusion constraint).

#9

how to modify the pattern, also is there a way to put custom message for individual exclusion constraints in the main message pack

#10

HI,
If you want to specify separate messages for Exclude and Unique constraint violations you can extend UniqueConstraintViolationHandler and implement any logic you want there.

public class MyUniqueConstraintViolationHandler extends UniqueConstraintViolationHandler {


    @Override
    protected boolean doHandle(Throwable throwable, WindowManager windowManager) {

        String excludeStr = "ERROR: conflicting key value violates exclusion constraint";
        Pattern excludePattern = Pattern.compile(excludeStr);

        Pattern uniqPattern = clientConfig.getUniqueConstraintViolationPattern();

        if (excludePattern.matcher(throwable.toString()).find()){
            windowManager.showNotification("Exlude Constraint violation", Frame.NotificationType.ERROR);
            return true;
        }

        if (uniqPattern.matcher(throwable.toString()).find()){
            windowManager.showNotification("Unique Constraint violation", Frame.NotificationType.ERROR);
            return true;
        }


        return false;
    }

}

Then register the custom bean in web-spring.xml: add the following string (define the reference to your class):

<bean id="cuba_UniqueConstraintViolationHandler" class="com.company.sample.web.myhandlers.MyUniqueConstraintViolationHandler"/>
1 Like