RE: [firebird-support] Prevent overlaping dates in concurent environment
Hi All!
Let’s see an another solution.
!!! This won’t work if you are using snapshot transactions. !!!
(If you need snapshot you have to solve the rollback problem with an another
solution.)
CREATE EXCEPTION EXCEPTION_EX 'Somewhere someting went terrible wrong…';
Table for checking the collisions:
CREATE TABLE MEETINGS_UQ (
ROOM INTEGER NOT NULL,
DATE_FROM DATE NOT NULL,
DATE_TODATE NOT NULL,
TR_NO INTEGER NOT NULL
);
ALTER TABLE MEETINGS_UQ ADD CONSTRAINT MEETINGS_UQ_PK PRIMARY KEY (ROOM,
DATE_FROM, DATE_TO);
CREATE DESCENDING INDEX MEETINGS_UQ_I1 ON MEETINGS_UQ (TR_NO);
CREATE OR ALTER TRIGGER MEETINGS_UQ_BI FOR MEETINGS_UQ
ACTIVE BEFORE INSERT POSITION 0
as
begin
-- Paste here the very sophisticated business logic
if (exists (select 1 from meetings_uq muq where muq.room = new.room)) then
begin
exception exception_ex 'Room is full!';
end
end
Table for inserted data:
CREATE TABLE MEETINGS (
ROOM INTEGER NOT NULL,
DATE_FROM DATE NOT NULL,
DATE_TODATE NOT NULL
);
CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
ACTIVE BEFORE INSERT POSITION 0
as
declare variable tr_no integer;
begin
tr_no = current_transaction;
in autonomous transaction do
begin
insert into MEETINGS_UQ (ROOM, DATE_FROM, DATE_TO, TR_NO)
values (new.room, new.date_from, new.date_to, :tr_no);
end
end
In case of rollback:
CREATE OR ALTER TRIGGER DATABASE_ON_TR_ROLLBACK
ACTIVE ON TRANSACTION ROLLBACK POSITION 1
as
declare variable tr_no integer;
begin
tr_no = current_transaction;
in autonomous transaction do
begin
delete from meetings_uq muq where muq.tr_no = :tr_no;
end
end
Test1:
isql1:
insert into meetings(room, date_from, date_to) values (1, 'TODAY', 'TODAY');
isql2:
insert into meetings(room, date_from, date_to) values (2, 'TODAY', 'TODAY');
isql3:
insert into meetings(room, date_from, date_to) values (1, 'TODAY', 'TODAY');
GL_EXCEPTION_EX.
Room is full!.
At trigger 'MEETINGS_UQ_BI' line: 8, col: 7
At trigger 'MEETINGS_BI' line: 9, col: 7.
Test2:
isql1:
insert into meetings(room, date_from, date_to) values (3, 'TODAY', 'TODAY');
rollback;
isql2:
insert into meetings(room, date_from, date_to) values (3, 'TODAY', 'TODAY');
commit;
András
From: firebird-support@yahoogroups.com
Sent: Wednesday, September 12, 2018 7:50 PM
To: firebird-support@yahoogroups.com
Subject: ODP: [firebird-support] Prevent overlaping dates in concurent
environment
Hi,
Solution is good only for dates and only in small range but considering date
with time or numbers you see that solution is realy limited.
As Tomasz say will be good to see „systematic solution”
And to check overlaping your sample is quite ok but is ineficient
|---R1---|
|---R2-|
|---R1---|
|---R2-|
|---R1---|
|---R2-|
|R1---|
|--R2--|
Better is check when dates do not overlap and do negation – only 2
possibilities
NOT (DATE_TO1mailto:firebird-supp...@yahoogroupscom]
Sent: Wednesday, 12 September, 2018 14:41
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Prevent overlaping dates in concurent
environment
Hi Tomasz!
Tested on 2.5.8, dialect 1:
CREATE TABLE RESERVED_DATE (
RES_DATE DATE NOT NULL
);
ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY
(RES_DATE);
CREATE TABLE MEETINGS (
DATE_FROM DATE NOT NULL,
DATE_TO DATE NOT NULL
);
CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
ACTIVE BEFORE INSERT POSITION 0
as
declare variable curr_date date;
begin
curr_date = new.date_from;
while (curr_date <= new.date_to) do
begin
insert into reserved_date (res_date) values (:curr_date);
curr_date = dateadd(1 day to curr_date);
end
end
run on first transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.01', '2018.09.10')
run parallel on secound transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.08', '2018.09.15')
violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table
"RESERVED_DATE".
Problematic key value is ("RES_DATE" = '8-SEP-2018').
At trigger 'MEETINGS_BI' line: 9, col: 7.
András
From: firebird-support@yahoogroups.com
Sent: Wednesday, September 12, 2018 1:59 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Prevent overlaping dates in concurent
environment
On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support]
wrote:
> Create a (reserved_dates) table with date field, and make that field unique..
> When a user inserts a date into the reservation table a trigger immadiate
> inserts this date to the reserved_dates table too. Then the unique key will
> stop secound insert instead of the first transaction is not commited.
This won't work. All dates may be different and the intervals may still
overlap.
Karol, that's an interesting issue and I'm really curious if there's a
clever solution.
So far I've ch
Re: [firebird-support] Prevent overlaping dates in concurent environment
On 12.09.2018 at 14:40, Omacht András aoma...@mve.hu [firebird-support] wrote: > [...] >while (curr_date <= new.date_to) do > > begin > >insert into reserved_date (res_date) values (:curr_date); > >curr_date = dateadd(1 day to curr_date); > > end > > end On a second thought, I don't like this idea after all. What if instead of dates you wanted to process timestamps (date + time)? Would you insert all possible timestamps from the interval with a 1 second step? Or a millisecond? There has to be a more elegant solution. Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Prevent overlaping dates in concurent environment
On 12.09.2018 at 15:04, Svein Erling Tysvær setys...@gmail.com [firebird-support] wrote: > Why wouldn't my suggestion work, Tomasz? When Karol inserts his first row > into his main table it will contain DATE_FROM 2018-09-10 and DATE_TO > 2018-09-20 and the trigger would insert 11 rows (one for each date) into my > suggested table. When the next row is inserted with DATE_FROM 2018-09-15 > and DATE_TO 2018-09-22, the trigger would fail inserting 8 rows due to the > unique constraint. > > The one place where I know my suggestion was incorrect, is that the trigger > needs to be AFTER UPDATE as well (I only said AFTER INSERT/DELETE), with a > change of dates, both DELETE and INSERT must be done. Other than that I > would expect it to work (although it does make things a bit slower than not > having this additional table). > > Set Yes, I've already admitted I didn't get the idea that you wanted to insert all dates from within the interval (and not just the boundaries), sorry for that. However, when you swap dates to timestamps (date + time) or just the intervals of real numbers, the whole idea breaks apart. Nonetheless, the problem itself doesn't seem very artificial (I can imagine many situations when this kind of constraint would be useful) and it would be strange if there was no systematic solution. In fact what is needed here is a check that gets executed on commit ;) cheers Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
RE: [firebird-support] Prevent overlaping dates in concurent environment
The practicality of entering each date in the range into a table depends on the
application. For short date ranges this should be fine, but very wide ranges
will have a bit of a performance impact on inserts.
Let’s say you want to book a resource for a date range. Then you could create
tables like this:
create table RESOURCE
(
ID integer,
NAME varchar(64)
);
alter table RESOURCE add constraint RESOURCE_PK primary key (ID) using index
RESOURCE_PK_IDX;
create table RESOURCE_BOOKING
(
ID integer,
RESOURCE_ID integer,
FROM_DAT DATE,
TO_DAT DATE,
);
alter table RESOURCE_BOOKING add constraint RESOURCE_BOOKING_PK primary key
(ID) using index RESOURCE_BOOKING_PK_IDX;
create index RESOURCE_BOOKING_RESOURCE_IDX on RESOURCE_BOOKING(RESOURCE_ID);
create table RESOURCE_BOOKING_DETAIL
(
BOOKING_ID integer,
RESOURCE_ID integer,
BOOKED_DAT DATE
);
alter table RESOURCE_BOOKING_DETAIL add constraint RESOURCE_BOOKING_DETAIL_UNQ
unique (RESOURCE_ID,BOOKED_DAT) using index RESOURCE_BOOKING_DETAIL_UNQ_IDX;
create index RESOURCE_BOOKING_DETAIL_IDX on RESOURCE_BOOKING_DETAIL(BOOKING_ID);
The RESOURCE_BOOKING_DETAIL_UNQ unique constraint will ensure that a RESOURCE
cannot be booked more than once for the same day. The associated index is also
useful for selecting the days a RESOURCE is booked for.
The RESOURCE_BOOKING_DETAIL_IDX is useful for when you want to delete a BOOKING
by its ID.
Two ranges may overlap in 4 ways and you have to check all 4 scenarios to
detect an overlap. There is no way to utilise standard DB constraints to
validate this.
I have written some PSQL SP to validate the 4 cases, but I normally do this in
my domain layer. This means there is no guarantee that overlap does not exist
in a concurrent environment.
The range overlap cases are as follows (not sure if the email spacing will be
preserved):
|---R1---|
|---R2-|
|---R1---|
|---R2-|
|---R1---|
|---R2-|
|R1---|
|--R2--|
From: firebird-support@yahoogroups.com
[mailto:firebird-supp...@yahoogroups..com]
Sent: Wednesday, 12 September, 2018 14:41
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Prevent overlaping dates in concurent
environment
Hi Tomasz!
Tested on 2.5.8, dialect 1:
CREATE TABLE RESERVED_DATE (
RES_DATE DATE NOT NULL
);
ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY
(RES_DATE);
CREATE TABLE MEETINGS (
DATE_FROM DATE NOT NULL,
DATE_TO DATE NOT NULL
);
CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
ACTIVE BEFORE INSERT POSITION 0
as
declare variable curr_date date;
begin
curr_date = new.date_from;
while (curr_date <= new.date_to) do
begin
insert into reserved_date (res_date) values (:curr_date);
curr_date = dateadd(1 day to curr_date);
end
end
run on first transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.01', '2018.09.10')
run parallel on secound transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.08', '2018.09.15')
violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table
"RESERVED_DATE".
Problematic key value is ("RES_DATE" = '8-SEP-2018').
At trigger 'MEETINGS_BI' line: 9, col: 7.
András
From: firebird-support@yahoogroups.com
Sent: Wednesday, September 12, 2018 1:59 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Prevent overlaping dates in concurent
environment
On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support]
wrote:
> Create a (reserved_dates) table with date field, and make that field unique..
> When a user inserts a date into the reservation table a trigger immadiate
> inserts this date to the reserved_dates table too. Then the unique key will
> stop secound insert instead of the first transaction is not commited.
This won't work. All dates may be different and the intervals may still
overlap.
Karol, that's an interesting issue and I'm really curious if there's a
clever solution.
So far I've checked the check (pun intended):
create table TST1 (
d1 timestamp,
d2 timestamp,
constraint no_overlap check (
not exists (
select * from TST1 t1
where exists (
select * from TST1 t2
where t1.d1 between t2.d1 and t2.d2
or t1.d2 between t2.d1 and t2.d2
)
)
)
);
insert into TST1 values('01.01.2018', '30.09.2018'); -- in transaction A
insert into TST1 values('01.02.2018', '30.10.2018'); -- in transaction B
-- commit A (no errors)
-- commit B (no errors)
and it doesn't work. You can still insert two overlapping pairs and both
transactions get committed without errors, resulting in overlapping
intervals being inserted. So, unless you change the transaction
isolation level (I always
Re: [firebird-support] Prevent overlaping dates in concurent environment
Why wouldn't my suggestion work, Tomasz? When Karol inserts his first row into his main table it will contain DATE_FROM 2018-09-10 and DATE_TO 2018-09-20 and the trigger would insert 11 rows (one for each date) into my suggested table. When the next row is inserted with DATE_FROM 2018-09-15 and DATE_TO 2018-09-22, the trigger would fail inserting 8 rows due to the unique constraint. The one place where I know my suggestion was incorrect, is that the trigger needs to be AFTER UPDATE as well (I only said AFTER INSERT/DELETE), with a change of dates, both DELETE and INSERT must be done. Other than that I would expect it to work (although it does make things a bit slower than not having this additional table). Set Den ons. 12. sep. 2018 kl. 14:49 skrev Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support] < firebird-support@yahoogroups.com>: > On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support] > wrote: > > Create a (reserved_dates) table with date field, and make that field > unique.. When a user inserts a date into the reservation table a trigger > immadiate inserts this date to the reserved_dates table too. Then the > unique key will stop secound insert instead of the first transaction is not > commited. > > This won't work. All dates may be different and the intervals may still > overlap. > >
Re: [firebird-support] Prevent overlaping dates in concurent environment
On 12.09.2018 at 14:40, Omacht András aoma...@mve.hu [firebird-support]
wrote:
> Tested on 2.5.8, dialect 1:
>
>
>
> CREATE TABLE RESERVED_DATE (
>
> RES_DATE DATE NOT NULL
>
> );
>
> ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY
> (RES_DATE);
>
>
>
> CREATE TABLE MEETINGS (
>
> DATE_FROM DATE NOT NULL,
>
> DATE_TODATE NOT NULL
>
> );
>
>
>
> CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
>
> ACTIVE BEFORE INSERT POSITION 0
>
> as
>
> declare variable curr_date date;
>
> begin
>
>curr_date = new.date_from;
>
>while (curr_date <= new.date_to) do
>
> begin
>
>insert into reserved_date (res_date) values (:curr_date);
>
>curr_date = dateadd(1 day to curr_date);
>
> end
>
> end
>
>
>
> run on first transaction:
>
>
>
> insert into MEETINGS (DATE_FROM, DATE_TO)
>
> values ('2018.09.01', '2018.09.10')
>
>
>
> run parallel on secound transaction:
>
> insert into MEETINGS (DATE_FROM, DATE_TO)
>
> values ('2018.09.08', '2018.09.15')
>
>
>
> violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table
> "RESERVED_DATE".
>
> Problematic key value is ("RES_DATE" = '8-SEP-2018').
>
> At trigger 'MEETINGS_BI' line: 9, col: 7.
OK, what I didn't get was that you intended to put _all_ subsequent
dates from within an interval to the auxiliary table.
Well, that way it should work. And I wouldn't be too worried about the
size of the table, unless the intervals would be hundreds of years long.
Tomasz
--
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__
RE: [firebird-support] Prevent overlaping dates in concurent environment
Hi Tomasz!
Tested on 2.5.8, dialect 1:
CREATE TABLE RESERVED_DATE (
RES_DATE DATE NOT NULL
);
ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY
(RES_DATE);
CREATE TABLE MEETINGS (
DATE_FROM DATE NOT NULL,
DATE_TODATE NOT NULL
);
CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
ACTIVE BEFORE INSERT POSITION 0
as
declare variable curr_date date;
begin
curr_date = new.date_from;
while (curr_date <= new.date_to) do
begin
insert into reserved_date (res_date) values (:curr_date);
curr_date = dateadd(1 day to curr_date);
end
end
run on first transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.01', '2018.09.10')
run parallel on secound transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.08', '2018.09.15')
violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table
"RESERVED_DATE".
Problematic key value is ("RES_DATE" = '8-SEP-2018').
At trigger 'MEETINGS_BI' line: 9, col: 7.
András
From: firebird-support@yahoogroups.com
Sent: Wednesday, September 12, 2018 1:59 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Prevent overlaping dates in concurent
environment
On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support]
wrote:
> Create a (reserved_dates) table with date field, and make that field unique..
> When a user inserts a date into the reservation table a trigger immadiate
> inserts this date to the reserved_dates table too. Then the unique key will
> stop secound insert instead of the first transaction is not commited.
This won't work. All dates may be different and the intervals may still
overlap.
Karol, that's an interesting issue and I'm really curious if there's a
clever solution.
So far I've checked the check (pun intended):
create table TST1 (
d1 timestamp,
d2 timestamp,
constraint no_overlap check (
not exists (
select * from TST1 t1
where exists (
select * from TST1 t2
where t1.d1 between t2.d1 and t2.d2
or t1.d2 between t2.d1 and t2.d2
)
)
)
);
insert into TST1 values('01.01.2018', '30.09.2018'); -- in transaction A
insert into TST1 values('01.02.2018', '30.10.2018'); -- in transaction B
-- commit A (no errors)
-- commit B (no errors)
and it doesn't work. You can still insert two overlapping pairs and both
transactions get committed without errors, resulting in overlapping
intervals being inserted. So, unless you change the transaction
isolation level (I always use read committed), I don't have more ideas
at the moment.
have a good one
Tomasz
--
__--==--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==--__
__ Information from ESET Mail Security, version of virus signature
database 18037 (20180912) __
The message was checked by ESET Mail Security.
http://www.eset.com
[Non-text portions of this message have been removed]
RE: [firebird-support] Prevent overlaping dates in concurent environment
Hi, but this require to have table with all dates. 365 rows per year per customer. This is not possible solution. This sample with customers was only simplification. But extend this sample to meeting time date with time. This table will be too huge... Regards,Karol Bieniaszewski null
Re: [firebird-support] Prevent overlaping dates in concurent environment
What about a separate table for dates with one row per date and a UNIQUE constraint that you populate from a (AFTER INSERT/DELETE) trigger on your real table? HTH, Set Den ons. 12. sep. 2018 kl. 11:49 skrev liviuslivius liviusliv...@poczta.onet.pl [firebird-support] < firebird-support@yahoogroups.com>: > > > Hi, > > is there a good way to prevent overlaping dates to be inserted to the > table? > > You know DATE_FROM, DATE_TO and you can have > 2018-09-10 to 2018-09-20 > and > 2018-09-15 to 2018-09-22 > they ovelap on 15,16,17,18,19 and 20 > > > No my real sample but simple to understand is: > > customer and meeting planning. > Currently we lock customer record and only one user can do > update/insert/delete operation at a time. > But what if we need multiple users insert/update data in the same time? > How to prevent inserting overlaping dates then? > > I know that we can write trigger and check overlap there but what if two > users do insert and still not commited transaction? > User1 do insert, trigger is fired and check that no overlaping dates > exists - and this transaction is not commited yet > User2 do insert, trigger is fired and check that no overlaping dates > exists because user 1 still not commit transaction and user2 transaction do > not see recrods from user1. > And at this point, if user1 commit and user2 commit, then overlaping dates > can be inserted into database. > > regards, > Karol Bieniaszewski > > > >
Re: [firebird-support] Prevent overlaping dates in concurent environment
On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support]
wrote:
> Create a (reserved_dates) table with date field, and make that field unique..
> When a user inserts a date into the reservation table a trigger immadiate
> inserts this date to the reserved_dates table too. Then the unique key will
> stop secound insert instead of the first transaction is not commited.
This won't work. All dates may be different and the intervals may still
overlap.
Karol, that's an interesting issue and I'm really curious if there's a
clever solution.
So far I've checked the check (pun intended):
create table TST1 (
d1 timestamp,
d2 timestamp,
constraint no_overlap check (
not exists (
select * from TST1 t1
where exists (
select * from TST1 t2
where t1.d1 between t2.d1 and t2.d2
or t1.d2 between t2.d1 and t2.d2
)
)
)
);
insert into TST1 values('01.01.2018', '30.09.2018'); -- in transaction A
insert into TST1 values('01.02.2018', '30.10.2018'); -- in transaction B
-- commit A (no errors)
-- commit B (no errors)
and it doesn't work. You can still insert two overlapping pairs and both
transactions get committed without errors, resulting in overlapping
intervals being inserted. So, unless you change the transaction
isolation level (I always use read committed), I don't have more ideas
at the moment.
have a good one
Tomasz
--
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__
RE: [firebird-support] Prevent overlaping dates in concurent environment
Hi Karol! Create a (reserved_dates) table with date field, and make that field unique.. When a user inserts a date into the reservation table a trigger immadiate inserts this date to the reserved_dates table too. Then the unique key will stop secound insert instead of the first transaction is not commited. András From: firebird-support@yahoogroups.com Sent: Wednesday, September 12, 2018 11:45 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] Prevent overlaping dates in concurent environment Hi, is there a good way to prevent overlaping dates to be inserted to the table? You know DATE_FROM, DATE_TO and you can have 2018-09-10 to 2018-09-20 and 2018-09-15 to 2018-09-22 they ovelap on 15,16,17,18,19 and 20 No my real sample but simple to understand is: customer and meeting planning. Currently we lock customer record and only one user can do update/insert/delete operation at a time. But what if we need multiple users insert/update data in the same time? How to prevent inserting overlaping dates then? I know that we can write trigger and check overlap there but what if two users do insert and still not commited transaction? User1 do insert, trigger is fired and check that no overlaping dates exists - and this transaction is not commited yet User2 do insert, trigger is fired and check that no overlaping dates exists because user 1 still not commit transaction and user2 transaction do not see recrods from user1. And at this point, if user1 commit and user2 commit, then overlaping dates can be inserted into database. regards, Karol Bieniaszewski __ Information from ESET Mail Security, version of virus signature database 18037 (20180912) __ The message was checked by ESET Mail Security. http://www.eset.com [Non-text portions of this message have been removed]
