firebird-devel

[Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Hi!

I'm implementing what's described in subj. Now, user can already do this, but it's not good.

When engine reads a NOT NULL field with a DEFAULT, it executed the DEFAULT expression and returns a value.

For CURRENT_DATE/TIME/TIMESTAMP expressions, it returns the "0 value", with for dates means 1858-11-17. Only simple expressions are allowed for DEFAULT now, with means more problems if complex expressions are allowed.

Initial verification to implement the thing in a good way has to populate the fields in DFW, after the new format has been created. This does not solve the problem completely. There can be snapshot transactions that see the new field but can't see its default value.

Related to above, I tried to update records in system transaction. Initially works in some condition, but I don't think it's good way to go either.

So I think on an alternate way. At field creation time, its default value may be written in RDB$FORMATS. When reading a NOT NULL field with a DEFAULT, the value written in the format version of the record is returned. This may solve the problem of complex expressions, the problem of transaction visibility, and don't need an update on the whole table. I've no code to prove this, it's just an idea.

What do you think?


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Ann W. H. <aha...@ib...>]

Adriano dos Santos Fernandes wrote:
> 
> So I think on an alternate way. At field creation time, its default 
> value may be written in RDB$FORMATS. When reading a NOT NULL field
> with a DEFAULT, the value written in the format version of the
> record is returned. This may solve the problem of complex expressions,
> the problem of transaction visibility, and don't need an update on the
> whole table. I've no code to prove this, it's just an idea.
> 

How would you handle the case of a default of USER or CURRENT_TIME?


Cheers,

Ann

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Ann W. Harrison wrote:
> Adriano dos Santos Fernandes wrote:
>> So I think on an alternate way. At field creation time, its default 
>> value may be written in RDB$FORMATS. When reading a NOT NULL field
>> with a DEFAULT, the value written in the format version of the
>> record is returned. This may solve the problem of complex expressions,
>> the problem of transaction visibility, and don't need an update on the
>> whole table. I've no code to prove this, it's just an idea.
>>
> 
> How would you handle the case of a default of USER or CURRENT_TIME?
> 
For DML, DEFAULT applies in the moment of INSERT.

My feeling is that for DDL, the new field of existing records should have the DEFAULT value evaluated in the moment of the DDL command is executed. Storing it value in the format allows that.

New records can't have the field with NULL, so some other value will be provided by the user or evaluated with the DEFAULT expression as now.


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Ann W. H. <aha...@ib...>]

Adriano dos Santos Fernandes wrote:
> Ann W. Harrison wrote:
>> Adriano dos Santos Fernandes wrote:
>>> So I think on an alternate way. At field creation time, its default 
>>> value may be written in RDB$FORMATS. When reading a NOT NULL field
>>> with a DEFAULT, the value written in the format version of the
>>> record is returned. ...
>>>
>> How would you handle the case of a default of USER or CURRENT_TIME?
>>
> For DML, DEFAULT applies in the moment of INSERT.
> 
> My feeling is that for DDL, the new field of existing records should have the DEFAULT value evaluated in the moment of the DDL command is executed. Storing it value in the format allows that.
> 

Here's the problem I see, and it may exist already.

Create a table, which creates format 1 for that table.
Store a bunch of records.

Add a NOT NULL field with a default value of 'ABC',
which creates format 2 for the table.  The new format
includes the default value.

Now if you read records for that table, the new field's
value is 'ABC'.

Now alter the table, changing the default value to '123',
creating a new format 3 which includes the new default
for that field.

Read the same records again and the value is probably going
to be '123'.


Cheers,

Ann

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Ann W. Harrison wrote:
> 
> Here's the problem I see, and it may exist already.
> 
> Create a table, which creates format 1 for that table.
> Store a bunch of records.
> 
> Add a NOT NULL field with a default value of 'ABC',
> which creates format 2 for the table.  The new format
> includes the default value.
> 
> Now if you read records for that table, the new field's
> value is 'ABC'.
> 
That's ok.

> Now alter the table, changing the default value to '123',
> creating a new format 3 which includes the new default
> for that field.
> 
> Read the same records again and the value is probably going
> to be '123'.
> 
If the record format is 2, it should return 'ABC'. If the record is updated and written in the format 3, 'ABC' should be written to the field so it doesn't catch the format 3 value when reading it.


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Vlad K. <hv...@us...>]

