RE: [firebird-support] Is it a Trigger Problem?

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Zoran!

I suggest you to make unique key on
"T_ORDERCUSTOMER".OrderId
and
T_ORDERVEHICLE".OrderId

and you will get an exception when the secound row is inserted into the tables.

Maybe this helps to find out where is the problem.

András

From: firebird-support@yahoogroups.com 
Sent: Wednesday, July 17, 2019 4:47 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Is it a Trigger Problem?


This might sound impossible...

I have one master and two detail tables. Relation is defined thru Foreign Key 
and cascade delete. When I insert a master row, the master table trigger 
inserts one empty detail row (only master key column populated)) in each of the 
two detail tables. So each master row has only one detail row in each of two 
detail tables and that doesn't change during the life of a master row.

All works well. Very, very seldom my client gets two detail rows for the same 
master row. Sometimes it is in detail 1 table, sometimes it is in detail 2 
table. There are 5 users updating and inserting new rows 8 hours a day. 
Database size is small (about 10,000 rows in the main table).

Application code does not insert any rows into detail tables, just updates 
them. There are several triggers in the database, but the only one that touches 
detail tables is the one in the master table.

Now crazy question. How is this possible? Is there any special circumstance in 
FB which might cause this?

Here is part of the trigger in master table:

  if (INSERTING) then
  begin
insert into "T_ORDERCUSTOMER" (OrderId) values (new.Id);
insert into "T_ORDERVEHICLE" (OrderId) values (new.Id);


Server: FB 3.4, Win 10 Pro, 12GB RAM

Workstations: Win 10 Home and Pro, 4-8GB RAM
Development: Delphi 10.3.1, Win 10 Ent, 32GB RAM

Tnx.



__ Information from ESET Mail Security, version of virus signature 
database 19701 (20190717) __

The message was checked by ESET Mail Security.
http://www.eset.com



__ Information from ESET Mail Security, version of virus signature  
database 19705 (20190718) __

The message was checked by ESET Mail Security.
http://www.eset.com



[Non-text portions of this message have been removed]







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9

[Omacht András aoma...@mve.hu [firebird-support]]

Hi!

I sent this e-mail two times at saturday, but yahoo didn’t deliver it.
I try it again:


Hi Dany!

Try with CTE:

with procs as (
select distinct "PROC"
   from "MOVI"
   where "MOVI"."TIPO" in ('1','A','B')
   and ("MOVI"."FECH" between 
'20190301' and '20190412')
   and "MOVI"."MIEM" = '15JMS45D7A'
   and "MOVI"."HECH" = 'N' order by 
"PROC")
select proc.proc
  from procs
left outer join proc on proc.proc = procs.proc
  where proc.proc is not null



Maybe it will be fast with inner join too:

with procs as (
select distinct "PROC"
   from "MOVI"
   where "MOVI"."TIPO" in ('1','A','B')
   and ("MOVI"."FECH" between 
'20190301' and '20190412')
   and "MOVI"."MIEM" = '15JMS45D7A'
   and "MOVI"."HECH" = 'N' order by 
"PROC")
select proc.proc
  from procs
inner join proc on proc.proc = procs.proc




András


From: firebird-support@yahoogroups.com 
Sent: Monday, May 27, 2019 4:25 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Query that was very fast under the oldest 2.5 
versions are very slow under 2.5.8 or 2.5.9



Hello Karol; here you have both query plans, excecuted over same FDB file, on 
the same machine:

-
FIREBIRD 2.5.0:
-

Preparing query: select 
"PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC"."OBSE","TPRO"."DSCR"
 as 
"D_TPRO","PROC"."OJUD","PROC"."INST","PROC"."EXP1","PROC"."EXP2","PROC"."EXP3","PROC"."EXP4"
 from "PROC" left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO" left outer 
