hi all
Создал две базы, одну на LI-T3.0.0.31056, вторую на LI-V2.5.3.26744.
К обеим подключаюсь клиентом 2.5.
В обеих базах ввожу вот этот DDL: 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
set term ^;
create or alter trigger trg_connect active on connect position 0 as begin end
^
create or alter trigger trg_disconnect active on disconnect position 0 as begin end
^
set term ;^
commit;
--------------------------
recreate table attach_intro(
ip varchar(15),
att int default current_connection,
dts timestamp default 'now',
constraint attach_intro primary key(ip, att) using index pk_attach_intro
);
commit;
--------------------------
set term ^;
create or alter trigger trg_connect active on connect position 0 as
begin
merge into attach_intro t
using (select rdb$get_context('SYSTEM','CLIENT_ADDRESS') ip, current_connection att from rdb$database) s
on t.ip = s.ip and t.att = s.att
when matched then update set dts = 'now'
when NOT matched then insert(ip) values(rdb$get_context('SYSTEM','CLIENT_ADDRESS') );
end^
create or alter trigger trg_disconnect active on disconnect position 0 as
begin
--delete from attach_intro t where ip=rdb$get_context('SYSTEM','CLIENT_ADDRESS') and att = current_connection;
delete from attach_intro t where att = current_connection;
end^
set term ;^
commit;
(то есть, делается попытка регистрировать момент входа и момента выхода; регистрация вЫхода, кстати , не сработает при грохании коннекта через mon$attach, но "щаз не об этом").
Далее проверяю на 2.5 : 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3253:/var/db/fb25/tmp25.fdb -n
Database: 192.168.0.220/3253:/var/db/fb25/tmp25.fdb
SQL> show trigger trg_connect;
TRG_CONNECT, Sequence: 0, Type: ON CONNECT, Active
as
begin
merge into attach_intro t
using (select rdb$get_context('SYSTEM','CLIENT_ADDRESS') ip, current_connection att from rdb$database) s
on t.ip = s.ip and t.att = s.att
when matched then update set dts = 'now'
when NOT matched then insert(ip) values(rdb$get_context('SYSTEM','CLIENT_ADDRESS') );
end
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> show trigger trg_disconnect;
TRG_DISCONNECT, Sequence: 0, Type: ON DISCONNECT, Active
as
begin
--delete from attach_intro t where ip=rdb$get_context('SYSTEM','CLIENT_ADDRESS') and att = current_connection;
delete from attach_intro t where att = current_connection;
end
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- обнуляем на всякий случай, и выходим; проверим содержимое на след. коннектах
SQL> delete from attach_intro; commit; exit;
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3253:/var/db/fb25/tmp25.fdb -n
Database: 192.168.0.220/3253:/var/db/fb25/tmp25.fdb
SQL> select current_connection, a.* from attach_intro a;
CURRENT_CONNECTION IP ATT DTS
================== =============== ============ =========================
18 192.168.43.96 18 2014-04-18 21:09:37.9090
SQL> exit;
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3253:/var/db/fb25/tmp25.fdb -n
Database: 192.168.0.220/3253:/var/db/fb25/tmp25.fdb
SQL> select current_connection, a.* from attach_intro a;
CURRENT_CONNECTION IP ATT DTS
================== =============== ============ =========================
19 192.168.43.96 19 2014-04-18 21:09:52.2130
SQL> exit;
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3253:/var/db/fb25/tmp25.fdb -n
Database: 192.168.0.220/3253:/var/db/fb25/tmp25.fdb
SQL> quit;
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3253:/var/db/fb25/tmp25.fdb -n
Database: 192.168.0.220/3253:/var/db/fb25/tmp25.fdb
SQL> select current_connection, a.* from attach_intro a;
CURRENT_CONNECTION IP ATT DTS
================== =============== ============ =========================
21 192.168.43.96 21 2014-04-18 21:10:02.1430
SQL> quit;
Как видим, "нормальное" завершение коннекта действительно удаляет запись в attach_intro.
Теперь то же самое на 3.0: 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3333:/var/db/fb30/tmp30.fdb -n
Database: 192.168.0.220/3333:/var/db/fb30/tmp30.fdb
SQL> show trigger trg_connect;
TRG_CONNECT, Sequence: 0, Type: ON CONNECT, Active
as
begin
merge into attach_intro t
using (select rdb$get_context('SYSTEM','CLIENT_ADDRESS') ip, current_connection att from rdb$database) s
on t.ip = s.ip and t.att = s.att
when matched then update set dts = 'now'
when NOT matched then insert(ip) values(rdb$get_context('SYSTEM','CLIENT_ADDRESS') );
end
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> show trigger trg_disconnect;
TRG_DISCONNECT, Sequence: 0, Type: ON DISCONNECT, Active
as
begin
--delete from attach_intro t where ip=rdb$get_context('SYSTEM','CLIENT_ADDRESS') and att = current_connection;
delete from attach_intro t where att = current_connection;
end
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> delete from attach_intro; commit; exit;
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3333:/var/db/fb30/tmp30.fdb -n
Database: 192.168.0.220/3333:/var/db/fb30/tmp30.fdb
SQL> select current_connection, a.* from attach_intro a;
CURRENT_CONNECTION IP ATT DTS
================== =============== ============ =========================
79 192.168.43.96 79 2014-04-18 21:13:19.5050
SQL> exit;
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3333:/var/db/fb30/tmp30.fdb -n
Database: 192.168.0.220/3333:/var/db/fb30/tmp30.fdb
SQL> select current_connection, a.* from attach_intro a;
CURRENT_CONNECTION IP ATT DTS
================== =============== ============ =========================
82 192.168.43.96 82 2014-04-18 21:13:29.2530
82 192.168.43.96 79 2014-04-18 21:13:19.5050
SQL> quit;
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3333:/var/db/fb30/tmp30.fdb -n
Database: 192.168.0.220/3333:/var/db/fb30/tmp30.fdb
SQL> ^C
C:\MIX\firebird\OLTPTEST>isql 192.168.0.220/3333:/var/db/fb30/tmp30.fdb -n
Database: 192.168.0.220/3333:/var/db/fb30/tmp30.fdb
SQL> select current_connection, a.* from attach_intro a;
CURRENT_CONNECTION IP ATT DTS
================== =============== ============ =========================
88 192.168.43.96 82 2014-04-18 21:13:29.2530
88 192.168.43.96 85 2014-04-18 21:13:37.0130
88 192.168.43.96 88 2014-04-18 21:13:46.4160
88 192.168.43.96 79 2014-04-18 21:13:19.5050
Как видно, записи из лога НЕ удаляются!
Не поверив своим глазам, натравил трейсы на isql + немедленный exit. trace 2.5 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
Trace session ID 1 started
2014-04-18T21:23:58.4550 (11239:0x7f02be608910) TRACE_INIT
SESSION_1
2014-04-18T21:23:58.4550 (11239:0x7f02be608910) ATTACH_DATABASE
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
2014-04-18T21:23:58.4550 (11239:0x7f02be608910) START_TRANSACTION
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_100, CONCURRENCY | WAIT | READ_WRITE)
2014-04-18T21:23:58.4560 (11239:0x7f02be608910) EXECUTE_TRIGGER_START
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_100, CONCURRENCY | WAIT | READ_WRITE)
TRG_CONNECT (ON CONNECT)
2014-04-18T21:23:58.4560 (11239:0x7f02be608910) EXECUTE_TRIGGER_FINISH
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_100, CONCURRENCY | WAIT | READ_WRITE)
TRG_CONNECT (ON CONNECT)
0 ms, 1 read(s), 13 fetch(es), 2 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$DATABASE 1
ATTACH_INTRO 1
2014-04-18T21:23:58.4560 (11239:0x7f02be608910) COMMIT_TRANSACTION
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_100, CONCURRENCY | WAIT | READ_WRITE)
0 ms
2014-04-18T21:23:58.4590 (11239:0x7f02be608910) START_TRANSACTION
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_101, CONCURRENCY | WAIT | READ_WRITE)
2014-04-18T21:24:04.3370 (11239:0x7f02be608910) COMMIT_TRANSACTION
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_101, CONCURRENCY | WAIT | READ_WRITE)
0 ms, 1 write(s), 1 fetch(es), 1 mark(s)
2014-04-18T21:24:04.3370 (11239:0x7f02be608910) START_TRANSACTION
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_102, CONCURRENCY | WAIT | READ_WRITE)
2014-04-18T21:24:04.3370 (11239:0x7f02be608910) EXECUTE_TRIGGER_START
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_102, CONCURRENCY | WAIT | READ_WRITE)
TRG_DISCONNECT (ON DISCONNECT)
2014-04-18T21:24:04.3390 (11239:0x7f02be608910) EXECUTE_TRIGGER_FINISH
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_102, CONCURRENCY | WAIT | READ_WRITE)
TRG_DISCONNECT (ON DISCONNECT)
1 ms, 3 read(s), 54 fetch(es), 4 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$INDICES 1
RDB$RELATIONS 3
RDB$TRIGGERS 2
RDB$RELATION_CONSTRAINTS 1
ATTACH_INTRO 2 1 1
2014-04-18T21:24:04.3390 (11239:0x7f02be608910) COMMIT_TRANSACTION
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_102, CONCURRENCY | WAIT | READ_WRITE)
0 ms
2014-04-18T21:24:04.3390 (11239:0x7f02be608910) DETACH_DATABASE
/var/db/fb25/tmp25.fdb (ATT_25, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
2014-04-18T21:24:04.3390 (11239:0x7f02be608910) TRACE_FINI
SESSION_1
trace 3.0 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
Trace session ID 12 started
2014-04-18T21:21:02.2970 (7295:0x7f386b1f95c0) TRACE_INIT
SESSION_12
2014-04-18T21:21:02.2970 (7295:0x7f386b1f95c0) ATTACH_DATABASE
/var/db/fb30/tmp30.fdb (ATT_91, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
2014-04-18T21:21:02.2980 (7295:0x7f386b1f95c0) START_TRANSACTION
/var/db/fb30/tmp30.fdb (ATT_91, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_106, CONCURRENCY | WAIT | READ_WRITE)
2014-04-18T21:21:02.2990 (7295:0x7f386b1f95c0) EXECUTE_TRIGGER_START
/var/db/fb30/tmp30.fdb (ATT_91, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_106, CONCURRENCY | WAIT | READ_WRITE)
TRG_CONNECT (ON CONNECT)
2014-04-18T21:21:02.2990 (7295:0x7f386b1f95c0) EXECUTE_TRIGGER_FINISH
/var/db/fb30/tmp30.fdb (ATT_91, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_106, CONCURRENCY | WAIT | READ_WRITE)
TRG_CONNECT (ON CONNECT)
0 ms, 1 read(s), 13 fetch(es), 2 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$DATABASE 1
ATTACH_INTRO 1
2014-04-18T21:21:02.2990 (7295:0x7f386b1f95c0) COMMIT_TRANSACTION
/var/db/fb30/tmp30.fdb (ATT_91, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_106, CONCURRENCY | WAIT | READ_WRITE)
0 ms
2014-04-18T21:21:02.3020 (7295:0x7f386b1f95c0) START_TRANSACTION
/var/db/fb30/tmp30.fdb (ATT_91, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_107, CONCURRENCY | WAIT | READ_WRITE)
2014-04-18T21:21:13.0720 (7295:0x7f386b1f95c0) COMMIT_TRANSACTION
/var/db/fb30/tmp30.fdb (ATT_91, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
(TRA_107, CONCURRENCY | WAIT | READ_WRITE)
0 ms, 1 write(s), 1 fetch(es), 1 mark(s)
2014-04-18T21:21:13.0720 (7295:0x7f386b1f95c0) DETACH_DATABASE
/var/db/fb30/tmp30.fdb (ATT_91, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:3704
2014-04-18T21:21:13.0720 (7295:0x7f386b1f95c0) TRACE_FINI
SESSION_12
Результаты превосходят ожидания: в 3.0 триггер на DISconnect... не срабатывает. Вообще :'(((
ЧЯДНТ ? |
