AW: [firebird-support] Firebird lock conflict
Hello again, there are some problems which are present since the migration of firebird 3.0 RC2. Two of them I have reportet, does anyone know where now some records locked and ibexpert can change them? thanks Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Montag, 14. März 2016 12:33 An: firebird-support@yahoogroups.com Betreff: [firebird-support] Firebird lock conflict Hello, fb3 rc2, newest odbc driver.. If I have to change some records, it works fine with ibexpert, but with odbc connection (linked tables) I get for he same records an lock conflict. No problem with the old version 2.5 or 2.1. Do you know which time firebird 3 final will realeased? Thanks
Re: [firebird-support] Composite index - issue or not existing feature?
Hi Karol! At the risk of this being confusing or even incorrect (I trust Dmitry or Ann will correct if it is incorrect). >SELECT * FROM dbo.XXX X WHERE X.A BETWEEN 2 AND 30 *AND* X.B BETWEEN 5 AND 60 My understanding of this is that Firebird (in theory) have two choices. Either (a) use XXX__A__B once using it exclusively for A or (b) use XXX__A__B 29 times (2 through 30) using it for both A and B. Firebird prefers to use (a). If you added a new index covering only B, then Firebird could use XXX__A__B UNIQUE for A and also the new index for B (unlike many other databases, Firebird can utilize several indexes for each table of a query). Think of XXX__A__B more as the index of a book, than a tree. You would have to look up the "subchapter" of B under each "main chapter" of A. I never run into this issue, simply because I always(*) prefer single field indexes. HTH (even though it may be overly simplified), Set (*) In theory with the exception of 'borderline performance issues', were single field indexes are too slow, whereas multifield indexes are quick enough. Though I have to say that I work on smaller databases than you do and never have experienced this (I prefer simplicity over - let me make a wild guess - 20% performance improvement).
Re: [firebird-support] Re: Composite index - issue or not existing feature?
On Mon, Mar 14, 2016 at 3:41 AM, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > W dniu 2016-03-14 08:36:40 użytkownik Dmitry Yemanov > dim...@users.sourceforge.net [firebird-support] < > firebird-support@yahoogroups.com> napisał: > > > > SELECT * FROM dbo.XXX X WHERE X.A BETWEEN 2 AND 30 *AND* X.B BETWEEN 5 > > AND 60 > ... > > As you can see only A key is used but B key should be also used. > > I am missing something? > > Yes, you do. If the first segment is matched for non-equality, then the > following segments cannot be used. > > > Why? > Index is a Tree? And if i found VALUE 2 in A key then i can fast find > value 5 in sub key (leaf) > You scan through keys in A, and then in finded nodes you look for leafs in > B > Expanding on Dmitry's answer... Yes, the index is a tree, but it's not a tree the way you imagine it. At least not the way I imagine you imagine it, which is something like this:. The top of the index has ranges of values for the first field in the index, which point to narrower ranges of values for for the first field, going down to the point where you get a single value for the first field with ranges of values for the second field hanging under it. Indexes like that get unbalanced easily and tend to be very deep. A Firebird index key is a single value built out of all parts of a compound key with some extra stuff dribbled around so you can tell the difference between the pairs of values "deadlock" "search" and "dead" "locksearch". The whole key is mangled so it compares correctly bytewise. An index entry has a prefix, a key - possibly missing some front bytes - and the database key (record id) of the matching record. A single level Firebird index is just a stream of index entries. It's a little more complicated than that to reduce the cost of reading large pages(*). When there are too many entries to fit on a single page, Firebird splits the page and creates a new level(**) above the level with index keys and record ids. The upper level has index keys, record ids, and the page number of the lower level index page that starts with that index key and record id(***). The lower levels have pointers to their upper level, and to their right and left neighbors. Each time an index page splits(), the first key and record id pair of the new page gets pushed up to the next level. Eventually the upper level has to split and a new, even higher level is created pointing to the next layer down. Which is a very long winded way of saying that the Firebird index handling code hasn't a clue that it's looking at a compound index, let alone where the values are split. If you want to do range retrievals on several fields, create an index for each one. Then, when Firebird executes the search, it will search one index, building a bitmap of record ids in that range, then search the other building a second bitmap and combine the two bitmaps to retrieve only those records that match both criteria. Good luck, Ann (*) Index entries are variable length so you can't use a binary search on an index page. When pages got bigger that 4KBb, the time spent on index searches went mostly into reading, on average, half the page. Now indexes in databases with large page sizes have an internal index that cuts the average read of a 16Kb page from 8Kb to 1Kb (**) To simplify internal bookkeeping, the first physical page allocated to an index will always be the top page, so there's some fancy data shuffling when a new level is created. First Firebird creates two new pages, then it moves the data from the old top level to the new pages and fills the old top level with pointers down to the new pages. (***) The record id is propagated to the upper levels to make each index entry unique, even when the keys aren't. That saves a lot of time in index garbage collection but isn't otherwise interesting. () The way a page split works depends on where the entry that caused the split would go on the page. If the new entry goes in the middle of the page, half the entries are moved to the new page. If it would have been the last entry, most of the entries stay on the old page and only enough to start the new page goes on that page. So if you're loading records in key order - or using a sequence to create your keys - the index stays dense.
[firebird-support] Re: Python FDB Driver, Dialect 3 and NUMERIC
On Mon, Mar 14, 2016 at 9:58 AM, Ray Cote wrote: > I’ve run into a problem while porting code over from the old > KInterbasDB library to the new FDB Python library. Receiving the error > “-817 Metadata update statement not allowed any the current database SQL > dialect 3” when attempting to create a table with NUMERIC(15, 3). > > My understanding is that this statement is fine for dialect 3, and should > store as a BIGINT (page 135 of The Firebird Book). This code has been > working fine with KInterbasDB. > > If I change NUMERIC(15,3) to NUMERIC(9,3) the code runs properly. > Perhaps there’s something I’ve not set to allow the BIGINT storage? > As some self-followup, I know there can be issues with NUMERIC precisions 10 and over between dialects 1 and 3 — as per this note from Helen Borrie: https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/3793 But I’m pretty sure I’m running dialect 3 here. Tried creating the database and then re-connecting while setting sql_dialect parameter to 3. Get same error. The SQL runs fine in the isql client — just failing through FDB driver. —Ray -- Raymond Cote, President voice: +1.603.924.6079 email: rgac...@appropriatesolutions.com skype: ray.cote
[firebird-support] Re: Composite index - issue or not existing feature?
14.03.2016 10:41, liviuslivius wrote:
> Why?
> Index is a Tree? And if i found VALUE 2 in A key then i can fast find
> value 5 in sub key (leaf)
> You scan throught keys in A, and then in finded nodes you look for leafs
> in B
Compound index key is a single concatenated value, not two separate
ones. You may search for exact match {2, 5} or for its "substring" {2}.
You cannot get the second segment from the key as independent value.
Firebird indices are imprecise and generally you may convert value->key
but cannot key->value.
Dmitry
[firebird-support] Python FDB Driver, Dialect 3 and NUMERIC
Hello: I’ve run into a problem while porting code over from the old KInterbasDB library to the new FDB Python library. Receiving the error “-817 Metadata update statement not allowed any the current database SQL dialect 3” when attempting to create a table with NUMERIC(15, 3). My understanding is that this statement is fine for dialect 3, and should store as a BIGINT (page 135 of The Firebird Book). This code has been working fine with KInterbasDB. If I change NUMERIC(15,3) to NUMERIC(9,3) the code runs properly. Perhaps there’s something I’ve not set to allow the BIGINT storage? Guidance appreciated. —Ray Sample source: I’m running: 32-bit Windows 10 Python 2.7.11 FDB library 1.5.1 Firebird Embedded 2.1.5.18497/Dialect 3. import fdb con = fdb.create_database( "CREATE DATABASE 'test.fdb' USER 'test' PASSWORD 'test'", sql_dialect=3 ) sql = """ CREATE TABLE bob ( field2 NUMERIC(15, 3) NOT NULL ); """ con.execute_immediate(sql) ‘Error while executing SQL statement:\n- SQLCODE: -817\n- Dynamic SQL Error\n- SQL error code = -817\n- Metadata update statement is not allowed by the current database SQL dialect 3', -817, 335544569 -- Raymond Cote, President voice: +1.603.924.6079 email: rgac...@appropriatesolutions.com skype: ray.cote
[firebird-support] Firebird lock conflict
Hello, fb3 rc2, newest odbc driver.. If I have to change some records, it works fine with ibexpert, but with odbc connection (linked tables) I get for he same records an lock conflict. No problem with the old version 2.5 or 2.1. Do you know which time firebird 3 final will realeased? Thanks
Re: [firebird-support] Re: Composite index - issue or not existing feature?
W dniu 2016-03-14 08:36:40 użytkownik Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] napisał: 14.03.2016 10:32, liviuslivius wrote: > > simple table > CREATE TABLE XXX( > A INTEGER > , B INTEGER > , CONSTRAINT UK_XXX__A__B UNIQUE(A, B) > ) > commit; > SELECT * FROM dbo.XXX X WHERE X.A BETWEEN 2 AND 30 *AND* X.B BETWEEN 5 > AND 60 > Select Expression > -> Filter > -> Table "XXX" as "X" Access By ID > -> Bitmap > -> Index "UK_XXX__A__B" Range Scan (lower bound: *1/2*, > upper bound: *1/2*) > As you can see only A key is used but B key should be also used. > I am missing something? Yes, you do. If the first segment is matched for non-equality, then the following segments cannot be used. Dmitry._,_._ Hi Dmitry, Why? Index is a Tree? And if i found VALUE 2 in A key then i can fast find value 5 in sub key (leaf) You scan throught keys in A, and then in finded nodes you look for leafs in B reagards, Karol Bieniaszewski
[firebird-support] Re: Composite index - issue or not existing feature?
14.03.2016 10:32, liviuslivius wrote: > > simple table > CREATE TABLE XXX( > A INTEGER > , B INTEGER > , CONSTRAINT UK_XXX__A__B UNIQUE(A, B) > ) > commit; > SELECT * FROM dbo.XXX X WHERE X.A BETWEEN 2 AND 30 *AND* X.B BETWEEN 5 > AND 60 > Select Expression > -> Filter > -> Table "XXX" as "X" Access By ID > -> Bitmap > -> Index "UK_XXX__A__B" Range Scan (lower bound: *1/2*, > upper bound: *1/2*) > As you can see only A key is used but B key should be also used. > I am missing something? Yes, you do. If the first segment is matched for non-equality, then the following segments cannot be used. Dmitry
[firebird-support] Composite index - issue or not existing feature?
Hi, simple table CREATE TABLE XXX( A INTEGER , B INTEGER , CONSTRAINT UK_XXX__A__B UNIQUE(A, B) ) commit; SELECT * FROM dbo.XXX X WHERE X.A BETWEEN 2 AND 30 AND X.B BETWEEN 5 AND 60 Select Expression -> Filter -> Table "XXX" as "X" Access By ID -> Bitmap -> Index "UK_XXX__A__B" Range Scan (lower bound: 1/2, upper bound: 1/2) As you can see only A key is used but B key should be also used. I am missing something? Is this issue in Firebird3 or this is not existing feature? AFAIK in MSSQL this work ok regards, Karol Bieniaszewski
