Re: [firebird-support] Firebird 2.5 - Alter table - drop default - bug or feature

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 25-4-2017 18:21, Lester Caine les...@lsces.co.uk [firebird-support] 
wrote:
> On 25/04/17 16:50, Mark Rotteveel m...@lawinegevaar.nl
> [firebird-support] wrote:
>> The SQL standard is quite clear at what needs to happen: adding a new
>> column with a default should behave as if that column has existed from
>> the original create table (btw: irrespective of the NOT NULL constraint,
>> something that if I'm not mistaken also Firebird doesn't do correctly).
>
> Exactly the problem in my book. If you add 'DEFAULT' without a 'NOT
> NULL', then in my book the unpopulated fields should remain NULL unless
> I populate them. When you now add creating the field later then things
> should be consistent but there is no consistent way of handling things
> based on the SQL standard inconsistencies.

I find the opposite a lot more logical. I create a new field with a 
default, the existing records should get the value with that default. If 
you want the value of a field to be null for existing rows, then first 
add it without a default (which implies default null), and then alter 
the default.

This is what the SQL:2011 standard says:
"""
2) Let C be the column added to T.
 Case:
 a) [about generated columns]
[..]
 b) Otherwise, C is a base column.
  Case:
  i) [about identity columns]
[..]
  ii) Otherwise, every value in C is the default value 
for C.
[..]
4) In all other respects, the specification of a  in 
an  has the same effect as specification of the 
 in the  for T would have had.
"""

>> This has the effect that all existing records should get the default
>> value as if they had that value from the start (either stored or
>> virtual). It also means that a subsequent alteration of the default (new
>> value or dropping the default), should not lead to changes to the value
>> of those rows that existed before the column was added with; the columns
>> need to retain the original default.
>
> The argument that other engines put forward is this idea that a record
> does not need to store a full set of fields, some can be 'virtual' and
> only exist when something is stored in them. I HOPE that this is not
> something that Firebird plans to adopt? In my book the 'original value'
> is always 'NULL' unless other rules require something replaces it, and
> an empty field magically showing some default value is not a safe way of
> working.

You are misinterpreting my words. With virtual I mean that the value 
might not yet be actually persisted with the record itself, but that for 
all intents and purposes the system behaves as if it **is**. This is BTW 
what Firebird currently does with format versions.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Firebird 2.5 - Alter table - drop default - bug or feature

[Maxi maxiroba...@gmail.com [firebird-support]]

2017-04-25 11:24 GMT-03:00 Svein Erling Tysvær setys...@gmail.com
[firebird-support] :

>
>
> Hi everyone,
>>
>> I encountered strange behavior droping a column definition with default
>> value.
>>
>> The environment is Ubuntu 16.04 LTS 64 bits, Firebird version
>> LI-V2.5.6.27020 (Firebird 2.5 SuperClassic)
>>
>>
>> I'll try to explain with an example. The statement sequence is:
>>
>> 1. Create the table
>> CREATE TABLE "SCHEMA_AUTHOR" ("ID" integer NOT NULL PRIMARY KEY, "NAME"
>> varchar(255) NOT NULL, "HEIGHT" integer CHECK ("HEIGHT" >= 0))
>>
>> 2. Popultate with some data
>> INSERT INTO SCHEMA_AUTHOR (ID, NAME, HEIGHT) VALUES ('1', 'Anonimus1',
>> NULL);
>>
>> 3. Alter the table
>> ALTER TABLE "SCHEMA_AUTHOR" ADD "SURNAME" varchar(15) DEFAULT 'surname
>> default' NOT NULL
>>
>> After step 3, I have a record with a new column with 'surname default'
>> value into SURNAME field.
>>
>> 4. Drop default definition
>> ALTER TABLE "SCHEMA_AUTHOR" ALTER COLUMN "SURNAME" DROP DEFAULT
>>
>> After step 4, the SURNAME field value is setting to empty string ('')
>>
>> Is that the correct behavior ?
>>
>
> I think you're wrong in assuming that the record after step 3 contains
> 'surname default'. Please try
>
> select iif( "SURNAME" is null, 'I'm empty', 'I contain a value')
> from SCHEMA_AUTHOR
>

After step 3, that select returns "I contain a value".
Kinda confusing don't?

Ok, to add a new NOT NULL field with a DEFAULT definition should not
populate the existing rows.



> after step 3. I don't use Firebird 3 myself, but I'd be greatly surprised
> if they'd changed so that existing records got default values when defining
> the default values. Also, take a look at this old document:
> https://firebirdsql.org/manual/nullguide-alter-pop-tables.html.
>


I'm using Firebird 2.5 (I dont test this in firebird 3).



>
> Basically, what you should do is to add another step after step 3:
>
> UPDATE SCHEMA_AUTHOR
>SET "SURNAME" = 'surname default';
>
>
Yes, this seem like ever we need to run an update to populate the not null
field, despite we set this with a default.

Now it seems a little more clear for me.

Regards
Maxi

Re: [firebird-support] Firebird 2.5 - Alter table - drop default - bug or feature

[Lester Caine les...@lsces.co.uk [firebird-support]]

On 25/04/17 16:50, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] wrote:
> The SQL standard is quite clear at what needs to happen: adding a new 
> column with a default should behave as if that column has existed from 
> the original create table (btw: irrespective of the NOT NULL constraint, 
> something that if I'm not mistaken also Firebird doesn't do correctly).

