Re: [firebird-support] How does the optimizer of Firebird determine join order?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, May 13, 2014 at 10:37 PM, Zhan Li zhanl...@gmail.com[firebird-support]
firebird-support@yahoogroups.com wrote:



 Hi All,

 I've been studying the optimizer of firebird for a while, and I was
 wondering how the optimizer chooses join order? As most query optimizers
 determine join order via a dynamic programming algorithm pioneered by IBM's
 System R database project, does the optimizer of firebird use similar
 strategy?


I can't speak to the details of the current optimizer, but the original
design of the optimizer was developed concurrently with IBM's System R, so
neither derived from the other.  Firebird has a cost-based optimizer which
seeks to minimize the number rows returned as the intermediate product.
 The optimizer focuses on inner joins.  Outer joins have an implicit order
- the side that is preserved (e.g. the first named term in a left outer
join) must precede the optional side.  Yes, if you have something like:

from customers c,
   left outer join addresses a on (a.cust_id = c.cust_id)
   left outer join invoices i on (i.cust_id = c.cust_id)

you would necessarily look up customers first and could chose to look at
addresses or invoices in either order.  But that has very little to do with
the overall cost.

These examples are simple - the actual cases are joins of many tables.

Before it begins optimization, Firebird distributes equalities.

from orders o
  inner join customers c on (o.cust_id = c.cust_id)
where c.cust_id = 12345

is transformed into:

from orders o
  inner join customers c on (o.cust_id = c.cust_id)
where c.cust_id = 12345 and o.cust_id = 12435

That allows the optimizer to choose between starting with customers (a
single valued lookup) and starting with orders (potentially multi-valued,
but possibly desirable, depending on what else was included in the query.


The first design of the optimizer considered all possible orders for inner
joins, estimating the cost based on the selectivity of the index and the
nature of the conjunct.  An equality comparison where one side is
identified by a comparison between a constant and a unique key and the
other on an equality between that unique key and a unique key in the second
table requires looking at two records (and a few index entries).  For
example:

from orders o
  inner join customers c on (o.cust_id = c.cust_id)
where o.order_id = 12345

Each order has only one value for cust_id, so the optimizer chooses to look
up the order by its unique key, then use the cust_id in the order to look
up the customer by its unique key.

from orders o
  inner join customers c on (o.cust_id = c.cust_id)
where c.cust_state = 'RI'

Here the optimizer considers the selectivity for the index on state =
'RI' and compares it with the cardinalities of orders and customers. If
the company doesn't do much business, but all of it is in Rhode Island, the
cost may be lower if it reads all the orders, does a single row look-up on
customers, and throws out those that aren't in Rhode Island.

Assuming that the index selectivity was reasonably correct, and the index
values were distributed evenly (i.e. no cases where 90% of the entries had
one value and the other 10% were random), eventually that algorithm got a
good join order.  At the time, Oracle used semantic ordering:  tables were
evaluated in the order they appeared in the query.  However, the full cross
product of joins of more than
 9 or so, the full cross product lead to problems, the optimization took
twelve hours.  Running the query too .05 milliseconds.  Good answer, bad
process.

Interbase (as it was then) adopted a strategy of weighting results.  The
first ordering it considered was analyzed in full and the full cost of the
intermediate product was saved.  The subsequent orderings compared
themselves with the first as each table was introduced and if at any step
the cost was greater than the first, the ordering was discarded.

I think there was a subsequent reorganization that considers the longest
possible chain of equalities first.

A goal, at least for Interbase and early versions of Firebird, was to
emphasize getting good results for good queries and not chasing silly
queries - like returning no results from the compiler if one of the
conditions was and 1 = 2.

Good luck,

Ann

 f

Re: [firebird-support] Re: database became broken for any reasons

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Fri, May 16, 2014 at 1:46 AM, markovdmi...@yahoo.com [firebird-support] 
firebird-support@yahoogroups.com wrote:



 I use gfix for define broken DB or not, and I try to do backup and restore.
 I may see a lot of diffrent errors, for example orphan pages, corrupted
 indexes, also orphan backversions, but the last is not a error for database.


Gfix reports problems that are not actually corruption as if they were
serious.   Anytime the database shuts down without being closed cleanly,
there's a chance that it will contain orphan pages, orphan back versions of
records, and index entries that don't correspond to a record.  The first
two problems represent lost space.  All three can be fixed by a
backup/restore cycle with gbak.   Firebird's index handling code will
generally ignore incorrect index entries.

Problems reported by gbak - either on backup or restore are likely to be
actual corruption.

Good luck,

Ann

Re: [firebird-support] Re: database became broken for any reasons

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com [firebird-support] 
firebird-support@yahoogroups.com wrote:



 For preventing orphan pages or backversions I turn off autosweep. So for
 the last month I don't see any orphan pages or orphan backversions.


Probably you were shutting the database down hard while a sweep was active.



 I know that shutdown during sweep can broke database, so I turn off
 autosweep and do only manual sweep.


I've got to take exception to your statement.  Shutdown during sweep
doesn't break the database.  It leaves some
space (record or page) inaccessible, but, assuming you're running with
forced writes turned on, the database is not
corrupt. All the data is accessible and correct.


 My service of database looks like this:
 1) try normally close all connections(all my applications get commands for
 starting of autoclose procedure)

OK

 2) kill all terminal instance of my applications

OK, though that will leave errors in the log - but you caused them, so not
a big deal.

 3) shutdown database(my software use non root access, so it's guarantee
 single user access)

OK.

 4) killing all processes fb_inet_server.exe

Not OK.  Once the connections are gone, the fb_inet_servers will eventually
stop, after they've written all their changes to disk.  If you kill them,
there's a chance that, for example, an old record version will have been
removed and the page it was on will have been written, but the older
versions that are chained to that record will not have been removed and
their pages flushed to disk.  Firebird uses a technique called careful
write to maintain on-disk consistency without a separate log.  Essentially
that means that when new things are created, the thing is created first,
then pointer to it follow.  Conversely, when a thing is removed, all the
pointers are removed first, then the thing itself.  When done correctly and
consistently, careful writes never leave broken pointers.  However, it can
lead to lost space.  For example, when a table needs a new data page,
Firebird looks for a free page - they're indicated on pages called
PageInformationPages or PIPs.  When it finds a free page on a PIP, Firebird
marks the page as in use, before it starts the process of allocating that
page to the table,  If the operation is interrupted, the PIP will say that
the page is in use, but it's not part of a table or any other part of the
database - hence, orphaned.

Orphans are a normal and benign result of a hard database shutdown with
write pending.

Good luck,

Ann

Re: [firebird-support] filter conditions: WHERE vs. JOIN clause

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, May 27, 2014 at 11:17 AM, jakef...@yahoo.com [firebird-support] 
firebird-support@yahoogroups.com wrote:



 We have some new members of our team with a MS SQL background, and they
 have a preference for including filter conditions in the JOIN clause.  They
 say, the predicates in the ON clause are applied to the table before the
 join, whereas the WHERE clause is semantically applied to the result of the
 join and It has to do with the record set that is available when the
 WHERE clause executes. When you move more conditions into the ON clause,
 you have less records (potentially) when the WHERE clause executes  Is
 that true?


Not for Firebird, and probably not for MS SQL either.  For inner joins, the
optimizer picks the join order that minimizes the number of rows to be
retrieved based on all conditions the JOIN ON terms and the WHERE terms.
 Before doing so, Firebird distributes equalities, so if you have a
multi-way join on a single term (e.g. customer.cust_id = invoice.cust_id
for the first pair and invoice.cust_id = payment.cust_id on the second)
Firebird can join any of the pairs.  With outer joins, a condition in the
WHERE clause that affects the right side of a left outer join (the one that
doesn't have to exist) effectively turns off the outerness of the join
(unless it includes OR value IS NULL).

 

It's a pretty pathetic join optimizer that doesn't move conditions around
and depends on the user's syntax to order conditions.   Logically, you can
think about the operation as doing the joins in the order presented on the
conditions in the JOIN ... ON, then applying the WHERE conditions to that
output stream, but one of the nice things about relational databases is
that the engine is free to do anything behind the scenes to make your query
fast in spite of your efforts.

Good luck,

Ann

Re: [firebird-support] Restore DB with Firebird 1.5 Super Server vs Classic Server

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Sat, May 24, 2014 at 7:43 AM, Halim Boumedjirek
halim1...@hotmail.com[firebird-support]
firebird-support@yahoogroups.com wrote:


 I used default restore settings and I got over 8 hours of restore time.
 Increasing the cache size using gfix -b did not help at all. I got the same
 result. I don't think it helps during the restore process.


A lot of the work of a gbak restore is recreating all the indexes.
 Firebird creates indexes on existing data by sorting the data then
building the index on the sort result.  You might look at the sizes of the
sort buffers.  Small sort buffers require that Firebird write out more
small segments to disk.  I don't know if the default for sort buffers is
smaller for classic, but it might be.

Good luck,

Ann

Re: [firebird-support] Update only changed tuple/ execute if-statement via execute statement?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Sat, May 24, 2014 at 8:51 AM, eric wulfhekel eric.wulfhe...@gmail.com
[firebird-support] firebird-support@yahoogroups.com wrote:


  I want to create a update trigger which sets the current date to a field
 modified if values has changed. I have a lot of tables with a lot of
 columns so i want to work with system table for that task. I build the
 if-statement within a select and wat to execute this statement


I've left your example below.  You're on the right track.  The actual
problem you're having is that EXECUTE STATEMENT executes SQL statements.
 You're generating a statement that can be used only in triggers and stored
procedures.  As Set suggested, a better plan would be to use your current
approach, but generate actual CREATE TRIGGER statements rather than the
PSQL.  Yes, that means that you will need to recreate triggers when your
tables change, but you may be able to do that - or at least signal that
maintenance is required - with a DDL trigger.

Alternately you could generate a series of SQL statements - UPDATE ...
WHERE NEW.field IS DISTINCT FROM OLD.field - and execute them.
 However, that would be horribly inefficient - searching the system tables,
generating a query, passing that query to the engine where it has to be
parsed, compiled, optimized, executed, and released - all of that for every
field in every record you change.  I'd bet that performance would be
measured not by wall clock, but by calendar.

Good luck,

Ann




Here's the statement you tried and the error...



 CREATE TRIGGER PLZTEST_MODIFIED FOR PLZTEST
 ACTIVE BEFORE UPDATE POSITION 0
 AS
 declare variable stmt varchar(1024);
 begin
 for
 select 'if (new.' || rdb$field_name || ' is null and old.' ||
 rdb$field_name || ' is not null or new.' || rdb$field_name ||
 'is not null and old.' || rdb$field_name || ' is null or new.' ||
 rdb$field_name || '  old.' || rdb$field_name || ') then
 new.MODIFIED = current_timestamp;'
  from rdb$relation_fields
  where rdb$relation_name = 'PLZTEST'
  into :stmt
 do
 execute statement stmt;
 end


 The resulting statement looks good.
 The problem here seems, that i could not execute statements starts with
 if. the error occur is

 Dynamic SQL Error.
 SQL error code = -104.
 Token unknown - line 1, column 1.
 if.

 Or maybe is there another way to realise that?

 Thank you in advance


 

Re: [firebird-support] this keeps happening

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Jun 22, 2014, at 3:58 AM, MYKS51 my...@telus.net [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 Using SAM 2014.3 on Win7 system with built into SAM, Firebird. Whenever
 I try to reboot my computer it hangs at Shutting Down and though I've waited
 as long as 10 minutes it doesn't seem to continue so must hit reboot button.
 When I check the Event Viewer there are occurrences of the same error over
 and over,all related to Firebird SQL Server with this line of dialogue:
 
 
 C:\Program Files (x86)\SAMBC\firebird.conf: illegal line [UDF]
 

Your best hope is to get help from the producers of SAM.  To get useful help 
from this group, you'll need to include at least the relevant lines of 
firebird.conf, and  a few lines before and after.

Good luck,

Ann

Re: [firebird-support] no-wait vs wait transactions?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Jul 8, 2014, at 3:37 PM, jonatan.laurit...@yahoo.dk [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 I have Firebird 2.x database which from time to time has rather long 
 transactions - up to 30 seconds and so, there is high probability that the 
 same record will be updated from the several different transactions and the 
 lock conflict - deadlock exeption will be raised. I am currently using nowait 
 transactions.
 
 The question is - what is the best option in this case - to use wait or 
 nowait transactions? 
 

Generally I prefer wait transactions to no-wait to avoid live lock  - the 
problem that occurs when Transaction A has a resource that Transaction B needs. 
 B tries, fails, retries, fails again and so on until A finally releases the 
resource.  When B is a wait transaction, it sits quietly until A releases the 
resource, then either succeeds (if A failed) or gets and error if A succeeded.  
The wait case avoids a lot of pointless cycles.  

I have no idea how BDE transactions behave ... Borland had some very 
imaginative engineers.  

There should be no problem mixing wait and no wait transactions - some will use 
more resources, some will return errors to the user (or whatever is on the 
other end) sooner.  

Good luck,

Ann

Not to make you jealous or anything, but we just came back into cell phone 
range on our leisurely cruise down the Maine coast.  I'd send a picture, but 
the list won't let me.  Now it's 6:30.  The drinks flag is out; the dog has had 
her run through the woods and a bit of swimming.  Life is good.

Re: [firebird-support] no-wait vs wait transactions?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Jul 9, 2014, at 3:09 PM, jonatan.laurit...@yahoo.dk [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 1) Almost all of A-transactions end with success in my case, so almost always 
 B-transactions will fail with deadlock message anyway. Therefore the wait 
 mode has no advantages in my case.
Actually, I think it does, depending on what B is trying to do.  If B can 
decide that it really doesn't care about the record A changed then fine.  If B 
really really wants to change that record, so rolls back and retries the 
change, then B may get 15 error before A finally commits.  Each of those 15 
retries is wasted cycles and possibly wasted I/O.
 
 2) There are floating around some stories from my clients that the deadlock 
 messages can remain in database up to the restart of the Firebird server, it 
 is said that backup/restores is needed in some times. Is it really so?

It absolutely should not be.  If there's a conflicting transaction that they 
haven't found and terminated, then yes, restarting the server will clear the 
conflict, but so will finding and terminating the transaction.  

Firebird reports two different types of deadlock: one has a primary error of 
Update Conflict and represents a conflict between two user transactions.  The 
other, which may be reported as an internal error first, then deadlock, is a 
bug in the code - meaning that it should never happen.  Firebird's internal 
structures should be deadlock free.  If your users are finding internal 
deadlocks (which is unlikely, given the amount of use the code has had over the 
decades) then they are seeing a bug and should report it.

 It would be nice to get some confirmation to it. As I understand, then any 
 problems with locks should be removed when the client rollback transaction or 
 in the worst case disconnect from the database.

User level contention is 
 
 3) But what to do with concurrent updates? Is it possible purely in Firebird 
 (2.1.x) implement some kind of transaction queue? So that all the work is 
 done by nested transactions but only when the required records do not have 
 the locks on them. Maybe there is already available some queueing middleware 
 for this.

That's sometimes implemented with a gateway record - something you must be able 
to update before you can proceed with what you're actually trying to do.  
Someone may have a better solution based on newer versions of Firebird.  SELECT 
... FOR  UPDATE WITH LOCK might work, or it might just move the problem.

Good luck,


Ann

Re: [firebird-support] Synchronisation between two processes

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

Sorry, from my phone, sobad formatting and terse.  What transaction isolation 
mode(s) are you using?  Ordinarily direct file access is limited to one 
process. What version of Firebird?

Ann

 On Jul 11, 2014, at 10:31 AM, Bryan Cole bryan.c...@teraview.com 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 
 
 Hi,
 
 I have two client processes (on uptodate linux host) accessing a firebird 
 database using the direct-file-access method (libfbembed.so). Most of the 
 time, when a write transaction is committed in one process the data is 
 visible in the second process more-or-less immediately. Sometimes (apparently 
 depending on the order of DB access, size of the table and phase of the 
 moon), it takes much much longer (14 seconds) for changes to show up in the 
 second process. Is there a method to ensure that the results of a transaction 
 are immediately available to other processes? 
 
 I'm using firebird-2.5.2, superclassic server on linux (Fedora20). All DB 
 writes/reads are within the context of a transaction.
 
 Thanks,
 
 BC
 
 -- 
 Group Leader, Technical Development Group - Teraview Ltd.
 Platinum Building, St. John's Innovation Park, Cambridge CB4 0DS, UK.
 tel: +44 (0)1223 435386, fax: +44 (0)1223 435382, web: www.teraview.com 
 Registered Number: 04126946, VAT Number: 770 8883 84
 
 ...preferred document formats: ODF (ISO/IEC 26300:2006), PDF
 
 

Re: [firebird-support] Skip records

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Jul 22, 2014, at 9:47 AM, 'checkmail' check_m...@satron.de 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 I would like to read every n record, depending on number of records.
 

Do you care if your results are repeatable?  If so, you'll have to sort all the 
records then skip records in the result set.   

Good luck,

Ann

Re: [firebird-support] Re: Very long transaction start after deleting a big number of records.

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Jul 29, 2014, at 12:26 AM, brucedickin...@wp.pl [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 I haven't thought about scanning all the rows with SELECT after DELETE but it 
 might be something. Perhaps FB 3.0 will help us with this. 

Doing the select in the same transaction as the delete won't help.  The delete 
has created a bunch of deleted stub records, leaving in place the last real 
version of the record and any back versions that other transactions require.  
All that stays in place for the duration of your transaction, just in case the 
transaction actually fails.  Once the transaction that did the delete  and all 
its contemporaries have ended, the stubs and old record versions are ready to 
be removed, but not before then.

V3 probably won't change that significantly, but it may allow you to switch 
from Classic to a single multi-threaded server and use a garbage collect thread 
rather than cooperative garbage collection. 

Good luck,

Ann

Re: [firebird-support] CONTAINIG vs. LIKE

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

Containing is not case sensitive and cannot use an index. But it could not be 
indexed anyway because thestring could occur anywhere in the field, not just at 
the start. Starting (or is it starts?) is case sensitive and equivalent to 
like% so it can use an index. 

Good luck,

Ann

 On Aug 4, 2014, at 11:50 AM, Mark Rotteveel m...@lawinegevaar.nl 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 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
 -- 
 Mark Rotteveel
 
 
 
 
 
 
 ++
 
 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] Forced write, page size and buffer size

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Aug 20, 2014, at 9:05 AM, Aldo Caruso aldo.car...@argencasas.com 
[firebird-support] firebird-support@yahoogroups.com wrote:

 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 ...

Firebird's forced write should be sufficient if the file system and disk honor 
forced write (fsync) - and if Firebird implements forced write correctly.  If 
those criteria are met, barriers serve only to protect the disk journal.  
Firebird long predates file system journalling and operates correctly without 
journals. 

Best regards,

Ann

Re: [firebird-support] Case insensitive search on a memo field

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Fri, Sep 5, 2014 at 8:43 AM, Stefan Heymann li...@stefanheymann.de
[firebird-support] firebird-support@yahoogroups.com wrote:

  I’m using Firebird 2.5.3, and I am looking for text in a emo field, eg.
  I’m tempted to do something like
Select * from mytable t where lowercase(t.mymemo) like ‘%find me%’
  but since this memo field could be enormous, I’m guessing that wold be
 horribly inefficient.

 I use upper() for that and it's surprisingly fast (lowercase shouldn't
 be different).

 What you can do is try to use a case insensitive collation like
 unicode_ci or unicode_ci_ai, but I don't know if that really speeds
 things up.


