Re: [firebird-support] Python Firebird library fdb for web development

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thank you very much for your answer.

Aldo Caruso

El 7/8/19 a las 07:27, 'pcisar' pci...@ibphoenix.cz [firebird-support] 
escribió:

Hi,

The FDB driver has no direct support for either connection pool or 
"persistent connection". However, such functionality is often provided 
by Python web frameworks (for example Django has it), so check your 
options. If it's not provided by framework of your choice, you can use 
3rd party solution like https://pypi.org/project/connection-pool/ (dig 
around on PyPI for others) or write one yourself that fits yout needs.


best regards
Pavel Cisar
IBPhoenix

[firebird-support] Python Firebird library fdb for web development

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi,

   I'm currently using Firebird 3.0 for a web development using PHP 7.2 
with Apache 2.4 on Ubuntu 18.04

   I'm evaluating a new development with Python 3.6 using a 
microframework like Bottle or Flash.

   I've been using fdb Python package ( python-fdb 1.6 ) for Python 
scripts ( not for web development ) successfully.

   One of the key functions of PHP library for Firebird is the 
possibility to open a persistent connection ( function ibase_pconnect 
[1] ), which allows keeping it open when the script ends, so that it can 
be reused in the future in case another script or the same one needs to 
open a connection with the same parameters (i.e. same database, user, 
role, etc. ). This creates a connection pool that is useful for 
increasing the script response time.

   I wonder whether this can be done with fdb package.

Thanks in advance for any clarification.

Aldo Caruso

[1] https://www.php.net/manual/en/function.ibase-pconnect.php







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] COMMENT ON charset

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thanks ! It worked well with the switch -ch "UTF-8"

El 20/12/18 a las 16:03, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] escribió:
> 20.12.2018 19:10, Aldo Caruso aldo.car...@argencasas.com [firebird-support] 
> wrote:
>>   The sql script is coded in UTF-8
> You must inform server about it using SET NAMES before CONNECT or -ch 
> switch.
>

[firebird-support] COMMENT ON charset

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

   How is defined the chartset used in a COMMENT ON statement ?

     I'm getting into trouble when I execute a sql script containing 
COMMENT ON statements, using isql-fb -input 

Re: [firebird-support] Privileges for generators in Firebird 3

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

I created a ticket: http://tracker.firebirdsql.org/browse/CORE-5938

Thanks !


El 06/10/18 a las 10:45, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escribió:
> > On which page is it possible to suggest for improvements ?
>
> Create a ticket on the tracker (http://tracker.firebirdsql.org) under 
> CORE.
>
> -- 
> Mark Rotteveel

Re: [firebird-support] Privileges for generators in Firebird 3

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thank you Mark.


 Why isn't a way to define a specific sequence ? i.e.

>>
>> GRANT ALTER SEQUENCE  TO ROLE 

>Because that wasn't considered when this was implemented; consider
>filing an improvement request.

On which page is it possible to suggest for improvements ?

Thanks again.
Aldo Caruso

Re: [firebird-support] Privileges for generators in Firebird 3

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

 > select gen_id(GEN_AGENDA_NRO, 287390 - gen_id(GEN_AGENDA_NRO , 0)) from
 > rdb$database

An ugly hack, I agree, but works.


 >This does make you wonder if maybe RESTART WITH shouldn't fall under
 >USAGE instead of the ALTER privilege (or alternatively if using gen_id
 >with anything other than 0 or 1 shouldn't fall under the ALTER privilege).

I agree also: a bit strange.

Thanks.

Aldo Caruso

[firebird-support] Privileges for generators in Firebird 3

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

     I'm migrating from Firebird 2 to Firebird 3

     When setting a generator, I get the following error:

SET GENERATOR GEN_AGENDA_NRO to 287390;

unsuccessful metadata update
SET GENERATOR GEN_AGENDA_NRO failed
no permission for ALTER access to GENERATOR GEN_AGENDA_NRO

The same happens if I use the new syntax:

ALTER SEQUENCE GEN_AGENDA_NRO RESTART WITH 287390;

Reading documentation I found that there is a new SQL command for 
granting privileges to metadata objects like sequences

GRANT ALTER ANY 
TO [USER | ROLE]  |  [WITH GRANT OPTION];

I'm a bit confused about how to specify the sequence

Should I use

GRANT ALTER ANY SEQUENCE TO ROLE 

or

GRANT ALTER SEQUENCE TO ROLE 

Why isn't a way to define a specific sequence ? i.e.

GRANT ALTER SEQUENCE  TO ROLE 


Thanks in advance for any answer.

Aldo Caruso







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Two aliases pointing to the same file

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thanks !


El 14/09/18 a las 06:11, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escribió:


On 13-9-2018 22:42, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] wrote:
> Hello,
>
>     Is there any issue having two aliases pointing to the same physical
> file ?
>
> Example:
>
> old_alias = /var/lib/firebird/2.5/data/mydb.fdb
>
> new_alias = /var/lib/firebird/2.5/data/mydb.fdb
>
>     Some clients connect using the "old_alias", while others 
"new_alias".


There should be no issues with recent versions of Firebird, but I seem
to vaguely recall issues in Firebird 2.1 or earlier, but that might also
have been something with differences in case of filenames.

IIRC, the engine resolves the alias to the filename, and then it is as
if you specified that filename.

Mark
--
Mark Rotteveel

Re: [firebird-support] Two aliases pointing to the same file

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thanks for your answer.

I'm renaming aliases for code refactoring purposes, so during some 
transitional time some scripts and programs will be accessing via the 
old alias while others via the new one.


I only wanted to know if under that situation the server still realizes 
that they both types of connections point to the same database and there 
will be no problems with locks, transactions, etc.


Regards,

Aldo


El 14/09/18 a las 02:44, Norman Dunbar nor...@dunbar-it.co.uk 
[firebird-support] escribió:


I've been doing that for years! I have, for example, an alias 
'employee' and another 'employee.fdb' - if I remember correctly, it 
was originally used as an example when the aliases file was installed 
- many years ago. I just followed suit.



Cheers,
Norm.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

[firebird-support] Two aliases pointing to the same file

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

     Is there any issue having two aliases pointing to the same physical 
file ?

Example:

old_alias = /var/lib/firebird/2.5/data/mydb.fdb

new_alias = /var/lib/firebird/2.5/data/mydb.fdb

     Some clients connect using the "old_alias", while others "new_alias".

Thanks

Aldo








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] hosting on linux questions

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

I'm running Firebird on Ubuntu, but also I'have run it on Debian for 
many years with no problems in either case.

Regards,
Aldo Caruso

Re: [firebird-support] gsec in Firebird 3.0

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Unfortunately, Ubuntu 18.04 doesn't install any example database, that's 
why I resourced to security3.db


I have a script that automates a server installation, changing SYSDBA 
password and adding users. I did that with gsec.


I know that now I should do it with SQL sentences, but as I have no 
database, I have to use security3.db.


Is it dangerous to use this database to add users  ?

Aldo


El 12/07/18 a las 17:06, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escribió:


On 12-7-2018 21:24, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] wrote:
>     thanks for your answer.
>
>     One question related to changing SYSDBA password for the first time
> without using gsec.
>
>     I noticed that, as you have no other database than security3.fdb,
> you must connect to it, but for doing so you can't use remote
> connections ( not even localhost ) and, as you access directly to the
> database file, you must run isql-fb as root. Also you need to stop the
> service in order to have exclusive access to the database file.

You could just connect to the employee example database (or any other
local and accessible database) using an embedded connection (which
should be the default when you don't specify a hostname) and create (or
alter) the account there.

I would suggest to explicitly specify the user manager plugin to use
with the `USING PLUGIN ..` clause, so you don't accidentally create (or
alter) less safe accounts using the legacy user manager if you didn't
intended that (or vice versa).

>     So you must follow the following steps ( Ubuntu 18..04 )
>
>
> sudo service firebird3.0 stop
>
> sudo isql-fb -user sysdba -password  security.db
>
> SQL> alter user sysdba set password '';
>
> SQL> exit;
>
> sudo service firebird3.0 start
>
>
> Is this sequence of steps correct ?

It shouldn't be necessary, especially not as root.

Mark
--
Mark Rotteveel

Re: [firebird-support] gsec in Firebird 3.0

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Mark,

    thanks for your answer.

    One question related to changing SYSDBA password for the first time 
without using gsec.


    I noticed that, as you have no other database than security3.fdb, 
you must connect to it, but for doing so you can't use remote 
connections ( not even localhost ) and, as you access directly to the 
database file, you must run isql-fb as root. Also you need to stop the 
service in order to have exclusive access to the database file.


    So you must follow the following steps ( Ubuntu 18.04 )


sudo service firebird3.0 stop

sudo isql-fb -user sysdba -password  security.db

SQL> alter user sysdba set password '';

SQL> exit;

sudo service firebird3.0 start


Is this sequence of steps correct ?

Thanks,

Aldo

||


El 12/07/18 a las 15:37, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escribió:


On 12-7-2018 16:24, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] wrote:
> Hello,
>
>     The gsec utility still exists in Firebird 3.0.2 ( Ubuntu 18.04
> distribution ), although its man page states that it is deprecated.
>     I tested it and works. I have some scripts that use it in order to
> add users.
>     Is it still reliable to use it or should I have to rewrite those
> scripts invoking isql-fb with the new command "create user" ?

Deprecated doesn't mean it doesn't work, it means that it is advisable
to stop using it and that it is may be removed in a future.

In other words: you can use it, but you should consider moving to SQL
user management so you don't run into problems when gsec is removed.

One of the downsides of gsec is that it doesn't have support for
multiple user managers, so you can only use the user manager that is
first in the configured list. It also doesn't support some of the newer
features (like user tags). I believe there are some more downsides, but
I can't readily think of them.