Exactly the problem in my book. If you add 'DEFAULT' without a 'NOT
NULL', then in my book the unpopulated fields should remain NULL unless
I populate them. When you now add creating the field later then things
should be consistent but there is no consistent way of handling things
based on the SQL standard inconsistencies.

> This has the effect that all existing records should get the default 
> value as if they had that value from the start (either stored or 
> virtual). It also means that a subsequent alteration of the default (new 
> value or dropping the default), should not lead to changes to the value 
> of those rows that existed before the column was added with; the columns 
> need to retain the original default.

The argument that other engines put forward is this idea that a record
does not need to store a full set of fields, some can be 'virtual' and
only exist when something is stored in them. I HOPE that this is not
something that Firebird plans to adopt? In my book the 'original value'
is always 'NULL' unless other rules require something replaces it, and
an empty field magically showing some default value is not a safe way of
working.

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

Re: [firebird-support] Firebird 2.5 - Alter table - drop default - bug or feature

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 25-4-2017 17:43, Lester Caine les...@lsces.co.uk [firebird-support] 
wrote:
> On 25/04/17 12:51, Maxi maxiroba...@gmail.com [firebird-support] wrote:
>> I expect the value assigned by DEFAULT  when I add the new field is
>> still there.
>
> There has been various discussions on this area ...
>
> If you add 'NOT NULL' to an existing field, then you need to populate
> the existing fields with a value in the absence of a default. In my book
> the existing records start the update for adding a new field with simple
> new null records, and FROM A GOOD PRACTICE VIEW I would expect to
> populate these empty fields under my control, so the idea that adding
> the 'DEFAULT' to a field THEN populates existing fields 'magically' is
> wrong, and the way the 'magic' bit would work depends on the order you
> process things anyway.
>
> It's not a 'bug' but rather preference and I'm with SET that the process
> should include a managed UPDATE rather than a magic one ...

This is not about adding a NOT NULL or a default to an existing field, 
it is about adding a whole new column (not null) with a default to an 
existing table, and then dropping that default.

The SQL standard is quite clear at what needs to happen: adding a new 
column with a default should behave as if that column has existed from 
the original create table (btw: irrespective of the NOT NULL constraint, 
something that if I'm not mistaken also Firebird doesn't do correctly).

This has the effect that all existing records should get the default 
value as if they had that value from the start (either stored or 
virtual). It also means that a subsequent alteration of the default (new 
value or dropping the default), should not lead to changes to the value 
of those rows that existed before the column was added with; the columns 
need to retain the original default.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Firebird 2.5 - Alter table - drop default - bug or feature