As Set said before, no index is going to help unless the text string
is at the very beginning of the field.  CONTAINING is the best bet -
it's case insensitive, but doesn't take wild cards, so if you're looking
for %Find%me%, you'll need to use LIKE or SIMILAR and upcase
the incoming value. However, you;re going to read every byte of
every instance of that blob (memo) field - well, every byte of every
instance that does not contain your value, and every byte up to the
value if it does exist.

What's needed is a full-text index, which Firebird doesn't offer.  Lucene
and others produce full-text indexes that can be used with Firebird (I
think, haven't tried it).

Good luck,

Ann

Re: [firebird-support] Firebird Embedded corruptions

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Sat, Sep 13, 2014 at 12:22 PM, Jan Flyborg jan.pers...@gmail.com
[firebird-support] firebird-support@yahoogroups.com wrote:



 We have shipped Firebird Embedded bundled together with our product for a
 few years now and the system is currently in production at several thousand
 of our customer's sites...

 All is well and Firebird has served us good so far with the exception of
 database corruptions that gets reported from a new set of customers every
 week. We are now at the planning stage for the next major release of
 our product and we are thus rethinking if Firebird really is a good choice,
 because of this.

 I can understand that.




 Lots of effort has gone into solving this problem on our side, so I think
 the normal prerequisites has already been put into place (e.g using forced
 writes and so forth), but our system needs to be up and running 24x7, which
 means that it is not possible to schedule periodic backup/restore cycles
 and my personal theory is that Firebird embedded gets corrupted over time
 if you are not doing this regularly.


Nice theory, but if the database is physically corrupt, you can't back it
up, and if it's logically corrupt, you can't restore it.  I think it's
worth looking elsewhere for the problem.

So I have have a few questions that I would appreciate if someone could
 answer:

 1. Is it feasible to run Firebird Embedded 24x7 in a setup where there are
 no scheduled backup/restore cycles. If not, how often should this be
 performed to ensure that the database does not get corrupted.


It should be possible to run Firebird Embedded 24x7.  Without knowing what
you're seeing as corruptions, it's very hard to guess why they're
occurring.  What errors are your customers seeing?  What do they (and you)
do to correct the errors?


 2. Most of our customers are not using a UPS. From my experiments I have
 not managed to create a corrupted database by turning of the power while
 doing a large set of writes (in a session running in VirtualBox). Could
 someone please confirm that this is indeed safe when you are running with
 synchronized writes turned on?


A hard shutdown should not corrupt a database that has forced writes
enabled.  It might corrupt the file system, but again, without knowing what
the errors and problem are, it's hard to guess.


 3. Are there any operations on a live database that should be avoided to
 minimize the risk of corruptions?


Dropping tables and altering tables to drop fields are pretty dangerous
operations, but even if that is what's happening, the development group
should be given a reproducible case that corrupts databases.


 4. Just read a discussion about whether it is needed or not to call
 fb_shutdown to stop Firebird Embedded. Could this be the reason why we are
 getting corruptions? Should we change our service to perform this call when
 it is stopped?

 5. I have also seen discussions of turning of automatic sweeps of the
 database (and doing them manually instead). Is this a likely source of
 corruptions for our setup?


No. Sweeping the database is very much like backing it up without creating
the backup file.  When a sweep starts during heavy database usage, it can
reduce performance but not corrupt the database.

So, question back to you:  what errors are you seeing and how have you
fixed them?

Good luck,

Ann

Re: [firebird-support] Firebird Embedded corruptions

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Mon, Sep 15, 2014 at 7:41 AM, Jan Flyborg jan.pers...@gmail.com
[firebird-support] firebird-support@yahoogroups.com wrote:




 I just made another posting where I tried to describe three different
 examples of things we have seen.


The first was a wrong page type, which sounds like a bug that was fixed in
a newer version in code that's common to all Firebird architectures.  In
your case, the bad page was in an index (7).  If you can find the index
with the bad page and recreate it, all will be well.

Just as an FYI, the page types are:
 0 -   undefined, normally an uninitialized page and indicates a
bad page pointer elsewhere;
 1 -  Database header page
 2 - Page inventory page
 3 - Transaction inventory page
 4 - Pointer page
 5 - Data page
 6 - Index root page - contains information about each index on the
table, one per table
 7 - Index (B-tree) page
 8 - Blob data page
 9 -   Generator pages

The second problem (CCH_precedence: block marked.  file: cch.cpp line:
4390) is more concerning - I don't remember having read a bug about it.
CCH is the cache handler.  A mark is the sign that a page is about to be
changed.   When Firebird is forced to write a page either as part of a
commit or to free space in the cache, it must write out any pages that the
page depends on first.  That's a little obscure.  Suppose that the page
you're about to write has a record with a back version, and the back
version is on a different page.  To keep the database consistent, the page
with the back version must be on disk before the page that includes a
record that points to the back version.  Firebird keeps a list of
precedence relationships and CCH goes through them before writing a page.
I think the error means that someone is currently writing  to a page that's
on the precedence list.  That should never happen.  It's interesting that
the problem occurred during an alter index operation.  However, the
database should be fine on disk and usable after you restart Firebird.
Page marks are entirely in memory.  It's quite possible that I missed a bug
report and this problem was fixed in a later version.

The third problem is two records in a referencing table lack mates in the
referenced table, despite a referential constraint.  I have no idea how
that happened, but it should be reasonably easy to fix in your database.

The first problem is what I would call a physical  corruption - the
internal structure of the database is corrupt.  The second is an in-memory
  corruption - the disk database is OK, but the in-memory version is
damaged.  The third is logical corruption - the database is physically
intact, but does not conform to the data rules..



 Typically we fix our problems with a gfix -mend and then doing a backup
 restore cycle. Usually some tables then still have problems (typically
 foreign keys that refers to non existing primary keys), so if possible we
 then remove the faulty records and then it works again.


Gfix is pretty old and somewhat crude.  IBFirstAid might give you better
help on physical corruptions.  Checking that there is no non-conforming
data before creating constraints may help with logical corruption.

Good luck (and my apologies for the late response)

Ann

Re: [firebird-support] read-only select generates 'lock conflict on no wait transaction'?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Thu, Sep 18, 2014 at 1:00 PM, 'Carlos H. Cantu' lis...@warmboot.com.br
[firebird-support] firebird-support@yahoogroups.com wrote:



 If you are sure that there will be no data editing, I recommend you to use
 ReadCommited+ReadOnly transaction, since it will not block garbage
 collection.


Assuming you can live with a little inconsistency in your results.  And
that the problem is not that someone has introduced a No Rec Version
condition into the transaction definition.

Cheers,

Ann

Re: [firebird-support] Firebird Embedded corruptions

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Sep 23, 2014 at 10:49 AM, Jan Flyborg jan.pers...@gmail.com
[firebird-support] firebird-support@yahoogroups.com wrote:



 The first was a wrong page type, which sounds like a bug that was fixed in
 a newer version in code that's common to all Firebird architectures.  In
 your case, the bad page was in an index (7).  If you can find the index
 with the bad page and recreate it, all will be well.

 That sounds very good and it seems like an upgrade to 2.5.3 will make
 sure that we do not see this again.


Anytime your users get an error of of the form wrong page type, expected 7
encountered n, you can probably work with them to identify and rebuild the
bad index.




 The second problem (CCH_precedence: block marked.  file: cch.cpp line:
 4390) is more concerning

 If that is of any help for you, I was wrong in my original posting when I
 said we were using 2.5.1 (I mean that the line numbers in the exception
 might lead you to draw the wrong conclusion when I gave you the wrong
 version). We are currently using 2.5.2 and nothing else.


I follow bug reports but not religiously.  So I searched for one that
includes block marked and modify RDB$INDICES and found #4467 which is
marked as will not fix and described as a user error.  User errors should
not cause internal cache manager problems, so I'm somewhat bemused.  It was
reported in 2.5.2, so it may well be your problem.




 The third problem is two records in a referencing table lack mates in the
 referenced table, despite a referential constraint.  I have no idea how
 that happened, but it should be reasonably easy to fix in your database.


 In another posting (later than yours) Fabiano is saying that these errors
 are connected to bad memory chips and in the future we will instruct our
 users who are having this problem to run memtest86 overnight to check that
 the memory is physically OK. These constraints problems are actually the
 most common that we see.


Clever memory problem to corrupt just the key or the constraint check.
Certainly it's worth checking that the memory is OK.  I'd also check that
the referencing key looks generally sound.  Do you add referential
constraints to existing databases?  A problem with broken constraints is
that the error doesn't leave traces, so a reproducible case would be very
helpful, but very hard to produce.




 Gfix is pretty old and somewhat crude.  IBFirstAid might give you better
 help on physical corruptions.  Checking that there is no non-conforming
 data before creating constraints may help with logical corruption.


 Yes that would probably be a better choice for us, but we cannot bundle
 IBFirstAId togethe r with our application. Will however download it and
 try it on files to got sent to us.


The analysis tool is free - maybe your users could download it themselves
to look for evidence.  But it's not going to help with broken referential
constraints or mangled cache precedence.



 Another thing, what do you say about the posting above where the theory is
 that Volume Shadow Copy is interfering with the database? Have you heard
 about that before?


I'm quite sure that Volume Shadow Copy won't make good copies of an active
database or any other file that's open for random writes.  Whether it could
corrupt the original is an open question.  Lots of people claim to have
seen instances where copying a database corrupts the original.


 And another last comment. We have bundled Firebird w ith very many
 installations of our product and it might be the case that what we are
 seeing are very rare problems, that no one else has experienced before. Do
 you think we should post bug reports every time we see an exception or a
 problem that you have not already been made aware of?


Search the tracker (http://tracker.firebirdsql.org/browse) first to see if
the problem has been reported.  Then you might mention it on the support
list to see if there's something that looks like a user error so you won't
annoy the developers with stuff that the volunteers on this list could
resolve.  But if your getting errors with source file and line numbers, the
chances are good that you've found a bug.  Firebird is used pretty widely
and quite heavily in many installations.  However, the embedded form
probably gets less stress in the world than any of the architectures, so
you may be stressing something unusual.   No development group, open or
closed source, can fix bugs it doesn't know about.

Thank you for working with Firebird on these problems.

Good luck,

Ann

Re: [firebird-support] BLOB

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Fri, Oct 10, 2014 at 9:58 AM, tiberiu_horv...@yahoo.com
[firebird-support] firebird-support@yahoogroups.com wrote:



 my BLOB records are plain text phrases, somewhere between 1 and 400 char-s
 , containing setup information (in Delphi I save a TStringList to file and
 I backup that file in my database).


Hmmm.   What is your page size?  How big are the records (excluding
blobs)?  Those are very small blobs and are probably being stored on the
same page with the data.  That's interesting but probably not significant.
If you know that your blobs will always be text and never exceed 500
characters or so, you might be better off using a varchar field assuming
that the change doesn't cause your record to exceed 64K bytes.  There's a
slight overhead for reading a blob, even a small one on the same page,
because the application has to read the record first, get the blob id, then
read the blob using the id.  Two reads to the same page in cache is not
significantly expensive, but it is more expensive than reading once.
Firebird's record compression will eliminate the extra space - declare your
field to be varchar (800) - you'll waste a few bytes of compressed nothing,
but less than the blob overhead.


 My question was about saving these tables with gbak -v (verbose) where I
 can see the time spent to backup / restore each table. Can I speedup this
 process somehow ?

 1. maybe gbak backup without some tables


Nope.  Gbak creates a new database on restore.  A new database with only
the volatile tables just won't be the same.

2. maybe some magic gbak switch that knows that my BLOBs are text only


Text, binary, it's all the same to gbak.   Try the -g suggestion - if gbak
is cleaning out garbage, it's slow.

Good luck,

Ann

 

Re: [firebird-support] Performance of Firebird (Superserver vs Superclassic, etc.)

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Oct 29, 2014, at 9:36 PM, Geoff Worboys ge...@telesiscomputing.com.au 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 Doesn't that depend on how they implemented the separate threads?
 SS does/can use different threads for separate connections to the
 same database, can't it?

Yes.

  So if the threads operate over separate
 connections then wouldn't SS be able to use multiple CPUs?

The threading was designed for a single CPU machine so they don't make any 
effort to share data structures.  Multi-threaded, yes.  But only one thread can 
run at any one time.  That's what's great about V3 - threads running in 
parallel with a shared database page cache.

Cheers,

An
 

Re: [firebird-support] Re: In Firebird can you tell when a Stored procedure has been compiled

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

Having corrected some top-posting for clarity...



 On Thu, 18 Dec 2014 18:28:56 -0500, 'Edward Mendez' emendez...@nc.rr.com

 [firebird-support] firebird-support@yahoogroups.com wrote:
  Can you tell in Firebird 2.1.x or 2.5.x when a stored procedure has been
  compiled?



 It is compiled (to BLR) at the moment you execute the CREATE, ALTER,
 CREATE OR ALTER or RECREATE statement.

 Mark

  On Sat, Dec 20, 2014 at 10:31 AM, 'Edward Mendez' emendez...@nc.rr.com
 [firebird-support] firebird-support@yahoogroups.com wrote:



 Mark,



 Thank you, I kind of figured that.



 Maybe I should have elaborated on my situation. I was given a FB Database
 and I need to verify if any of the stored procedures have been
 compiled/changed after a certain date. From what I can gather from the RDB$
 tables, it looks like this is not feasible. But I needed to make sure.




Err, it's slightly more complicated than that.  When you create or alter a
procedure, the PSQL is translated into BLR (Barry Louis Rubinson, also
Binary Language Representation).  Aside from being inscrutable and binary,
BLR is really no more compiled than PSQL.  A procedure is compiled on first
reference after the database is opened (for SuperServer) or the connection
established for Classic.  Compilation means that the data references are
validated and translated into their internal names, conjuncts
(relationships between tables) are evaluated and equalities distributed,
indexed paths identified and their cost analyzed, and actions are
translated into a tree of execution nodes.

None of that addresses your present problem, which is identifying
procedures that have been changed recently.   This link may help:
http://www.firebirdfaq.org/faq210/

Cheers,

Ann

Re: [firebird-support] Deadlock exception occurs but it shouldn't?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Dec 24, 2014, at 3:22 AM, brucedickin...@wp.pl [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 I have two threads which constantly and at the same time are writing to this 
 table:
 
 UPDATE OR INSERT INTO PARAMS (NAME) VALUES(:P_NAME) MATCHING (NAME) RETURNING 
 ID;
 
 
 I've set my transaction parameters like this:
 
 FtraMain.TRParams.Add('isc_tpb_write');
 FtraMain.TRParams.Add('isc_tpb_read_committed');
 FtraMain.TRParams.Add('isc_tpb_wait');
 FtraMain.TRParams.Add('isc_tpb_no_rec_version');
 
 As far as I understand, such configuration should prevent deadlock exception 
 to occur. However, deadlock still occurs from time to time:
 
 Deadlock.
 Deadlock.
 Update conflicts with concurrent update.
 Concurrent transaction number is 57258.
 


The Deadlock error is somewhat misleading.  This is not a classic deadlock of 
the sort that databases that implement lock-based concurrency get.  However, 
the solution is the same as for a deadlock (i.e. roll back and retry your 
update) so at a high level, deadlock isn't a bad description.

What you're seeing is Firebird's way of avoiding dirty writes in a system with 
multiple record versions.  The rule is that if the most recent version of a 
record was not committed when your transaction started, then you can't update 
that record.   In concurrency mode, which provides a stable snapshot of the 
database, the rule is the minimum necessary to avoid losing concurrent writes. 


Read_committed mode was added later to meet some programmers' expectation 
that a transaction would always see the most recently committed version of a 
record, and to hell with consistency.  However the rules for update conflicts 
were not changed at the same time, so even if you can see a change that's 
committed now but wasn't when you started, you still can't update that record.

Good luck,

Ann

Re: [firebird-support] Re: Firebird Embedded open database file on network share

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Jan 22, 2015, at 5:06 AM, ralf.erdm...@web.de [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 So I now know I had no good idea with the network share :-)

I hope I made that clear :-)
 
 I thought it possible because I read that firebird embedded is at most the 
 same engine as the firebird server.

It is.  But the Shared Server has a single process mediating all physical 
access to the database file and the Classic Server uses interprocess 
communication to avoid physical access conflicts.
The Embedded Engine mediates conflicts between connections it makes, but has no 
way to detect the existence of other Embedded Engines accessing the file 
through a network share.

Good luck,

Ann
 

Re: [firebird-support] Problem with sub-optimal query plan

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Feb 3, 2015, at 8:17 AM, Fulvio Senore mail...@fsoft.it [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 I want to retrieve rows showing INVOICES data and some CUSTOMERS data so 
 I use an inner join, and I want to see newer invoices first so I add an 
 ORDER BY clause. I only need a few rowssince I will show only the latest 
 invoices in a grid.
 The query is something like
 
 SELECT INVOICES.*, CUSTOMERS.NAME
 FROM INVOICES INNER JOIN CUSTOMERS ON INVOICES.CUSTOMER_ID = 
 CUSTOMERS.CUSTOMER_ID
 ORDER BY INVOICES.YEAR DESC, INVOICES.NUMBER DESC
 
 I have created a descending index on INVOICES.YEAR, INVOICES.NUMBER
 
 The problem is that if the tables are large the query is rather slow. 
 Looking at the plan I see:
 
 PLAN SORT (JOIN (CUSTOMERS NATURAL, INVOICES INDEX (FK_INVOICES_CUSTOMERS)))
 
 so the database loads all rows and then it sorts them.Of course it is slow.
 

Unfortunately, there's nothing in your query that limits the number of invoices 
you return for each customer.  If you actually wanted all the invoices, the 
sort would be faster than the random retrieval by invoice index.  But you 
don't.  And what you want is not the first customer/invoice pair, but all 
customers and only the first invoice from each.

You might try something like this:

select c.name, (select first 1 i.* from invoices i 
where i.customer_id = c.customer_id
order by i.year desc, i.number descending)
  from customers c
 

If you have customers without invoices, you could add a where exists ... to 
the end of the query.


Good luck,

Ann
 

Re: [firebird-support] How to join records

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Feb 16, 2015, at 4:22 AM, josef.gschwendt...@quattro-soft.de 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 we have 2 tables
 For each record in Table1 there are 2 records in Table2.
 
 Table1 (T1)
 ==
 1
 2
 
 
 Table2 (T2)
 
 T2T1
 ==
 11
 21
 32
 42
 
 What is the cheapest way to get a dataset like below?
 
 T1T2aT2b
 ===
 1   12
 2   34
 

Can't guarantee it's the cheapest, but you might try

select T1.T1, max T2.T1 T2a, max T2.T2 T2B
from T1 
  inner join T2 on T1.T1 = T2.T1
  group by T1.T1


Good luck,


Ann

Re: [firebird-support] How to join records

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Feb 17, 2015, at 7:26 AM, Svein Erling Tysvær 
svein.erling.tysv...@kreftregisteret.no [firebird-support] 
firebird-support@yahoogroups.com wrote:

 
 Could Common Table Expressions or Derived Tables bring any advantages?
 
 I suppose you could use a combination of the two suggestions:

[good suggestion omitted]
 
 EXECUTE BLOCK

[and another]
 
 is also an alternative.

The original request mentioned performance.  Generally EXECUTE BLOCK requires 
more processing than a statement that can be prepared once and executed many 
times. 
 
Cheers,


Ann

Re: [firebird-support] There is a simple way to rename constraint that have not a name?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Jan 30, 2015, at 1:40 PM, Luigi Siciliano luigi...@tiscalinet.it 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 There is a way to rename constraint that have not a name?
 
 
 UPDATE
   rdb$relation_constraints
 SET
   rdb$constraint_name = 'MyPrimaryKeyName'
 WHERE
   rdb$relation_name = MyTable
   AND rDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
 
 There are contraindications?
 
 

Sigh.  In V3, system tables are read only except through DDL. I don't know 
whether the DDL has been extended to include renaming constraints.

Good luck,

Ann
 

Re: [firebird-support] FW: FB 2.5.1 db's needing restore

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Mon, Jan 12, 2015 at 10:40 AM, Rudi Feijó rudi.fe...@multidadosti.com.br
[firebird-support] firebird-support@yahoogroups.com wrote:





 *I noticed that, when upgrading to 2.53, it is highly advisable to restore
 db’s that were on 2.5.1, as stated in the docs :*

 *Warning re Databases Created or Restored under Firebird 2.5.1*

 All users upgrading from Firebird 2.5.1 to a higher sub-release are
 strongly advised to migrate databases using *gbak* backup/restore. If
 this is impracticable, at least rebuild all compound indices in the
 databases being migrated.

 Databases being upgraded from older Firebird versions (ODS 11.1 and lower)
 or v.2.5.0 are not affected by this regression.



 My question is, do I need to perform the gbak on 2.5.1, then the restore
 on 2.5.3 ? Or is it ok if I simply gbak/restore on 2.5.3


To the best of my recollection, the problem was specific to compound
indexes.  Gbak doesn't copy indexes, it recreates them in the new version
of the database, so it shouldn't matter which version of the server or gbak
you use to create the backup as long as you use the new gbak and new server
to restore the database.

Good luck,

Ann

Re: [firebird-support] Re: Speed issues

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Feb 9, 2015, at 11:52 AM, 'Leyne, Sean' s...@broadviewsoftware.com 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 What is your cache settings

[2048]
 
 
 Zoran's complaint is about the first time the query is executed.  In that 
 case, nothing would be in cache and everything would still need to be loaded 
 from disk.
 
A 2048 page cache may be small enough that it would cause problems even during 
loading, given the size of his database and the tables involved.  Having to 
reload the same pointer pages would exacerbate his problems.  Given that the 
access is indexed, pointer pages and intermediate index pages may need to be 
loaded several times after they've forced all the metadata pages out.  

It can't hurt to us a much larger cache.  

Good luck,

Ann
 

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

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 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

Re: [firebird-support] Database paths and aliases

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Mar 21, 2015, at 6:29 AM, Emil Totev em...@tot-consult.com 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 Can there be any problem with a database, accessed simultaneously by
 some or all of the following methods:
 
 -- database file full path, /srv/firebird/mydb.fdb
 -- database alias, mydb = /srv/firebird/mydb.fdb
 -- another alias, another = /srv/firebird/mydb.fdb
 -- /srv/firebird/link, which is a symlink to /srv/firebird/mydb.fdb
 -- /var/firebird/mydb.fdb, where /var/firebird is a symlink to /srv/firebird
 
 I'd expect firebird to be clever enough to know that it is serving the
 same file, but I'd like to be sure it is true, and for all the
 architectures (classic/super).
 

Yes, Firebird is at least that clever on all platforms, which is critically 
important.  In the ancient past there were cases where file sharing went 
undetected, but the last one I remember was before Firebird 1.0.  

Good luck,

Ann

Re: [firebird-support] Connect via TCP and shared file parallel

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Mar 22, 2015, at 7:01 AM, 'Parzival' parzival1...@gmx.at 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 I am running Firebird 2.5.0 on Windows for some time on several customer 
 locations. When the client and the server are in the same network the 
 performance is good. It gets difficult when a more complex network structure 
 (eg. Access via VPN) is in place. I see then error 10054 in the firebird.log 
 and the client needs a lot of time to read data form the database.
 

I can't help you with that, though others on the list may have suggestions.
  
 
 Searching for solutions I understand the the root cause of the error 10054 is 
 on the network level. In the case the current network setup needs to remain 
 unchanged I consider to access the firebird DB via a shared file:
 
 Using Z:\mysharedfolder\mydb.fdb instead of remotehost:myalias
 
 A s far as I have tested it this works also when some clients access via TCP 
 and others via the file.   Are there any known problems or gotchas on that 
 approach?
 

Yes.  Unless there is a managing Firebird server or a set of cooperating 
internet servers running on one machine, there's no coordination between client 
changes.  The database will rapidly become corrupt.  If your tests were read 
only or single client, you may not have seen corruption.   Continue and you 
will.  (See previous message.)  

Good luck,


Ann
 

Re: [firebird-support] Newbie question on how to hold an unusual data type

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Mar 6, 2015, at 9:58 AM, brian br...@meadows.pair.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 My problem is that I must avoid duplicated records in the database,
 the unique key is a complicated structure containing four 16-bit words
 plus a 108-bit set of flags. The combination of the whole lot must be
 unique. I don't need to retrieve this data other than to check for
 duplicate records, so I can massage it in FreePascal so that it can go
 into the database in any form that's desirable. 

If there's any chance that you'll ever need data fromt the four 16-bit words, 
I'd create a five part primary key with four small integers followed by a 14 
byte string octets.  Firebird index keys are compressed and rearranged so they 
compare bytewise regardless of the original format - single field or multiple, 
combinations of strings and numbers, etc.  If it were me, I'd let Firebird 
create the key from data.
 
 
 If I were using PostgreSQL, I'd store the whole lot as a single
 172-bit bitstring, but I can't find any mention of an equivalent data
 type in the Firebird documentation that I've been able to find. 

String of type octets works.
 

Good luck,

Ann

Re: [firebird-support] Newbie question on how to hold an unusual data type

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Mar 7, 2015, at 9:43 AM, brian br...@meadows.pair.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 Thanks folks, particularly to Ann - that bit about the way Firebird
 arranges the indexes itself was the clincher. It seems difficult to
 find a full manual for Firebird unless I speak Russian (I don't!), all
 there seems to be on the main site are updates - unless this is meant
 to mean the full manual updated to version 2.5, in which case it's a
 strange (and misleading) way to put it. 

Long standing problem.  When Inprise released the InterBase sources, they 
didn't release either the doc set sources or the test system.  
InterBase/Firebird was already a very large product and creating an open source 
doc set was daunting.  Helen Borrie wrote a very complete book for APress, 
which has been updated and is available on-line, but not free.  There's a 
project to translate the Russian book to English. 

Good luck,

Ann

Re: [firebird-support] windows client debian server

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Mar 7, 2015, at 7:07 AM, wolfgangschammhaust...@yahoo.com 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 very basic, using on Linux debian 7 firebird2.5-super, must i use on the 
 windows7 clientside the same version? 
 

   No.  You can use an older or newer client.  The older client may not be able 
to use some features of a newer server.  In the long run, it's probably better 
to match server and client library versions, but a little bit of skew won't 
matter.

   What you must do is match the client library and the client application in 
terms of the address size - a 64-bit application needs a 64-bit client library, 
and a 32-bit application needs a 32-bit client library.  Either client library 
can access either size Firebird server.  And, of course, the client doesn't 
care if the server is Super, Classic, or SuperClassic.
 


Good luck,

Ann
 

Re: [firebird-support] How do I get my database back?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Mar 24, 2015 at 11:42 AM, Tim Ward t...@telensa.com
[firebird-support] firebird-support@yahoogroups.com wrote:

 gbak: ERROR:validation error for column BOXNUMBER, value *** null ***
 gbak: ERROR: warning -- record could not be restored
 gbak:Exiting before completion due to errors


I'm glad you got most of your database back.  I think the IBSurgeon people
have a tool that  will let you fix a backup file - maybe to the level of
mucking with individual constraints.


 So, at some point prior to the backup someone had added this NOT NULL
 column, and hadn't gone round setting the values in the column. Yes, well,
 there's then the question about how come you're allowed to get a database
 into such an illegal state in the first place, isn't there.


 Yup.  Although the core of Firebird has move forward, some parts still
reflect decisions that were made more than a generation ago.  Specifically,
Firebird doesn't (generally) validate constraints when they're added.  When
computers were small and slow and processing time was precious, we felt
that good programmers always validated constraints before they added them,
so having the database revalidate was a waste of money and penalized the
responsible to protect the lazy.


 *** BUT *** this is a you had one job issue, isn't it?

 The one and only job of a backup utility is to create a backup that can
 be restored. If it doesn't do that it's failed. At its one and only job.


Perhaps you'd be happier with NBackup, which doesn't understand the
database structure at all - it just backs up pages and, when asks, puts
them back together at some previous state.


 S many other approaches could have been taken.


Most of the utilities, including gbak, are properly layered on the
database.  One of the original philosophies (besides not duplicating the
work a responsible database programmer or administrator would do
automatically) was that any function that one of the tools needed was
probably something that an application would need sooner or later. Keeping
tools properly layered meant that we couldn't invent a magic hook to fly us
out of a corner we'd painted ourselves into. But it does limit some of
magic that might be nice.



 , that wouldn't have
 lost my database for me, including but probably not limited to:

 (1) During backup, fail if the backup file being created is one that it
 won't be able to restore.


That would make the backup slower - there may be other ways a layered
application can validate data it reads, but the one that comes to mind is
simultaneously building a backup file and a new database.


 (2) During restore ... er, just restore it anyway? - in its previous
 state the database was, strictly speaking, illegal[#], but it was, in
 real life, working fine. If gbak took this option, with a warning, then
 I'd be able to fix the data ... but as it is, I can't, because I can't
 restore the database.


It would be relatively easy - well sort of - to skip offending records
rather than just blowing off whole tables.  But a layered application can't
store invalid data.  Just imagine the uses of a connection parameter that
says ignore all validation.


 Yes I did discover no_validity, with which the restore did create *a*
 database, but it was a completely ing useless database as the NOT
 NULL constraints appeared to have been dropped everywhere, and who knows
 what other manglings had taken place (the documentation doesn't list
 them explicitly, it just says deletes validity constraints from
 restored metadata).


The theory is that the constraints are all there, just inactive.  Yes, it's
going to be a pita to find them, turn them on one at a time, and test them.




 Which means that gbak had a third option to get it
 right:

 (3) Provide a restore option that warns about validity checking errors,
 rather than failing them, but doesn't actually delete the checks from
 the database.


As above. I suppose gbak could catch errors, and work back somehow to the
original constraint - not sure that's at all clear from the error codes -
commit and maybe disconnect, then start a transaction, deactivate the
constraint, send out a message, and continue.


 So, please, how am I expected to get my database back?


I'm assuming that you've trashed the original database and can't start from
there. If you can't afford to lose a table with an invalid constraint, you
might try the IBSurgeon tool.  Or restore the database twice, once,
metadata only with the constraints active and once with data but no
constraints.  Then pump the data from the second to the first using one of
the third party data pump tools.

Or, follow the recommendation that you backup regularly and
restore periodically just to be sure your backups are good.  Gbak isn't the
only backup tool that's let me down over the decades.  And it's a lot more
malleable than the disk backups I've fought with.


OK OK, so I don't
 actually need the data in the one table causing the problem (there
 

Re: [firebird-support] Scope of uniqueness constraint?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Apr 1, 2015, at 5:51 AM, Tim Ward t...@telensa.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 
 (1) Transaction 1 - check for EXXON, find it doesn't exist
 (2) Transaction 1 - create EXXON
 (3) Transaction 2 - check for EXXON, find it doesn't exist (because it 
 can't see the one created by transaction 1)
 (4) Transaction 2 - create EXXON
 (5) Transaction 1 - commit
 (6) Transaction 2 - commit
 
 This fails, as one would expect, due to the violation of the uniqueness 
 constraint. But my question is: does it fail at point (4), because the 
 uniqueness constraint is somehow active/visible/whatever across 
 transactions, or does it fail at point (6), because the uniqueness 
 constraint only takes account of committed stuff?

In a WAIT transaction, Transaction 2 will stall after step 4 and receive an 
error after step 5.  That avoids a possible live lock that could occur if 
Transaction 1 fails between step 2 and step 5.  In some pathological cases, the 
two transactions could kill each other perpetually.

In a NO WAIT transaction (80% certainty) Transaction 2 gets an error on step 4, 
without waiting for Transaction 1 to commit. 

In no case will Transaction 2 proceed beyond step 4 unless Transaction 1 rolls 
back.  Firebird knows there's a problem.  In the WAIT case, it stalls the 
second transaction until the first finishes. 

Good luck,

Ann

Re: [firebird-support] Character sets. (Again.)

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Mar 30, 2015, at 7:01 AM, Tim Ward t...@telensa.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 Sorry about this, but I really can't work out what's going on here, and 
 could do with some clues.

Not so much a clue as a question.  If you declare the columns as varchar, do 
you see the same behavior?  Passing a fixed length string with variable sized 
characters is tricky.  Do you hand the client enough buffer to handle the 
declare number of characters regardless their oddity, or do you pass enough to 
hold the string that currently exists?

 
 The basic problem is that I'm getting string values of columns into PHP 
 padded on the right to four times their correct length.
 
 I've tried various combinations of things and got various weird results. 
 Here's one example:
 
 (1) I believe the database default character set is UTF8 (although I 
 don't know in detail what that means or what it's used for)
 
 SQL select rdb$character_set_name from rdb$database;
 RDB$CHARACTER_SET_NAME
 UTF8
 
 (2) I've got a column in a table defined as ASCII (not that that makes 
 an awful lot of difference, I get very similar results if it's UTF8):
 
   METERID   CHAR( 8) CHARACTER SET ASCII 
 COLLATE ASCII,
 
 (3) I connect ISQL without a -ch command line parameter and get the 
 field arriving in ISQL as eight characters long:
 
 SQL select meterid from tblmeterchange;
 
 METERID
 
 0001A6BN
 0001A6BN
 
 (4) I connect ISQL with -ch UTF8 and get the field arriving in ISQL as 
 32 characters long:
 
 SQL select meterid from tblmeterchange;
 
 METERID
 
 0001A6BN
 0001A6BN
 
 (5) I connect via PHP using ibase_connect with UTF8 as the character 
 set parameter and get this eight character field returned as the eight 
 wanted characters padded on the right with 24 spaces.
 
 (6) If I change the definition of the METERID column to UTF8 I similarly 
 get 32 characters in PHP.
 
 So what should I be doing to get an eight character value of an eight 
 character field turning up as eight characters in PHP? (I don't think I 
 have the option of setting the connection charset to anything other than 
 UTF8 because I don't know what else it might affect and there are plenty 
 of other columns in the database which, unlike this one, can't be 
 declared as ASCII ... but then I don't know exactly what the concept of 
 connection character set means or does anyway.)
 
 I have read 
 http://www.firebirdsql.org/file/community/ppts/fbcon11/FbCon2011-Charsets-Heymann.pdf,
  
 but that doesn't help - it suggests that Firebird will transliterate as 
 necessary, but padding an eight character string with 24 spaces to make 
 a 32 character string isn't any sort of transliteration I'm familiar with.
 
If you're storing the data as UTF8 and asking for UTF8, then I guess you get a 
buffer big enough to hold the maximum sized eight character string.

Good luck,

Ann
 

Re: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Feb 24, 2015, at 3:29 AM, Svein Erling Tysvær 
 svein.erling.tysv...@kreftregisteret.no [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 Sukhen wrote:
 update never fails but it fails in insert query. loop run in a single 
 connection with a new datarows in same query.
 
 I answered:
 Could it be that another transaction not visible to your current 
 transaction has inserted a row that creates a lock conflict with your 
 insert and that your code repeatedly tries (and fails)
 to insert?

I missed the fact that it was an insert, not an update that failed.  That could 
be a conflict with a long running transaction which has inserted the same value 
for a primary key.
 Or that it is a real deadlock, where one transaction first successfully 
 updates record A, then fails on record B,
 whereas another transaction has successfully updated record B and fails on 
 record A?

That should be reported immediately.
 
 Typically, there would be no problems on INSERT, whereas UPDATE could 
 create problems. Getting the problem on INSERT, I'd say 
 indicates some kind of lock conflict which means that you have some UNIQUE 
 field or constraint or don't use generators to 
 populate your primary key.
 
 Sukhen answered:
 is it could be any buffersize/memmroy issue. please, suggest if it is how to 
 check it in firebird
 please, somebody help me out

Buffers and memory shouldn't cause the process to stall for hours.
 

Best regards,

Ann

Re: [firebird-support] Re: Is there still a difference between sweep and gabage collection?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Feb 23, 2015, at 1:06 AM, SUKHEN DASS sukhend...@yahoo.com 
[firebird-support] firebird-support@yahoogroups.com wrote:
 
 
 I am using vb.net which has a grid of 100,000 records and 30 columns have to 
 insert in a firebird table.

First, you've hijacked a thread, which is not considered polite on this very 
traditional email list.

 The insertion will take place if product id does not exist else record to be 
 update.

Right - and I'm assuming you've got more than one thread working at a time.

 But inside the loop while executing ExecuteNonQuery control does not come 
 back wait for 10 hours still not came and no error message and it happens on 
 random rows not always for the same recrod

OK.  What's happening is that one thread has updated the record and gone off to 
do something else or gone to sleep.  It's really important that you commit your 
transaction after updating a record unless there's more than one record that 
must be updated to make the database logically consistent.

 I am struck past 7 days i am working googled but no solution Finally i 
 decided to firebird support team

You've come to the right place.  What you should do is commit after each record 
insert/update and probably choose the no wait transaction option.  What's 
happening is that one thread is updating record A without committing the update 
then another thread is trying update the same record.  Because there's an 
uncommitted update, the second update waits, hoping the first will fail and it 
can succeed.  But its hopes are in vain.  The first thread continues on its 
merry way, ignoring the trail of uncommitted changes it leaves in its path and 
the other threads, waiting for it to end.
 
 My project is getting delayed please, help

Read some more about transaction semantics, or just get in the habit of 
commiting after each set of changes that leave the database consistent.

Good luck,

Ann
 

Re: [firebird-support] Re: Is there still a difference between sweep and gabage collection?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

Thomas,

Gbak backup removes all unnecessary back versions and all rolled back changes, 
unless it is run with the -g switch.  That switch is provided for those cases 
where a database cleanup is not needed.  Examples include the case when the 
database will immediately be replaced by a restored version, upgrading the ODS, 
for example, or when a sweep has just been run.  

What gbak does not do that sweep does it change the identity of the oldest 
interesting transaction.  Unlike the oldest active transaction, the oldest 
interesting transaction is not critical for performance, but having it correct 
does make things slightly better.  The oldest interesting transaction is the 
first transaction, still visible in the database, that did not commit.  Any 
data created by older transactions is known to have been created by committed 
transactions.  A transaction reading it doesn't have the check the state of the 
creating transaction.  When accessing data created by a more recent 
transaction, the reader must test a bit in a bit vector to determine the state 
of the creating transaction.  Pretty cheap.

During a sweep, gfix notes the oldest transaction whose work it could not clean 
up.  At the end of the sweep, that becomes the oldest interesting transaction.

In the absence of the -g switch or the attachment parameter that implements 
that switch, any process that reads all the data in a database removes all 
unnecessary old or rolled back record versions, either itself or through the 
proxy of the garbage collect thread.  Only gfix changes the recorded oldest 
interesting transaction.


Cheers,


Ann

Re: [firebird-support] Newbie question on how to hold an unusual data type

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Mar 7, 2015, at 4:12 AM, 'Louis van Alphen' lo...@nucleo.co.za 
[firebird-support] firebird-support@yahoogroups.com wrote:
 
 For storage purposes I would use normal column types, ints, whatever.

OK.  

 For duplicate checking, I would in the app, calculate a unique hash
 from all the necessary fields taking part in the duplicate check.

Much less OK.  Hash functions do not produce unique results - even hashing to a 
range much larger than the intitial string will produce duplicates.  See the 
birthday problem.  So by hashing you're taking what may well be a unique value 
and mashing it into something that's likely to have duplicates.

 That hash goes into a varchar column that has a unique constraint.

Why store everything twice?  Firebird handles unique and primary key 
constraints on multiple columns reasonably efficiently.  What this process does 
is double the storage while introducing accidental duplicates.  At the same 
time, it leaves in place all the processing Firebird does to create an easy to 
compare key from the given data. Which is good, but all the work done in the 
application is wasted.


 The db engine then enforces uniqueness..
 

True and very important.  Uniqeness can only be determined by the database 
engine.  If two transactions attempt to store the same value in a column, 
neither can see the other's entry and each will see its action as preserving 
uniqueness.  The Firebird server sees all and knows all, and it will prevent 
concurrent transactions from violating unique or primary key constraints.

For those of you who follow the dev list and saw a request for a READ 
UNCOMMITTED mode, don't leap up and down saying There it is!  There's the 
reason for READ UNCOMMITTED!  If I only could read uncommitted records, I could 
(at great expense and inconvenience) maintain uniqueness in my application 
without relying on the Firebird engine.  

A moment's calm reflection will reveal the flaw in that thinking.  Let us 
assume that you want to enter Arbuthnot into a column that should not contain 
duplicates.  When your transaction starts, there's already an Arbuthnot 
committed, but a concurrent transaction has deleted it without committing.  You 
read the uncommitted deletion, conclude that your Arbuthnot is the only 
Arbuthnot, store it and commit.  The concurrent transaction rolls back its 
deletion and now there are two Arbuthnots, both created by committed 
transactions.

Here endeth the lesson.

Cheers,

Ann

Re: [firebird-support] large system slows over time

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

Hi Nick,
 
 I have a system that is slowing down the longer it stays running and I'd like 
 to know why.
...
 
 Is there any evidence I can gather before I reboot the system which I expect 
 (from past experience) will return the system to the better performance
 

Hmmm   If rebooting will solve the problem and you don't have a very long 
running transaction, then I doubt that garbage accumulation or collection is 
the source of the problem.  
Nor is there anything else wrong with your physical database - fragmentation or 
whatever.
I'd look at memory usage - using both Firebird and OS tools.  Look for paging 
before you reboot.
After you reboot, track memory usage daily or more often.   It doesn't take 
much of a leak - or unfortunate caching - to built up over month of 27 tps.

Accelerating the move to 2.5.4 would also be a good idea, both because it may 
correct the problem and because it probably offers better diagnostic tools for 
this sort of problem.  

Good luck,

Ann

Re: [firebird-support] large system slows over time

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On May 5, 2015, at 2:38 PM, Jesus Garcia jeg...@gmail.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 Hello, I think the problem is the database fragmentation along the time. I 
 have databases in production 24x7x365.  If I backup and restore on a test 
 environment, the time is good. 

In your case, yes data distribution in the database is a likely culprit, though 
disk I/O speed is not always best with contiguous data.  However Nick says his 
database speed improved just by restarting the server or rebooting the system.  
That's unlikely to defragment the database. 

Good luck,

Ann

Re: [firebird-support] Why index is not used in this query?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On May 15, 2015, at 9:02 AM, brucedickin...@wp.pl [firebird-support] 
 firebird-support@yahoogroups.com wrote:

 SELECT * FROM 
 TABLE_2 T2
   INNER JOIN
 TABLE_1 T1
   ON
  T2.TABLE_1_ID = T1.ID
 
 After executing this query I am getting such plan:
 
 PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2))
 
 
 Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am 
 getting error when i try to enforce it with: 
 PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2))
 
 
 
There's nothing to look up in the primary key index of T1 - indexes are used to 
find records withh specific values in the key fields.  If you added another 
condition like T1.ID between 5 and 10, you'd get the plan you expect.

Good luck,

Ann




 here is full example:
 
 
 CREATE TABLE TABLE_1
 (
   ID INTEGER NOT NULL,
   NAME VARCHAR(32),
   CONSTRAINT PK_TABLE_1 PRIMARY KEY (ID)
 );
 
 GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
  ON TABLE_1 TO  SYSDBA WITH GRANT OPTION;
  
 CREATE TABLE TABLE_2
 (
   ID INTEGER NOT NULL,
   TABLE_1_ID INTEGER
 );
 
 ALTER TABLE TABLE_2 ADD CONSTRAINT FK_TABLE_2
   FOREIGN KEY (TABLE_1_ID) REFERENCES TABLE_1 (ID);
 GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
  ON TABLE_2 TO  SYSDBA WITH GRANT OPTION;
 
 
 INSERT INTO TABLE_1 VALUES(1, 'V_1');
 INSERT INTO TABLE_1 VALUES(2, 'V_2');
 INSERT INTO TABLE_1 VALUES(3, 'V_3');
 INSERT INTO TABLE_1 VALUES(4, 'V_4');
 INSERT INTO TABLE_1 VALUES(5, 'V_5');
 
 INSERT INTO TABLE_2 VALUES(1, 1);
 INSERT INTO TABLE_2 VALUES(2, 1);
 INSERT INTO TABLE_2 VALUES(3, 2);
 INSERT INTO TABLE_2 VALUES(4, 2);
 INSERT INTO TABLE_2 VALUES(5, 3);
 
 SELECT * FROM 
 
 TABLE_2 T2
   INNER JOIN
 TABLE_1 T1
   ON
  T2.TABLE_1_ID = T1.ID
 
 After executing this query I am getting such plan:
 
 
 
 PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2))
 
 
 
 Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am 
 getting error when i try to enforce it with: 
 PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2))
 
 
 
 Thanks for any clues.
 
 
 
 
 
 