join "OJUD" o1 on "PROC"."OJUD"=o1."OJUD" left outer join "ZONA& quot; o2 on 
o1."ZONA"=o2."ZONA" where ((("PROC"."PROC" in (select distinct "PROC" from 
"MOVI" where "MOVI"."TIPO" in ('1','A','B') and ("MOVI"."FECH" between 
'20190301' and '20190412') and "MOVI"."MIEM" = '15JMS45D7A' and "MOVI"."HECH" = 
'N' order by "PROC" order by 
o2."ORDE",o1."ORDE",lower("PROC"."ACTO"),lower("PROC"."DEMA")
Prepare time: 1.498s
Field #01: PROC.PROC Alias:PROC Type:STRING(10)
Field #02: PROC.CARP Alias:CARP Type:STRING(18)
Field #03: PROC.ACTO Alias:ACTO Type:STRING(200)
Field #04: PROC.DEMA Alias:DEMA Type:STRING(200)
Field #05: PROC.OBSE Alias:OBSE Type:STRING(120)
Field #06: TPRO.DSCR Alias:D_TPRO Type:STRING(120)
Fie ld #07: PROC.OJUD Alias:OJUD Type:STRING(10)
Field #08: PROC.INST Alias:INST Type:STRING(1)
Field #09: PROC.EXP1 Alias:EXP1 Type:STRING(18)
Field #10: PROC.EXP2 Alias:EXP2 Type:STRING(18)
Field #11: PROC.EXP3 Alias:EXP3 Type:STRING(18)
Field #12: PROC.EXP4 Alias:EXP4 Type:STRING(18)
PLAN SORT ((MOVI INDEX (MOVI_PROC, MOVI_FECH)))
PLAN SORT (JOIN (JOIN (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK)), O1 INDEX 
(OJUD_PK)), O2 INDEX (ZONA_PK)))


Executing...
Done.
3051054 fetches, 8 marks, 17180 reads, 8 writes.
0 inserts, 0 updates, 0 deletes, 148293 index, 52648 seq.
Delta memory: 79112 bytes.
Total execution time: 0:01:34 (hh:mm:ss)
Script execution finished.

-
FIREBIRD 2.5.8:
-- ---

Preparing query: select 
"PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC"."OBSE","TPRO"."DSCR"
 as 
"D_TPRO","PROC"."OJUD","PROC"."INST","PROC"."EXP1","PROC"."EXP2","PROC"."EXP3","PROC"."EXP4"
 from "PROC" left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO" left outer 
join "OJUD" o1 on "PROC"."OJUD"=o1."OJUD" left outer join "ZONA" o2 on 
o1."ZONA"=o2."ZONA" where ((("PROC"."PROC" in (select distinct "PROC" from 
"MOVI" where "MOVI"."TIPO" in ('1','A','B') and ("MOVI". uot;FECH" between 
'20190301' and '20190412') and "MOVI"."MIEM" = '15JMS45D7A' and "MOVI"."HECH" = 
'N' order by "PROC" order by 
o2."ORDE",o1."ORDE",lower("PROC"."ACTO"),lower("PROC"."DEMA")
Prepare time: 0.031s
Field #01: PROC.PROC Alias:PROC Type:STRING(10)
Field #02: PROC.CARP Alias:CARP Type:STRING(18)
Field #03: PROC.ACTO Alias:ACTO Type:STRING(200)
Field #04: PROC.DEMA Alias:DEMA Type:STRING(200)
Field #05: PROC.OBSE Alias:OBSE Type:STRING(120)
Field #06: TPRO.DSCR Alias:D_TPRO Type:STRING(120)
Field #07: PROC.OJUD Alias:OJUD Type:STRING(10)
Field #08: PROC.INST Alias:INST Type:STRING(1)
Field #09: PROC.EXP1 Alias:EXP1 Type:STRING(18)
Field #10: PROC.EXP2 Alias:EXP2 Type:STRING(18)
Field #11: PROC.EXP3 Alias:EXP3 Type:STRING(18)
Field #12: PROC.EXP4 Alias:EXP4 Type:STRING(18)
PLAN SORT ((MOVI INDEX (MOVI_PROC)))
PLAN SORT (JOIN (JOIN (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK)), O1 INDEX 
(OJUD_PK)), O2 INDEX (ZONA_PK)))


Executing...
Done.
2458464 fetches, 8 marks, 843116 reads, 8 writes.
0 inserts, 0 updates, 0 

RE: [firebird-support] Query optimization when using sub query with in operator

[Omacht András aoma...@mve.hu [firebird-support]]

Hi!

Try this:

with ids as (select '123' id from RDB$DATABASE )
select a.*
  from AnyTable a
inner join ids i on i.id = a.id

András


From: firebird-support@yahoogroups.com 
Sent: Friday, March 1, 2019 1:11 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Query optimization when using sub query with in 
operator



The following problem applies to Firebird 3.0. Cant test right now on
older versions.

Suppose we have a table called AnyTable with column ID as primary key.

The query

select * from AnyTable where ID = '123'

produce the following plan...

PLAN (AnyTable INDEX (PK_AnyTable))

Now a second query, semantically identical to the first...

select * from AnyTable where ID in ( '123' )

will produce the same plan:

PLAN (AnyTable INDEX (PK_AnyTable))

However that third query, also semantically identical to the first...

select * from AnyTable where ID in ( select '123' from RDB$DATABASE )

will produce a really bad plan:

PLAN (RDB$DATABASE NATURAL)
PLAN (AnyTable NATURAL)

If I try to force the primary key index, an error occurs:

SQL Message : -284
index  cannot be used in the specified plan

Are there any way to force index usage on a situation like that?

Ivan Cruz.



__ Information from ESET Mail Security, version of virus signature 
database 18952 (20190228) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

Re: [firebird-support] Re: optimal free disk space

[Omacht András aoma...@mve.hu [firebird-support]]

Hi!

Read only, read commited transactions can be long running, but read only 
snapshots can't.

--
András


 Eredeti üzenet 
Feladó: "shg_siste...@yahoo.com.ar [firebird-support]" 

Dátum: 2019. 02. 25. 17:54 (GMT+01:00)
Címzett: firebird-support@yahoogroups.com
Tárgy: [firebird-support] Re: optimal free disk space



Thanks! Now I see why Dimitry said it was a problem of badly designed program. 
I always try to be very carefull with transactions, but clearly I'm missing 
something here.

The problem are the read/writte transactions, right? No problem with the read 
only ones?

Is it possible to know which tables where updated inside a particular 
transaction?

thanks a million!!




__ Information from ESET Mail Security, version of virus signature 
database 18933 (20190225) __

The message was checked by ESET Mail Security.
http://www.eset.com



__ Information from ESET Mail Security, version of virus signature 
database 18933 (20190225) __

The message was checked by ESET Mail Security.
http://www.eset.com

Válasz: [firebird-support] Consecutive values from different fields

[Omacht András aoma...@mve.hu [firebird-support]]

Edit previous line, add semicolon at the end:

wd1 = wd2;


András


 Eredeti üzenet 
Feladó: "'Autoneer' myauton...@gmail.com [firebird-support]" 

Dátum: 2019. 01. 27. 9:04 (GMT+01:00)
Címzett: firebird-support@yahoogroups.com
Tárgy: RE: [firebird-support] Consecutive values from different fields


Hi Set

Thank you much appreciated, I would agree with Lester’s approach however this 
an inherited DB.

I tried your SQL and get the error below.

“Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 28, column 5.
end.”


execute block ( drivernr integer = :drivernr ) returns ( consecutivedays
integer ) as
  declare variable d7 integer;
  declare variable d6 integer;
  declare variable d5 integer;
  declare variable d4 integer;
  declare variable d3 integer;
  declare variable d2 integer;
  declare variable d1 integer;
  declare variable wd1 date;
  declare variable wd2 date;
  declare variable started integer;
begin
  started = 0;
  consecutivedays = 0;
  for select iif( d7 = 'DRIVING', 1, 0 ), iif( d6 = 'DRIVING', 1, 0 ),
 iif( d5 = 'DRIVING', 1, 0 ), iif( d4 = 'DRIVING', 1, 0 ),
 iif( d3 = 'DRIVING', 1, 0 ), iif( d2 = 'DRIVING', 1, 0 ),
 iif( d1 = 'DRIVING', 1, 0 ), "WEEKDATE"
  from DRIVERPLAN
  where drivernr = :drivernr
  order by "WEEKDATE" desc
  into :d7, :d6, :d5, :d4, :d3, :d2, :d1, :wd2 do
  begin
if ( ( started = 0 ) or ( wd2 = wd1 + 7 ) ) then
begin
  wd1 = wd2
end-- ERROR HERE
else
begin -- previous week without record for driver
  suspend;
  exit;
end
if ( d7 = 1 ) then
begin
  consecutivedays = consecutivedays + 1;
  started = 1;
end
else if ( started = 1 ) then
begin
  suspend;
  exit;
end
if ( d6 = 1 ) then
begin
  consecutivedays = consecutivedays + 1;
  started = 1;
end
   else if ( started = 1 ) then
begin
  suspend;
  exit;
end
if ( d5 = 1 ) then
begin
  consecutivedays = consecutivedays + 1;
  started = 1;
end
else if ( started = 1 ) then
begin
  suspend;
  exit;
end
if ( d4 = 1 ) then
begin
  consecutivedays = consecutivedays + 1;
  started = 1;
end
else if ( started = 1 ) then
begin
  suspend;
  exit;
end
if ( d3 = 1 ) then
begin
  consecutivedays = consecutivedays + 1;
  started = 1;
end
else if ( started = 1 ) then
begin
  suspend;
  exit;
end
if ( d2 = 1 ) then
begin
  consecutivedays = consecutivedays + 1;
  started = 1;
end
else if ( started = 1 ) then
begin
  suspend;
  exit;
end
if ( d1 = 1 ) then
begin
  consecutivedays = consecutivedays + 1;
  started = 1;
end
else if ( started = 1 ) then
begin
  suspend;
  exit;
end
  end
  suspend;

I have tried SQLFIDDLE and SQLTEST.NET but the only allow select statements

Any idea why this error would occur?

Regards

Stef

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: 26 January 2019 16:58
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Consecutive values from different fields



>I have a question if you can help me PLEASE.
>
>Using a Firebird SQL query is it possible to count the consecutive
number of matching values in different fields?
>I need to determine the LAST consecutive days a driver has been working.
>
>DPID  WEEKDATE   DRIVERNR DRIVER D1  D2  D3  D4
D5  D6  D7
>83145 12.11.2018 697  JOHN SMITH DRIVING
>83290 19.11.2018 697  JOHN SMITH DRIVING LEAVE   LEAVE   LEAVE
LEAVE   LEAVE
>83435 26.11.2018 697  JOHN SMITH DRIVING
>84160 31..12.2018 697  JOHN SMITH DRIVING DRIVING DRIVING
DRIVING DRIVING DRIVING
>84305 07.01.2019 697  JOHN SMITH DRIVING AWOL
>84450 14.01.2019 697  JOHN SMITH
DRIVING DRIVING
>84595 21.01.2019 697  JOHN SMITH DRIVING DRIVING DRIVING DRIVING
>
>Using the data above my result should be 5

Sorry for replying a bit late.

I fully agree with Lester. Normally, things are simpler if you have one
occurrance per record rather than seven occurances per record. For any
human it is easy to understand the concept of week and that D2 follows
D1, D3 D2 etc, but that at the same time D1 of week2 follows D7 of
week1. It is considerably harder to tell a computer the same thing.

Anyway, your question also involves the consept of having to think of
missing days (and weeks), something that is not straight forward in SQL
regardless of whether your table is arranged in a way appropriate for
spreadsheets (your suggestion) or databases (Lesters suggestion).
Returning data that doesn't exist in the table is troublesome anyway.

Hence, I'd go for an EXECUTE BLOCK (the 'on the fly' alternative to
writing a stored procedure). This is at least an easy concept to
understand. My 

RE: [firebird-support] long condition faster?

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Olaf!

select bon.*
  from (select case when t.price > 100 then 'EXPENSIVE'
   else 'CHEAP'
   end buy_or_not
  from  t) bon
  where bon.buy_or_not = 'CHEAP'

András

From: firebird-support@yahoogroups.com 
Sent: Thursday, January 3, 2019 2:27 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] long condition faster?


Hello,

if I select a value:

a.standort,
  case when(a.fertigstellung <= :fstichtag) then 4 when ((fertigstellung >= 
:fstichtag or fertigstellung is null) and abnahme <= :fstichtag) then 3 end
  from ...

and I would check this in the where-condition, should I write the entire text 
again or can I do it shorter?

Example:

Where case... is not null

If I set the Field "as fielda", I cannot do this: "where fielda is not null"

Thanks a lot!

Best regards

Olaf





__ Information from ESET Mail Security, version of virus signature 
database 18646 (20190103) __

The message was checked by ESET Mail Security.
http://www.eset.com



__ Information from ESET Mail Security, version of virus signature 
database 18646 (20190103) __

The message was checked by ESET Mail Security.
http://www.eset.com



[Non-text portions of this message have been removed]







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: [firebird-support] Re: Transaction access mode (read only vs. read write) from context?

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Vlad!

Thank you!

Is it possible to backport this items to 2.5.9?

András

From: firebird-support@yahoogroups.com 
Sent: Wednesday, September 12, 2018 6:13 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Transaction access mode (read only vs. read 
write) from context?



> According to the documentation 
> (https://firebirdsql.org/refdocs/langrefupd20-get-context.html) I can retrive 
> the transaction’s isolation level:

> select rdb$get_context('SYSTEM', 'ISOLATION_LEVEL') from rdb$database

>

> But I need to know the access mode too. Is this possible somehow to know it 
> via contexts?



Firebird3 supports following transaction-related items in 'SYSTEM' namespace:



"TRANSACTION_ID", "ISOLATION_LEVEL", "LOCK_TIMEOUT", "READ_ONLY"



Regards,

Vlad







[Non-text portions of this message have been removed]

RE: [firebird-support] Prevent overlaping dates in concurentenvironment

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Karol!

Yes, but if you do lock in MEETINGS_UQ_BI the lock will hold only while the 
trigger runs in autonomous transaction. Depending on the business logic it can 
be much more shorter (some millisecounds) than locking in „parent” transaction 
and hold it for minutes or more waiting for user interaction…

András

From: firebird-support@yahoogroups.com 
Sent: Thursday, September 13, 2018 8:29 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Prevent overlaping dates in concurentenvironment


Hi,

thank you András, but this is what we do already. I mention this in my first 
post.
"Currently we lock customer record and only one user can do 
update/insert/delete operation at a time."

But this cause serialisation of operations, only one "user" at same time.
This cause that our server sleep instead of use it full power.

Regards,
Karol Bieniaszewski



[Non-text portions of this message have been removed]

RE: [firebird-support] Prevent overlaping dates in concurent environment

[Omacht András aoma...@mve.hu [firebird-support]]

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

[Omacht András aoma...@mve.hu [firebird-support]]

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

[Omacht András aoma...@mve.hu [firebird-support]]

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]

[firebird-support] Transaction access mode (read only vs. read write) from context?

[Omacht András aoma...@mve.hu [firebird-support]]

Hi All!

According to the documentation 
(https://firebirdsql.org/refdocs/langrefupd20-get-context.html) I can retrive 
the transaction's isolation level:
select rdb$get_context('SYSTEM', 'ISOLATION_LEVEL') from rdb$database

But I need to know the access mode too. Is this possible somehow to know it via 
contexts?

I know there is a way queriing it via mon$ tables:

select t.mon$read_only
  from mon$transactions t
  where t.mon$transaction_id = current_transaction

but if I remember correctly Vlad said - at one of the conferences - it was very 
expensive operation to query the mon$ tables for a lot of transactions (because 
of the full database lock while it's processing).

Thanks,

András



__ Information from ESET Mail Security, version of virus signature 
database 18037 (20180912) __

The message was checked by ESET Mail Security.
http://www.eset.com

RE: [firebird-support] "starting with" inside a procedure

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Sergio!

Starting with is your friend.

create or alter procedure ST_WITH (IN1 varchar(100), IN2 varchar(100))
returns (OK char(1))
as
begin
  if (IN1 starting with IN2) then
begin
  ok = 'Y';
end
  else
begin
  ok = 'N';
end
  suspend;
end

select * from st_with('WORD', 'WO')
OK
Y

select * from st_with('WORD', 'X')
OK
N

András


From: firebird-support@yahoogroups.com 
Sent: Tuesday, July 24, 2018 6:13 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] "starting with" inside a procedure



Hello! I know that there are a lot of different ways to accomplish this, but I 
wonder if I'm missing something...



if I want (inside a stored proc or a trigger) to know if str1 starts with str2, 
I can do:



   if  (position(str2 in str1) = 1) then...



but there isn't something like the "starts with" predicate we have in SQL?



   if  (str1 starts with str2)  then...



thanks!!!



Sergio



__ Information from ESET Mail Security, version of virus signature 
database 17768 (20180724) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

Re: [firebird-support] get number of combinations of to keys

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Set,


isn't


select count(1)
  from (select pro, fa
  from tab
  where Key = :Key
  group by pro, fa)
into :ProFaCount


enough?


András



Feladó: firebird-support@yahoogroups.com , 
meghatalmazó: Svein Erling Tysvaer setys...@gmail.com [firebird-support] 

Elküldve: 2018. július 16. 9:11
Címzett: firebird-support@yahoogroups.com
Tárgy: Re: [firebird-support] get number of combinations of to keys



Well, you need to know more about PRO and FA than we do, but if they are 
character fields and neither of them can include _, then maybe:

select count( distinct coalesce( PRO, '' ) || '_' || coalesce( FA, '' ) ) 
ProFaCount
from Tab
where Key = :Key

would work. The COALESCE is there in case the fields could be NULL. If PRO and 
FA are non-null numbers and FA always is between 0 and 99, then the query would 
be

select count( distinct ( 100 * PRO ) + FA ) ProFaCount
from Tab
where Key = :Key

HTH,
Set

Den man. 16. jul. 2018 kl. 08:58 skrev josef.gschwendt...@quattro-soft.de 
[firebird-support] 
mailto:firebird-support@yahoogroups.com>>:



Hi,


Is there a way to get this ProFaCount in one statement (without for select)

ProFaCount = 0;
for
  select distinct PRO, FA
from Tab
   where Key = :Key
into :PRO, :FA
do
  ProFaCount = ProFaCount + 1;


Thank you for your help.


Regards,

Josef






__ Information from ESET Mail Security, version of virus signature 
database 17721 (20180716) __

The message was checked by ESET Mail Security.
http://www.eset.com



__ Information from ESET Mail Security, version of virus signature 
database 17721 (20180716) __

The message was checked by ESET Mail Security.
http://www.eset.com

RE: [firebird-support] Delete issue

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Set,

because I was in hurry and did not optimize enough.

Point goes to you. 

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Friday, June 15, 2018 11:09 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Delete issue


Good to see that the issue is solved, but of curiosity: Why not simply

delete from tplan_kw_pos a
where not exists( select * from tauftr_ge b
where a.auftragsjahr = b.jahr
  and a.auftragsnr = b.nr )

Set

2018-06-15 10:38 GMT+02:00 Omacht András aoma...@mve.hu 
[firebird-support] 
mailto:firebird-support@yahoogroups.com>>:
Hi Anonymous!

I think you sould check and run if correct this statement:

delete from tplan_kw_pos x
  where not exists (select 1
  from tplan_kw_pos a
inner join tauftr_ge b on a.auftragsjahr = b.jahr and 
a.auftragsnr = b.nr
  where a.id = x.id)

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups..com]
Sent: Friday, June 15, 2018 10:19 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Delete issue


Hello,

I would like to delete all records, where no order number already exists in the 
table tauftr_ge. Here an example of the statement:

delete from tplan_kw_pos x where x.id =  (select a.id 
from tplan_kw_pos a left join tauftr_ge b on a.auftragsjahr = b.jahr and 
a.auftragsnr = b.nr where b.nr is null and 
a.id = x.id)


I get an error, The cursor identified in the UPDATE or DELETE statement is not 
positioned on a row.
no current record for fetch operation.

It looks simple, delete some records, but what can be wrong?

Thank you.





__ Information from ESET Mail Security, version of virus signature 
database 17555 (20180615) __

The message was checked by ESET Mail Security.
http://www.eset.com



__ Information from ESET Mail Security, version of virus signature 
database 17555 (20180615) __

The message was checked by ESET Mail Security.
http://www.eset.com



[Non-text portions of this message have been removed]







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links






__ Information from ESET Mail Security, version of virus signature 
database 17555 (20180615) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

RE: [firebird-support] Delete issue

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Anonymous!

I think you sould check and run if correct this statement:

delete from tplan_kw_pos x
  where not exists (select 1
  from tplan_kw_pos a
inner join tauftr_ge b on a.auftragsjahr = b.jahr and 
a.auftragsnr = b.nr
  where a.id = x.id)

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Friday, June 15, 2018 10:19 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Delete issue


Hello,

I would like to delete all records, where no order number already exists in the 
table tauftr_ge. Here an example of the statement:

delete from tplan_kw_pos x where x.id =  (select a.id from tplan_kw_pos a left 
join tauftr_ge b on a.auftragsjahr = b.jahr and a.auftragsnr = b.nr where b.nr 
is null and a.id = x.id)


I get an error, The cursor identified in the UPDATE or DELETE statement is not 
positioned on a row.
no current record for fetch operation.

It looks simple, delete some records, but what can be wrong?

Thank you.





__ Information from ESET Mail Security, version of virus signature 
database 17555 (20180615) __

The message was checked by ESET Mail Security.
http://www.eset.com



__ Information from ESET Mail Security, version of virus signature 
database 17555 (20180615) __

The message was checked by ESET Mail Security.
http://www.eset.com



[Non-text portions of this message have been removed]







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Gábor!

I see, you have many root elements.
Then Mark gave the perfect solution:

WITH RECURSIVE tree AS (
SELECT t.id, t.parent_id
FROM table1 t
  inner join table1 root on root.id = t.parent_id
WHERE root.parent_id is null
UNION ALL
SELECT m.id, t.parent_id
FROM table1 m
JOIN tree t ON t.id = m.parent_id
WHERE m.parent_id is not null
ORDER BY m.id
)
select r.id, r.parent_id
  from table1 r
where r.parent_id is null
union all
SELECT t.id, t.parent_id
FROM tree t

(I prefer join instead of exists, it is better when you have good indicies.)

The result is:
ID  PARENT_ID
1   
7   
2   1
3   1
4   1
5   1
6   1
8   7
9   7
10  7
11  7
12  7

András



__ Information from ESET Mail Security, version of virus signature 
database 17471 (20180530) __

The message was checked by ESET Mail Security.
http://www.eset.com








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Gábor!

Now I can only test on 2.5.8, and it works:

ID  PARENT_ID
7   
8   7
9   7
10  7
11  7
12  7

András

-Original Message-
From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com] 
Sent: Wednesday, May 30, 2018 2:42 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

2018. 05. 29. 19:08 keltezéssel, Omacht András aoma...@mve.hu 
[firebird-support] írta:
> Hi Gabor!
> 
> You can do it with recursive cte:


First of all, thank you very much! But...

I got "multiple rows in singleton select multiple rows in singleton select" 
(yes, double) error message with my real data. It's easy to reproduce just 
execute the next script for additional test data.

INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (7, NULL); INSERT INTO TABLE1 (ID, 
PARENT_ID) VALUES (8, 7); INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (9, 8); 
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (10, 7); INSERT INTO TABLE1 (ID, 
PARENT_ID) VALUES (11, 10); INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (12, 7);

Gabor







++

Visit http://www.firebirdsql.org and click the Documentation item on the main 
(top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links





__ Information from ESET Mail Security, version of virus signature 
database 17470 (20180530) __

The message was checked by ESET Mail Security.
http://www.eset.com




__ Information from ESET Mail Security, version of virus signature 
database 17470 (20180530) __

The message was checked by ESET Mail Security.
http://www.eset.com








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Gabor!

You can do it with recursive cte:

WITH RECURSIVE tree AS (
SELECT t.id, t.parent_id
FROM table1 t
WHERE t.parent_id = (select root.id from table1 root where 
root.parent_id is null)
UNION ALL
SELECT m.id, t.parent_id
FROM table1 m
JOIN tree t ON t.id = m.parent_id
WHERE m.parent_id is not null
ORDER BY m.id
)
select r.id, r.parent_id
  from table1 r
where r.parent_id is null
union all
SELECT t.id, t.parent_id
FROM tree t


András

-Original Message-
From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com] 
Sent: Tuesday, May 29, 2018 6:25 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

Hi All,

I have a table (see below) with some parent/child data. I want to know very top 
parent of every child. So the expected result (with below test
data) is:

ID,TOP_PARENT_ID
1,NULL
2,1
3,1
4,1
5,1
6,1

Is this result can be produced with a recursive CTE? (I tried already without 
success.) Or use a recursive stored function (I use 3.0) for it?


CREATE TABLE TABLE1 (ID BIGINT NOT NULL, PARENT_ID BIGINT);

INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (1, NULL);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (2, 1);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (3, 2);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (4, 1);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (5, 4);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (6, 1);

Gabor






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links





__ Information from ESET Mail Security, version of virus signature 
database 17465 (20180529) __

The message was checked by ESET Mail Security.
http://www.eset.com




__ Information from ESET Mail Security, version of virus signature 
database 17465 (20180529) __

The message was checked by ESET Mail Security.
http://www.eset.com








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: [firebird-support] Firebird using all available memory

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Fabiano!

You should run garbage collection (sweep).


Oldest transaction  905
Oldest active   62303005
Oldest snapshot 62303005
Next transaction62382577
András

OMACHT ANDRÁS
fejlesztési igazgató

[cid:image001.png@01CFE703.C881C660]
Libra Szoftver Zrt.
1113 Budapest, Karolina út 65.
Tel.: +36 1 255 3939
Fax: +36 1 209 1477
http://www.libraszoftver.hu

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Wednesday, May 23, 2018 4:29 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird using all available memory


Hi, Thomas. It is zero. I presume in this case it uses the system default, 
right?

Regards,

Fabiano

Database header page information:
Flags   0
Generation  62390727
System Change Number0
Page size   4096
ODS version 12.0
Oldest transaction  905
Oldest active   62303005
Oldest snapshot 62303005
Next transaction62382577
Sequence number 0
Next attachment ID  1290314
Implementation  HW=AMD/Intel/x64 little-endian OS=Linux CC=gcc
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date   Apr 4, 2018 7:03:44
Attributes  force write


[A képet a feladó eltávolította.]

2018-05-22 18:30 GMT-03:00 'Thomas Kragh-Pedersen (TKG)' 
t...@kmd.dk [firebird-support] 
>:

Hey Fabiano
Perhaps DefaultDbCachePages is overwritten by database header information. 
Gstat -h will tell you that. Look for “Page buffers”


From: firebird-support@yahoogroups.com 
>
Sent: 18. maj 2018 16:12
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Firebird using all available memory


Hi,

I have a Debian 9 Linux server with 8GB of memory, a database with 30GB, page 
size 4KB, and Firebird 3.0.3 running in superserver mode and about 150-200 
concurrent connections.

In my firebird.conf I have changed the following properties:

DefaultDbCachePages = 524288 # 2GB
TempCacheLimit = 1024M # 1GB
LockMemSize = 2M

As superserver uses a shared cache, I presumed the total used memory should 
stay between 3GB and 4GB, but it keeps growing during the day until it reaches 
the 8GB limit and Firebird shuts down with this message:

debian  Fri May 18 10:45:35 2018
Shutting down the server with 150 active connection(s) to 2 
database(s), 0 active service(s)

I am checking the active transactions during the day, and altought there are 
some old (2h to 3h) active transactions, I don't that an old active transaction 
should cause this, but I am reporting anyway, maybe it is relevant.

Do someone have some advice to what should I do to prevent this from happening?

Maybe my understanding about how Firebird uses memory is incorrect, so any 
advice about how I should calculate the required amount of memory needed is 
welcome.

PS: I have migrated from FB 2.5 recently (previously I did use superclassic 
mode), and these problems started after the migration.

Regards,

Fabiano

Vi gør opmærksom på, at denne e-mail kan indeholde fortrolig information. Hvis 
du ved en fejltagelse modtager e-mailen, beder vi dig venligst informere 
afsender om fejlen ved at bruge svarfunktionen. Samtidig beder vi dig slette 
e-mailen i dit system uden at videresende eller kopiere den. Selvom e-mailen og 
ethvert vedhæftet bilag efter vores overbevisning er fri for virus og andre 
fejl, som kan påvirke computeren eller it-systemet, hvori den modtages og 
læses, åbnes den på modtagerens eget ansvar. Vi påtager os ikke noget ansvar 
for tab og skade, som er opstået i forbindelse med at modtage og bruge e-mailen.

Please note that this message may contain confidential information. If you have 
received this message by mistake, please inform the sender of the mistake by 
sending a reply, then delete the message from your system without making, 
distributing or retaining any copies of it. Although we believe that the 
message and any attachments are free from viruses and other errors that might 
affect the computer or it-system where it is received and read, the recipient 
opens the message at his or her own risk. We assume no responsibility for any 
loss or damage arising from the receipt or use of this message..




__ Information from ESET Mail Security, version of virus signature 
database 17432 (20180523) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message 

RE: [firebird-support] Re: Firebird temp files

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Karol,

we have chosen the following solution to avoid temp disk writes:

  *   Firebird 2.5.8 classic default configuration (with 2 changes see below)
  *   456 databases (~379 GB)
  *   average ~4000 fb_inet_server processes
  *   10 GB ramdisk (/ramdisk/fb_temp) for external files and for 
tempdirectories (chmod 700 for firebird user):
 *   ExternalFileAccess = /ramdisk/fb_temp;/hdd_path/fb_temp
 *   TempDirectories = /ramdisk/fb_temp;/hdd_path/fb_temp

This 10 GB more than enough for us. Based on the monitoring system data, it has 
never been completely full.

András

RE: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure

[Omacht András aoma...@mve.hu [firebird-support]]

Hi!

You should suspend every records like this:

  DO
begin
  IF (PI_KEY_IN = 0) THEN
EXCEPTION ROOT_CAT_NODE_DELETE;

SUSPEND;
end


András


From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Thursday, May 3, 2018 10:33 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Problem returning multiple rows from a 
CTE-recursive procedure



Hello...

I have been in the process of converting my application's SQL Server T-SQL code 
to Firebird PSQL procedures.  So far so good until I got to the one where I 
built a Firebird procedure with a recursive CTE in it.

The following code is an exact match to my original SQL Server T-SQL code

>>>
WITH RECURSIVE HIERARCHY_TABLE AS
(
SELECT RCN1.CN_KEY,
   RCN1.CN_PARENT_KEY
FROM RI_CATEGORY_NODES RCN1
WHERE RCN1.CN_KEY = 3
UNION ALL
 sp;   SELECT RCN2.CN_KEY,
   RCN2.CN_PARENT_KEY
FROM RI_CATEGORY_NODES RCN2
JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY =
 HIERARCHY_TABLE.CN_KEY
)
SELECT * FROM HIERARCHY_TABLE;
<<<

When run in my Firebird DB Manager within a query script-screen against the two 
records in the table, it returns both records as it should.

The records are setup as follows...

>>>
record #1   CN_KEY = 3,  CN_PARENT_KEY = 0
 (a parent key of 0 means that this is the top-most record in 
the hierarchy)

record #2   CN_KEY = 4,  CN_PARENT_KEY = 3
 (child record to to record #1)
<<<

The result then from this test is that the Firebird PSQL code produces the same 
exact result as my SQL Server's T-SQL code when run.

The problem I am finding however, is that when I run my Firebird PSQL code 
above within a procedure, it only returns record #2, the child record, instead 
of both records.

My Firebird PSQL procedure is as follo ws...

>>>
CREATE PROCEDURE SP_GET_CAT_CHILD_NODES_BYKEY(
  PI_KEY_IN BIGINT NOT NULL)
RETURNS(
  PI_KEY_OUT BIGINT,
  PI_PARENT_KEY_OUT BIGINT)
AS
BEGIN
  FOR
WITH RECURSIVE HIERARCHY_TABLE AS
(
SELECT RCN1.CN_KEY,
   RCN1.CN_PARENT_KEY
FROM RI_CATEGORY_NODES RCN1
WHERE RCN1.CN_KEY = :PI_KEY_IN
UNION ALL
SELECT RCN2.CN_KEY,
  bsp; RCN2.CN_PARENT_KEY
FROM RI_CATEGORY_NODES RCN2
JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY =
 HIERARCHY_TABLE.CN_KEY
)
SELECT CN_KEY,
   CN_PARENT_KEY
FROM HIERARCHY_TABLE
INTO :PI_KEY_OUT,
 bsp;:PI_PARENT_KEY_OUT
  DO

  IF (PI_KEY_IN = 0) THEN
  EXCEPTION ROOT_CAT_NODE_DELETE;

  SUSPEND;
END;
<<<



Can anyone explain why my procedure is not returning the expected number of 
records?



Thank you...



__ Information from ESET Mail Security, version of virus signature 
database 17326 (20180503) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

RE: [firebird-support] Optional FK constraint on 0 (not NULL)

[Omacht András aoma...@mve.hu [firebird-support]]

Sorry, two little corrections:

3. create a before insert or update trigger which fill up this field like this: 
new.master_id_fk = nullif(new.master_id, 0);

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Wednesday, March 28, 2018 9:44 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Optional FK constraint on 0 (not NULL)



Hi Bhavbhuti,

I can suggest you only a workaround….

1. your current field name is: MASTER_ID
2. add a new field, e.g.: MASTER_ID_FK
3. create a before insert and update trigger which fill up this field like 
this: new.master_id_fk = nullif(master_id, 0);
4. create the foreign key on MASTER_ID_FK field.

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups...com]
Sent: Wednesday, March 28, 2018 8:54 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Optional FK constraint on 0 (not NULL)



Hi all

I have a transaction table that has an integer FK field. This FK field can be 
either a 0 (no master record selected) or an integer that is a FK value from 
the master PKs. If I put in a FK Constraint then it will not allow me a 0 in 
the transaction table's FK field. I know NULL is allowed but for the present I 
can't use NULL in the transaction table's FK field as it might impact other 
operations.



Is there an intermediate way to constraint only if a value > 0 is present in 
the transaction table's FK field. I need to stop a master record from deleting 
in case there is a reference.



Please advise



Kind regards

Bhavbhuti





__ Information from ESET Mail Security, version of virus signature 
database 17128 (20180328) __

The message was checked by ESET Mail Security.
http://www.eset.com

[Non-text portions of this message have been removed]



__ Information from ESET Mail Security, version of virus signature 
database 17128 (20180328) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

RE: [firebird-support] Optional FK constraint on 0 (not NULL)

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Bhavbhuti,

I can suggest you only a workaround….

  1.  your current field name is: MASTER_ID
  2.  add a new field, e.g.: MASTER_ID_FK
  3.  create a before insert and update trigger which fill up this field like 
this: new.master_id_fk = nullif(master_id, 0);
  4.  create the foreign key on MASTER_ID_FK field.

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Wednesday, March 28, 2018 8:54 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Optional FK constraint on 0 (not NULL)



Hi all

I have a transaction table that has an integer FK field.  This FK field can be 
either a 0 (no master record selected) or an integer that is a FK value from 
the master PKs.  If I put in a FK Constraint then it will not allow me a 0 in 
the transaction table's FK field.  I know NULL is allowed but for the present I 
can't use NULL in the transaction table's FK field as it might impact other 
operations.



Is there an intermediate way to constraint only if a value > 0 is present in 
the transaction table's FK field.  I need to stop a master record from deleting 
in case there is a reference.



Please advise



Kind regards

Bhavbhuti





__ Information from ESET Mail Security, version of virus signature 
database 17128 (20180328) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

RE: [firebird-support] select *at least* N rows

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Sergio and Mark,

a bit more general solution (for example max. 100 empty rows without 100 union 
all):

with recursive
  last_empty_row as (
  select 100 rownum  -- set expected row number here
from rdb$database),
  empty_rows as (
  select 1 rownum
from rdb$database
  union all
  select tr.rownum + 1 rownum
from empty_rows tr
  where tr.rownum < 100)  -- set expected row number here
select first 100 rownum, field1, field2  -- set expected row number 
here, replace your field names here
  from (
-- your real select is coming here…
select first 100 0 rownum, 'A' field1, 'B' field2   -- set expected 
row number here, replace your field names here
  from rdb$database-- replace your table name here
union all
select t.rownum, null field1, null field2-- replace your field 
names here
  from empty_rows t
cross join last_empty_row l
  where t.rownum <= l.rownum
order by 1)

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Friday, March 23, 2018 6:51 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] select *at least* N rows