[Lester Caine les...@lsces.co.uk [firebird-support]]

On 25/04/17 12:51, Maxi maxiroba...@gmail.com [firebird-support] wrote:
> I expect the value assigned by DEFAULT  when I add the new field is
> still there.

There has been various discussions on this area ...

If you add 'NOT NULL' to an existing field, then you need to populate
the existing fields with a value in the absence of a default. In my book
the existing records start the update for adding a new field with simple
new null records, and FROM A GOOD PRACTICE VIEW I would expect to
populate these empty fields under my control, so the idea that adding
the 'DEFAULT' to a field THEN populates existing fields 'magically' is
wrong, and the way the 'magic' bit would work depends on the order you
process things anyway.

It's not a 'bug' but rather preference and I'm with SET that the process
should include a managed UPDATE rather than a magic one ...

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

Re: [firebird-support] Immediate Detection of Disconnected Clients on Classic

[hv...@users.sourceforge.net [firebird-support]]

---In firebird-support@yahoogroups.com,  wrote :

On 24.04.2017 o 10:41, atunccekic@... mailto:atunccekic@... [firebird-support] 
wrote:
 

 > We have some desktop applications which are connecting to firebird, started 
 > crashing for a while.
 > These applications are mostly on our wireless link connected building so I'm 
 > suspicious about the network.
 > Then I created a new test project. When I run the application, connect to 
 > the database, disconnect the client network just for a second and reconnect 
 > the network again, the database connection is broken and the application 
 > can't continue.
 > Then I saw a new feature on firebird 2.5 like this:
 > Immediate Detection of Disconnected Clients on Classic Vladyslav Khorsun
 > The Classic server now detects immediately when a Classic process has been 
 > broken by a client disconnection. Its response is to terminate any pending 
 > activity, roll back the active transaction and close the network connection. 
 > Tracker reference CORE-818.
 > Can this be the reason that a short interruption on the network causing the 
 > problems on the application?
 > Can this feaure be disabled so that I can see if this is the problem or not?

 
 Hi,
 
 My answer is based more on the way the networs work than the actual 
 knowledge of FB internals. If you work on a wired network and the 
 network interface is set up to auto detect the link availability (in 
 most cases it is), pulling the network plug out and connecting it back 
 causes the network interface to reset (all sockets are closed, the 
 interface is set up from zero). However, if the network is disrupted 
 somewhere in between the end points (your server and client) but both of 
 them have the physical link up, both the server and the client keep the 
 sockets open (eventually re-transmitting TCP packets after some 
 timeouts), because they simply can't know why there's no traffic on the 
 link - failure or just nothing to say ;). When the network gets 
 operatinal again, it's possible that the database connection actually is 
 still valid and the app keeps working like nothing happened.
 In case of wireless, every disconnection is like pulling the plug - the 
 interface resets and all sockets are closed. That might explain why your 
 crashes occur mostly on clients connected via wifi.


   Very good and correct explanation

 
 I don't actually think Firebird can detect client disconnection 
 "immediately" when there is no data exchange between the server and the 
 client (it would require some kind of heartbeat increasing traffic and 
 would be based on timeouts, which are tricky), otherwise byzantine 
 network partitions wouldn't be so nasty.


   Firebird can do it even if there is no data exchange because it always 
listens all
known client socket. When OS closed socket it is detected by Firebird listener.

Regards,
Vlad

 

[firebird-support] Re: Firebird 2.5 - Alter table - drop default - bug or feature

[hv...@users.sourceforge.net [firebird-support]]

---In firebird-support@yahoogroups.com,  wrote :
 
 Hi everyone,
 I encountered strange behavior droping a column definition with default value.

 The environment is Ubuntu 16.04 LTS 64 bits, Firebird version LI-V2.5.6.27020 