> Ann W. Harrison wrote:
>> 
>> Here's the problem I see, and it may exist already.
>> 
>> Create a table, which creates format 1 for that table.
>> Store a bunch of records.
>> 
>> Add a NOT NULL field with a default value of 'ABC',
>> which creates format 2 for the table.  The new format
>> includes the default value.
>> 
>> Now if you read records for that table, the new field's
>> value is 'ABC'.
>> 
> That's ok.
> 
>> Now alter the table, changing the default value to '123',
>> creating a new format 3 which includes the new default
>> for that field.
>> 
>> Read the same records again and the value is probably going
>> to be '123'.
>> 
> If the record format is 2, it should return 'ABC'. If the record is updated and written in the 
> format 3, 'ABC' should be written to the field so it doesn't catch the format 3 value when 
> reading it.

    What if record still stored in format 1 ?

Regards,
Vlad

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Vlad Khorsun wrote:
>> Ann W. Harrison wrote:
>>> Here's the problem I see, and it may exist already.
>>>
>>> Create a table, which creates format 1 for that table.
>>> Store a bunch of records.
>>>
>>> Add a NOT NULL field with a default value of 'ABC',
>>> which creates format 2 for the table.  The new format
>>> includes the default value.
>>>
>>> Now if you read records for that table, the new field's
>>> value is 'ABC'.
>>>
>> That's ok.
>>
>>> Now alter the table, changing the default value to '123',
>>> creating a new format 3 which includes the new default
>>> for that field.
>>>
>>> Read the same records again and the value is probably going
>>> to be '123'.
>>>
>> If the record format is 2, it should return 'ABC'. If the record is updated and written in the 
>> format 3, 'ABC' should be written to the field so it doesn't catch the format 3 value when 
>> reading it.
> 
>     What if record still stored in format 1 ?
> 
Records at format 1 (which does not have the field added in 2) should use the default value stored in format 2. So 'ABC' should be written when updating to format 3 too.


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Vlad K. <hv...@us...>]

> Vlad Khorsun wrote:
>>> Ann W. Harrison wrote:
>>>> Here's the problem I see, and it may exist already.
>>>>
>>>> Create a table, which creates format 1 for that table.
>>>> Store a bunch of records.
>>>>
>>>> Add a NOT NULL field with a default value of 'ABC',
>>>> which creates format 2 for the table.  The new format
>>>> includes the default value.
>>>>
>>>> Now if you read records for that table, the new field's
>>>> value is 'ABC'.
>>>>
>>> That's ok.
>>>
>>>> Now alter the table, changing the default value to '123',
>>>> creating a new format 3 which includes the new default
>>>> for that field.
>>>>
>>>> Read the same records again and the value is probably going
>>>> to be '123'.
>>>>
>>> If the record format is 2, it should return 'ABC'. If the record is updated and written in the 
>>> format 3, 'ABC' should be written to the field so it doesn't catch the format 3 value when 
>>> reading it.
>> 
>>     What if record still stored in format 1 ?
>> 
> Records at format 1 (which does not have the field added in 2) should use the default value stored in format 2. 
> So 'ABC' should be written when updating to format 3 too.

    We have record, stored in format 1, without additional field. We have formats 2 and 3
with different default values for the same field. When engine read record it converts it into
most current format - format 3 in this case. The question is - what value should be in new
field, added with format 2 ? 'ABC' or '123' ? I think - 'ABC'.

Regards,
Vlad

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Vlad Khorsun wrote:
>> Records at format 1 (which does not have the field added in 2) should use the default value stored in format 2. 
>> So 'ABC' should be written when updating to format 3 too.
> 
>     We have record, stored in format 1, without additional field. We have formats 2 and 3
> with different default values for the same field. When engine read record it converts it into
> most current format - format 3 in this case. The question is - what value should be in new
> field, added with format 2 ? 'ABC' or '123' ? I think - 'ABC'.
> 
That's what I said, no? :-)