Also consider this: dialect 1 has been deprecated since InterBase 6 back
at the turn of the century, and yet Firebird still supports it...

Mark
--
Mark Rotteveel

[firebird-support] gsec in Firebird 3.0

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

     The gsec utility still exists in Firebird 3.0.2 ( Ubuntu 18.04 
distribution ), although its man page states that it is deprecated.
     I tested it and works. I have some scripts that use it in order to 
add users.
     Is it still reliable to use it or should I have to rewrite those 
scripts invoking isql-fb with the new command "create user" ?

Thanks,
Aldo

Re: [firebird-support] Doubts about Firebird in Linux....

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi András,

   if your file system has barrier enabled ( ext4 by default has it ), 
ant you set forced writes ON, then massive updates will be very slow. 
You should decide between setting FW On or using a file system with 
barrier activated.

     There has been a thread in this forum about this point some time 
ago. [1]

Aldo

[1] 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/125057
 

Re: [firebird-support] Doubts about Firebird in Linux....

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

I'm running Firebird on Linux since many years, with the following 
environment:


1. Ubuntu 14.04 ( I've also run it on Debian 6.0 )
2. ext4
3. Journaling
4. Firebird 2.5.2

Aldo Caruso

El 16/05/18 a las 20:43, Javier Cintron fcintr...@yahoo.com 
[firebird-support] escribió:

Firebird ver 2.5.7.27050 64 bits
Superserver
Windows Server 2012R2

I am planning to change the operating system that hosts our ERP's 
Firebird Databases from Windows Server 2012R2 to Linux and I have some 
questions about it:



 1. Which Linux distribution do you recommend? I have experience using
Centos.
 2. Which Linux file system do you recommend? (ext3 ext4, etc)
 3. Do you recommend using a journaling file system?
 4. One of the requirements is that the Firebird has to be 2.5.7.27050
but as of today the current version is 2.5.8.27089: is there a
repository of past firebird versions?


Thanks in advance for your help

Re: [firebird-support] Adding a field with NOT NULL constraint

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello Helen,

    One of the most dangerous consequences of forgetting to update 
values of the new added field, when it has constraints, is that, when 
recovering a database from a backup, errors are risen and the recover 
process aborts. Although backups are done raising no error, that doesn't 
guarantees that data is consistent. Only when a restore is done, data 
integrity is fully checked. Normally, backups are done far more 
frequently that restore, as they are done at least on a daily basis.


    To prevent or at least mitigate this risk, a cron job could be 
created in order to daily recover the database from its last daily 
backup onto another file.


    gbak -replace_database -se localhost:service_mgr -user sysdba 
-password   



    If an inconsistence were found, an error would be thrown and the 
cron job user will receive an e-mail.


    Is there a better way to test full integrity of all the data than a 
restore ?


Thanks

Aldo Caruso


El 10/02/18 a las 15:41, Helen Borrie hele...@iinet.net.au 
[firebird-support] escribió:


Hello Aldo,

> My questions are the following:

> 1) Is the intended effect to fill behind the scenes a newly created
> field with its default value when there is a not null constraint ?

No. Only inserts subsequent to the commit of the DDL for the new
field will use the default in the case where no value is provided.

Note, also, that default values apply only to inserts and only where
the field is absent from the field list for the insert.

> 2) Could this behind the scenes filling fail because of an update or
> insert of another concurrent transaction ?

There is no "behind the scenes filling". If you add a NOT NULL field
to an existing table, or change a nullable field to NOT NULL, then you
are responsible for filling the field yourself, immediately after the
DDL is committed.

update mytable set newfield = 1 where newfield is null

update mytable set existingfield = 1 where existingfield is null

As for the effect on concurrent transactions, you should not be
attempting to change the structure of a table while it is in use.

HB

> 
> Posted by: Aldo Caruso 
> 

> ++

> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side 
menu there.


> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/

> ++
> 

> Yahoo Groups Links

--
Kind regards,
Helen Borrie

Re: [firebird-support] Re: Adding a field with NOT NULL constraint

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Dmitry,

    Thanks for your answer.

    Also note that when a not null field is created with a default 
value ( test4 ), not only any select returns its default value but also 
the engine considers it in compare statements as if it contained the 
default value.


    This is also true if you decide to change the default value 
afterwards: compare statements will vary accordingly.


    Example

alter table table1 add test4 integer default 4 not null;

select distinct test4, iif(test4 = 4,1,0) as t4 from table1;

Result:
 test4  t4
4     1

If you change the default value afterwards:

alter table t1 alter column test4 set default 5;

select distinct test4, iif(test4 = 5,1,0) as t4 from table1;

Result:
 test4  t4
5     1

In other words, this has the same effect as if the field value changed 
when you changed its default value.


Extending Helen advice, whenever you add or change constraints related 
to the definition of a field using a DDL statement, you must update the 
value of that field in all records by means of a DML statement in order 
to ensure that no inconsistent data is saved or shown, whichever client 
library you use.


Thanks,
Aldo Caruso

El 11/02/18 a las 02:40, Dmitry Yemanov dim...@users.sourceforge.net 
[firebird-support] escribió:


10.02.2018 22:33, Aldo Caruso wrote:
>
> A strange behavior is seen in the combination not null and no default
> value. It is returned as a 0 for selects but treated as a NULL when
> comparing.

In fact, the engine returns NULL. But query prepare describes the output
descriptor as NOT NULL. Some connectivity layers (including ISQL, IIRC)
get fooled, as NULL is not expected from a NOT NULL descriptor, and zero
/ empty string is returned. I recall that IBExpert is able to return
NULL in this case.

Dmitry

Re: [firebird-support] Adding a field with NOT NULL constraint

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Helen,

     Thank you very much for you advices.

     As a matter of fact, I had been changing table structures and 
stored procedures while other clients were connected since many years 
with no problems. Nevertheless, what you say is indeed true and can give 
rise potentially to trouble.

     Better to disconnect clients ( during for a low activity hours ) 
before applying DDL statements.

     Thanks.

Aldo Caruso


El 10/02/18 a las 17:32, Helen Borrie hele...@iinet.net.au 
[firebird-support] escribió:
>>    Your last advice concerns me a bit. Is it also valid for
>> changing stored procedures or triggers ?
> As an abiding principle - yes.  But, for SPs and triggers, the effect
> varies according to a few factors.  The BLR for these modules is
> cached on first use.  Changes conducted whilst the module is in cache
> will not take effect until the cached copy is removed.
>
> For Classic and Superclassic, each user has a private cache that
> disappears when that user detaches from the database.  For
> Superserver, the cache is shared, so the changes will not take effect
> for any user until all users detach.
>>    Should I have always to disconnect every client before   executing 
>> DDL sentences ?
> My advice is "Yes, always".  There might be some conditions where
> changing things while users are online is plain sailing but how would
> you know for certain?  Whilst the engine may allow you to effect
> changes without throwing errors or corrupting on-disk structures, it
> would be difficult to assure yourself that you are not going to
> corrupt the in-memory structures that users already have in place.
>
> And, when all is said and done, assumptions about the structure of
> the database objects are made in the client application and any active
> request refers to the status quo when that client connected.
>
> HB
>
>
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu 
> there.
>
> Also search the knowledgebases at 
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Adding a field with NOT NULL constraint

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello Helen,

  I saw your answer after I had replied to Dimitry. It is clear that 
there is no automatic filling and I'm responsible to fill fields with 
not null constraint.


  Your last advice concerns me a bit. Is it also valid for changing 
stored procedures or triggers ?


  Should I have always to disconnect every client before executing DDL 
sentences ?


Thanks

Aldo


El 10/02/18 a las 15:41, Helen Borrie hele...@iinet.net.au 
[firebird-support] escribió:


Hello Aldo,

> My questions are the following:

> 1) Is the intended effect to fill behind the scenes a newly created
> field with its default value when there is a not null constraint ?

No. Only inserts subsequent to the commit of the DDL for the new
field will use the default in the case where no value is provided.

Note, also, that default values apply only to inserts and only where
the field is absent from the field list for the insert.

> 2) Could this behind the scenes filling fail because of an update or
> insert of another concurrent transaction ?

There is no "behind the scenes filling". If you add a NOT NULL field
to an existing table, or change a nullable field to NOT NULL, then you
are responsible for filling the field yourself, immediately after the
DDL is committed.

update mytable set newfield = 1 where newfield is null

update mytable set existingfield = 1 where existingfield is null

As for the effect on concurrent transactions, you should not be
attempting to change the structure of a table while it is in use.

HB

> 
> Posted by: Aldo Caruso 
> 

> ++

> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side 
menu there.


> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/

> ++
> 

> Yahoo Groups Links

--
Kind regards,
Helen Borrie

Re: [firebird-support] Adding a field with NOT NULL constraint

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thanks for your answer.

I made some tests and found some strange exceptions to the rules you 
described.

Consider the following DDL sentences, which create four fields with the 
for possible combinations for default value and not null constraint:

alter table table1 add test1 integer;
alter table table1 add test2 integer not null;
alter table table1 add test3 integer default 3;
alter table table1 add test4 integer default 4 not null;

Given the following select sentence:

select distinct test1, test2, test3, test4,
   iif(test1 is null,1,0) as t1,
   iif(test2 is null,1,0) as t2,
   iif(test3 is null,1,0) as t3,
   iif(test4 is null,1,0) as t4
   from table1;

you get the following row:

test1    test2  test3     test4  t1   t2  t3   t4
[null]    0        [null] 4        1    1    1    0

So it seems that, when there is no not null constraint (test1 and 
test3), the engine returns null in select clauses as well as when 
comparing values. The default value of t3 is not used.
On the other hand, when there is a not null constraint, you have two cases:
   there is a default (test4): The engine uses this default for selects 