On 23-3-2018 18:26, shg_siste...@yahoo.com.ar [firebird-support] wrote:
> Hello! is there any trick to select a fixed number of rows? I mean, no
> matter if I have 2 rows which match the select, I need to always return
> 10 rows. Of course the last 8 would be all null in this example...
>
> I hope I'm clear with my question! Not sure if I'm in the "right path",
> but if I can do that I can fix very easily a stored procedure I'm
> working on now.

There is nothing directly in Firebird to do that, you could try
something like this (Firebird 3, for earlier versions use ROWS 10
instead of "fetch first 10 rows only"):

select ID, NAME
from (
-- Need to nest to avoid limitation in the Firebird SQL grammar
select ID, NAME from (
select ID, NAME
from ITEMS
order by id
fetch first 10 rows only
)
-- as many null columns is in the above query
-- repeat the union all as many times as you need guaranteed rows
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
)
order by id nulls last
fetch first 10 rows only

Technically the order by is not necessary, but leaving it out makes you
rely on an implementation detail. If you do add it, the "nulls last" is
required.

Mark
--
Mark Rotteveel



__ Information from ESET Mail Security, version of virus signature 
database 17106 (20180323) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

RE: [firebird-support] update or insert from in-line query

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Mark,

I meant, if he needs insert and update, it's enough for him.

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Monday, March 19, 2018 9:04 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] update or insert from in-line query