Re: [firebird-support] Re: How to determine size of database structures?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On May 15, 2015, at 2:14 AM, brucedickin...@wp.pl [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 
 This is nice tool, however it does not show how much space is taken by given 
 table and its indices... I am sure this can be deduced by reading gstat 
 output file but I don't know how... 
 

Gstat doesn't report blob pages or data overflow pages (records larger than a 
page).  It may not report intermediate index levels. Nor does it report pages 
that support the structure of the database.  Even so,  I'm surprised that it's 
only reporting about half the size of the database.

Good luck,


Ann

Re: [firebird-support] garbage collect

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Apr 9, 2015, at 11:01 AM, Nick Upson n...@telensa.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 to make sure I've got this right

Not quite.
 
 starting with the same database I can do either
 
 a) gbak with gc

Won't advance the oldest active
 
 b) gbak without gc and gfix -sweep

Will move the oldest active and will get a few more records than a)
 
 and I will have the same final result

Best is gback with garbage collect plus sweep:  moves the oldest actives, gets 
the most old versions, shouldn't take much longer since the sweep doesn't do 
much.
 
 
 Extending this further, how can identify which tables contain (or did 
 contain) the garbage. 

gstat shows the number of versions present before you start.
 
 

good luck,

Ann
 

Re: [firebird-support] Database Size Is Not Reducing

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Apr 6, 2015, at 9:08 AM, Vishal Tiwari vishuals...@yahoo.co.in 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 I am using Firebird 2.5 database.