and when comparing values.
   there is no default (test2): It returns 0 for selects but uses null 
value for comparing operations.

Of course, we are always speaking in the case that no field value exists 
in the returned record version.

As you can see, default value are only assumed when there is a not null 
constraint.
A strange behavior is seen in the combination not null and no default 
value. It is returned as a 0 for selects but treated as a NULL when 
comparing.

Thanks for any answer.
Aldo Caruso


El 10/02/18 a las 13:48, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] escribió:
> 10.02.2018 16:32, Aldo Caruso aldo.car...@argencasas.com [firebird-support] 
> wrote:
>> I discovered that when a field is added to a table with a NOT NULL
>> constraint and a default value, it is automatically filled with that
>> default value.
> No, it isn't. It would be too slow.
> Default value is returned by select if no field value exists in returned 
> record
> version. Engine works about this way:
> 1) Prepare buffer for returned values and fill it with default values.
> 2) Replace values in the buffer with values extracted from storage.
> 3) Send the buffer to client side.
>
>

[firebird-support] Adding a field with NOT NULL constraint

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

     I discovered that when a field is added to a table with a NOT NULL 
constraint and a default value, it is automatically filled with that 
default value. Example:

alter table table1 add field1 integer default 1 not null;
commit;
select distinct field1 from table1;

FIELD1

    1

Nevertheless, when I applied this DDL statement on a production table, 
with concurrent connections, one record wasn't filled with its default 
value, but remained NULL.
I discovered this, days after, when I tested a restore from a backup and 
the restore process was aborted because of a constraint validation error.

My questions are the following:

1) Is the intended effect to fill behind the scenes a newly created 
field with its default value when there is a not null constraint ?
2) Could this behind the scenes filling fail because of an update or 
insert of another concurrent transaction ?

Thanks
Aldo

[firebird-support] Extracting Metadata through isql-fb -x

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello

     Normally I use isql-fb with -x switch to extract metadata in order 
to compare the database development structure with the database 
production structure and figure out the changes I must apply to the latter.

     Both outputs are redirected to files, and I use diff command to 
compare them.

     The only problem I find is that, while creation DDL sentences for 
Domanins, Tables, Procedures, Triggers, Privileges, Triggers, etc. are 
alphabetically sorted, creation DDL for Views aren't.

     The isql-fb command applied to two databases which have the same 
declared Views, outputs their creation DDL sentences in different order, 
which makes analysis through diff command difficult.

     I will appreciate any help.

Thanks,

Aldo Caruso

Re: [firebird-support] Connection rejected by remote interface

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi Mark,

the version number is 2.5.2.

Unfortunately I cannot upgrade it to a more recent one because the 
OS is Ubuntu 14.04, and I have to wait until a complete migration to 
Ubuntu 16.04 until I can upgrade Firebird to a newer version.


Thanks,

Aldo Caruso


El 17/07/17 a las 12:01, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escribió:


On 17-7-2017 14:08, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] wrote:
> I have the same problem running Firebird super server on Linux (
> Ubuntu 14.04 ). Sadly, the only solution I found was to restart 
Firebird

> service.
>
> I wrote a bash script that attempts to connect to the database. If
> it could not establish a connection, it restarts Firebird service. This
> script is run periodically by cron service.

That would suggest the listener crashed. Which version are you using?

Mark
--
Mark Rotteveel

Re: [firebird-support] Connection rejected by remote interface

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

   I have the same problem running Firebird super server on Linux ( 
Ubuntu 14.04 ). Sadly, the only solution I found was to restart Firebird 
service.


I wrote a bash script that attempts to connect to the database.  If 
it could not establish a connection, it restarts Firebird service. This 
script is run periodically by cron service.


Aldo Caruso


El 14/07/17 a las 10:07, 'Dieter Blume' d...@blume-edv.de 
[firebird-support] escribió:


Dear Friends,

i am a software-developer, using Delphi 2006 and Firebird 2.5.

Yesterday, I tried to migrate from Firebird 2.5 to Firebird 3.0.

Everything works fine, but only on my server. When I tried to
connect to Firebird 3.0 using a remote PC, the following error
occured:

"Connection rejected by remote interface"

I used exactly the same components on server and client.
- IBConsole
- gds32.dll (copy of fbclient.dll from 32-bit-setup)
- my own application using connect-string "server:c:\database\test.gdb"

I deactivated the Windows-Firewall on the server with no success.

Any suggestions? I would be happy about your help.
Please excuse my worse english.

Best Regards

Dieter Blume
staatl. gepr. EDV-Betriebswirt / Wirtschaftsinformatiker

Dieter Blume EDV-Organisation e.K.
33129 Delbrück - von-Galen-Straße 38
Tel: 05250 997849 - Fax: 05250 997859
Mobil: 0171 5028601 oder 0174 8434424
eMail: d...@blume-edv.de
Internet: www.blume-edv.de
USt-IdNr.: DE235021893

Re: Odp: [firebird-support] Connection rejected by remote interface

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Karol,

well supposed. Is not the case.

Regards,

Aldo


El 23/06/17 a las 11:38, 'liviusliv...@poczta.onet.pl' 
liviusliv...@poczta.onet.pl [firebird-support] escribió:


Hi,

I saw this error when someone install Interbase over Firebird. But i 
suppose this is not the case


Regards,
Karol Bieniaszewski

- Reply message -
Od: "Aldo Caruso aldo.car...@argencasas.com [firebird-support]" 
<firebird-support@yahoogroups.com>

Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Connection rejected by remote interface
Data: pt., cze 23, 2017 14:03


Hi Mark,

thanks for your answer.

Nevertheless, I looked up for crashed server errors in
/var/log/syslog as well as in /var/log/firebird2.5.log and found none.
The server did not crash, it simply began to refuse local connections
suddenly.

Also, I ran the server for many years and had never seen this problem.

The only real difference is that two weeks ago I modified
/etc/firebird/2.5/firebird.conf disabling remote interface connections.

RemoteBindAddress = localhost

   ( previously I had this line commented )

Taking into account the error message suddenly thrown, I suspect
that may be a bug when this option is set.

Aldo Caruso









++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side 
menu there.


Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/


++


Yahoo Groups Links

Re: [firebird-support] Connection rejected by remote interface

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi Mark,

 thanks for your answer.

 Nevertheless, I looked up for crashed server errors in 
/var/log/syslog as well as in /var/log/firebird2.5.log and found none. 
The server did not crash, it simply began to refuse local connections 
suddenly.

 Also, I ran the server for many years and had never seen this problem.

 The only real difference is that two weeks ago I modified 
/etc/firebird/2.5/firebird.conf disabling remote interface connections.

RemoteBindAddress = localhost

( previously I had this line commented )

 Taking into account the error message suddenly thrown, I suspect 
that may be a bug when this option is set.

Aldo Caruso









++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Connection rejected by remote interface

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi,

 I have Firebird 2.5 running since many years under the following 
environment

OS: Ubuntu 14.04 Server

Firebird Superserver  2.5.2.26540

Web server: apache2 2.4.7

 Today connections from local PHP scripts could not be established. 
I tried to attach from Flamerobin.

 The error message was the following

Context: Database::Connect
Message: isc_attach_database failed

SQL Message : -923
Connection not established

Engine Code: 335544421
Engine Message :
connection rejected by remote interface

I had to restart Firebird in order to let clients connect.

Any clue will be welcome.

Thanks

Aldo Caruso

[firebird-support] Read only transaccions and generators

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

 I discovered that a read only transaction can increment a generator.

 Is that right ?

Thanks

Aldo

Re: [firebird-support] Read Only Transaction

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thanks for your answer.

Regarding the "blob not found" error, there is a thread about this issue [1]

Nevertheless, I believe that RO/RC transactions are assigned a unique 
ID, because as I start them the next transaction ID keeps incrementing.


[1] 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/111668


Aldo Caruso


El 07/03/17 a las 15:52, Ann Harrison aharri...@ibphoenix.com 
[firebird-support] escribió:


> On Mar 7, 2017, at 10:30 AM, Aldo Caruso aldo.car...@argencasas.com 
wrote:


>
> Supposing a connection only needs to read from a database. Which are 
the benefits of starting a read only transaction ?

>
I doubt that there is any difference in performance that you could 
measure. Almost all transactions change the database - cooperative 
garbage collection, incrementing the next transaction id on the header 
page, ending the transaction on the TIP. The security checks have to 
be made to insure read access - checking for higher levels is no more 
expensive. DEclaring a transaction to be read-only is a slight check 
on programmers - Firebird will detect and block writes that were added 
carelessly in a point version of an application.


However, a read-only, read-committed transaction has merit if you 
don't need consistency. Firebird doesn't assign a unique transaction 
id to RO/RC transactions, so they don't change the next transaction 
id, they don't block garbage collection, and they don't change the 
state of a bit on a TIP. From time to time (IIRC) they'll return a 
"blob. not found" error if the record holding the blob is garbage 
collected between the time the RO/RC transaction reads the record and 
retrieves the blob.


Basically, read-only was implemented decades ago to give InterBase a 
check mark on standard compliance and Firebird added a hack to provide 
low overhead, low consistency transactions.


Good luck,

Ann

[firebird-support] Read Only Transaction

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

 Supposing a connection only needs to read from a database.

 Which are the benefits of starting a read only transaction ?

Thanks,

Aldo Caruso

Re: [firebird-support] Transactions isolation levels and collisions

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Ann,

thank you very much for your answer.

Aldo


El 01/03/17 a las 11:44, Ann Harrison aharri...@ibphoenix.com 
[firebird-support] escribió:


On Mar 1, 2017, at 9:53 AM, Aldo Caruso wrote:


Ann,

  Thanks for your detailed answer.