So a not-null field with NULL value (or not present), in format N, should return the default value of format (N, N + 1, N + 2, ...) (the first that's found).

To rule above works, the field (not-null, with default) should have its value stored when updating the record to a new format.


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Vlad K. <hv...@us...>]

> Vlad Khorsun wrote:
>>> Records at format 1 (which does not have the field added in 2) should use the default value stored in format 2. 
>>> So 'ABC' should be written when updating to format 3 too.
>> 
>>     We have record, stored in format 1, without additional field. We have formats 2 and 3
>> with different default values for the same field. When engine read record it converts it into
>> most current format - format 3 in this case. The question is - what value should be in new
>> field, added with format 2 ? 'ABC' or '123' ? I think - 'ABC'.
>> 
> That's what I said, no? :-)
> 
> So a not-null field with NULL value (or not present), in format N, should return the default value of format 
> (N, N + 1, N + 2, ...) (the first that's found).

    Yes, and this makes things more complex.

> To rule above works, the field (not-null, with default) should have its value stored when updating the record 
> to a new format.

    This will be satisfied automatically as record must be read before update ;)

Regards,
Vlad

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Vlad Khorsun wrote:
>> Vlad Khorsun wrote:
>>>> Records at format 1 (which does not have the field added in 2) should use the default value stored in format 2. 
>>>> So 'ABC' should be written when updating to format 3 too.
>>>     We have record, stored in format 1, without additional field. We have formats 2 and 3
>>> with different default values for the same field. When engine read record it converts it into
>>> most current format - format 3 in this case. The question is - what value should be in new
>>> field, added with format 2 ? 'ABC' or '123' ? I think - 'ABC'.
>>>
>> That's what I said, no? :-)
>>
>> So a not-null field with NULL value (or not present), in format N, should return the default value of format 
>> (N, N + 1, N + 2, ...) (the first that's found).
> 
>     Yes, and this makes things more complex.
> 
Most of new features adds complexity. :-)

But seems minimal, in very localized places, and should not impact the performance in general.


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Rustam G. <fir...@ma...>]

Adriano > When engine reads a NOT NULL field with a DEFAULT, it
Adriano > executed the DEFAULT expression and returns a value.

Sorry, this is suggested solution or 3.0 behaviour ?

> What do you think?

As for me, the subj is **just not needed**.

a) Changing field from nullable to not nullable has no correlations
with fields default value - field should have the default, bu should
have no default too.

b) I think the solution may be simple - before (when) changing field
type from nullable to not nullable (and for other check too, probably)
checking record values and if some of them has incorrect values -
just bring an exception. Certainly, there can be troubles with records
visibility, exclusive access etc...

WBR, GR

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Rustam Gadjimuradov wrote:
> Adriano > When engine reads a NOT NULL field with a DEFAULT, it
> Adriano > executed the DEFAULT expression and returns a value.
> 
> Sorry, this is suggested solution or 3.0 behaviour ?
> 
>> What do you think?
> 
> As for me, the subj is **just not needed**.
> 
> a) Changing field from nullable to not nullable
...
> 
> b) I think the solution may be simple - before (when) changing field
> type from nullable to not nullable

Looks like you are talking about different subject.


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Rustam G. <fir...@ma...>]

Adriano 
>> b) I think the solution may be simple - before (when) changing field
>> type from nullable to not nullable
> 
> Looks like you are talking about different subject.

You talking about adding new NOT NULL field.
I think, this is one subject with changing field
from nullable to not nullable. Or what is the
difference from your POV ?

Or you want totally move the moment of default
handling - from DML (at INSERT statement) to
DDL ? Hmmm...

WBR, GR

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Vlad K. <hv...@us...>]

> I'm implementing what's described in subj. Now, user can already do this, but it's not good.

    Not sure i understand - what is not good ?
 
> When engine reads a NOT NULL field with a DEFAULT, it executed the DEFAULT expression and returns a value.
> 
> For CURRENT_DATE/TIME/TIMESTAMP expressions, it returns the "0 value", with for dates means 1858-11-17. 

    Hmm, do you know why it returns 0 ? According to the said above it should evaluate expression
at time of reading record (also not perfect, but better than return zero). Looks like a bug.

> Only simple expressions are allowed for DEFAULT now, with means more problems if complex expressions are allowed.

    Is standard allow to reference fileds from the same table in default expression ?
 