what operating system?

Cheers,

Ann

Re: [firebird-support] Database Size Is Not Reducing

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Apr 6, 2015, at 9:08 AM, Vishal Tiwari vishuals...@yahoo.co.in 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 I am using Firebird 2.5 database.  I have a table that has column of type 
 Blob field, this table also contains some other columns.
 
 The issue is, I had total 78,000 records in this table. What I did is as 
 every record was containing image in it, I then ran an update query and set 
 Null value to all the records for this blob column.
 
 I then backed up the database using GBack and then restored. After restored 
 the database there is no change in database size.   Any idea why it is so ?

No.  I assume you committed the transaction that set the blobs to null before 
you backed up the database and that the image blobs were of considerable size 
(more than 100 bytes or so).  How big was the original database?  How big is 
the recreated database?  Did you run gstat to see where the space is going?  

Just deleting data, without the gbak backup/restore will not reduce the size of 
the database.  Firebird's internal storage is complex - much more so than 
databases which store each table in its own file. Releasing space from the 
middle of a file is not possible. 
 
 I was referring to below link,  
 http://www.devrace.com/en/fibplus/articles/2161.php
 which says: 
 It is important to know and remember that in contrast to other fields, BLOBs 
 data are not stored in the table record. Table records store only BLOB_ID, 
 whereas BLOB body is kept in separate database tables.

That's not entirely correct.  If the blob data fits on the same data page as 
the record, it will be stored there.  If not, it will be stored on an overflow 
page.  Gstat doesn't report overflow pages - whether for large blobs or records 
larger than a page.  Or rather it didn't when last I looked.  Someone may have 
fixed that.  Even if gstat doesn't report them, overflow pages belong to a 
table.  They are located through records in table.  Their page headers include 
information that identifies the table they belong to, the record or blob they 
contain, and their sequence in that record or blob.
 
 Is because of this reason there is no change in database size?

No.
 
 If it so then, I am curious to know where does Firebird store the images?

On database pages - as above.

Good luck,

Ann

Re: [firebird-support] garbage collect

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Apr 5, 2015, at 11:23 AM, Nick Upson n...@telensa.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 
 is there any effective difference between doing gbak with gc and 
 gbak without gc and then gfix -sweep

The only differences (that I can think of) are caused by concurrent actions. If 
you garbage collect during backup, the sweep can remove new garbage created 
while the backup ran. 

Imagine that garbage collection takes enough time for fine thousand 
transactions to complete and each of those transactions makes three record 
versions obsolete. Neither gbak nor sweep can remove record versions made 
obsolete by the completion of a concurrent transaction. 

So if you backup without garbage collection, your backup is quicker, sweep 
starts sooner and takes longer so those 15,000 old record versions continue 
moldering in your database. 

If you backup with garbage collection and then sweep, the backup is slower, but 
the sweep starts later and can remove those unlovable record versions.  Sort of 
a wash, rinse, repeat thing - if that phrase means anything to those who aren't 
elderly 
Americans.  

Cheers,


Ann
From sunny Sint Maarten.  
 

Re: [firebird-support] Re: V1.56 query killing my V2.54 app

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Apr 8, 2015, at 10:05 AM, andrew_s_...@yahoo.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 Let's see when histograms come to the party, it'll make things more 
 interesting for sure. G
 

