how to reproduce the exclusion constraint violation error and possible solutions

[manthan-jsharma]

Hi,

I'm currently working on #2126

i have opened the mathesar project in demo mode, but unaware where to run the sql query or is there any other way to implement it using the frontend @mathemancer ?

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE "Checkouts" DROP CONSTRAINT IF EXISTS sim_chck_excl;
ALTER TABLE "Checkouts"
  ADD CONSTRAINT sim_chck_excl
  EXCLUDE USING gist
    ("Item" WITH =, tsrange("Checkout Time", "Check In Time", '[]') WITH &&);

cc @dmos62

[dmos62]

To reproduce, you'll want to log into demo mode via the frontend, so that you have a demo session. Then connect to the demo database via pgadmin or psql and use that query to alter the library schema. Then try to use that schema via the frontend.

[manthan-jsharma]

hey @dmos62,

I'm able to reproduce the constraint violation error in "checkout" table.

'

Yes I agree, the error message is not user friendly, Below are some potential error messages that we can consider displaying:

 

1. The first solution is to display the same error message with enhanced formatting, achieved by incorporating appropriate spaces.

 

2. The second solution involves incorporating a customised error message specifically for cases when an exclusion constraint is triggered.

 
I believe that the 2nd solution is quite challenging to implement and may lack generality. In my opinion, I would prefer to implement the 1st solution, which involves displaying the error message with improved formatting and line breaks.

@dmos62 , @mathemancer , please let me if you have any other solutions to discuss and and also please confirm whether we should implement first solution or not ?

[dmos62]

Examine how we're currently handling violations of the unique constraint, for example. That will be the best starting point.

[manthan-jsharma]

hi @dmos62,

I have raised a pull request. Please review it: #3200

 

can you please confirm if we need any extra data in detail property in api response ?

 

I will write the test cases once the changes are confirmed