On 19-3-2018 07:51, Omacht András aoma...@mve.hu [firebird-support] wrote:
> Hi Hamis,
>
>
>
> merge is your friend:
>
> https://firebirdsql.org/refdocs/langrefupd21-merge.html
>
>
>
> In FB 2.5 series only insert and update is supported.

What do you mean with this sentence?

--
Mark Rotteveel



__ Information from ESET Mail Security, version of virus signature 
database 17079 (20180319) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

RE: [firebird-support] update or insert from in-line query

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Hamis,

merge is your friend:
https://firebirdsql.org/refdocs/langrefupd21-merge.html

In FB 2.5 series only insert and update is supported.

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Monday, March 19, 2018 7:48 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] update or insert from in-line query



Hi,

Is UPDATE OR INSERT INTO table-name  SELECT ...

not supported? I'm using Firebird 2.5.8.

thanks

Hamish



__ Information from ESET Mail Security, version of virus signature 
database 17079 (20180319) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

RE: [firebird-support] ASCII values

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Hugo!

If i understood well, you need this (tested on Fb2.5.8):

select * from table where field = ascii_char(3) || ascii_char(1);

or

select * from table where ascii_val(substring(field from 1 for 1) = 3 and 
ascii_val(substring(field from 2 for 1) = 1;

András


From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Friday, March 9, 2018 6:33 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] ASCII values



Hello,

I have a column char(2) where two ASCII values stored. For example 0011, 
0001 are stored as (3,1).
When I do a select I dont get any result because the result is not 
alphanumeric. 3 is ETX and 1 is NUL.
I can get the values programmatically but I want to do bit comparison with sql 
query.
Anyone might know how?

Thx,
Hugo Larson





__ Information from ESET Mail Security, version of virus signature 
database 17027 (20180309) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

RE: [firebird-support] exponential, string to double precision

[Omacht András aoma...@mve.hu [firebird-support]]

Hi Olaf!

Try this:
select cast('+0.200E+01' as double precision) from rdb$database

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Wednesday, February 28, 2018 7:44 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] exponential, string to double precision