Histograms may  be less useful in Firebird than you might think, unless there's 
a lot of work done in query preparation.  In order to use the fact that there 
are lots more 0's than 1's in a two valued index effectively, Firebird has to 
know whether you're looking for a 1 (good) or as 0 (forget the index).  
Firebird queries are optimized when they are prepared, so without major 
changes, the histogram is useful only for literal values - not parameters.   
Probably a query could be partially optimized at prepare time and have a final 
optimization when the query is executed and all paramters are known.  However, 
there's also the case where values become known ony during the execution of a 
join.  Should Firebird do a row-by-row optimization of a nested loop join?

I wonder if you tripped over an improvement in Firebird.  In 1.5, Firebird kept 
only the total selectivity for compound indexes.  More recent versions keep the 
selectivity at each column - e.g. if you have a two valued column as the first 
part of an index and a very selective colum next, Firebird 1.5 considered the 
index a good candidate, even if you only matched the first part.  In later, 
smarter versions, it recognizes that the first part alone is not very good. 

Good luck,

Ann

Re: [firebird-support] Altering a domain

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Jun 16, 2015 at 11:15 AM, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] 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: Odp: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On 6/12/2015 2:11 PM, Jack Mason jackma...@mindspring.com 
[firebird-support] wrote:



Thank you very much.   I will try the  -o switch and the gfix 
suggestion as well.


Don't bother with gfix.  It does a physical database check - pointers 
point to what
they should, pages are the right type, etc.  Your problem is with the 
logic of the database,

specifically with one of the grants.

From a very historical point of view, InterBase was created with a 
security model that
everything was allowed unless prohibited.  SQL's model is that nothing 
is allowed
unless permitted.  Some odd things were done to convert from the 
InterBase model
to the SQL model and not all of them were done well.  You seem to have 
tripped over
one of those issues, probably something that worked OK with earlier 
versions of

Firebird, but which contemporary Firebird rejects.

Good luck,

Ann

Re: Odp: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On 6/11/2015 11:05 AM, Jack Mason jackma...@mindspring.com 
[firebird-support] wrote:




However, that won't help us.  Our concern is that we have been backing 
up our databases for years and it has been a fruitless exercise.  We 
cannot restore them.  We did not have that problem with Interbase 6 
but switched to Firebird because it was touted as being modern, 
safer etc.  Now we find it is virtually worthless unless we can 
restore a database it has backed up.


OK.  First, there's a -o switch that you should use on the restore if 
the normal restore gets an error.  That
switch tells gbak to commit after creating the metadata and after 
loading data for each table.  That will
get you through most problems in backup files.  In this case, it will 
get you all of your data, indexes, and

constraints.

Second, the problem is somewhere in the privileges you've defined ... I 
can't tell which from the

error message.

Third, IBSurgeon has tools that will allow you to fix most broken gbak 
backups.


Fourth, in the future, restore one out of ten backups to be sure that 
the backup procedure is actually working. That's a good precaution on 
any kind of backup.  Once in ancient history, we realized after a crash 
that we'd been backing up to /dev/null - great optimization, not so good 
for recovery.


Nbak has its strengths and weaknesses as well.

Good luck,

Ann

Re: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On 6/11/2015 4:33 PM, Jack Mason jackma...@mindspring.com 
[firebird-support] wrote:


Why is everyone else not having this problem?  The databases are 
Firebird databases.  For 15 years we had no corruption from Interbase 
6.  Two years ago, we backed up our databases with Interbase 6 gbak 
and then used Firebird 2.52 to restore them.


Firebird 2.52 will back them up, but will not restore them without 
getting the trigger (3) error.  Since we have 5 databases, each 
built by Firebird two years ago from Interbase 6 backups and backed up 
for two years by Firebird 2.52 gbak, and totally different programs 
accessing the databases, it appears the corruption is/has been caused 
by Firebird.


Yet, no one else has seen this problem?

The problem may be that InterBase 6 and older Firebirds still used some 
non-SQL style grants.  Over time,
Firebird has become fussier about correct syntax and semantics.   If you 
still have an old database, perhaps

you can look at the privileges especially those on system tables.

Good luck,

Ann

Re: [firebird-support] Re: Differences when adding a Primary Key

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente' 
 sistemas2000profesio...@gmail.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 Without using CONSTRAINT doesn't work and in such case the name is choosed by 
 Firebird, not for me.
 
 
 For add a Primary Key to a table we can write:
 
 ALTER TABLE MyTable ADD PRIMARY KEY (ID);
 
 or we can write:
 
 ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID);
 
 In the first case, the Firebird puts the name of the Primary Key, in the 
 second case we choose that name, but...
 
 Why sometimes the first case fails and the second case always work?
 
 But (just sometimes) the first case fails.
 

How does it fail?  What's the error message? Or does it kill the connection?  
Crash the server?  Freeze the O/S?  Set the machine on fire?

Cheers,

Ann

Re: [firebird-support] Is it safe to have multiple instances of Gbak with garbage collection?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Aug 19, 2015, at 5:49 PM, tvd...@ymail.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 I believe it is generally safe to have multiple simultaneously running 
 instances of Gbak backing up the same server, when all of them read data from 
 within their own transaction.

Right.  Gbak is just another application.  It generates its own BLR, which is 
unusual, but actually easier than generating SQL, if you happen to be a 
program.  If it were a modern program, it would generate dynamic SQL.  It's 
also pretty smart about dependencies between system table elements. However, 
there is not magic.  A Gbak backup is just a read-only application, like any 
other.

 Is that any different when Gbak is not supplied with the argument to skip 
 garbage collection?

All applications that read the database do garbage collection by default.  
Coopertive garbage collection and the garbage collect thread all work together 
all the time.

 Does that put either the backup file or (worse) the database itself at risk 
 of corruption? Perhaps someone can explain how this would work out under the 
 hood.

No magic, except the normal magic of MVCC. 

My question to you is why run multiple simultaneous backups?  

Good luck,

Ann
 
 

Re: [firebird-support] Knowing the previous rows

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Aug 7, 2015, at 1:56 PM, 'Walter R. Ojeda Valiente' 
 sistemas2000profesio...@gmail.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 Well, after run GSTAT and reading the output I can see how many garbage a 
 whole table has, but it don't shows me the story of a row. ¿How many 
 COMMITs and how many ROLLBACKs the row with ID 1234 of the table CLIENTS had 
 had?

Firebird doesn't track that information.  I suggested that you start with gstat 
to figure out which tables are worth instrumenting, then instrument them.

You can track committed inserts (one per record, obviously) and updates and 
deletes (again, one per record) with triggers.  Updates that roll back will be 
hard to track because Firebird cleans up rolled back changes immediately if it 
possibly can - and the triggered changes to internal are included in the 
clean-up.  If you write your trigger to an external table the changes won't be 
rolled back, but you won't be able to tell the difference between a successful 
update and a failed update.  

I don't have easy access to the Firebird release notes, but suspect that 
sometime someone added a system variable that will give you access to 
transaction ids.  With that, and a transaction triggger that writes the 
terminal state of a transaction to an external table, you should be able to get 
the information you want.

But why?  I understand caring about long strings of back versions.  But why do 
you care about failed updates?  Firebird removes them immediately unless the 
server (or inet-server for Classic) has crashed.  Server crashes should be rare.

Good luck,


Ann

Re: [firebird-support] Composite Primary Key in JOIN - performance

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Aug 11, 2015, at 4:59 AM, steve.decle...@yahoo.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 ...when I take a look at the small lookup tables used to store payment 
 conditions, currency codes, titles, etc, the primary key is always 
 VARCHAR(4) to either store a generator number of a user defined CODE. But 
 most of the time the value is just coming from a generator and with '0' as 
 prefixes, like '0001', '0002'. It doesn't make much sense to me as the code 
 is not shown anywhere. I guess the design would be better if a SMALLINT was 
 used, am I right ? 2 Bytes instead of 8, correct ?
 
In general, fixed size small textual fields should be CHAR rather than VARCHAR. 
 Since the value stored is always the same length, the two bytes that describe 
the actual vs. declared length are wasted.  And yes, an integer type would be 
better if the content will always be numeric.  I have a personal problem with 
small int based on unpleasant experiences when they overflow, wrap around, and 
are generally a nuisance.  But as long as you're certain you'll never have more 
than 32,767 of them... 
 The PROJECT table has a primary key VARCHAR(7) and more worse the SUBPROJECT 
 table has composite primary key made up of the PROJECTNUMBER VARCHAR(7) AND A 
 UNIQUE SUBPROJECTNUMBER for each project, VARCHAR(4).
 
 So JOINs look like this :
 
 JOIN PROJECT PR ON PR.PR_NR = AColumn
 
 JOIN SUBPROJECT SU ON SU.SU_NR = PR.PR_NR AND SU.SU_SUB = AColumn
 
 The quotation table has only 30.000 records and is not performing too good. 
 That SUBPROJECT JOIN has to JOIN on 2 fields because of the composite primary 
 key. VARCHAR(7) + (4) = 11, sounds like 22 bytes to me, instead of 4 bytes of 
 an Integer.
 

Firebird's index key generator is sensitive to major datatypes and tries to 
minimize the key size by eliminating trailing blanks in strings - which won't 
exist in your case because the actual data is zero filled on the left, not 
blank filled on the right.  Numeric keys are represented as a mangled double 
precision float.  The mangling causes the number to compare correctly bytewise 
(moving and inverting the sign) and eliminates trailing zeros after the decimal 
point.  

Compound keys are handled so they also compare bytewise and should be 
reasonably quick.


So, yes, I do think you'd be better off with integer keys (skip the small int) 
but no, I don't think that's the problem with your queries.  Things that would 
help figure out why looking up stuff in a 30K record table is slow might 
include:  query plan, indexes on non-primary keys, query stats specifically 
reads vs. fetches, and probably other things I'm not thinking of now.

Generally, you're right that lying to Firebird by calling a number a variable 
length character string will lead to unhappiness...  just not this particular 
unhappiness.

Good luck,

Ann

Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Aug 11, 2015, at 12:22 PM, 'Louis van Alphen' lo...@nucleo.co.za 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 Sure I understand that, but the reads/fetches are 3000 to return 1 row.

Which involves a four way join, on top of having to find and use the system 
tables to identify the user tables, columns, and indexes, then find the 
appropriate index pages, pointer pages, data pages, etc. for it's internal 
queries and your query.  The second time, most of the heavily used stuff is in 
cache.

Good luck,

Ann
 
 
 
 From: firebird-support@yahoogroups.com 
 [mailto:firebird-support@yahoogroups.com] 
 Sent: 11 August 2015 06:15 PM
 To: firebird-support@yahoogroups.com
 Subject: Re: [firebird-support] Reads inconsistency between FB TraceManager 
 and IB Planalyser
 
 
 
 
 
 Hi Lois,
 
 Reads value means actual reads from the disk.
 If you run query twice, necessary database pages are cached, and you will see 
 in stats more fetches  - i.e., calls to cache, not record fetches.
 
 Actually, always is better to run query twice, to get heated cache.
 
 Regards,
 Alexey Kovyazin
 IBSurgeon
 
 
 
 
 
 
 
 
 
 I am trying to find a performance issue in a system and I am using 2 tools 
 just for comparison and verification. One being FB TraceManager (FBTM) and 
 the other is an old util called
 
 Interbase Planalyzer (IP)
 
 
 
 It seems that the particular query from the view TRACKED_ITEM_LOCATION_DETAIL 
 is slow.
 
 From FBTM,while monitoring the app, I get the following raw output:
 
 
 
 --
 
 2015-08-11T16:35:52.4990 (6180:00F9A948) EXECUTE_STATEMENT_FINISH
 
D:\PROJECTS\KKI\TECH\DATABASE\PRODUCTION 
 DATABASES\2015-07-14\DIGITAN.KKI.FDB (ATT_34, SYSDBA:NONE, NONE, 
 TCPv4:127.0.0.1)
 
(TRA_20898, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE)
 
 Statement 3407:
 
 ---
 
 select * from TRACKED_ITEM_LOCATION_DETAIL where ITEM_ID = ? order by ID
 
 ^^^
 
 PLAN JOIN (JOIN (JOIN (TRACKED_ITEM_LOCATION_DETAIL TILD ORDER TILD_PK_IDX 
 INDEX (TILD_ITEM_IDX), TRACKED_ITEM_LOCATION_DETAIL TL INDEX (TL_PK_IDX)), 
 TRACKED_ITEM_LOCATION_DETAIL FTL INDEX (TL_PK_IDX)), 
 TRACKED_ITEM_LOCATION_DETAIL USR INDEX (USR_PK_IDX))
 
 param0 = bigint, 2147191655
 
 1 records fetched
 
112 ms, 3316 read(s), 3325 fetch(es)
 
 Table Natural IndexUpdateInsert
 Delete   Backout Purge   Expunge
 
 ***
 
 USER_ 1
 
 TRACKING_LOCATION_1
 
 TRACKED_ITEM_LOCATION_DETAIL_ 1
 
 --
 
 
 
 3325 fetches and  112ms seem way over the top. The cache hit ratio is 0%.
 
 
 
 However, when I use IP and manually enter the same SQL, I get the following:
 
 Prepare time 1ms
 
 Execution time 148ms
 
 Fetch time 4ms
 
 With a total of 7 fetches. Not sure where FBTM gets 3K fetches?
 
 
 
 Also, if I remove the order by, IP reports a drastic reduction is execution 
 time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference?
 
 
 
 I am not understanding what is going on.
 
 
 
 Either way, here are the table  view info  stats
 
 
 
 Thanks
 
 Louis van Alphen 
 
 
 
 --
 
 CREATE TABLE TRACKED_ITEM_LOCATION_DETAIL_ (
 
UID   DOM_UID /* DOM_UID = VARCHAR(36) */,
 
IDDOM_ID /* DOM_ID = BIGINT NOT NULL */,
 
IS_DELETEDDOM_BINARY /* DOM_BINARY = SMALLINT DEFAULT 0 NOT NULL 
 CHECK ((  value in ( 0,1) )) */,
 
CREATED_DTM   DOM_DTM default current_timestamp /* DOM_DTM = TIMESTAMP 
 */,
 
CREATED_USER_ID   DOM_FK /* DOM_FK = BIGINT */,
 
ROW_ORIGINDOM_FK /* DOM_FK = BIGINT */,
 
ITEM_ID   DOM_FK NOT NULL /* DOM_FK = BIGINT */,
 
LOCATION_ID   DOM_FK NOT NULL /* DOM_FK = BIGINT */,
 
FROM_LOCATION_ID  DOM_FK /* DOM_FK = BIGINT */,
 
START_DTM DOM_DTM NOT NULL /* DOM_DTM = TIMESTAMP */,
 
END_DTM   DOM_DTM /* DOM_DTM = TIMESTAMP */,
 
START_DAT DOM_DAT NOT NULL /* DOM_DAT = DATE */,
 
END_DAT   DOM_DAT /* DOM_DAT = DATE */
 
 );
 
 
 
 ALTER TABLE 

Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Aug 11, 2015, at 1:30 PM, 'Louis van Alphen' lo...@nucleo.co.za 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 Thanks Ann, so maybe the 2 tools' metric are different. So maybe FBTM
 includes all (low level) reads and IP only user table reads.

Or maybe one runs some queries before the one it measures so all the system 
table queries are compiled, optimized, and executed, filling the cache with 
good stuff.
 
 Also, if I remove the order by, IP reports a drastic reduction is
 execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a
 difference?
 

Unh, maybe.   Between the list, my mail system and yours, I've lost most of the 
formatting and can't look at the original while typing (grrr)   You seem to 
use left outer joins and that can be a problem because (in general) the order 
in which outer joins are declared in the query is the order in which they have 
to be accessed in the query plan.   

For example, a three way join of students, registrations, and courses with 
inner joins can be run starting with any of the three tables and get the same 
results.  

Select s.student_name, c.course_name 
from students s 
 join registrations r on r.student_id = s.student_id
 join courses c on c.course_id = r.course_id

The optimizer can choose to start with registrations and lookup students and 
courses by primary key, or students and look up a registration by student_id 
and a course by the course_id in registrations, or courses - registrations - 
students.  Or sort the students and registrations and merge them, looking up 
courses from the matching pairs.  Or other things, probably.

However, this query can be executed in only one way: students - registrations 
- courses

Select s.student_name, c.course_name 
from students s 
left outer join registrations r on r.student_id = s.student_id
 left outer join courses c on c.course_id = r.course_id

If that way isn't optimal, too bad.  The query must be executed that way or 
you'll miss all the students who aren't registered for courses and all the 
registrations that don't correspond to any courses.

It may be that adding the sort, you've convinced Firebird that it has to read 
and sort all the records in that table, then start from the sorted result.  Try 
using one of the various syntaxes that tells it to return only the first record 
found that matches the criteria.  That may cause Firebird to walk the index ... 
read records in index order ...  rather than reading the table and sorting it.

Now that probably sounds really dumb, but in fact, walking a table in index 
order is generally a lot slower than reading it in physical order and sorting 
the result.  Yes, sort is n(log n), but there's a big K applied that is the 
cost of a random record read.  So, if you expect a lot of results, sort first 
and ask for matches later   If not, use 'Select FIRST n' or 'LIMIT n' 
or whatever the standards committee chose to bless ... maybe  'FETCH FIRST 
n'.  Any of those hints will convince Firebird to walk the table in index 
order.

This query is likely to read and sort the students first, then start looking 
for the rest of the data unless there's a good index on student_name.  