But this leads me to the following question: If snapshot 
transactions have their own copy of the Transaction Inventory Pages ( 
TIP ), taken when the transaction started, transaction A couldn't see 
the state of transaction B or transaction C ( because they are not in 
this copy ).


So I guess that snapshot transactions use its own copy of TIP for 
reading ( selects ), but when it comes to updates or deletes, in 
order to search for collisions, they should also access the global 
shared TIP.





Not exactly.  A snapshot transaction has a copy of rhe relevant 
section of the TIP as of the time it starts. Transactions started 
later alway count as "active" because they could not have committed 
before the started.


When a snapshot transaction tries to update a record and finds that 
the most recent version was created by a transaction that was active 
or unknown according to its copy of the TIP, it attempts to take a 
lock on that transaction's id.


Every transaction holds an exclusive lock on its id until it ends. 
 When the lock is granted to the waiting transaction, it knows that 
the other transaction has ended and asks the transaction management 
module for the final state of the dead transaction.


Good luck,

Ann

Re: [firebird-support] Transactions isolation levels and collisions

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Ann,

  Thanks for your detailed answer.

  I agree with you: the only isolation level which each transaction 
takes care of is its own isolation level. So this behavior happens 
because A isolation level is snapshot ( regardless of the isolation 
level of B or C ).


Also I suspected your last conclusion: Firebird only checks the 
most recent record version.


But this leads me to the following question: If snapshot 
transactions have their own copy of the Transaction Inventory Pages ( 
TIP ), taken when the transaction started, transaction A couldn't see 
the state of transaction B or transaction C ( because they are not in 
this copy ).


So I guess that snapshot transactions use its own copy of TIP for 
reading ( selects ), but when it comes to updates or deletes, in order 
to search for collisions, they should also access the global shared TIP.


Is that true ?

Aldo




El 01/03/17 a las 09:29, Ann Harrison aharri...@ibphoenix.com 
[firebird-support] escribió:



> On Feb 28, 2017, at 3:07 PM, Aldo Caruso wrote:
>
> I'm trying to understand interactions between transactions with
> different isolation levels.

The problem is not mixed isolation levels. You would get the same 
behavior if all transactions were Snapshot


The scenario (described below in detail) is

Start transaction A

Start transaction B

B updates some record

B commits

Start transaction C

C updates the same record

A attempts to update that record and waits for C to end

Whatever C dies A will fail because of a conflict with C if C commits 
or a conflict with B if C rolls back. Why doesn't A just fail immediately?


The answer is that Firebird checks only the most recent record version 
for conflicts. Checking the next back version would avoid having A 
wait in this case but would require more reading in general.


Good luck,

Ann

>
> My environment is Firebird Super Server 2.5 (v. 2.5.2) on Linux
> Ubuntu 14.04
>
> I'm testing by opening two consoles and running isql-fb on both 
consoles.

>
> On the first one I start a SNAPSHOT transaction ( lets call it
> transaction A ).
>
> Afterwards, on the second console, I start another transaction with a
> READ COMMITTED RECORD_VERSION isolation level ( lets call it 
transaction

> B ).
>
> In transaction B, I update a record X and commit the transaction.
>
> In the same second console I start a third transaction, also READ
> COMMITTED RECORD_VERSION ( lets call it transaction C ).
>
> In transaction C, I update record X, but neither commit it nor roll it
> back ( transaction C remains active ).
>
> On the first console, in transaction A, I update record X and it
> obviously waits for a commit or a rollback of transaction C.
>
> Back on the second console, in transaction C, I roll it back.
>
> Not surprisingly, on the first console, transaction A ends waiting with
> a collision error, because although transaction C rolled back,
> transaction B, that had started after transaction A, had made an update
> and committed it. As the isolation level of transaction A was SNAPSHOT,
> it finds a collision and so an error is risen.
>
> So we have the following situation:
>
> If transaction C ends rolling back (as in the example), transaction A
> raises an error because of the collision with the previous transaction
> that touched and committed the record ( transaction B ).
>
> If transaction C ends committing, transaction A also raises an error
> because of the collision with transaction C.
>
> Whichever way transaction C ends ( committing or rolling back ), the
> waiting update in transaction A is aborted with an error message 
because

> of a collision.
>
> The question is which was the point of waiting in transaction A for the
> end of transaction C ? Why didn't it raise an error in the same 
moment I

> tried to do an update in transaction A ? Couldn't it foresee that the
> record was previously updated by a committed transaction B, which
> started after the SNAPSHOT transaction A, and so whichever the outcome
> of C were there would be a collision ?
>
> Note also that if in the example above, we wouldn't have started
> transaction C, transaction A would raise an error as soon as it 
tried to

> update the record that was updated and committed by B ( I tested it ).
>
> Thanks for any answer.
>
> Aldo Caruso
>
>
>
>
> 
> Posted by: Aldo Caruso 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side 
menu there.

>
> Also search the knowledgebases at 
http://www.ibphoenix.com/resources/documents/

>
> ++
> 
>
> Yahoo Groups Links
>
>
>

[firebird-support] Transactions isolation levels and collisions

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

   I'm trying to understand interactions between transactions with 
different isolation levels.

   My environment is Firebird Super Server 2.5 (v. 2.5.2) on Linux 
Ubuntu 14.04

   I'm testing by opening two consoles and running isql-fb on both consoles.

On the first one I start a SNAPSHOT transaction ( lets call it 
transaction A ).

Afterwards, on the second console, I start another transaction with a 
READ COMMITTED RECORD_VERSION isolation level ( lets call it transaction 
B ).

In transaction B, I update a record X and commit the transaction.

In the same second console I start a third transaction, also READ 
COMMITTED RECORD_VERSION ( lets call it transaction C ).

In transaction C, I update record X, but neither commit it nor roll it 
back ( transaction C remains active ).

On the first console, in transaction A, I update record X and it 
obviously waits for a commit or a rollback of transaction C.

Back on the second console, in transaction C, I roll it back.

Not surprisingly, on the first console, transaction A ends waiting with 
a collision error, because although transaction C rolled back, 
transaction B, that had started after transaction A, had made an update 
and committed it. As the isolation level of transaction A was SNAPSHOT, 
it finds a collision and so an error is risen.

So we have the following situation:

If transaction C ends rolling back (as in the example), transaction A 
raises an error because of the collision with the previous transaction 
that touched and committed the record ( transaction B ).

If transaction C ends committing, transaction A also raises an error 
because of the collision with transaction C.

Whichever way transaction C ends ( committing or rolling back ), the 
waiting update in transaction A is aborted with an error message because 
of a collision.

The question is which was the point of waiting in transaction A for the 
end of transaction C ? Why didn't it raise an error in the same moment I 
tried to do an update in transaction A ? Couldn't it foresee that the 
record was previously updated by a committed transaction B, which 
started after the SNAPSHOT transaction A, and so whichever the outcome 
of C were there would be a collision ?

Note also that if in the example above, we wouldn't have started 
transaction C, transaction A would raise an error as soon as it tried to 
update the record that was updated and committed by B ( I tested it ).

Thanks for any answer.

Aldo Caruso

Re: [firebird-support] Help: firebird and apache Processes doesn't die

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Paul,

thanks for your answer.

I see two firebird processes, and I'm sure there is no real user 
connected, as apache server is down.


How could I find out whether CACHE_WRITER and GARBAGE_COLLECTOR 
processes are active ?


Thanks

Aldo


El 10/01/17 a las 11:50, 'Paul Beach' pabe...@waitrose.com 
[firebird-support] escribió:


>

SuperServer wil hold cached data as long as any connections exist. 
Only when the last "real" user disconnects

from the database will the cache be cleared.

Which two users are still connected to the database? CACHE WRITER and 
GARBAGE COLLECTOR?

If so then these should also finish with the last user connection.

Paul

Re: [firebird-support] Help: firebird and apache Processes doesn't die

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Dimitry,

   thanks for your answer. I'll follow your advice.

   Nevertheless, its still a bit strange that, when more connections are 
established, memory keeps increasing up to nearly all the RAM available 
( 4 GBytes ). My database size is only 150 MBytes.

 Shouldn't page cache and firebird structures size kept in RAM be of 
the same order of magnitude as databse size in the worst case ?

Aldo


El 10/01/17 a las 11:32, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] escribió:
> 10.01.2017 14:03, Aldo Caruso aldo.car...@argencasas.com [firebird-support] 
> wrote:
>>Is this a bug in Firebird Super Server or it was designed this way ?
> It depends on which exactly consumed memory. If it is page cache and 
> other internal
> Firebird structures, then it is by design. If it is memory leak from Firebird 
> or UDF, it
> is a bug.
> Use monitoring tables to find out how much memory is consumed by Firebird 
> itself.
>
>

Re: [firebird-support] Help: firebird and apache Processes doesn't die

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi again,

  I tested it many times: after stopping Apache server, the quantity of 
Firebird processes decrease to 2, but the memory isn't released. Running 
"top -u firebird" command displays a lot of memory ( nearly 2 GBytes ) 
consumed by Firebird.


  Is this a bug in Firebird Super Server or it was designed this way ?

Thanks,
Aldo Caruso

El 08/01/17 a las 17:12, Aldo Caruso aldo.car...@argencasas.com 
[firebird-support] escribió:


Hi,

I'm using Firebird 2.5 Super server, PHP 5.5 and Apache2 on Ubuntu 14.04

As I start apache server, and the web site gets visits, apache
processes start to grow as well as firebird processes ( one per second
), nearly without limit. The memory consumption also increases. After a
few seconds my site is unresponsive.

If I stop apache, firebird processes don't decrease immediately as
expected, but only after a minute they jump from the high quantity (for
example 150) to 2 processes. Nevertheless, firebird doesn't release the
memory.

In my php scripts every ibase_pconnect sentence has it corresponding
ibase_close sentence, inside a try statement.

