Re: [firebird-support] Re: A error of too many concurrent execution of the same request on a trigger
At 08:03 PM 29/11/2011, ibmcom2011 wrote: heLen, Thanks. I found I should add a with check option to the end of a updatable view in the introduction of FB2.0. Now my FB version is 2.5. But the option requires a where sub-clause, and I don't know how to do. Do you have a WHERE clause in the view? If not, WITH CHECK OPTION is not valid. What WITH CHECK OPTION will do is prevent the user from updating the column (or columns) used in the WHERE filter. But for the view for the purpose described, you should leave it unfiltered and apply a WHERE clause to it when you access it in the table trigger. And Now, I have a suppose on my trigger. For the Column YE is not be updated by user (it is non-visible for users), I want to strict the execution condition of the trigger through the Column. But how to write the condition? if (updating or (deleting)) then A test for modification would be invalid for (deleting). begin /* if YE is modified then leave */ if (updating) then begin if (old.YE is distinct from new.YE) then begin /* do nothing */ end else begin /* do what you intended */ ... end end ./h
Re: [firebird-support] Clustering Firebird
Hello, No such thing as Firebird cluster exists, at least in the frames of current versions. If you need failover and/or scalability, design your system in distributed manner with replication. Regards, Alexey Kovyazin IBSurgeon Hello everybody Do you know some book or article about clustering Firebird's databases? Greetings. Walter. [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
[firebird-support] Re: A error of too many concurrent execution of the same request on a trigger
Helen, I created a view as you said. CREATE OR ALTER VIEW V_T_BALANCE( F_ACD_ID, NUM1, NUM2, BALANCE) AS select F_ACD_ID, NUM1, NUM2, BALANCE from FIN_ACC_CASH_DIARY where 1 = 1 with check option ; Then create a trigger for the view: CREATE OR ALTER TRIGGER V_T_BALANCE_AUD0 FOR V_CASH_DIARY_BALANCE ACTIVE AFTER UPDATE OR DELETE POSITION 0 AS begin /* Trigger text */ end ^ Meanwhile, in the TABLE1, the Trigger has been modified like this: if (updating or (deleting) ) then begin select max(f_acd_id) from table1 into :maxID; nid = old.f_acd_id; while (nid :maxid) do begin select first 1 balance from V_T_BALANCE where f_acd_id :nid order by f_acd_id desc into :ye; update V_T_BALANCE set balance = :ye + new.num1 - new.num2 where f_acd_id = :nid; nid = :nid + 1; end end But when I modified a record, the trigger doesn't work. Either the view or the table, the value of Column BALANCE is not changed. --- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote: At 03:11 AM 29/11/2011, you wrote: Hi, I try to update a column value in a trigger after updating a row, but it raises a error of too many concurrent execution of the same request. I know this is caused by recursion, but I can't avoid it. Yes, you can avoid it, as long as you are using Fb 2 or higher. I want update a column value of all the rows which is after the updating rows. ID -- Num1 -- Num2 - YE 1 -- 15 -- 25 -- 28 2 ... 3 ...---32 4 20 --- 35 ---27 --- 32+20-35 =27 5 6 7 if in row 4, then num1 or num2 is changed, then for the rows 4 to 7, the columns YE will be changed followed. - The trigger is like this: declare variable nID integer; declare variable maxid integer; if (updating or (deleting) ) then begin select max(f_acd_id) from table1 into :maxID; nid = old.f_acd_id; while (nid :maxid) do begin select first 1 balance from fin_acc_cash_diary where f_acd_id :nid order by f_acd_id desc into :ye; update fin_acc_cash_diary set balance = :ye + new.num1 - new.num2 where f_acd_id = :nid; nid = :nid + 1; end end Looking forward to your help, thanks. There are a few things wrong with this approach. As you've already found, your attempt to use a trigger to perform DML on other rows in the same table will just recurse indefintely until the limit (1000 executions) kicks in and stops it. Another thing that's wrong (or, rather, unwise) is assuming that the current state of the table's data, as seen by your transaction, is the same as what all other transactions are seeing. In a multi-user system we depend on this illusion for concurrency: it's called transaction isolation. But max(f_acd_id) as seen in your transaction is *not* the same as max(f_acd_id) that another transaction is seeing, if you have multiple users modifying this table. Let's hope nobody cares too much about the synchronicity of the calculated results you are storing there. Going back to the thing you are asking about, don't perform your updates by updating or deleting into the *table* that owns this trigger. Create an updatable view on this table for exclusive use by this trigger - that is, a single-table view with no computed or derived fields and having all non-nullable columns present (plus any nullable ones you want, of course). Then, write an after update or delete do-nothing trigger *for the view*, e.g., create trigger aud_vtable for vtable active after update or delete as begin /* */ end The aud_ trigger on the table will need to be granted the necessary privileges to update the view columns you want updated. With this approach, in Fb 2.0 and above, the update or delete ops that are executed from the table's trigger will fire the view's aud_ trigger and the table's aud_ trigger(s) will be bypassed. That will block the firing of the infinitely recursive trigger events that are happening with the DML callouts you are doing currently on the parent table. If you are still using Fb 1.5, unfortunately this won't help: in fact, it's likely to make matters worse. Fb 1.5 has a bug, whereby BOTH triggers would fire and nothing gets blocked. Not sure about 1.0...I have a vague recollection the bug was introduced in Fb 1.5maybe, maybe not. It didn't get fixed until v.2.0, anyway. ./heLen
[firebird-support] Firebird 2.5.1 on Lion 10.7.2 hangs opening second database
Using Firebird 2.5.1 on Lion 10.7.2 on a 4 core machine I attach to 2 separate databases on the same machine in quick succession via localhost. Firebird always opens the first database with no problems but often hangs opening the second database. The only way out is to reboot repeatedly until it does work. It then continues through the session to attach without error to both databases repeatedly whenever my program is re-started. On shutting down and later reboot it often hangs again. Debugging shows my program stops on calling isc_attach_databaseType. System logs show nothing that I can see as relevant. I have tried all 32/64 versions of Firebird for OSX except the lipo version and they all exhibit the same problem in Lion but work without problems in ms windows and in Snow Leopard. Any ideas?
Re: [firebird-support] Re: A error of too many concurrent execution of the same request on a trigger
At 10:21 PM 29/11/2011, ibmcom2011 wrote: Helen, I created a view as you said. No, you didn't. CREATE OR ALTER VIEW V_T_BALANCE( F_ACD_ID, NUM1, NUM2, BALANCE) AS select F_ACD_ID, NUM1, NUM2, BALANCE from FIN_ACC_CASH_DIARY where 1 = 1 with check option ; I said: But for the view for the purpose described, you should leave it unfiltered and apply a WHERE clause to it when you access it in the table trigger. You don't want CHECK OPTION. ./H
Re: [firebird-support] Firebird 2.5.1 on Lion 10.7.2 hangs opening second database
2011/11/29 johnryh john...@yahoo.com.au Using Firebird 2.5.1 on Lion 10.7.2 on a 4 core machine I attach to 2 separate databases on the same machine in quick succession via localhost. Firebird always opens the first database with no problems but often hangs opening the second database. The only way out is to reboot repeatedly until it does work. It then continues through the session to attach without error to both databases repeatedly whenever my program is re-started. On shutting down and later reboot it often hangs again. Debugging shows my program stops on calling isc_attach_databaseType. System logs show nothing that I can see as relevant. I have tried all 32/64 versions of Firebird for OSX except the lipo version and they all exhibit the same problem in Lion but work without problems in ms windows and in Snow Leopard. Any ideas? Not really, we know that they are some problems with OsX 10.7, but don't know exactly what an why. As soon as we can we will investigate this can you post a bug report with a small test case in the tracker ? thanks
[firebird-support] Re: Firebird 2.5.1 on Lion 10.7.2 hangs opening second database
johnryh [2011-11-29 09:29] : Any ideas? what Firebird kind are you using ? Classic, SuperClassic, Superserver ? may be you use trace/audit with SuperClassic or SuperServer ?
[firebird-support] Re: Fwd: trigger that calls procedure
Thanks Helen, it worked perfectly. I got the idea of the flow, now I can proceed with my learning. Thanks again! --- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote: Remove SUSPEND from the SP. Then, call the procedure like this if the trigger wants only a one-row set: execute procedure proc1 (arg1, arg2, .) returning_values (:var1, :var2, ) /* then do what you want with the vars */ PSQL can't do anything with a multi-row set except step through it, row by row, in a loop. If you want the trigger to use values from a set of multiple rows, determine an end condition and put the EXECUTE PROCEDURE in a WHILE loop controlled by variable: ... declare variable looper smallint=0; begin /* initialise your args and vars here */ while (looper = 0) do begin /* assign values to your args */ execute procedure proc1 (arg1, arg2, .) returning_values (:var1, :var2, ) if (end condition) then begin looper = 1; leave; end /* do what you want with the vars */ /* re-initialise the vars */ end end end Don't take this as an absolute template for what you want to do, it's just to give you the idea for designing the flow that you need. ./heLen
[firebird-support] Re: Clustering Firebird
--- In firebird-support@yahoogroups.com, W O sistemas2000profesional@... wrote: Hello everybody Do you know some book or article about clustering Firebird's databases? Greetings. Firebird HA can be achievied today with replication and using a heartbeat like in the Linux-HA project http://lists.linux-ha.org/pipermail/linux-ha-dev/2010-November/017894.html http://en.wikipedia.org/wiki/Linux-HA Scaling firebird to work on multiple machines and to distribute the load (CPU+IO read/writes) is not possible yet
[firebird-support] Re: Mac OS X (Lion) 64-bit Firebird admin tool?
Works like a champ! Thanks! --- In firebird-support@yahoogroups.com, Philippe Makowski makowski@... wrote: 2011/11/28 greg_runnels greg_runnels@... Does anyone have a good suggestion for an admin tool on the Mac? I recently upgraded to the 64-bit version of Firebird 2.5.1, and FlameRobin doesn't work with it. if you want to use Firebird 64 bit (wich is the good choice under MacOsX 10.7) with Flamerobin, or any old software that is 32bits, then install the dual arch client lib that's easy, just use the lipo package it is Firebird 64bits with client lib in both 64 and 32 bits http://www.firebirdsql.org/en/server-packages/#MacOSX
[firebird-support] Re: Firebird 2.5.1 on Lion 10.7.2 hangs opening second database
I had almost the exact same issue. I installed the lipo 64 version. Everything is happy now! --- In firebird-support@yahoogroups.com, johnryh johnryh@... wrote: Using Firebird 2.5.1 on Lion 10.7.2 on a 4 core machine I attach to 2 separate databases on the same machine in quick succession via localhost. Firebird always opens the first database with no problems but often hangs opening the second database. The only way out is to reboot repeatedly until it does work. It then continues through the session to attach without error to both databases repeatedly whenever my program is re-started. On shutting down and later reboot it often hangs again. Debugging shows my program stops on calling isc_attach_databaseType. System logs show nothing that I can see as relevant. I have tried all 32/64 versions of Firebird for OSX except the lipo version and they all exhibit the same problem in Lion but work without problems in ms windows and in Snow Leopard. Any ideas?
[firebird-support] Re: Firebird 2.5.1 on Lion 10.7.2 hangs opening second database
--- In firebird-support@yahoogroups.com, philippe makowski makowski@... wrote: johnryh [2011-11-29 09:29] : Any ideas? what Firebird kind are you using ? Classic, SuperClassic, Superserver ? may be you use trace/audit with SuperClassic or SuperServer ? Everything except the lipo version. Before I install the lipo version I will try a trace/audit when I get a moment. Thanks, John
[firebird-support] insert script
FB 2.5 I need to insert data with a script. but get an error Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 2, column 1. INSERT. the script itself is like below- any ideas what is wrong? INSERT INTO CAR_MARK (car_mark_id, car_mark_name) VALUES (1, 'test '); INSERT INTO CAR_MARK (car_mark_id, car_mark_name) VALUES (2, 'test two'); INSERT INTO CAR_MARK (car_mark_id, car_mark_name) VALUES (3, 'test three');
Re: [firebird-support] insert script
At 12:22 PM 30/11/2011, Net Newbie wrote: FB 2.5 I need to insert data with a script. but get an error Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 2, column 1. INSERT. the script itself is like below- any ideas what is wrong? INSERT INTO CAR_MARK (car_mark_id, car_mark_name) VALUES (1, 'test '); INSERT INTO CAR_MARK (car_mark_id, car_mark_name) VALUES (2, 'test two'); INSERT INTO CAR_MARK (car_mark_id, car_mark_name) VALUES (3, 'test three'); Show us what's at the end of line 1 ! ./hb
[firebird-support] Application is very slow while connecting to Firebird database
Hi All, Application is very slow while connecting Firebird database version 2.5.0 using the .Net Provider 2.5.1. Please have a look at the snap shot of database what I am using. I have changed the Page buffers size from default 0 to 4000 to check the performance of the database, but I could not get any thing extra. Shall I do anything else with the firebird.conf file for setting up the database? While installing the database, it is taking default value and I have not done any setup for this, if it require please guide me how I ca do that.Please guide me how to achieve the better performance in firebird. Same application is running fine while connecting the SQL server 2008. Please let me know if you want anything about database, for checking the performance of it. Expecting your valuable suggestion on this. Thanks, Mahesh Pratihari Sonata Software Limited Phone : +91 80 3097 1570 Mobile : +91 99808 37446 www.sonata-software.com Please don't print this email unless you really need to. This will preserve trees on our planet. [Non-text portions of this message have been removed]