Select s.student_name, c.course_name 
from students s 
left outer join registrations r on r.student_id = s.student_id
 left outer join courses c on c.course_id = r.course_id
 where s.student_name = 'Dinah MacQuarie'
 order by s.student_id

This may give a faster result in the absence of an student_name index.  It may 
not, but do consider trying it when playing with query forms...

Select first 30 s.student_name, c.course_name 
from students s 
left outer join registrations r on r.student_id = s.student_id
 left outer join courses c on c.course_id = r.course_id
 where s.student_name = 'Dinah MacQuarie'
 order by s.student_id

It's worth noting that Firebird does not store records in primay key order 
unless the records happen to arrive in primary key order.  Even when records 
are created in primary key order - as with a generated key - deleted records 
may be replaced with records created much later.   Lots of databases cluster 
records on primary key, making it unnecessary to sort when reading the primary 
key.   That has other costs, particularly with natural keys...  This post is 
already way to long to take on that issue.


Now, just to complicate things, it's possible to turn left outer joins to inner 
joins - at least if you're a person.   Maybe the Firebird optimizer knows about 
this, and maybe it doesn't.  Certainly in the previous century one database 
developer - initials JAS - thought that if you were dumb enough to use outer 
joins when you shouldn't, the optimizer shouldn't reward your stupidity.   He's 
become more benevolent in his old age.  Anyway, here's the case:

Select s.student_name, c.course_name 
from students s 
 join 

Re: [firebird-support] Knowing the previous rows

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 
 
 
 The idea is to know how many garbage a table has. Or several tables. Or the 
 whole database.
 

That you can find out with gstat.  It won't tell you how bad each record is, 
but it will tell you the total number of back version, average, min, and max 
chain lenght.

Then you can set up triggers (if you want) on troublesome tables.

Good luck,

Ann
 
 VISIT YOUR GROUP New Members 6
 • Privacy • Unsubscribe • Terms of Use 
 

Re: [firebird-support] Plan evaluation in stored procedures

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Aug 10, 2015, at 8:15 AM, Aldo Caruso aldo.car...@argencasas.com 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 
 does it mean that if the database is shut down periodically the stored 
 procedure plans would be recalculated ? Shutting the database periodically 
 solves the problem ?
 

Not Sean, but yes.  Shutting down the database will cause stored procedures to 
be reoptimized. 


Good luck,

Ann
 
 On 9 de agosto de 2015 20:14:46 GMT-03:00, 'Leyne, Sean' 
 s...@broadviewsoftware.com [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 Aldo,
 
  My question is: does the optimizer evaluates the plan for the query each
  time the stored procedure is excecuted or is it evaluated only once, when
  the stored procedure is compiled to BLR.
  In the latter case it would be a dissadvantage because as time passes,
  table cardinality and indeces distribution vary, and if the plan was 
  frozen at
  the stored procedure compilation time, may be that it would't be optimal 
  any
 
 The plan for SP is determined when the SP is invoked for the first time from 
 disk.
 
 So your worse is not as bad you think, but if you are running superserver 
 without the database havng no active connections, the SP will remain in 
 memory and the plan will not reflect the latets index stats.
 
 Sean
 
 
 

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

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On 24-7-2015 21:37, Aldo Caruso 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 
 [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
[firebird-support] 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

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Sun, Jul 26, 2015 at 3:19 PM, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] 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: [firebird-support] Re: Firebird Indexing problem

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Fri, Oct 23, 2015 at 2:40 AM, drci...@yahoo.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> I don't think I understand you fully. Indexed reads a hudreds of times
> faster than non indexed.
>

Yes, when you're looking for a specific value.  When you're looking for
something that is NOT a specific value, then it's often faster to read the
table in its storage order rather than reading through an index.  In fact,
if I remember correctly, the Firebird optimizer will never choose indexed
access when the lookup is based on non-equality.


> And the table will have millions of records, so I do need them.
>

Reading millions of records through an index is unlikely to be faster than
reading the same millions in storage order.
Normally, when Firebird reads records through an index, it constructs a bit
map of the record numbers of records that match the indexed condition.  The
record numbers include the page on which the record is located.   If the
bitmap include 90% of the pages in the table, Firebird will then read the
table in storage order (which is also record number order) skipping 10% of
the pages, and the work that went into building the bitmap is completely
wasted.

"then this is cheaper to scan table (500 records) then use 400 indexed
> reads"
> What do you mean scan table? How do I do that? Like a stored procedure
> with for select? I don't understand, please explain.
>

You don't have to do anything.  The Firebird optimizer analyzes your query,
attempting to find indexed paths to data.  If it doesn't find usable
indexed access, it resorts to a table scan.


Good luck,

Ann

Re: [firebird-support] SQL Optimation best way

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Nov 4, 2015 at 8:41 AM, 'checkmail' check_m...@satron.de
[firebird-support]  wrote:

>
>
>  two tables
>
>
>
> A:
>
> Field1, Field2, Field3, Field4, Field5
>
>
>
> B
>
> FieldA,  FieldB, FieldC
>
>
>
> Now I would like to get the following:
>
>
>
> Sum(A.field3) where a.field1 = condition1 and a.field2 = condition2
>
> In the same statement I would include
>
> Sum(b.fieldc) where b.fielda = a.field4 and b.fieldb = a.field5
>
>
>

Without having tried it, I suggest:

select sum (A.field3) from A where A.field1 = condition1 and A.field2 =
condition2
UNION
select sum (b.fieldc) from B inner join A
 where  A.field1 = condition1
and A.field2 = condition2
and B.fielda = A.field4
and B.fieldb = A.field5

If A.field3 and B.fieldc are of different types you may need to cast them.

Good luck,

Ann



>

Re: [firebird-support] Optimize query for date ranges

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

> On Oct 11, 2015, at 5:00 AM, 'Parzival' parzival1...@gmx.at 
> [firebird-support]  wrote:
> i am running a simple query where the result contains 3 records. The query 
> should provide all records in a specific week.
> 
> 
>  
> 
> SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND WOCHE = 
> '2015-41';
> 
>  
> 
> Some times I dont have a week but two dates:
> 
>  
> 
> In this case – the very same number of records = 3 is the result set I see 
> that the table Arbeitseinteilung gots 42 reads.
> 
>  
> 
> SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND a.DATUM 
> >= '05.10.2015' AND a.DATUM <= '11.10.2015';
> 
> How can I improve the query?
> 

Do you have an index on DATUM?  What ate the plans for the two queries?

Good luck,

Ann

Re: AW: [firebird-support] Optimize query for date ranges

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Oct 13, 2015 at 3:47 PM, 'Parzival' parzival1...@gmx.at
[firebird-support]  wrote:

> Hello Thomas,
>
> the result set contains 3 record for criteria.
>
> Records DATUM >= '5.10.2015' = 102
> Records DATUM <= '11.10.2015' = 26
>
> It seems that both queries need to be executed and then the subset of
> matching data for both date conditions is provided.
>

Here's the query:

SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
a.DATUM >= '05.10.2015' AND a.DATUM <= '11.10.2015';

I don't think that's the way it works.  The query plan shows a one key
 lookup, because that's the main stream of the query execution.  However,
Firebird can use multiple indexes on a single query, which it will do this
time.  First it builds a bit map of the db_keys of records with baustelle =
12345.   It then uses one of the indexes (ascending one would hope) on
DATUM to get the db_keys of records with DATUM between 05.10.2015 and
11.10.2015 (which should be stored as a date type and not a string).
Firebird then ANDs the two bitmaps and returns the rows that match both
criteria.

You might get better performance on this query with a compound index on
baustelle and datum.  You'll pay for it when you insert, update, or delete
rows from arbeitseinteilung.

You seemed concerned about the number of reads this query requires.  Did
you measure the number of reads with a cold cache (i.e a freshly started
database) or one that had already done something with arbeitseinteilung?
 46 is a low number for a completely cold cache.

Good luck,

Ann

Re: AW: [firebird-support] Changing Columns

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Jul 7, 2015, at 2:25 AM, 'checkmail' check_m...@satron.de 
 [firebird-support] firebird-support@yahoogroups.com wrote:
 
 .. or is there an option to export all data from the database without 
 database itself, then I can change the ddl, create a clean database and 
 import the data again? If I do it manually, I must show in every table if is 
 there a relation in another one.
 
I guess the best solution is either to extract the metadata (isql -x) or use 
gbak restore only the metadata, make the changes to a new, empty database, then 
use one of the load utilities that can be found at ibphoenix.com under 
download/tools/migration.

Best regards,


Ann
 

Re: [firebird-support] Gbaked and restored database larger than the original?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

 On Jul 8, 2015, at 8:14 AM, Svein Erling Tysvær 
 svein.erling.tysv...@kreftregisteret.no [firebird-support] 
 firebird-support@yahoogroups.com wrote:
 
 Take a look here: http://www.firebirdfaq.org/faq361/
 
 I'd add that restore also has a -use_all_space option. If you don't specify 
 this, then pages are filled to approximately 80% (80% is preferable over 100% 
 unless it is a read-only database). See 
 http://www.firebirdsql.org/manual/gbak-cmdline.html or 
 http://www.firebirdsql.org/manual/gfix-pagespace.html for more information.
 


Just being pedantic here.   The space reserved depends on the size of the 
record - the full declared size plus the record header (approx 14 bytes).  
Firebird computes the number of records that could fit on the page and reserves 
16 bytes per record.  So if your records are big, you'll reserve less space.  A 
record that consists of a single integer could reserve nearly half the page.

Best regards,

Ann

Re: [firebird-support] Firebird backup on virtual machine

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Aug 28, 2015, at 2:40 AM, Macma mac...@wp.pl [firebird-support] 
firebird-support@yahoogroups.com wrote:
 
 My biggest concern was that after restore form Veeam backup firebird
 database file could be damage. If not there is nothing I can worry
 about. I'm just want to be sure that when someone restore full vmdk file
 my system will work and survive such operation.

Unless Veeam works some magic that stops writes to the database while it makes 
its copy, there is a risk that the copy will not reflect the state of the 
database at any one instant.  Firebird's careful write guarantees that at any 
instant the database is correct, so it will survive a server, file system, or 
O/S crash.  

You can use gbak and ask Veeam to backup only the backup file.  You can stop 
the database before the backup starts.  You can ask for a call that blocks 
updates while the backup is being done.  You could probably pay for a feature 
to redirect writes to a sequential file to be applied to the database after the 
backup.  I think using Nbak won't work because (IIRC) it stores its page images 
in a random access file, so the page image file will have the same problem as 
the database when copied block by block.

Good luck,

Ann

Re: [firebird-support] About firebird performance

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Sat, Sep 5, 2015 at 12:00 PM, Hector Sánchez hec...@planatec.es
[firebird-support]  wrote:

>
>
> I discovered something else quite interesting:
>
> I realized both IB and FB dbs have pagebuffers set to 100.000. I changed
> FB to 0 and now it works like I expected!!..Same query now completes on FB
> on 50 sec. It seems this is the problem although I don't understand why.
>
> Nevertheless, I'll try on a physical machine as you and Carlos suggest and
> see how it behaves.
>
>
Very odd.   Could you collect the performance stats for the query on the
two systems? Specifically, I'd like to know how much physical I/O each did
- that's reported as Reads and Writes.  Fetches and Marks don't matter
here.

Cheers,

Ann

On another system I worked on we realized as the cache got very large that
a totally fair LRU was taking up a significant fraction of the CPU load.
That might be what you're seeing.

Re: [firebird-support] Error on Gfix

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Sep 29, 2015 at 4:50 PM, Jeferson Sisto jsis...@gmail.com
[firebird-support]  wrote:

>
>
> Ok, I know this full procedure about corrupt database.
>
> But, when the unique problem about corrupt database is ORPHAN PAGE. In
> this especific situation, is necessary to execute all the steps, including
> the backup/restore?
>
> Looking my situation, I'm having ONLY orphan page in a database that have
> 20~30 GB. In this database, the backup/restore procedure delay
> significative time...
>

Orphan pages are just lost space.  If you've only got three or four (at
16KB per page) in a 20 GB database by all means ignore it.   Orphan record
versions are the same, but even less significant.

Both those errors can occur when the database shuts down without writing
out its cache completely.  They're totally benign.


For those who care deeply, here's the situation.  Firebird uses careful
write to maintain on-disk consistency.  That translates to writing the
thing pointed to before the pointer when creating objects and removing the
pointer before the object when releasing objects.

Consider the case of allocating a new page to a table.

1) the page number is released from the free page list, so it can't be
allocated twice and the free page list is written.
2) the page is formatted to look like a data page for that table and
written to the database.
3) the page number is entered into the list of page that belong to the
table and that list is written to the database.

If the database stops between 1 and 2 or 2 and 3, that page is neither free
nor associated with a table, index, or internal structure.  But it doesn't
cause any problems either.  It just sits there, wasting a small amount of
space until gfix removes it or the database is restored from a backup.

Similarly when Firebird garbage collects an old record version, it first
clears the pointer to the version in the next newer version, then clears
the entry on the data page that locates the actual record.  Last, it
removes index entries that are no longer valid.  If there's a failure
between clearing the record pointer and the entry on the data page, that
record version sits there until gfix removes it or the database is restored.

Good luck,

Ann

Re: [firebird-support] AW: Performance comparision for primary keys - int vs. varchar

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Fri, Oct 2, 2015 at 5:09 PM, Köditz, Martin martin.koed...@it-syn.de
[firebird-support]  wrote:

>
>
> I’m using integer IDs for primary keys in my tables. What if I would use
> varchar fields instead? Will I lose performance in that case, especially
> for big tables? Will joins still work as fast as they do for the integer
> column.
>

Your performance should be about the same with varchars or integers.
Firebird always compares index keys bytewise and only the significant part
of the value is stored.

A single field key is first converted to one of the three canonical types:
 string with collation, double precision, and (sadly) 64 bit integer.
Dates become double precision floating point numbers.