> Initial verification to implement the thing in a good way has to populate the fields in DFW, after the new format has 
> been created. This does not solve the problem completely. There can be snapshot transactions that see the new 
> field but can't see its default value.

    It is performance killer "solution" and i don't like it very much.

> Related to above, I tried to update records in system transaction. Initially works in some condition, but I don't 
> think it's good way to go either.

    Don't do it. System transaction should be read-only.

> So I think on an alternate way. At field creation time, its default value may be written in RDB$FORMATS. When 
> reading a NOT NULL field with a DEFAULT, the value written in the format version of the record is returned. This 
> may solve the problem of complex expressions, the problem of transaction visibility, and don't need an update on 
> the whole table. I've no code to prove this, it's just an idea.

    Sounds not bad but will not work if complex expressions referencing another fields from the same
table is allowed by standard.

Regards,
Vlad

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Vlad Khorsun wrote:
>> I'm implementing what's described in subj. Now, user can already do this, but it's not good.
> 
>     Not sure i understand - what is not good ?
>  
Current behavior is not good. Imagine a default expression with a generator being incremented every time a record is read.

>> When engine reads a NOT NULL field with a DEFAULT, it executed the DEFAULT expression and returns a value.
>>
>> For CURRENT_DATE/TIME/TIMESTAMP expressions, it returns the "0 value", with for dates means 1858-11-17. 
> 
>     Hmm, do you know why it returns 0 ? According to the said above it should evaluate expression
> at time of reading record (also not perfect, but better than return zero). Looks like a bug.
> 
It explicitly returns "0", I don't know why. See it in evl. But returning different dates/times everytime is what I said to be not good.

>> Only simple expressions are allowed for DEFAULT now, with means more problems if complex expressions are allowed.
> 
>     Is standard allow to reference fileds from the same table in default expression ?
>  
I don't know, but apparent don't make sense. But I see no problem in a function call being used, for example.

>> Initial verification to implement the thing in a good way has to populate the fields in DFW, after the new format has 
>> been created. This does not solve the problem completely. There can be snapshot transactions that see the new 
>> field but can't see its default value.
> 
>     It is performance killer "solution" and i don't like it very much.
> 
>> Related to above, I tried to update records in system transaction. Initially works in some condition, but I don't 
>> think it's good way to go either.
> 
>     Don't do it. System transaction should be read-only.
> 
Formats are updated using it. ;-)


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Claudio V. C. <cv...@us...>]

> -----Original Message-----
> From: Adriano dos Santos Fernandes [mailto:adr...@gm...] 
> Sent: Sábado, 14 de Noviembre de 2009 17:31
>
> Current behavior is not good. Imagine a default expression 
> with a generator being incremented every time a record is read.

But this is because you want to go beyond constant values that are the only
ones allowed now (except the current_* expressions). I don't think generator
usage for defaults is part of the standard.

:-)

C.

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Claudio Valderrama C. wrote:
>> -----Original Message-----
>> From: Adriano dos Santos Fernandes [mailto:adr...@gm...] 
>> Sent: Sábado, 14 de Noviembre de 2009 17:31
>>
>> Current behavior is not good. Imagine a default expression 
>> with a generator being incremented every time a record is read.
> 
> But this is because you want to go beyond constant values that are the only
> ones allowed now (except the current_* expressions). I don't think generator
> usage for defaults is part of the standard.
> 
Except literals all expressions supported are CURRENT_*. Also do not forget that NOT-NULL field can be changed to NULL and that "smart" logic of evaluate DEFAULT at read will suck.


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Vlad Khorsun wrote:
> 
>     Sounds not bad but will not work if complex expressions referencing another fields from the same
> table is allowed by standard.
> 
The standard mentions only simple expressions, like we have now:

<default clause> ::=
  DEFAULT <default option>

<default option> ::=
    <literal>
  | <datetime value function>
  | USER
  | CURRENT_USER
  | CURRENT_ROLE
  | SESSION_USER
  | SYSTEM_USER
  | CURRENT_CATALOG
  | CURRENT_SCHEMA
  | CURRENT_PATH
  | <implicitly typed value specification>

