1

I have a table keep track of hotel reservations with exclusion constraint like below.

Currently, I allow guests to update their reservations. So if guest_id 1 change his reservation from 3 day reservation to a single day from 2010-01-03, 2010-01-03, postgres will block the update due to the overlap constraint if i ran the this update statement:

update reservation set from_ts = '2021-01-03 00:00:00', to_ts='2021-01-10 23:59:00', during = '[2021-01-03 00:00:00, 2021-01-10 23:59:00]' where id = 1

How do you go about allowing this update then? Do you have to keep the reservation id the same, and delete the others?

** note: I'm actually storing a day per row as I have other attributes to keep track at per day basis **

Table: reservation
 id | room |  from_ts            |   to_ts             | guest_id
----+------+---------------------+---------------------+------------
  1 |  101 | 2010-01-01 00:00:00 | 2010-01-01 23:59:00 | 1
  2 |  101 | 2010-01-02 00:00:00 | 2010-01-02 23:59:00 | 1
  3 |  101 | 2010-01-03 00:00:00 | 2010-01-03 23:59:00 | 1

CREATE TABLE reservation (
    id int,
    guest_id int,
    room int,
    from_ts timestamp without time zone,
    to_ts timestamp without time zone,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);
-- bootstrap to test the problem
INSERT INTO reservation ( id, guest_id, room, from_ts, to_ts, during ) VALUES ( 1, 1, 101, '2021-01-01 00:00:00', '2021-01-01 23:59:00', '[2021-01-01 00:00:00, 2021-01-01 23:59:00]');
INSERT INTO reservation ( id, guest_id, room, from_ts, to_ts, during ) VALUES ( 2, 1, 101, '2021-01-02 00:00:00', '2021-01-02 23:59:00', '[2021-01-02 00:00:00, 2021-01-02 23:59:00]' );
INSERT INTO reservation ( id, guest_id, room, from_ts, to_ts, during ) VALUES ( 3, 1, 101, '2021-01-03 00:00:00', '2021-01-03 23:59:00', '[2021-01-03 00:00:00, 2021-01-03 23:59:00]' );

-- update statement will fail after you run the insert statements
update reservation set from_ts = '2021-01-03 00:00:00', to_ts='2021-01-10 23:59:00', during = '[2021-01-03 00:00:00, 2021-01-10 23:59:00]' where id = 1
weiklr
  • 159
  • 1
  • 7
  • 1
    No, it won't. Just make sure you use `UPDATE`, not `INSERT`. – Laurenz Albe Sep 28 '21 at 10:38
  • i see. is it advisable to do this to include guest_id in gist like this? EXCLUDE USING GIST (guest_id with <>, room WITH =, tsrange(from_ts,to_ts) WITH &&) – weiklr Sep 28 '21 at 11:16
  • No, don't add the guest. – Laurenz Albe Sep 28 '21 at 11:23
  • i updated my question to resemble more closely how i'm storing the data. – weiklr Sep 28 '21 at 16:08
  • I see no column `during` in your table. – Laurenz Albe Sep 28 '21 at 16:10
  • i ran out of space. during is a tsrange made up of from_ts and to_ts. the value in the update statement is an example – weiklr Sep 28 '21 at 16:53
  • Why do you have that redundancy in your data? Anyway, I cannot reproduce your error. Perhaps a reproducible test case would help. – Laurenz Albe Sep 28 '21 at 21:05
  • 1
    i updated the question with insert statements that you can run to reproduce it. the create table statement should work. i'm still exploring this actually. I thought it's explicit that way. do you recommend to have a explicit field like "during" to be used with exclusion constraints? – weiklr Sep 29 '21 at 07:20

1 Answers1

1

You can solve that problem by using a different exclusion constraint instead of the one you created:

ALTER TABLE reservation ADD EXCLUDE USING gist (
   room WITH =,
   guest_id WITH <>,
   tsrange(from_ts, to_ts, '[]') WITH &&
);

That will exclude two entries where the room is equal and the timstamp ranges overlap and the reservation is for different guests.

Note that I used an expression rather than during. It is a good idea to avoid redundancy in database design. You can of course also keep the duration and do away with from_ts and to_ts.

Also note how awkward to_ts is, ending at 23:59:00. You may instead choose intervals that are open at the upper end:

SELECT tsrange('2021-01-02 00:00:00', '2021-01-03 00:00:00');

                    tsrange                    
═══════════════════════════════════════════════
 ["2021-01-02 00:00:00","2021-01-03 00:00:00")
(1 row)

This won't overlap with a range that starts with 2021-01-03 00:00:00.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • i tried including guest_id into the gist too. It works, but when i tried to migrate legacy data into the table, which might contain overlaps, i noticed that it runs pretty slow compared to the gist without guest_id. Do you happen to know this is the case? – weiklr Sep 29 '21 at 07:50
  • I don't know, but `<>` usually isn't very selective. You could experiment with putting that condition last in the index; I don't know if that will make a lot of difference. – Laurenz Albe Sep 29 '21 at 08:04
  • Thanks! It's already in the last of the index actually. I think it's just not very selective like you said. probably stick to excluding it from the gist and handle it in code i guess. – weiklr Sep 29 '21 at 08:08
  • Isn't that just a one-time process, migrating the data? Just let it take its time. It shouldn't bother you after that. – Laurenz Albe Sep 29 '21 at 09:43