Strings that have a collation other than their byte value are converted to
their collation format.  That's something of a black art and expands the
size of the string, but the result is that the string finds its correct
place when sorted with other strings of the same collation.  'A', 'a', 'â',
'á', 'Ă', 'ã', 'ä', 'å', 'ă', 'ą', 'Ā' all appear in their appointed
places.  (Sorry for what that did to your email client   in mine,
that's eleven variants on 'A'.)  Trailing blanks are not included in the
key.

The double precision number is mangled so it too sorts bytewise - roughly
invert the sign, then exponent, then mantissa, truncating trailing zeros.

Depending on the endianness of 64bit integers on the computer, they too are
mangled so they compare bytewise.  That may seem like a disoptimization,
but index keys are not stored on natural boundaries and they undergo prefix
compression so there's no way to use a larger comparison than byte by byte.


Compound keys are much the same.  Each part is converted to its index key
type and padded to a multiple of 4 bytes.  After every four bytes, Firebird
adds a byte with the position of the current field of the key.  Thus an
index on LastName, FirstName, ZodiacSign would come out as 1Harr1ison2Ann
3Gemi3ni.  This avoids the embarassment of confusing Damnation with Dam
nation.

Why did I say "(sadly)" above?  Because having a single format for numbers
allows Firebird to change the size of numbers without recreating indexes on
them.  But when Borland added 64 bit integers back - InterBase had 64 bit
integers from the beginning on Vaxes - some bright spark realized that
double precision has 56 bits of precision and 64 bit integers have 64
bits.  On the other hand, Firebird indexes are designed to handle some
imprecision ... or the remaining 8 bits could be tacked on the end...
whatever.  So you have to rebuild indexes when going from Numeric/Decimal 9
to Numeric/Decimal 12.  Sad.

"Prefix compression?"  When storing a key other than the first on a page or
the first after a jump on page Firebird looks at the preceding key and
truncates that part of the beginning of the next key that duplicates is
predecessor and tacks the length of the truncated part at the beginning.
Thus the strings "", "AAAB", "AAAC", "AABC" become
"", "3B", "3C", and "2BC".There is a problem with some formats of
GUID which put the volatile part of the number first, followed by the fixed
part.  That defeats prefix compression and inflates the size of indexes.

"Jump?" - Prefix compression reduces the size of indexes by a lot, reducing
I/O, but requires reading across the whole page to decipher the key.  Fine
with 1K pages, but with larger page sizes the computation was
unacceptable.  So each index page now has an index of its own pointing to
the offsets of uncompressed entries. That index is called a jump vector.


More than you wanted to know.  Somewhere on the IBPhoenix web site there
should be a couple of papers called Firebird for Experts (or something like
that) - one of they goes into gory detail on the layout of index pages.

Good luck,

Ann


>
>

Re: [firebird-support] Re: SELECT for get the number of contexts

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Sun, Sep 27, 2015 at 1:37 PM, 'Walter R. Ojeda Valiente'
sistemas2000profesio...@gmail.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> The only way to know how many contexts a stored procedure has is looking
> at the source code and counting handly them?
>
>
The limit on contexts is per statement, not for an entire stored procedure,
trigger, or whatever.  A statement is a SELECT, INSERT, DELETE, or UPDATE.

Each table reference is a context.  So, for example, a reflexive join has
more than one context for the same table.  An update with lots of
subselects has contexts for each part of the subselect.   A statement that
references a view has contexts for every table reference in the view.

So, don't worry about the total number of contexts in a stored procedure,
but do worry about the use of complex views in apparently simple
statements.

Good luck,

Ann




>
>>
>>
>
>
> 
> --
>

Re: [firebird-support] Very Strange: Query giving different results

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Mon, Oct 5, 2015 at 12:59 PM, 'Alexis (PROINTER)' ale...@prointersl.com
[firebird-support]  wrote:

> Hi, we have found a very strange problem with our firebird database, a
> record on a table of firebird databes is not always beeing returned:
>
> 1 - Using flamerobin we are never fetching record 1648 of our datble.
>
> 2 - Using an application we are developing (with vb.net on vs2010) the
> record is fetched:
>
> 3 - If we query from the application with condition IDESTADO = 3 wich the
> desired record has that value that record is also not fetched:
>
>
Yahoo ate your table structure, so this is just a guess.  You may have an
index that's missing an entry - that shouldn't happen, of course.  Using
isql, try fetching the record by primary key value, primary key
concatenated with an empty string (if it's a string) or plus zero (if it's
a number).  They try the same thing on any secondary indexes.

Good luck,

Ann

Re: [firebird-support] Securing database against corruption on systems that suddenly get turned off

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Mon, Sep 21, 2015 at 4:53 AM, Glenn Thomas Hvidsten g...@vensafe.no
[firebird-support]  wrote:

>
>
>
> We have FirebirdSQL (2.1) running on a client computer (Windows). This
> client has some problems with his power grid which causes the PC to
> suddenly and unexpectedly shut down in infrequent intervals (usually once
> or twice per day). Most of the time this doesn’t cause any problems, but
> once in a while this causes the database to be corrupted.
>
> I’m not at all surprised that the database gets corrupted like this (as
> turning off a computer without shutting everything down gracefully can have
> that effect).
>
>
If you have turned off forced writes, corruption is likely.  If forced
write is on, what are the errors you're seeing when the database is
corrupted?  I'm not suggesting that you disregard advice about adding a UPS
and make backups - excellent suggestions - but the Firebird architecture
should survive a power failure if writes are being forced to disk in the
correct order.  So I'm curious...

Good luck,

Ann

Re: [firebird-support] Deleting records and deadlocks

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Dec 2, 2015 at 11:49 AM, Tim Ward t...@telensa.com
[firebird-support]  wrote:

>
>
> Yes I do know it's not a real deadlock, I was using the word because I
> knew it would be understood and because, I'm pretty sure?, I've seen it
> in one of the relevant error messages.
>

This is really ancient history, but the reason that update conflicts are
reported as deadlocks is rooted in the early dark ages of computing, say
around 1981.  DEC, to its distress, found itself developing two relational
databases, one a relational layer on a CODASYL system, and one relational
from the ground up using multi-generational concurrency control (created by
Jim Starkey).

Management decreed that the two systems be compatible shared libraries.
Change a link and your application goes from using one to using the other
without changes.  That meant compatible everything but ODS - system tables,
api, and error codes.  The solution for error codes was to agree on major
error codes that an application could receive and respond to, with the
possibility of minor codes to for system specific behavior.  Since the
solution to an update conflict (not a possibility in the lock-based
traditional implementation) is the same as the resolution of a deadlock -
rollback and try again - it was lumped under deadlock.

When Jim created InterBase, he used the same interfaces in hopes of gaining
market share from VAX/RDBMS users who wanted a compatible interface on
other hardware.  Alas, by that time anyone still using VAXen was so
addicted to DEC that they wouldn't consider other hardware let alone
foreign software.

Cheers,

Ann

Re: [firebird-support] Is it save to append some data at end of the binary firebird database file?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Dec 1, 2015 at 7:42 AM, 'Moessinger, Semjon' s.moessin...@pi.ws
[firebird-support]  wrote:
>
>
>
> My main intend is to add some version information to a firebird database
> file, since I will use firebird as embedded database and installers/update
> mechanism will need this information.
>

Not a great idea.  Unusual, imaginative, but probably catastrophic.  When
Firebird needs to add more data, it will append some number of blocks to
the end of the file.  I don't remember the algorithm exactly, but the space
will be some number of disk blocks that work out to full pages.  On a good
day, your information will be overwritten.  On a bad day, the allocation
will fail or become misaligned and make the database unusable.

>
>
> The information should be available platform independent and I would
> prefer a solution not needing any database access.
>

Maybe distribute a separate file?

Good luck,

Ann

>
>

Re: [firebird-support] Is it save to append some data at end of the binary firebird database file?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

Fabiano Kureck suggested sticking application version information in the
checksum slot of the page header.

Mark Rotteveel quite correctly responded.
>
>
> That is very dangerous to do, the same bytes might be reused differently
> in a newer ODS. Either use a normal Firebird table to store version info,
> or do it outside of the database. Do not hack things in the internal
> structure.
>

The page header is not a good place to stick information. It does change to
support new features.  However, the database header page has a place that
you might be able to use.  The format of the header page is defined in
ods.h.   The first part is fixed.  The second part is a string of
"clumplets" which are groups of attribute-length-data triplets.  By design,
Firebird skips over clumplets it doesn't understand.  You could write a
program that adds a new clumplet of a type undefined by Firebird to hold
your version information.  It wouldn't survive a gbak backup/restore cycle.

The discussion was about finding portable mechanism to identify the
application version of a database.  Are you aware that databases are not
portable across machines with different endian characteristics?

Good luck,

Ann

>
>

> 
> --
>

Re: [firebird-support] Deleting records and deadlocks

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

> On Dec 2, 2015, at 6:35 AM, Tim Ward t...@telensa.com [firebird-support] 
>  wrote:
> 
> What about if two concurrent transactions are both trying to *delete* 
> the *same* record at once? - from the point of the view of the user's 
> objectives there's no reason why this shouldn't work, as either way the 
> record is going to end up deleted, which is what the user wants, but 
> would I be right in guessing that Firebird isn't that clever, and that 
> deadlocks are possible in this scenario?

It's not nearly that simple.  What would happen if one of those transactions 
rolled back? 

Good luck,

Ann

Re: [firebird-support] Bad performance of Firebird in Windows Server 2012

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Dec 29, 2015 at 2:44 PM, Macma mac...@wp.pl [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> Do I have to change any configuration or the matter is that if I can´t
> enable disk cache, there is nothing I can do to improve the performance?
>
> Try to disable force write on that database.
>

After you have a UPS installed and an aggressive backup schedule.

Good luck,

Ann

Re: [firebird-support] UPDATE to same record causing heavy disk I/O

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Dec 22, 2015 at 2:17 PM, Caroline Beltran
caroline.d.belt...@gmail.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> Since I began using Firebird, I have kept my transactions (type
> concurrency) very short and then call COMMIT immediately afterward.  This
> has worked very well.
>
> I recently had the need to perform more complex processing and what I did
> was to keep everything short and modular.  But I am now seeing that my
> design in this specific case was flawed.
>
> I am updating different parts of the same record repeatedly and I believe
> that this is causing multiple back versions which causing excessive disk
> write I/O and slowing things down terribly:
>
> a) begin a transaction, update FIELD_1 of MYTABLE, COMMIT, and end
> transaction.
>

Just curious, why do you say both "COMMIT" and "end transaction" - Commit
ends the
transaction.

Good luck,

Ann

> b) begin a transaction, update FIELD_2 of MYTABLE, COMMIT, and end
> transaction.
> c) begin a transaction, update FIELD_3 of MYTABLE, COMMIT, and end
> transaction.
> d) begin a transaction, update FIELD_4 of MYTABLE, COMMIT, and end
> transaction.
> e) begin a transaction, update FIELD_5 of MYTABLE, COMMIT, and end
> transaction.
> Note: other tables are inserted and updated during during transactions a-e
> but those tables are not presenting any problems.  The problem is with
> MYTABLE.
>
> Of course, I normally update all fields in one transaction but in this
> particular case, determining the contents of each field is a complex
> process that requires manipulation and analysis of the the data provided by
> a number of other Firebird SELECT queries to the database.
>
> I am averaging about 300 transactions per minute during this process that
> may last 12 hours and during that time, things get terribly slow.
>
> So can someone confirm my suspicions, will each of the 5 transactions
> above to the same row of data cause 5 new 'back versions'?
>
> Like I said, I have always kept transactions very short.  I am thinking of
> something like this instead:
>
> a) begin a transaction, update FIELD_1 of MYTABLE.
> b) update FIELD_2 of MYTABLE.
> c) update FIELD_3 of MYTABLE.
> d) update FIELD_4 of MYTABLE.
> e) update FIELD_5 of MYTABLE, COMMIT, and end transaction.
> If something fails anywhere in between, I would ROLLBACK this single
> transaction.
>
> Keeping my transactions shorter and more modular as above is easier from a
> development point of view but I have the back version performance issue.
> Although the second method means a much longer transaction, I won't have
> back versions to deal with.  Do you think that this approach would be
> better?
>
> Thank you
>
> P.S.  Sweeping the database does not help with the performance problem,
> the only temporary solution to regain performance is to backup using GBAK
> and restore.
>
>
> 
>

Re: [firebird-support] Numeration without hole, Is right Before Insert Trigger?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Dec 22, 2015 at 9:40 AM, Luigi Siciliano luigi...@tiscalinet.it
[firebird-support]  wrote:

>
>I must assign a serial number, without hole, in a column of a fiscal
> document.  I must assign the number only when I know if the document is
> complete
> and I think the right moment is on a Before Insert Trigger for the table.
>

Yes that's a good place, but you've got to be very careful.
Generators/Sequences won't
work because they're deliberately non-transactional.  Once you take one its
gone and if
your operation fails, you'll have a hole.

>
> Is right or the insertion can fail? If not right, when I must assign the
> number to be sure of not have a hole in numeration?
>

One way to get numbers without holes is to create a table with one field
that contains
the seed for  your numbers.  In your before insert trigger update that
field adding one to it,
then read to get the new value.  Unfortunately, if someone else has
inserted a record
concurrently, your transaction will wait then get an error and you'll need
to re-run the
whole thing.

 Check the FAQ's at FirebirdSQL.org for other ways of handling this problem.


Good luck,

Ann


>
>

Re: [firebird-support] UPDATE to same record causing heavy disk I/O

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Tue, Dec 22, 2015 at 2:17 PM, Caroline Beltran
caroline.d.belt...@gmail.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> Since I began using Firebird, I have kept my transactions (type
> concurrency) very short and then call COMMIT immediately afterward.  This
> has worked very well.
>

Good, but that too can be overdone.  Each transaction start causes a change
to the header page and end causes changed pages including a transaction
inventory page to be written to disk.  There's some grouping of writes, but
as a rule, think that each transaction you create causes two extra page
writes beyond the data and indexes.

>
> I recently had the need to perform more complex processing and what I did
> was to keep everything short and modular.  I am updating different parts of
> the same record repeatedly and I believe that this is causing multiple back
> versions which causing excessive disk write I/O and slowing things down
> terribly:
>

> a) begin a transaction, update FIELD_1 of MYTABLE, COMMIT, and end
> transaction.
> b) begin a transaction, update FIELD_2 of MYTABLE, COMMIT, and end
> transaction.
> c) begin a transaction, update FIELD_3 of MYTABLE, COMMIT, and end
> transaction.
> d) begin a transaction, update FIELD_4 of MYTABLE, COMMIT, and end
> transaction.
> e) begin a transaction, update FIELD_5 of MYTABLE, COMMIT, and end
> transaction.
>

There are several problems with this.  One is the significant transaction
overhead you introduce. A second, as you've guessed is that you're creating
a back version for each update.   Another is that any transaction reading
your record between updates will see some field that have been changed and
others that haven't.  Another, and not insignificant, is the danger that
some other transaction will change your part or all of a record between
your transactions, leaving the record inconsistent.


> Of course, I normally update all fields in one transaction but in this
> particular case, determining the contents of each field is a complex
> process that requires manipulation and analysis of the the data provided by
> a number of other Firebird SELECT queries to the database.
>
> I am averaging about 300 transactions per minute during this process that
> may last 12 hours and during that time, things get terribly slow.
>

 Probably some information from the monitoring tables will let someone else
give you good advice.

>
> So can someone confirm my suspicions, will each of the 5 transactions
> above to the same row of data cause 5 new 'back versions'?
>

Absolutely.

>
> Like I said, I have always kept transactions very short.  I am thinking of
> something like this instead:
>
> a) begin a transaction, update FIELD_1 of MYTABLE.
> b) update FIELD_2 of MYTABLE.
> c) update FIELD_3 of MYTABLE.
> d) update FIELD_4 of MYTABLE.
> e) update FIELD_5 of MYTABLE, COMMIT, and end transaction.
> If something fails anywhere in between, I would ROLLBACK this single
> transaction.
>

That's not going to work either.  Your first update will create a back
version that's just the difference between the old record state and the new
state.  The second (or maybe third) will create a back version that's the
whole record (IIRC) - much larger and possibly off page. Off page matters
because it doubles the page writes.

>
> Keeping my transactions shorter and more modular as above is easier from a
> development point of view but I have the back version performance issue.
> Although the second method means a much longer transaction, I won't have
> back versions to deal with.  Do you think that this approach would be
> better?
>

No, just do all the computations in a single transaction and update the
record once with all the changes.

>
>
> P.S.  Sweeping the database does not help with the performance problem,
> the only temporary solution to regain performance is to backup using GBAK
> and restore.
>

That's why I suspect there's more to it than just back versions and would
like to see something about I/O, reads, writes, fetches, marks, etc.

Good luck,

Ann

>
>
> 
>

Re: [firebird-support] Re: UPDATE to same record causing heavy disk I/O

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Thu, Dec 24, 2015 at 1:03 PM, Dmitry Yemanov dim...@users.sourceforge.net
[firebird-support]  wrote:

> 24.12.2015 05:31, 'Leyne, Sean' wrote:
> >
> > With today's unlimited availability of disk space and silly-low cost per
> GB for storage, would an argument to dispense with the delta and simply
> store a full copy of the record (not including BLOB) be worthy of
> discussion?
>
> It's not about storage cost, but about IOPS. Bigger record = more I/O
> for the same data = slower performance. Situation is better for SSDs,
> but "silly-low cost" does not really apply there.
>

Right.  The logic was never about saving space on disk, except to the
extent that it reduces the amount of I/O necessary to complete a query.

>
> > I know that Jim has mentioned that in his later db engine he has adopted
> a reverse approach which has the latest version stored in full and for
> transactions required back versions responsible processing the deltas.  In
> this way, the latest version of the row are always complete so that the
> back versions can be dropped very efficiently.
>
> Isn't it exactly how Firebird works?
>

Yes it is.  The primary record version - the most recently created one - is
always complete. The earlier record versions may be whole or deltas.

Jim did handle back versions differently in Netfrastructure and slightly
differently again in NuoDB.  InterBase was designed for systems where
having a whole megbyte of memory, so stuff had to go to disk as quickly as
possible.  When designing for more generous memory systems, he chose to
keep only the most current committed record on disk. That version, and
important back versions, and the newest uncommited version were all
maintained in memory.  If the system went down, any old transactions that
needed old versions went down with it.

NuoDB did approximately the same thing, except that it was distributed, so
old versions had to be maintained a bit more carefully so losing one node
would never lose all old versions.

His latest database, AmorphousDB handles versioning at the attribute level
rather than the record level, but follows the model that only the most
recently committed version of an attribute is worth the cost of a disk
write.

Cheers,

Ann

Re: [firebird-support] UPDATE to same record causing heavy disk I/O

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Dec 23, 2015 at 7:12 AM, Alexey Kovyazin a...@ib-aid.com
[firebird-support]  wrote:

>
> longest chain of versions here).
>
> If you will see a long chain of versions, it means that you are updating
> the same record while some other writeable transaction is active, or some
> old transaction was rollabacked with mark on TIP, so it makes Firebird to
> preserve versions of updated record.
>

Updating a record will always create a back version, even if there are no
other users in the database.  Back versions function in three ways:

  1) They provide a non-blocking consistent view of data for concurrent
transactions.
  2) They allow Firebird to detect and prevent conflicting updates.
  3) They are the mechanism for verb, savepoint, and transaction rollback

The third function must be available even in single user applications.

Good luck,

Ann

>
>
>
> 
>

Re: [firebird-support] Re: UPDATE to same record causing heavy disk I/O

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Dec 23, 2015 at 12:01 PM, Dmitry Yemanov
dim...@users.sourceforge.net [firebird-support] <
firebird-support@yahoogroups.com> wrote:

> 23.12.2015 01:36, Ann Harrison wrote:
>
> >
> > ...Your first update will create a back
> > version that's just the difference between the old record state and the
> > new state.  The second (or maybe third) will create a back version
> > that's the whole record (IIRC) - much larger and possibly off page.
>
> Correct, but from another side third, fourth, etc updates will not
> create any new versions. May be worth checking whether a "heavy" second
> update could be better than creating a long version chain.
>

Right. When we were working on InterBase 1.1 (I think) a friend of Jim's
suggested using deltas for back versions to save space.  He's still a
friend, but that feature was a real trial to implement and debug, partly
because we ran out of bits in the record header.  There's one that says
"the version behind me is a delta", but not one that says "I am a delta."
 The first one is essential because it means that you've got to set up a
copy of the record on which to apply deltas.  The second would have been
very nice for detecting bugs that lead to having the first bit set when it
shouldn't be or not set when it should.