Hello,

is there a simple function to convert a string "+0.200E+01" to a number, 
double precision, in this case to 2.0?

Thank you

Best regards

Olaf



__ Information from ESET Mail Security, version of virus signature 
database 16977 (20180228) __

The message was checked by ESET Mail Security.
http://www.eset.com



__ Information from ESET Mail Security, version of virus signature 
database 16977 (20180228) __

The message was checked by ESET Mail Security.
http://www.eset.com



[Non-text portions of this message have been removed]







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

RE: [firebird-support] Select freezes

[Omacht András aoma...@mve.hu [firebird-support]]

Hi!

Maybe the select starts the garbage collection, and it slows down it. When you 
run backup without -g it does the garbage collection too, so could fix the 
problem. (Are you running it with / without -g?)


  *   How many updates / delete run average on that table? Is it often changed 
or not?
  *   Can business logic cause many rollback transactions?
  *   Are you running long running transactions, which can block the garbage 
collection?
  *   Run gstat -h and check transaction markers (Oldest transactin, Oldest 
active, Oldest snapshot, Next transaction, Sweep interval) when the select hang 
to check this theory.

http://www.firebirdsql.org/pdfmanual/html/gfix-housekeeping.html

András

From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Saturday, December 30, 2017 12:42 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Select freezes



