Em 23/11/2016 08:10, Adriano dos Santos Fernandes escreveu:
> On 23/11/2016 06:15, Roman Simakov wrote:
>> 2016-11-23 2:26 GMT+03:00 Leyne, Sean <s...@broadviewsoftware.com>:
>>> I can't imagine how the engine could manage to keep the schema/object cache
>>> and transaction context in sync or, in the absence of that, to 'broadcast'
>>> that schema has changed and any existing cached object should be released
>>> as soon as possible, to force the object definition to be reloaded.
>> Why not to use the same sync objects? :) Locks, ASTs, flags of
>> invalidated cache?
>> The table t does know about new record format. The view v also must know it.
>>
>> I suppose it's not so easy but I agree it must be.
>>
> See "Metadata locking policy and caching" in fb-architect archives.
>
> Seems fb devs did prefer what works (laugh) currently than implement a
> reliable solution.
>
> However, it seems for me that Alexandre's problem is not what Sean
> talked. The problem him talked occurs even after disconnect and
> reconnect, no?
>
>
> Adriano
>
Thanks for the attention....
I can assure that the error happens even after a disconnect/reconnect
cycle, because the change I did was months ago, and just got the error
two days ago when a new record was inserted and the length is greater
than the previous size...
But I was just confirming it and I got no error, see:
C:\bd>\Fb25\bin\isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database "foo" user "sysdba" password "masterkey";
SQL> show database;
Database: foo
Owner: SYSDBA
PAGE_SIZE 4096
Number of DB pages allocated = 196
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 1
Transaction - oldest active = 2
Transaction - oldest snapshot = 2
Transaction - Next = 5
ODS = 11.2
Default Character set: NONE
SQL>
SQL> create table t(a char(1));
SQL> commit;
SQL>
SQL> insert into t values ('a');
SQL> commit;
SQL>
SQL> select * from t;
A
======
a
SQL> commit;
SQL>
SQL> create view v as select a from t;
SQL> commit;
SQL>
SQL> select * from v;
A
======
a
SQL>
SQL> alter table t alter column a type char(2);
SQL> commit;
SQL>
SQL> select * from t;
A
======
a
SQL> select * from v;
A
======
a
SQL>
SQL> update t set a = 'aa';
SQL> commit;
SQL>
SQL> select * from t;
A
======
aa
SQL>
SQL> select * from v;
A
======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL> quit;
C:\bd>\Fb25\bin\isql foo -user sysdba -password masterkey
Database: foo, User: sysdba
SQL> select * from v;
A
======
aa
SQL>
That puzzled me, and I did a test case that mimics the real problem, the
above case was a simplification.
Confirmed... I happens even after a disconnect/reconnect cycle, with
just one logged user.... see:
C:\bd>\Fb25\bin\isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database "foo" user "sysdba" password "masterkey";
SQL> show database;
Database: foo
Owner: SYSDBA
PAGE_SIZE 4096
Number of DB pages allocated = 196
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 1
Transaction - oldest active = 2
Transaction - oldest snapshot = 2
Transaction - Next = 5
ODS = 11.2
Default Character set: NONE
SQL>
SQL> create table t(a char(1));
SQL> commit;
SQL>
SQL> alter table t add b computed by ((select first 1 t2.a from t t2
where t2.a = t.a));
SQL>
SQL> insert into t values ('a');
SQL> commit;
SQL>
SQL> select * from t;
A B
====== ======
a a
SQL> commit;
SQL>
SQL> create view v as select a from t;
SQL> commit;
SQL>
SQL> select * from v;
A
======
a
SQL>
SQL> create view v2 (a) as
CON> select
CON> (select first 1 t2.a from t t2 where t2.a = t1.a)
CON> from
CON> t t1;
SQL>
SQL> commit;
SQL>
SQL> alter table t alter column a type char(2);
SQL> commit;
SQL>
SQL> select * from t;
A B
====== ======
a a
SQL> select * from v;
A
======
a
SQL> select * from v2;
A
======
a
SQL>
SQL> update t set a = 'aa';
SQL> commit;
SQL>
SQL> select * from t;
A B
====== ======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL>
SQL> select * from v;
A
======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL>
SQL> select * from v2;
A
======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL> quit;
C:\bd>\Fb25\bin\isql foo -user sysdba -password masterkey
Database: foo, User: sysdba
SQL> select * from t;
A B
====== ======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL> select * from v;
A
======
aa
SQL> select * from v2;
A
======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL>
to resolve the above error I need to:
C:\bd>\Fb25\bin\isql foo -user sysdba -password masterkey
Database: foo, User: sysdba
SQL> alter table t drop b;
SQL> alter table t add b computed by ((select first 1 t2.a from t t2
where t2.am= t.a));
SQL> commit;
SQL> select * from t;
A B
====== ======
aa aa
SQL> select * from v;
A
======
aa
SQL> select * from v2;
A
======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL> create or alter view v2 (a) as
CON> select
CON> (select first 1 t2.a from t t2 where t2.a = t1.a)
CON> from
CON> t t1;
SQL>
SQL> commit;
SQL> select * from v2;
A
======
aa
I don't know the FB internals, and perhaps are even using the wrong
term, but somehow, the view thinks that the field still is a char(1)
(record format version ?), and when I select a record with bigger length
the exception is trown in a similar way as the following statement.
SQL> select cast('aa' as char(1)) from rdb$database;
CAST
======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
see you !
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