Once the deltas were working reasonably well, we discovered that some users
were updating a single record dozens of times in a transaction.  Not a use
case we'd considered.  I don't remember whether we decided to create a
complete back version after two or three updates in one transaction, though
I vaguely remember arguing that like Martinis, one or two was pretty
reasonable, but after three things are likely to go off in an unexpected
direction.  With small records, a complete back version saves space
compared with three deltas.  With big records and small changes... not so
much.

Cheers,

Ann

>
>
> Dmitry
>
>
>
>
> 
> Posted by: Dmitry Yemanov 
> 
>
> ++
>
> 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] Re: Is Firebird 3 ready for Production?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Thu, May 26, 2016 at 8:30 AM, fabia...@itbizolutions.com.au
[firebird-support]  wrote:

>
> I wrote:
>
>
>   In V3.0, Firebird is multi-threaded at the client statement level.  It
> does not decompose queries and schedule the pieces on different processors.
>
>
>
> I understand, I thought true SMP was referring to assigning each client's
> request to all CPU at once, instead of balancing the clients requests
> across processors. So the current version (FB 3) is probably going to
> outperform the FB 2.54 when there are concurrent requests passed to the
> server engine because each request will go to a different processor,
> correct? The more concurrent requests we have the more we are going to
> notice the difference, right?
>

Right.  The strength of a relational database is its ability to process
multiple client requests simultaneously.  That's the goal of Firebird V3.
If all you want to do is strip data off the disk as fast as possible, there
are better technologies.

Splitting client requests across processors in general is hard.  Well, it's
less hard if you don't care if the result is performance improvement.  In
general, splitting a single requests across processors is beneficial if
there's lots of computing or if the request can be split so different parts
access different disks.  A simple "select count(*) from MyTable" cannot be
improved by splitting it across processors unless the table can also be
split across disks in a deterministic way.   On example that could be
improved by splitting a single request is large sorts, which can be
improved by splitting them so one thread is fetching records while others
sort subsets.  With large memories and 64 bit address spaces, lots of
sorting can happen in memory.

But frankly, if I were the core developers, I'd want to put my efforts into
other areas that will provide more benefits than fast large sorts.

Good luck,

Ann

Re: [firebird-support] Re: Is Firebird 3 ready for Production?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, May 25, 2016 at 2:53 PM, Ann Harrison 
wrote:

> On Wed, May 25, 2016 at 1:11 PM, fabia...@itbizolutions.com.au
> [firebird-support]  wrote:
>
>>
>>  Now on the flip side, the performance sucks, it is worst than with FB
>> 2.54, and when looking at the task manager on windows it appears only one
>> processor it doing the job, as if the code was not SMP enabled very
>> strange.
>>
>
> One possibility is that you're testing V3.0 SuperServer single user.  In
> V3.0, Firebird is multi-threaded at the client statement level.  It does
> not decompose queries and schedule the pieces on different processors.
> That means that a full-table scan runs on only one processor.  Two
> simultaneous full-table scans will run on two processors.
>

I should have continued to say that two full-table scans probably won't be
any faster in 3.0 than they were in 2.5 because you're measuring disk
transfers and adding processors doesn't make the disk go faster.  I might
also have mentioned that sharing caches and other internal structures
between simultaneous threads is one challenge.  Distributing queries across
processors is another and totally different challenge.  The Firebird
developers were wise (in my opinion) to take the challenges one at a time.

Good luck,

Ann

Re: [firebird-support] Re: Is Firebird 3 ready for Production?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, May 25, 2016 at 1:11 PM, fabia...@itbizolutions.com.au
[firebird-support]  wrote:

>
>  Now on the flip side, the performance sucks, it is worst than with FB
> 2.54, and when looking at the task manager on windows it appears only one
> processor it doing the job, as if the code was not SMP enabled very
> strange.
>

One possibility is that you're testing V3.0 SuperServer single user.  In
V3.0, Firebird is multi-threaded at the client statement level.  It does
not decompose queries and schedule the pieces on different processors.
That means that a full-table scan runs on only one processor.  Two
simultaneous full-table scans will run on two processors.

Good luck,

Ann

Re: [firebird-support] Engine12 mistery

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

The way I think of the Y-valve* is that the stem of the Y is the client -
whether the normal fbclient or the Java client or other language specific
clients.  The connection request goes from the client to a provider.
Right now, the providers are Remote (which may be built into the Y-valve)
and Engine12.  Someone could create a provider that translated Firebird
requests to Oracle/PostgreSQL/MySQL or whatever and offer a provider
to a different back end.  If there's enough demand, Firebird 2.5 could be
cut back to be a provider to older versions of Firebird.

So the stem is the client and the branches are the providers.

Cheers,

Ann

* Y-valve is a nautical term.  On a boat, the head (toilet) outflow goes
through
a Y-valve.  One leg takes the effluent to a holding tank, the other dumps it
overboard.  No parallel implied between what goes through the Firebird
Y-valve
and the nautical term.

On Tue, Jun 14, 2016 at 4:46 PM, fabia...@itbizolutions.com.au
[firebird-support]  wrote:

> 
>
>
> Mark
>
> Thank you, I think I am beggining to understand the concept, so the
> provider's have to be combined including ALWAYS Engine12 (the core FB
> server procedure/method that really goes to the file on disk and does the
> reading and writting to the DB). So the following combinations are valid:
> Providers = Remote + Engine12
> Providers = Loopback + Engine12
> Providers = Remote  + Loopback + Engine12 (the first two in any orther)
>
> And any Providers combination excluding Engine12 would not work because it
> is not including the component that goes to the DB file and reads/writes
> from /to it.
>
> It seems Engine12 is not the same "type of component" as the other
> Providers. In the documentation it is refered as a Y valve, if I understand
> it correctly Engine12 is the base of the Y, while the other providers are
> the top left and top right of the Y, you can have any top you want, BUT YOU
> MUST HAVE the base always, else it does not work, correct?
>
>
> Cheers,
> Fabian
>
>
>
> - Original Message -
> *From:* Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
> 
> *To:* firebird-support@yahoogroups.com
> *Sent:* Wednesday, June 15, 2016 5:32 AM
> *Subject:* Re: [firebird-support] Engine12 mistery
>
> On 2016-06-14 19:44, fabia...@itbizolutions.com.au [firebird-support]
> wrote:
> > My understanding from reading the documentation is:
> >
> > Provider = Remote means the client is on the network, excluding
> > 127.0.0.1
> > Provider = Loopback means the client is actually on the same OS
> > instance as the FB3 engine, and it is using the 127.0.0.1 to access
> > the database to avoid the "embedded server concept" from answering
> > the
> > request, as it would capture the DB file and will not allow any other
> > clients from remote /network source.
> > Engine12 = The local server takes control of the database as if it
> > was
> > an embedded server, killing all future chance of accessing the DB
> > from
> > the network, so Engine12 is if I understand correctly the way to talk
> > to the DB engine in "exclusive" mode when you want to perform
> > maintenance or work on the security database.
> >
> > Is the above wrong?
>
> Yes, your understanding is wrong. Engine12 **is** the component that
> actually does the work with the database file. The rest is just plumbing
> to connect to servers (or make fbclient connect to a local server),
> select the right provider, etc.
>
> As the release notes say:
>
> http://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-engine.html
>
> * Remote (establish network connection)
> * Engine12 (main database engine)
> * Loopback (force network connection to the local server for  name> without an explicit network protocol being supplied).
>
> There was also talk of providing an 'Engine11' which would essentially
> be a Firebird 2.5 you could use to connect to ODS 11.2 and older
> database files.
>
> Your confusion likely stems from the fact that Firebird embedded now is
> fbclient + Engine12, as opposed to Firebird server + Engine12 for a
> normal database server, while Firebird embedded used to be a separate
> DLL/SO. BTW: In normal situations fbclient **does not** have access to
> Engine12, so it only behaves as a client library.
>
> Mark
>
>
>
> 
> Posted by: Mark Rotteveel 
> 
>
> ++
>
> 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] Forced writes on Linux?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Fri, Jan 15, 2016 at 5:15 AM, Tim Ward t...@telensa.com
[firebird-support]  wrote:

> I know the folklore around forced writes
>
> - turning forced writes on is safer
>

Not exactly folklore.  Firebird doesn't have a journal but instead depends
on careful writes to keep the database constantly consistent.  After a
crash - system or database - there's no recovery process.  The database is
available instantly.

The basic principle of careful write is writing data before pointers. When
adding data, the page with the data is written before the page that points
to it.  When removing data, the pointer is cleared before the data.  This
principal applies to internal structures like pointers as well as records
and indexes.  If you allow the operating system to write pages in its
preferred order, the database on disk may be inconsistent.  As long as
nothing goes wrong, the state of the database on disk isn't an issue.  At
one time, Windows didn't flush its page cache until a file was closed,
leading to losing hours or days of work.

What's different now is that machines are both faster and more reliable.
If nothing crashes, careful write doesn't matter.



> - turning forced writes on is slower
> - the safety bit is much more of an issue with Windows than with Linux,
> to the extent that it used not to work on Linux and nobody noticed for
> years.
>
> But I haven't found any actual, y'know, like, data, evidence, so on.
> Things like (with reference to Linux, I'm not interested in Windows):
>
> (1) Is there any data about how much slower it is, eg has anyone done
> any benchmark runs on their systems? - I've found just one blog entry
> somewhere with a number ("up to three times slower") but without any
> (published) data behind it.
>

I wouldn't particularly trust benchmarks that are more than three years
old.

>
> (2) Is there any evidence about how much safer it is? Statistics on
> corruptions with and without? Analysis of individual database corruption
> events showing whether turning on forced writes would or would not have
> prevented the corruption event?
>

The evidence I have is too old to be relevant, so I can offer only theory,
not
practice.  In theory, there should be no difference...

Cheers,

Ann

Re: [firebird-support] What is more effective execute block or select?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Jan 27, 2016 at 5:01 PM, setysvar setys...@gmail.com
[firebird-support]  wrote:

>  >If the question is which is likely to perform better, then the answer
> is the select.
> ...
>  >In general, execute block should be used when you want to change what
> Firebird
>  >considers fundamental elements of a quer...
>
> I'd never thought I would ask YOU this question, but are you sure, Ann?
>

Sure?  No.

> I just wonder if you've thought EXECUTE STATEMENT where you've written
> EXECUTE BLOCK.
>

Probably.  Does EXECUTE BLOCK allow you to build up the block at runtime?
If so, then I think I may be right unless the compiler is clever enough to
recognize
that your particular block is static.

>
> UPDATE  h
> SET  = (SELECT  FROM  t WHERE
> h. = t.)
>
> is much slower than
>
> EXECUTE BLOCK AS
> Declare variable a integer;
> Declare variable b integer;
> BEGIN
>FOR SELECT DISTINCT , 
>FROM 
>INTO :a, :b do
>  UPDATE HugeTable
> SET  = :b
> WHERE  = :a;
> END
>

GDS/Galaxy aka InterBase, aka Firebird was built around a relational
language that practitioners at the time would have called "procedural" as
opposed to Quel which was "declarative".  Both lost to SQL which was just
ugly.  The procedural language typically
used nested loops "for  for  do 
end-for end-for", which maps very nicely into the PSQL
FOR SELECT.   The SQL UPDATE statement is one of the ugliest parts of that
ugly language and makes it very difficult to optimize
the case where you're drawing values from a small table to update a large
table.

If EXECUTE BLOCK requires static queries, then I'm completely wrong.  If
not, you might be better writing procedures for updates like this, or as
Mark suggests, MERGE.

Cheers,

Ann

Re: [firebird-support] Page is of wrong type (expected 5, found 0)

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Thu, Jan 28, 2016 at 4:12 PM, Martin Schwedhelm silo...@yahoo.com
[firebird-support]  wrote:

>
>
> As far as I can tell (via gstat) forced write is enabled.
> Also, regarding virtual machines, at least ESX(i) does not cache guest OS
> writes
> (http://www.yellow-bricks.com/2011/04/07/mythbuster
> s-esxesxi-caching-io/).
>

OK.  There are myths about disks that lie about writing stuff, but that's
way on the outer edge
of probability, especially since you've got lots of pages of zeros.

>
> Maybe a thing that was not clear in my initial post:
> We don't use a sql command that deletes that many records or drop tables.
> The missing (zeroed) data should not be zeroed at all.
>

 When Firebird releases a page because it went empty or the table was
dropped or
whatever, it doesn't zero the page.  My somewhat feeble recollection is
that pages of zeros
are newly allocated and never used, or trompled by rogue processes.

Good luck,

Ann

Re: [firebird-support] What is more effective execute block or select?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Jan 27, 2016 at 7:48 AM, hamacker sirhamac...@gmail.com
[firebird-support]  wrote:

>
>
> I would know whats methods is more effective, per exemple:
>

Example below shows two convoluted ways to return 'Y' or 'N' depending on
whether an item with a particular id exists: a select against rdb$database
and an execute block.

If the question is which is likely to perform better, then the answer is
the select.
Select statements can be compiled and optimized once and reused, avoiding
validating access and metadata references.  The execute block must be
compiled,
optimized, and access checked each time it's used.

In general, execute block should be used when you want to change what
Firebird
considers fundamental elements of a query - the fields returned, the tables
accessed,
the sorting elements, and the conditions in the on and where clauses.

If the question is which of these particular queries is more effective, the
answer is
neither.  Mark pointed out that there are much simpler ways to determine if
an item
with a particular value exists, though they simply fail to return a value
when the
answer is no.  In most cases it's pretty easy to build the rest of the
logic in whatever
it is that's asking the question.

Good luck,

Ann




> To know if itens exists or not, select
> select
>   case
> when exists(select 1 from cv_itens  where id_cv=51716)
> then 'Y'
> else 'N'
>   end as existe_itens
> from rdb$database
>
> OR using block execute:
> execute block
> returns(exist varchar(1))
> as
> begin
>   exist='N';
>   if (exists(select 1 from cv_itens  where id_cv=51716)) then exist='Y';
>   suspend;
> end
>
> I think that first method using rdb$database as source, checking
> permissions and others things and second method is not and more powerfull
> using psql.
>
> I would like to know what you guys think about.
>
>
>
> 
>

Re: [firebird-support] Restoring Backup

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Feb 24, 2016 at 12:01 PM, Hugo Eyng hugoe...@msn.com
[firebird-support]  wrote:

Referring to the buffers requested for gbak:

>
>
> Why 12? Why not? :)
>

There's a cost to managing a large buffer cache.  Firebird will use the
entire
cache even if there's no particular reason to do so.

A gbak backup needs a few dozen pages for system table stuff that's
referenced frequently and for each table backed up another few pages
that help locate data.  The data pages are read once and then are of no
more interest.   So the default cache size is fine for a backup.   Two
hundred pages is probably enough to backup almost any database.  If
you use a huge cache, it will be filled with useless data pages.

A gbak restore needs the same few dozen pages of system tables and
some pages for internal table management.  When it is restoring data, it
fills a data page and goes on to the next one.  A large cache will fill with
pages that will not be referenced again until the indexes are built.  To
build indexes, Firebird reads records and sorts by keys.  That might
suggest that keeping millions of pages in cache would improve performance
by eliminating disk reads.   However, unless you've got a huge amount
of memory, those data pages will reduce the amount of memory available
for the sort.  The sort algorithm produces chunks of sorted key/record id
pairs which are subsequently merged.  The more chunks that can be kept
in memory, the faster the sort.   If you've used all your machine's  memory
for the cache, the sort will be slow and may start paging.

Good luck,

Ann

[firebird-support] Gbak and indexes

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

Someone on a different forum asked whether gbak restores wouldn't be faster
if gbak backed up indexes so it didn't have to rebuild them.  The writer
wanted the
clean-up and compaction that a gbak backup and restore provides, but not the
time spent retrieving records, sorting, and rebuilding indexes.

There's a problem with that.  An old index is of absolutely no use in a
restored database exactly because records are in different places.  The
index contains the physical location of records.   Change the location,
invalidate the index.   That's the short answer.

The longer answer:

An index contains a key value and a "record id" also called a db-key. When
an index search finds an acceptable approximation of the value desired, it
uses the db-key to find the corresponding record.

 (It's actually more complicated than that, and depends on whether the
index is being used to optimize a sort, but in the end, an index search
finds a db-key and uses it to find a record.)

What's in a db-key?  Three things: a pointer page sequence number, and
offset into the pointer page, and an index offset on the data page.  A
pointer page is a part of the structure of a table that's not visible
outside the database.  It's simply a page that contains a vector of page
numbers.  Those page numbers belong to the data pages in the table.  The
RDB$PAGES table contains the sequence number and page number of pointer
pages for all tables, among other things.  After a table has been
referenced, the pointer page information is kept in memory. The pointer
page may be in cache or Firebird may need to read it from disk.

With the pointer page in its virtual hand, Firebird reads the page number
at the offset indicated by the db-key to get the page number of the
appropriate data page and finds the data page in cache or on disk.  The
offset on the data page in the db-key is an indirect pointer to the
record.  Each data page has an index to the records on it containing the
actual offset and length of the record, so a page can be reorganized
without changing the db-keys of the records on it.

A gbak restore creates a new database and populates it with the data it
backed up from the old database.  Records will be stored on different pages
and at different offsets.  Pages that were part of one table in the old
database may be part of a different table or some other structure in the
new database.

Gbak restore could be made faster if Firebird recognized at a commit that
the transaction created several indexes on the same table and built all the
indexes in parallel rather than reading the table for each index, but using
an old index in a new database is not a good idea.

Good luck,

Ann