Hello.



My software is running on a client and sometimes something happens and a SELECT 
command in a table (only in this table) freezes. Then I just do a backup (only 
a backup, without restore) and the command runs correct again.



What could it be?



Thank you.







__ Information from ESET Mail Security, version of virus signature 
database 16657 (20171230) __

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]

RE: [firebird-support] Get the value of Query Not Available In Table

[Omacht András aoma...@mve.hu [firebird-support]]

Hi!

with nums as (
  select 1 num from rdb$database
union all
  select 2 num from rdb$database)
select n.num
  from nums n
  where not exists (select 1
  from temptable t
  where t.id = n.num)

András

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Tuesday, November 28, 2017 11:56 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Get the value of Query Not Available In Table



Hi,

I am looking for a solution to get values in query and those not available in 
table data.



CREATE TABLE TEMPTABLE(

ID SMALLINT);



INSERT INTO TEMPTABLE(ID) VALUES(1);



SELECT * FROM TEMPTABLE

WHERE ID IN (1,2)



in the Above case I want to get the value 2 as output of a query.



Kindly Help.

Thanks In Advance.





__ Information from ESET Mail Security, version of virus signature 
database 16483 (20171128) __

The message was checked by ESET Mail Security.
http://www.eset.com




[Non-text portions of this message have been removed]