Any idea about why apache processes keep alive and also why they
don't free firebird processes ?

( I'm in a hurry, because my site is not working )

Thanks in advance for any help.

Aldo

[firebird-support] Help: firebird and apache Processes doesn't die

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi,

   I'm using Firebird 2.5 Super server, PHP 5.5 and Apache2 on Ubuntu 14.04

   As I start apache server, and the web site gets visits, apache 
processes start to grow as well as firebird processes ( one per second 
), nearly without limit. The memory consumption also increases. After a 
few seconds my site is unresponsive.

 If I stop apache, firebird processes don't decrease immediately as 
expected, but only after a minute they jump from the high quantity (for 
example 150) to 2 processes. Nevertheless, firebird doesn't release the 
memory.

   In my php scripts every ibase_pconnect sentence has it corresponding 
ibase_close sentence, inside a try statement.

 Any idea about why apache processes keep alive and also why they 
don't free firebird processes ?

( I'm in a hurry, because my site is not working )

Thanks in advance for any help.

Aldo

[firebird-support] Memory consumption or database corruption

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi,
 I'm using firebird 2.5 superserver, php5 and apache2 on a Linux server.

 I noticed an increase in memory ( 50 MB / day ) in the last months, 
that forced me to periodically restart apache server (once every two or 
three weeks).

 Yesterday, after an apache restart, memory started to increase out 
of control in a few minutes, as well as the number of apache processes ( 
normally 30, yesterday more than 100 ) and the number of firebird 
threads ( normally 10, yesterday more than 100 ). Needless to say that 
the web page didn't respond.

 I tried restarting firebird server, but nothing changed ( memory 
and processes started to grow out of control again ).

 I took caution measures ( stopped apache, firebird, made a copy of 
the database file, etc.)

 I stopped apache server, shut down the database, ran "gfix 
-validate -full" which threw the error shown below.

 Summary of validation errors
 Number of record level errors: 1

  Also in the /var/log/firebird2.5.log I found the following lines

( when the problem began )

 INET/inet_error: read errno = 104

 ( when doing gfix -validate )

 Relation has 2407 orphan backversions (0 in use) in table PROPS (147)

 Afterwards I made a back up and restore of the database, no errors 
were shown during those processes, and everything went back to normality.

 I wonder whether these errors point out some kind of database 
corruption and if they are related with the memory consumption symptoms 
I described before.

Thanks
Aldo Caruso

Re: [firebird-support] Database design. Simple question!

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

My advice is to create that field in the invoices table.

You will always have this field at hand without resorting to an outer 
join with another table that only has two fields ( the invoice ID and 
this field ) each time you need to inspect it.


Aldo

El 25/07/16 a las 18:51, shg_siste...@yahoo.com.ar [firebird-support] 
escribió:


Hello! I  have a FB 2.5 database with a tabla "invoices". I need to 
add a field in that table which will be null most of the times. Lefts 
say, that 90% of the records will have this new field in null.



What is your advice? To create a new table with a FK to "invoices" and 
save the needed field in a separate table (only when the value is not 
null of course) of just create the new field in the main "invoices" table?



I think I read somewhere that null fields use very little space in the 
database. Is that true?



Thanks for any input!!!


Sergio

Re: [firebird-support] Database/Backup consistency check

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

The third option is the best.

If you can create now a backup and no errors are shown, and you can 
restore it to a new host with a reliable HD, and no errors are show, you 
can be sure that it is ok.


Aldo Caruso

El 04/07/16 a las 15:52, 'Steffen Heil (Mailinglisten)' 
li...@steffen-heil.de [firebird-support] escribió:


Hi

I have a database on a hard drive which is failing. The hard drive 
already has some bad sectors and we are about to replace it.


Now I have a backup of that database created with gbak that I could 
use. However I am not sure if the hard drive was still fine, when the 
backup was created.
On the other hand, I can right now access the database just fine and I 
could try to simply copy it.

As third option I could create a new backup to external media right now.

Now my questions:
- Is there any way to check a copied database file for corruption? If 
the file copy works without error (no file system I/O error) can I be 
sure or is there a command to be sure, if the file is completely intact?
- If I restore the backup and gbak does not report an error, can I be 
sure that everything is okay?
- If I create a new backup and gbak does not report an error, can I be 
sure that the new backup is okay?


There used to be a checksum in the page header, but IIRC that field 
was abandoned.

Then again I am still at 2.0.4...

Regards,
Steffen

[Non-text portions of this message have been removed]

[firebird-support] Plan in a non selectable stored procedure

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

 I noticed that the plan a stored procedure uses can be inspected if 
it is a selectable stored procedure, whereas if it is a stored procedure 
that is not selectable (i.e. it contains no suspend clause ) its plan 
cannot be inspected.
 Is there a way to inspect the plan in that case ?

Thanks,
Aldo







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: Rif: [firebird-support] Chararacter set NONE to ISO8859_1

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thank you very much for your answer.

El 26/07/15 a las 19:13, Ann Harrison aharri...@ibphoenix.com 
[firebird-support] escibió:



On Sun, Jul 26, 2015 at 3:19 PM, Aldo Caruso 
aldo.car...@argencasas.com mailto:aldo.car...@argencasas.com 
[firebird-support] firebird-support@yahoogroups.com 
mailto:firebird-support@yahoogroups.com wrote:





Why do you say that All but SQL are now dead and BLR is an
artifact without benefit ?


There is absolutely no interest in interoperability between database 
companies. Claiming to support SQL is good enough.  Nobody expects an 
application to work transparently between databases, let along across 
databases.


And, if BLR doesn't include datatype information, why do you say
that I have to recompile triggers and SP anyway ?


You don't have to recreate the procedure from the SQL definition.  
However you'll have to recompile the BLR into an execution tree - 
which happens anytime you shut the database down.


Cheers,

Ann

Re: Rif: [firebird-support] Chararacter set NONE to ISO8859_1

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thanks for your answer.

I'm a bit confused by your two final phrases.
Why do you say that All but SQL are now dead and BLR is an artifact 
without benefit ?
And, if BLR doesn't include datatype information, why do you say that I 
have to recompile triggers and SP anyway ?


Aldo

El 26/07/15 a las 14:06, Ann Harrison aharri...@ibphoenix.com 
[firebird-support] escibió:



On 24-7-2015 21:37, Aldo Caruso aldo.car...@argencasas.com
mailto:aldo.car...@argencasas.com
[firebird-support] wrote:

 I wonder if the following strategy has any pitfall:

 1) Create the temporary field of the right character set
 2) Fill it with data, using CAST to OCTETS as suggested by The
Firebird
 Book
 3) Alter the original field type, changing it to the right
character set
 4) Fill it with data from the temprorary field
 5) Drop the temporary field

 Notice that only the 2nd step could raise conversion errors,
and you
 have to manage them there.
 In the 4th step no error could occur since both fields are of
the same
 type and character set.


El 25/07/15 a las 05:45, Mark Rotteveel m...@lawinegevaar.nl
mailto:m...@lawinegevaar.nl [firebird-support] escibió:


That would probably work, but you might have to recreate
triggers, views
and stored procedures anyway. I am not sure if the generated BLR
contains assumptions/behavior based on the original format.

On Sat, Jul 25, 2015 at 2:22 PM, Aldo Caruso 
aldo.car...@argencasas.com mailto:aldo.car...@argencasas.com 
[firebird-support] firebird-support@yahoogroups.com 
mailto:firebird-support@yahoogroups.com wrote:




So, to be conservative, let's use the original process as
recommended by the book.
Thank you.



You might try your original approach because BLR is actually a very 
high level language - albeit binary. It doesn't include datatype 
information, string sizes, collations, or character set.  All that is 
supplied when the request is compiled into an execution tree. The 
purpose of BLR was to allow a database to encode user requests in a 
network and machine friendly format that could support SQL, QUEL, and 
DEC's database language which became GDML.  Since all but SQL are now 
dead, BLR is an artifact without benefit - serving only to remind the 
ancient that the world could have been very different and much more 
interoperable..


Anyway, you'll have to recompile your triggers etc., but the BLR 
should be OK.


Cheers,

Ann

Re: Rif: [firebird-support] Chararacter set NONE to ISO8859_1

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

So, to be conservative, let's use the original process as recommended by 
the book.

Thank you.

El 25/07/15 a las 05:45, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escibió:


On 24-7-2015 21:37, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] wrote:
 In the process of moving the data back and forth, using a temporary
 field of the right character set, you have to drop the original field an
 then recreate it.

 The process of dropping a field is a bit nasty because you have first to
 comment all the strored procedures and triggers in which this field is
 used, drop the field, and then uncomment those stored procedures and
 triggers.

 I wonder if the following strategy has any pitfall:

 1) Create the temporary field of the right character set
 2) Fill it with data, using CAST to OCTETS as suggested by The Firebird
 Book
 3) Alter the original field type, changing it to the right character set
 4) Fill it with data from the temprorary field
 5) Drop the temporary field

 Notice that only the 2nd step could raise conversion errors, and you
 have to manage them there.
 In the 4th step no error could occur since both fields are of the same
 type and character set.

That would probably work, but you might have to recreate triggers, views
and stored procedures anyway. I am not sure if the generated BLR
contains assumptions/behavior based on the original format.

Mark
--
Mark Rotteveel

Re: Rif: [firebird-support] Chararacter set NONE to ISO8859_1

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Pretty clear.
Thank you.

El 23/07/15 a las 05:54, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escibió:


On Wed, 22 Jul 2015 21:09:12 -0300, Aldo Caruso
aldo.car...@argencasas.com
[firebird-support] firebird-support@yahoogroups.com wrote:
 Raffaele,

 thanks for your answer.
 The question is what prevents data loss in the first procedure ?
 Suppose the filed in question is

 FIRST_NAME VARCHAR(60) CHARACTER SET NONE

 so lets create a temporary field

 TMP_NAME VARCHAR(60) CHARACATER SET ISO8859_1

 and then lets fill it

 UPDATE TABLE1 SET TMP_NAME = CAST(FIRST_NAME AS VARCHAR(60)
 CHARACTER SET OCTETS)

 Whichever character (from 0 to 255) were in FIRST_NAME would be blindly
 copied to TMP_NAME.
 This has the same effect as assuming that the characters in FIRST_NAME
 where loaded as ISO8859_1, so simply changing FIRST_NAME type from NONE
 to ISO8859_1 would be equivalent.

No, if you alter the character set of an existing column, then the old
data will remain in its old format and format version, and will only be
converted when selected/queried. Leading potentially to conversion errors
at run time (there are a number of bytes in ISO-8859-1 that are not valid
to use). If you create a new column and transfer the data, the validity
check is done at the moment of transfer and you can fix any problems at
that time.

The important difference between these two is the time the conversion
error might occur.

Mark

Re: [firebird-support] Chararacter set NONE to ISO8859_1

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

 Stefan,

thanks for your answer.
I think that passing through a temporary field by filling it with a 
cast to octets of the original field has the same effect that assuming 
that the original field had been filled with ISO8859_1 character set.

See my answer to Raffaele.

Regards,
Aldo

El 22/07/15 a las 15:09, Stefan Heymann li...@stefanheymann.de 
[firebird-support] escibió:


 I wonder what is wrong with simply invoking ALTER TABLE X ALTER
 COLUMN Y TYPE CHAR(N) CHARACTER SET ISO8859_1

This will only change the metadata of that field. It will not check if
the data really is ISO8859_1. When *you* are sure about that, it
shouldn't be a problem.

Regards

Stefan

Re: Rif: [firebird-support] Chararacter set NONE to ISO8859_1

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Raffaele,

thanks for your answer.
The question is what prevents data loss in the first procedure ?
Suppose the filed in question is

FIRST_NAME VARCHAR(60) CHARACTER SET NONE

so lets create a temporary field

TMP_NAME VARCHAR(60) CHARACATER SET ISO8859_1

and then lets fill it

UPDATE TABLE1 SET TMP_NAME = CAST(FIRST_NAME AS VARCHAR(60) 
CHARACTER SET OCTETS)


Whichever character (from 0 to 255) were in FIRST_NAME would be blindly 
copied to TMP_NAME.
This has the same effect as assuming that the characters in FIRST_NAME 
where loaded as ISO8859_1, so simply changing FIRST_NAME type from NONE 
to ISO8859_1 would be equivalent.


Aldo



El 22/07/15 a las 04:57, 'Raffaele Confalone' r.confal...@libero.it 
[firebird-support] escibió:


The first procedure provides you safe from data loss.
you can directly execute the change of the charset after doing a complete
backup of the database.




---Messaggio originale---

Da: Aldo Caruso aldo.car...@argencasas.com [firebird-support]
Data: 21/07/2015 18.38.39
A: firebird-support@yahoogroups.com
Oggetto: [firebird-support] Chararacter set NONE to ISO8859_1

Hi,

I have some VARCHAR fields whose character set were incorrectly
defined as NONE. I would like to use ISO8859_1 instead.

I read on The Firebird Book that in order to do so you must add a
new temporary field, with the right character set, fill it with the data
from the original field ( using OCTECT as an intermediat chararacter set
) and finally drop and recreate the original field with the right
character set, filling it with the data that was stored in the temporary
field. The process ends up dropping the temporary field.

I wonder what is wrong with simply invoking ALTER TABLE X ALTER
COLUMN Y TYPE CHAR(N) CHARACTER SET ISO8859_1

Thanks in advance for any help.

Aldo Caruso







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu
there.

Also search the knowledgebases at http://www.ibphoenix
com/resources/documents/

++


Yahoo Groups Links





-
Nessun virus nel messaggio.
Controllato da AVG - www.avg.com
Versione: 2015.0.6081 / Database dei virus: 4392/10282 - Data di rilascio:
21/07/2015


[Non-text portions of this message have been removed]

[firebird-support] Chararacter set NONE to ISO8859_1

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi,

 I have some VARCHAR fields whose character set were incorrectly 
defined as NONE. I would like to use ISO8859_1 instead.

 I read on The Firebird Book that in order to do so you must add a 
new temporary field, with the right character set, fill it with the data 
from the original field ( using OCTECT as an intermediat chararacter set 
) and finally drop and recreate the original field with the right 
character set, filling it with the data that was stored in the temporary 
field. The process ends up dropping the temporary field.

 I wonder what is wrong with simply invoking ALTER TABLE X ALTER 
COLUMN Y TYPE CHAR(N) CHARACTER SET ISO8859_1

Thanks in advance for any help.

Aldo Caruso







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Validation error for unknown column

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi,

 I created a table with a numeric non null field. Afterwards I 
realized that it could be a calculated field, so I dropped the field and 
recreated it accordingly.

Before:

NETO CURRENCY NOT NULL,
IVA CURRENCY NOT NULL,
TOTAL CURRENCY NOT NULL

After:

NETO CURRENCY NOT NULL,
IVA CURRENCY NOT NULL,
TOTAL COMPUTED BY (NETO + IVA)

( Being CURRENCY a domain NUMERIC(18,2) )

Then I tried to insert a record in the table using a stored procedure,  
but the insertion failed giving the following strange message :

validation error for column *** unknown ***, value *** null ***

Then I tried to insert the record directly in the table, using no stored 
procedure, but I got the same result.

I guess that the unknown column has to do with the dropped and 
recreated TOTAL field, and something remainend in memory related to its 
non null constraint.

I dropped and recreated the stored procedure, and then error 
dissapeared. I can insert records with no problem now.
For security, I restarted Firebird and checked the database using gfix 
validate. No errors were shown.

Does anyone have any idea of what produced such a strange error ?

Thanks in advance,

Aldo Caruso












++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Altering a domain

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi,

 I have defined the following domain :

CREATE DOMAIN CURRENCY AS decimal(9,2)

   I would like to alter it as the following :

ALTER DOMAIN CURRENCY AS decimal(18,2)

 I have many fields in tables that depend on this domain and they 
are filled with values.

 Will they be restructured in their internal representation ( from 
INTEGER to BIGINT in dialect 3 ) with no harm for the stored data ?

Thanks in advance for any answer.

Aldo Caruso






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Altering a domain

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Ann,

thank you very much for your clear explanation.

Aldo

El 16/06/15 a las 12:44, Ann Harrison aharri...@ibphoenix.com 
[firebird-support] escibió:
On Tue, Jun 16, 2015 at 11:15 AM, Aldo Caruso 
aldo.car...@argencasas.com mailto:aldo.car...@argencasas.com 
[firebird-support] firebird-support@yahoogroups.com 
mailto:firebird-support@yahoogroups.com wrote:




 I have defined the following domain :

CREATE DOMAIN CURRENCY AS decimal(9,2)

   I would like to alter it as the following :

ALTER DOMAIN CURRENCY AS decimal(18,2)

 I have many fields in tables that depend on this domain and they
are filled with values.

 Will they be restructured in their internal representation ( from
INTEGER to BIGINT in dialect 3 ) with no harm for the stored data ?



Initially, only the domain and column definitions will change, 
creating new

format versions for the tables affected.  When you insert new records into
those tables, the new records will have the new format.  Old records will
be changed in memory when you read them, so they appear to be in the
new format.  The stored format of existing records change when the
records are modified.

Good luck,

Ann

Re: [firebird-support] Check constraints vs. user defined triggers

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi Ann,

I ran a test and there is no performance difference, so I'll keep 
using check constraints as they are simpler.

Thanks for your suggestion.

Aldo

El 04/01/15 a las 15:31, Ann Harrison aharri...@ibphoenix.com 
[firebird-support] escibió:




 On Dec 29, 2014, at 9:56 PM, Aldo Caruso aldo.car...@argencasas.com 
[firebird-support] firebird-support@yahoogroups.com wrote:


No one has answered, so I'll offer a not-very interesting suggestion.

 I have a table with four check constraints, which basically check
 logical conditions between its fields.

 In order to enforce those constraints, Firebird creates behind the
 scenes two triggers for each one of them (I suppose one for update and
 the other for insert operation). So in this case it ends up with eight
 system created triggers.

 I suppose this degradates performance of massive insert or updates, as
 four triggers have to be launched for each insert or update operation.

Triggers aren't launched like separate programs, but get incorporated 
in the compiled request so eight small triggers have about the same 
performance impact as one large trigger.


 Does it have sense in order to enhace performance not to use check
 contraints but instead check those constraints globally using only two
 user defined triggers (before insert and before update), raising
 exceptions when logical conditions aren't met ?

 Is it worthwhile ? Which are the pros and cons of this approach ?

In your situation, I'd build a test and measure the diffence in 
performance of the two approaches, even though in theory there 
shouldn't be much.


Good luck,

Ann

[firebird-support] Check constraints vs. user defined triggers

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi,

I have a table with four check constraints, which basically check 
logical conditions between its fields.

In order to enforce those constraints, Firebird creates behind the 
scenes two triggers for each one of them (I suppose one for update and 
the other for insert operation). So in this case it ends up with eight 
system created triggers.

I suppose this degradates performance of massive insert or updates, as 
four triggers have to be launched for each insert or update operation.

Does it have sense in order to enhace performance not to use check 
contraints but instead check those constraints globally using only two 
user defined triggers (before insert and before update), raising 
exceptions when logical conditions aren't met ?

Is it worthwhile ? Which are the pros and cons of this approach ?

Thanks for your opinion.

