Hello Sean!
On 2014.05.01. 21:24, Leyne, Sean wrote:
>> TRIGGER : NEW/OLD values accessible by column name, and column number
>> (PSQL)
>> - e.g.: NEW['id'], NEW[0]
>> - gain : code reduction, dynamic code (don't have to alter the trigger
>> ICO
>> the table structure altered)
> 1- What advantage does NEW['id'] have over the current syntax "new.id"???
I could iterate trough columns/values, currently I can't.
>
> 2- column numbers reference is extremely dangerous, the column position can
> be changed using simple DDL. So, all Trigger references would be invalidated!
I do not want hardcode index numbers it's about iteration. But you're
right, support only access by name is quite enough.
>
>
>> Optimization I.
>> - VOLATILE (default) / DETERMINISTIC flag for UDF and FUNCTION (maybe
>> lifecycle for deterministic : statement, transaction, connection)
>> - gain : preformance (can cache DETERMINISTIC UDF/FUNCTION results)
> Actually, I think that with the exception of a random number generator, the
> default for UDF should be DETERMINISTIC (Over 99% of UDFs are not volatile).
User shold define deterministic flag. Only slow UDF/FUNCTION shold be
flagged this or else the cache size and lookup time would increase too big.
>
>
>> - SELECT DISTINCT <index fields> FROM table is slow (natural scan on all
>> records) and SELECT <index fields> FROM table GROUP BY <index fields> is
>> also slow (worse! : index scan on all records).
>> - currently you have to keep a separate table with this information
>> because
>> you can't access to this information fast
>> - solution 1 : use index in this case;
> Using an index may not help:
>
> 1- an index is stored without regard to physical disk location, so using it
> will create a huge amount of random disk IO. Whereas a NATURAL scan follows
> the table.
>
> 2- In an version based database like Firebird each row will need to be read
> to confirm the current value of the target field.
>
>
>> Index usage optimization II.
>> IS NOT NULL should use index. It is equivalent with >= min_value or <=
>> max_value based on index direction
> I don't think this is possible.
>
> With an MVCC it is possible for all rows to have both a NULL and NOT NULL
> values stored in the field index, so reading each rows is required. But as
> noted in #1 above, reading by index can lead to significant disk
> IO/degradation
I don't understand this. An equivalent statement can use index and
produce the same result and this why can't? What I'm missing?
>
>> Index usage optimization III.
>> - condition pre-evaluation and reduction. e.g.: WHERE 1 = 2 AND field =
>> :param is always FALSE. Evaluation does not needed for all records, can
>> decide at prepare time whether the result is an empty resultset or an
>> unfiltered resultset.
> I agree that the engine needs to add support for 'execution time'
> optimization. To allow for conditions to be bypassed (as your example) as
> well as eliminate unneeded JOIN in SELECT and Views.
>
>
>> PSQL exception handling : new variables aside GDSCODE and SQLCODE
>> - ERRORMESSAGE : form fbclient
>> - GDSCODE_ORI, SQLCODE_ORI, ERRORMESSAGE_ORI : execute statement
>> errors on external database hides the original error code, you should access
>> them with these
>> - gain : better logging and error tracking and user feedback
> User feedback?
Based on error code we have our extended error messages and suggestions
to deal with it, but in this case we can't properly do this.
>> rename objects
>> it's a nightmare to rename soething with many-many dependencies
>
>
>> Save blob as file to disk at server side
>> - gain : easy UDF deploy and update (and many other thing)
>> - it's also a seurity risk, so some options needed in firebird.conf
> This would violate data integrity requirements. If I save data to a Blob, I
> expect the data to be available -- saving externally would allow for external
> data to be deleted without the database/me knowing.
>
> I believe that there are UDFs available for this already, no?
A very old and unsuppoted udf. In my case I do not expect to read back
from file to DB just one way.
>
>
>> select from execute block
>> - gain : avoid stored procedure creation. With select from select it's
>> easy to
>> generate code, but can't do that with execute block. (select from procedure
>> is allowed, but execute block can't replace the procedure in this case)
> EXECUTE BLOCK already supports returning data, what are you looking for?
Once I was needed the following logic (via code generation result). I
bypassed it using stored proc.
SELECT
FROM
(EXECUTE BLOCK
AS
BEGIN
SELECT
FROM (
EXECUTE BLOCK
AS
BEGIN
END)
)
>
> ------------------------------------------------------------------------------
> "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
> Instantly run your Selenium tests across 300+ browser/OS combos. Get
> unparalleled scalability from the best Selenium testing platform available.
> Simple to use. Nothing to install. Get started now for free."
> http://p.sf.net/sfu/SauceLabs
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
------------------------------------------------------------------------------
Is your legacy SCM system holding you back? Join Perforce May 7 to find out:
• 3 signs your SCM is hindering your productivity
• Requirements for releasing software faster
• Expert tips and advice for migrating your SCM now
http://p.sf.net/sfu/perforce
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