<datetime value function> ::=
    <current date value function>
  | <current time value function>
  | <current timestamp value function>
  | <current local time value function>
  | <current local timestamp value function>


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Vlad K. <hv...@us...>]

>>> Only simple expressions are allowed for DEFAULT now, with means more problems if complex expressions are allowed.
>> 
>>     Is standard allow to reference fileds from the same table in default expression ?
>>  
> I don't know, but apparent don't make sense. But I see no problem in a function call being used, for example.

    Make sense or not - doesn't matter. If something is allowed then there will be user which can't live without it ;)

    Your method will not work if expressions dependent on values of another fields of the same table is allowed
and we should investigate it at first.
 
>>> Initial verification to implement the thing in a good way has to populate the fields in DFW, after the new format has 
>>> been created. This does not solve the problem completely. There can be snapshot transactions that see the new 
>>> field but can't see its default value.
>> 
>>     It is performance killer "solution" and i don't like it very much.
>> 
>>> Related to above, I tried to update records in system transaction. Initially works in some condition, but I don't 
>>> think it's good way to go either.
>> 
>>     Don't do it. System transaction should be read-only.
>> 
> Formats are updated using it. ;-)

    Yes, and it creates some bad issues. No need to introduce new problems.

Regards,
Vlad

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Vlad K. <hv...@us...>]

> Vlad Khorsun wrote:
>> 
>>     Sounds not bad but will not work if complex expressions referencing another fields from the same
>> table is allowed by standard.
>> 
> The standard mentions only simple expressions, like we have now:

    Very good for us ;)

Regards,
Vlad

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Ann W. H. <aha...@ib...>]

Adriano dos Santos Fernandes wrote:

> 
> But seems minimal, in very localized places, and should not impact the performance in general.

How are you going to represent the values and associate them
with the fields?  Just curious.

Ann

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Ann W. Harrison wrote:
> Adriano dos Santos Fernandes wrote:
> 
>> But seems minimal, in very localized places, and should not impact the performance in general.
> 
> How are you going to represent the values and associate them
> with the fields?  Just curious.
> 
Formats current are represented by a list of Ods::Descriptor. Count of
fields is blobLength / sizeof(Ods::Descriptor).

I now want to encode it as follow:
	numberOfFields: USHORT
	list of Ods::Descriptor
	numberOfFieldsWithDefault: USHORT
	list of (<fieldId: USHORT><Ods::Descriptor><data: dsc_length bytes>)

Default values are read by MET_format and written by DFW/make_format.
EVL_field "clever" logic is replaced by the new logic that catch
defaults from the format.


Adriano

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Claudio V. C. <cv...@us...>]

> -----Original Message-----
> From: Adriano dos Santos Fernandes [mailto:adr...@gm...] 
> Sent: Domingo, 15 de Noviembre de 2009 21:15
> 
> I now want to encode it as follow:
> 	numberOfFields: USHORT
> 	list of Ods::Descriptor
> 	numberOfFieldsWithDefault: USHORT
> 	list of (<fieldId: USHORT><Ods::Descriptor><data: 
> dsc_length bytes>)

What about using ULONG instead of USHORT for whatever extension needed in
the future? I know some SAS tests that use a very high amount of fields and
it seems it's not pure lab testing.

C.

Re: [Firebird-devel] Adding NOT NULL fields with DEFAULT

[Adriano d. S. F. <adr...@gm...>]

Claudio Valderrama C. escreveu:
>> -----Original Message-----
>> From: Adriano dos Santos Fernandes [mailto:adr...@gm...] 
>> Sent: Domingo, 15 de Noviembre de 2009 21:15
>>
>> I now want to encode it as follow:
>> 	numberOfFields: USHORT
>> 	list of Ods::Descriptor
>> 	numberOfFieldsWithDefault: USHORT
>> 	list of (<fieldId: USHORT><Ods::Descriptor><data: 
>> dsc_length bytes>)
>>     
>
> What about using ULONG instead of USHORT for whatever extension needed in
> the future? I know some SAS tests that use a very high amount of fields and
> it seems it's not pure lab testing.
There are many changes needed to support more than 64K columns. :-) I 
think have partial support for it in the ODS is not good.

And more than 64K column is also not needed IMHO.


Adriano