Aldo







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] -804 Error ( randomly )

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hi,

 I'm inserting records from PHP / Apache / Ubuntu  in a Firebird 
table. This table has an integer non null field, and a before insert 
trigger which takes care of setting it to the next integer value in case 
the user attempts to set this field to null.

 If this field is set to any integer from PHP there is no problem.

 On the other hand, if it is set to null ( lefting to the trigger 
the task of assigning it ) any of the following behaviours happens 
(randomly)

1) A -804 error is shown in the apache log, with the message: Dynamic 
SQL Error SQL error code = -804 Incorrect values within SQLDA structure

2) The Apache sub process is aborted, with the log message: child pid 
 exit signal Segmentation fault (11)

3) A random integer is assigned to the field. This behaviour is by far 
less frequent, but happened a couple of times.

Thanks in advance for any help.

Aldo Caruso








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] -804 Error ( randomly )

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Thank you for your answer.

I think the best I can do is to avoid letting the user to set this 
field, whether it is null or not. Instead it should be set only by the 
trigger, so that the SQL sentence doens't have this field included.

Aldo

El 09/12/14 a las 18:20, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escibió:
 On 9-12-2014 21:42, Aldo Caruso aldo.car...@argencasas.com
 [firebird-support] wrote:
I'm inserting records from PHP / Apache / Ubuntu  in a Firebird
 table. This table has an integer non null field, and a before insert
 trigger which takes care of setting it to the next integer value in case
 the user attempts to set this field to null.

If this field is set to any integer from PHP there is no problem.

On the other hand, if it is set to null ( lefting to the trigger
 the task of assigning it ) any of the following behaviours happens
 (randomly)

 1) A -804 error is shown in the apache log, with the message: Dynamic
 SQL Error SQL error code = -804 Incorrect values within SQLDA structure
 Error numbers are not really helpful (eg -804 is 10 different errors),
 it is better to provide the specific error code (which for this error
 message is 335544713 (or isc_dsql_sqlda_value_err).

 2) The Apache sub process is aborted, with the log message: child pid
  exit signal Segmentation fault (11)

 3) A random integer is assigned to the field. This behaviour is by far
 less frequent, but happened a couple of times.
 The problems you describe above are possibly caused by memory corruption
 issues. It sounds like you or an intermediate layer is incorrectly
 reading from or writing values into an XSQLDA (the struct used for
 getting information into and out of Firebird), overwriting other memory
 areas or reading (or causing Firebird to read) beyond process memory.

 As you mention the field is NOT NULL, then most likely you (or the
 intermediate layer) thinks it is possible to set a non-nullable XSQLVAR
 to NULL without setting the sqltype to nullable. I have been bitten by
 that problem myself once in the native sub-protocol in Jaybird (see
 http://tracker.firebirdsql.org/browse/JDBC-271 and
 http://tracker.firebirdsql.org/browse/CORE-3913).

 The problem might be that fbclient does not check the null-indicator if
 the sqltype is not-nullable, so it will attempt to read the sqldata
 field that is either set to null, or worse pointing to a random memory
 location. Another cause could be the reverse: the type has been made
 nullable, but the sqlind value hasn't been set with similar results.

 As I have no idea about the PHP side of things I can only guess about
 workarounds, etc.

 Mark







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Charsets

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

 Given a populated database with default character set = None.
 Some fields have character set = None while others have character 
set = ISO8859_1

 1) Which is the best way to modify those field definitions which 
have character set = None to character set = ISO8859_1 (there are char, 
varchar as well as blob type text fields) without losing information ?

 2) Can the default character set be changed ? In that case, does it 
affect current fields that have character set = None or it only affects 
the character set of future fields definitions ?

Thanks in advance,
Aldo








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Forced write, page size and buffer size

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

  The hard truth is that the only _absolute guarantee_ to prevent 
database corruption is FW = ON.

Provided that the file system also has barrier enabled ...

Regards,
Aldo






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Forced write, page size and buffer size

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

You can check whether forced writes status is on or off submitting on a 
FB console the following command

SHOW DATABASE

In order to set it on / off, you shoud run

gfix -user [user_name] -password [psw] -w [sync | async] [database_name]

Regards,
Aldo

Re: [firebird-support] Forced write, page size and buffer size

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Ann,

   thanks for the correction. I erroneously missinterpreted that file 
system barriers were also necessary.

Aldo







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Forced write, page size and buffer size

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Sean,

  thanks for your answer.

Aldo

El 18/08/14 a las 15:59, 'Leyne, Sean' s...@broadviewsoftware.com 
[firebird-support] escibió:




 Given this scenario my questions are the following:

 1) Does it makes sense to activate forced writes on Linux ?

Different versions of Linux file systems provide better protection 
than others.


Personally, I believe that forced write = ON is necessary for all OS 
-- but I suspect that I am in the minority (some will swear they have 
never had a problem/db corruption, but I am too chicken to take the 
risk -- I like sleeping at night).


 2) Is increasing the page size the right approach to improve 
performance ?

 Which are the drawbacks of setting page size to 16K ?

There is no universally true answer, it depends.

 3) The same question stands for cache pages: is it useful ( or has
 disadvantages ) to have so many cache pages as there are on disk pages (
 provided there is enough RAM size ) ?

As long as you have the RAM there is no problem.

Sean

Re: [firebird-support] Forced write, page size and buffer size

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Rich,

I got the point: with forced writes off you can't be sure of when the 
data is sent to disk.
But, in that case, what is the point of turning it off ? If you must 
wait for the server shut down to be 100% sure that the data is written 
to disk, isn't the risk too high to have forced write off ?
I suspect that data is written to disk far before than the server shut 
down or a connection close, otherwise FB designers shoudn't leave forced 
write as an option, it should always be activated.


Aldo

El 18/08/14 a las 16:11, 'Saunders, Rich' greym...@mykolab.com 
[firebird-support] escibió:


On 2014-08-18 14:51, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] wrote:
 After that I noticed that some massive updates ( 100K records ) took
 nearly 5 minutes, whereas with async writes it used to take 10 seconds.

Of course, we don't know how much work actually took place during that
10 second period when
forced writes were off. Could be that very little of the update was
actually on disk
at that point. So do you consider the massive update actually done at
that point?

Also while forced writes are off, when is all that 5 minutes worth of
work actually done? When the database is closed? When the server was
shut down? Thats's the problem with forced writes being off - you never
know.

--

Cheers!
Rich Saunders

Re: [firebird-support] Re: Forced write, page size and buffer size

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Dmitry,

   Your guess is correct: Super Server architecture.
   Reliability in this context means for me having no database 
corruption after a power failure or kernel panic.

Aldo Caruso

El 19/08/14 a las 06:12, Dmitry Yemanov dim...@users.sourceforge.net 
[firebird-support] escibió:
 18.08.2014 22:51, Aldo Caruso wrote:
 For reliability reasons, I decided to turn on forced writes on a
 database running on Linux.
 What is FB architecture: SS / CS / SC? I suspect SS given your default
 cache size (2048 pages) but it's worth double checking.

 1) Does it makes sense to activate forced writes on Linux ?
 It depends on what reliability means to you.

 2) Is increasing the page size the right approach to improve performance
 ? Which are the drawbacks of setting page size to 16K ?
 The cost is a higher concurrency for the same pages under parallel load.

 3) The same question stands for cache pages: is it useful ( or has
 disadvantages ) to have so many cache pages as there are on disk pages (
 provided there is enough RAM size ) ?
 It would surely not hurt.


 Dmitry





 

 

 ++

 Visit http://www.firebirdsql.org and click the Documentation item
 on the main (top) menu.  Try FAQ and other links from the left-side menu 
 there.

 Also search the knowledgebases at 
 http://www.ibphoenix.com/resources/documents/

 ++
 

 Yahoo Groups Links










++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Forced write, page size and buffer size

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Carlos,

I agree with you. The performance degradation was very high on an 
ext4 file system ( which has barrier enabled by default ). On the other 
hand, I found no noticiable performance degradation on an ext3 file 
system ( which has barrier disabled by default ).


Having barrier disabled on a server with an ext3 file system, does 
FW=ON improve reliability or is it useless ?


Aldo Caruso

El 18/08/14 a las 16:27, 'Carlos H. Cantu' lis...@warmboot.com.br 
[firebird-support] escibió:


Usually, if you turn FW = ON on Linux, and your filesystem has barrier
enabled, it will affect performance of batch updates really badly.

You would either accept the performance degradation, or disable one of
them (FW or barrier).

Carlos
Firebird Performance in Detail - http://videos.firebirddevelopersday.com
www.firebirdnews.org - www.FireBase.com.br

ACacacfs Hello,

ACacacfs For reliability reasons, I decided to turn on forced writes 
on a

ACacacfs database running on Linux.

ACacacfs After that I noticed that some massive updates ( 100K 
records ) took
ACacacfs nearly 5 minutes, whereas with async writes it used to take 
10 seconds.


ACacacfs One solution is, of course, disabling sync writes when doing 
massive
ACacacfs updates. Unfortunately not always massive updates are under 
database
ACacacfs admin control ( some end users actions can lead to massive 
updates,

ACacacfs indirectly, by means of triggers ).

ACacacfs Another aproach I tested was augmenting page size from its 
default
ACacacfs value ( 4 KB ) to its maximum allowed value ( 16 KB ). The 
speed was
ACacacfs notably enhaced ( 1 minute for the update + 10 seconds for 
the commit,
ACacacfs but sometimes 2 seconds for the update and 40 seconds for 
the commit).


ACacacfs It should be pointed out that 4 KB was fine, taking into 
account
ACacacfs record size ( max. 300 bytes ) and index max depth ( always 
 3 ).