(Firebird 2.5 SuperClassic)
 

 

 I'll try to explain with an example. The statement sequence is:
 

 1. Create the table
 CREATE TABLE "SCHEMA_AUTHOR" ("ID" integer NOT NULL PRIMARY KEY, "NAME" 
varchar(255) NOT NULL, "HEIGHT" integer CHECK ("HEIGHT" >= 0))

 

 2. Popultate with some data
 INSERT INTO SCHEMA_AUTHOR (ID, NAME, HEIGHT) VALUES ('1', 'Anonimus1', NULL);

 

 3. Alter the table
 ALTER TABLE "SCHEMA_AUTHOR" ADD "SURNAME" varchar(15) DEFAULT 'surname 
default' NOT NULL

 

 After step 3, I have a record with a new column with 'surname default' value 
into SURNAME field.
 

 4. Drop default definition
 ALTER TABLE "SCHEMA_AUTHOR" ALTER COLUMN "SURNAME" DROP DEFAULT

 

 After step 4, the SURNAME field value is setting to empty string ('')
 

 Is that the correct behavior ?
 

It should be fixed in FB3, AFAIU

Regards,
Vlad

Re: [firebird-support] Firebird 2.5 - Alter table - drop default - bug or feature

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 24-4-2017 20:50, Maxi maxiroba...@gmail.com [firebird-support] wrote:
>
>
> Hi everyone,
>
> I encountered strange behavior droping a column definition with default
> value.
>
> The environment is Ubuntu 16.04 LTS 64 bits, Firebird version
> LI-V2.5.6.27020 (Firebird 2.5 SuperClassic)
>
>
> I'll try to explain with an example. The statement sequence is:
>
> 1. Create the table
> CREATE TABLE "SCHEMA_AUTHOR" ("ID" integer NOT NULL PRIMARY KEY, "NAME"
> varchar(255) NOT NULL, "HEIGHT" integer CHECK ("HEIGHT" >= 0))
>
> 2. Popultate with some data
> INSERT INTO SCHEMA_AUTHOR (ID, NAME, HEIGHT) VALUES ('1', 'Anonimus1',
> NULL);
>
> 3. Alter the table
> ALTER TABLE "SCHEMA_AUTHOR" ADD "SURNAME" varchar(15) DEFAULT 'surname
> default' NOT NULL
>
> After step 3, I have a record with a new column with 'surname default'
> value into SURNAME field.
>
> 4. Drop default definition
> ALTER TABLE "SCHEMA_AUTHOR" ALTER COLUMN "SURNAME" DROP DEFAULT
>
> After step 4, the SURNAME field value is setting to empty string ('')
>
> Is that the correct behavior ?

Yes and no. Yes this is how Firebird works, and no it doesn't conform to 
the behavior expected by the SQL standard. Firebird 3 does it better, 
but some people consider that to be a bug (see CORE-5507 and the 
accompanying discussion on firebird-devel).

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Firebird 2.5 - Alter table - drop default - bug or feature

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

>
> Hi everyone,
>
> I encountered strange behavior droping a column definition with default
> value.
>
> The environment is Ubuntu 16.04 LTS 64 bits, Firebird version
> LI-V2.5.6.27020 (Firebird 2.5 SuperClassic)
>
>
> I'll try to explain with an example. The statement sequence is:
>
> 1. Create the table
> CREATE TABLE "SCHEMA_AUTHOR" ("ID" integer NOT NULL PRIMARY KEY, "NAME"
> varchar(255) NOT NULL, "HEIGHT" integer CHECK ("HEIGHT" >= 0))
>
> 2. Popultate with some data
> INSERT INTO SCHEMA_AUTHOR (ID, NAME, HEIGHT) VALUES ('1', 'Anonimus1',
> NULL);
>
> 3. Alter the table
> ALTER TABLE "SCHEMA_AUTHOR" ADD "SURNAME" varchar(15) DEFAULT 'surname
> default' NOT NULL
>
> After step 3, I have a record with a new column with 'surname default'
> value into SURNAME field.
>
> 4. Drop default definition
> ALTER TABLE "SCHEMA_AUTHOR" ALTER COLUMN "SURNAME" DROP DEFAULT
>
> After step 4, the SURNAME field value is setting to empty string ('')
>
> Is that the correct behavior ?
>

I think you're wrong in assuming that the record after step 3 contains
'surname default'. Please try

select iif( "SURNAME" is null, 'I'm empty', 'I contain a value')
from SCHEMA_AUTHOR

after step 3. I don't use Firebird 3 myself, but I'd be greatly surprised
if they'd changed so that existing records got default values when defining
the default values. Also, take a look at this old document:
https://firebirdsql.org/manual/nullguide-alter-pop-tables.html.

Basically, what you should do is to add another step after step 3:

UPDATE SCHEMA_AUTHOR
   SET "SURNAME" = 'surname default';

HTH,
Set

Re: [firebird-support] Immediate Detection of Disconnected Clients on Classic

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 24.04.2017 o 10:41, atuncce...@duzen.com.tr [firebird-support] wrote:

>   We have some desktop applications which are connecting to firebird, started 
> crashing for a while.
>   These applications are mostly on our wireless link connected building so 
> I'm suspicious about the network.
>   Then I created a new test project. When I run the application, connect to 
> the database, disconnect the client network just for a second and reconnect 
> the network again,  the database connection is broken and the application 
> can't continue.
>   Then I saw a new feature on firebird 2.5 like this:
>   Immediate Detection of Disconnected Clients on Classic Vladyslav Khorsun
>   The Classic server now detects immediately when a Classic process has been 
> broken by a client disconnection. Its response is to terminate any pending 
> activity, roll back the active transaction and close the network connection. 
> Tracker reference CORE-818.
>   Can this be the reason that a short interruption on the network causing the 
> problems on the application?
>   Can this feaure be disabled so that I can see if this is the problem or not?

Hi,

My answer is based more on the way the networs work than the actual 
knowledge of FB internals. If you work on a wired network and the 
network interface is set up to auto detect the link availability (in 
most cases it is), pulling the network plug out and connecting it back 
causes the network interface to reset (all sockets are closed, the 
interface is set up from zero). However, if the network is disrupted 
somewhere in between the end points (your server and client) but both of 
them have the physical link up, both the server and the client keep the 
sockets open (eventually re-transmitting TCP packets after some 
timeouts), because they simply can't know why there's no traffic on the 
link - failure or just nothing to say ;). When the network gets 
operatinal again, it's possible that the database connection actually is 
still valid and the app keeps working like nothing happened.
In case of wireless, every disconnection is like pulling the plug - the 
interface resets and all sockets are closed. That might explain why your 
crashes occur mostly on clients connected via wifi.
I don't actually think Firebird can detect client disconnection 
"immediately" when there is no data exchange between the server and the 
client (it would require some kind of heartbeat increasing traffic and 
would be based on timeouts, which are tricky), otherwise byzantine 
network partitions wouldn't be so nasty.

hope this helps

cheers
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Firebird 2.5 - Alter table - drop default - bug or feature

[Maxi maxiroba...@gmail.com [firebird-support]]

2017-04-24 16:39 GMT-03:00 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support] :

>
>
>
>
> > 3. Alter the table
> > ALTER TABLE "SCHEMA_AUTHOR" ADD "SURNAME" varchar(15) DEFAULT
> > 'surname default' NOT NULL
> >
> > After step 3, I have a record with a new column with 'surname default'
> value
> > into SURNAME field.
> >
> > 4. Drop default definition
> > ALTER TABLE "SCHEMA_AUTHOR" ALTER COLUMN "SURNAME" DROP
> > DEFAULT
> >
> > After step 4, the SURNAME field value is setting to empty string ('')
> >
> > Is that the correct behavior ?
>
> You dropped the "DEFAULT", what would you expect the system to assign?
>
> __,_._,_
>

I expect the value assigned by DEFAULT  when I add the new field is still
there.

I see what there are two different behaviors

1. Adding a new not null field with default value to already populate table
2. Adding a new record (after add the field with default)

Then, when I drop DEFAULT, all pre-existing records lost the value but new
record inserted after, maintains the value assigned by default