ACacacfs Going one step further, I augmented cached pages from its 
default (
ACacacfs 2048 ) to 8192. Some small performance improvement was 
observed, but not
ACacacfs very significative. It shoud be noted also that, with a 16 
KB page size,
ACacacfs the database has 5700 pages on disk, so there are enough 
cache pages to

ACacacfs hold the entire database.

ACacacfs Given this scenario my questions are the following:

ACacacfs 1) Does it makes sense to activate forced writes on Linux ?
ACacacfs 2) Is increasing the page size the right approach to improve 
performance

ACacacfs ? Which are the drawbacks of setting page size to 16K ?
ACacacfs 3) The same question stands for cache pages: is it useful ( 
or has
ACacacfs disadvantages ) to have so many cache pages as there are on 
disk pages (

ACacacfs provided there is enough RAM size ) ?

ACacacfs Thanks in advance for any clue.
ACacacfs Aldo

ACacacfs 

ACacacfs 

ACacacfs 
++


ACacacfs Visit http://www.firebirdsql.org and click the Documentation 
item
ACacacfs on the main (top) menu. Try FAQ and other links from the 
left-side menu there.


ACacacfs Also search the knowledgebases at
ACacacfs http://www.ibphoenix.com/resources/documents/

ACacacfs 
++

ACacacfs 

ACacacfs Yahoo Groups Links

[firebird-support] Forced write, page size and buffer size

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

   For reliability reasons, I decided to turn on forced writes on a 
database running on Linux.

   After that I noticed that some massive updates ( 100K records ) took 
nearly 5 minutes, whereas with async writes it used to take 10 seconds.

   One solution is, of course, disabling sync writes when doing massive 
updates. Unfortunately not always massive updates are under database 
admin control ( some end users actions can lead to massive updates, 
indirectly, by means of triggers ).

   Another aproach I tested was augmenting page size from its default 
value ( 4 KB ) to its maximum allowed value ( 16 KB ). The speed was 
notably enhaced ( 1 minute for the update + 10 seconds for the commit, 
but sometimes 2 seconds for the update and 40 seconds for the commit).

   It should be pointed out that 4 KB was fine, taking into account 
record size ( max. 300 bytes ) and index max depth ( always  3 ).

   Going one step further, I augmented cached pages from its default ( 
2048 ) to 8192. Some small performance improvement was observed, but not 
very significative. It shoud be noted also that, with a 16 KB page size, 
the database has 5700 pages on disk, so there are enough cache pages to 
hold the entire database.

   Given this scenario my questions are the following:

1) Does it makes sense to activate forced writes on Linux ?
2) Is increasing the page size the right approach to improve performance 
? Which are the drawbacks of setting page size to 16K ?
3) The same question stands for cache pages: is it useful ( or has 
disadvantages ) to have so many cache pages as there are on disk pages ( 
provided there is enough RAM size ) ?

Thanks in advance for any clue.
Aldo







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] CONTAINIG vs. LIKE

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Mark,

   thanks for your answer. (the page number I mentioned was on the first 
edition ).

Aldo Caruso

El 04/08/14 a las 11:50, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escibió:
 On 4-8-2014 16:42, Aldo Caruso aldo.car...@argencasas.com
 [firebird-support] wrote:
 Hello,

I have three questions relating CONTAINING predicate.

In The Firebird Book I read on page 318 that indexes are used
 also on search conditions against CONTAING predicates. I did some test
 and, inspecting its PLAN, I found that it uses NATURAL order instead of
 using an index.

 1) ¿ Under which circumstances an index is used when the search has a
 CONTAINING condition ?

  Given the following two SQL clauses

 SELECT * FROM TABLE1 WHERE UPPER(FIELD1) LIKE '%TEST%'
 SELECT * FROM TABLE1 WHERE FIELD1 CONTAINING 'TEST'

 2) Are they logically equivalent ?
 3) Which of them is faster ?
 They are logically equivalent, and I'd assume they perform similar
 (although a CONTAINING might have the benefit that it doesn't need to
 support more complex patterns). I am not 100% sure, but I think Helen's
 book is wrong here (btw: page 270 in The Firebird book second edition).

 Mark







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] CONTAINIG vs. LIKE

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

 I have three questions relating CONTAINING predicate.

 In The Firebird Book I read on page 318 that indexes are used 
also on search conditions against CONTAING predicates. I did some test 
and, inspecting its PLAN, I found that it uses NATURAL order instead of 
using an index.

1) ¿ Under which circumstances an index is used when the search has a 
CONTAINING condition ?

   Given the following two SQL clauses

SELECT * FROM TABLE1 WHERE UPPER(FIELD1) LIKE '%TEST%'
SELECT * FROM TABLE1 WHERE FIELD1 CONTAINING 'TEST'

2) Are they logically equivalent ?
3) Which of them is faster ?

Thanks in advance.
Aldo Caruso









++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Suboptimal plan

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

 In a select statement there are two tables joined by a single 
matching field, with search conditions in both of them. One of them has 
roughly 13 records, while the other has 600.

 Inspecting the plan generated by the optimizer, I realized that it 
uses the shorter one as the controlling stream instead of the longer one 
(i.e. the shorter table is at the left side).

 Using a PLAN expression with the corresponding indexes to bypass 
the plan deviced by the optimizer in order to switch the order of the 
streams, the performance is boosted (nearly three times faster).

 What can cause the optimizer to pick a shorter table as the 
controlling stream ?

 Is there a workaround to induce the optimizer to select the streams 
in a fixed way or should I have to resort to a manually imposed PLAN ?

Thanks,
Aldo







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Suboptimal plan

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Sean and Carlos,

  thanks for your answer.

  Although the statistics are up to date the optimizer still chooses 
the shorter stream as the controller.


  Nevertheless, I found a workaround using a subquery on the shorter 
table instead of a join.


  This forced the optimizer to traverse the longer table first and 
boosts the performance even more than using an optimized query with a 
fixed plan.


Thank you very much for your help.

Aldo


El 01/08/14 a las 16:01, 'Carlos H. Cantu' lis...@warmboot.com.br 
[firebird-support] escibió:


Are the indexes statistics up to date?

Also, post the query, query plan, and indexes stats.

Carlos
Firebird Performance in Detail - http://videos.firebirddevelopersday.com
www.firebirdnews.org - www.FireBase.com.br

ACacacfs Hello,

ACacacfs In a select statement there are two tables joined by a single
ACacacfs matching field, with search conditions in both of them. One 
of them has

ACacacfs roughly 13 records, while the other has 600.

ACacacfs Inspecting the plan generated by the optimizer, I realized 
that it
ACacacfs uses the shorter one as the controlling stream instead of 
the longer one

ACacacfs (i.e. the shorter table is at the left side).

ACacacfs Using a PLAN expression with the corresponding indexes to 
bypass
ACacacfs the plan deviced by the optimizer in order to switch the 
order of the

ACacacfs streams, the performance is boosted (nearly three times faster).

ACacacfs What can cause the optimizer to pick a shorter table as the
ACacacfs controlling stream ?

ACacacfs Is there a workaround to induce the optimizer to select the 
streams
ACacacfs in a fixed way or should I have to resort to a manually 
imposed PLAN ?


ACacacfs Thanks,
ACacacfs Aldo

Re: [firebird-support] Suboptimal plan

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Alexandre,

  thanks for your clues !
  I certain circumstances the first one could change the result set, as 
it changes an inner join to an outer join.
  On the other hand, the second one is really good an prevents the 
optimizer to use the index on the long table, as you said.


Aldo

El 01/08/14 a las 18:29, Alexandre Benson Smith 
ibl...@thorsoftware.com.br [firebird-support] escibió:


Hi !

There is some tricks to change the way optimizer will choose the order 
of tables:


example:
select
   *
from
   LongTable L left join
   ShortTable S on (S.FieldA = L.FieldA)

The outer join would force the table on the left to be scaned first.

Another option is
select
   *
from
   LongTable L join
   ShortTable S on (S.FieldA = L.FieldA+0)

the +0 (or || '' if the field is a char/varchar) would prevent the 
optimizer to use the index on LongTable.FieldA and could lead to 
change the join order


see you !




Em 1/8/2014 18:09, Aldo Caruso aldo.car...@argencasas.com 
[firebird-support] escreveu:

Sean and Carlos,

  thanks for your answer.

  Although the statistics are up to date the optimizer still chooses 
the shorter stream as the controller.


  Nevertheless, I found a workaround using a subquery on the shorter 
table instead of a join.


  This forced the optimizer to traverse the longer table first and 
boosts the performance even more than using an optimized query with a 
fixed plan.


Thank you very much for your help.

Aldo


El 01/08/14 a las 16:01, 'Carlos H. Cantu' lis...@warmboot.com.br 
[firebird-support] escibió:


Are the indexes statistics up to date?

Also, post the query, query plan, and indexes stats.

Carlos
Firebird Performance in Detail - http://videos.firebirddevelopersday.com
www.firebirdnews.org - www.FireBase.com.br

ACacacfs Hello,

ACacacfs In a select statement there are two tables joined by a single
ACacacfs matching field, with search conditions in both of them. 
One of them has

ACacacfs roughly 13 records, while the other has 600.

ACacacfs Inspecting the plan generated by the optimizer, I realized 
that it
ACacacfs uses the shorter one as the controlling stream instead of 
the longer one

ACacacfs (i.e. the shorter table is at the left side).

ACacacfs Using a PLAN expression with the corresponding indexes to 
bypass
ACacacfs the plan deviced by the optimizer in order to switch the 
order of the
ACacacfs streams, the performance is boosted (nearly three times 
faster).


ACacacfs What can cause the optimizer to pick a shorter table as the
ACacacfs controlling stream ?

ACacacfs Is there a workaround to induce the optimizer to select 
the streams
ACacacfs in a fixed way or should I have to resort to a manually 
imposed PLAN ?


ACacacfs Thanks,
ACacacfs Aldo